최근 구글 스프레드시트 활용팁 시리즈는 여러분의 문제점을 해결해드리고 있습니다. 이번에는 봄바람님께서 googlefinance 함수에서 중국 상해 주식은 지원하지 않는데, 불러올 수 있는 방법이 있는지를 문의하셔서, 해결책을 또 제시해드립니다! ^ㅁ^/

얼마전까지만 해도 googlefinance 함수로 상해 주식 정보를 불러올 수 있었는데, 구글과 중국 정부 사이가 안좋아서인지 언젠가부터 data가 제공되지 않고 있네요. 야후 파이낸스에서도 안타깝게도 중국 주식에 대한 정보는 제공하고 있지 않기 때문에, 중국 주식 정보를 제공하는 다른 사이트를 이용하도록 하겠습니다.

정보를 긁어올 웹사이트는 중국 주식에 투자하시는 분들이라면 자주 방문하실 aastocks입니다.

 

이 웹사이트를 이용하여 필요한 정보를 불러와보았습니다. 테이블로 된 정보를 불러올 수 있는 importhtml 함수 뿐만 아니라, text를 특정한 조건으로 분리해주는 split 함수, 범위로 되어있는 값 중에서 정해진 위치의 셀값을 가져올 수 있는 index 함수, 내가 원하는 형식의 텍스트만 쏙! 뽑아서 가져올 수 있는 정규표현식 함수인 regexextract 함수 등을 이용해 종합적으로 만들어진 예제입니다. 🙂

 

여기서 가장 기본이 되는 =importhtml(“웹사이트주소,“table”,16) 이라는 함수를 사용해보면, 원래는 아래와 같은 결과가 출력됩니다.

 

이렇게 테이블로 출력되는 값들을 내가 필요한 것만 뽑아서 쓰기 위해서 다양한 함수들이 사용되었죠? 🙂

 

주가 불러오기

=regexextract(index(split(index(importhtml(N3,“table”,16),1,1),char(10)),1,4),“[0-9]*\.[0-9]*”)

 

PER 불러오기

=index(split(index(split(index(importhtml(N3,“table”,16),4,1),char(10)),1,2),“/”),1,1)

 

EPS 불러오기

=REGEXEXTRACT(index(importhtml(N3,“table”,16),4,2),“[0-9]*\.[0-9]*”)

 

PBR 불러오기

=index(split(index(split(index(importhtml(N3,“table”,16),6,1),char(10)),1,2),“/”),1,1)

 

시가배당률 불러오기

=index(split(index(split(index(importhtml(N3,“table”,16),5,1),char(10)),1,2),“/”),1,1)

 

최근에 지급된 배당금

=regexextract(index(split(index(importhtml(N3,“table”,16),10,1),CHAR(10)),1,2),“[0-9]*\.[0-9]*”)

 

배당금이 지급된 날짜 불러오기

=text(index(split(index(importhtml(N3,“table”,16),10,1),CHAR(10)),1,4),“yyyy-mm-dd”)

 

등등

이 글에서 각각의 함수에 대한 설명은 생략합니다;; 궁금하신 분은 제 강의를 들으러오세요……응?;;;;

사용을 원하시는 분은 아래 예제 스프레드시트에 가셔서 파일 >> 사본만들기 하신 뒤 활용하시면 된답니다!

 

중국 상해주식 주가 및 각종 지표 불러오기 예제 시트

*주의사항

최근 들어 import 함수를 사용할때 로딩이 오래걸리는 현상이 자주 목격되고 있습니다. 구글 독스 포럼에서도 전세계 사람들이 모여 왜 안되냐며 난리입니다. 잘 안된다고 단정지어 말하기도 어려운게, 어쩔때는 업데이트가 즉각 이루어지는데, 어쩔때는 몇분이 걸리기도 합니다. 현재 전세계적으로 일어나는 현상인만큼, 사용하시는 분들의 주의와 인내심이 필요합니다!

업데이트가 좀 잘 안되고 에러가 뜬다 싶으면, 시트를 오픈해놓고 잠시 딴짓을 하다가 확인해보면 업데이트가 되어 있곤 합니다. 그것도 100%는 아니지만. =_=;;

Categories: 유용한 것들

18 Comments

구글 스프레드시트 활용팁 (11) – 중국 상해 주식 정보 불러오기

  1. 모닝스타 성공투자5원칙 관련해서 내용정리를 하다보니
    빈누님 블로그에 오랜만에 오게 되었네요….
    중국주식에 계량적 지표를 이용한 투자를 하려고 준비중인데
    유용한 팁이 될 것 같습니다. (피터린치, 윌리엄오닐, 짐슬레이터, 문병로 등 객관적 수치화가 가능한 대가들만 대상으로 준비중입니다. )
    구글 스프레드, 막히면 좀 뚫어주실 거죠^^

    p.s. 구월동이 직장입니다, 언젠가는 뵐 일도 있겠죠^^

    1. 변화의서님, 제가 답변이 너무너무 늦었네요! ^^ 만들고 계시는 투자 지표 너무 기대됩니다. 혹시 공개하실 예정이신지요? +ㅁ+

      어려운 부분 있으시면 언제든 의뢰해주시구요. ㅎㅎㅎ

      저 요즘 참여하는 투자모임이 있는데 2주에 한번씩 구월동에 간답니다. 진짜 언젠가 뵐지도요 ^0^

  2. 감사합니다 잘사용하겠읍니다 더불허 한가지 부탁드리자면 .. 홍콩 상해 동시 상장주식의 경우 가격을 원화로 비교할수 있게 해주시면 좋을듯합니다 어느쪽이든 싼쪽으로 매수할수 있을듯 하네요

    1. 좋은 아이디어인것 같습니다. 단지 상장 주식 수 등에서 차이가 날텐데 주가만 비교해서 될까요? EPS나 BPS를 비교하는 방식으로 만들면 의미가 있을 수 있겠네요. ^^

      주가, EPS, BPS 불러와서 원화로 환산하면 A/H주의 가격 차이 뿐만 아니라 환율의 차이로 인한 부분까지도 볼 수 있긴 하겠네요.

  3. 스프레드 시트 정말 유용하게 활용하고 있습니다. 감사합니다.
    그런데 혹시 KRX금시세도 불러올 수 있나요?
    코딩해보면 될 것 같기도 한데 혼자선 잘 안되네요..

    1. =SUBSTITUTE(INDEX(importhtml(“http://info.finance.naver.com/marketindex/goldDetail.nhn”,”table”,2),2,2),”원”,””,1)

      이렇게 하시면 http://info.finance.naver.com/marketindex/goldDetail.nhn 이 페이지에서 “계좌(고객입금시)”에 해당하는 고시가격이 나옵니다. 그 아래에 있는 것들 불러오시려면 중간에 2,2) 이렇게 되어있는 것을 3,2 혹은 4,2 이런식으로 한칸씩 바꾸시면 됩니다.

      1. 적용해보니 잘 되네요! 감사합니다.
        테이블 지정을 어떻게 해야하는지 헷갈렸는데 안내해주신 내용 따라하니 쉽게되네요.

        그런데 KRX금시장 시세와 신한은행에서 제공하는 네이버 국내금시세가 일치하나요?
        약간의 오차가 있더라도 금시세 변동은 같이 움직이니 큰 상관은 없어보이지만 궁금해지네요.

        계좌입금시와 계좌출금시 금 시세가 약 2%차이나는 것을 보면 매매 수수료 1%정도 적용된것 같은데 KRX거래하는 금계좌수익을 스프레드 시트로 관리할때는 계좌입금,출금시 시세의평균을 현재가로 적용해야할지 고민이 되네요.

        1. krx시세를 제공하는 웹사이트를 혹시 알려주실 수 있나요? krx.co.kr에서는 못찾기도 했지만; 일단 거기에서는 구글로 가져올 수 없는 형태의 웹사이트처럼 보여서요. 네이버 증권처럼 html로 krx 시세를 제공해주는 사이트가 있으면, 그곳에서 가져오는 것이 가능합니다.

          1. 저도 krx시세를 제공하는 웹사이트를 찾고 있는데 아직 못찾았습니다. 보통 MTS통해서 확인하기 때문에 html으로는 더 알아봐야 할것 같습니다. 네이버 금시세도 오차가 발생해도 어느정도 보정하면 괜찮을것 같습니다. 나중에라도 알게되면 댓글로 말씀드리겠습니다.
            감사합니다!

          2. 정보 감사합니다. 근데 사본 만들어서 이용할려고 하니 오류가 발생하는데. .지금도 잘 되는 건가요? ref 오류 내용을 보면 함수 index 매개변수 3 값은 4(로) 유효값은 0이상 1이하 입니다. 이렇게 표시 되네요. ㅠㅠ

          3. 지금 확인해보니 잘 되는 것 같습니다. 다만 REF에러가 떠있는 경우에는 아직 로딩중이라서 그런 경우이구요. 너무 많은 데이터를 한번에 불러오려고 하지 마시고 조금씩 로딩하셔서 값만 paste하셔서 쓰는게 좋습니다. real-time으로 쓰려고 하실때는 몇개의 종목만 골라서 하시구요.

  4. 스프레드 시트 정말 유용하게 활용하고 있습니다. 감사합니다.
    그런데 혹시 KRX금시세도 불러올 수 있나요?
    코딩해보면 될 것 같기도 한데 혼자선 잘 안되네요..

    1. =SUBSTITUTE(INDEX(importhtml(“http://info.finance.naver.com/marketindex/goldDetail.nhn”,”table”,2),2,2),”원”,””,1)

      이렇게 하시면 http://info.finance.naver.com/marketindex/goldDetail.nhn 이 페이지에서 “계좌(고객입금시)”에 해당하는 고시가격이 나옵니다. 그 아래에 있는 것들 불러오시려면 중간에 2,2) 이렇게 되어있는 것을 3,2 혹은 4,2 이런식으로 한칸씩 바꾸시면 됩니다.

      1. 적용해보니 잘 되네요! 감사합니다.
        테이블 지정을 어떻게 해야하는지 헷갈렸는데 안내해주신 내용 따라하니 쉽게되네요.

        그런데 KRX금시장 시세와 신한은행에서 제공하는 네이버 국내금시세가 일치하나요?
        약간의 오차가 있더라도 금시세 변동은 같이 움직이니 큰 상관은 없어보이지만 궁금해지네요.
        혹시 전일대비 시세변동%을 불러오려면 범위 지정을 어떻게 해야하는지도 알려주실수 있을까요?

        계좌입금시와 계좌출금시 금 시세가 약 2%차이나는 것을 보면 매매 수수료 1%정도 적용된것 같은데 KRX거래하는 금계좌수익을 스프레드 시트로 관리할때는 계좌입금,출금시 시세의평균을 현재가로 적용해야할지 고민이 되네요.

        1. krx시세를 제공하는 웹사이트를 혹시 알려주실 수 있나요? krx.co.kr에서는 못찾기도 했지만; 일단 거기에서는 구글로 가져올 수 없는 형태의 웹사이트처럼 보여서요. 네이버 증권처럼 html로 krx 시세를 제공해주는 사이트가 있으면, 그곳에서 가져오는 것이 가능합니다.

          1. 저도 krx시세를 제공하는 웹사이트를 찾고 있는데 아직 못찾았습니다. 보통 MTS통해서 확인하기 때문에 html으로는 더 알아봐야 할것 같습니다. 네이버 금시세도 오차가 발생해도 어느정도 보정하면 괜찮을것 같습니다. 나중에라도 알게되면 댓글로 말씀드리겠습니다.
            감사합니다!

  5. 정보 감사합니다. 근데 사본 만들어서 이용할려고 하니 오류가 발생하는데. .지금도 잘 되는 건가요? ref 오류 내용을 보면 함수 index 매개변수 3 값은 4(로) 유효값은 0이상 1이하 입니다. 이렇게 표시 되네요. ㅠㅠ

    1. 지금 확인해보니 잘 되는 것 같습니다. 다만 REF에러가 떠있는 경우에는 아직 로딩중이라서 그런 경우이구요. 너무 많은 데이터를 한번에 불러오려고 하지 마시고 조금씩 로딩하셔서 값만 paste하셔서 쓰는게 좋습니다. real-time으로 쓰려고 하실때는 몇개의 종목만 골라서 하시구요.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다