Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function plotPortfolioData() {
- // get the Portfolio Chart sheet, and create it if it doesn't exist
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- var portfolioDataSetT = transpose(GetComprehensivePortfolioData(ss));
- varSheetName = "Charts";
- // IMPORTANT
- var firstIndividualStockColumn = 6; // first column in portfolioDataSetT where the actual individual holdings start
- // loop through every value in the portfolio dataset and replace 0 with a blank string (this has the effect of removing
- // any 0 quantity lines from the stacked charts).
- for (var i = 2; i != portfolioDataSetT.length; i++) // start at i = 2 to skip the header and first row (needed for charting)
- {
- for (ii in portfolioDataSetT[i])
- {
- if (portfolioDataSetT[i][ii] <= 0)
- {
- portfolioDataSetT[i][ii] = '';
- }
- }
- }
- if (!ss.getSheetByName(varSheetName))
- {
- var newSheetCreated = true;
- ss.insertSheet(varSheetName);
- var sheet = ss.getSheetByName(varSheetName);
- }
- else
- {
- var sheet = ss.getSheetByName(varSheetName);
- // remove all charts in this existing sheet
- var charts = sheet.getCharts();
- for (var i in charts)
- {
- sheet.removeChart(charts[i]);
- }
- var sheet = ss.getSheetByName(varSheetName);
- }
- // Build the data into the spreadsheet
- var sheetDataRange = sheet.getRange(1, 1, portfolioDataSetT.length, portfolioDataSetT[0].length);
- sheetDataRange.setValues(portfolioDataSetT);
- // prepare the pie chart data
- // start by listing the individual stock names vertically
- var number_of_custom_rows = 1; // the number of manual rows we will add that are not just stocks
- var pieChartSize = (portfolioDataSetT[0].length + 1 - firstIndividualStockColumn) + number_of_custom_rows; // number of holdings in the pie chart
- var pieChartFirstColumn = portfolioDataSetT[0].length+1; // just start after the data pile
- var pieChartStockTitles = sheet.getRange(1,1,1,portfolioDataSetT[0].length).getValues(); // the range of headers
- var pieChartHoldingsRange = sheet.getRange(portfolioDataSetT.length,1,1,portfolioDataSetT[0].length);
- var pieChartHoldings = pieChartHoldingsRange.getValues(); // range of holdings values
- // set the pie chart holdings range number format
- pieChartHoldingsRange.setNumberFormat("$#,###");
- var pieChartDataSpace = sheet.getRange(1,pieChartFirstColumn,pieChartSize,2); // the space where the pie chart data will be printed for graphing (2 columns for name and holding value for each stock)
- for (var i = 0; i != pieChartSize - number_of_custom_rows; i++)
- {
- pieChartDataSpace.getCell(i+1,1).setValue(pieChartStockTitles[0][firstIndividualStockColumn-1+i]); // add the name of the holding to the first pie chart column
- pieChartDataSpace.getCell(i+1,2).setValue(pieChartHoldings[0][firstIndividualStockColumn-1+i]); // add the value of the holding to the 2nd pie chart column
- }
- // now add the custom rows
- pieChartDataSpace.getCell(pieChartSize,1).setValue(pieChartStockTitles[0][4]); // add the cash header
- pieChartDataSpace.getCell(pieChartSize,2).setValue(pieChartHoldings[0][4]); // add the cash amount
- var chartHeight = 400;
- var chartWidth = 500;
- var offset = 3; // buffer region in pixels between charts and the edge of screen
- // hide the numbers for neatness:
- sheet.hideRows(1,portfolioDataSetT.length);
- // performance chart (top left)
- var performanceChart = sheet.newChart()
- .setOption('title', "Portfolio Measurements")
- .setPosition(portfolioDataSetT.length, 1, offset, offset)
- .setOption('height',chartHeight)
- .setOption('width',chartWidth)
- .setOption('lineWidth',1)
- //.setOption('vAxis.format',"$#,###")
- .setOption('vAxis.gridlines.count',10)
- .setOption('theme','maximized')
- .setChartType(Charts.ChartType.LINE)
- .addRange(sheet.getRange("A:D")) // date,cost base, cash-only,cost-base no cash
- .build();
- sheet.insertChart(performanceChart);
- // performance chart (bottom left)
- var compositionChart = sheet.newChart()
- .setChartType(Charts.ChartType.AREA)
- .setOption('title', "Portfolio Composition")
- .setPosition(portfolioDataSetT.length, 1, offset, 2*offset + chartHeight)
- .setOption('height',chartHeight)
- .setOption('width',chartWidth)
- .setOption('theme','maximized')
- .setOption('vAxis.gridlines.count',10)
- .setOption('isStacked',true)
- .setOption('lineWidth',0)
- .setOption('areaOpacity',0.8)
- //.setOption('vAxis.format',"$#,###")
- .addRange(sheet.getRange("A:A")) // dates
- .addRange(sheet.getRange("E:E")) // cash balance
- .addRange(sheet.getRange(1,firstIndividualStockColumn,portfolioDataSetT.length,portfolioDataSetT[0].length + 1 - firstIndividualStockColumn)) // add the individual stock holdings
- .build();
- sheet.insertChart(compositionChart);
- // pie chart portfolio composition (top right)
- var pieChart = sheet.newChart()
- .setChartType(Charts.ChartType.PIE)
- .setOption('title', "Portfolio Composition")
- .setPosition(portfolioDataSetT.length, 1, 2*offset + chartWidth, offset)
- .setOption('height',chartHeight)
- .setOption('width',chartWidth)
- .setOption('pieSliceText','label')
- .setOption('is3D',true)
- .addRange(sheet.getRange(1,pieChartFirstColumn,pieChartSize,1)) // add the individual stock names
- .addRange(sheet.getRange(1,pieChartFirstColumn+1,pieChartSize,1)) // add the individual holding values
- .build();
- sheet.insertChart(pieChart);
- // fix up number formatting
- var sheetWholeRange = sheet.getDataRange();
- sheetWholeRange.setNumberFormat("$#,###"); // set all numbers to currency
- var sheetDateRange = sheet.getRange("A:A");
- sheetDateRange.setNumberFormat("d-LLL-YYYY"); // set date column formatting
- }
Add Comment
Please, Sign In to add comment