※ "버크셔 해서웨이 연간 보고서처럼 주식 포트폴리오 만들기" 내용 중 상세 설명 포스팅입니다.
1. 버크셔 해서웨이 연간 보고서 살펴보기
위 사진은 버크셔 해서웨이 연간 보고서 중 "Investments" 장표입니다. 해당 장표는 "보유 기업 / 보유 주식 수 / 보유 비중 / 총 매입 금액 / 총 평가 금액" 순으로 클래식하게 정리되어 있습니다.
- 버크셔 해서웨이 연간 보고서 링크 : https://www.berkshirehathaway.com/reports.html
이미 구글 스프레드시트로 주식 포트폴리오 관리하시는 분들 중 클래식한 스타일로 정리하고 싶다면, 위 양식도 좋은 방법이라 생각합니다.
다만, 이렇게 완벽한 장표에도 제 눈에 1가지 아쉬운 점이 있었습니다.
바로 주식 보유 비중의 정렬 방식입니다.
"보유 비중이 높은 순으로 정렬되었으면 좋았겠거니..." 하는 아쉬움이 남습니다.
(내림차순 정렬 필요)
구글 스프레드시트로 주식을 관리할 때, 실시간 가격 변동에 따라 보유 비중 순위도 매번 뒤바뀔 텐데... 어떻게 관리할 수 있을까요?
(일일이 필터로 정렬해주는 방식, RANK 함수 사용은 생각에서 배제)
2. QUERY 함수로 해결하기
1. 먼저 예제로 활용될 구글 스프레드시트 파일을 다운로드가 필요합니다.
파일 다운로드는 컴퓨터 접속 후, 아래 링크를 클릭해주세요.
https://docs.google.com/spreadsheets/d/1d-n9kbjW-12qpM4SKTgkrafuWfOl3nN0zf7-lKj3pZo/edit?usp=sharing
2. "파일 → 사본 만들기"를 클릭하여 파일을 다운로드 받습니다. 그러면 예제로 활용될 파일은 준비되었습니다.
3. 예제 파일에는 총 3개의 시트가 있습니다. 3개의 시트에서 최종적으로 우리가 만들 시트는 "Investments 시트"입니다.
1) Stock List 시트 : 보유 주식 작성용
2) Backup Data 시트 : 보유 주식 1차 정리용
3) Investments 시트 : 우리가 만들 최종 결과물
4. 먼저, 버크셔 해서웨이 연간 보고서처럼 "Investments" 시트 양식을 제작합니다.
(내용을 제외한 양식부터 제작)
1) Shares : 보유 주식 수
2) Company : 기업 명
3) Percentage of Company Owned : 보유 비중
4) Cost : 총 매입 금액
5) Market : 총 평가 금액
5. "Backup Data 시트"에 이미 정리된 데이터를 Vlookup 함수를 사용하여 가져옵니다. 여전히 결과 값은 보유 비중 순으로 정렬되어 있지 않습니다.
1) Shares에 사용한 수식
=iferror(vlookup(D11, 'Backup Data'!C:H,5,0),"")
2) Company : 기업 명 텍스트 입력
▶ 보유 비중이 가장 높은 주식 순으로 작성되도록 수식 입력 예정
3) Percentage of Company Owned 수식
=iferror(vlookup(D11, 'Backup Data'!C:H,6,0)*100,"")
4) Cost 수식
=iferror(vlookup(D11, 'Backup Data'!C:H,3,0),"")
5) Market 수식
=iferror(vlookup(D11, 'Backup Data'!C:H,2,0),"")
6-1. 보유 비중 순으로 정렬하기 위해선 필터 기능을 사용하는 것이 아닌, Query 함수 사용이 필요합니다.
(필터 기능을 사용 시, 주가 변동으로 보유 비중 순위가 변동되면 매번 재정렬이 필요하니.. 생각에서 배제하자구요)
※ 쿼리 함수 기초적인 사용 방법 알아보기
https://heecheoldo.tistory.com/203
6-2. 우선, Company 열 D11 셀에 아래 수식을 입력합니다.
=QUERY('Backup Data'!C5:H, "SELECT C")
▶ 해당 수식은 QUERY 함수를 사용한 것으로 "Backup Data 시트의 C5:H의 데이터 범위에서 C열(종목명)을 가져와라는 의미"입니다.
7. 결과를 보면 여전히 보유 비중 순으로 정렬되지 않았습니다. (문제점 1)
또한 종목 명 수식을 D11에 입력했지만, 값은 D12부터 작성되었습니다. (문제점 2)
※ 문제점 1 해결 방안
먼저 보유 비중 순으로 정렬하기 위해 ORDER BY 절을 사용하도록 하겠습니다.
(상세 사용 방법은 아래 링크 중 예제 6번 참고)
https://heecheoldo.tistory.com/203
=QUERY('Backup Data'!C5:H, "SELECT C ORDER BY H DESC")
▶ "Backup Data 시트의 C5:H 데이터 범위에서 C열(종목명)을 가져오는 데, H열(보유 비중) 내림차순으로 가져와라는 의미"입니다.
※ 문제점 2 해결 방안
D11부터 종목명을 작성되도록 OFFSET 절을 사용합니다.
=QUERY('Backup Data'!C5:H, "SELECT C ORDER BY H DESC OFFSET 1")
▶ "Backup Data 시트의 C5:H 데이터 범위에서 C열(종목명)을 가져오는 데, H열(보유 비중) 내림차순 + 1행을 띄워서 가져와라는 의미"입니다.
위 예시처럼 QUERY 함수를 활용한다면, 실시간으로 보유 비중 순위가 달라지더라도 깔끔하게 정렬된 보고서가 완성됩니다.
(보유 비중 내림차순 자동 정렬)
감사합니다.
※ "버크셔 해서웨이 연간보고서처럼 주식 포트폴리오 만들기" 두 번째 내용
실시간 주식 보유 비중 정렬하기 #2 : SORT 함수 활용하기
※ "버크셔 해서웨이 연간보고서처럼 주식 포트폴리오 만들기" 설명 두 번째 내용입니다. 지난 포스팅 내용은 Investment 장표에서 Query 함수를 활용한 주식 보유 비중 내림차순 정렬에 대해 알아보
heecheoldo.tistory.com
'주식 > 구글 스프레드시트' 카테고리의 다른 글
| 버크셔 해서웨이 연간보고서 따라 만들기 #3 : 시장지수 데이터 수집하기 (0) | 2022.07.18 |
|---|---|
| 실시간 주식 보유 비중 정렬하기 #2 : SORT 함수 활용하기 (0) | 2022.06.02 |
| 구글 스프레드시트로 퀀트 투자하는 방법 : Query 함수의 세계로 빠져들자 (2) | 2022.04.05 |
| 과거 시장 수익률 계산하는 방법 (코스피, 나스닥 시장 수익률 산출하기) (0) | 2022.03.21 |
| 시장 지수 과거 데이터 한번에 다운로드 받는 방법 (코스피, S&P500, 나스닥 등) (0) | 2022.03.20 |
댓글