※ "버크셔 해서웨이 연간보고서처럼 주식 포트폴리오 만들기" 설명 네 번째 내용입니다.
지난번 포스팅에 이어, 각 시장 지수 데이터 값을 IMPORTRAGNE 함수를 활용하여 가져오도록 하겠습니다.
▶ 시장지수 데이터 수집하기 포스팅 :
https://heecheoldo.tistory.com/227?category=959948
1. 예제 파일 #2 다운로드
이번 포스팅은 "예제 파일 #2"로 설명할 예정이오니, 아래 링크 클릭 후 "파일 → 사본 만들기"를 통해 다운로드하여 주시기 바랍니다.
▶ 예제 파일 #2 :
https://docs.google.com/spreadsheets/d/1cyNigKgV7AQPnnRWWw_gSaVrmMy8Y1H2HZXCZbDLZpg/edit#gid=55017370
2-1. IMPORTRANGE 함수 설명
"예제 파일 #1"에 수집한 각 시장 지수 데이터를 "예제 파일 #2"로 가져오도록 하겠습니다. 구글 스프레드시트의 강력한 함수 IMPORTRANGE 함수를 활용해서요.
(예제 파일 #1 데이터 → 예제 파일 #2 수집)
- 예제 파일 #1 : #1. History_Market index_블로그 공유
- 예제 파일 #2 : #2. Performance_블로그 공유
이때 한 가지 의문사항이 생깁니다. 왜 굳이 "구글 스프레드시트 파일"을 하나 더 만들어서 데이터를 불러와야 할까요?
IMPORTRANGE 함수를 활용하면, DB 별도 관리가 용이합니다. 또한, 실시간 데이터 로딩 속도를 높일 수 있습니다. 관리하는 데이터 양이 점점 많아지면, 1개의 구글 스프레드시트 파일로는 오류 나기 십상입니다.
그래서 별도 구글 스프레드시트 파일들을 만들어, 필요 데이터만 추출/관리가 중요합니다. 이 부분은 많은 구글 스프레드시트 파일을 관리해보시면, 이해할 날이 오실 거라 생각합니다.
자세한 IMPORTRANGE 함수 설명은 아래 링크를 참고해주세요.
[구글 스프레드시트] importrange 함수로 주식 DB 관리하기
저는 구글 스프레드시트로 주식 포트폴리오 관리, S-RIM 적정주가 구하기 등 다방면으로 활용하고 있습니다. 오늘은 importrange 함수를 활용하여 주식 DB 관리 방법에 대해 포스팅해보겠습니다. 1)
heecheoldo.tistory.com
2-2. 수집 데이터 가져오기
1) "예제 파일 #2"에 "History_Market index_Real time" 시트가 위 사진과 같이 있습니다.
- 1행 : 날짜 / 코스피 지수 / 코스닥 지수 / S&P500 지수 / 나스닥 지수 / 다우 지수
2) IMPORTRANGE 함수를 활용하여, A2 셀에 아래 수식을 입력합니다.
※ IMPORTRANGE 함수 구문
=IMPORTRANGE("데이터를 가져 올 스프레드시트 URL", "시트 이름!가져올 셀 범위")
※ A2셀 입력 수식
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1r4MLdPaYCF3Ro0phMR_Nhk3DrGyex7KOzesNZldDgs0/edit?usp=sharing","Market index_Real time!B2:G")
=IMPORTRANGE("예제파일 #1 URL", "시트 이름!가져올 셀 범위")
3) "예제 파일 #1"에 있는 "Market index_Real time" 시트에서 B2:G2 셀 범위 데이터를 실시간으로 가져왔습니다.
각 시장지수 수익률 계산을 위한 데이터 준비가 끝났습니다.
이제 연도별 시장 수익률을 구해보도록 하겠습니다.
3-1. 연도별 시장 수익률 구하기 : 연평균 수익률 공식 설명
먼저 위 사진처럼 시장 수익률을 구하기 위한 공식은 아래와 같습니다.
※ 연평균 수익률 공식
예시 #1. 2011년 코스피 지수 수익률 구하기
- '11년 최종 가치 (2011년 12월 29일) : 1,825.74
- '11년 시초 가치 (2011년 1월 3일) : 2,070.08
- 투자기간 : 1년 (365일)
: 2011년 1월 1일 ~ 2011년 12월 31일
예시 #2. 2011년 ~ 2012년 코스피 지수 수익률 구하기
- '12년 최종 가치 (2012년 12월 28일) : 1,997.05
- '11년 시초 가치 (2011년 1월 3일) : 2,070.08
- 투자기간 : 2년 (730일)
: 2011년 1월 1일 ~ 2012년 12월 31일
예시 #3. 2022년 코스피 지수 수익률 구하기 (22년 7월 15일 기준)
- 최종 가치 (2022년 7월 15일) : 2,330.98
- 22년 시초 가치 (2011년 1월 3일) : 2,070.08
- 투자기간 : 0.54년 (196일)
: 2022년 1월 1일 ~ 2022년 7월 15일
3-2. 연도별 시장 수익률 구하기 : 함수 수식 기입
위 예제 공식을 활용하여, 연도별 수익률이 자동 기입되도록 함수 수식을 입력하도록 하겠습니다.
1) 기본적으로 L열, N열, P열, R열, T열에 복잡한 함수 수식을 입력하였습니다.
이미 "예제 파일 #2"에 자동 입력되도록 기입해놓았으니, 잘 이해되지 않으시면 "B열 연도 값만 수정"하여 사용하시면 됩니다.
설명 #1) 코스피 2011년 수익률 구하기 함수 수식
★ L12 셀 함수 수식 :
=if(year(today())>=B12,((vlookup(max(filter('History_Market index_Real time'!A:A,year('History_Market index_Real time'!A:A)=B12,not('History_Market index_Real time'!B:B=""))),'History_Market index_Real time'!A:F,2,0))/(vlookup(min(filter('History_Market index_Real time'!A:A,year('History_Market index_Real time'!A:A)=B12,not('History_Market index_Real time'!B:B=""))),'History_Market index_Real time'!A:F,2,0)))^(1/((MAX(filter('History_Market index_Real time'!A:A,year('History_Market index_Real time'!A:A)=B12))-date(B12,1,1)+1)/365)),"")
: 2011년 마지막 날 종가 값 / 첫 개장 날 종가 값을 가져온 후 수익률 계산
설명 #2) 코스피 2022년 수익률 구하기 함수 수식
(2022년 7월 18일 기준)
★ L23 셀 함수 수식 :
=if(year(today())>=B23,((vlookup(max(filter('History_Market index_Real time'!A:A,year('History_Market index_Real time'!A:A)=B23,not('History_Market index_Real time'!B:B=""))),'History_Market index_Real time'!A:F,2,0))/(vlookup(min(filter('History_Market index_Real time'!A:A,year('History_Market index_Real time'!A:A)=B23,not('History_Market index_Real time'!B:B=""))),'History_Market index_Real time'!A:F,2,0)))^(1/((MAX(filter('History_Market index_Real time'!A:A,year('History_Market index_Real time'!A:A)=B23))-date(B23,1,1)+1)/365)),"")
: 2022년 7월 18일 종가 값 / 첫 개장 날 종가 값을 가져온 후 수익률 계산
2) 함수 수식이 복잡하고 이해하기 힘드실 거라 판단하여, 위 사진처럼 깔끔하게 흰색 폰트로 가렸습니다.
(Back data만 완벽하다면 연평균 수익률은 자동 계산)
3) B열 Year 값만 변경하여 사용하시면, 연도별 수익률이 자동 계산되오니 참고하시기 바랍니다.
4) 시트 맨 아래에는 종합 연평균 수익률이 자동 계산됩니다.
설명 #1
(2020년 1월 1일 ~ 2023년 10월 19일까지)
- 코스피 연평균 수익률 : 2.8%
- 코스피 누적 수익률 : 111.1%
설명 #2
(2020년 1월 1일 ~ 2023년 10월 19일까지)
- S&P500 연평균 수익률 : 7.7%
- S&P500 누적 수익률 : 132.4% (2020년 대비 1.324배)
어려운 내용 이해하시느라 고생 많으셨습니다. 다음 포스팅에서는 각 시장 지수 수익률과 비교하기 위한, "내 연평균 주식 수익률 구하기"입니다.
: 입출금 내역 고려한 TWR 수익률 구하기 (Time Weighted Return)
※ "버크셔 해서웨이 연간보고서처럼 주식 포트폴리오 만들기" 다섯 번째 내용
버크셔 해서웨이 연간보고서 따라 만들기 #5 : 입출금 내역 고려한 연평균 수익률 구하기
※ "버크셔 해서웨이 연간보고서처럼 주식 포트폴리오 만들기" 설명 다섯 번째 내용입니다. 지난 포스팅에 이어, 현금 흐름(입출금)을 고려한 내 주식 연평균 수익률 구하기에 대해 설명드리겠
heecheoldo.tistory.com
'주식 > 구글 스프레드시트' 카테고리의 다른 글
| 버크셔 해서웨이 연간보고서 따라 만들기 #6 : 내 연평균 수익률과 시장 지수 수익률 비교하기 (0) | 2023.04.23 |
|---|---|
| 버크셔 해서웨이 연간보고서 따라 만들기 #5 : 입출금 내역 고려한 연평균 수익률 구하기 (0) | 2023.04.20 |
| 버크셔 해서웨이 연간보고서 따라 만들기 #3 : 시장지수 데이터 수집하기 (0) | 2022.07.18 |
| 실시간 주식 보유 비중 정렬하기 #2 : SORT 함수 활용하기 (0) | 2022.06.02 |
| 실시간 주식 보유 비중 내림차순 정렬하기 #1 : QUERY 함수 활용하기 (0) | 2022.04.08 |
댓글