구글 스프레드 시트에서 펀드/주식등의 자산의 기간 성과를 비교하기 위한 차트 생성입니다.
노란색 셀인 시작일, 종료일, 기간을 수정하면 바로 그려 줍니다. 날짜를 선택하면 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 를 이용해서 다운 받습니다.
주식 가격임시 시트에 임시로 복사를 하고 기준가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
구글계정에 가입하고 파일메뉴의 사본만들기를 이용하여 개인 구글드라이브로 복사해서 사용하셔야 합니다.
복사하는 방법은 다음과 같습니다.
'컴퓨터 활용 > 구글스프레드시트 응용' 카테고리의 다른 글
구글 스프레드 시트에서 체크박스 다루기 1 - 전체 선택, 해제 / 구글 앱스 스크립트 (0) | 2022.08.10 |
---|---|
구글 앱스 스크립트로 차트 그리기 (0) | 2022.07.15 |
인베스팅(investing) 주가정보 수집하기 / 구글 스프레드 시트에서 사용자 정의 함수로 구현 (0) | 2022.06.01 |
폼 입력으로 신규 펀드 생성 하기 @구글 스프레드시트 펀드 수익률 관리 & 웹 앱(Web App ) 구현 (0) | 2022.03.19 |
펀드 거래내역 입력 폼 만들기 @구글 스프레드시트 펀드 수익률 관리 (0) | 2022.03.13 |