Analyse financial information from multiple providers in one place - AMEX

Overall it is pretty basic information. I did note that it wasn't clear whether the "Amount" on each transaction included or excluded "Commission". I assumed that it did include commission. 

Using the LUSID Tools Python package (soon to be released on GitHub) I loaded the transactions into LUSID. 

The fields on the left in the mapping e.g. code, transaction_id etc. are those required by LUSID and the fields on the right are the corresponding fields or columns in my AMEX data. If a value on the right is prefixed by "$" it represents a constant to be used across all transactions.

Generating My Balance

If you're not already familiar, LUSID can generate the balance of any account from its transaction history using its inbuilt movements engine. For LUSID to be able to do this I need to tell it the economic meaning of each different type of transaction. So far I have already added transaction types for Monzo and Vanguard which you can see summarised in the table below.

 
For AMEX, I only have a single transaction type that I called "CreditCardTransaction". It is most similar to the Vanguard CashOnly transaction type in that it only affects cash. However unlike with Vanguard the debits and credits are swapped.

This is because AMEX represents a purchase by me e.g. Netflix subscription as a positive amount (increase in my debt to AMEX) and a payment from me to pay off the card as a negative (reduction in my debt to AMEX). This is the opposite of how Vanguard represents cash transactions and so I need to actually decrease my balance by the amount of each transaction rather than increasing it. This has the effect of inverting the amounts so that I can join my AMEX account with the other two accounts.

With the CreditCardTransaction configured you can see the updated types below.

I was then able to generate my current holdings.

If you have a keen eye you may have noticed from the screenshots of my account earlier that this number is completely wrong!

What happened!?

When I loaded in my Monzo and Vanguard accounts I had my entire transaction history since I opened each account. This allowed LUSID to run the entire event history of each account from its inception. I was therefore able to correctly generate my current balance.

Unlike those two accounts I only have a subset of my entire transaction history here and so LUSID is working with incomplete information. Hence my calculated balance is incorrect. In this case it is showing AUD$612.12 (i.e. AMEX owes me money) rather than -AUD$107.15 (i.e. I owe AMEX money). 

To fix this LUSID has the ability to set the holdings for a specific point in time. Behind the scenes it will automatically post adjusting transactions to ensure that no matter what my balance today will always be -$AUD107.15.

Now as tempting it is to just update today's balance to be correct, if I was to generate the holdings for any date before today the balance would still be incorrect. In some systems which are only forward looking this wouldn't really matter. However as LUSID is fully bi-temporal it would be a shame to not go and add in my backdated balance to ensure that all generated positions are accurate.

So instead what I need to do is seed my balance right before the first transaction that I added into LUSID. In this case I know that my balance at this point in time (May 10, 2018) must have been the difference between my correct balance today and what LUSID has incorrectly generated. That is -$107.15 - $612.12 = -$719.27. So on May 10, 2018 I must have owed AMEX AUD$719.27.

I set this holding in LUSID using the LUSID Tools python package.

I was then able to re-generate my holdings to see if they were accurate.

Another difference between my AMEX account and my Vanguard ISA and Monzo accounts is that rather than being in GBP it is in AUD.

So that I can join the three accounts together I need to convert the AUD amounts into GBP. To do this I gathered daily FX rates from Exchange Rates API. Exchange rates API is a free service for current and historical foreign exchange rates published by the European Central Bank.

I took these rates and put them into LUSID.

Debt Ratio

With the foreign exchange rates populated, I generated my holdings for every day over the past year. Combining the result with my Monzo and Vanguard balances I was able to produce a nice visualisation showing my assets, liabilities and debt ratio since February this year.