Assets vs liabilites

Analyse financial information from multiple providers in one place - AMEX

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.

API Page Hopeful
 

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.

API Page Less Hopeful

 

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

AMEX Home Page - Clean

 

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.

AMEX Date Selector - Clean

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.

 

AMEX Download Transactions - Clean

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.

AMEX Downloaded Transactions XLS - Clean

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.

AMEX_code_20

You can see a sample of the transactions after the transformations in the table below.
 

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

 

 

apple.com/bill

NSW

2000

AUSTRALIA

Retail & Grocery

Computer Supplies

c124c59c6eab59bef327c389f38533cad8750d937e20b84ff9a012810bffa02b

AUD

14.99

1

16 Oct 2019

17 Oct 2019

ITUNES.COM/BILL SYDNEY

21.98

 

 

apple.com/bill

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.

AMEX_code_21

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.

AMEX_code_22

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.

AMEX_code_23

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

AMEX_code_22
holding_type units settled_units cost.amount cost.currency
Balance -107.15 -107.15 -107.15 AUD

Yay!
 
 
Almost There
 

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.

AMEX_code_24

I took these rates and put them into LUSID.

AMEX_code_25

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.

Screen Shot 2019-11-07 at 3.38.05 pm

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.