Guest User

Untitled

a guest
Jan 18th, 2019
139
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.68 KB | None | 0 0
  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. }
Add Comment
Please, Sign In to add comment