The tools I use to track my money, vs the tools I want

Posted on by FIRE v London

A recent blog post /comment thread on Ermine’s Simple Living in Somerset was the inspiration for this post – about the tools/processes I use for tracking my finances.

Ermine and I find ourselves relying on a 2004 piece of Windows software, Quicken 2004, produced by a US company that pulled out of the UK market for personal finance software in 2005. It continues to offer QuickBooks, for business accounts, but thankfully Quicken 2004 still works, albeit without any live-off-internet functionality. Credit due to Microsoft for the backwards compatibility too (you listening, Apple?).

Much as I had assumed that I was in a minority of about one, until I read Ermine’s blog post, a lot of online banking/credit card systems do offer some support for Quicken file formats so I suspect there are a few tens of thousands of us still out there.

But in recent years a wealth of new tools/capabilities have emerged, and as a result my approach has evolved somewhat.

These days I use a mixture of
1) Quicken (2004)
2) Excel
3) Google Sheets
4) Interactive Brokers

1) QUICKEN

Quicken is my source of truth for spending – credit cards, current accounts, etc.

You could get online stock quotes back in 2003

Until a year or so ago I categorised every transaction. These days the advent of contactless payments has meant a proliferation of tiny transactions on my statement and I only categorise large payments or transactions in certain categories – e.g. charity donations – tho I do still categorise 90%+ of my spending.

Quicken is very helpful for letting me pull out specific spending categories – e.g. charity donations, property rental spending, bank interest, previous tax payments, etc. Thankfully, many banks/card providers do let me export either in native Quicken format (despite Quicken having left the market 10+ years ago!) and/or a CSV format, and I use csvconverter.biz to map CSV files into Quicken format. As a result, manual keying, while not unheard of, is mostly eliminated.

Quicken also has a record of every transaction in all my investment accounts except for Interactive Brokers. However because it won’t import prices at all (any more) let alone live ones, I don’t properly update valuations. What matters, and what I use Quicken as source of truth for, is the Buy/Sell transactions. I use Quicken for my CGT reporting for these accounts.

I also try to capture all dividend/income (except IB) in Quicken. This is a key metric I track on my FIRE journey. Quicken lets me look at it by account, by time period, by category, and export quickly in Excel. It is fast and effective, once the data is in place.

2) Excel

I use Excel for tracking the value of my investment portfolio, and update it fully at least monthly. It knows my target asset allocation and helps me monitor the deltas between target and actual, consolidating positions across accounts. A version of this spreadsheet is available via my blog (or search ‘FvL investment tracking spreadsheet’), in Google Sheets.

I also use this sheet to track my investment expenses – both at an account level (e.g. platform monthly fees) and at a fund/ETF level (with my estimate of TERs/OCFs).

This tracking sheet knows relatively little about my income. It does know the notional income yield of each holding but doesn’t know what actually happens.

I use separate ad hoc Excel spreadsheets for help with tax reporting, e.g. of property rental income/expenses (exporting spending from Quicken).

3) GoogleSheets

I have the ‘live valuation’ bit of my Excel spreadsheet in GoogleSheets. This is really just a vanity tool – to let me eyeball quickly, mid-month, what is going on.

4) InteractiveBrokers

My most important investment account provider is IB. I don’t keep a parallel log of these transactions, but rely on IB’s reporting tools to be able to find a) dividends/income b) year end statements c) Capital Gains. IB has excellent off-the-shelf reports, which will operate to any date range (max 12 months), as well as a build-your-own-report feature called FlexReports (that I don’t use, but like to know I could).

A product wishlist….

With the advent (in the UK) of OpenBanking, the process of integrating financial data from disparate sources is becoming easier. But it it is some way off a point that would really help me here.

In the meantime there is no shortage of apps/projects that claim to provide the perfect financial tracking solution. As yet none can yet cater for me.

Just in case one of the Product planners at Moneydashboard, ReboApp or so on is interested, here is what I want out of a tool:

  • Ability to import all regular bank accounts – card statements, current accounts, categorise spending, and do reports/queries quickly (by date range and with ‘pivot table’ filtering/organising, or CSV output).
  • Ability to import investment portfolios – and allow me flexibly to track security prices, dividends and compare to each other, indices, in different currencies, etc. Ability to categorise holdings against arbitrary asset types, geographies, investment styles (i.e. tag things with an arbitrary number of properties), and build custom reporting around these.
  • Ability to import investment transaction histories – including dividends.
  • High level of security and no funny business about data ownership. I am not as paranoid about cloud-based data as the Ermine, but I do need to trust the solution in the same way I trust Google/Microsoft/Apple/similar.

The above list really doesn’t sound that complicated to build, but nobody has done it for UK investors as of the time of writing! I would pay perhaps £100 per year for this solution but anything like £20/month sounds too pricey to tempt me.

I live in hope….

19 thoughts on “The tools I use to track my money, vs the tools I want”

  1. I currently have access to Bloomberg Professional ($24k/annum+ extra for some forms of market data) plus access to all the investment bank research, databases, trading platforms, APIs, Excel addins etc. I obviously leverage these tools where I can for my personal portfolio. Having had access to this types of functionality for over two decades, I often wonder what I would do outside of the “professional setup”.

    Without going into personal specific but assuming a HNW type asset portfolio of say £5-10mm, then something like Bloomberg would still be too expensive, adding say 20-40bp of NAV to costs. Nonetheless, I think paying for something in the region of up to 5bp might well be worth considering, which is £2.5k to £5k. I would argue that such a cost may be justified in terms of better data quality, analytics and just ease of use.

    A free financial analytics library for Excel is already available via QuantLibXL which covers everything I would need in terms of bonds, fx, forwards and options (and equities if I needed that). Something like Deriscope or Excelpricefeed can be added (for a $100 or so) to give me a way to get live data into Excel. A bare bones verison of Reuters Eikon at £3k/annum might be worth it. I’ve also considered buying a research license from a investment bank for a few thousand dollars (if it wasn’t for the idiiocy ofr MIFID2 that would be free!).

    I think you might be being a bit stingy limiting yourself to £100! I think you can afford to spend a little bit more …

    Liked by 3 people

  2. Thanks for the interesting read – always good to see what other people are using to keep track of everything. Given the popularity of Quicken, it’s quite surprising that Microsoft haven’t tried to bring it back in one form or another. I’m a MoneyDashboard user (and fan), but there are still a number of kinks for them to iron out. Still relying on the trusty Excel spreadsheet to track my investment returns.

    Really looking forward to seeing what MDB do in the area of tracking investment returns – it may just be a headline balance figure initially but there’s real scope for them to develop something special once they sort the data connections and pull in everything your broker shows. It looks like HL are one of the APIs in their pipeline, so hopefully it won’t be too long before we start to have available connections for brokers more generally.

    Like

  3. +1 for Quicken. You don’t have to give your bank passwords to anyone and it’s not Cloud, where all your data belong to Them.

    Although there’s an argument that it’s not the right tool of the job, Quicken will still import prices from a CSV. You can set up a google drive spreadsheet that has the stock code as LON:RDSB and then in the next column GOOGLEFINANCE(A1) to get the price.

    In quicken your stock tickers need to match, ie ticker LON:RDSB. Download the drive spreadsheet as a csv. You appear to be able to change them and keep the history.

    The killer that they don’t tell you is you can only File:Import prices if Quicken is set to view the portfolio (CTRL:U). If it is set to look at anything else like your bank accounts it doesn’t play ball and you can’t import prices.

    Liked by 1 person

    1. Excellent tip – thank you!
      I can’t get file paths to work (I think due to admin permissions) but if I plonk the CSV in Quicken’s C:\Program Files (x86)\Quicken directory, this works reasonably well.
      Numerous tickers/securities don’t seem to work – but enough do that this is useful.
      Thanks again!

      Like

      1. If googlefinance doesn’t have the tickers you probably have an alternative source for the more esoteric stuff, You can import more than one csv file, one after the other. The last one will always overwrite any values, but if they are for different stocks it will work.

        You need to make the stock tickers match. Exactly, including capitalisation I believe. Also it will only do day end imports, you can’t import historical data.

        It works well enough for me to get a feeling of marked to market networth, on the occasions i run this

        Like

    1. @PC – very interesting – thank you. I’d never heard of Moneydance and have spent a few hours playing with it.

      It appears to be a very fully-specced alternative to Quicken. Thanks for the tip!

      This means that it does one thing definitely better than Quicken 2004 does for me… namely it pulls in online stock prices. Yippee. This means I am tempted to move my Investment tracking to it. I don’t see an incremental win from moving my Spending tracking.

      However after quite a bit of faffing (but without any Perl, @ermine), I can not find a decent way of importing Transactions Histories from online brokerages. I have tried quite a bit of stuff but struggled and am now giving up.

      Any suggestions?

      Like

      1. hi FvL,

        if that is the sort of thing that is amenable to a perl script then I could take a look for you? You’d prob need to send me a brief description of what you’re trying to achieve and an example Transaction History file that you’re trying to parse.

        Like

      3. I’ve imported transaction histories using a foul combination of Excel and VBScript. I had to do this when I left iii and again when I left TD. The print version of transactions was more amenable to scripting, and is generally better for screen-scraping like that – it doesn’t have all the interactive chrome. Perl would have done it in a better way IMO, but it’s only when I had to import stuff every month that I used that. Excel and VBScript are a bit more visual to develop and may be an easier learning curve.

        Liked by 1 person

  5. I used Quicken from 1996 to 2008 and since then I have used Tesco Personal Finance which is actually a version of Moneydance. My version is from 2008 so I am minded to upgrade to the latest Moneydance. Any further information on the latest Moneydance would be of interest.
    On my old version I re-key all transactions and month end share prices. I also maintain Excel spreadsheets and use two websites to monitor my portfolio. These processes probably encourage me to have fewer transactions and fewer holdings!

    Liked by 1 person

  6. Have you had a look at sharesight.com i find it good for following my investments and price updates, it is cloud based though.

    I am not UK based so not sure how it will work for you, they say it is avaliable for UK based investments.

    Liked by 1 person

  10. I have been looking for a similar tool for ages with no success. Open banking is cool, but it is only mandatory for payment accounts, and I’m not sure broker accounts quality. I.e. the era of automatically pulling transactions from HL is far into the future without them being forced to do an API.

    However, I don’t think it’s as easy to build something as you imply. Instead of what you use, I have something I’ve built for myself to record transactions manually and do basic XIRR return calculations. Given the number of edge cases, it’s taken much longer than I hoped (with no dividends or live stock prices – although arguably that’s the easy bit)

    I’m quite interested and ready to spend time on building this, but given the variety of approaches and preferences, I doubt it would automatically work for everyone without becoming Bloomberg.

    Liked by 1 person

  11. Anyone ever forget to update their transactions for a few months…how did you get back to it? I think loads of microtransactions that aren’t real from a project I was doing is putting me off visiting the madness..

    Like

Leave a comment