아침에 출근하자마자 가장 먼저 하는 일 중 하나가 '실시간 환율'이나 '주식 시세'를 검색창에 치고 숫자를 확인하는 직장인들이 참 많습니다. 해외 영업팀이라면 오늘의 달러(USD) 환율을 확인해 정산 시트에 수동으로 곱해 주어야 하고, 투자 담당자나 자산 관리자라면 보유한 포트폴리오의 실시간 주가 변동을 일일이 업데이트해야 하죠.
게다가 경쟁사 제품의 판매 가격이 변했는지 확인하기 위해 하루에도 몇 번씩 경쟁사 쇼핑몰 링크를 열어보고 가격을 복사해 내 시트에 기록하는 '수동 크롤링'을 당연하게 감수하기도 합니다. 이 짧은 작업은 매일 반복되면 한 달에 수십 시간을 갉아먹는 엄청난 좀도둑이 됩니다. 때로는 사람이 손으로 숫자를 옮겨 적다가 오타를 내어 분기 정산 시 대참사가 발생하기도 합니다.
하지만 구글 스프레드시트는 세상의 모든 실시간 데이터 흐름과 연결될 수 있는 강력한 '클라우드 허브'입니다. 오늘은 수식 단 한 줄만 적어두면 내가 자고 있는 동안에도 환율, 주가, 웹사이트의 최신 정보를 알아서 긁어와 실시간으로 채워주는 '외부 데이터 연동 공식'을 전수해 드립니다.
## 1. 금융 데이터의 치트키: GOOGLEFINANCE 함수로 실시간 환율과 주가 트래킹
구글 스프레드시트에는 엑셀 사용자들이 가장 부러워하는 독보적인 사기급 함수가 내장되어 있습니다. 바로 구글 파이낸스가 제공하는 실시간 금융 시장 데이터를 곧바로 시트에 얹어주는 GOOGLEFINANCE 함수입니다.
실시간 원달러 환율 불러오기 공식:
=GOOGLEFINANCE("CURRENCY:USDKRW")이 수식을 입력하고 엔터를 치는 순간, 오늘의 실시간 원달러 환율 숫자가 시트에 툭 떨어집니다. 이 수식 옆에 내가 정산해야 할 외화 금액 셀을 곱해주기만 하면, 실시간 원화 환산 금액 연산 시스템이 뚝딱 완성됩니다.
수학적으로 이 관계를 표현하면 다음과 같이 모델링할 수 있습니다.
$$\text{원화 환산 금액} = \text{외화 금액} \times \text{GOOGLEFINANCE("CURRENCY:USDKRW")}$$
실시간 주가 정보 가져오기 공식:
=GOOGLEFINANCE("NASDAQ:GOOG", "price")앞에는
시장:티커형태로 적어주고, 뒤에는 내가 알고 싶은 정보인"price"(가격)를 적어주면 구글의 실시간 주식 가격이 연동됩니다.가격뿐만 아니라 시가총액(
"marketcap"), 주가수익비율("pe"), 심지어 특정 기간 동안의 일별 역사적 주가 흐름까지 표 형태로 긁어올 수 있어 개인 포트폴리오 자산 대시보드를 구축하는 데 최고의 무기입니다.
## 2. 웹 정보 수집의 기본: IMPORTHTML로 웹상의 표(Table) 통째로 긁어오기
주가나 환율 외에 일반 인터넷 웹사이트나 블로그, 위키백과 등에 정리되어 있는 격자무늬의 '표(Table)'나 '리스트(List)' 데이터를 내 시트로 긁어오고 싶을 때가 있습니다. 드래그해서 복사-붙여넣기를 하면 포맷이 다 깨지기 십상이죠. 이럴 때는 IMPORTHTML 함수를 사용하면 단 1초 만에 표 전체를 시트로 대접해 줍니다.
IMPORTHTML 공식 기본형:
=IMPORTHTML("웹사이트_주소", "table", 몇_번째_표_인지_숫자)예를 들어, 위키백과의 특정 경제 지표 표가 있는 웹 주소를 넣고 두 번째 인자에
"table", 세 번째 인자에1을 적어주면 해당 페이지에 있는 첫 번째 표의 행과 열이 내 시트에 셀별로 깔끔하게 쪼개어져 완벽히 이식됩니다.복잡한 파이썬 크롤링 코딩을 한 줄도 모르는 문과생이라도, 클릭과 주소 복사 한 번만으로 웹 데이터 수집가가 될 수 있는 놀라운 경험을 선사합니다.
## 3. 크롤링의 끝판왕: IMPORTXML과 XPath로 특정 텍스트 저격하기
표 형식이 아니라 웹사이트의 특정 영역에 있는 숫자(예: 네이버 페이의 내 포인트, 특정 뉴스 기사의 제목, 쇼핑몰의 실시간 가격 등) 딱 한 단어만 조준 사격하듯 솎아내고 싶을 때는 가장 강력한 끝판왕 함수인 IMPORTXML을 사용합니다.
이 함수를 쓰기 위해서는 내가 가져오고 싶은 글자의 인터넷 주소인 'XPath'라는 개념을 딱 한 번만 이해하시면 됩니다.
XPath 아주 쉽게 따는 방법:
크롬 브라우저에서 원하는 웹페이지를 켭니다. (예: 어떤 쇼핑몰의 가격 표시 부분)
가격 숫자 위에 마우스를 올리고 우클릭을 한 뒤 '검사(Inspect)'를 누릅니다.
우측에 복잡한 개발자 도구 창이 켜지며 해당 글자가 위치한 코드 블록이 파란색으로 선택되어 있습니다.
그 파란색 코드 영역 위에서 다시 우클릭을 한 뒤
[Copy] -> [Copy XPath]를 클릭합니다. 내 컴퓨터 클립보드에 해당 글자의 가상 주소가 복사되었습니다. (예://*[@id="price_amount"])
IMPORTXML 공식 대입법:
=IMPORTXML("웹사이트_주소", "복사한_XPath_주소")단, 수식 안에 넣을 때는 복사한 XPath 내부의 큰따옴표(
")를 작은따옴표(')로 바꾸어 감싸주어야 수식 에러가 나지 않습니다.예:
=IMPORTXML("https://example.com/item", "//*[@id='price_amount']")이렇게 적어두면, 해당 웹페이지가 열릴 때마다 실시간으로 가격 변동 정보를 내 시트로 자동 배달받을 수 있습니다.
## 4. 외부 데이터 연결 시 실무자가 반드시 조심해야 할 3대 한계점
이 외부 수집 수식들은 너무나 강력해서 온갖 페이지를 다 긁어오고 싶어지게 만듭니다. 하지만 기술적인 '만능'은 없습니다. 안정적인 자동화 대시보드를 유지하기 위해 아래의
1) 자바스크립트(JS) 기반 페이지는 수집 불가능:
IMPORTXML과IMPORTHTML은 정적인 웹 소스 코드(HTML)만 읽을 수 있습니다.최근 유행하는 동적 로딩 웹사이트(화면을 아래로 내릴 때 가격이 뒤늦게 뜨거나, 로그인 세션이 유지되어야만 데이터가 보이는 페이지, 혹은 React/Vue 등으로 설계된 페이지)는 이 함수들이 접근했을 때 텅 빈 값이나 에러를 뿜어냅니다.
수식을 썼는데 아무것도 나오지 않는다면 해당 사이트가 동적 스크립트로 동작하고 있기 때문입니다.
2) 잦은 호출로 인한 임시 밴(Ban)과 IFERROR 예방:
구글 서버를 통해 상대방 웹사이트의 데이터를 계속해서 긁어오는 구조이기 때문에, 상대방 보안 서버 측에서 "이 시트는 너무 비정상적으로 조회를 많이 하네?" 하고 IP나 접근을 임시로 차단해 버릴 수 있습니다. 이럴 때는 시트 전체가
#N/A에러로 도배됩니다.이를 막기 위해 반드시 2편에서 배운
IFERROR함수로 감싸 두는 습관을 들여야 대시보드의 레이아웃이 무너지지 않습니다.예:
=IFERROR(IMPORTXML("URL", "XPath"), "데이터 확인 필요")
3) 구글 시트 자체의 호출 한계량 제어:
구글 스프레드시트 파일 하나당 외부 데이터를 요청할 수 있는 API 호출 제한 한계는 약
$50,000$ 건 수준입니다.데이터베이스의 수천 개의 행마다 개별적으로
IMPORTXML수식을 주렁주렁 달아두면, 구글 시트가 연산을 멈춰버리거나 로딩 속도가 끔찍하게 느려집니다.대량 수집이 필요할 때는 뒤에서 배울 '구글 앱스 스크립트(Apps Script)'를 통해 시간차 정기 수집(트리거) 시스템으로 전환해야 시트의 속도를 지킬 수 있습니다.
[7편 핵심 요약]
=GOOGLEFINANCE함수를 활용하면 매일 변동하는 실시간 달러 환율 및 주식 자산 데이터를 수동 조작 없이 실시간 무한 동기화할 수 있다.=IMPORTHTML은 특정 웹페이지 내에 정리된 격자무늬의 표(Table)나 목록을 클릭 한 번으로 흐트러짐 없이 시트에 이식해 준다.크롬 개발자 도구의 'Copy XPath' 기능을 통해 특정 텍스트 주소를 복사한 뒤
=IMPORTXML에 대입하면 경쟁사 가격 등 표 외의 단어 데이터까지 크롤링이 가능하다.보안 페이지나 동적 로딩(JS) 페이지는 수집이 불가능하며, 잦은 오류와 과부하를 예방하기 위해
IFERROR쉴드를 걸어주고 수집 개수를 최소화하는 조절이 필수적이다.
[다음 편 예고]
다음 8편에서는 복잡한 함수 코딩이나 세팅조차 귀찮은 실무자들을 위해, 마우스로 내가 정해둔 움직임을 단 한 번만 녹화해 두면 버튼 하나로 그 움직임 전체를 영원히 반복 복제해 주는 '코딩 제로 자동화: 매크로(Macro) 기록 기능 활용법'을 아주 쉽고 친절하게 다룹니다.
[댓글 유도 질문]
평소에 실시간 주가나 환율, 혹은 경쟁사 가격 정보를 수동으로 확인하느라 매일 아침 시간을 허비했던 번거로운 시나리오가 있으셨나요? 오늘 소개해 드린 자동화 기법 중 어떤 것을 시트에 가장 먼저 연동해 보고 싶으신지 댓글로 공유해 주세요!
아침에 출근하자마자 가장 먼저 하는 일 중 하나가 '실시간 환율'이나 '주식 시세'를 검색창에 치고 숫자를 확인하는 직장인들이 참 많습니다. 해외 영업팀이라면 오늘의 달러(USD) 환율을 확인해 정산 시트에 수동으로 곱해 주어야 하고, 투자 담당자나 자산 관리자라면 보유한 포트폴리오의 실시간 주가 변동을 일일이 업데이트해야 하죠.
게다가 경쟁사 제품의 판매 가격이 변했는지 확인하기 위해 하루에도 몇 번씩 경쟁사 쇼핑몰 링크를 열어보고 가격을 복사해 내 시트에 기록하는 '수동 크롤링'을 당연하게 감수하기도 합니다. 이 짧은 작업은 매일 반복되면 한 달에 수십 시간을 갉아먹는 엄청난 좀도둑이 됩니다. 때로는 사람이 손으로 숫자를 옮겨 적다가 오타를 내어 분기 정산 시 대참사가 발생하기도 합니다.
하지만 구글 스프레드시트는 세상의 모든 실시간 데이터 흐름과 연결될 수 있는 강력한 '클라우드 허브'입니다. 오늘은 수식 단 한 줄만 적어두면 내가 자고 있는 동안에도 환율, 주가, 웹사이트의 최신 정보를 알아서 긁어와 실시간으로 채워주는 '외부 데이터 연동 공식'을 전수해 드립니다.
## 1. 금융 데이터의 치트키: GOOGLEFINANCE 함수로 실시간 환율과 주가 트래킹
구글 스프레드시트에는 엑셀 사용자들이 가장 부러워하는 독보적인 사기급 함수가 내장되어 있습니다. 바로 구글 파이낸스가 제공하는 실시간 금융 시장 데이터를 곧바로 시트에 얹어주는 GOOGLEFINANCE 함수입니다.
실시간 원달러 환율 불러오기 공식:
=GOOGLEFINANCE("CURRENCY:USDKRW")이 수식을 입력하고 엔터를 치는 순간, 오늘의 실시간 원달러 환율 숫자가 시트에 툭 떨어집니다. 이 수식 옆에 내가 정산해야 할 외화 금액 셀을 곱해주기만 하면, 실시간 원화 환산 금액 연산 시스템이 뚝딱 완성됩니다.
수학적으로 이 관계를 표현하면 다음과 같이 모델링할 수 있습니다.
$$\text{원화 환산 금액} = \text{외화 금액} \times \text{GOOGLEFINANCE("CURRENCY:USDKRW")}$$
실시간 주가 정보 가져오기 공식:
=GOOGLEFINANCE("NASDAQ:GOOG", "price")앞에는
시장:티커형태로 적어주고, 뒤에는 내가 알고 싶은 정보인"price"(가격)를 적어주면 구글의 실시간 주식 가격이 연동됩니다.가격뿐만 아니라 시가총액(
"marketcap"), 주가수익비율("pe"), 심지어 특정 기간 동안의 일별 역사적 주가 흐름까지 표 형태로 긁어올 수 있어 개인 포트폴리오 자산 대시보드를 구축하는 데 최고의 무기입니다.
## 2. 웹 정보 수집의 기본: IMPORTHTML로 웹상의 표(Table) 통째로 긁어오기
주가나 환율 외에 일반 인터넷 웹사이트나 블로그, 위키백과 등에 정리되어 있는 격자무늬의 '표(Table)'나 '리스트(List)' 데이터를 내 시트로 긁어오고 싶을 때가 있습니다. 드래그해서 복사-붙여넣기를 하면 포맷이 다 깨지기 십상이죠. 이럴 때는 IMPORTHTML 함수를 사용하면 단 1초 만에 표 전체를 시트로 대접해 줍니다.
IMPORTHTML 공식 기본형:
=IMPORTHTML("웹사이트_주소", "table", 몇_번째_표_인지_숫자)예를 들어, 위키백과의 특정 경제 지표 표가 있는 웹 주소를 넣고 두 번째 인자에
"table", 세 번째 인자에1을 적어주면 해당 페이지에 있는 첫 번째 표의 행과 열이 내 시트에 셀별로 깔끔하게 쪼개어져 완벽히 이식됩니다.복잡한 파이썬 크롤링 코딩을 한 줄도 모르는 문과생이라도, 클릭과 주소 복사 한 번만으로 웹 데이터 수집가가 될 수 있는 놀라운 경험을 선사합니다.
## 3. 크롤링의 끝판왕: IMPORTXML과 XPath로 특정 텍스트 저격하기
표 형식이 아니라 웹사이트의 특정 영역에 있는 숫자(예: 네이버 페이의 내 포인트, 특정 뉴스 기사의 제목, 쇼핑몰의 실시간 가격 등) 딱 한 단어만 조준 사격하듯 솎아내고 싶을 때는 가장 강력한 끝판왕 함수인 IMPORTXML을 사용합니다.
이 함수를 쓰기 위해서는 내가 가져오고 싶은 글자의 인터넷 주소인 'XPath'라는 개념을 딱 한 번만 이해하시면 됩니다.
XPath 아주 쉽게 따는 방법:
크롬 브라우저에서 원하는 웹페이지를 켭니다. (예: 어떤 쇼핑몰의 가격 표시 부분)
가격 숫자 위에 마우스를 올리고 우클릭을 한 뒤 '검사(Inspect)'를 누릅니다.
우측에 복잡한 개발자 도구 창이 켜지며 해당 글자가 위치한 코드 블록이 파란색으로 선택되어 있습니다.
그 파란색 코드 영역 위에서 다시 우클릭을 한 뒤
[Copy] -> [Copy XPath]를 클릭합니다. 내 컴퓨터 클립보드에 해당 글자의 가상 주소가 복사되었습니다. (예://*[@id="price_amount"])
IMPORTXML 공식 대입법:
=IMPORTXML("웹사이트_주소", "복사한_XPath_주소")단, 수식 안에 넣을 때는 복사한 XPath 내부의 큰따옴표(
")를 작은따옴표(')로 바꾸어 감싸주어야 수식 에러가 나지 않습니다.예:
=IMPORTXML("https://example.com/item", "//*[@id='price_amount']")이렇게 적어두면, 해당 웹페이지가 열릴 때마다 실시간으로 가격 변동 정보를 내 시트로 자동 배달받을 수 있습니다.
## 4. 외부 데이터 연결 시 실무자가 반드시 조심해야 할 3대 한계점
이 외부 수집 수식들은 너무나 강력해서 온갖 페이지를 다 긁어오고 싶어지게 만듭니다. 하지만 기술적인 '만능'은 없습니다. 안정적인 자동화 대시보드를 유지하기 위해 아래의
1) 자바스크립트(JS) 기반 페이지는 수집 불가능:
IMPORTXML과IMPORTHTML은 정적인 웹 소스 코드(HTML)만 읽을 수 있습니다.최근 유행하는 동적 로딩 웹사이트(화면을 아래로 내릴 때 가격이 뒤늦게 뜨거나, 로그인 세션이 유지되어야만 데이터가 보이는 페이지, 혹은 React/Vue 등으로 설계된 페이지)는 이 함수들이 접근했을 때 텅 빈 값이나 에러를 뿜어냅니다.
수식을 썼는데 아무것도 나오지 않는다면 해당 사이트가 동적 스크립트로 동작하고 있기 때문입니다.
2) 잦은 호출로 인한 임시 밴(Ban)과 IFERROR 예방:
구글 서버를 통해 상대방 웹사이트의 데이터를 계속해서 긁어오는 구조이기 때문에, 상대방 보안 서버 측에서 "이 시트는 너무 비정상적으로 조회를 많이 하네?" 하고 IP나 접근을 임시로 차단해 버릴 수 있습니다. 이럴 때는 시트 전체가
#N/A에러로 도배됩니다.이를 막기 위해 반드시 2편에서 배운
IFERROR함수로 감싸 두는 습관을 들여야 대시보드의 레이아웃이 무너지지 않습니다.예:
=IFERROR(IMPORTXML("URL", "XPath"), "데이터 확인 필요")
3) 구글 시트 자체의 호출 한계량 제어:
구글 스프레드시트 파일 하나당 외부 데이터를 요청할 수 있는 API 호출 제한 한계는 약
$50,000$ 건 수준입니다.데이터베이스의 수천 개의 행마다 개별적으로
IMPORTXML수식을 주렁주렁 달아두면, 구글 시트가 연산을 멈춰버리거나 로딩 속도가 끔찍하게 느려집니다.대량 수집이 필요할 때는 뒤에서 배울 '구글 앱스 스크립트(Apps Script)'를 통해 시간차 정기 수집(트리거) 시스템으로 전환해야 시트의 속도를 지킬 수 있습니다.
[7편 핵심 요약]
=GOOGLEFINANCE함수를 활용하면 매일 변동하는 실시간 달러 환율 및 주식 자산 데이터를 수동 조작 없이 실시간 무한 동기화할 수 있다.=IMPORTHTML은 특정 웹페이지 내에 정리된 격자무늬의 표(Table)나 목록을 클릭 한 번으로 흐트러짐 없이 시트에 이식해 준다.크롬 개발자 도구의 'Copy XPath' 기능을 통해 특정 텍스트 주소를 복사한 뒤
=IMPORTXML에 대입하면 경쟁사 가격 등 표 외의 단어 데이터까지 크롤링이 가능하다.보안 페이지나 동적 로딩(JS) 페이지는 수집이 불가능하며, 잦은 오류와 과부하를 예방하기 위해
IFERROR쉴드를 걸어주고 수집 개수를 최소화하는 조절이 필수적이다.
[다음 편 예고]
다음 8편에서는 복잡한 함수 코딩이나 세팅조차 귀찮은 실무자들을 위해, 마우스로 내가 정해둔 움직임을 단 한 번만 녹화해 두면 버튼 하나로 그 움직임 전체를 영원히 반복 복제해 주는 '코딩 제로 자동화: 매크로(Macro) 기록 기능 활용법'을 아주 쉽고 친절하게 다룹니다.
평소에 실시간 주가나 환율, 혹은 경쟁사 가격 정보를 수동으로 확인하느라 매일 아침 시간을 허비했던 번거로운 시나리오가 있으셨나요? 오늘 소개해 드린 자동화 기법 중 어떤 것을 시트에 가장 먼저 연동해 보고 싶으신지 댓글로 공유해 주세요!