폼 입력으로 신규 펀드 생성 하기 @구글 스프레드시트 펀드 수익률 관리 & 웹 앱(Web App ) 구현

펀드닥터 서비스가 중단되면서 2017년도에 구글 스프레드시트로 펀드 수익률을 관리하는 시트를 만들어 사용 중입니다. 개인적으로 사용하던 것을 조금씩 수정해 왔는데 이번에 주식 수익률 관리처럼 자동으로 데이터를 입력하는 부분을 만들었습니다. 공개용에도 기능을 추가하고 내부를 좀 뜯어서 보여 드리고자 합니다.

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

​최근에 펀드거래내역을 입력하는 어플리케이션을 만들었는데 이번에는 신규 펀드를 등록하는 것을 자동화했습니다.

펀드이름, 펀드코드, 펀드 정보를 입력하고 매수내역, 매도내역 스프레드시트에 있는 거래 내역에서 펀드 수익률을 계산하는 시트입니다.

​1) 운용목록 : 현재 운용 중인 펀드 수익률 계산

2) 전체펀드목록 : 과거 펀드까지 포함된 펀드 수익률 계산

3) 보유펀드기준가 : 금일 펀드들의 기준가를 수집하기 위한 대상 목록 금일 기준가, 펀드닥터가 계산한 기간별 수익률들 수집

보유펀드기준가 시트

​

펀드가 신규로 생성이 되면 위 3개의 시트에 정보가 각각 들어가야 됩니다.

구글 스프레드시트로 하는 펀드 수익률 관리

https://docs.google.com/spreadsheets/d/1O9jYnsTj83IyUE2B1eMtTUbWlXd5dnXV_yNy9XElduQ/edit?usp=sharing

출처는 반드시 밝혀 주시고 상업적인 이용이 아니라 개인적인 사용이면 얼마든지 수정하여 활용하셔도 좋습니다.

다음은 내부적 동작에 대해서 간략하게 설명해 보았습니다.

저는 개발자가 아닌 관계로 좀 허접할 수 있습니다. 양해 바랍니다.

​

​

메뉴에 있는 확장 프로그램에 Apps Script 메뉴를 선택하면 스크립트 에디터가 나옵니다.

menu.gs 파일에 다음 부분이 있는데 onOpen() 함수는 시트 시작 시 제일 먼저 실행되는 함수입니다.

function onOpen(){ var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("펀드종합"); var ui = SpreadsheetApp.getUi(); ui.createMenu('데이터입력') .addItem('공지/기타', 'noticeBox') .addItem('스케쥴러 등록.', 'registerScheduledTask') .addItem('펀드데이터 가져오기.', 'FundDataUpdate_Start') .addItem('매수/매도내역입력', "showInputForm") .addItem('펀드생성', "createFundItem") .addToUi();

상단 메뉴에 사용자정의 메뉴를 등록 시키는 역할을 합니다

​

폼을 표시하기 위하여 만들어야 할 createFundItem 함수는 다음과 같습니다.

function createFundItem()
{
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var 운용목록 = ss.getSheetByName("운용목록");
  var codeSheet = ss.getSheetByName("Code");

  var fund_broker =  codeSheet.getRange("증권사명").getValues(); // 증권사
  var fund_class = codeSheet.getRange("B2:B").getValues(); // 펀드 대 분류 
  var fund_profile = codeSheet.getRange("C2:C").getValues(); // 펀드 프로파일 
  
  var template = HtmlService.createTemplateFromFile("NewFund.html");

  template.broker = fund_broker;
  template.fund_class = fund_class;
  template.fund_profile = fund_profile;
  
  title ="펀드명 입력";
  
  var htmlOutput = template.evaluate()
    .setWidth(500)
    .setHeight(500);
  
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, title);
}

Code 시트의 B열은 펀드 대분류이며 C 열은 펀드 프로파일입니다.

- FORM에 있는 SELECT 박스에 표시하기 위해서 getRange() 함수로 지정하고 getValue() 함수를 이용 배열로 가져왔습니다.

- template의 하위 항목 fund_class로 전달하고 evaluate() 함수를 설정한 후 ShowModalDialog로 전달시키면 구글 시트 상에 NewFund.html 파일로 창이 뜹니다.

​

onOpen()에서 설정한 데이터 입력 메뉴에서 펀드 생성을 누르면 다음과 같은 창이 뜨게 되고 이제 입력만 하면 됩니다.

펀드 코드는 금융투자협회 펀드 코드로서 펀드닥터에 검색해 보면 나옵니다.

펀드 코드를 입력하고 펀드정보를 가져오기를 누르면 펀드명과 펀드닥터 분류가 채워집니다.

저장 하기를 누르면 운용목록, 전체투자목록,보유펀드기준가 시트에 행이 추가됩니다.

펀드정보 가져오기 버튼을 누르면 실시간으로 펀드닥터의 페이지에서 기준가 및 정보를 가져와서 펀드명과 펀드닥터 분류에 채워집니다.

펀드데이터를 가져오는데 getFundData 함수를 이용하는데 getFundData예제 시트를 보시면 10,11 번째 인자에서 참조하여 구합니다.

getFundData는 코드. gs의 235번 행에 있습니다.

위의 함수는 매일 오전에 발표되는 펀드데이터를 수집하는 기능도 합니다.

코드.gs 의 350번 라인에 FundDataUpdate() 함수가 그 역할을 합니다.

​

NewFund.html 파일에서는 자바스크립트 라이브러리 jQuery를 이용했습니다.

 

// NewFund.html 의 일부
// jQuery 구현 

 $(function() {
    
      $("#Save").click(postData); // postData 함수로 폼 데이터 입력 하기 
      $("#input_form").find(":input").change(function(){
          this.disabled= false;
      });      

      $("#getFundInfo").click(function(){
        $(this).prop("disabled","true"); //잠시 버튼 비활성화 
        getFundInfo($("#fundCode").val());
      });

      ................ 생략 ......................
      
  });


    function getFundInfo(code){
       google.script.run
        .withSuccessHandler(FundNameDataSuccess)
        .getFundInfo(code);
    }

    // 펀드 정보 가져와 채워 넣음 
    function FundNameDataSuccess(data,elem){
      $("#getFundInfo").prop("disabled",""); //비활성화 제거 
      $("#fundName").val(data.fundName);
      $("#fd_class").val(data.fd_class);
    }

펀드가져오기 버튼은 <button id="getFundInfo">펀드정보가져오기</button> 이렇게 되어 있는데

$("#getFundInfo").click(function(){ 실행할 코드 }) 는 id 가 getFundInfo라는 객체(버튼, HTML INPUT 박스 등등)가 클릭될 때 이벤트가 발생하고 실행할 자바스크립트 코드가 실행됩니다. 그리고 $("fundCode").val() 은 객체에 있는 값을 가져옵니다.

 

1. 잠시 버튼을 비활성화하고 펀드정보가 가져왔을 때 비활성화를 제거합니다.

2. getFundInfo 함수에 $("#fundCode").val() 를 이용 펀드 코드가 매개변수로 실행이 됩니다.

3. getFundInfo(code) 함수는 google.script.run로 구글 앱스 스크립트가 실행됩니다.

4. 코드. gs의 서버 측 getFundInfo() 함수가 실행되고

5. 펀드 데이터를 가져오는 getFundData 함수에서 펀드정보를 구한 후 자바스크립트 객체가 리턴됩니다.

코드.gs  336 라인 

/**********************************************
 * 펀드 생성 폼인 NewFund.html 85번 라인  
 * google.script.run 함수에서 실행
 * 
 */

function getFundInfo(code){
    var arr = getFundData(code);
    return { fundName : arr[9], fd_class : arr[10] };
}

위의 FundNameDataSuccess(data,elem) 함수에 있는

$("#fundName").val(data.fundName); 에서 처럼 data.fundName 으로 받아서 HTML 폼에 채워 넣습니다.

다음은 저장하기 버튼을 눌렀을 때 입력되는 서버 측 함수입니다.

/**
 * 신규 펀드 입력 루틴
 *  
 */ 
function insertFund(e){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet()

  var operList = ss.getSheetByName("운용목록");
  var fundList = ss.getSheetByName("전체펀드목록");
  var hold_fund = ss.getSheetByName("보유펀드기준가");


  var fd_class = e.fd_class; // 제로인 유형분류 
  var fundName = e.fundName; // 펀드명
  var fundCode = e.fundCode  // 펀드코드 

  var isTaxed = (e.isTaxed=="Y")?"과세":"비과세";
  var fundTargetYield = e.FundTargetYield;

  var fund_class= (fund_class =="new" && e.fund_class_new != "")? e.fund_class_new: e.fund_class;
  var fund_profile = (fund_profile =="new" && e.fund_profile_new != "")? e.fund_profile_new: e.fund_profile;
  

  operList.insertRowAfter(2);
  fundList.insertRowAfter(2);
  hold_fund.insertRowAfter(2);

  operList.getRange("2:2").copyTo(operList.getRange("3:3"));
  fundList.getRange("2:2").copyTo(fundList.getRange("3:3"));
  hold_fund.getRange("2:2").copyTo(hold_fund.getRange("3:3"));

  operList.getRange("A3").setValue('=HYPERLINK("http://www.funddoctor.co.kr/afn/fund/fprofile.jsp?fund_cd="&INDIRECT("B" & ROW()) ,"'+fundName+'")');
  operList.getRange("B3").setValue(fundCode);
  operList.getRange("H3").setValue(fundTargetYield); // 목표수익률

  operList.getRange("V3").setValue(fund_class); //투자지역
  operList.getRange("W3").setValue(e.fund_broker); //거래증권사
  operList.getRange("X3").setValue(fund_profile); // 프로파일
  operList.getRange("Y3").setValue(e.fd_class); // 제로인 투자유형분류 
  operList.getRange("Z3").setValue(isTaxed); // 과세여부


  fundList.getRange("A3").setValue('=HYPERLINK("http://www.funddoctor.co.kr/afn/fund/fprofile.jsp?fund_cd="&INDIRECT("B" & ROW()) ,"'+fundName+'")');
  fundList.getRange("B3").setValue(fundCode);
  fundList.getRange("H3").setValue(fundTargetYield); // 목표수익률

  fundList.getRange("W3").setValue(e.fund_broker); //거래증권사
  fundList.getRange("V3").setValue(fund_class); // 투자지역
  fundList.getRange("X3").setValue(fund_profile); // 프로파일
  fundList.getRange("Y3").setValue(e.fd_class); // 제로인 투자유형분류
  fundList.getRange("Z3").setValue(isTaxed); // 과세여부
 
  hold_fund.getRange("A3").setValue(fundName);
  hold_fund.getRange("B3").setValue(fundCode);

  return fundName +"가 생성되었습니다.";

}

주석을 남겨 뒀습니다. 3개의 시트로 입력이 되며 2번 라인을 복사하여 구현하므로 2번 라인은 절대 지우시면 안 되겠습니다. 모든 수식을 함수 내에 넣을 수도 있습니다.

기본적인 사항은 펀드 거래내역 입력 폼 만들기와 100% 동일합니다.  입력되는 부분을 설명해두었으니 참고하세요.

https://richardshin.tistory.com/225

펀드 거래내역 입력 폼 만들기 @구글 스프레드시트 펀드 수익률 관리

 

좀 더 자세히 알고 싶으시면 다음 구글 시트에서 데이터 입력을 위한 폼 구현 포스팅을 참고하세요.

https://richardshin.tistory.com/202

구글 시트에서 데이터 입력을 위한 폼 구현 Google Apps Script

 

웹 앱(Web App)

스마트폰에서 입력할 수 있도록 웹 앱(Web App) 도 만들었습니다. Form_Code.gs 파일의 제일 마지막에 doGet() 함수 입니다.​

폼을 생성하기전 준비 함수 인 createFundItem 함수의 내용을 doGet 에 입력 하면 WEB 으로 폼을 띄울 수 있습니다. 스마트폰에서 입력하기 위해서 입니다.

​

웹 앱 링크는 사용자 메뉴(데이터 입력)에 공지기타에 있습니다.

​