버크셔 해서웨이 연간보고서 따라 만들기 #3 : 시장지수 데이터 수집하기

※ "버크셔 해서웨이 연간보고서처럼 주식 포트폴리오 만들기" 설명 세 번째 내용입니다.

지난번 "버크셔 해서웨이 연간보고서"의 Investments 장표를 똑같이 만들어봤다면, 이번에는 연간 수익률 비교 장표를 만들어보겠습니다.

 

 

- Investments 장표 만들기 설명 :

 

https://heecheoldo.tistory.com/215?category=959948 

 

 

 

 

위 사진은 버크셔 해서웨이 연간 보고서 중 "연간 수익률 비교" 장표입니다. 해당 장표는 버크셔 해서웨이 수익률 vs S&P500 지수 수익률이 연도별로 정리되어 있습니다.

 

 

- 버크셔 해서웨이 연간 보고서 링크 : https://www.berkshirehathaway.com/reports.html

 

 

 

우리는 해당 장표를 벤치마킹하여, 연도별 "내 주식 수익률"과 "시장 지수 수익률"을 비교해보도록 하겠습니다. 이때 시장 지수는 "코스피 지수 / 코스닥 지수 / S&P500 지수, 나스닥 지수 / 다우 지수"까지 추가해보도록 하겠습니다.

 

 

 

우선, 위 장표를 만들기 위해선 단계별 작업이 필요합니다.

 

 

1) 일별 시장 지수 데이터 수집하기

 

 

2) 수집한 데이터 가져오기 : IMPORTRANGE 함수 활용

 

 

3) 내 주식 연평균 수익률 계산하기 : TWR 계산

 

 

이번 포스팅에서는 "일별 시장 지수 데이터 수집하기" 내용을 다루도록 하겠습니다.

 

 

 

 

 

1. 예제 파일 #1 다운로드하기

 

 

설명에 앞서 예제로 활용할 파일은 아래 링크를 통해 다운로드 가능합니다. 아래 링크 접속 후, "파일 → 사본 만들기"를 클릭하여 주세요.

 

 

- 예제 파일 #1 : 

 

https://docs.google.com/spreadsheets/d/1r4MLdPaYCF3Ro0phMR_Nhk3DrGyex7KOzesNZldDgs0/edit?usp=sharing 

 

 

 

 

 

 

 

2. 시장 지수 데이터 수집하기

 

 

각 시장 지수의 과거 데이터를 수집하기 위해 googlefinance 함수를 사용해야 합니다.

 

 

- googlefinance 함수 기본 설명 : https://heecheoldo.tistory.com/54

 

 

 

 

 

1) 먼저 코스피 지수 과거 데이터를 가져오기 위해, B2 셀에 아래 수식을 입력합니다.

 

 

=googlefinance("KRX:KOSPI","price","2010. 10. 11", today())

 

 

: 2010년 10월 11일 ~ 오늘까지 코스피 지수 값을 가져오세요

 

 

 

 

 

2) 수식 결과 2010년 10월 12일 코스피 지수 값부터 오늘(2022년 7월 15일)까지 코스피 지수 값을 가져왔습니다.

 

 

 

 

 

3) 해당 방법을 통해 코스닥 지수, S&P500 지수, 나스닥 지수, 다우 지수 과거 데이터도 가져옵니다.

 

(모든 데이터는 "Back data" 시트에 입력)

 

 

▶ 코스닥 지수 가져오기

 

=googlefinance("KOSDAQ","price","2010. 10. 11", today())

 

 

▶ S&P500 지수 가져오기

 

=GOOGLEFINANCE(".inx","price","2010. 10. 11", today())

 

 

▶ 나스닥 지수 가져오기

 

=GOOGLEFINANCE(".ixic","price","2010. 10. 11", today())

 

 

▶ 다우 지수 가져오기

 

=GOOGLEFINANCE(".dji","price","2010. 10. 11", today())

 

 

 

 

 

4) 별도 종합장 시트("Market index_Real time 시트")를 만들어서 "Back data 시트" 데이터를 가져옵니다.

 

 

- 이유 :  "Back Data 시트"에서 "종합장 시트"로 다시 데이터를 가져오는 이유는 "각 시장 지수별" 날짜 값이 다르기 때문입니다.

(특히 년 시작 날짜 / 마지막 날짜)

 

 

예시)

 

- 코스피 지수의 "2010년 마지막 날짜" : 2020년 12월 30일

 

- 나스닥 지수의 "2010년 마지막 날짜" : 2010년 12월 31일

 

 

 

 

 

5) Vlookup 함수를 활용하여 각 시장 지수 값을 가져옵니다.

 

(예제 파일에 수식이 입력되어 있으므로 참고)

 

 

 

 

6) 이제 각 시장 지수 "과거 데이터 ~ 현재 데이터"는 준비되었습니다.

 

 

 

 

++추가 업데이트 사항++

 

 

1) googlefinance 함수로 코스닥 지수 값을 가져올 시 오류가 발생했습니다.

 

 

2) 그래서 코스닥 지수 값은 네이버 금융 값을 실시간으로 가져온 뒤, 앱 스크립트로 일별 값을 자동 기록하도록 하였습니다.

 

 

 

 

 

3) 예제 파일에 "KOSDAQ_Back data" 시트를 참고하시길 바랍니다.

 

 

- 코스닥 지수 값을 자동 기록을 위해선 트리거 설정이 필요합니다. 트리거 설정은 아래 설명서 예제 링크를 참고하시기 바랍니다.

 

https://www.notion.so/heecheoldo/History-42ad34107a344034aa3c76e7324deb2c

 

 

 

 

다음 포스팅에서는 IMPORTRANGE 함수를 활용하여 수집한 데이터를 가져온 뒤, 연평균 수익률 계산을 해보겠습니다.

 

 

 

※ "버크셔 해서웨이 연간보고서처럼 주식 포트폴리오 만들기" 네 번째 내용

 

버크셔 해서웨이 연간보고서 따라 만들기 #4 : 수집 데이터 가져오기, 연평균 수익률 계산하기