[구글시트 강의] 4강. Googlefinance 함수를 이용한 주가 정보 가져오기 (크롤링)
1. 구글 시트를 활용한 웹 크롤링
: 주가를 비롯한 수많은 증권 정보들은 실시간으로 변하는 값이기에 주식 거래 매매일지를 Manual로 작성하는 것은 주기적으로 업데이트해주야한다는 불편이 따르며, 이 또한 실시간으로 적용이 되지 않아 활용도가 높지 않습니다.
물론 요즈음의 개인 투자자들은 HTS (Home Trading System)보다는 키x증권이나 나x 같은 MTS (Mobile Trading System)을 핸드폰에 깔아두어 언제든 편하게 볼 수는 있지만, 이 또한 이용하는 증권거래소가 2개 이상이거나 혹은 가상화폐나 해외주식 등 다양한 투자처에 분산을 해둔 개인 투자자들의 입장에서는 분산된 자산들을 일일이 확인하기가 힘들 수 있습니다.
개인적으로도 구글시트로 포트폴리오를 통합하기 전에는 나x에 들어가 직접 투자하는 국내주식을 확인한 후, 키x증권에서는 자동매매를 하고 있는 5개의 계좌들을, 가상화폐 현물을 확인하기 위해서는 업x트를, 가상화폐 선물 거래는 바x빗에서, 해외주식은 또 ... 등등, 결국 매일 일일이 확인하기를 포기했었습니다.
서두가 길었지만, 구글 드라이브에서 제공하는 구글 스프레드시트는 웹 기반으로 제작되어 있기에 실시간으로 데이터를 업데이트해줄 수 있으며, 도처에 퍼져있는 여러 증권 정보들을 웹 크롤링을 활용해 가져와주기만 한다면 약간의 가공을 거쳐 개인의 입맛에 맞는 포트폴리오를 만들 수 있습니다. 이번 글에서는 구글시트에서 제공하는 강력한 웹 크롤링 함수인 "Googlefinace 함수"에 대한 소개과 Data를 가져오는 간단한 예시를 설명해드릴 것이며, 앞으로의 포스팅에서 가져온 Data를 가공하는 방법과 포트폴리오를 구성하는 방법을 소개할 계획입니다.
2. Googlefinance 함수
(1) Googlefinance 함수란?
: 구글시트에서 Googlefinance 함수는 "Google Finance"에서 현재 또는 기존 유가증권 정보를 가져와주는 함수입니다.
기존에 Excel을 사용해왔던 사람들에게는 Data를 가공하려고 함수를 사용할 때
'=VLOOKUP(F1,$A$1:$B$5,2,0)' 이나
'=INDEX($A$1:$B$20,2,5)' 처럼,
참조하는 Array를 명시해주는 것이 익숙할 것입니다. 쉽게 말해, Data를 가공해줄 때에도 Reference가 되는 Data는 내가 직접 넣어준 Data가 됩니다.
하지만 위에서 말했듯이 구글시트는 웹 기반으로 만들어져 있기에, Data를 '네이버 금융'이나 '빗x'같은 홈페이지에서도 가져와줄 수 있으며, 위의 Googlefinance 함수의 Data 출처는 "Google Finance" 입니다. 이때 홈페이지명이 함수명과 같아서 헷갈리기는 하지만, 'Google Finance'는 네이버에서 관리하는 '네이버 금융'와 같이 '구글'에서 관리하는 금융 사이트라 생각하면 됩니다.
https://www.google.com/finance/
Google Finance - Stock Market Prices, Real-time Quotes & Business News
Google Finance provides real-time market quotes, international exchanges, up-to-date financial news, and analytics to help you make more informed trading and investment decisions.
www.google.com
(2) Googlefinance 함수의 입력 형식
'=GOOGLEFINANCE(Ticker, [Attribute], [Start_date], [End_date|num_days], [interval])'
: Googlefinace 함수의 입력 형식은,
'=googlefinance("GOOG", "price")
와 같이 필수 인수인 'Ticker'와 '[Attribute]' 만 입력해주어서도 사용 가능하며
'=googlefinance("GOOG","price",today()-5, today(),"daily")'
처럼 필수 인수에 기간과 간격을 선택적으로 넣어줄 수도 있습니다.
1) Ticker
: 고려할 유가증권의 시세 표시를 뜻합니다. 구글은 "GOOG"로, 코카콜라는 "KO"로 표시하는 것처럼, 주식 간의 구분을 용이하게 하기 위한 약어 표시라고 생각하면 됩니다. 다만, Googlefinace 함수의 사용을 위해서는 "거래소 약자"와 "주식 약자"를 모두 사용해주는 것이 좋으며, 만약 거래소 약자를 명시해주지 않는 경우에는 Google Finanace에서 적합하다 판단되는 거래소를 임의로 판단하기에 거래소 약자는 적어주는 게 좋습니다.
=googlefinance("GOOG","price")
=googlefinance("NASDAQ:GOOG","price")
예시로, Google은 NASDAQ에 상장되어 있으므로, Ticker를 입력할 때 "GOOG"에 거래소 약자를 추가한 "NASDAQ:GOOG"로 표시해주는 것이 Data의 신뢰도가 높으며, 국내 주식의 경우도 마찬가지로 'KOSDAQ', 'KOSPI' 등을 명시해주어야 합니다.
2) [Attribute]
: 가져올 Data의 속성을 뜻하며, 기본값은 "price"이지만, 필요에 따라 다음 List의 값들을 넣어줄 수도 있습니다.
3) [Start_date] (선택사항)
: 과거 데이터를 가져올 기간의 시작일입니다. 만약, 함수 내에 시작일만 적어주고 End_date를 적어주지 않는다면 시작일 하루의 데이터만을 가져옵니다.
4) [End_date|num_days] (선택사항)
: 과거 데이터를 가져올 기간의 종료일을 명시해주는 것으로, 혹은 데이터를 추출할 시작일부터의 일수를 적어줍니다.
5) [interval] (선택사항)
: 데이터의 반환 간격을 뜻합니다. 예시로 'Daily' 혹은 '1'을 적어준다면 하루 간격인 일봉을 추출해주며, 'Weekly' 혹은 '7'을 적어주면 주 간격인 주봉을 추출해줍니다. 다만, Googlefinance 함수에서는 일 간격과, 주 간격만을 제공하므로 "Daily", "Weekly","1", "7" 중 하나만을 넣어주어야 합니다.
3. Googlefinance 함수의 사용 예시
1) 삼성전자 30일 일봉 추출
=googlefinance("KRX:005930", "price", today()-30, today(), "daily")
: 위의 함수를 구글시트의 셀에 입력해줌으로써 간단히 삼성전자의 30일 종가를 추출해낼 수 있습니다. 이때 삼성전자의 Ticker는 '네이버 금융'이나 혹은 핸드폰에 깔려있는 MTS에서도 해당 주식을 검색하는 것 만으로도 손쉽게 확인해줄 수 있으며, 국내 주식의 경우 KOSPI는 "KRX:"를, KOSDAQ은 "KOSDAQ:"을 주식 약자 앞에 붙여주어야 합니다.
1) Ticker : KRX:005930
2) Attribute : Price
3) Start_date : today() - 30
4) End_date : today()
5) Interval : daily
2) 삼성전자 30일 시가, 저가, 고가, 종가, 거래량 추출
=googlefinance("KRX:005930", "all", today()-30, today(), "daily")
: 이전의 예시에서 'Attribute' 값 만을 "price"에서 "all"로 바꿔 줌으로써, 삼성전자의 30일 시가, 저가, 고가, 종가, 거래량을 Daily로 추출해낸 예시입니다. 이렇듯, Googlefinance 함수는 내가 보고자 하는 주식의 Ticker를 직접 찾아줘야하는 약간의 불편함만 따를 뿐, 함수의 사용은 어렵지 않습니다.
그리고 만약 Ticker 정보들을 매번 일일이 찾아보기가 귀찮다면, '한국거래소'에서 Ticker 정보들을 전부 다운로드 한 후 시트 하나를 추가해 붙여넣어놓고 필요할 때 마다 'vlookup' 함수로 끌어다쓰는 식으로 하면 됩니다. 저 또한 현재 이와 같은 방식으로 Ticker 정보들을 붙여주고 있으며, 이에 대한 설명은 다음 포스팅에서 다루어보도록 하겠습니다.
1) Ticker : KRX:005930
2) Attribute : all
3) Start_date : today() - 30
4) End_date : today()
5) Interval : daily
[구글시트 강의] 5강. Sparkline 함수를 이용한 주식 차트 만들기 (Visualization)
Working Egg ・ 2021. 6. 26. 17:43
#구글 #구글시트 #스프레드시트 #Sparkline #국내주식 #미국주식 #주식 #차트 #Chart #주식공부 #쉬운주식 #포트폴리오 #가시화 #Visualization #서이추 #서로이웃 #이웃환영
1. Sparkline 함수
(1) Sparkline 함수란?
: 구글시트에서 Sparkline 함수는 단일 셀 내에 포함된 차트를 만들어주는 함수입니다. 쉽게 말해, Sparkline 함수는 내가 입력해준 Data를, 내가 입력해준 형식에 맞게 차트를 그려주는 함수입니다.
물론 Sparkline 함수는 단순히 Input Data를 Visualization 해주는 함수이지만, 여기서는 이전 포스팅에서 다루었던 "Googlefinance" 함수와 접목하여 Price를 차트화하는 데 활용하는 방법을 소개해보려고 합니다.
https://blog.naver.com/tmdfo2005/222410881405
[구글시트 강의] 4강. Googlefinance 함수를 이용한 주가 정보 가져오기 (크롤링)
#구글시트 #스프레드시트 #구글 #함수 #주식 #국내주식 #미국주식 #googlefinance #서이추 #서로이웃 #이우...
blog.naver.com
(2) Sparkline 함수의 입력 형식
=Sparkline(Data, [Option])
1) Data
: 차트화할 Data를 명시해주는 것으로, 입력해줄 Data의 범위 또는 배열을 입력해줍니다.
=SPARKLINE(GOOGLEFINANCE("KRX:005930", "price", today()-30, today()))
- Data : GOOGLEFINANCE("KRX:005930", "price", today()-30, today())
- Option : X (선택사항으로 미입력 가능)
여기서 Data에는 'Googlefinance 함수'를 넣어주었는데, 여기에 함수가 아닌 숫자 값을 넣어주어도 상관 없습니다. 다만, Input Data에 일반 값이나 함수가 아닌, 웹 크롤링 함수인 'Googlefinance 함수'를 넣어줌으로써 매일 실시간으로 값이 변하기에 차트 또한 실시간 Update가 되는 장점이 있습니다.
2) [Option] (선택사항)
: 차트의 종류나 색깔, 축의 범위 등 사용자에 맞게 맞춤설정할 수 있는 선택적 설정 값입니다. 이는 선택사항으로 입력하지 않을 시에는 기본값인 'Line Chart'로 선택됩니다. 이에 대한 선택 옵션들은 다음과 같으며, 범위를 지정하는 경우에는 Option을 (Option, Option이 설정된 값)인 두 개의 셀로 구성해야 합니다.
2. Sparkline 함수 사용 예시
1) 함수 구현 예시
: 다음은 'Googlefinance 함수'의 구현을 위한 일부 국내 주식들의 Ticker이며, 이때 다음과 같이 'Sparkline 함수'를 접목함으로써 차트화할 수 있습니다.
=sparkline(googlefinance(D3,"price", today()-30, today()))
: "googlefinance(D3,"price", today()-30, today())"를 통해 각 주식들의 30일 일봉 Data를 추출하였고, 이때 "=Sparkline(Data)" 함수를 이용하여 이 Data들을 Visualization 해준 모습입니다. 다만, 단일 셀 내에 Chart를 그려주는 Sparkline 함수의 특성상, 작은 셀 내에 차트를 그려주니 답답하게 보입니다. 이는 Sparkline 함수가 들어간 셀의 크기를 키워주는 것만으로 해결할 수 있습니다.
2) 셀의 행열 크기 조절
: 이는 위에서와 달리 Sparkline 함수가 들어간 셀의 열 크기를 '124 에서 300'으로, 행 크기를 '21 에서 80'으로 키워준 예시입니다. 이와 같이 셀의 행과 열 크기를 바꿔주는 것만으로도, 위에서와 다르게 조금은 차트 다운 모습을 보입니다.
3) 셀의 텍스트 색상 조절
: Chart의 색상은 Option에 'Green' 같이 색상의 이름이나, 혹은 '3D3D3D' 같이 색상에 해당하는 16진수 코드를 넣어주어야 하지만, 위와 같이 Line Chart의 경우는 예외적으로 셀의 글꼴 색깔을 변경해주는 것 만으로도 Chart의 색상을 바꿀 수가 있습니다.
[구글시트 강의] 6강. imporhtml 함수를 이용하여 홈페이지의 표와 리스트 Data 가져오기 (크롤링)
Working Egg ・ 2021. 6. 30. 23:18
#구글 #구글시트 #스프레드시트 #구글시트강의 #쉬운구글시트 #importhtml #홈페이지 #html #표가져오기 #크롤링 #서이추 #서로이웃 #이웃환영
1. importhtml 함수
(1) importhtml 함수란?
: 구글시트에서 importhtml 함수는 HTML 페이지에서 표(table) 혹은 목록(list)에 있는 데이터를 가져와주는 기능을 합니다.
빠른 이해를 위해 예시를 먼저 보여드리자면,
https://finance.naver.com/item/main.nhn?code=005930
삼성전자 - 네이버 금융 : 네이버 금융
관심종목의 실시간 주가를 가장 빠르게 확인하는 곳
finance.naver.com
함수 : =IMPORTHTML("https://finance.naver.com/item/main.nhn?code=005930","table",4)
1) 다음과 같이 importhtml 함수를 이용하여
2) 네이버 금융 URL에서
3) 삼성전자의 주요재무정보 table을
4) 스프레드시트에 가져올 수 있습니다.
(2) importhtml 함수의 입력 형식
=importhtml(URL, Query, Index)
1) URL
: Data를 가져올 페이지의 URL입니다. 이때 프로토콜(ex: http//)을 포함해주어야 합니다.
쉽게 말해, 가져올 페이지의 URL을 복사 후 붙여넣기 해주시면 됩니다.
2) Query
: 원하는 Data의 구조를 명시해주는 것으로, "table" 혹은 "list"를 넣어줍니다.
가져오길 원하는 Data가 표 형식이면 "table"을
혹은 원하는 Data가 리스트 형식이면 "list"를
입력해주면 됩니다.
3) Index
: HTML 소스에 정의된 표 혹은 목록 중 반환할 Data를 확인하는 색인 값으로, "1"부터 시작합니다.
ex) 가져오길 원하는 Data가 3번째 table이면, =importhtml("URL", "table", 3)을
가져오길 원하는 Data가 2번째 list라면, =importhtml("URL", "list", 2)를
입력해주면 됩니다.
2. importhtml 함수의 사용 예시
(1) 삼성전자의 주요재무정보 가져오기
: 위에서 보여주었던 예시를, 조금 더 상세하게 보도록 하겠습니다.
함수 : =IMPORTHTML("https://finance.naver.com/item/main.nhn?code=005930","table",4)
: 우선, 기능 구현을 위한 함수입니다.
=importhtml(URL, Query, Index)
1) URL : "https://finance.naver.com/item/main.nhn?code=005930"
2) Query : "table"
3) Index : 4
1) URL : "https://finance.naver.com/item/main.nhn?code=005930"
: Data가 있는 페이지의 URL을 복사해서 붙여넣어줍니다.
※ 네이버 금융 URL 형식
: 이때, URL인 "https://finance.naver.com/item/main.nhn?code=005930"에 삼성전자의 Ticker인 "005930"이 들어간 것을 확인할 수 있는데, 이를 통해 네이버 금융의 URL이 어떤 형식으로 이루어져있는 지를 알 수 있으며 추후 이를 활용하여 포트폴리오를 작성하는 방법을 포스팅 하겠습니다.
2) Query : "table"
: 가져오길 원하는 Data의 구조가 'table' 형식이므로 "table"을 입력해줍니다.
3) Index : 4
: HTML 내에서 해당 Data의 색인 값이며, 여기서는 "4"를 입력해주었습니다.
4) 함수 결과
: 다음과 같이 importhtml 함수를 이용해 삼성전자의 주요재무정보 table을 무사히 가져왔음을 확인할 수 있습니다.
Google Finance에서 현재 또는 과거 증권 정보를 가져옵니다.
샘플 사용법
GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")
GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-30,TODAY())
GOOGLEFINANCE(A2,A3)
통사론
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
ticker- 고려해야 할 증권의 종목코드입니다. 정확한 결과를 얻고 불일치를 방지하려면 거래소 기호와 종목 코드를 모두 사용해야 합니다 . 예를 들어 'GOOG' 대신 'NASDAQ:GOOG'를 사용하세요.
교환 기호가 지정되지 않은 경우 GOOGLEFINANCE최선의 판단을 통해 귀하를 위해 교환 기호를 선택해 드립니다.
Reuters 기기 코드는 더 이상 지원되지 않습니다. 티커 123.TO 또는 XYZ.AX 대신 TSE:123 또는 ASX:XYZ를 사용하세요.
현재 모든 future가 지원되는 것은 아닙니다.
attribute- [ 선택사항 - "price"기본적으로 ] - Google Finance에서 가져올 속성 ticker이며 날짜가 지정된 경우 필수입니다.
attribute실시간 데이터의 경우 다음 중 하나입니다.
"price"- 실시간 가격 견적, 최대 20분 지연.
"priceopen"- 오픈 당시 가격입니다.
"high"- 당일 최고가입니다.
"low"- 당일 최저가입니다.
"volume"- 당일 거래량입니다.
"marketcap"- 주식의 시가총액입니다.
"tradetime"- 마지막 거래 시간.
"datadelay"- 실시간 데이터가 얼마나 지연되는지.
"volumeavg"- 일일 평균 거래량입니다.
"pe"- 주가수익률.
"eps"- 주당순이익입니다.
"high52"- 52주 최고가입니다.
"low52"- 52주 최저가입니다.
"change"- 전 거래일 마감 이후의 가격 변동입니다.
"beta"- 베타 값입니다.
"changepct"- 전 거래일 마감 이후 가격 변동률입니다.
"closeyest"- 전일 종가입니다.
"shares"- 발행주식수.
"currency"- 유가증권의 가격이 책정되는 통화입니다. 통화에는 거래 창이 없으므로 이 인수에 대해 open, low, high및가 volume반환되지 않습니다.
attribute과거 데이터의 경우 다음 중 하나입니다.
"open"- 지정된 날짜의 시가입니다.
"close"- 특정 날짜의 종가입니다.
"high"- 특정 날짜의 최고 가격입니다.
"low"- 특정 날짜의 최저가입니다.
"volume"- 지정된 날짜의 거래량입니다.
"all"- 무엇보다도.
attribute뮤추얼 펀드 데이터에 대한 다음 중 하나입니다.
"closeyest"- 전일 종가입니다.
"date"- 순자산가치가 보고된 날짜입니다.
"returnytd"- 연초부터 현재까지의 수익률입니다.
"netassets"- 순자산.
"change"- 가장 최근에 신고된 순자산가치와 직전 순자산가치의 변동입니다.
"changepct"- 순자산가치의 변화율입니다.
"yieldpct"- 분배수익률은 직전 12개월간 소득분배(주식배당, 채권이자지급)와 순자산가치차익을 합산한 금액을 전월 순자산가치수로 나눈 값입니다.
"returnday"- 1일 총 반환.
"return1"- 1주일 동안 총 수익을 얻을 수 있습니다.
"return4"- 4주 총 수익입니다.
"return13"- 13주 총 수익입니다.
"return52"- 52주(연간) 총 수익입니다.
"return156"- 156주(3년) 총수익률.
"return260"- 260주(5년) 총수익률.
"incomedividend"- 최근 현금배분 금액입니다.
"incomedividenddate"- 가장 최근에 현금을 배분한 날짜입니다.
"capitalgain"- 최근 자본이득 배분금액.
"morningstarrating"- 모닝스타의 "별" 등급.
"expenseratio"- 펀드의 비용비율.
start_date- [ 선택사항 ] - 이전 데이터를 가져올 때 시작 날짜입니다.
- start_date가 지정되었지만 지정되지 않은 경우 end_date|num_days해당 날짜의 데이터만 반환됩니다.
end_date|num_days- [ 선택사항 ]start_date - 과거 데이터를 가져올 때의 종료일 또는 데이터를 반환할 일수입니다 .
interval- [ 선택사항 ] - 반환된 데이터의 빈도입니다. "매일" 또는 "매주"입니다.
- interval1또는 로 지정할 수도 있습니다 7. 다른 숫자 값은 허용되지 않습니다.
노트
사용 제한: 해당 데이터는 금융 업계 전문가가 사용하거나 비금융 회사(정부 기관 포함)의 다른 전문가가 사용할 수 없습니다. 전문적인 용도로 사용하려면 제3자 데이터 제공업체로부터 추가 라이센스 비용이 부과될 수 있습니다.
모든 매개변수는 따옴표로 묶이거나 텍스트가 포함된 셀에 대한 참조여야 합니다.
중요사항:interval 가 숫자로 지정되는 경우와 가 end_date|num_days일 수로 지정되는 경우 는 예외일 수 있습니다 .실시간 결과는 단일 셀 내의 값으로 반환됩니다. 하루 동안의 기록 데이터라도 열 헤더가 포함된 확장된 배열로 반환됩니다.
일부 속성은 모든 기호에 대한 결과를 산출하지 못할 수도 있습니다.
- 날짜 매개변수가 지정된 경우 요청은 기록으로 간주되며 기록 속성만 허용됩니다.
GOOGLEFINANCE영어로만 제공되며 대부분의 국제 교류를 지원하지 않습니다.
이전 데이터는 Sheets API 또는 Apps Script를 통해 다운로드하거나 액세스할 수 없습니다. 그렇게 하려고 하면 스프레드시트의 해당 셀에 있는 값 대신 #N/A 오류가 표시됩니다.
견적은 모든 시장에서 제공되지 않으며 최대 20분까지 지연될 수 있습니다. 정보는 '있는 그대로' 제공되며 거래 목적이나 조언이 아닌 정보 제공 목적으로만 제공됩니다.
Google은 전달된 날짜를 GOOGLEFINANCE 정오 UTC 시간으로 처리합니다. 해당 시간 이전에 마감되는 거래소는 하루씩 늦어질 수 있습니다.
예
중요: 각 예는 자체 탭에 있습니다.
일반적인 사용법
Google Finance에서 시장 정보를 검색합니다.
공통 속성
과거 시장 데이터
Google Finance에서 지정된 날짜를 기준으로 과거 시장 정보를 검색합니다.
뮤추얼 펀드
뮤추얼 펀드의 공통 속성.
환율 동향
의 검색 결과 반환을 사용하여 지난 30일 동안의 환율 추세를 표시하는 셀 내부에 차트를 만듭니다 GoogleFinance.