daily pastebin goal
8%
SHARE
TWEET

Untitled

a guest Jan 18th, 2019 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function plotPortfolioData() {
  2.  
  3.   // get the Portfolio Chart sheet, and create it if it doesn't exist
  4.   var ss = SpreadsheetApp.getActiveSpreadsheet();
  5.  
  6.   var portfolioDataSetT = transpose(GetComprehensivePortfolioData(ss));
  7.  
  8.   varSheetName = "Charts";
  9.  
  10.   // IMPORTANT
  11.   var firstIndividualStockColumn = 6; // first column in portfolioDataSetT where the actual individual holdings start
  12.  
  13.   // loop through every value in the portfolio dataset and replace 0 with a blank string (this has the effect of removing
  14.   // any 0 quantity lines from the stacked charts).
  15.   for (var i = 2; i != portfolioDataSetT.length; i++) // start at i = 2 to skip the header and first row (needed for charting)
  16.   {
  17.     for (ii in portfolioDataSetT[i])
  18.     {
  19.       if (portfolioDataSetT[i][ii] <= 0)
  20.       {
  21.         portfolioDataSetT[i][ii] = '';
  22.       }
  23.     }
  24.   }
  25.  
  26.  
  27.   if (!ss.getSheetByName(varSheetName))
  28.   {
  29.     var newSheetCreated = true;
  30.     ss.insertSheet(varSheetName);
  31.     var sheet = ss.getSheetByName(varSheetName);
  32.   }
  33.   else
  34.   {
  35.     var sheet = ss.getSheetByName(varSheetName);
  36.  
  37.     // remove all charts in this existing sheet
  38.     var charts = sheet.getCharts();
  39.     for (var i in charts)
  40.     {
  41.       sheet.removeChart(charts[i]);
  42.     }
  43.     var sheet = ss.getSheetByName(varSheetName);
  44.   }
  45.  
  46.   // Build the data into the spreadsheet
  47.   var sheetDataRange = sheet.getRange(1, 1, portfolioDataSetT.length, portfolioDataSetT[0].length);
  48.   sheetDataRange.setValues(portfolioDataSetT);
  49.  
  50.   // prepare the pie chart data
  51.   // start by listing the individual stock names vertically
  52.   var number_of_custom_rows = 1; // the number of manual rows we will add that are not just stocks
  53.   var pieChartSize = (portfolioDataSetT[0].length + 1 - firstIndividualStockColumn) + number_of_custom_rows; // number of holdings in the pie chart
  54.   var pieChartFirstColumn = portfolioDataSetT[0].length+1; // just start after the data pile
  55.  
  56.   var pieChartStockTitles = sheet.getRange(1,1,1,portfolioDataSetT[0].length).getValues(); // the range of headers
  57.   var pieChartHoldingsRange = sheet.getRange(portfolioDataSetT.length,1,1,portfolioDataSetT[0].length);
  58.   var pieChartHoldings = pieChartHoldingsRange.getValues(); // range of holdings values
  59.  
  60.   // set the pie chart holdings range number format
  61.   pieChartHoldingsRange.setNumberFormat("$#,###");
  62.  
  63.   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)
  64.  
  65.   for (var i = 0; i != pieChartSize - number_of_custom_rows; i++)
  66.   {
  67.     pieChartDataSpace.getCell(i+1,1).setValue(pieChartStockTitles[0][firstIndividualStockColumn-1+i]); // add the name of the holding to the first pie chart column
  68.     pieChartDataSpace.getCell(i+1,2).setValue(pieChartHoldings[0][firstIndividualStockColumn-1+i]); // add the value of the holding to the 2nd pie chart column
  69.   }
  70.  
  71.   // now add the custom rows
  72.   pieChartDataSpace.getCell(pieChartSize,1).setValue(pieChartStockTitles[0][4]); // add the cash header
  73.   pieChartDataSpace.getCell(pieChartSize,2).setValue(pieChartHoldings[0][4]); // add the cash amount
  74.  
  75.   var chartHeight = 400;
  76.   var chartWidth = 500;
  77.   var offset = 3; // buffer region in pixels between charts and the edge of screen
  78.  
  79.   // hide the numbers for neatness:
  80.   sheet.hideRows(1,portfolioDataSetT.length);
  81.  
  82.   // performance chart (top left)
  83.   var performanceChart = sheet.newChart()
  84.       .setOption('title', "Portfolio Measurements")
  85.       .setPosition(portfolioDataSetT.length, 1, offset, offset)
  86.       .setOption('height',chartHeight)
  87.       .setOption('width',chartWidth)
  88.       .setOption('lineWidth',1)
  89.       //.setOption('vAxis.format',"$#,###")
  90.       .setOption('vAxis.gridlines.count',10)
  91.       .setOption('theme','maximized')
  92.       .setChartType(Charts.ChartType.LINE)
  93.       .addRange(sheet.getRange("A:D")) // date,cost base, cash-only,cost-base no cash
  94.       .build();
  95.   sheet.insertChart(performanceChart);
  96.  
  97.   // performance chart (bottom left)
  98.   var compositionChart = sheet.newChart()
  99.       .setChartType(Charts.ChartType.AREA)
  100.       .setOption('title', "Portfolio Composition")
  101.       .setPosition(portfolioDataSetT.length, 1, offset, 2*offset + chartHeight)
  102.       .setOption('height',chartHeight)
  103.       .setOption('width',chartWidth)
  104.       .setOption('theme','maximized')
  105.       .setOption('vAxis.gridlines.count',10)
  106.       .setOption('isStacked',true)
  107.       .setOption('lineWidth',0)
  108.       .setOption('areaOpacity',0.8)
  109.       //.setOption('vAxis.format',"$#,###")
  110.       .addRange(sheet.getRange("A:A")) // dates
  111.       .addRange(sheet.getRange("E:E")) // cash balance
  112.       .addRange(sheet.getRange(1,firstIndividualStockColumn,portfolioDataSetT.length,portfolioDataSetT[0].length + 1 - firstIndividualStockColumn)) // add the individual stock holdings
  113.       .build();
  114.   sheet.insertChart(compositionChart);
  115.  
  116.   // pie chart portfolio composition (top right)
  117.   var pieChart = sheet.newChart()
  118.       .setChartType(Charts.ChartType.PIE)
  119.       .setOption('title', "Portfolio Composition")
  120.       .setPosition(portfolioDataSetT.length, 1, 2*offset + chartWidth, offset)
  121.       .setOption('height',chartHeight)
  122.       .setOption('width',chartWidth)
  123.       .setOption('pieSliceText','label')
  124.       .setOption('is3D',true)
  125.       .addRange(sheet.getRange(1,pieChartFirstColumn,pieChartSize,1)) // add the individual stock names
  126.       .addRange(sheet.getRange(1,pieChartFirstColumn+1,pieChartSize,1)) // add the individual holding values
  127.       .build();
  128.   sheet.insertChart(pieChart);
  129.  
  130.   // fix up number formatting
  131.   var sheetWholeRange = sheet.getDataRange();
  132.   sheetWholeRange.setNumberFormat("$#,###"); // set all numbers to currency
  133.   var sheetDateRange = sheet.getRange("A:A");
  134.   sheetDateRange.setNumberFormat("d-LLL-YYYY"); // set date column formatting
  135. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top