How to record daily portfolio values in a Google Spreadsheet

In another article I described how to automatically record the history of a row of values in a Google Spreadsheet. Essentially, it describes how to create a History sheet into which a new row is added each day capturing the values of some row that is elsewhere in the spreadsheet. I mentioned in that article that one limitation of the approach is that it only works for values and formulas that don't require the spreadsheet to be open. Specifically, you can't use that approach to record the history of values calculated using the GoogleFinance() function. This means that it can't be used to record the history of a set of portfolio values.

There is a workaround to this which involves using the Google Apps Script FinanceApp service. So if you have a spreadsheet with a set of ticker symbols and quantities, and you want to build a sheet which records a portfolio value at the close of each trading day, then read on...

If you are not familiar with the scripting capabilities in Google Spreadsheet then it might be helpful to start with the previous article.

Create a spreadsheet which has a History tab where cells B1:E1 contain ticker symbols, and the cells below those - B2:E2 - contain the quantities of each in your portfolio. Column A is left blank because it will be used to record the date of each row of values.



Now create a new blank project by visiting Tools > Script Editor and add the following function

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("History");
  var source = sheet.getRange("A1:E2");
  var values = source.getValues();
  var result = new Array(5);
  result[0] = new Date();
  for (var i=1; i<5; i++) {
    var info = FinanceApp.getStockInfo(values[0][i]);
    Logger.log('The stock %s is trading at %s', info.name, info.price);
    result[i] = info.price * values[1][i];
  }
  sheet.appendRow(result);
};

Click save in the editor and pick recordHistory in the pulldown on the toolbar before clicking the play button. The first time you run this it will prompt you that authorization is required. Click continue and then accept. If you visit the History tab you should now see a new row added with the date in the first column and the values of each stock in your portfolio alongside.

If you want this to run at the end of each day, click the Current Project Triggers button in the script editor toolbar. From there, click the link to add a new trigger. Select recordHistory in the first pull-down menu, and time-driven in the second one. Now choose Day Timer, and 9pm-10pm.

That's all there is to it. I've put this sample code in a public google spreadsheet which you can copy by clicking the link. Feel free to ask any questions below or contact me on twitter.

Update: Unfortunately the Google Finance service is deprecated and as of Oct '14 has been shut down. The code above will no longer work. I have rewritten the same code linked above in the public google spreadsheet using the Yahoo Finance URL.

Image Credit: www.paulpreacher.com

No comments:

Post a Comment