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’ 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 sheet (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 sheet 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.  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

9 Comments on “Portfolio tracking spreadsheet: v2.0 release notes”

  1. dearieme says:

    That’s all very well but your spreadsheet is backward-looking. It’s a forward-looking one I want. Get on with it, man!

    Liked by 1 person

  2. Paul Hawkins says:

    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

    • 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

  3. Andy says:

    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.


  4. southwalesfi says:

    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

  5. […] Portfolio tracking spreadsheet: v2.0 release notes [Tool] – Fire V London […]


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