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.

Update 4: Sometime in early 2018 the Google Finance website started failing as well. I have rewritten the code again, this time using barrons.com. A side effect of this is you need to include the exchange with the ticker, and they need to be lower case. So where you might previously lookup "IBM" it will now need to be "xnys/ibm". You can work out the exchange names by searching on barrons.com for a ticker, and then looking at the URL.

Image Credit: www.paulpreacher.com

26 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
  6. Hi Alex, thanks for your code and your commitment to this thread for almost three years! I'm also getting the #N/A error code. The cells that the script should be copying are vlookup formulas but they are looking within a table that includes Google Finance data - specifically currency exchange data. Your google finance work around only seems to work for share prices. Any thoughts on how I might get a working solution?

    ReplyDelete
    Replies
    1. If there's a page on finance.google.com that reports the currency exchange data you want, then you might be able to adjust the lookupTicker() function in the latest update to the google sheet that I link. Basically it fetches the html from a given url (currently it constructs the url for a particular ticker, but you'll need to change the url to be whatever page you want) and then it uses a regular expression to extract the particular price. If you're not familiar with regular expressions then sites like regex101.com are a good starting place where you can paste in an example regex (like the ones in my code) and sample html (fetched from a browser using "Show Page Source" or similar).

      Delete
  7. Thank you for posting this. I have been looking for a way to do this literally for years. I am new to Google App Scripts so am unfamiliar with the API. In particular, I am trying to figure out exactly what is going on in the call to regExtract(). I understand that URLFetchApp is getting the content of the page at the target URL. I am unsure of what the getContextText() method does with that data, but suspect it turns it from HTML into some string that you can slice and dice. Further, I am unsure of how the second argument to your regExtract function works. Looks like some sort of regular expressionish sort of thing, but it is not apparent to me what fields it is pulling out of out of the first argument nor how one would know exactly where in a blob returned by getContextText() that the data one is looking for might be located. Can you elaborate for the slow kid in the class?

    On a related note, does the google finance page allow one to specify a previous date in the URL so I could generate historical data for the period of time I have not had this wonderful way of collecting daily data points at closing time?

    ReplyDelete
    Replies
    1. Thanks, Travis. As you guessed getContentText() returns the html from the response object. And regExtract is a helper function I wrote which takes the regular expression passed as the second parameter and returns the first string in the html which matches the regular expression. So where it says:
      regExtract(s, /{span class=pr}([^{]*)/)
      It is taking the html and looking for the first occurence of {span class=pr} (I've had to change < and > into { and } otherwise I can't post it as a comment in blogger) and then reading everything after that until it reaches the first case of "<" (i.e. the end of the tag). Sites like https://regex101.com are useful when you are trying to understand a regular expression. You can paste the regular expression at the top, and paste in the html of the page below (which you can get from your browser) and see how it works.

      I'm not sure if the site lets you specify a date in the url. If you can find a way to do it through the browser and can check if there is a date as a get parameter in the url, then it should work from a script.

      Delete
  8. Hello Alex. First of all, thanks at all for sharing this, it is extremely helpful. Lately I am getting an error when triggering the script, this is what I get:

    Your script, recordHistory, has recently failed to finish successfully. A summary of the failure(s) is shown below. To configure the triggers for this script, or change your setting for receiving future failure notifications, click here.

    The script is used by the document GLOBAL.

    Start Function Error Message Trigger End
    4/6/18 8:13 PM recordHistory Invalid response from https://finance.google.com/finance?q=SWX:SBEME-EUR (line 62, file "Code") time-based 4/6/18 8:13 PM
    Sincerely,

    Google Apps Script

    I was wondering if this has to do just with my particular sheet or if the script stopped working. Thanks a lot from Spain!

    ReplyDelete
    Replies
    1. Hi - yes, unfortunately the Google Finance website has been changed and it no longer seems possible to get a stock price from it. I haven't found an alternative web service or web site that I can easily get this from. If anyone can suggest a web site or service which provides free (delayed) stock prices without needing an API key (so it's easy to include in sample code) then I'll update the script.

      Delete
  9. I use https://www.investing.com/ with the function "=importxml" in my sheet to import some data on currency forward rates and works fine, but I am not sure if this means what you say about the API , I am illiterate regarding to coding to be honest.

    ReplyDelete
    Replies
    1. That might work. Looks like https://www.investing.com/equities/ibm shows a stock price for IBM. I'll see if I can extract the stock price in code from that page. Only thing is I see one of your example tickers was "SWX:SBEME-EUR" and I can't find that price on investing.com - going to https://www.investing.com/equities/SWX:SBEME-EUR doesn't work. ideally I need a site or service where I can construct the URL from the ticker symbol somehow.

      Delete
  10. Tickers may differ from the ones used by google finance, but i do not think if you plan to use US stocks you´ll have much of a problem, for me in Europe it is harder as if I do not specify the market in which they trade if tickers coincide, they´ll usually show the US stock. Also I use ETFs for my portfolio, so i guess I have to write it as https://www.investing.com/etfs/...

    ReplyDelete
    Replies
    1. Ok, couldn't get it working with investing.com but seem to have it working now with barrons.com. If you download a new copy of the spreadsheet and script you should find it works (for now). The tickers must be specified in lower case and include a market. So instead of "IBM" it would now be "xnys/ibm". Hope it works for you.

      Delete
  11. Hi Alex,

    I've used some of your google scripts before. I am a complete novice at this stuff. I have a script to copy a line and paste it on the first blank line as values (not formulas), but it seems to be pasting the formula because I get #REF errors when it runs. Can you help me with this script? Thanks!!

    function recordHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("History");
    var source = sheet.getRange("A2:BA2");
    var values = source.getValues();
    sheet.appendRow(values[0]);
    }

    ReplyDelete
    Replies
    1. The script looks fine. It would paste #REF if the cell it's reading has a formula which showed #REF when it was read. So, for example, if the formula had a problem at the time your script ran, then the value read from the cell would be "#REF" and it would write this to the new row.

      Delete