Portfolio tracking spreadsheet: v2.0 release notes

This post is a sequel to my Investment Tracking Spreadsheet post of three years ago.  A number of readers have found the spreadsheet (‘example portfolio returns tracker’) outlined in that post helpful, so it’s time to bring it up to date. 

Since I wrote that post I have in fact been using a Google Sheet as my ‘day to day’ portfolio tracking tool, which gives me live prices on most of my holdings.  My tracking Google Sheet uses the very same FvL example workbook as its master ‘database’, so the security data (tickers, expense ratios, etc) is updated fairly regularly. 

I have taken advantage of the long Easter weekend to release an updated public share tracking spreadsheet (here) which supports live prices.  The difficult bit is that it handles both equities (via GoogleFinance) and also funds (as listed on Hargreaves Lansdown). 

The spreadsheet is still read only, but you can make a copy (either download a copy in Excel, or make a copy in Google Sheets) to edit yourself and make your own portfolio tracking tool.  All appropriate disclaimers apply – use at your own risk.

Full release notes are as follows:

  • Updated statement formats.
    • ii / TD have now integrated their platforms. I have removed the TD input sheet and the ii sheet now uses their latest format (in fact the old TD format).  
    • EQi is the new name for Selftrade.  Their downloadable format has changed too – I have updated the input sheet.
    • Fidelity have updated their download format – this is updated.
  • Live share prices.
    • The sheet uses GOOGLEFINANCE() to pull in live prices for equities.  Googlefinance tickers are by default USA tickers, but it supports other exchanges too with the appropriate prefixes.  
    • I lean on Hargreaves Lansdown for Fund prices.  This is something of a hack, and HL’s website does not always play ball.  But it is a lot better than nothing. Note that I tend to use Income units, not Accumulation units, so my ‘ticker’ is usually the Income version.
    • Google/Hargreaves use arbitrary, and inconsistent units – between pounds and pence in particular.  There is a ‘scaling factor’ for each security that corrects this for; usual values are 1,  0.01 or 0.0001.  There is a manual hack for when holdings appear to be too large – above the max_unscaled parameter, set on the ‘0: Forex / assumptions’ tab (currently £100k).  
    • As live prices are somewhat unreliable, the spreadsheet falls back to the downloaded statement valuation if it has to.
  • The status check on the master tab (‘3: Integrated – live’) now works slightly differently.  It used to check that the master sheet corresponded exactly to the various input sheets.  Now it flags where the discrepancy between the master sheet (with live prices) and the input sheet exceeds a margin (currently 2.5%, set on the ‘0: Forex / assumptions’ tab).
  • FTSE-100 index over time, in the ‘over time’ tab, is now pulled in automatically via GoogleFinance.  
  • The returns tab (‘5: Returns’) now shows returns up to the penultimate row – i.e. the latest ‘historic’ datapoint.  This ignores the current, live data which is often (e.g. on weekends) not valid.
  • There is some additional meta data for most securities, which attempts to categorise securities into Monevator’s TA’s typology of tax status (from a UK resident’s point of view).  I haven’t found this that helpful but in case you do, enjoy. 

Many of these updates are visible in the screenshot below, taken from the ‘3: Integrated – live’ tab:

Screenshot from Returns tracker workbook

11 thoughts on “Portfolio tracking spreadsheet: v2.0 release notes”

  1. Impressive effort and I’m sure there are many hours of love and care gone into it and thanks for sharing it. I spent a long time working on a similar endeavour a few years ago, before biting the bullet and signing up to Bloomberg primarily for portfolio tracking. I’m sorry to say, it was only when I really dug deep into it I realised how inaccurate my excel version was. As a single example, to get real accuracy you have to account for a dividend on the day it goes XD. The difference I found from that one small error I was making had a massive impact on the difference to the real return. And that is just one example of many that all contribute to a major difference to a real return. For most people this kind of obsession with detail is ridiculous, but it is worth noting that from the work I have done most peoples estimates of returns are actually far from the reality.
    Best wishes,

    Liked by 1 person

    1. Fascinating, thanks for your reply Paul. My version reconciles to the period statements I get, so frankly that is accurate enough for me (and I am pretty sure, 90%+ of my readers). I always figure that if my month end numbers are a bit off /stale (/spiky due to XD issues) then that will catch up the following month. The thing that really matters is having my Quantities up to date, which I am pretty hot on. And with over 7 years’ track record the odd bit of daily turbulence is smoothed out.

      Liked by 1 person

  2. Pretty impressive sheet. I have something much simpler in Google Sheets.

    I notice you are using IMPORTXML to get the fund prices from HL. I used to do this, but I found this often failed to load the fund prices due ta Google restriction. I now use a Google Sheets script to do this, and it seems to work better. Might be worth trying if you have the same problem.


      1. // hl_price function to extract share prices for OEIC / UT from Hargreaves Lansdown webpage
        // Created because google IMPORTXML function wasn’t loading
        // See the following link for a problem description
        // https://support.google.com/docs/forum/AAAABuH1jm0YMhLzXEfwkI/?hl=en
        // Code obtained from
        // https://stackoverflow.com/questions/39014766/to-exceed-the-importxml-limit-on-google-spreadsheet/48483734#48483734
        // Also see following link for a slightly different approach using xml instead of regex to get the price
        // https://stackoverflow.com/questions/41253339/trouble-with-importxml-on-google-sheets/41275946#41275946

        function importRegex(url, regexInput) {
        var output = ”;
        var fetchedUrl = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
        if (fetchedUrl) {
        var html = fetchedUrl.getContentText();
        if (html.length && regexInput.length) {
        output = html.match(new RegExp(regexInput, ‘i’))[1];
        // Grace period to not overload
        // Utilities.sleep(1000);
        return output;

        function hl_price(url) {

        // regex regular expression to extract the bid price
        // HL price looks like this
        // Sell:30,056.71p
        var bid_price_regex = ‘Sell:([0-9,]*\.[0-9]+[0-9]+)p’;

        // Grace period to not overload

        return importRegex(url, bid_price_regex);

        // test function – run with Run->Run function->run_test, then look at View->Logs to see output
        function run_test() {

        url = “https://www.hl.co.uk/funds/fund-discounts,-prices–and–factsheets/search-results/v/vanguard-global-small-cap-index-accumulation”


        price = hl_price(url);


        Liked by 1 person

  3. I wimped out and use morningstar instead- the free portfolio tracker actually seems ok- although they recently changed the layout for mobile and it barely works at all. I suppose it stops me looking at it all the time now though.

    Liked by 1 person

  4. After rebuilding my own portfolio tracker ‘stealing’ some ideas from yours, which was much appreciated. I now have the ability to track and update my portfolio at the click of a button. Can your provide the rational behind your calculation of the Sharpe Ratio with

    =12/sqrt(12)*average(‘4: Over time’!V8:V59)/stdevp(‘4: Over time’!V8:V59)

    How does this pull in the returns for lowest risk returns? Or is it some kind of simplified Sharpe Ratio.


    1. Hi Robert. My Sharpe ratio is annual, and simplified to the extent that I am ignoring Rf, the risk-free rate. If you research converting from monthly returns to annual you will see that the formula is basically multiple by sqrt(12), which is the same as multiplying by 12 and dividing by sqrt of 12. In essence you get from average monthly returns (R) to annual annualised by multiplying by 12, and you get from monthly standard deviation (V) to annual standard deviation by multiplying by square root of 12. Formula is R/V.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s