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

구글 스프레드시트 활용팁 (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에 모두 넣을 수 있습니다. ^-^/

이것부터….
이것까지!!!

 

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

 

 

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

 

요렇게 설정을 해두시면!

 

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

 

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

 

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

 

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

Check Also

구글 스프레드시트 활용팁 (10) – 과거 특정일자의 주가나 환율 등 불러오기

먼저 항상 저에게 글감을 던져주시는 많은 분들께 감사의 말씀을 드립니다. ^_^ 오늘은 melon님이 댓글로 문의하신 …

  • 기가막히네요. 매번 잘 쓰고 있습니다

    • 많은 분들의 시간을 절약하는데 도움이 되고 있어 기쁩니다! ^0^

  • J.H partners

    헉… 댓글 수정 못하는가요? 이름을 진짜 이름적어 버려서.. ^^;;

    • 빈누

      제가 댓글을 승인안해서 안나왔을거에요. 댓글 승인해두었으니 삭제하시면 됩니다. ^_^

      그리고 참고로 요즘 import 함수가 좀 느려진 부분이 좀 있는것 같아요. 특히 다음 금융에서 크롤링 해올때 그러던데… 구글 자체적인 문제이거나 다음쪽 웹페이지 문제일수도 있구요. 현재로써는 뾰족한 수가 없네요. ㅠ_ㅠ

      • J.H partners

        네.. 일단 제가 찾게 되면 말씀 드리겠습니다! 저 실명으로 적힌 댓글은 제가 안지워지는데 혹시 대신 지워 주시겠습니까~?

        • 빈누

          삭제했습니다! 비밀번호 등 입력하셔서 삭제가 가능하실 거라고 생각해서 놔뒀었는데, 그런 기능이 없는건가요? =_=; (저는 항상 로그인이 되어있으니 알길이 없네요…)

  • 크게될놈

    너무 좋은데요 데이터를 불러오는 로딩속도야 기다릴수 있는데 다음 사이트측 문제인지 에러가 너무 잦네요 코스닥정보는 못받아올때도 많고… 매일 수익률을 기록하는 스크립트로 기록중인데 데이터오류로 기록되지 않는날이 생기네요.. 다음보다 좀 더 신뢰할만한 사이트는 없을까요?

    • 빈누

      안그래도 저도 최근에 다시 20분 딜레이 있더라도 안정된게 낫겠다 싶어서 구글파이낸스 함수로 돌아갔어요 ㅠ_ㅠ; 저도 좀 더 안정적으로 가져올 수 있는 방법을 생각해보겠습니다.