구글 스프레드시트 활용팁 (9) - 신규상장 주식까지 반영되는 실시간 종목리스트 만들기 (importxml 함수 활용)

벌써 올린지 1년이 되어가는 이 글에서 우리나라 주식 시장에 상장되어있는 전 종목을 엑셀 화일로 다운받아 구글 스프레드시트에서 활용하는 방법에 대해서 쓴 적이 있습니다. 그런데 이 방법은 쉽고 간단하지만, 새롭게 상장되거나 상장 폐지 되는 종목에 대한 업데이트를 지속적으로 해줘야 한다는 단점이 있습니다. 

오늘은 좀 더 복잡하긴 하지만, 현 시점에서 상장되어있는 모든 종목을 실시간으로 리스트업할 수 있는 방법에 대해서 알려드릴까 합니다. 간단하게 table을 불러올 수 있는 importhtml 함수가 아닌, importxml 함수를 활용해봅시다. :)

IMPORTXML(URL, xpath_검색어)

IMPORTXML("https://en.wikipedia.org/wiki/Moon_landing", "//a/@href")

XML, HTML, CSV, TSV, RSS 및 Atom XML 피드를 포함한 다양한 구조화된 데이터로부터 데이터를 가져옵니다.

표나 목록으로 되어있는 항목만 가져올 수 있는 importhtml 함수와는 달리, 다양한 구조로 되어있는 데이터를 가져올 수 있는 것이 importxml 함수의 장점!

전종목리스트를 불러오기 위해 사용할 웹페이지는 다음 금융의 전종목시세 페이지입니다. 이 페이지에서 모든 종목명과 해당 종목명에 걸린 링크에 대한 정보를 가져오면 됩니다. :)

=importxml("http://finance.daum.net/quote/all.daum?type=S&stype=P","//table[@class='gTable clr']//tr//td[@class='txt']")

이 함수를 사용하면, 모든 종목의 종목명을 텍스트로 불러오게 됩니다. 

=importxml("http://finance.daum.net/quote/all.daum?type=S&stype=P","//table[@class='gTable clr']//tr//td[@class='txt']//a/@href")

비슷하게 생겼지만 살짝 다르게 생긴 요 함수로는 각각의 종목명에 걸려있는 링크 주소를 가져올 수 있습니다.

이제 여기부터는 약간의 엑셀 함수를 응용하시면 됩니다. ^ㅅ^

=right(B3,6)

요 함수를 사용하면 링크 주소에 나오는 가장 오른쪽의 6자리 숫자를 이용해 종목코드를 가져올 수 있구요. 이 6자리 종목코드에다가, 구글에 사용하기 위해서는 "KRX:"를, 야후 파이낸스 코드가 필요할때는 ".KS"를 붙여주면 됩니다. 

다음에서는 코스닥은 또 별도의 페이지에서 전종목시세를 제공하고 있으므로, 해당 페이지를 똑같은 방식으로 불러오면 코스피/코스닥 전 종목의 종목명과 종목코드를 손쉽게 가져올 수 있습니다. 

다음 금융 페이지에서 감사하게도 새롭게 상장되는 기업과 상장 폐지 되는 기업에 대해서는 업데이트를 해주고 계시니, 저희는 단물만 쏙! 빼먹는걸로.....

그런데 여기까지 따라해보신 분이라면 위와 같이, 코스피 리스트 따로 코스닥 리스트 따로 나와서 나중에 종목명으로 참조를 하려고 하면 함수를 여러번 겹쳐서 써야 한다는 단점이 생깁니다. 그래서 마지막으로 이 두가지 리스트를 하나로 합칠 수 있는 꿀팁(!)을 드립니다.

=unique({D3:D;L3:L})

UNIQUE함수는 각 범위를 체크해서 중복된 값이 있으면, 그것을 하나씩만 리스트업해주는 함수인데요. 위와 같이 여러개의 범위를 세미 콜론(;)으로 묶어서 하나로 만들어줄 수 있습니다. 종목코드도 UNIQUE 함수를 써서 묶어주면, 코스피와 코스닥 전체 종목명을 한 COLUMN에 모두 넣을 수 있습니다. ^-^/

이것부터....

이것까지!!!

다른 시트에서 이제 종목명과 종목코드를 참조할때는 훨씬 사용하기 편해지겠죠?

그리고 다른 시트에서 종목명을 입력할때는 마우스 우클릭 >> '데이터 확인' 메뉴를 이용해서, 종목리스트를 드롭다운 메뉴로 불러올 수 있게 만들면 더더욱 사용이 간편해집니다. 

요렇게 설정을 해두시면!

요렇게 드롭다운메뉴가 나와서 간편하고 정확하게 입력이 가능해집니다. :D

이 스프레드시트의 사용을 원하시는 분은 아래의 링크를 방문하셔서 파일 >> 사본만들기를 선택하셔서 본인의 구글 드라이브에 사본을 만들어서 활용하시면 됩니다.

*우리나라 코스피/코스닥 종목 전체 리스트 구글 스프레드시트

*importxml 함수는 해당 웹페이지의 구조를 분석하여, 특정 조건에 맞는 data를 수집해오는 방식입니다. 해당 웹페이지에 변동이 생긴다면, 문제가 생길 수 있습니다. 혹시 이 파일에 문제가 있다면, 댓글로 신고해주세요. :)

파이낸셜프리덤 블로그에 올라오는 새글 편하게 읽는 방법

 -  이메일로 받아보기 / Feedly에서 구독 / RSS Feed 구독하기 / 네이버 이웃커넥트  

 그리고 페이스북에서도 만나요!

Financial Freedom

이 글이 도움되었다 생각하시는 분은 공감 하트 ♡ 클릭 부탁드려요 :D