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

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

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

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

참고: 국내 종목은 정식 연재에서 다루지 않습니다. 국내 개별 종목은 배당이 고려된 가격을 얻는 것이 현실적으로 쉽지 않습니다. 미국의 경우 개별 종목이라도 배당이 먼저 공지됩니다. 국내 개별 종목은 배당락이 먼저 발생하고, 얼마를 배당할지는 몇 개월 후 주주총회에서 결정됩니다. 이 때문에 무료로 구할 수 있는 대부분의 국내 개별 종목 데이터는 배당이 고려되어 있지 않습니다. 국내 상장 ETF/ETN만 유일한 예외입니다. 이들 상품은 배당이 먼저 공지되기에 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년치를 한 번에 받을 수 있습니다. 보다 긴 기간에 대해서는, 마지막 날짜를 원하는 날짜로 지정하고, 여러 번 나누어서 받아 병합해야 합니다. 차트 위에 숫자가 적혀있는 입력창이 있는데, 이를 2,000으로 설정하면 2,000거래일 데이터를 받을 수 있습니다.

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

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

참고: 증권사마다 제공하는 데이터와 데이터 기간이 다를 수 있으니 구체적인 방법을 확인할 필요가 있습니다. 각자 자신에게 맞는 방법을 알아보고, 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의 누적 수익률 (환헤지, 환노출) - 파이썬 사용

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

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

아래 왼쪽 그림은 야후 파이낸스에서 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%였습니다. 참고: TR이 PR보다 38.7% 더 많은 자산이 된 것이 아닙니다. 초기 투자금의 38.7% 만큼 더 많은 자산이 되었다는 뜻입니다.

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

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

출간 안내: 연재를 묶어 읽기 쉽게 보완하여 편집한 책이 종이책(교보문고)과 전자책(리디북스, 교보문고, Yes24, 곧 알라딘)으로 출간되었습니다. 책 소개: 구글 시트로 시작하는 투자 포트폴리오 분석 (오렌지사과의 불친절한 워크북) 출간에 부쳐 (샘플북 포함)

참고 서적: <왜 위험한 주식에 투자하라는 걸까? - 장기 투자와 분산 투자에 대한 통계학적 시각> - 이 연재에서 소개하는 각종 분석 방법의 의미를 소개합니다.

함께 읽으면 좋은 글:

• [데이터 분석 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를 운용하는 방법)