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

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

 

월/티커별 통계

이번 시트에서는 총 6가지 열에 대해 작성합니다.

1) 월/티커별 그룹화
해당 월에 어떤 계좌에서 투자했는지, 일자와는 상관 없이 해당 월에 대한 통계를 내기 위해 그룹화합니다.

2) 말일 종가
해당 월의 말일 종가를 기록합니다.
6번째 작업인 티커별 말일 평가액을 계산하기 위함입니다.

3) 거래일 기준 말일
해당 월에 개장이 마지막으로 된 날을 기록합니다.
4, 5, 6번째 작업의 기준일을 계산하기 위함입니다.

4) 티커별 말일 투자 누계액
최초부터 해당 월 거래일 기준 말일까지 티커별 누적 투자액을 기록합니다.

5) 티커별 말일 투자 수량 누계
최초부터 해당 월 거래일 기준 말일까지 티커별 누적 수량을 기록합니다.

6) 티커별 말일 평가액
4번과 5번을 곱한 결과를 기록합니다.

위 내용들을 만들어 볼텐데요.
저는 USD/KRW 화폐별로 나누어 기록하고 있습니다.
이 포스팅에서는 USD를 기준으로 작성하겠습니다.
KRW 통계를 위해서는 아래 1번에서 제가 표기한 부분만 변경해주시면 됩니다.

 

1) 월/티커별 그룹화

"USD 통계 - 월/티커별" 시트에서 A1셀에 아래와 같이 입력합니다.

USD 통계 - 월/티커별 ing (1)

개발자들이라면 많이 보는 SQL과 유사한데요, 이 시리즈에서는 간략하게 무엇을 하는지만 알려드리고 넘어갈게요.
3줄 요약하자면 아래와 같습니다.

1. 주식 투자 매입 리스트에서 B~G 열을
2. USD 화폐만 선택해서
3. 년, 월, 티커별로 그룹화

=QUERY('주식 투자 매입 리스트'!B:G, 
       "SELECT YEAR(F), MONTH(F)+1, B, SUM(E), SUM(G) 
        WHERE F IS NOT NULL AND C = 'USD'
        GROUP BY YEAR(F), MONTH(F)+1, B 
        ORDER BY YEAR(F) ASC, MONTH(F)+1 ASC, B ASC", 1)

위 코드를 복사 붙여넣기 해주시면 아래의 빨간 영역 같이 각 셀이 자동완성됩니다.
만약 KRW 통계를 내고 싶으시다면 3번째 줄에 C = 'USD' 부분을 C = 'KRW'로 바꿔주세요.

USD 통계 - 월/티커별 ing (2)

"주식 투자 매입 리스트" 시트에서 가져온 데이터는 빨간 네모 영역처럼 그룹화됩니다.
해당 월에 어떤 계좌에서 투자했는지, 몇일에 투자했는지에 상관 없이 월/티커별로 그룹화한거예요.

 

2) 말일 종가

티커별로 그룹화가 되었으니 해당 월의 말일 종가를 가져오겠습니다.
GOOGLEFINANCE 함수를 사용하여 가져올건데요.
지난 달은 괜찮지만, 현재 월(작성일 기준 25년 3월)의 거래일 중 마지막날의 데이터를 가져와야 합니다.
또한 전일 종가를 사용하면 금일 가격이 실시간으로 반영이 안되는 문제가 있습니다.
저는 금일 가격까지 실시간으로 반영되기를 원해서 그렇게 함수를 구현하였습니다.

F1 셀에는 "말일 종가"를 입력하고 그 아래 F2 셀에는 위에 작성한 조건을 만족하도록 함수를 구현합니다.

=INDEX(
    QUERY(
        GOOGLEFINANCE(
            C2,
            "price",
            EOMONTH(DATE(A2, B2, 1), -1),
            EOMONTH(DATE(A2, B2, 1), 0)
        ), "SELECT Col2 ORDER BY Col1 DESC LIMIT 1"
    ), 2, 1)

F2 셀에 입력하고 이번에도 마찬가지로 아래 행까지 모두 적용시켜주세요.

USD 통계 - 월/티커별 - 말일 종가 (1)

 

3) 거래일 기준 말일

다음으로 거래일 기준으로 말일을 가져오겠습니다.
G1셀에 "거래일 기준 말일"을 작성하고 그 아래 G2셀에 함수를 구현합니다.

=DATEVALUE(
    TEXT(
        INDEX(
            QUERY(
                GOOGLEFINANCE(
                    C2,
                    "close",
                    EOMONTH(DATE(A2, B2, 1), -1),
                    EOMONTH(DATE(A2, B2, 1), 0)
                ), "SELECT Col1 ORDER BY Col1 DESC LIMIT 1"
            ), 2, 1), "YYYY-MM-DD"))

위처럼 구현하시면 단순 숫자만이 표기 될 텐데요.
아래와 같이 G열 모두에게 서식 변경을 진행하고 그 아래의 셀들도 적용해주세요.

USD 통계 - 월/티커별 - 거래일 기준 말일

 

4) 티커별 말일 투자 누계액

투자 시작 때 부터 해당 월 말일까지의 티커별 투자 누계액을 기록합니다.
H1 셀에 "티커별 말일 투자 누계액"을 작성하고 H2 셀에 아래와 같이 함수를 입력합니다.
"거래일 기준 말일" 열을 조건으로 주어 티커별 합계를 구합니다.

=SUMIFS(E$2:E, G$2:G, "<="&G2, C$2:C, "="&C2)

3번과 같이 아래 셀들도 적용해 줍니다.

USD 통계 - 월/티커별 - 티커별 말일 투자 누계액

 

5) 티커별 말일 투자 수량 누계

투자 시작 때 부터 해당 월 말일까지의 티커별 투자 수량 누계를 기록합니다.
I1 셀에 "티커별 말일 투자 수량 누계"를 작성하고 I2 셀에 아래와 같이 함수를 입력합니다.
"거래일 기준 말일" 열을 조건으로 주어 티커별 합계를 구합니다.

=SUMIFS(D$2:D, G$2:G, "<="&G2, C$2:C, "="&C2)

4번과 같이 아래 셀들도 적용해 줍니다.

USD 통계 - 월/티커별 - 티커별 말일 투자 수량 누계

 

6) 티커별 말일 평가액

4번과 5번을 곱한 합을 기록합니다.
J1 셀에 "티커별 말일 평가액"을 작성하고 J2 셀에 아래와 같이 계산 식을 입력합니다.

=F2*I2

5번과 같이 아래 셀들도 적용해 줍니다.

USD 통계 - 월/티커별 - 티커별 말일 평가액

 

7) 셀 서식 지정

통계 시트는 완성되었습니다.
하지만 총액이나 누계액, 평가액 쪽이 숫자로만 되어있어서 읽히질 않아요.
서식을 달러로 바꿔주세요.

USD 통계 - 월/티커별 - 셀 서식 지정

 

마무리

이렇게 "USD 통계 - 월/티커별" 시트 작성을 마무리 했습니다.
KRW는 1번을 참고해서 비슷하게 진행하시면 됩니다.
다음 시간에는 "USD 통계 - 월말 매입/평가액" 시트를 만들어보겠습니다.
감사합니다.