googlefinance() 함수의 #N/A 오류 시 importxml()로 웹 크롤링 하기

자산관리를 위해 구글 스프레드 시트를 사용하면서 (20분 지연된)실시간 데이터의 반영이 가능해졌다. 하지만 실제 보름 정도 사용해보니 googlefinance() 함수에서 의외로 #N/A를 반환하는 경우가 종종 발생한다. 정확한 이유는 알 수가 없다. 구글링 해봐도 비슷한 이슈들이 있는 듯 하다.

 

그래서 내가 사용하고 있는 방법은 googlefinance() 함수에서 #N/A를 반환할 때 차선책으로 다른 사이트에서 importxml() 함수를 사용해서 웹 크롤링 해오는 방법을 사용하고 있다. 

 

googlefinance() 에서 #N/A를 반환하는 경우에 국내 주식의 현재가 정보는 서울경제 사이트에서 가져온다. 서울경제 사이트는 특정 종목의 현재가 정보를 https://www.sedaily.com/Stock/종목코드의 형태로 제공하고 있어서 비교적 가독성과 접근이 쉽다. 따라서, 우리는 현재가를 표시할 셀에 아래와 같이 수식을 적어주면 된다.

# C7 셀에는 종목의 코드가 기록되어 있다 
=IFERROR(GOOGLEFINANCE(C7,"PRICE"), index(importxml("https://www.sedaily.com/Stock/"&C7,"//*[@id='left-scroll-in']/div[2]/div/ul/li[1]"),1,1))

importxml(url, xpath) 함수는 2개의 인자를 갖는다. 첫번째는 웹 페이지의 주소를 나타내고 두번째 인자는 해당 웹 페이지에서 데이터를 가져올 위치를 나타내는 xpath 이다. 웹 페이지 주소는 주소창에서 복사하면 되지만 xpath는 아래와 같이 특별한 과정을 거쳐야 얻을 수 있다.  

 

먼저 해당 웹 페이지에 접속해서 마우스 우클릭을 하여 팝업창에서 검사를 선택하면 페이지 오른쪽에 소스 뷰어가 열린다. 여기서 뷰어의 왼쪽 상단의 표시된 곳을 클릭하고 웹 페이지에서 우리가 찾기를 원하는 현재가 정보가 있는 위치에서 클릭하면 오른쪽 뷰어에서 해당 데이터의 소스 위치로 이동한다.

 

그리고 위와 같이 해당 소스에서 마우스 우클릭을 하면 xpath 를 복사할 수가 있다. 여기서 복사한 xpath를 importxml() 함수의 두번째 인자로 주면 해당 웹 페이지에서 예를 든 맥쿼리 인프라의 현재가 11,250원을 가져올 수가 있게 된다.

 

하지만 국내 주식의 현재가는 서울경제에서 가져올 수 있지만 미국주식의 정보까지는 제공하지 않는다. 따라서 미국주식은 야후 파이낸스에서 가져오도록 하였다. 야후 파이낸스는 서울경제 사이트보다 주소가 좀 복잡하긴 하지만 방식은 동일하다.

# C34 셀에 종목의 티커가 기록되어 있다.
=IFERROR(GOOGLEFINANCE(C34,"PRICE"),index(importxml("https://finance.yahoo.com/quote/"&C34&"?p="&C34&" &.tsrc=fin-srch","//*[@id='quote-header-info']/div[3]/div[1]/div[1]/fin-streamer[1]"),1,1))

마지막으로 펀드의 기준가 정보는 googlefinance()에서 제공하지 않으며 웹 크롤링을 통해서만 얻을 수 있는데 현재로서는 펀드닥터 사이트 이외에는 모든 펀드들의 기준가 정보를 제공하는 사이트는 찾지 못했다. 물론 개별적인 펀드 운용사의 사이트 들도 있지만 제각각이고 웹 크롤링이 안되는 사이트들도 많다. 다행이도 펀드닥터 사이트에서의 웹 크롤링은 아직까지는 비교적 안정적인 듯 싶다.

 

이상으로 이번 글에서는 한국주식, 미국주식의 가격정보를 googlefinance()를 통해 얻고 오류시 대처 방안으로 importxml() 를 통해 웹 크롤링 하는 방법을 알아보았다.