Portfolio tracking spreadsheet: v2.0 release notesPosted: 2020-04-11
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: