본 포스팅에 사용되는 함수 :: IMPORTXML, IMPORTRANGE, IMPORTDATA, ARRAYFORMULA, SUBSTITUE, LEFT, RIGHT, CONCAT, IF, IFERROR, VLOOKUP, GOOGLEFINANCE, TODAY, SUM, SUMPRODUCT, UNIQUE, ISBLANK
본 포스팅에 사용되는 Script :: Copy Column
- 주식 리스트
종합잔고에서 빠질 수 없는 주식 현재가!
그 것을 불러오기 위해 주식 리스트가 필요합니다.
다음 증권에서 소스를 가져오는데, 다음증권에선 코스피와 코스닥을 분류 해놨습니다.
그리고 구글Finance 에서 코드 검색을 통해 가져올 때도 코스피 코드와 코스닥 코드가 다르기 때문에 분류되어 있는게 더 좋습니다.
- 코스피 종목 리스트 가져오기
우선 시트 하나를 추가로 열고 이름을 “주식”으로 합니다.
A2
=importxml("http://finance.daum.net/quote/all.daum?type=S&stype=P","//table[@class='gTable clr']//tr//td[@class='txt']")
|
B2
=importxml("http://finance.daum.net/quote/all.daum?type=S&stype=P","//table[@class='gTable clr']//tr//td[@class='txt']//a/@href")
|
//a/@href 를 추가하면 링크된 주소를 불러오는 Xpath_Query 입니다.
이는 링크 주소 마지막에 종목코드가 붙어있기 때문이죠. (숫자로…)
C2
=ArrayFormula(if(A2:A="","",CONCAT("KRX:",right(B2:B,6))))
|
만약 코스피 종목명(A2:A)값이 비어있으면 그대로 두고, 비어있지 않으면 좌측에 KRX를 붙이는데, 우측에는 링크주소(B2:B)의 우측부터 6개의 문자를 가져다 붙이라는 함수입니다.
- 코스닥 종목 리스트 가져오기
D2
=importxml("http://finance.daum.net/quote/all.daum?type=S&stype=Q","//table[@class='gTable clr']//tr//td[@class='txt']")
|
코스닥 종목 불러오는 함수입니다.
E2
=importxml("http://finance.daum.net/quote/all.daum?type=S&stype=Q","//table[@class='gTable clr']//tr//td[@class='txt']//a/@href")
|
위와 동일
F2
=ArrayFormula(if(D2:D="","",CONCAT(right(E2:E,6),".KQ")))
|
만약 코스닥 종목명(D2:D)값이 비어있으면 그대로 두고, 비어있지 않으면, 좌측에 링크주소(E2:E)의 우측부터 6개 문자를 붙이고, 우측에는 .KQ을 붙이라는 함수입니다.
- 코스피 & 코스닥 한 줄로 보기
G2
=unique({A2:A;D2:D})
|
코스피와 코스닥 종목명을 한 줄로 만듭니다.
H2
=unique({C2:C;F2:F})
|
코스피와 코스닥 종목코드를 한 줄로 만듭니다.
- 현재가 보기
I2
=IF(ISBLANK(H2:H),"",GOOGLEFINANCE(H2:H,"price"))
|
구글 파이낸스에서 종목코드(H2:H)의 "price"값을 불러오라는 함수입니다.
GoogleFinance 함수는 ArrayFormula 와 함께할 수 없기 때문에 대충 3000행까지 드래그 해줍니다.
이렇게 주식리스트를 만들어 20분 지연된 시세를 지속적으로 받아볼 수 있게되었습니다.
다음엔 전자화폐 시세를 불러오도록 할게요!
참고
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [Prologue]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(1)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(2)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(3)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [전자화폐]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [종합잔고]