본 포스팅에 사용되는 함수 :: IMPORTXML, IMPORTRANGE, IMPORTDATA, ARRAYFORMULA, SUBSTITUE, LEFT, RIGHT, CONCAT, IF, IFERROR, VLOOKUP, GOOGLEFINANCE, TODAY, SUM, SUMPRODUCT, UNIQUE, ISBLANK
본 포스팅에 사용되는 Script :: Copy Column
- 구글 스프레드시트~?!
구글 스프레드시트를 일단 들어가야합니다.
그리고 우측 하단의 빨간 동그라미 안에있는 더하기를 클릭해주세요!
새 스프레드시트가 펼쳐졌나요~?
그러면 좌측 하단 시트1을 backdata로 하겠습니다.
앞으로 백데이터로서 작용할것이기 때문이죠.
(시트 이름은 만드시는분이 알아서 정하셔도 되요.)
- 종목명(종목코드) 불러오기
신한금융투자에서 제공하는 데이터를 불러오겠습니다.
E2
=IMPORTXML("http://open.shinhaninvest.com/product/rp/5904011.jsp?sortIdx=5&sortGubun=2&bond_name=","//a[contains(@class, 'product')]") |
그럼 밑에 이미지처럼 쭉~ 내려오죠?
저는 나중을 위해 E열에 넣어놨어요.
- 종목명과 종목코드 분리하기
이걸 종목명이랑 코드명으로 분리해야 나중에 편히 사용할 수 있게됩니다.
F2
=ARRAYFORMULA(IFERROR(SUBSTITUTE(E2:E,if(E2:E="","",RIGHT(E2:E,15)),""),""))
|
[GoogleSpreadsheet] - 구글 스프레드시트 :: ArrayFormula
[GoogleSpreadsheet] - 구글 스프레드시트 :: IF, IFERROR
G2
=ARRAYFORMULA(IFERROR(IF(E2:E="","",LEFT(RIGHT(E2:E,13),12)),""))
|
[GoogleSpreadsheet] - 구글 스프레드시트 :: ArrayFormula
[GoogleSpreadsheet] - 구글 스프레드시트 :: IF, IFERROR
- 현재가 등락률 거래량 거래대금(천) 대수호가수익률 매도호가수익률 불러오기
현재가 빼고 큰 쓸모는 없지만 제공하는 데이터기도 하고… 나중에 쓸모있겠다 싶어 일단 불러올게요.
현재가
H2
=IMPORTXML("http://open.shinhaninvest.com/product/rp/5904011.jsp?sortIdx=5&sortGubun=2&bond_name=","//tr/td[contains(@class, 'r')][1]")
|
등락률
I2
=IMPORTXML("http://open.shinhaninvest.com/product/rp/5904011.jsp?sortIdx=5&sortGubun=2&bond_name=","//tr/td[contains(@class, 'r')][2]")
|
거래량
J2
=IMPORTXML("http://open.shinhaninvest.com/product/rp/5904011.jsp?sortIdx=5&sortGubun=2&bond_name=","//tr/td[contains(@class, 'r')][3]")
|
거래대금(천)
K2
=IMPORTXML("http://open.shinhaninvest.com/product/rp/5904011.jsp?sortIdx=5&sortGubun=2&bond_name=","//tr/td[contains(@class, 'r')][4]")
|
매수호가수익률
L2
=IMPORTXML("http://open.shinhaninvest.com/product/rp/5904011.jsp?sortIdx=5&sortGubun=2&bond_name=","//tr/td[contains(@class, 'r')][5]")
|
매도호가수익률
M2
=IMPORTXML("http://open.shinhaninvest.com/product/rp/5904011.jsp?sortIdx=5&sortGubun=2&bond_name=","//tr/td[contains(@class, 'r')][6]")
|
- 현재가 가공
여기까지 하고나서, 채권 데이터를 불러오는건 끝났으면 하는 바램이었어요.
하지만 특수문자때문에 현재가를 바로 사용할 수도 없고 지우려고 했지만 ArrayFormula가 ImportXML과 사용할 수 없기 때문에!
새로운 열을 사용해야 합니다.(있다가 전환가를 위해서 N열은 아껴둘게요.)
O2
=ARRAYFORMULA(IF(H2:H="","0.00",IFERROR(SUBSTITUTE(SUBSTITUTE(H2:H,"▲ ",""),"▼ ",""),""))) |
여기까지 다 하시면, 간단한 데이터는 볼 수 있습니다.
하지만 아침마다 데이터를 초기화시키는 신함금융증권...
그래서 매일 거래가 없으면, 또 데이터가 올라오지 않아, 있다가도 없다가, 없다가고 있다가 하는 종목들때문에 매일 데이터를 백업해야 합니다.
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [Prologue]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(2)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(3)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [주식 리스트]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [전자화폐]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [종합잔고]