구글 스프레드시트 주식 배당 관리 - 1. 시작하기 (2022년 기준) | 구글 파이낸스 | 현재가 | 배당 | 차트 넣기 | 환율 | 섹터 | 베타 | 상관계수

1. 구글 파이낸스 Google Finance

구글 스프레드시트는 구글 파이낸스 googlefinance 라는 기능을 제공한다.

https://support.google.com/docs/answer/3093281?hl=ko

GOOGLEFINANCE - 문서 편집기 고객센터

구글 파이낸스는 미국, 우리 나라를 포함한 세계 각국의 주식 시황 및 관련 정보를 구글 스프레드시트로 불러들일 수 있는 기능이다. 위의 링크에 들어가 보면 구글 파이낸스를 통해 다양한 정보를 시트로 불러들일 수 있다는 것을 알 수 있다. 한 가지 아쉬운 점은 최대 20분가량의 시차가 있다는 것이다. 그래서 구글 스프레드시트에 구글 파이낸스 함수를 추가하면 시트의 아래쪽에 "증시가격은 모든 주식 시장의 증시가격을 표시하는 것은 아니며 최대 20분 전 정보임을 참고하시기 바랍니다. 정보는 '있는 그대로' 제공되며 거래 목적이나 조언이 아닌 정보 제공 목적으로만 제공됩니다"라는 메시지가 명시된다. 구글 스프레드시트를 실시간 매매에 활용하는 것이 아니라 포트폴리오 관리, 그중에서도 배당주 관리에 사용한다면 20분의 지연은 큰 문제는 되지 않으리라 생각한다.

 

2. 현재가 불러오기

일단 시작은 매우 간단하다. 새로운 구글 스프레드시트를 하나 연다. 코스피와 코스닥에 상장된 종목에는 숫자로 이루어진 종목코드가 부여된다. 미국 주식의 경우 티커 ticker 라는 이름이 붙는다. 예를 들어 애플의 티커는 AAPL 이다. 구글 파이낸스는 기업명이 아니라 티커를 인식한다.

티커에 AAPL을 넣었다. 티커는 가급적이면 대문자로 넣는 것이 좋다. 구글 파이낸스는 대소문자를 가리지 않고 인식하긴 하지만 구글 파이낸스 외의 사이트에서 데이터를 가져올 때 티커를 소문자로 넣으면 오류가 나는 경우가 있다. 

 

그리고 현재가 셀에

=GOOGLEFINANCE(A2, "price")

를 넣는다. A2는 티커가 들어있는 셀의 주소이다.

그럼 이렇게 애플의 현재가, 실제로는 20분 정도 지연된 시세, 가 표시된다.

전일 종가에 비해 주식의 가격이 얼마나 오르거나 떨어졌는지도 표시할 수 있고, 그것을 %로 표현할 수도 있다.

가격 변화를 표시하는 함수는

=GOOGLEFINANCE(A2, "change")

이다.

변화율을 표시하는 함수는

=GOOGLEFINANCE(A2, "changepct")

이다.

 

 

 

3. 배당금 정보 불러들이기 : 핀비즈, 야후 파이낸스, dividendinformation.com

(1) 2022년 1월 기준 핀비즈에서 배당금 정보 불러들이기

그런데 아쉽게도 구글 파이낸스는 배당금에 대한 정보를 제공하지 않는다. 그래서 배당금에 대한 정보는 핀비즈 finviz에서 긁어와야 한다. 핀비즈에서 데이터를 추출하는 방법에 대한 블로그나 유튜브는 참고할만한 것이 많다. 과정은 생략하고 여기 쓰이는 함수는 다음과 같다.

=INDEX(IMPORTHTML("https://finviz.com/quote.ashx?t="&A2,"Table",7),7,2)

위의 식에서 "Table",7 이라는 것은 핀비즈에서 셀 A2에 들어있는 티커, 여기서는 AAPL, 의 페이지를 찾고, 그 페이지에 있는 여러 개의 표 중 7번을 찾으라는 의미이다.

https://finviz.com/quote.ashx?t=aapl 에 들어가보자.

AAPL Apple Inc. Stock Quote

 

페이지 중간 정도에 위의 표가 보인다. 그중 배당 dividend 는 7행 2열에 들어있다.

=INDEX(IMPORTHTML("https://finviz.com/quote.ashx?t="&A2,"Table",7),7,2)

위의 언급된 함수를 다시 보자. 이 함수는 핀비즈의 해당 종목 페이지에 들어가서 표 7의 7행 2열의 데이터를 가져오라는 의미이다.

그런데 2021년까지는 위의 테이블의 번호가 8번이었는데, 2022년 들어 7번으로 바뀌었다. 그래서 작년을 기준으로 만들어진 유튜브 영상을 보고 따라 하다 보면 배당금 정보가 제대로 표시되지 않을 수 있다.

위의 함수를 넣으면 0.88이라는 값이 표시는 된다. 그런데 앞뒤로 *가 붙어서 썩 보기가 좋지 않다.

그래서 위의 함수 대신

=SUBSTITUTE(INDEX(IMPORTHTML("https://finviz.com/quote.ashx?t="&A2,"Table",7),7,2),"*","")

를 넣는다. 이 함수는 "*"을 ""로 대체하라는 명령, 즉 *을 지우라는 명령이다. 그렇게 해보면

이렇게 보다 깔끔한 표를 얻을 수 있다. 다른 종목의 티커도 한 번 넣어보자.

이렇게 배당금이 표시된다.

 

(2) 2022년 3월 29일 기준 핀비즈 배당금 정보 불러오기

그런데, 2022년 3월 29일 기준으로 핀비즈에서 제공되는 종목 별 배당금 정보의 경로가 또 바뀌었다. 아래 링크를 참고하면 함수를 수정하여 배당금 정보를 불러올 수 있다.

구글 스프레드시트 주식 배당 관리 - 19. 2022년 3월 29일 기준 핀비즈 Finviz 배당 불러오기 업데이트

 

(3) 야후 파이낸스 Yahoo Finance 에서 배당금 정보 불러오기

그리고 야후 파이낸스 Yahoo Finance 에서 배당금 정보를 가져오는 방법에 대한 별도의 포스팅도 올려두었으니 필요하신 분들께 도움이 되길 빈다. 야후 파이낸스에서 데이터를 가져올 때는 반드시 티커를 대문자로 넣어야 한다. 그렇지 않으면 오류가 발생할 수 있다.

 

구글 스프레드시트 주식 배당 관리 - 25. 야후 파이낸스 Yahoo Finance 에서 배당 정보, 배당금, 가져오기

구글 스프레드시트 주식 배당 관리 - 25. 야후 파이낸스 Yahoo Finance 에서 배당 정보, 배당금, 가져

 

 

(4) 야후 파이낸스와 핀비즈의 배당금 정보 차이에 대한 설명

야후 파이낸스와 핀비즈에서 제공하는 배당금의 정보에는 차이가 있다. 간략히 이야기하면 핀비즈의 배당금 정보는 과거 1년의 배당금 정보이고, 야후 파이낸스의 정보는 올해 예상 배당금에 대한 것이다. 이에 대한 별도의 포스팅을 올려두었으니 도움이 됐으면 한다.

 

구글 스프레드시트 주식 배당 관리 - 29. 핀비즈와 야후 파이낸스의 배당 정보가 다를 때

구글 스프레드시트 주식 배당 관리 - 29. 핀비즈와 야후 파이낸스의 배당 정보가 다를 때

 

 

(5) 야후 파이낸스와 핀비즈의 배당금 정보의 차이를 이용하는 방법

위에서 이야기한 야후 파이낸스와 핀비즈의 배당 정보의 차이를 이용하면 직전 배당과 예상 배당을 한 눈에 관리할 수 있다. 이에 대한 포스팅을 올려두었으니 도움이 됐으면 한다.

 

구글 스프레드시트 주식 배당 관리 - 32. 예상 배당, 직전 배당 불러오기

구글 스프레드시트 주식 배당 관리 - 32. 예상 배당, 직전 배당 불러오기

 

 

(6) 나의 배당수익률 구하기, 세후 배당수익률 구하기

배당주 투자에 있어서 가장 중요한 수치가 있다면 배당수익률일 것이다. 더 중요한 것은 세후 배당수익률이라고 할 수 있다. 특히 미국의 경우 배당소득세가 15% 징수되어서 배당금이 지급되기 때문에 포트폴리오를 짤 때 이를 미리 감안하는 것이 좋다.

 

세후 배당과 세후 배당수익률을 구한 예이다.

 

위의 이미지처럼 배당소득세를 차감한 배당금을 계산하고, 이를 가지고 배당수익률을 구하는 것이 실제 받게 되는 배당금의 규모를 파악하는 데 도움이 된다.

 

아래 링크는 그 방법에 대해 정리해둔 포스팅이다.

 

구글 스프레드시트 주식 배당 관리 - 12. 내 배당수익률 계산하고 수익률에 따라 색깔 넣기

구글 스프레드시트 주식 배당 관리 - 12. 내 배당수익률 계산하고 수익률에 따라 색깔 넣기

 

 

(7) 과거 10년치 배당 기록 전부 불러오기

핀비즈나 야후 파이낸스는 직전 배당 혹은 예상 배당 정보만을 제공한다. 그러나 dividendinformation.com을 이용하면 과거 10년 가량의 배당 기록을 한 번에 불러들일 수 있다.

 

과거 10년치 배당 기록을 가져온 결과이다.

 

위의 이미지처럼 과거 10년 가량의 배당 기록을 가져올 수 있다. 그리고 그 정보를 기반으로 배당별 배당성장률을 구할 수 있고, 각 연도별 배당금의 합계를 구하여 연도별 배당 성장률도 구할 수 있다.

 

자세한 것은 아래 링크를 참조하면 된다.

 

구글 스프레드 시트 주식 배당 관리 - 34. 과거 배당 기록 불러오기 | 배당성장률 계산하기 | dividendinformation 이용하기

구글 스프레드 시트 주식 배당 관리 - 34. 과거 배당 기록 불러오기 | 배당성장률 계산하기 | dividen

 

 

 

3. 국내 주식 현재가, 직전 배당, 예상 배당 정보 불러오기

위에서는 구글 파이낸스와 핀비즈, 야후 파이낸스를 이용하여 미국 주식과 관련된 정보를 시트로 불러들이는 방법을 알아보았다. 당연히 국내 주식에도 같은 내용을 적용할 수 있다. 구글 파이낸스를 이용하는 것은 동일하지만, 국내 주식의 경우 네이버 금융의 정보를 불러들인다는 것이 다르고, 그래서 함수에도 다소 차이가 있다. 아래 링크를 참고하면 손쉽게 국내 주식의 현재가와 배당금 정보를 불러들이는 방법을 시트에 구현할 수 있다. 

 

구글 스프레드시트 주식 배당 관리 - 8. 국내 주식 직전 배당, 예상 배당 관리

구글 스프레드시트 주식 배당 관리 - 8. 국내 주식 직전 배당, 예상 배당 관리

 

 

 

 

 

4. 셀 안에 차트, 그래프를 넣는 방법

(1) 셀 안에 개별 종목의 차트 넣기

구글 스프레드 시트가 제공하는 sparkline이라는 함수를 이용하면 아래 이미지처럼 셀 안에 개별 종목들의 차트를 표시할 수 있다.

 

셀 안에 개별 종목들의 차트를 넣을 수 있다.

 

아래 링크를 참조하면 위의 이미지처럼 셀 안에 차트를 넣을 수 있으니 참고로 하면 도움이 될 것이다.

 

구글 스프레드시트 주식 배당 관리 - 6. 셀 안에 차트 넣기

구글 스프레드시트 주식 배당 관리 - 6. 셀 안에 차트 넣기

 

(2) 개별 종목 혹은 섹터별 수익률을 바 그래프로 표시하기

또한 아래 이미지처럼 각 종목이나 각 섹터의 수익률을 바 그래프로 표시할 수도 있다.

 

셀 안에 각 종목의 수익률을 바 그래프로 시각화할 수 있다.

 

아래 링크를 참고하면 위의 이미지처럼 각 종목의 수익률을 일목요연하게 바 그래프로 시각화할 수 있다.

 

구글 스프레드 시트 주식 배당 관리 - 33. 셀 안에 수익률 바 그래프 넣기

구글 스프레드 시트 주식 배당 관리 - 33. 셀 안에 수익률 바 그래프 넣기

 

(3) 섹터별 혹은 종목별 매입비중을 바 그래프로 표시하고 관리하기

아래 이미지처럼 섹터별 매입 비중을 바 그래프로 시각화하여 한 눈에 파악하게 할 수도 있다.

 

각 섹터의 매입비중을 바그래프로 시각화할 수 있다.

 

 

아래 링크에 들어가면 위의 이미지처럼 섹터별 매입비중을 바 그래프로 시각화할 수 있는 방법이 정리되어 있다.

 

구글 스프레드시트 주식 배당 관리 - 7. 매입 비중을 바 그래프로 표시

구글 스프레드시트 주식 배당 관리 - 7. 매입 비중을 바 그래프로 표시

 

 

 

 

5. 종목에 대한 주요 정보 불러들이고, 계산하기

구글 스프레드 시트의 기능을 이용하면 배당 성향, 베타 등의 종목별 주요 정보를 쉽게 시트로 불러들이고 한 눈에 관리할 수 있다.

 

(1) 배당 성향 불러오기

배당주 투자에 있어 배당 성향은 종목 선택을 할 때 반드시 검토해야 하는 수치 중 하나이다. 아래 이미지처럼 구글 스프레드 시트에 각 종목들의 배당 성향을 불러들여서 관리할 수 있는 방법이 있다. 

 

배당성향을 쉽게 불러들이고 관리할 수 있다.

 

아래 링크를 확인하면 그 방법이 정리되어 있으니 도움이 됐으면 한다.

 

구글 스프레드시트 주식 배당 관리 - 16. 배당성향(Payout Ratio) 가져오기

구글 스프레드시트 주식 배당 관리 - 16. 배당성향(Payout Ratio) 가져오기

 

 

(2) 베타 가져오기

베타(Beta)는 전체 시장 대비 해당 종목의 변동성을 나타내는 지표이다. 아래 이미지처럼 베타 역시 쉽게 시트로 불러들여서 종목의 성향을 파악하고 분석하는 데 쓸 수 있다. 

각 종목별 베타는 구글 파이낸스와 핀비즈에서 모두 제공한다. 각각을 모두 불러들일 수 있다.

 

베타는 구글 파이낸스에서 기본적으로 제공하는 값이기도 하고, 핀비즈에서 불러들일 수도 있다. 아래 링크를 참조하면 두 수치 모두 쉽게 불러들일 수 있으니 도움이 됐으면 한다.

 

구글 스프레드시트 주식 배당 관리 - 17. 베타 (Beta) 가져오기

구글 스프레드시트 주식 배당 관리 - 17. 베타 (Beta) 가져오기

 

(3) 상관계수 분석하기

두 변수 - 즉, 두 개의 종목 혹은 두 개의 시장 수치 등 -  의 움직임이 얼마나 상관관계를 가지고 움직이는지를 나타내는 상관계수라는 것이 있다. 상관계수는 어디선가 불러들일 수 있는 수치는 아니며 직접 계산을 해야 한다. 그런데 이를 구글 스프레드 시트를 가지고 매우 쉽게 할 수 있다.

 

각 종목과 SPY와의 상관계수를 구한 결과이다.

 

각 종목과 특정 종목 - 위의 이미지에서는 애플 - 사이의 상관계수를 구한 결과이다.

 

위의 이미지처럼 SPY와 각 종목과의 상관계수를 구할 수도 있다. 그리고 티커만 바꿔서 어떤 종목과의 상관계수도 매우 쉽게 구할 수 있다.

 

아래 링크를 참조하면 그 방법이 정리되어 있으니 도움이 됐으면 한다.

 

구글 스프레드시트 주식 배당 관리 - 18. 상관계수(Correlation Coefficient) 1초 만에 구하기, 어떤 종목에라도 적용 가능!

구글 스프레드시트 주식 배당 관리 - 18. 상관계수(Correlation Coefficient) 1초 만에 구하기, 어떤 종목

 

 

(4) 각 종목의 섹터(sector), 서브섹터(industry) 가져오기

각 종목이 어느 섹터, 혹은 어느 서브섹터 - 영어로는 industry라고 표현한다 - 에 속하는지도 매입 비중 관리에 매우 중요하다. 아래 이미지와 같이 이 정보를 쉽게 가져오는 방법이 있다.

 

각 종목의 섹터를 쉽게 시트로 불러들일 수 있다.

 

아래 링크를 참조하면 섹터와 서브섹터를 가져오는 방법이 정리되어 있으니 도움이 됐으면 한다.

 

구글 스프레드시트 주식 배당 관리 - 24. 섹터 자동으로 가져오기

구글 스프레드시트 주식 배당 관리 - 24. 섹터 자동으로 가져오기

 

 

(5) 과거 특정 시점과 비교한 주가의 변화율 계산하기

구글 파이낸스를 이용하면 과거 특정 시점에 비해 현재 주가가 몇 %나 변했는지 손쉽게 계산할 수 있다.

 

위의 이미지는 5일 전과 비교한 주가의 변화율을 표시한 것이다.

 

위의 이미지는 180일 전과 비교한 주가의 변화율을 표시한 것이다.

 

 

위의 이미지의 E2 셀에 5를 넣으면 5일 전, 10을 넣으면 10일 전, 180을 넣으면 180일 전의 주가에 대비한 현재가의 변화를 %로 표시할 수 있다.

 

아래 링크는 그 방법에 대해 정리한 포스팅의 링크이다.

 

구글 스프레드시트 주식 배당 관리 - 21. 예전에 비해 주가가 몇 %나 변했는지 표시하기

구글 스프레드시트 주식 배당 관리 - 21. 예전에 비해 주가가 몇 %나 변했는지 표시하기