구글 스프레드 시트로 펀드 수익률 관리를 하고 있습니다.
제 작년에 최종 수정을 하였는데 기준가 수집하는 방법 , 초기화면에 상승,하락 펀드, 설정액 증가 , 감소 펀드 표시 기능을 추가하고 다음 포스팅에서 설명을 하였습니다.
이번에는 제가 개인적으로 사용하는 펀드 시트에 몇가지 기능을 추가하였는데 공개용에도 업데이트 하려고 합니다.
이번에 개선 한 기능은 recordHistory() 라는 일일 수익률 기록 함수가 하루에 한번 씩 실행 되는데 투자하고 있는 펀드에 이벤트가 발생하면 첫 화면인 "펀드종합" 시트와 이메일로 알려 주는 기능입니다.
원래 펀드닥터에서 직접 가져 왔을 때는 1일 벤치마크 수익률 및 펀드 결산일을 알 수 있었는데 펀드닥터에서 가져올 수 없게 되어 어쩔 수 없이 네이버 증권을 이용하게 되었답니다. 그 때 만들어둔 루틴을 사용하였습니다.
펀드 관리 시트는 거의 안 들어 오는 터라 들어가지 않고 매일 마이너스 난 펀드가 원금을 회복하는지 알고 싶었고 설정액이 심하게 빠진다는 것은 펀드에서 자금유출이 심하거나 수익률이 저하되는 경우인데 이것을 매일 매일 채크해서 메일로 받아보면 어떨까해서 만들어 보았습니다.
이벤트는 다음과 같습니다.
- 5일 설정액 증가 펀드
- 5일 설정액 감소 펀드
- 목표수익률 도달 펀드
- 5일 수익률 3% 이상 초과 펀드
알고자하는 정보가 있다면 얼마든지 이벤트 설정이 가능합니다.
설정액 증가 펀드는 보유펀드기준가 시트의 5일 설정액 증감 정보를 이용하였습니다. 수식은 글 마지막에 언급 해 두었습니다 목표수익률 도달 펀드는 보유펀드 기준가의 H열에 목표 수익률을 직접 설정 해주었습니다 .
5일 수익률은 보유펀드기준가 시트의 F 열인 5일 수익률을 참조 합니다.
보유펀드 기준가 시트
일일 실행 후 결과 - 메일전송, 첫화면 공지
메일 전송 결과는 다음과 같습니다.
도구 메뉴의 스프레드 편집기를 실행 시키면 소스를 보실 수 있습니다.
읽기 전용입니다. 수정을 위해서는 시트 복사 해서 사용하세요.
구글 스프레드 시트의 주소는 다음과 같습니다.
5일 수익률은 F 열이며 F2:F41 까지 보유펀드_5일수익률 이란 영역이름으로 지정 되어 있습니다.
하루에 한번 수익률을 기록하기 위하여 실행하는 recordHistory() 함수의 뒷 부분에 2열 부터 41열 까지 돌아가면서 조건이 맞으면 그 정보를 누적으로 메시지에 추가하여 최종 메일로 발송합니다.
해당 부분의 소스는 다음과 같습니다.
var fundNameRange = fundList.getRange("보유펀드_이름").getValues();
var fundValRange = fundList.getRange("보유펀드_기준가").getValues();
var fundGiJunGa = fundList.getRange("보유펀드_과표기준가").getValues();
var fundTargetYield = fundList.getRange("보유펀드_목표수익률").getValues();
var fundProfitYield = fundList.getRange("보유펀드_수익률").getValues();
var fundSulJungAck5 = fundList.getRange("보유펀드_설정액5").getValues(); // 5일 설정액
var fund5day = fundList.getRange("보유펀드_5일수익률").getValues(); // 5일 수익률
// 이벤트 문자열 변수
var sJaUpFundName =""; // 5일 설정액 증가 펀드
var sJaDnFundName =""; // 5일 설정액 감소 펀드
var fundReturnOnTarget = ""; // 목표수익률 도달 펀드
var fundGoodPerform5 = ""; // 5일 수익률이 3%가 넘어간 펀드
// 보유펀드의 기준가 영역의 루프를 돌립니다.
for(var i=0; i<fundValRange.length; i++)
{
// 5일 설정액 증가
if( parseInt(fundSulJungAck5[i]) > 0.05){
sJaUpFundName += fundNameRange[i].toString()+"(" +
Math.round( parseFloat(fundSulJungAck5[i])*10000)/100 +"%)\n";
}
// 5일 설정액 감소
if(parseInt(fundSulJungAck5[i]) < -0.05){
sJaDnFundName += fundNameRange[i].toString()+"(" +
Math.round( parseFloat(fundSulJungAck5[i])*10000)/100 +"%)\n";
}
// 목표수익률 도달
if(parseFloat(fundTargetYield[i]) <= parseFloat(fundProfitYield[i])){
fundReturnOnTarget += fundNameRange[i].toString() + "(현재 수익률 : " +
Math.round( parseFloat(fundProfitYield[i])*10000)/100 +"% / "+
Math.round( parseFloat(fundTargetYield[i])*10000)/100 +"%)\n";
}
// 5일 수익률이 3%가 넘어간 것
if(parseFloat(fund5day[i]) >= 0.03 ){ // 문자이니 float 로 만들어줘야함
fundGoodPerform5 += fundNameRange[i].toString() +
"5일 수익률 : " + Math.round( parseFloat(fund5day[i])*10000)/100 +"%\n";
}
// 펀드명이 비어 있으면 스킵
if(fundNameRange[i].toString() != "" ) continue;
}
if(sJaDnFundName || sJaUpFundName ){
if(sJaUpFundName != "") msg2 +="5일 설정액 5% 이상 증감 펀드 : \n" + sJaUpFundName;
if(sJaDnFundName != "") msg2 +="5일 설정액 5% 감소 증감 펀드 : \n" + sJaDnFundName;
}
if(fundReturnOnTarget != ""){
msg2 +="☆ 목표수익률 도달 펀드\n" + fundReturnOnTarget;
}
if(fundGoodPerform5 != ""){
msg2 +="☆ 5일 수익률 3% 이상 펀드\n" + fundGoodPerform5;
}
if(msg != "")
{
msg += msg2; // 메일 전송용으로 메시지 합칩
sheet.getRange("G1").setValue(msg2); // 첫 화면에 보여주기
writeLog(msg); // 로그 Log 시트에 보냄
sendEmail(msg); // 메일로 보냄
}
다음은 5일 설정액 증감과 수익률 의 엑셀 수식 입니다.
5일 설정액 증감 |
INDEX(funddata!E:E, ARRAY_CONSTRAIN( ARRAYFORMULA(MATCH(B2&수집대상일,funddata!H:H&funddata!A:A,0)),1,1) ) --- 금일 설정액 수식 / INDEX(funddata!E:E, ARRAY_CONSTRAIN( ARRAYFORMULA( MATCH(B2&WORKDAY( 수집대상일,-5,휴일영역),funddata!H:H&funddata!A:A,0) ) ,1,1) ) --- 5일 전 설정액 수식 -1 |
5일 수익률 |
$C2 -- 기준가 / INDEX(funddata!$B:$B, ARRAY_CONSTRAIN( ARRAYFORMULA( MATCH($B2&WORKDAY( 수집대상일,-5,휴일영역),funddata!$H:$H&funddata!$A:$A,0) ),1,1) -- 5일 전의 기준가 ) - 1 |
여러번 포스팅을 통하여 설명 하였으니 자세한 설명은 생략 하고 배열 수식을 이용한 MATCH 함수, INDEX 함수 사용법은 다음 포스팅을 참조 하세요.
'컴퓨터 활용 > 구글스프레드시트 응용' 카테고리의 다른 글
해외 상장 ETF - 구성종목 가져오기 (0) | 2021.04.30 |
---|---|
펀드 구성종목 가져오기 & 내일 자 기준가 예측하기 @구글 스프레드 시트 (0) | 2021.04.25 |
구글 스프레드시트로 하는 펀드수익률 관리 - 최종 수정 사항 (6) | 2021.04.09 |
구글 스프레드시트로 하는 펀드 수익률 관리 1 - 구성 및 설정 (0) | 2021.04.05 |
국내 ETF 구성 종목 가져오기 @ 구글 스프레드 시트 이용 (0) | 2021.04.03 |