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

펀드 매수/매도 내역을 입력을 수식 복사 및 정보 변경없이 간편하게 입력하도록 만들었습니다. Google Apps Script를 이용한 구글 스프레드 시트 내에서 동작하는 애플리케이션입니다. 개인적으로 만든 부분을 공개용 버전에도 기능 추가한 것이고요. 펀드 거래는 많지 않아서 수작업에 의존했는데 그래도 입력 폰이 있으면 쉽게 입력을 할 수 있습니다.

​구글 시트 메뉴에 보면 데이터 입력에 매수/매도 내역 입력이라는 하위 메뉴를 하나 만들고 실행시키면 다음과 같은 다이얼로그 창이 뜹니다. 여기서 펀드 매도 / 매수 정보를 입력하고 저장하기를 누르면 매수 내역, 매도 내역 시트에 등록이 됩니다.

매수내역 시트

메뉴 추가 및 입력 폼 만드는 방법은 다음 포스팅에서 아주 옛날에 설명을 해드렸습니다.

 

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

간략히 설명을 드리면 menu.gs 파일에 onOpen() 함수에 .addItem('매수/매도내역입력', "showInputForm") 를 등록시키면 메뉴에 등록이 됩니다.

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();​

showInputForm이라는 함수를 만들어야 하는데요.  이 함수는 구글 스프레드시트 상에서 입력 폼(FORM)을 띄워주는 역할을 합니다. 폼 띄우면서 시트의 정보를 HTML 폼으로 전달하기도 합니다.

아래에서는 운용_펀드명, 운용_펀드 코드, 운용_현재 기준가 영역을 배열로 통째로 넘겨 줍니다.

/* 
 * 매수/매도내역 입력 폼
 */

function showInputForm(){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var buySheet = ss.getSheetByName("매수펀드");
  var 운용목록 = ss.getSheetByName("운용목록");
  var codeSheet = ss.getSheetByName("Code");
  
  // 투자 펀드명 
  var sName = 운용목록.getRange("운용_펀드명").getValues();
  var sCode = 운용목록.getRange("운용_펀드코드").getValues();
  var sValue = 운용목록.getRange("운용_현재기준가").getValues();
 
  var fund_broker =  codeSheet.getRange("증권사명").getValues(); // 증권사 
  
 
  var template = HtmlService.createTemplateFromFile("Form.html");
  template.fundNames = sName;
  template.fundCodes = sCode;
  template.fundValues = sValue;
  template.broker = fund_broker;
 
  var html = template.evaluate().setWidth(600).setHeight(450);
  title ="펀드 매수/매도 입력";
  SpreadsheetApp.getUi().showModalDialog(html, title);
}

Form.html이라는 웹 페이지를 만들었는데요.

fundNames , fundValues , fundCodes 매개변수는 다음과 같이 넘어오게 되고 다음과 같이 SELECT 박스에 값을 표시하는 데 사용됩니다.

 <select name="fundName" id="fundName">
   <option value=""> 선택 </option>
   <? for (var i in fundNames) { ?>
   <option value="<?= fundNames[i]?>" price="<?= fundValues[i]?>" 
     fundcode="<?= fundCodes[i]?>"><?= fundNames[i]?></option>
   <? } ?>
  </select>

Form.html 파일

Form.html의 첫 부분은 jQuery 구현 및 Css 라이브러리를 포함하고 있으며 jQuery combobox 위젯 설정 등 자세한 내용은 주석으로 남겨 두었습니다.

​매수매도 라디오 버튼은 jQuery UI의 radio 위젯을 펀드명은 combobox 위젯, 날짜는 datepicker 을 이용하였습니다. 
저장하기 버튼을 누르면 $("#Save").click(postData); 이 동작 되게 되어 postData 함수가 실행됩니다.

function postData() 
{
  
  $("#error,#success").remove(); // 에러, 성공 메시지 삭제 
  this.disabled = true; // 
  
  // 입력폼이 채워져 있는 지 확인 하고 비워져 있으면 입력 재확인  
  if(!$("#fundName").val()){ 
    checkField('펀드명을 입력하세요',$("#fundName") ); 
    return false; 
  }
  if(!$("#UnitPrice").val()){ checkField('펀드기준가를 입력하세요',$("#UnitPrice")); return false; }
  if(!$("#UnitQty").val()){ checkField('좌수를 입력하세요',$("#UnitQty")); return false; }

  // 핵심 함수 
  google.script.run
  .withSuccessHandler(showSuccess) // 성공하면 showSuccess 실행 
  .withFailureHandler(showError)   // 실패하면 showError 실행 
  .withUserObject(this)// showSuccess 함수에 2번째 인자로 Save 버튼을 사용하겠다 그래서 showSuccess 에 elem 이 넘어갑니다
  .writeBuySell(this.form);
  
  // Form_Code.gs 에 서버측 writeBuySell() 함수가 실행 되고 return 받는 문자열이 showSuccess , showError 의 msg 로 전달 
}

// 서버측 함수에서 리턴된 메시지(에러) 표시 
function showSuccess(msg,elem) {
  elem.disabled = false;
  var div = $('<div id="success"><font color="green">' + msg + '</font></div>');
  $(elem).after(div);
  
  //setTimeout("google.script.host.close();",1000);
}


function showError(msg, elem) {
  elem.disabled = false;
  var div = $('<div id="error" class="error">' + msg + '</div>');
  $(elem).after(div);
}


function checkField(msg,elem) {
  var div = $('<div class="success"><font color="red">' + msg + '</font></div>');
  elem.after(div);
  $("#Save").prop("disabled",false);
}

위에 HTML 문서에 있는 핵심 함수 google.script.run 함수는 다음을 참고하시면 됩니다.

https://developers.google.com/apps-script/guides/html/reference/run

Class google.script.run (Client-side API)  |  Apps Script  |  Google Developers

 

writeBuySell 함수가 마침내 폼에 입력된 정보를 가지고 시트에 입력하는 등 구글 시트 내에서 처리를 해줄 텐데요.  writeBuySell 함수의 매개변수 e 에 폼에서 입력된 정보가 넘어오게 됩니다. 
즉, e.fundName 변수에 <input name="fundName"> 입력 박스의 입력된 값이 넘어옵니다.

function writeBuySell(e)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var buySheet = ss.getSheetByName("매수내역");
  var sellSheet = ss.getSheetByName("매도내역");
  var fundList = ss.getSheetByName("운용목록");
  var tradeLog = ss.getSheetByName('거래기록');
  var sNameArr = fundList.getRange("운용_펀드명").getValues();

  var fundName = e.fundName;
  var fundCode = e.fundCode;
  var unitPrice = e.UnitPrice;
  var unitQty = e.UnitQty;
  var broker = e.fund_broker;
  var commissiontax = e.commissiontax;
  var buyOrSell= e.buyOrSell;
  
  var inx = 0, c= 0, row = 0;
  var d = new Date(e.s_date); 
  var d2 = new Date(e.t_date); 
  
  var toDay = Utilities.formatDate( d ,"GMT+9","yyyy-MM-dd"); // 거래일
  var tDay = Utilities.formatDate( d2 ,"GMT+9","yyyy-MM-dd"); // 매입기준일 

  for(var i = 0 ; i< sNameArr.length; i++)
  {
    if(sNameArr[i] == fundName ){ row = i+1;  inx = i; c++;   break;    }  
  }
  
  if(row == 0) { Browser.msgBox(fundName + "은 없습니다. 종목을 등록해주세요." );  return; }
  if(c > 1   ) { Browser.msgBox(fundName + "이 두개가 존재 합니다." );  return; }


  if( buyOrSell =="매수")
  {

    buySheet.insertRowAfter(2); // 2행 뒤에 빈행 삽입
    buySheet.getRange("2:2").copyTo(buySheet.getRange("3:3"));
    // 2행의 내용을 3행에 복사 - getRange(2:2) 
    // 2행의 영역을 잡아서 buySheet 의 3행영역에 CopyTo

    buySheet.getRange("A3").setValue(toDay);
    buySheet.getRange("B3").setValue(fundName);
    buySheet.getRange("C3").setValue(unitQty);
    buySheet.getRange("D3").setValue(unitPrice);
    buySheet.getRange("F3").setValue(commissiontax);
    buySheet.getRange("I3").setValue(tDay);
    buySheet.getRange("J3").setValue(broker);
    buySheet.getRange("M3").setValue(e.BalanceQty);

    //buySheet.getRange("L3").setValue(fundCode);
    //buySheet.getFilter().sort(1, false);
  }
  else 
  {
   
    sellSheet.insertRowAfter(2);
    sellSheet.getRange("2:2").copyTo(sellSheet.getRange("3:3"));

    sellSheet.getRange("A3").setValue(toDay);
    sellSheet.getRange("B3").setValue(fundName);
    sellSheet.getRange("C3").setValue(unitQty);
    sellSheet.getRange("D3").setValue(unitPrice);
    sellSheet.getRange("F3").setValue(commissiontax);
    
    sellSheet.getRange("H3").setValue(broker);
    sellSheet.getRange("J3").setValue(tDay);
    sellSheet.getRange("K3").setValue(e.BalanceQty);
  }
 
  return fundName + " " + unitPrice +" " + unitQty +" 좌수 " + buyOrSell + "입력이 성공했습니다.";
}

위 함수의 처리 방식은 2행의 수식과 값을 복사하고 필요한 열의 데이터를 치환하는 방식입니다.

buySheet.insertRowAfter(2);
2행 뒤에 빈 행 삽입

buySheet.getRange("2:2").copyTo(buySheet.getRange("3:3"));
2행의 내용을 3행에 복사 - getRange(2:2) 2행의 영역을 잡아서 buySheet의 3행 영역에 CopyTo

이렇게 구현한 후의 단점은 2 행을 삭제하면 동작하지 않는다는 것인데요. 위 방식으로 처리하지 않으려면 매수/매도 내역 시트에 입력될 행의 수식을 모두 함수에 넣어야 하는데 수식이 많아지면 입력 속도도 늦어지고 귀찮아집니다. 그래서 위 방식으로 구현했습니다.
모든 수식을 따로 입력하고 싶으면 Form_Code.gs 파일에 writeBuySell2 함수를 만들어 두었습니다.

 var r = 3;
  // r = buySheet.getLastRow()
  var newLine = new Array();
  if( buyOrSell =="매수")
  {
    newLine.push(toDay); // 금일 날짜  
    newLine.push(fundName); 
    newLine.push(unitQty); 
    newLine.push(unitPrice); 
    newLine.push("=D"+r+"*ROUNDDOWN(C"+r+"/1000,3)"); 
    newLine.push(commissiontax); 
    newLine.push("=E"+r+"-F"+r); 
    newLine.push("연금펀드"); // 프로파일  
    newLine.push(tDay); 
    newLine.push(broker); 
    newLine.push(""); 
    newLine.push(fundCode); 
    newLine.push(e.BalanceQty); 

    newLine.push("=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(펀드매수좌수*(매수펀드코드=L"+r+")*(펀드매수일자<=A"+r+"))-SUM(펀드매도좌수*(매도펀드코드=L"+r+")*(펀드매도일자<=A"+r+"))), 1, 1)");
    newLine.push("=N"+r+"*D"+r+"/1000");
    newLine.push("=INDEX('보유펀드기준가'!M:M,MATCH(L"+r+",'보유펀드기준가'!B:B,0))");
    newLine.push("=S"+r+"*C"+r+"/1000");
    newLine.push("=(Q"+r+"-E"+r+")/E"+r);
    newLine.push("=INDEX('보유펀드기준가'!C:C,MATCH(L"+r+",'보유펀드기준가'!B:B,0))");
   
    buySheet.insertRowBefore(r).getRange(r, 1, 1, newLine.length).setValues([newLine]); 

  }

 

위와 같이 newLine이라는 배열에 수식을 일일이 넣는 방식으로 구현해야 합니다.

3 행에 입력되므로 r 변수에는 3이 들어갑니다.  시트상에 L3이라는 수식이 있으면 모두 L"+r+"으로 치환하여 주어야 됩니다. 마지막행에 추가하고 싶으면 r = buySheet.getLastRow() 로 바꿔 주면 됩니다.

​buySheet.insertRowBefore(r).getRange(r, 1, 1, newLine.length).setValues([newLine]);
3행 전에 빈 행을 삽입하고 값이 입력될 영역을 취한 다음 newLine 배열 변수의 값을 설정합니다. newLine 배열에 [ ]를 씌워서 값을 getRange로 설정된 영역에 설정해 버리면 라인이 삽입됩니다

 

구글 스프레드시트로 하는 펀드 수익률 관리는 최초 작성한 다음 포스팅을 참고하세요.

 

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

 

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

구글스프레드시트 주식 수익률 관리 - 최종 수정 사항