This blog post is part of a series in which I’m going to integrate each of my personal financial accounts with LUSID to get a comprehensive picture of my net worth. Something I’ve wanted to do for a long time but never had the tools to achieve.
Imagine if you could put all of your assets in one place – current account, ISAs, insurance, pension funds – to get a simplified and connected view of you net worth and run powerful analytics. That’s what I’m going to try to do over the next few months.
I set out to link all my personal financial accounts (e.g. Monzo, AMEX et al.) and investments (Vanguard ISA, AJ Bell) together in one place. This is quite a similar use case to an asset manager who might want to aggregate investment data from their prime brokers, custodian and fund accountant all in one place. So I set up an account with our LUSID Early Access Program to see just how open the financial ecosystem really is.
Hasn’t that already been done though?
Interestingly but perhaps unsurprisingly, asset management is quite the laggard when it comes to open access to information. Our recent blog post Start up pain and making mistakes for the long term revealed that lots of hedge funds and asset managers are incredibly frustrated that they can’t easily access, interrogate and combine data from different sources.
Now, you might say that for personal finance there are a number of companies that provide aggregation services already and you would be right e.g. Frollo, You Need A Budget, Money Dashboard. However, I've never been able to find a solution which gives me the ability to get a complete picture of my finances. Especially as I now have accounts in Australia and the UK. There are always one or two accounts which aren’t supported.
First off the block - Monzo
The first account I decided to integrate is Monzo. A digital bank (or perhaps a hipster bank if you are a NatWest or Lloyds loyalist) Monzo has grown rapidly to have just over 3 million account holders. I was most optimistic that out of all my accounts, Monzo would be the easiest to get my data from. I was not disappointed. In fact, once I’d pulled my Monzo transaction data into LUSID I was able to do some pretty powerful analysis (much more than you can do in the Monzo app directly).
Monzo allows you to access a rich set of information about your account and transactions via their application programming interface (API).
To get access I headed to https://developers.monzo.com/ and followed the sign-in instructions.
Once signed in, I was given an access token which I could use to call the Monzo API.
Interestingly for some API calls such as to list my entire transaction history Monzo uses Strong Customer Authentication, a series of rules and regulations intended to enhance the security of payments and limit fraud. I therefore also had to authorise access to the Monzo Developer Portal via the Monzo app. You can see what this looks like in the screenshots below.
Getting my entire transaction history
Once I had allowed access to my data it was relatively straight-forward to get my entire transaction history (2,468 transactions) via the API. Note that I used Python for this project.
I took the response from the Monzo API and turned it into a Pandas DataFrame to make it easier to work with. There are 33 fields of information returned by Monzo which you can see below.
This is actually a little misleading as there are a few columns here which contain other columns inside them (nested columns). A good example of this is the metadata column which contains a plethora of other data.
In addition to the nested columns the “amount” columns are in Pence Sterling rather than Pounds Sterling. I therefore had to make a few transformations to unnest the columns and convert the amounts to Pounds.
After these transformations the number of columns ballooned out to 87.
What the hell is a Pot Id?
One of the most interesting columns that came out of this expansion is the “pot id”. Pots are a cool Monzo feature which allow you to set money aside for different purposes. Money in a pot is held separately from your current account and can’t be used in transactions. I use pots frequently for budgeting and so it was important to get both my total balance from Monzo as well as the balance of each of my pots.
Unfortunately the pot id is not very descriptive and has the format of something like “pot_00009dIYwlReHJ2KBehkET”. Fortunately, Monzo also has a List Pots endpoint on its API which you can use to get the pot details for each pot id.
Identifying transaction types
With all the columns expanded and the pretty names of my pots listed there was just one final thing to do. Differentiate between the different types of transactions. As far as I could tell there are three different types of transactions across my Monzo account. They are:
i) A standard current account transaction where money goes into or out of my current account e.g. buying groceries
i) This is when I transfer money to or from one of my pots e.g. taking money out of the groceries pot to fund my current account for my subsequent purchase of groceries
i) Exactly as described, a transaction that is declined due to insufficient funds (I had more of these than I’d like to admit...!)
I also added in exchange rate information where there was a foreign currency transaction and added a price column (more on this later). This gave a final output with 89 columns.
Wow, that’s a lot of data
I was actually very impressed with the breadth of data that Monzo provides for each of my transactions.
I expected to see just 3 or 4 columns with something like “date”, “amount”, “description” and “balance”. The fact that Monzo gives you information such as “metadata_mastercard_internal_message_id” absolutely blew me away. Perhaps personal finance isn’t so different to asset management after all. A dataset like this with 100 (or more) columns of which some are very sparsely populated is a common occurrence in the asset management world.
How to get the data into LUSID?
I was quite intimidated about getting this information into LUSID. Fortunately, LUSID uses an extensible data schema. What this means is that I only had to identify the 10 columns that LUSID absolutely requires to understand how to process the transactions to generate my balance. The rest of the columns are created as-is inside LUSID using custom properties.
These properties aren’t used in any calculations to generate my balance but they are readily available for other purposes. This ensures that I haven’t lost any data during the import as you never know what might be important later.
Looking over the data I created this mapping without too much trouble (the left side is the LUSID field, the right side is the column in the Monzo data) and then using the LUSID Python SDK I uploaded the transactions into LUSID. If you are interested in more detail around loading transactions into LUSID you can find detailed examples of how to do this here.
Generating my current balance from the transaction history
With the transactions now in LUSID I could generate my current balance. LUSID does this via a configurable movements engine which allows you to specify the meaning of each transaction type. Recall that in this case we have three different types of transactions.
I configured each of these in LUSID so that for any “CurrentAccountTransaction” LUSID updates my current account balance by the amount of the transaction.
For any “CurrentAccountPotTransfer” LUSID updates both my current account balance and the appropriate pot balance for a net change of 0.
Finally, for the “DeclinedCurrentAccountTransaction” it makes no change to any balance at all.
You can read more about configuring transaction types here.
With the transaction types configured I was then able to get the balance for my current account and all my pots out of LUSID using the LUSID Excel Add-In.
Taking a quick look at my Monzo app I confirmed that the balance for my current account and each of my pots was correct. You can see this for a subset of my pots in the screenshot below.
Given that I now had my entire Monzo transaction history in LUSID I realised that I could calculate the balance for my current account and all of my pots at any point in time.
This is a feature that doesn’t exist in the Monzo app at all. In fact looking across the Monzo API I can’t find a single place where you can get a historical current account or total balance, let alone the balance for every pot at any point in time. You can see an example using the Excel Add-In to get my balances as of the 26th of May below.
It’s hard to understate the value of this. This allows you to do things like compare your financial position a year ago to today or to chart your daily balances over an entire year. By generating my balances for each day I was able to do exactly that, creating a time series visualisation of the size of each of my pots since I first opened my Monzo account.
One down, many to go
In summary, Monzo makes it very easy to access your transaction data. After a few simple transformations and some slight configuration in LUSID I was able to load my entire transaction history. From this history I could generate my balance across my current account and all pots at any point in time and was able to create a pretty cool visualisation showing my activity since I first opened my account.
A great start to calculating my net worth, but still a long way to go, in my next post we’ll see if we can integrate a Vanguard ISA account into LUSID.