Home » Adding historical graphs to the web’s best stock portfolio spreadsheet

Adding historical graphs to the web’s best stock portfolio spreadsheet

If you’re looking for a spreadsheet to accurately track the performance your stock/equity portfolio look no further than Inve$tment Moats Stock Portfolio Tracker.

This post is about how to add historical tracking graphs to your stock portfolio tracker spreadsheet, and assumes you have made your own copy of the spreadsheet above into your own Google Sheets account.

  1. Insert a new sheet in the spreadsheet. Call it History.
  2. In Row 20, enter the following column headers:
    Date Stock Summary USD Stock Summary (local)
  3. In cell A21, enter today’s date.
  4. In cell A22, enter the value =‘Portfolio Summary’!D5
  5. In cell A23, enter the value =‘Portfolio Summary’!D4
  6. Select cell range A20:B21, and select Insert > Chart
  7. Choose Line Chart, under Chart Types.
  8. Select the Customize tab, and enter a title for your graph
  9. Click Insert
  10. Drag the chart into position at cell A1
  11. Select cell range A20:A21 and C20:C21
  12. Choose Line Chart, under Chart Types.
  13. Select the Customize tab, and enter a title for your graph
  14. Click Insert
  15. Drag the chart into position next to the first chart
  16. Now comes the automation:
  17. Select Tools > Script Editor
  18. At the bottom of the code on the right, at line 25, enter the following code:
    function recordHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Stock History");
    var source = sheet.getRange("B21:C21");
    var values = source.getValues();
    values[0][2] = values[0][1];
    values[0][1] = values[0][0];
    values[0][0] = new Date();
    sheet.appendRow(values[0]);
    };
  19. Click Save
    google sheets script editor save
  20. Select recordHistory from the list of functions
    google sheets script editor select function
  21. Select the Trigger’s button:
    google sheets script editor timer
  22. Ensure recordHistory runs every midnight
    google sheets script editor every midnight
  23. Click Save again, and you’re finished.

Any problems or issues, let me know by leaving a comment.