구글 스프레드시트로 하는 펀드 수익률 관리 1 - 구성 및 설정

예전에 엑셀(MS Excel)로 만든 펀드수익률관리를 구글 스프레드시트로 옮겨 봤습니다.

​사용하기 위해서는 엑셀(MS Excel)이란 소프트웨어가 필요하고 가지고 다니거나 다운로드를 해서 실행시켜야 하는 단점이 있었는데 구글 독스(Google Docs)를 이용하여 인터넷에만 연결되어 있으면 편하게 관리할 수 있게 되었습니다.
대부분의 기능은 동일합니다. 웹쿼리를 이용하여 펀드의 기준가를 가지고 오는 부분은 IMPORTHTML을 이용하여 구현하였습니다. 펀드 데이터의 출처는 네이버 펀드입니다.

펀드 종합, 펀드목록, 매수, 매도 , 보유 펀드 기준가, 펀드 데이터, 주가지수, 주가 데이터, 금리 정보, 일별 수익률 이렇게 10개의 시트로 구성되어 있습니다.

수익률은 잔고 좌수와 입출금을 기반으로 계산합니다.

펀드 종합 시트

전체펀드는 매도한 펀드까지 포함하여 전체 수익률입니다. 이미 매도한 펀드의 확정 손익까지 포함하여 수익률을 계산하였습니다. 현재 운용 펀드는 현재 1좌라도 남아 있는 펀드를 대상으로 합니다.
매도 펀드는 이미 매도하여 손실이 확정된 펀드의 수익률입니다.

상승펀드, 하락펀드,설정액 증가, 설정액감소 펀드를 집계 하였습니다.

펀드 목록 /  펀드 추가하는 방법

​이 시트는 투자한 펀드를 입력 하시면 됩니다. 
전부 삭제 하지 마시고 펀드 코드를 수정하시거나 수식이 있는 2행을 복사하여 2행 이후에 행을 추가하여 복사해서 붙여넣기하고 펀드 명과 펀드코드를 변경하시면 자동 계산됩니다. 
- 노란색 부분만 입력 하면 됩니다.  나머지는 수식입니다. 

1일~20일 수익률은 보유펀드 기준가 시트에서 계산되어 표시되었습니다. 

보유펀드 기준가 시트

펀드의 기준가를 업데이트 할 펀드의 목록 입니다.  이 시트가 핵심입니다. 
2행을 복사해서 제일 마지막에 삽입하고 노란색 부분만 변경 입력 하면 됩니다. 

가져오기를 Y로 설정해야 해당 펀드가 수집이 됩니다.  펀드의 코드를 순차적으로 돌아가면서 네이버 펀드로 부터 기준가 및 정보를 가져와서 funddata 시트에 누적으로 입력 합니다. 

펀드가 수집되지 않으면 기준가 열이 #N/A 로 되었다가 수집이 끝나면 기준가가 자동 검색이 됩니다.

D열 부터 H열 까지는 금일부터 해당 일 전 까지 수익률을 계산 한 것입니다 

행 복사 후 변경 해야 할 열 
펀드명, 펀드코드,  가져오기, 구분  

매수 시트

노란색 헤더로 되어 있는 부분만 입력하면 됩니다.
나머지는 수식이기 때문에 신규 펀드 매수항목을 입력 하기 위해서 편의상 행을 복사하여 마지막 행에 삽입하는 형식으로 하시면 됩니다. 다음 펀드 매수 기준일(기준가가 발표된 날), 펀드명, 좌수, 매수 기준가 매수한 증권사, 재투자 여부 펀드 코드를 입력하시면 되겠습니다.

※ 주의할 점
여기서 보셔야 할 사항이 있는데 재 투자 여부입니다.
이것을 펀드 결산시 수수료 가져가면서 추가로 좌수를 늘려 주는 부분인데 최종 매수하신 후 입력하실 때 펀드가 기준가 결산이 되었는지 확인 후 펀드 기준가가 이상하게 전에 매수했던 때와 현저히 작거나 1000으로 되어 있다면 재 투자된 좌 수만큼 추가로 작성해주셔야 합니다. 이때 재 투자 여부에 Y로 설정을 해 둡니다.

거래하고 있는 금융기관에서 펀드거래내역을 살펴보시면 재 투자 항목이 있습니다.

재투자를 안 해놨을 때에도 재투자 항목을 반드시 넣고 그 다음에 매도시트에 같은 좌수로 매도처리 해주시면 됩니다. 

​매도 시트

매수와 동일하게 행을 추가한 다음 매도도 수식이 있는 마지막 행을 복사하여 붙여넣기를 합니다 
매도 날짜 및 펀드명, 거래수량, 매도 기준가, 매도 금액, 증권사, 펀드 코드 같은 것을 입력하면 됩니다.

매도 금액은 거래수량 * 매도 기준가 /1000 이 됩니다.

---

 

fundtemp 시트 

내부 스크립트에 populateDataUpdate()  함수에 의하여 보유펀드 기준가 시트의 펀드코드를 가져와서 루프(Loop)를 둘리면서 1행 1열에
=ImportHtml("http://finance.naver.com/fund/fundDailyQuoteList.nhn?page=1&fundCd=K55301BS2819", "table", 1)
펀드코드가 바뀌는 수식이 적용되고 나오는 데이터에서 2행을 복사해서  funddata 에 매일 기록 합니다. 

Funddata 시트 

일별 기록 시트 & 펀드 수익률 기록하기

MS 엑셀로 했을 때는 펀드 종합에 있는 수익률을 일별 수익률 탭에 매일매일 복사하였습니다.
하지만 누락한 날도 있을 수 있어서  구글 서버의 스케줄러(예약된 작업)에서 실행할 시간과 스크립트만 입력해 두면 컴퓨터가 꺼져 있어도 자동으로 실행해주기 때문에 매일 수익률을 기록할 수 있게 됩니다.

코스피, 코스닥, 다우지수 등의 주가 데이터와 매일매일 수익률 변화를 "일별" 이란 시트에 기록을 하는 것입니다.
도구 메뉴에서 스크립트 편집기를 선택합니다.  코드의 원본은 스크립트 편집기에 들어가면 있습니다.
다음에 recordHistory를 선택합니다. 실행이나 디버그를 누릅니다.

시트 복사 하기

 

시트 복사 후 설정 방법

시트 복사 후 스케줄러에 반드시 등록해 줘야 합니다.

아래의 함수는 일괄 등록 함수 이고 메뉴에 스케쥴러 등록을 누르면 실행 됩니다. 

// 스케쥴러 등록 함수 
function registerScheduledTask(){
   
   ScriptApp.newTrigger('recordHistory').timeBased().everyDays(1).atHour(19).create();
   ScriptApp.newTrigger('populateDataUpdate_Start').timeBased().everyDays(1).atHour(18).create();
}

 

권한 설정 

스크립트를 실행 하면 권한을 요청 하는데 다음과 같이 허용해 주시면 됩니다. 

권한을 묻는 화면
실행 하다 보면 권한이 없다는 메시지가 뜰 수 있습니다.  스크립트 실행할 때와 같이 
좌측 하단에 보면 스크립트이름(으)로이동(안전하지 않음)을 누른다음 허용 버튼을 눌러 주시면 됩니다.

실행 시간을 변경해야 하는 경우 수동으로 등록하는 방법은 도구의 스크립트 편집기에서 시계 모양의 트리거 버튼을 누릅니다. 물론 수정도 가능합니다.
두 함수가 트리거에 등록이 되면  매일 수익률이 업데이트 되어 있는 것을 보실 수 있습니다.

예약된 작업 수동 설정 및 수정

펀드 일일 수익률 기록 함수 /  펀드 기준가 수집 함수 

매일 스크립트를 실행하도록 하려면 스케쥴러에 등록을 시켜야 합니다.

1. 실행 메뉴 밑에 시계 아이콘을 누릅니다.
2. 그럼 다음과 같은 화면을 만나게 되는데 트리거가 설정되어 있지 않습니다 여기를 클릭하여 트리거를 추가하세요를 물어 보면 저장을 클릭합니다.

실행에 일일 수익률 기록 함수인 recordHistory를 선택하고 시간 기반, 일일 타이머, 오후 8시부터 9시 사이에 자동 실행을 선택하여 저장을 누릅니다. 
스크립트 편집기의 왼쪽에 시계모양을 눌러보면 스크립트를 예약된작업(트리거)에 등록 하는 것이 나옵니다.
다음과 같이 등록을  하면 됩니다. 

 

일일 펀드 수익률 메일로 받아보기 
recordHistory 함수가 실행 되면 금일의 수익률을 메일로 보내주는 기능을 만들었습니다. 

​구글 스프레드시트를 공부하면서 개인에 맞게 수정하셔서 사용하시면 되겠습니다.

구글 스프레드시트로 하는 펀드수익률 관리 - 수정 사항 / 최신 업데이트