본문 바로가기

컴퓨터 활용/구글스프레드시트 응용

구글 스프레드시트로 하는 펀드수익률 관리 - 일일 펀드 분석 및 이벤트 E-mail 통보

구글 스프레드 시트로 펀드 수익률 관리를 하고 있습니다.

제 작년에 최종 수정을 하였는데 기준가 수집하는 방법 , 초기화면에 상승,하락 펀드, 설정액 증가 , 감소 펀드 표시 기능을 추가하고 다음 포스팅에서 설명을 하였습니다.

이번에는 제가 개인적으로 사용하는 펀드 시트에 몇가지 기능을 추가하였는데 공개용에도 업데이트 하려고 합니다.

이번에 개선 한 기능은 recordHistory() 라는 일일 수익률 기록 함수가 하루에 한번 씩 실행 되는데 투자하고 있는 펀드에 이벤트가 발생하면 첫 화면인 "펀드종합" 시트와 이메일로 알려 주는 기능입니다.

원래 펀드닥터에서 직접 가져 왔을 때는 1일 벤치마크 수익률 및 펀드 결산일을 알 수 있었는데 펀드닥터에서 가져올 수 없게 되어 어쩔 수 없이 네이버 증권을 이용하게 되었답니다. 그 때 만들어둔 루틴을 사용하였습니다.

펀드 관리 시트는 거의 안 들어 오는 터라 들어가지 않고 매일 마이너스 난 펀드가 원금을 회복하는지 알고 싶었고 설정액이 심하게 빠진다는 것은 펀드에서 자금유출이 심하거나 수익률이 저하되는 경우인데 이것을 매일 매일 채크해서 메일로 받아보면 어떨까해서 만들어 보았습니다.

이벤트는 다음과 같습니다.

  1. 5일 설정액 증가 펀드
  2. 5일 설정액 감소 펀드
  3. 목표수익률 도달 펀드
  4. 5일 수익률 3% 이상 초과 펀드

알고자하는 정보가 있다면 얼마든지 이벤트 설정이 가능합니다.

설정액 증가 펀드는 보유펀드기준가 시트의 5일 설정액 증감 정보를 이용하였습니다. 수식은 글 마지막에 언급 해 두었습니다  목표수익률 도달 펀드는 보유펀드 기준가의 H열에 목표 수익률을 직접 설정 해주었습니다 .
5일 수익률은 보유펀드기준가 시트의 F 열인 5일 수익률을 참조 합니다.

보유펀드기준가 시트

보유펀드 기준가 시트

보유펀드 기준가 시트

일일 실행 후 결과 - 메일전송, 첫화면 공지

메일 전송 결과는 다음과 같습니다.

도구 메뉴의 스프레드 편집기를 실행 시키면 소스를 보실 수 있습니다.
읽기 전용입니다. 수정을 위해서는 시트 복사 해서 사용하세요.

구글 스프레드 시트의 주소는 다음과 같습니다.

 

 

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

펀드의 숫자가 많아질수록 구글 스프레드시트가 가져올 수 있는 한 시트에서 쓸 수 있는 importhtml 함수의 수가 30개 내외이기에 30개가 넘어가면 데이터 가져올 때 에러가 발생합니다. 그래서 펀

richardshin.tistory.com

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 함수 사용법은 다음 포스팅을 참조 하세요.

 

마지막 매수 건 이후 수익률 구하기 @ 구글 스프레드시트로 하는 주식 수익률 관리

네이버 증권 주식 수익률 관리가 없어진 이후 구글 스프레드시트로 투자하고 있는 주식을 관리하기 위하여 만들었는데 필요한 기능을 하나씩 추가하였는데 공개용 버전에도 추

richardshin.tistory.com