[데이터 분석 2] TR 데이터를 받아 그래프로 그려보자 (구글 시트 편)

지난 글에서 구글 시트에서 GOOGLEFINANCE() 함수를 이용하여 주가 데이터를 불러오고 로그 스케일(log scale) 그래프를 그려 보았습니다. 얼핏 보기에는 이렇게 계속 진행하면 될 듯 하지만, 큰 문제가 있습니다. GOOGLEFINANCE() 함수로 가져오는 데이터는 배당이 고려되지 있지 않기 때문입니다. 지난 글: [데이터 분석] 주가 데이터를 불러와서 그래프로 그려보자 (구글 시트 편)

모든 자산의 가격은 배당 재투자 가격(TR; Total Return)으로 비교해야 합니다. 배당률이 높으면 자산의 가치가 저평가되는 문제를 보완하기 위해서입니다. 아쉽게도 구글 파이낸스는 배당이 고려되지 않은 PR(Price Return) 데이터만 제공합니다. 참고: [기초 1] 장기 수익과 배당 재투자 (PR과 TR)

TR로 자산 가격을 얻기 위해서는 구글 파이낸스가 아닌 다른 데이터 소스를 이용해야 합니다. 해외 주식의 경우 야후 파이낸스가 그나마 무난한 편입니다. 참고: 야후 파이낸스라고 해서 모든 데이터가 깨끗하게 잘 정리되어 있지 않습니다. 각종 문제가 내포되어 있거나, 없던 문제가 발생하는 경우가 있으니, 경우에 따라서는 투자자가 신경을 써야 할 수 있습니다. 야후 파이낸스 수정 주가 오류 (일부 분배/배당 내역 누락으로 인한 부정확한 TR값)

참고: 국내 종목은 정식 연재에서는 다루지 않습니다. 부록에서 언급할 수 있습니다. 국내 개별 종목은 배당이 고려된 가격을 얻는 것이 현실적으로 어렵습니다. 미국의 경우 개별 종목이라도 배당이 먼저 공지됩니다. 국내 개별 종목은 배당락이 먼저 발생하고, 얼마를 배당할지는 몇 개월 후 주주총회에서 결정됩니다. 이 때문에 무료로 구할 수 있는 대개의 국내 개별 종목 데이터는 배당이 고려되어 있지 않습니다. 국내 상장 ETF만 유일한 예외로 배당이 먼저 공지되기에 TR 가격을 얻을 수 있습니다. 여기에 미국과 한국의 거래일수가 다르고, 환율까지 고려되어야 하기에 정식 연재에서는 국내 종목을 소개하거나 비교하지 않습니다.

참고: 이 연재는 책 <왜 위험한 주식에 투자하라는 걸까? - 장기 투자와 분산 투자에 대한 통계학적 시각>에서 소개한 주요 분석 방법을 구글 시트로 적용해 보는 부록의 성격을 가집니다. 책의 초고에 해당되는 블로그 글이나 읽기 편하게 정리하고 편집한 책을 한두 번 차근차근 읽어 분석 방법의 의미를 이해한 후에 보면 더욱 좋습니다. 연재 그 자체만으로도 따라 하기에 별 무리가 없는 수준으로 설명하려고 하지만, 세부적인 설명이 일부 생략되어 있을 수 있고, 분석 방법의 의의를 이해한 상태에서 보면 좀 더 도움이 될 수 있기 때문입니다.

주의: 이 글은 특정 상품 또는 특정 전략에 대한 추천의 의도가 없습니다. 이 글에서 제시하는 수치는 과거에 그랬다는 기록이지, 앞으로도 그럴 거라는 예상이 아닙니다. 분석 대상, 기간, 방법에 따라 전혀 다른 결과가 나올 수 있습니다. 데이터 수집, 가공, 해석 단계에서 의도하지 않은 오류가 있을 수 있습니다. 일부 설명은 편의상 현재형으로 기술하지만, 데이터 분석에 대한 설명은 모두 과거형으로 이해해야 합니다.

TR 데이터 구하기

야후 파이낸스에서 SPY에 대한 과거 데이터(Historical Data)를 살펴보면, 구글 파이낸스와는 달리 칼럼 하나가 더 있는 것을 알 수 있습니다.

야후 파이낸스에서 SPY의 과거 데이터

Close 항목 옆에 Adj Close 항목이 있습니다. Close와 Adj Close 모두 넓은 의미에서 수정 주가입니다. 수정 주가는 주식의 병합 또는 분할을 고려해서 가격을 재산정한 것입니다. 삼성전자는 2018년 5월에 50 : 1로 액면 분할을 했습니다. 1주당 250만원 정도였던 주가는 1주당 5만원으로 낮아졌습니다. 삼성전자 구주 1주를 가지고 있던 투자자는 신주 50주로 교체 발급받았습니다. 주가가 1 / 50로 낮아진 것을 반영하여, 이전 가격을 소급해서 재조정한 주가가 수정 주가입니다.

Adj Close는 배당 고려 수정 주가입니다. 배당금을 받으면, 즉시 모두 재투자했다고 가정하고 주가를 재산정한 것입니다. 금융 자산의 가격은 배당을 고려하지 않으면, 착시가 발생할 수 있기에, 특별한 이유가 없으면 모두 TR로 비교해야 합니다. 참고: 액면 병합과 분할만 고려한 경우를 수정 주가라 부르기도 하고, 배당 재투자를 한 경우를 수정 주가라 부르기도 합니다. 

야후 파이낸스에서는 과거 데이터를 CSV 파일로 다운로드할 수 있었습니다. 최근 정책이 바뀌었는지 유료 멤버십에 가입하지 않으면 다운로드할 수 없게 되었습니다. 참고: 야후 파이낸스 도움말

연재 초반부터 예상치 못한 난관에 부딪쳐서 난감합니다. 인베스팅닷컴(investing.com) 데이터도 살펴보았는데, TR 가격이 아닙니다. 제가 사용하는 미래에셋증권의 HTS(Home Trading System) 경우 해외주식차트 화면에서 PR 또는 TR 가격을 받을 수 있습니다. 최대 2,000거래일까지 대략 8년치를 한 번에 받을 수 있습니다. 마지막 날짜는 원하는 날짜로 지정하고, 여러 번 나누어서 받아 병합해야 합니다. 차트 위에 150이라는 숫자가 적혀있는 입력창이 있는데, 이를 2,000으로 설정하면 2,000거래일로 데이터를 받을 수 있습니다.

미래에셋증권 HTS의 종목 차트 화면에서 오른쪽 마우스 버튼 > 보기 > 엑셀로 저장을 하면 NC2라는 확장명으로 파일이 저장됩니다. 엑셀이 없는 분은 확장자를 CSV로 바꾸면 다른 프로그램에서도 읽을 수 있습니다. 차트 설정에서 수정 주가 항목을 선택해야 배당 고려 수정 주가가로 그래프가 표시되고 TR로 데이터를 받을 수 있습니다. 미래에셋증권 HTS는 화면 캡처를 지원하지 않기에 다운로드하는 화면은 생략합니다. 아래는 2,000거래일에 대해 SPY의 PR과 TR 가격을 각각 다운로드하여 구글 시트에 종가만 복사한 결과입니다.

미래에셋증권 HTS를 이용하여 받은 SPY의 8년치 PR 및 TR 종가

참고: 증권사마다 제공하는 데이터와 데이터 기간이 다를 수 있으니 구체적인 방법을 확인할 필요가 있습니다. 개인적으로는 파이썬(Python)에 관련 라이브러리를 이용하여 PR과 TR 데이터를 구합니다. 이 연재에서는 파이썬을 이용하는 방법을 소개하지 않기에, HTS를 이용하는 방법으로 설명했습니다. 각자 자신이 취할 수 있는 여러 방법을 알아보고, 효율적인 방법으로 TR 데이터를 구할 필요가 있습니다.

PR과 TR 그래프

SPY의 PR과 TR 데이터를 구했으니, 그래프에 함께 나타낼 수 있습니다. 로그 스케일(log scale)로 나타냈습니다.

SPY의 PR과 TR

TR은 배당 재투자가 고려되어 있기에, PR보다 과거 가격이 낮아 보입니다. 이를 보정하기 위해 데이터를 날짜 기준 오름차순으로 정렬하고, 초기 가격 대비 자산비로 나타냅니다. 자산비는 수익률 + 1입니다. 참고:  [기초 2] 수익률, 로그 스케일, 그리고 손익 비대칭성

SPY의 자산비 (PR과 TR)

왼쪽은 날짜를 기준으로 오름차순으로 정렬하고, 자산비를 구하기 위해 종가 / 첫 번째 종가 수식을 사용하였습니다. 수식을 보면 =B2/B$2라고 되어 있습니다. B$2는 첫 번째 날짜의 종가(PR)입니다. 입력한 수식을 복사해서 붙이면 됩니다.

오른쪽은 자산비를 이용하여 로그 스케일 그래프로 나타낸 것입니다. 구글 시트가 불편한 점 중에 하나는 로그 스케일로 세로축을 지정하면 세부 조정이 어렵다는 점입니다. 예를 들어 다음 그래프와 같이 로그 스케일을 사용하지만, 자산비 대신 인식하기 쉬운 수익률로 표현하면서 원하는 간격 또는 지점에 세로축선을 지정할 수 없습니다. 해당 그래프는 파이썬으로 만들었습니다.

SCHD와 SPY의 누적 수익률 (환헤지, 환노출)

(추가) 야후 파이낸스 웹페이지 데이터를 복사해서 사용하기

블로그 구독자이신 크바시르님께서 알려주신 방법을 추가합니다. 야후 파이낸스의 특정 종목에 대한 과거 데이터 페이지의 내용을 구글 시트로 복사해서 사용하는 방법입니다. 데이터 양이 많을 때는 시간이 오래 걸릴 수 있으니, 적절한 기간으로 나누어서 여러 번 반복해서 병합하는 게 편리할 수 있습니다.

다음은 야후 파이낸스에서 SPY의 과거 데이터를 2020년부터 2024년까지 5년치 표시한 화면입니다. Ctrl+A로 전체를 선택하거나, 표 위에 있는 "Currency in USD"부터 테이블을 조금 포함하도록 선택해서 복사해서 구글 시트에 붙입니다. 예제에서는 전체 화면을 선택해서 붙였습니다.

야후 파이낸스에서 SPY 화면을 복사해서 구글 시트에 붙인 결과

표 부분만 남기고 나머지는 제거합니다. 표에 배당금이 표시된 열이 있습니다. 병합되어 있는 셀입니다. 이 셀은 선택하고 Ctrl+A로 전체 테이블을 선택하면 모든 병합을 한꺼번에 풀 수 있습니다.

병합된 셀 선택후 모든 병합 풀기

데이터 필터를 걸고, 고가(High)가 비어있는 행만 선택합니다.

필터를 걸고 고가(High)에 대해 공백인 경우만 보기

필터 된 행은 모두 배당금 관련 행입니다. 모두 선택해서 지우고 필터를 풉니다.

배당금 행을 모두 제거하고 필터를 삭제한 결과

구글 시트는 Dec 30, 2024와 같은 날짜 형식을 제대로 인식하지 못합니다. 문자열로 인식합니다. 마이크로소프트 엑셀은 제대로 인식하니 날짜를 엑셀로 복사해서 날짜 형식을 바꾼 후에 다시 구글 시트로 복사하면 됩니다. 처음부터 엑셀에 야후 파이낸스 데이터를 붙여서 처리하는 것이 더 간편할 듯합니다.

엑셀을 이용하여 날짜 형식을 변환하는 예

도움 주신 크바시르님께 감사드립니다.

정리하며

구글 시트에서 GOOGLEFINANCE() 함수로 얻을 수 있는 종목 가격 데이터는 배당 재투자가 고려되지 않은 PR 가격입니다. 긴 기간의 데이터에 대해 합리적으로 자산의 성과를 비교 분석하려면 TR 가격을 이용해야 합니다. 야후 파이낸스는 PR과 TR 가격 모두 제공하지만, 아쉽게도 이제는 멤버십에 가입해야 다운로드가 가능합니다. 이 글에서는 대안으로 증권사 HTS를 이용하여 PR과 TR 가격을 얻는 사례를 보였습니다. 각자 자신이 사용하는 시스템이나 환경을 고려해서 TR 가격을 구할 수 있는 방법을 찾아 두어야 합니다.

PR과 TR 데이터를 이용해서 그래프를 그려보면, TR의 의미가 무엇인지 확연히 알 수 있습니다. 8년 정도의 기간 동안 SPY의 TR과 PR 간 수익률 차이는 38.7%였습니다.

이어지는 글: [데이터 분석 3] 주가 데이터에서 수익률 통계량을 뽑아보자 (구글 시트 편)

목록: 자산 배분 분석 방법과 사례 글 모음 [목록] (순서대로 차근차근 읽기를 권합니다)

함께 읽으면 좋은 글:

• [데이터 분석 1] 주가 데이터를 불러와서 그래프로 그려보자 (구글 시트 편)
• [자산 배분] QQQ + SOXX(미국 반도체 ETF) + 환율 (평균-분산 그래프 분석)
• [자산 배분] VUG(미국 성장주 ETF) + VTV(미국 가치주 ETF) + 환율 (평균-분산 그래프 분석)
• <왜 위험한 주식에 투자하라는 걸까? - 장기 투자와 분산 투자에 대한 통계학적 시각> 출간에 부쳐 (샘플북 포함)
• [자산 배분] SPY + EWJ(일본 대형주 ETF) + 환율 (평균-분산 그래프 분석)
• S&P 500 국내 ETF는 무엇이 좋을까? (국내 상장 ETF 9종 비교와 분석)
• 나스닥 100 국내 ETF는 무엇이 좋을까? (국내 상장 ETF 5종 비교와 분석)
• [자산 배분] SCHD + QQQ + 환율 (평균-분산 그래프 분석)
• QQQ5(QQQ 5배 레버리지)는 1년간 얼마나 녹았을까?
• 외화 RP는 위험한가? (증권사가 RP를 운용하는 방법)