적립식 투자 관리 - 구글 스프레드시트로 투자 현황 한눈에 보기 (5)

안녕하세요.
적립식 투자자를 위한 구글 스프레드시트로 투자 현황 한 눈에 보기 시리즈 5편을 시작하겠습니다.

 

대시보드 (주식 투자 현황)

대시보드는 한 눈에 내가 투자한 정보를 모두 볼 수 있어야 합니다.
그 목적에 맞게 아래와 같이 구성되어 있습니다.

1) 달러 환율

2) 계좌/티커/거래통화별 투자 현황

3) 자산 현황 그래프

 

사전작업

우선 시트 자체를 깔끔하게 만들어줍니다. 셀을 구분짓는 선을 없애줄거에요.
Ctrl + A를 눌러 시트 전체를 선택한 이후에 선에 대해 하얀색으로 칠해줍니다.

사전 작업 - 셀 테두리 색 변경

 

1) 달러 환율

셀 테두리 색상과 셀 색상 채우기를 통해 적절히 꾸며주시면 됩니다.
내용은 아래와 같이 기준 통화, 환율, 기준일이 됩니다.
환율과 기준일을 가져오는 방법은 아래와 같습니다.

=GOOGLEFINANCE("CURRENCY:USD"&C2) # 환율

=TODAY() # 기준일

저는 위 내용으로 아래와 같이 만들었습니다.

달러 환율

 

2) 계좌/티커/거래 통화 별 투자 현황

아래의 컬럼을 만들 예정입니다.
# 자동완성 예정이라 붙인 부분은 함수를 통해 생성됩니다.

1. 계좌 # 자동완성
2. 티커 (코드)  # 자동완성
3. 거래 통화  # 자동완성
4. 종목명
5. 현재가
6. 매입 수량
7. 매입 평단
8. 총 투자금
9. 평가액
10. 수익률
11. 최근 30일 차트
12. 최근 90일 차트
13. 최근 1년 차트

 

계좌/티커/거래 통화
B6셀에 아래와 같이 입력해주세요.

=SORT(UNIQUE('주식 투자 매입 리스트'!A:C), 1, TRUE, 2, TRUE)

계좌/티커/거래 통화를 자동완성 하여 '주식 투자 매입 리스트' 시트에서 가져와줄거예요.
그럼 아래와 같이 작성됩니다.

 

종목명
E7셀에 이렇게 입력해요.
티커를 통해 종목 풀네임을 가져오는 것이고 미장에서 없다면 한국장에서 가져온다는 로직이 들어있습니다.

=IFERROR(GOOGLEFINANCE(C7, "name"), GOOGLEFINANCE("KRX:"&C7, "name"))

 

현재가
F7셀에 이렇게 입력하세요.
아래와 같이 입력하여 원화/외화에 대응하여 종목의 현재가를 잘 표시해줍니다.

=TEXT(IFERROR(GOOGLEFINANCE(C7), GOOGLEFINANCE("KRX:"&C7)), IF(D7="KRW", "#,###", "#,###.00"))

 

매입 수량
G7셀에 이렇게 입력하세요.
해당 티커의 총 매입 수량을 표기합니다.

=SUMIFS('주식 투자 매입 리스트'!E:E, '주식 투자 매입 리스트'!A:A, B7, '주식 투자 매입 리스트'!B:B, C7)

 

매입 평단
H7셀에 이렇게 입력하세요.
해당 티커의 매입 평균 가격을 표시합니다.

=TEXT(SUMPRODUCT('주식 투자 매입 리스트'!D:D, '주식 투자 매입 리스트'!E:E, ('주식 투자 매입 리스트'!A:A=B7)*('주식 투자 매입 리스트'!B:B=C7)/G7), IF(D7="KRW", "#,###", "#,###.00"))

 

총 투자금
I7셀에 이렇게 입력하세요.
해당 티커의 총 투자금을 표시합니다.

=TEXT(G7*H7, IF(D7="KRW", "#,###", "#,###.00"))

 

평가액
J7셀에 이렇게 입력하세요.
해당 티커의 현재 평가액을 표시합니다.

=TEXT(G7*F7, IF(D7="KRW", "#,###", "#,###.00"))

 

수익률
K7셀에 이렇게 입력하세요.
해당 티커의 수익률을 계산합니다.

=(J7-I7)/I7

 

최근 30일 차트
L7셀에 이렇게 입력하세요.
해당 티커의 30일간 차트를 그립니다.

=SPARKLINE(IFERROR(GOOGLEFINANCE(C7,"PRICE",TODAY()-30,TODAY()), GOOGLEFINANCE("KRX:"&C7,"PRICE",TODAY()-30,TODAY())))

 

최근 90일 차트
M7셀에 이렇게 입력하세요.
해당 티커의 90일간 차트를 그립니다.

=SPARKLINE(IFERROR(GOOGLEFINANCE(C7,"PRICE",TODAY()-90,TODAY()), GOOGLEFINANCE("KRX:"&C7,"PRICE",TODAY()-90,TODAY())))

 

최근 1년 차트
N7셀에 이렇게 입력하세요.
해당 티커의 1년간 차트를 그립니다.

=SPARKLINE(IFERROR(GOOGLEFINANCE(C7,"PRICE",TODAY()-365,TODAY()), GOOGLEFINANCE("KRX:"&C7,"PRICE",TODAY()-365,TODAY())))

 

여기까지 따라오셨으면 아래와 같은 화면이 되어있을겁니다.

저는 여기서 수익률에 색감을 입힙니다. 수익중인지, 아닌지 확인할 수 있게 말이죠.
수익중이면 초록색, 손실중이면 빨간색으로 입히려 합니다.
그럴 때는 조건부 서식을 봐야 하는데요. 서식 메뉴에서 아래 빨간 부분을 클릭해주세요.

조건부 서식을 2개를 만들어 주시는데, 위 이미지를 참고하여 만들어주세요. 범위는 K7부터입니다.
완료 버튼을 눌러 나오시고, 아래처럼 수익률 열을 선택하여 퍼센테이지로 표기하게 해주세요.

 

3) 자산 현황 그래프

이제 달마다 얼마나 상승해왔는지를 볼 수 있도록 그래프를 그립니다.
차트 삽입이 필요합니다. 삽입 메뉴에서 차트를 선택합니다.

 

차트를 만드시면 오른쪽에 차트 편집기라는 화면이 있습니다.
화면과 같이 데이터 범위에는 아래 내용을 넣어주시면 되어요.

'USD 통계 - 월말 매입/평가액'!A2:C88

그럼 아래와 같이 차트가 그려질텐데요, 빨간색 영역이 평가액이고 파란색 영역이 투자액입니다.
테스트 데이터를 넣었기에 차트가 기괴합니다만 방향은 맞습니다.
아래 GIF처럼 라벨을 넣고, 세로축에 달러도 넣어주고, 차트의 제목도 입혀서 꾸며주세요.

 

차트 꾸미기까지 완성하였습니다.
KRW의 경우는 글을 읽으시면서 어떤 부분을 바꾸면 되겠다는 생각이 드셨으리라 생각합니다.
만약 필요하시다면 관련 글도 포스팅 하겠습니다.

여기까지 읽어주셔서 감사합니다.