This blog post is the third in a series in which I integrate all of my personal financial accounts with LUSID - our open, API-first, developer friendly investment data platform - 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 How to analyse financial information from multiple providers in one place I added all the transactions from my Vanguard ISA account into LUSID where I could see them alongside my Monzo account in one place.
In this blog I’m focusing less on what I own and more on what I owe by connecting my American Express (AMEX) credit card account. At the end of this blog I hope to be able to see how my debt ratio (i.e. total liabilities / total assets) has changed over the last year or so.
Open Banking - A brief glimpse of hope
When I did a search for “AMEX API” I was pleasantly surprised to find that they had an "Open Banking" page with an Account Financials API.

I clicked through to get access and was excited to be greeted with phrases such as “RESTful APIs with JSON format for data exchange” and "APIs can be accessed through any channel".
I thought that perhaps this might be another account that I can connect (like Monzo) where I didn't have to muck around with spreadsheets.

As I worked through my request to get API access, I came across terms such as "AISP" and "active eIDAS certificate". I didn't know what these meant and after a quick Google search my hopes of using the AMEX API were dashed.
AISP stands for “Account Information Service Provider”. A quick perusal of the UK Financial Conduct Authority’s (FCA) website soon made it clear that I was not one of these and was not going to be in any time soon. In fact there are less than 200 hundred AISPs registered in the UK.
It was more than a little disappointing that although AMEX does surface transaction information via an API it is only available to a select few registered Account Information Service Providers and I couldn't have access to my own information directly.
Back to Basics
So with an API out of the question it was back to manually downloading my transactions. I did this by following the steps below.
1. Logged in to my account and clicked “View All Recent Activity” in the bottom left hand corner under my transactions

2. On the activity page clicked on Date Range and selected the date range that I wanted transactions for. The furthest I could go back was 11th May 2018 which is about 17 months. I’ve had the account for over 6 years.
3. Opened the “Download” pane above my transactions, selected ‘MS Excel’ as the format and checked the box to include all my additional transaction data. Then clicked the “Download” button.
After following this process I was able to open the downloaded file called “Summary.Xls”.
It had a single sheet which contained some of my account information as well as a list of all my transactions between 11th May 2018 to today.
Making the data machine friendly
To be able to work with this data, I had to remove the leading rows containing my account information and make a few transformations. I was able to do this quite easily using the Pandas library in Python.
Date |
Date Processed |
Description |
Amount |
Foreign Spend Amount |
Commission |
Doing Business As |
Merchant Address |
Category |
Subcategory |
id |
currency |
amount_local |
exchange_rate |
24 Oct 2019 |
24 Oct 2019 |
BPAY PAYMENT-THANK YOU REC # |
-350.76 |
|
|
|
|
Miscellaneous |
Other |
7947fb2157b3eade828b159bb9c9434d897a65efde1ebdfe657eb2e76026b00f |
AUD |
-350.76 |
1 |
22 Oct 2019 |
24 Oct 2019 |
NETFLIX.COM MELBOURNE |
13.99 |
|
|
NETFLIX COM |
VIC 3000 AUSTRALIA |
Communications |
Internet Communication |
e22bd69dfe9cf107273329fbb5e6771aba3ea60be52701ad92419cb6f2bc8809 |
AUD |
13.99 |
1 |
21 Oct 2019 |
21 Oct 2019 |
AUDIBLE.COM.AU |
14.95 |
|
|
AUDIBLE |
LONDON W6 7JP UNITED KINGDOM OF GB AND NI |
Retail & Grocery |
Online Purchases |
00616d34edd8805d3abbf193b641a01fd7558a297058444d786ed79a74b0d095 |
AUD |
14.95 |
1 |
17 Oct 2019 |
18 Oct 2019 |
ITUNES.COM/BILL SYDNEY |
14.99 |
|
|
NSW 2000 AUSTRALIA |
Retail & Grocery |
Computer Supplies |
c124c59c6eab59bef327c389f38533cad8750d937e20b84ff9a012810bffa02b |
AUD |
14.99 |
1 |
|
16 Oct 2019 |
17 Oct 2019 |
ITUNES.COM/BILL SYDNEY |
21.98 |
|
|
NSW 2000 AUSTRALIA |
Retail & Grocery |
Computer Supplies |
7ab8ede80725b77a130e8924754f3a60f337d36d0c007e6b8d2476c873fc8c7a |
AUD |
21.98 |
1 |
|
13 Oct 2019 |
13 Oct 2019 |
AMAZON PRIME*AMZN.CO.UK/PM |
15.41 |
GBP7.99 |
$0.45 |
AMAZON PRIME UK |
|
Retail & Grocery |
Online Purchases |
b3565f253a15e40fb1b0a5147042d9fe82b882eb8a04637ab71bf948ba4658ae |
GBP |
7.99 |
1.93 |
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.
Source | Transaction Type | Cash Balance | Security Balance | Properties |
Monzo | CurrentAccountTransaction | +increase | n/a | pot_name = "current_account" |
Monzo | DeclinedCurrentAccountTransaction | n/a | n/a | n/a |
Monzo | CurrentAccountPotTransfer | +increase / -decrease | n/a |
pot_name = "current_account" |
Vanguard | CashOnly | +increase | n/a | n/a |
Vanguard | InvestmentOnly | n/a | +increase | n/a |
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.
Source | Transaction Type | Cash Balance | Security Balance | Properties |
AMEX | CreditCardTransaction | -decrease | n/a | n/a |
Monzo | CurrentAccountTransaction | +increase | n/a | pot_name = "current_account" |
Monzo | DeclinedCurrentAccountTransaction | n/a | n/a | n/a |
Monzo | CurrentAccountPotTransfer | +increase / -decrease | n/a |
pot_name = "current_account" |
Vanguard | CashOnly | +increase | n/a | n/a |
Vanguard | InvestmentOnly | n/a | +increase | n/a |
I was then able to generate my current holdings.
holding_type | units | settled_units | cost.amount | cost.currency |
Balance | 612.12 | 612.12 | 612.12 | AUD |
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.
holding_type | units | settled_units | cost.amount | cost.currency |
Balance | -107.15 | -107.15 | -107.15 | AUD |
Yay!
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.
Note: Liabilities is scaled by a factor of 10 so a comparison can more easily be drawn. The chart starts from February 2019 as I still have some missing accounts to add for the debt ratio before this date to make any sense.
Next Time
Next time we will switch our focus to pensions/superannuation where I will look at bringing in my Australian Super account.