구글 스프레드시트로 하는 펀드수익률 관리 - 최종 수정 사항

펀드의 숫자가 많아질수록 구글 스프레드시트가 가져올 수 있는 한 시트에서 쓸 수 있는 importhtml 함수의 수가 30개 내외이기에 30개가 넘어가면 데이터 가져올 때 에러가 발생합니다. 그래서 펀드 데이터를 가져오는 방식을 변경하였습니다.
기존 방식은 네이버 펀드의 펀드 기준가를 시트 하나에 모두 표시하는 방법이었는데요. 9일간 기준가만 나오기에 수익률 계산도 9일 밖에 되지 않았습니다.

새 방식은 매일 기준가가 발표되면 오후에 네이버펀드로 부터 데이터를 수집하는 방법인데요. 펀드의 수가 아무리 많더라도 수집 스크립트를 이용하여 가져와서 그날의 펀드정보(기준가, 설정액..)만 뽑아내어 funddata 시트에 기록하는 방식입니다. 이렇게 하면 구글 스프레드시트가 지원할 수 있는 행만큼 데이터를 수집하여 5년 전 수익률도 계산할 수 있게 됩니다.
펀드닥터에서 가져오면 되지만 펀드닥터에는 ID/PW를 넣어야 접속이 가능해서 구글 스프레드시트에서 가져올 수 없네요. 서버가 빠른 네이버 증권에서 데이터를 가져오는 것이 원활합니다.

1일 BM/결산일 통계자료를 제외한 모든 정보를 8시간 이후(오후5시 이후)에나 얻을 수 있습니다.

○ 펀드 종합 시트

1. 금일 수익금, 전일 평가액, 전일 등락률, 금 일 등락률을 추가하였습니다.

2. 상승/ 하락 펀드 분석
최대 상승 및 최대 하락 펀드 / 상승 펀드 및 하락 펀드 목록 그리고 금일 상승폭 및 자신의 수익률을 표시했습니다.

3. 설정액 증감 정보
펀드에서 가장 중요한 것이 바로 설정액이 지속적으로 감소하는지입니다. 마찬가지로 금일 상승폭과 수익률을 표시합니다.

4. 매일 수익률에 대한 그래프는 차트 시트에서 그려주고 있지만 왼쪽 하단에 구글 스프레드시트의 내장 기능인 SPARKLINE 함수를 이용하여 간단한 그래프를 그려 보았습니다.

​○ 보유 펀드 기준가 시트

이 시트가 가장 중요한 시트입니다.

​

각 열에 대하여 설명을 드리면

1. 펀드명, 펀드 코드가 필수적으로 있어야 하고

2. 기준가는 funddata에서 펀드 코드와 날짜로 검색한 기준가를 표시

3. 1, 2, 5, 30, 60 일 전의 수익률을 계산해서 보여 줍니다.

4. 1일 BM 열은 해당 펀드가 BM(벤치마크) 대비 수익률을 차후를 위하여 비워 두었습니다.

펀드닥터나 펀드 운용사에서 직접 수집이 가능하면 매일 수입하여 채워 넣을 생각입니다.

5. 과표기준가는 배당소득세 계산을 위하여 펀드정보로부터 수집

6. 설정액 증감은 전일 설정액과 금일 설정액의 차이 값으로 자동 계산되었습니다.

7. 보유 여부는 좌수가 0 이상인 펀드이면 Y로 표시했습니다.

8. 네이버 펀드와 펀드닥터로 연결하는 하이퍼링크도 만들어 두었습니다.

9. 가져오기 열

사용자가 직접 지정하는 열이 하나 있는데 가져오기 열입니다. Y 이면 해당 펀드는 funddata 시트에 데이터를 가져옵니다. N이나 비워두면 funddata 시트에 데이터를 가져오지 않습니다.

​○ 포트폴리오 조정(리밸런싱)

 

별 다른 것은 없고 펀드 목록의 U 열인 투자 카테고리를 기준으로 SUMIFS 함수를 이용하여 해당 카테고리의 투자 원금을 더한 것입니다. 목표 비중과 신규 투자 금액을 설정하면 추가로 입금하고 매도해야 하는 조정금액을 계산해 줍니다. 필요할 것 같아서 별도의 시트에 분리하였습니다.

​

수동으로 등록하는 방법은 도구의 스크립트 편집기에서 시계 모양의 트리거 버튼을 누릅니다. 물론 수정도 가능합니다.

 

populateDataUpdate_Start() 함수를 위와 방식으로 등록시켜 줘야 합니다.

마찬가지로 매일매일 펀드 성과를 기록하는 recordHistory() 함수도 6시~7시 사이에 실행 되도록 설정해 주시면 됩니다.

​

펀드정보 수집 5시, 일일 기록 6시 실행의 시간을 변경하지 않으시려면 상단 데이터 입력 메뉴에 "스케줄러 등록"을 누르시면 됩니다. 함수의 코드는 도구>스크립트 편집기의 유틸. gs에 있습니다.

​

※ 현재 주식관리처럼 웹 폼을 이용하여 펀드 매수/매도 탭에 수식을 복사하거나 수정하지 않고 값만 입력하면 되도록 초보자도 쉽게 추가하여 사용할 수 있도록 만들고 있는 중입니다. 사실 엑셀을 조금 배우면 쉽습니다.

​

펀드 목록 시트에 항목을 추가하는 방법

새 펀드를 등록할 때 펀드 목록 시트에 2행 전체를 선택하고 아래에 1행 삽입 후 2행을 복사한 다음 붙여 넣으면 됩니다. 추가하신 후 A 펀드 이름과 B 펀드 코드를 변경하시면 자동 계산됩니다.

S~W 열은 펀드의 정보에 맞게 수정해 주시면 됩니다.

​

 

​

매수 및 매도 펀드 시트에도 마찬가지로 2행을 선택하시고 다음에 1행을 삽입한 다음 복사해서 진행하시면 됩니다. ※ 이렇게 하는 이유가 2행부터 범위 이름이 지정되어 있기 때문입니다. 부득이 펀드 매수 금액을 합산을 하려면 2행부터 범위가 지정되어야 하더군요. 첫행이 문자열이라 합산하면 에러가 납니다. 2행의 앞에 넣게 되면 C3:C931로 영역에서 제외가 되어 문제가 되는 거죠. 2행 뒤에 넣어야 됩니다.

재투자 매수 항목 입력

재투자 항목도 다음과 같이 연초에 증권사/은행의 거래내역을 뽑아서 넣어 줍니다.

★ 편드 결산 시 재투자 하지 않고 배당을 받도록 했으면 재투자로 항목을 넣고

매도 시트에 동일 금액으로 매도를 쳐줘야 합니다.

​★ 수작업으로 할 때는 어떤 시트이든 1행은 수식이므로 지워서는 안됩니다.

2행 부터 이름 영역이 지정되어 있기에 영역이 삭제 되기 때문입니다. 행 삽입도 2행 다음에 삽입 해야 합니다.

---

엑셀을 할 수 있으면 아주 기본적인 내용이고 개인별로 필요한 정보를 표시하고 응용해서 펀드관리를 하실 수 있을 것이라 봅니다. 재 투자 항목 입력은 잊지 마세요.

​네이버 펀드 서비스 종료 안내 2021년 12월 13일 

펀드 기준가 데이터를 구하지 못하여 사용이 불가하게 되었습니다.  펀드닥터에서도 제공이 안되네요.

펀드기준가 수집에 문제가 있어 정상사용이 불가능합니다.  현재 점검중이고 시트공유는 비공개로 했습니다.