My investment-tracking spreadsheetPosted: 2017-01-17
I’ve been tracking my portfolio rigorously for over four years now. One thing I’ve been asked for quite a few times is a copy of the spreadsheet I use to do it. So here goes.
I face three key challenges in tracking my portfolio’s returns:
- Unitising the portfolio. This means correcting for ins/outs – withdrawals or deposits. Just because a £1m portfolio gained £100k doesn’t mean it’s delivered a return of 10%; if £30k of that gain was fresh contributions, for instance.
- Evaluating the portfolio’s exposure. By exposure I mean allocation by geography and allocation by asset type (equities, bonds, etc). Some platforms let you ‘X-ray’ your holdings but each has a proprietary way of doing it, and many platforms don’t offer any such feature.
- Integrating my holdings across multiple accounts. I have accounts with several brokerages and platforms. Each has a different way of doing it. They don’t even use the same tickers for the same underlying assets. I want a way of pooling all the portfolios into one consistent spreadsheet.
My template spreadsheet is available as a Google Sheet here. It’s 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.
This template spreadsheet includes example tabs for about half a dozen UK brokers, including Selftrade, TD Direct, Fidelity, Interactive Investor, Cofunds and Hargreaves Lansdowne. Adding a tab for a broker not already covered is not a difficult process.
Instructions are included on the first tab of the spreadsheet. Users need to be pretty familiar with spreadsheets and how they work.
In broad terms what you need to do to update the sheet is first of all download the latest portfolio as a CSV/Excel/similar from your brokerage accounts. Then copy/paste into appropriate tabs in this sheet.
Then add any new securities that the spreadsheet hasn’t yet seen. The template is designed to map lots of different versions of the same security (e.g. common holdings like Vanguard’s FTSE-100 ETF, or Vodafone shares) onto the same underlying security.
Bingo! – you have a consolidated view of your portfolio.
And you can drill into your portfolio by geography or asset type.
Then every month / quarter / year, you need to do two things. Firstly, copy/paste the latest values into a new row in the ‘over time’ tab.
Secondly, record the deposits/withdrawals in each account.
The spreadsheet will then build up a picture of your portfolio’s performance over time.
What this spreadsheet does not do is pull in real-time valuations from Yahoo! Finance/similar. I find such processes to be error-strewn, so have left them as an exercise for the reader.
I’d welcome comments on how useful this template spreadsheet is. If you have trouble using it please email me on mail at firevlondon.com and I’ll do my best to help. If I sense people are actually using it I may post ‘upgrades’.
In case you’re wondering, the initial portfolio data in the spreadsheet are all sample data only. But it does include 1000+ actual mappings of actual real life securities, and reasonably-up-to-date yield/expense/etc tagging for about 500 securities; this should speed up the process of getting your portfolio into the spreadsheet. Good luck!
P.S. Thanks to @Andrew for pointing out that Google Sheets won’t export the PivotTable into Excel properly. If you want to use the PivotTable in Excel you will need to rebuild it yourself. Here’s how
- In Excel, with your repaired workbook.
- Go into the 3 Integrated tab
- Put your cursor in the main data table, e.g. on a yellow ‘END MARKER’ cell.
- Insert.Pivottable. Follow the defaults which will create a new sheet with a fresh blank pivottable.
- Then put Geography into columns, Asset type into rows, and £GBP value into the Values. Et voila. I often find it useful to put Account into Filters too.