MonZO LUSID VaNGUARD (2)

How to analyse financial information from multiple providers in one place

This blog post is the second in a series in which I integrate each of my personal financial accounts with our investment data platform LUSID to get a comprehensive picture of my net worth. Doing this allows me to aggregate my financial information in one place, perform powerful analysis and get a clearer picture of what I own, and what I owe.

You may recall last time in Calculating your net worth: making the most of Monzo with LUSID we connected my Monzo account to LUSID. In this blog I’m connecting my Vanguard ISA account.

I’m going to use LUSID to show me both my Monzo current account and Vanguard ISA account in one place. This data can then be used to better assess my financial position. 

Vanguard - not at the vanguard of open technology!

With Monzo I was able to use a nice RESTful API to get my transactions. Unfortunately my search for a Vanguard API was fruitless. The only way to get my transaction history was to log in to the investor portal at https://www.vanguardinvestor.co.uk/, generate a report and then download it. You can see how I did this in the steps below.

1. Logged in to my account and clicked “Documents” in the left hand pane.

Image 1

 

2. Clicked “GO” in the “Report generator” pane in the bottom left of my screen. You may need to scroll down to see this pane.

Image 2

 

3. Chose the “Client Transactions Listing - Excel” report in the drop down. When asked, I selected the date range for which I wanted to run the report.

Image 3

 

4. The report appeared in my documents window. At first it said “Requested”, but after a few moments it updated to read “Created”. I was then able to download the file using the download button on the right.

Vanguard 4

After following this process I was able to open the file I downloaded called “LoadDocument.Xls”. It had two sheets. The first sheet contained my current balance and the second sheet contained my transactions. You can see a screenshot below.

Image 4

Nice(ish) to read if you are a human. Not so much if you are a machine.

First impressions

  1. The document gave me a lot less information than I had from Monzo which had over 80 fields
  2. The cash and investment side of each transaction seemed to be split into two separate transactions

Making the data machine friendly

To be able to do anything with this data I first had to make it a little more machine friendly.

Fortunately, I was able to read the excel (.xls) file using the Pandas library and this gave me a DataFrame to work with.

Looking at the data in the screenshot above you can see the sets of cash and investment transactions each start from the row which has the value “Date” in it. They also both end with the row with either “Balance” or “Cost”.

Using this heuristic I parsed the sparse ‘human- readable’ excel report into two DataFrames. One containing the cash transactions and the other investment transactions.

panda 1-1

You can see the details for each DataFrame below.

panda 2-1

Taking a closer look at each DataFrame they have mostly similar information. For example “Details” in the cash transactions and “TransactionDetails” in the investment transactions essentially represent the same thing. I decided to rename these similar columns so they matched and then combined the two sets of transactions. I also deleted the “Balance” column as LUSID will calculate balances based on the transactions.

panda 3-1

 

You can now see the transaction data is in a nice machine readable format. Perfect!

Excel 3

 

Instruments

With Monzo all my transactions involved only cash. In this instance, I now have transactions involving actual financial instruments (just one actually, the Vanguard LifeStrategy 60% Equity Fund - Accumulation fund). To make sure I could reference it when I added my transactions, I created this fund inside LUSID as an instrument using its ISIN as a unique identifier.

You can find examples of how to add instruments to LUSID in this example notebook here.

Final transformations

With the Vanguard fund added to LUSID as an instrument I was close to loading my transactions. But before I could add them there were a few transformations I had to make to the data.

One of the most important of these was to construct a transaction identifier. LUSID requires a unique transaction identifier for each transaction. Interestingly, Vanguard doesn’t provide any type of unique id with each transaction. To construct one I made the assumption that the combination of all the fields of a transaction i.e. the Date, Cost, InvestmentName etc. would be unique for each transaction and I could take the hash of this information and use it as the identifier.

panda 4-1

With the transformations complete I created a mapping from the DataFrame’s columns to the attributes LUSID requires and upserted the transactions into LUSID. You can find some great examples of upserting transactions into LUSID using these mappings here.

panda 5-1

 

Generating my current balance from the transaction history

With the transactions now in LUSID I could generate my current balance. In the last blog we spoke about how LUSID does this via a configurable movements engine which allows you to specify the meaning of each transaction type. We added three different transaction types for Monzo, a “CurrentAccountTransaction”, “CurrentAccountPotTransfer” and “DeclinedCurrentAccountTransaction”.

In this case we only have two different transaction types for Vanguard. These are “CashOnly” and “InvestmentOnly”.

A “CashOnly” transaction only affects the cash balance and a “InvestmentOnly” transaction only affects the security balance.

I set these up in the LUSID system settings and now you can see them alongside the Monzo transactions types.

Image 6
 

I now have a place where I can see my current account provider (Monzo) and my ISA account (Vanguard) in one place. Pretty cool!

For each source I have a library of transaction types and their economic meanings. When I get a transaction from Vanguard I use the Vanguard transaction types. When I get a transaction from Monzo I use the Monzo transaction types.

This allows me to connect to further financial accounts and systems without having to worry about mapping the semantic meaning of their transactions into a standard schema.

This is incredibly powerful as it allows me to easily build connections out to more providers without having to worry about breaking the integrations I have done so far.

Valuation

Valuation

Now as great as generating my balance is, it doesn’t really tell the whole story for an investment account like my ISA. What I really need to know is the net asset value of my Vanguard ISA.

To do this I retrieved the daily historical close price for my investments over the last year from the Vanguard website and loaded them into LUSID.

 

second_last_code_snippet_vanguard

With the quotes loaded I could then value my Vanguard ISA account by running an aggregation in LUSID. I did this for each day over the last year.

last_code_snippet_vanguard

Combining this with the data from my Monzo account I can now see how the balances of both accounts have changed over time!

VanguardAndMonzo

Tune in next time to see what happens when I try to integrate my AMEX account into LUSID, incorporating debts alongside my current account and ISA.