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.

Update 2: Sometime in 2017 the Yahoo Finance URL started returning 404. I have rewritten the code again to use the Google Finance URL.

Update 3: Sometime later in 2017 the Google Finance URL started failing as well. I have rewritten the code again, this time to use the Google Finance website.

Image Credit: www.paulpreacher.com

14 comments:

  1. Sir can you do it with googlefinance. I have got here a link: (http://finance.google.com/finance/info?client=ig&q="SCRIPNAME").
    Please do try sir. If it is successful just post it on the comments.
    Thanking you

    ReplyDelete
    Replies
    1. It's probably possible, but I don't have time to try. The Yahoo Finance function in the public spreadsheet linked in the article returns very similar information.

      Delete
  2. Hi Alex - thank you for this tutorial, I managed to append a range using the help you gave on the other page:

    function recordHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var inputSheet = ss.getSheetByName("Summary");
    var source = inputSheet.getRange("A2:U67");
    var values = source.getValues();
    var outputSheet = ss.getSheetByName("Log");
    outputSheet.getRange(outputSheet.getLastRow()+1,1,values.length,values[0].length).setValues(values);
    };

    But I have the same issue that it doesn't run unless the spreadsheet is open - how can I update this code so it works all the time?

    Cheers
    Michael

    ReplyDelete
    Replies
    1. It depends on what functions you are using in the spreadsheet in the source range you are trying to copy from (i.e. A2:U67 in your case). There are some spreadsheet functions which only populate when someone has the spreadsheet open. Things like IMPORTRANGE and some of the finance functions. If you're using these, then you'll need to do something like I mention in the other page and find a way to replicate the functionality inside the Google Apps Script itself. If you were doing an IMPORTRANGE, for example, then you would need to write the script to open the other spreadsheet and copy the desired value directly from that.

      Remember that even if you aren't using any of these functions directly in A2:U67, if those cells have formulas that depend upon cells where those functions are used - you'll have the same problem.

      Delete
    2. Thanks, I'm using importdata for the source range - here's the sheet: https://docs.google.com/spreadsheets/d/1rPP_ctrgzPlV1Mzql3RnUBlKakWWEtitgt1G0WBW7LM/edit#gid=0

      Then on top of that some queries and regexextract, as you can see it's quite complex so not sure how I can replicate that inside a script...any ideas?

      Delete
    3. IMPORTRANGE and QUERY will both be a problem for the approach I've described in these articles. REGEXEXTRACT should be fine I think, but haven't tested it. You're right it's not going to be simple to rewrite that functionality especially if you're not that experienced with Google Apps Script. Perhaps you should look for a freelance developer to help.

      Delete
  3. Hi Alex,

    I'm having this error 'ReferenceError: "FinanceApp" is not defined. (line 9, file "Code")Dismiss'' when i run the code. Any idea? Thanks

    ReplyDelete
    Replies
    1. Yes. See the Update at the end of the article. Unfortunately Google has deprecated this service, so the original code from the article will no longer work. I modified the sample to use Yahoo Finance at some point. If you click the link in the article for the sample code in a google public spreadsheet to make a new copy, then you should find the new code will work.

      Delete
    2. Thank you for your quick reply. I downloaded the sample and made a copy. When i try to run the script from the sample a have that error.

      ''Request failed for http://finance.yahoo.com/webservice/v1/symbols/IBM/quote?format=json returned code 404. Truncated server response: redirect (use muteHttpExceptions option to examine full response) (line 25, file "History")DetailsDismiss''

      How can i fix this? Thank you

      Delete
    3. Thanks for reporting that. Looks like sometime earlier this year Yahoo either changed or deprecated their finance service. So I've modified the code again, this time to use the Google Finance service (not the original FinanceApp which was deprecated). Seems to work now, so if you take another copy of the spreadsheet linked in the article you should have something that works.

      Delete
  4. It's working! Thank you very much. Its a really nice script to add to google spreadsheet! Really appreciated.

    ReplyDelete
  5. Everytime I attempt to run the script I receive an error stating "Invalid JSON from [url]". Is there an updated url that would resolve this?

    ReplyDelete
    Replies
    1. Apparently the Google Finance service is no longer working. I've rewritten the sample code to extract prices from the Google Finance website. If you download a new copy from the link in the article you should get the new code. Thanks for reporting it.

      Delete