구글 스프레드시트에서 주식펀드 기간 수익률 비교 차트로 그리기

구글 스프레드 시트에서 펀드/주식등의 자산의 기간 성과를 비교하기 위한 차트 생성입니다.

노란색 셀인 시작일, 종료일, 기간을 수정하면 바로 그려 줍니다. 날짜를 선택하면 DatePicker 가 나오는데 날짜를 지정하면 되겠습니다. 수기로 넣어도 됩니다.

시작일, 종료일, 기간의 값이 변경될 때마다 구글 앱스 스크립트이 onEdit 이벤트가 발생합니다.
차트그리기가 삭제되었다면 새로만들기 버튼도 만들어 두었습니다.

상단 메뉴의 확장프로그램의 Apps Script 를 선택하면 소스를 볼 수 있습니다.

/*
 * 시트가 실행될 때 
 */
function onOpen (e) {
  
  var ss = SpreadsheetApp.getActive();

   // 상단 메뉴만들기
   SpreadsheetApp.getUi().createMenu('실행하기')
      .addItem('차트만들기', 'newChart')
      .addItem('기준가2-데이터 추가)', 'updateChart1')      
      .addItem('차트 모두지우기', 'removeAllChart')            
      .addToUi();

  // onEdit 함수가 실행 되도록 트리거 만듬.
  ScriptApp.newTrigger('onEdit')
      .forSpreadsheet(ss)
      .onEdit().create(); 
}

/*  
 * 값(날짜)이 수정될 때 발생하는 이벤트 
 */

function onEdit(e) {

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var thisSheet = activeSheet.getSheetByName("그래프");
  var lastDay = thisSheet.getRange("종료일").getValue();  

  if ( e && e.range &&  e.range.getSheet().getName() === '그래프')
  {
    // 기간이 변경될 때 - 행이 2 열이 5인 Cell 이라면  
    if(e.range.getColumn() === 5 && e.range.getRow() === 2)
    {
      thisSheet.getRange("시작일").setValue( new Date(lastDay - e.range.getValue()*86400000));

      var s=thisSheet.getRange("숨겨질행1").getValue().toString();
      var p=thisSheet.getRange("숨겨질행2").getValue().toString();

      if(s=="#N/A" || p=="#N/A")
      {
        Browser.msgBox( "해당날짜의 데이터가 없습니다.");
      }
      else 
      {
        // 조건에 맞게 행을 숨깁니다. 
        hideRowsForChart(activeSheet);
      }        
      return;
    }
    
    // 날짜 변경될 때 행이 2  열이 2,4인 Cell 이라면  
    if((e.range.getColumn() === 2 || e.range.getColumn() === 4) && e.range.getRow() === 2 )
    {
      var s=thisSheet.getRange("숨겨질행1").getValue().toString();
      var p=thisSheet.getRange("숨겨질행2").getValue().toString();
      if(s=="#N/A" || p=="#N/A")
      {
        Browser.msgBox( "해당날짜의 데이터가 없습니다.");
      }
      else 
      {
        hideRowsForChart(activeSheet);
      }
    }
  }      
}

기간 선택에서 일 수를 입력하면 시작일이 정해집니다. 30일 전이 휴일일 수도 있으므로 에러가 발생할 수 있습니다.
※ 데이터가 없으면 없다고 나옵니다. 그럴 때는 앞 뒤로 하루 이틀 조정하면 됩니다.

펀드/주식 일별 가격 입력

기준가 쉬트에 차트를 그릴 데이터가 입력되어 있습니다.
펀드의 기준가가 아니더라도 주식등 기타자산도 됩니다. 날짜가 일치한다면 일별 기준가(가격)가 서로 달라도 3번째 열에서 그 기능을 합니다.

=B236/OFFSET( B$2,'그래프'!$K$2-2,0,1,1)-1

3번 째 열의 수식입니다. OFFSET 함수를 이용 시작일의 기준가를 얻습니다. 금일 기준가 : B$2셀 / 시작일의 기준가 -1 를 하면 시작일 부터 일별수익률이 계산되고 이를 그래프로 그립니다.

 

펀드/주식 기준가 추가하는 방법 

데이터를 추가하고 싶으면 B,C 열을 복사하여 뒷 부분에 추가(붙이기)하면 됩니다.  추가하면 구글 앱스 스크립트에서  getDataRange() 함수로 Data가 있는 셀의 영역을 취합니다.

 

 
주식 가격은 인베스팅에서 historical data 를 이용해서 다운 받습니다. 

 

주식가격 : 삼성전자

https://www.investing.com/equities/samsung-electronics-co-ltd-historical-data

 

주식가격 : KODEX 200

https://www.investing.com/etfs/samsung-kodex-kospi-200-securities-historical-data

주식 가격임시 시트에 임시로 복사를 하고  기준가2 에서 VLOOKUP 함수로 해당 일자의 주식가격을 검색합니다.

구글 앱스 스크립트로 차트 그리기 구현하기

차트로 그릴 데이터영역은 A 열 , C 열, E열, G 열 입니다. 아래에 주석 처리 되어 있는 것 처럼 C열 부터 addRange 함수로 수기로 입력해도 되지만 추가될 데이터를 감안해서 실제 데이터 영역이 모두 차트에 추가 되도록 DataRange 함수를 이용하였습니다.

/* 
 * 새 차트 만들기 
 */
function newChart() 
{
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var graphSheet = activeSheet.getSheetByName("그래프");
  
  var thisSheet = activeSheet.getSheetByName("기준가");
  var o = thisSheet.getRange("숨겨질행1").getValue();
  
  var allRange = thisSheet.getDataRange();

  var maxC = allRange.getNumColumns(); // 데이터영역 열수 
  var maxR = allRange.getNumRows(); // 데이터영역 행수 
    
  var chart = graphSheet.newChart()
  .addRange(allRange.offset(0,0,allRange.getNumRows(),1)) // 날짜 
  //.addRange(allRange.offset(0,2,allRange.getNumRows(),1)) // 펀드1 펀드갯수가 한정되어 있으면 개별추가 가능 
  //.addRange(allRange.offset(0,4,allRange.getNumRows(),1)) // 펀드2
  //.addRange(allRange.offset(0,6,allRange.getNumRows(),1)) // 펀드3 
  .setNumHeaders(1)
  .asLineChart()
  .setOption('title','펀드 비교')
  .setOption('textStyle.color', '#000000')
  .setOption('titleTextStyle.color', '#757575')
  .setOption('height', 600)
  .setOption('width', 1024);

  // getDataRange()를 이용하여 시트의 데이터의 모든 펀드 포함 동적 추가 
  for(var i=2;i<maxC;i+=2){   
    chart.addRange(allRange.offset(0,i,maxR,1)); 
  }

  // 차트 정보 얻기 차트 하단 정보 
  var charts = graphSheet.getCharts();
  var r = 4;
  if(charts.length > 0)
  {
      var containerInfo = charts[charts.length-1].getContainerInfo();
      var r = containerInfo.getAnchorRow();
      r += 30;
  } 
  
  // 차트 위치 선정 
  chart.setPosition(r, 2 , 12, 12);
  graphSheet.insertChart( chart.build());
  
}

차트를 그리기위한 기본 정보 미리 설정하고 addRange() 함수로 뒤에 정보를 입력하는 방식입니다.

  for(var i=2; i < maxC; i+=2 ){   
    chart.addRange(allRange.offset(0,i,maxR,1)); 
  }

차트를 그릴 데이터 추가는 addRange (차트영역) 으로 지정할 수 있는데 offset 함수를 이용하여 영역을 취하고
데이터가 입력 된 열을 찾은 다음 for 문으로 루프를 돌려 추가하는 방법입니다.
계속 데이터가 추가 되더라도 소스를 수정하지 않고 자동으로 그려 줍니다.

OFFSET 함수 사용법은
allRange.offset(0,0,allRange.getNumRows(),1) 에서 매개변수로 offset( 기준열, 기준행, 행 수,열 수) 인데 0,0 인 allRange의 왼쪽 상단 모서리 부터 데이터가 있는 영역의 제일 하단 그리고 1개 열이 영역으로 잡힙니다. 1열은 날짜이고 2열은 기준가 데이터 인데 실제 그래프를 그리는 열은 3열,5열 홀수 열입니다. offset에서는 배열인자가 0 부터 시작합니다.
위와 기준가 시트와 같이 행이 숨김 처리가되면 차트에서 표시가 되지 않습니다. 기간 별로 차트를 그리기 위해서 hideRowsForChart 함수에서 행을 숨김처리하여 그래프를 그립니다.

숨겨질행1, 숨겨질행2 는 숫자로서 시작일의 행 번호 , 종료일의 행 번호를 찾습니다. 그리고
thisSheet.hideRow(thisSheet.getRange(2,1,o-2)); 에서 시작일 이전, 종료일 이후의 행을 숨김처리 하는 명령입니다.
시작일의 수익률은 0 으로 초기화 되고 시작일 부터 종료일 까지 수익률을 새로 계산합니다.
그리고 그 데이터를 바탕으로 차트를 그리는 것이지요.

/*
 * 시작일, 종료일에 맞게 행을 숨긴다.
 */
function hideRowsForChart(ss){

  if(!ss) ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var thisSheet = ss.getSheetByName("기준가");
  var thisSheet2 = ss.getSheetByName("기준가2");
  // 숨겨진행 모두 보이기 
  thisSheet.showRows(1,thisSheet.getMaxRows());
  thisSheet2.showRows(1,thisSheet2.getMaxRows());

  var o = thisSheet.getRange("숨겨질행1").getValue();
  var p = thisSheet.getRange("숨겨질행2").getValue();

  // 숨겨질행1의 행 숨기기  ( Row:2 , Column:1 , Row수:숨겨질행1걊-1 
  thisSheet.hideRow(thisSheet.getRange(2,1,o-2));
  thisSheet2.hideRow(thisSheet2.getRange(2,1,o-2));
  
  // 숨겨질행2의 행 숨기기 ( Row:숨겨질행2+1 , Column:1 , Row수:마지막행-숨겨질행2걊
  thisSheet.hideRow(thisSheet.getRange(p+1,1,thisSheet.getLastRow()-p));
  thisSheet2.hideRow(thisSheet2.getRange(p+1,1,thisSheet2.getLastRow()-p));
}

예제 스프레드 시트 접속 하기 

https://docs.google.com/spreadsheets/d/1spjsP45-Jcichu-YD9_bB2WewlVgh6U3_IvCgDQuqlw/edit?usp=sharing

수익률 기간 비교 차트

 

구글계정에 가입하고  파일메뉴의 사본만들기를 이용하여 개인 구글드라이브로 복사해서 사용하셔야 합니다.
복사하는 방법은 다음과 같습니다. 

스프레드시트 복사하기