구글 스프레드시트 주식 배당 관리 - 14. 미국, 국내 주식 합산 시트 만들기

포트폴리오를 관리하는 데 있어 미국 주식, 한국 주식의 합산 수익률을 내는 것도 중요하다. 지난번 포스팅에서 미국 주식과 한국 주식의 시트를 따로 만드는 것이 편한다는 이야기를 했었다. 따라서 이번에는 다른 시트에 있는 정보를 가져오는 방법을 알아보려고 한다.

 

1. 국내 주식 포트폴리오의 총 수익률 구하기

 

구글 스프레드시트 주식 배당 관리 - 11. 총수익률 표시하고 색깔 넣기

 

위의 포스팅에서 미국 주식 포트폴리오의 총 수익률을 구하는 방법을 알아본 적이 있었다. 정확히 같은 방식으로 국내 주식에서도 총 수익률을 구해보려고 한다.

 

 

 

F1 셀은 각 종목의 평가금의 총합이다. 

 

G1 셀은 각 종목의 매입금의 총합이다.

 

B1 셀에는 총수익률을 넣었다.

 

총수익률 = 평가금의 총합 / 매입금의 총합 - 1 = F1/G1 -1

 

위의 식으로 구했다.

 

2. 각 시트의 이름을 바꾸고, 합산할 시트 새로 만들기

미국 주식 포트폴리오가 정리되어 있는 시트는 "US"로, 국내 주식 포트폴리오가 정리되어 있는 시트는 "KR"로 이름 붙였다. 그리고 합산할 시트의 이름은 "Total"로 정했다.

 

위와 같다.

 

3. 다른 시트의 값을 "Total" 시트로 가져오기

하나의 구글 스프레드시트의 다른 시트에 있는 값을 가져오는 방법은 간단하다.

 

예를 들어 "US" 시트에 있는 매입금의 총합을 가져온다고 해보자. 

 

 

위의 경우 매입금의 총합은 J1 셀에 있다. 

 

일단 아래와 같이 "Total"이라는 이름의 새로운 시트에 표를 구성한다. 

 

 

"Total" 시트의 매입금이 들어갈 D1 셀 안에

 

=US!J1 

 

이라고 넣으면 "US"라는 이름의 시트의 "J1" 셀의 값을 가져오라는 의미가 된다.

 

그런데 "US" 시트의 값은 모두 달러이므로 "Total" 시트로 가져올 때는 원화로 바꿔야 한다. 그게 아니라면 "US" 시트에 원화로 환산한 값을 추가하고 그것을 "Total" 시트로 가져와도 된다.

 

이 경우에는 가져온 값에 환율을 곱하는 방법을 썼다.

 

그래서 셀 안에

 

=US!J1*GOOGLEFINANCE("CURRENCY:USDKRW")

 

위의 식을 넣었다.

 

만약 식의 값이

 

 

위와 같이 표시되어서 보기가 싫다면

 

 

위의 툴바 중 소수점을 줄이는 아이콘을 눌러서 소수점을 없앨 수 있다.

 

 

같은 식으로 국내 주식의 값도 가져왔다. 위의 식에서 US 대신 KR을 넣고, 셀의 값을 바꾸면 될 것이다.

 

또한 미국, 국내 각각의 수익률도 구해보았다.

 

3. 미국 주식, 국내 주식을 합산한 수익률 구하기

 

방법은 동일하다. 미국, 국내 주식의 평가금과 매입금의 총합을 구한 뒤 합산 수익률을 구하면 된다.

 

 

그럼 결과는 위와 같다.

 

4. 차트로 매입 비중 표시하기

(1) 원형 챠트로 표시하기

미국 주식과 국내 주식의 매입 비중을 표시하기 위해 원형 차트를 하나 넣는 것도 좋다.

 

아래처럼 차트 설정을 한다.

 

 

그러면 아래와 같은 원형 차트가 완성된다.

 

(2) 바 그래프로 표시하기

매입 비중을 셀 안에 바 그래프로 표시하는 방법도 있다. 원형 차트를 만드는 것도 좋지만, 이것은 별도의 공간이 필요하다는 단점이 있다. 시트의 셀 안에 그래프를 넣어서 깔끔하게 시트를 관리하는 방법이 있다. 이에 대해서는 아래의 포스팅을 참고하면 된다.

 

구글 스프레드시트 주식 배당 관리 - 7. 매입 비중을 바 그래프로 표시

구글 스프레드시트 주식 배당 관리 - 7. 매입 비중을 바 그래프로 표시

 

그리고 셀 안에 차트를 넣는 방법에 대해서 궁금하다면 아래 링크를 참고하면 된다.

 

구글 스프레드시트 주식 배당 관리 - 6. 셀 안에 차트 넣기

구글 스프레드시트 주식 배당 관리 - 6. 셀 안에 차트 넣기

 

(3) 색상 스케일로 표시하기

바 그래프가 아니라 색상 스케일로 표시하는 방법도 있다. 이것은 별도의 칸을 새롭게 만들 필요조차 없어서 공간을 절약할 수 있는 방법이다. 이에 대해서는 아래의 포스팅을 참고하면 된다.

 

구글 스프레드시트 주식 배당 관리 - 26. 색깔로 매입 비중 관리하기

구글 스프레드시트 주식 배당 관리 - 26. 색깔로 매입 비중 관리하기