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.price);
    result[i] = info.price * values[1][i];

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:


  1. Sir can you do it with googlefinance. I have got here a link: ("SCRIPNAME").
    Please do try sir. If it is successful just post it on the comments.
    Thanking you

    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.

  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");

    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?


    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.

    2. Thanks, I'm using importdata for the source range - here's the sheet:

      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?

    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.