My investment tracking spreadsheet

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 monitor it.  So here goes.

I face three key challenges in tracking my portfolio’s returns:

  1. 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.
  2. 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.
  3. 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 that you want to monitor.  Then copy/paste into appropriate tabs in this sheet.

2017 01 multiple broker statements.pngThen 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.

2017 01 Name lookup asset database tab.png

Bingo! – you have a consolidated view of your portfolio.

2017 01 template integrated tab.png

And you can drill into your portfolio by geography or asset type.

2017 01 output allocation.png

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.

2017 01 output over time.png

Secondly, record the deposits/withdrawals in each account.

2017-01-output-inflows

The spreadsheet will then build up a picture of your portfolio’s performance over time.

2017 01 template returns.png

What this monitoring 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

  1. In Excel, with your repaired workbook.
  2. Go into the 3 Integrated tab
  3. Put your cursor in the main data table, e.g. on a yellow ‘END MARKER’ cell.
  4. Insert.Pivottable. Follow the defaults which will create a new sheet with a fresh blank pivottable.
  5. 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.

2017-01-fvl-spreadsheet-repaired

Advertisements

20 Comments on “My investment tracking spreadsheet”

  1. Thanks – this looks really useful. I’ve been planning to put together an “X-Ray” sheet for almost a year now, but I’ve just been too busy / lazy.

    I hope your sheet will mean that I finally get around to it in the near future.

    Liked by 1 person

  2. grasmi says:

    Thanks for sharing. Always useful to see how others are tackling this. On broker selection, is there any specific reason you’re not using Interactive Brokers? On my search I found them to be the cheapest and most fully featured platform (especially for dealing with foreign currencies and equities), with the best liquidity and fills I’ve encountered from a retail platform. The reporting and data available for extracting to spreadsheets like this is also excellent. I was just curious why IB didn’t make the cut for you in terms of broker selection (in case I’m missing some obvious negative with them – I have a lot of my eggs in that basket)?

    Keep up the great work.

    Liked by 1 person

    • Good question! In fact I love IB and use them a lot. They were in this spreadsheet until the last minute. However they are extra fiddly for spreadsheet updating for a few reasons. And I think they are quite niche in U.K. where they don’t support tax-sheltered ISAs.

      Liked by 1 person

      • Graeme Smith says:

        Ah ok – great – I’m not going crazy then – I thought maybe I was missing something! I’m currently based in Switzerland so the ISA situation is not such a big negative for me (although I quit my job yesterday, and depending on how successful they are at reeling me back in I could end up in either Singapore or back in London – if the latter, the ISA situation will become more of a consideration for me).

        I really find IB the best retail brokerage out there, and have most of my liquid assets in there. My only real gripe is that being based in Switzerland they made me open an account with the UK subsidiary and not the US one. Why does this matter? Because the US accounts are covered by SIPC (500k USD) as opposed to FSCS (50k GBP – i.e. Great British Pesos lately!).

        I have “Flex Queries” set up in my IB account automatically emailed as CSV to my gmail, then an IFTTT job downloads them to my dropbox automatically. I have a monster spreadsheet which covers my wider situation (the lights in the house flicker when I open it!) with some macros to import the data from the Flex Queries CSV files. It’s nice as I just hit one button to suck all the trading / portfolio data in. I have 4 main data sets I work with from IB:
        – Cash Transactions (dividends, also includes deposits and withdrawals to correctly calculate the real portfolio returns net of deposits / withdrawals)
        – Positions (just for managing current positions, but I also snapshot this data on a monthly basis for historical analysis)
        – Trades (I mainly use this for managing my options trades, but also historical trade/pnl analysis)
        – NAV (daily view of net asset value used for return calculations)

        The concentration risk with IB is a fairly major concern for me, but at the same time, it is really nice to be able to manage everything on the one platform, and for the moment I have chosen simplicity over risk reduction. This dramatically simplifies reporting and general day to day management. Being able to convert and move money around the globe at spot for $2 is a nice bonus. If I successfully leave my job I will need to diversify across more brokers as A) I will be more dependent on my investment income, and B) will have a significant cash injection (forced equity sale from my current employer) which I intend to invest in equities, but placing that with IB too leaves me severely exposed to them as a counterparty.

        It looks like you’re fairly diversified across brokers (assuming you have accounts for each of the tabs in the sheet)? Do you have a set of rules or guidelines you use to diversify across brokers? The thought of having to log into 10 different accounts fills me with dread – I’m hoping to maybe settle on 2 or 3, the selection of which will be dependent on which country I end up in next.

        Cheers,

        Graeme

        Liked by 1 person

      • Thanks @GraSmi – this is enomously interesting and useful. I too am with the UK/Swiss bit of IB. I do have a Flex Query set up but haven’t got anywhere near emailing/IFTTT/macros off it, ordinary mortals take note!

        As to diversification… I have indeed used all of these brokers/platforms at some point in the last 4 years. However I don’t still use them all. As astute readers of my blog will know I also use some others, ahem. Why so many? My original thinking was partly diversification and partly self-education. In truth the diversification has always been mostly a figleaf but the self-education has been helpful. Latest thinking on each one is as follows:
        – H-L: Why opened? For professional interest. And despite my reservations – I am cynical about their high fees and poor value. Why keep it? Generally H-L is redundant without a clear USP vs e.g. Fidelity/ii. But it would be a good starting point for service-led (i.e. non-blog-reading) people e.g. many friends of mine.
        – ii: why opened? For an execution-only passively managed SIPP. I think it’s arguably the best pick for larger execution-only accounts. Why keep it? It maintains its edge.
        – Selftrade. Why open it? It bought another exec-only broker that I had picked >15 years ago for its low fees. It’d become my main execution-only platform until I discovered IB. Why keep it? Diversification. It has a material slice of my portfolio.
        – IB. Why open it? Lowest fees, most powerful platform, provided you don’t need ISAs. Why keep it? Best-in-class platform for fees/features/markets. And not only does it offer margin loans – but they are very competitively priced. As a result it now has a big part of my portfolio.
        – Fidelity. Why open it? Originally it was a trusted full-feature brand that I knew professionally and respected. I wanted to learn how good it was. Why keep it? It’s been OK – it earns its fees. High service levels, good range of products. I recommend it to service-led folks like my family.
        – Cofunds. Why open it? pre-RDR I moved some old (Jupiter et al.) funds into a fees-rebated-to-me IFA and they offered Fidelity or Cofunds. I chose Cofunds because I already had Fidelity. Why keep it? Post RDR, I haven’t.
        – TD. Why open it? For Oz trading. Why keep it? Post IB, I haven’t.
        Good luck with the country choice! And thanks for helping my blog.

        Liked by 1 person

      • grasmi says:

        IB do actually have data API’s so you can pull data direct to Excel (it’s a 2 step process however – you need to call the API to get a token, then you call again to get the data). It’s fairly easy to set up, but I went with the flex query via email approach for a few reasons –
        1) I have an historical copy of the data off their systems which I know can’t be tampered with
        2) With IFTTT it’s all automatically put in my dropbox, so no real overhead after setting it up
        3) If I’m travelling light and only have my ipad with me, I can still open the CSV data via dropbox and manually copy the data into my spreadsheets (bypassing the macros that I normally use to import the data on my desktop).

        Thanks for sharing your reasoning on the other brokers! Very useful. If I manage to escape the clutches of work (it’s just a question of how big the carrots are they try and block the doorway with) I will need to pick a few more to spread my risk, so this is very helpful. Thank you for sharing.

        Graeme

        Liked by 1 person

  3. Roadmap2Retire says:

    Thanks for sharing. I use similar sheets and I will take a look to see if I can find something new and interesting that I can incorporate.

    Was surprised to see TD as a prominent UK broker. As a TD shareholder, this pleases me 🙂 Is TD big in UK?

    Best wishes
    R2R

    Liked by 1 person

  4. Very nice. I use ShareScope for portfolio tracking but a well designed spreadsheet is always going to be more flexible. I’m sure a lot of investors will find your spreadsheet very useful, so thanks for making it public.

    Liked by 1 person

  5. Small Cap Snowball says:

    Thanks for the spreadsheet. I use SharePad but it doesn’t take into account investments and withdrawals in it’s % calculations. So was planning on using the bogleheads spreadsheet you have previously mentioned. But this is even better.

    Liked by 1 person

    • Thanks SCS. I should have mentioned that this spreadsheet uses essentially the same methodology as the Bogleheads spreadsheet. Combined with my very own aggregation / allocation analysis functionality.

      Like

  6. fireinlondon says:

    Hi FvL,
    Thanks for this – very useful, and with all the comments as I will be looking to choose a new execution only broker for the next tax year – seems I have a lot of evaluating to do!
    FiL

    Liked by 1 person

  7. […] An investment tracking spreadsheet for you to try – Fire V London […]

    Liked by 1 person

  8. Andrew says:

    Hi FvL,
    Thank you for sharing. Just beginning to have a play with it…unfortunately when I try and open it in excel (after downloading it) I get a ‘We found a problem…’ message. This effectively removes the pivot table from the pivot table tab i..e this tab is blank.

    Do you know if there is a way around this at all?
    Thanks again.

    Like

    • Erm which version of Excel are you using, on which OS?

      Like

      • Andrew says:

        I am on Windows 7, with excel 2016.

        After saying ‘yes’ to do you want us to recover as much as possible / trust the source (if say ‘no’, then file does not open) then this is the message:

        Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
        Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

        Like

      • Andrew – yes I see what you mean. The same happens on my PC. I blame Google Sheets’ export faciity.

        I suggest you rebuild the pivot table in Excel. I’ll append instructions on how to do this in this post.

        Sorry for the inconvenience!

        Like

      • Andrew says:

        Hi
        That would be great (re pivot table instructions) if you could – thanks a lot.

        Like

  9. Je says:

    I use Halifax, TD & AJ Bell in the UK.
    Halifax were the original.
    I added TD for an increased range of overseas stocks, but they blew that business by increasing the spread on forex comission to 2%. Now back to 1.5% I believe, but even 0.5% would be greedy !! Also, if I hold USD 10,000 in forex, they will not allow me to spend more than about 90% of it in one trade because of the risk of forex movements. Even if I have several hundred thousand with them.
    A J Bell are for my SIPP.

    Anyway, are there any brokers out there that would easily allow me to open an offshore broker account ? e.g. In Singapore.
    This is not for tax dodging, rather to have something beyond our borders to assist my swift emigration the next time we look like having a Labour government.

    Liked by 1 person

  10. Lean Fernac says:

    Hi FIRE v London
    This spreadsheet has been the best gift I’ve received from finances blogs or forums. It’s really powerful and easy to use.
    Thank you for sharing with the digital community.

    Liked by 1 person


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s