구글 스프레드 시트 주식 배당 관리 - 35. 전일 총수익률 구하기 | 전일 대비 수익률 변화 구하기 | 색깔 넣기

오늘은 구글 스프레드 시트로 전일 총수익률을 계산하는 방법에 대한 포스팅을 올려보려고 합니다.

 

스프레드 시트로 포트폴리오를 관리할 때 아쉬운 것 중 하나가 MTS에서는 흔히 제공되는 기본 기능인 전일 수익률과의 비교였습니다. 구글 스프레드 시트에서도 번거롭긴 하지만 이것을 구해볼 수 있습니다.

 

 

1. 전일 평가금 구하기

기본적인 흐름은 다음과 같습니다.

 

• 각 종목 별 전일 평가금을 구한다.
• 위에서 구한 전일 평가금들의 총합을 구한다.
• 전인 평가금들의 총합을 매입금의 총합과 비교하여 전일을 수익률을 구한다.

 

일단 전일 평가금을 구하는 방법을 알아보겠습니다.

 

전일 평가금 구하기

 

전일의 평가금을 구하는 식은 아래와 같습니다.

 

=(index(GOOGLEFINANCE(A3, "close", WORKDAY(today(), -2)), 2, 2)*E3)

 

위의 식을 해당 셀에 넣으면 전일의 종가가 표시됩니다. 

 

위의 식 중 앞 부분만 따로 떼어내서 보면 아래와 같습니다.

 

=GOOGLEFINANCE(A3, "close", WORKDAY(today(), -2))

 

그리고 이것을 셀에 넣고 엔터를 치면 아래와 같은 결과가 나옵니다.

어제 종가 표시

위의 식은 2 영업일 전의 종가를 표시하라는 식입니다. 왜 2 영업일이냐는 질문이 나올 수 있는데, 우리가 보는 미국 주식의 주가라는 것이 1 영업일 전의 것이기 때문에 그렇습니다. 쉽게 말해 우리는 어제 종가를 기준으로 우리의 포트폴리오 수익률을 계산하니까요. 따라서 전일 수익률은 어제보다 하루 더 전의 수익률, 즉 영업일로 이틀 전의 수익률이 될 것입니다.

 

그런데 위의 식은 2x2 형식의 표로 데이터가 산출됩니다. 그래서 2행 2열의 데이터만 추출하는 index 함수를 쓰는 것입니다.

 

그리고 나서 E열에 있는 보유 주수를 곱하면 어제자 평가금이 됩니다. 

 

 

2. 전일 수익률 구하기

전일의 평가금을 구했으면 수익률을 구하는 것은 간단합니다.

 

수익률 = 평가금 / 매입금 -1 

 

입니다.

 

위의 이미지에서는 G1셀에 전일 수익률을 넣었습니다.

 

=(sum(G3:G10)/H1)-1

 

식은 위와 같습니다.

 

그리고 % 버튼을 눌러서 %로 바꿔주면 됩니다. 아니면 미리 100을 곱해두어도 좋습니다만 그러면 셀 안에 % 표시가 되지 않을 것입니다. % 표시 없이 깔끔한 것이 좋다면 아래와 같이 그냥 100을 곱한 값을 구하시면 됩니다.

 

=((sum(G3:G10)/H1)-1)*100

 

 

 

3. 전일 대비 수익률 구하고 색깔로 표시하기

전일 수익률과 대비하여 금일 수익률이 얼마나 변화했는지를 구하는 것은 간단합니다.

 

 

F1셀에 다음과 같은 식을 넣습니다.

 

=B1-G1

 

B1 셀의 총수익률은 이미 구해져 있습니다. 구하는 방법은 아래 링크를 참고하시면 됩니다.

 

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

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

 

그리고 F1 셀에 다음과 같은 조건부 서식을 적용합니다.

 

 

그러면 아래 이미지처럼 색깔이 표시될 것입니다. 전일에 비해 오늘이 더 떨어졌으면 파란색, 그 반대면 분홍색으로 표시됩니다.

 

전일 대비 수익률 변화에 색깔을 넣었습니다.

 

위의 포스팅을 따라 하시면 어제에 비해서 오늘 내 포트폴리오가 얼마나 변화했는지를 한눈에 파악할 수 있습니다. 도움이 되시길 빕니다.