AMEX Balance synced to Pot - a janky solution

[BritishLibrary]

So after some frustation at not being able to link my AMEX balance, to my “AMEX Pot” - I finally found a way to do it - or at least do it once a week, which I suppose is better than nothing.

So, introducing - the Weekly AMEX Balance to AMEX Pot Syncer with Google Sheets and Plus _{made for iOS because Android has a better feature for this} (catchier name welcome)

---

Quick overview

This uses a Weekly Balance notification email from AMEX, the body of which is passed into a Google Sheet, and a consistent term in those balance emails is sought out - and the actual balance filtered from there.

With the Plus / Google Sheets integration to go along side it, you can use this to calculate the difference in your AMEX pot weekly, and add the difference to the pot with IFTTT.

Like I say. Full on Janky.

Also, the method mentioned in the 2nd link above, might be more relevant for Android users - as IFTTT can read notification content in app on Android.

---

Requirements

• AMEX Account - with “Weekly Balance Notifications” turned on
• Gmail (or GSuite) / Outlook / Zoho mail account - or the ability to forward mail to one of these.
• Zapier Account (the free tier works fine)
• IFTTT Account
• Monzo Plus with Google Sheets Export turned on.

Properly Janky.

---

Set Up

Just a quick note - this got quite long, so the set up is hidden under the "details" of each section - press the arrow to reveal

Monzo Plus Data Export Prep

Details Below

• With your Google Sheets data (ideally a separate sheet using the IMPORTRANGE function) set up a field somewhere to determine the balance of your existing AMEX Pot (link here for a how to)
• You’ll refer to this field later in your sheet - so it’s important the range is fixed.

AMEX

Details Below

• Log into your AMEX account, and go to “Account Management”.
• Scroll to Alerts > Manage Card Alerts
  
• In that setting there should be a “Weekly Balance Email” option - pick the day you want and press save.
• Every week, at about 6AM on the day of your choice, you should get an email from AMEX with a table of various balances in.
• If you go for Saturday/Sunday - this should stop this solution occuring on the same day a Direct Debit is taken.

Google Sheets Set Up

Details Below

• I’ve made a google sheet as a template - you can copy the template here
• Basically, the “EmailBalance” sheet is what Zapier will connect to.
• The Action Sheet is where you do some analysis and connect your IFTTT link to.
• You can either add these to your Monzo Google Sheets Dashboard somewhere, or as a standalone sheet.
• You also need to change a calculation setting - if you go to File > Spreadsheet Settings > Calculation - change recalculation to “on change and every minute”. Because we’re using the now() function, this will give us more control.

Action Sheet

• This sheet is made of two halves - one is looking for your Zapier inputs (we’ll get to that) to determine the balance:
  
  
• the Date column will pull through the Latest Row added to the other sheet in this workbook.
• The remaining cells, look for the Location of the phrase “Current Balance:”, then pulls out a string of text immediately following current balance. It then finds the positon of “£” and “.” to work out where in that string your balance sits - to give you the final balance formatted as a number in the green cell. Groovy.
• It could all happen in one formula but I got a bit lost trying to make that work - because the comma sometimes appears if you balance is > £999 I decided to filter out too much text first and narrow from there.

The other half of the sheet is looking for your AMEX Pot in Monzo and doing some calculations:

• The Red Cells need user input.
• AMEX Pot Balance, cell I3, needs to point to whatever cell is tracking your AMEX Pot Balance in the rest of your dashboard.
• The Time cell in I6 is there to make sure this integration only fires after your desired time - mainly to avoid clashing with any Direct Debits confusing this - You can set this to whenever you like - but after 5:00:00 will mean you’re not moving money before any direct debits / BACS etc go out/come in.
• The Final Cell, I7, is where you will point IFTTT towards later.

Email Balance Sheet

This is basically the data dump for Zapier. Every time you get an email with your AMEX balance, it will add a row here. Basically the other sheet is always looking at Column A to find the very latest date that an email came in.

So simply we connect this to…

Zapier

Details Below

So Zapier is like IFTTT but more powerful. Except it doesn’t integrate with Monzo or IFTTT, which is why we have this workaround.

On the Free tier, you’re limited to what amount of actions you can do - but this process works.

In theory, this link should allow you to set this up easily - it’s set up for Gmail though I’m afraid. Let me know if it doesn’t work:

https://zapier.com/shared/09a7163383f27cabd6d3e5e9e53415850a6a50b3

Basically there are two steps to this task:

1. Search for an email with the subject “Your balance update”.
2. Add the body text of that email to your “EmailBalance” sheet in Google Sheets.

I’m not sure what config is needed if you follow the link above, but the settings I have are here:

Gmail Account - I assume Outlook works similarly but I can’t verify.

(edit account as needed)

Search string below - you can add something to the effect of from:(“whateveramexemail@is”) too for more specificity

in:inbox subject:("your balance update") 

The Find Data box should then ask to do a test search; Press Test and you’ll hopefully get this (once a relevant email has been received). If you want to speed it along you can send your self an email with the right subject.

Press continue.

Google Sheets

In the Google Sheets side set up the following:

In the next tab it will ask to link your specific sheet. Once you link your sheet and workbook, it will look something like this - it should find the Date / Body Text / Balance headers of your sheet.

In Date and Body Text, click into the drop down menu to select “Date” and “Body Plain”.

In the next tab, send data, it should do a test send, and you should see the data then pop up in your new Google Sheet.

Click Done and Save.

Now, every time you get an Email from AMEX with your Balance, this should add a row to this sheet.

And if you then take a look on your “Action” tab, this should now match the test data that Zapier just sent through, and hopefully in Cell I5 you should see the balance difference.

Last thing to do is to get IFTTT to move the balance when this sheet is updated.

IFTTT

Details Below

So go to IFTTT and create a new applet:
This:

• Service - Google Sheets
• Trigger - Cell Updated in a Spreadsheet

That:

• Service - Monzo
• Action - Move money into a pot.

In the Spreadsheet trigger, point it towards this workbook, and give the cell reference ActionSheet!I7 to this trigger.

In the Monzo Action, select your desired Pot, and in the ingredients selection, set {{value}} which should move the value from I7 into your pot.

And Save it all down are you are done!

Fin.

And that is IT! What this should now do is every time you get that balance update from AMEX, it should send the email body to your new Google Sheet, which will filter out the £XXX amount, and then calculate the difference between your AMEX Pot and your Balance, and if it needs topping up, it will move the difference over.

There are however some drawbacks and limitations to this:

1. Zapier only scans around every 15 to 30 minutes.
2. IFTTT Trigger can seemingly have an hour delay.
3. I’m not sure the behaviour of the balance email in the days very close to your DD payment. Will monitor that and tweak if needed.
4. Monzo Direct Debits come out in the early hours of the morning - the Time feature in here is to potentially stop any weird behaviour between an early email from AMEX and the Direct Debit clashing - you can reduce that clash by setting your notification email to a weekend.

---

So, what a journey. I hope that might be useful - it doesn’t run as frequently as I’d like it to - but I think this is the only reliable way I can find to work on iOS - until/if Apple ever allow notification values to be read from the Shortcuts app.

Any thoughts? is this useful to any of you in anyway? I know it’s a proper work around, but I’ve been testing it out over the last few days. Aside from the time delays it seems to work reliably enough.

[RichardL (Rich)]

This is great, I’ve been using the Android method for a few months now - I’ve even been able to pull and categorise transaction data into my Monzo Transactions sheet. What I’ve been finding is that occasionally I’m missing money from my pot because I haven’t dismissed the previous notification on my phone.

This will work quite nicely as a weekly sweep to make sure my AMEX pot balance matches up with my spending.
Thanks for sharing!

[BritishLibrary]

So I’ve just been playing around trying to force an update to the sheet / pot.

From Receipt of an Email -> Zapier -> Sheets took about 15 minutes to update.

And then about 45 Minutes for IFTTT to notice the sheet change and move money.

So about an hour from Email to pot being up to date.

[BritishLibrary]

Ok so the day of my Direct Debit going out and I find a problem - because the sheet now sees my Pot balance as £0, it thinks I need to add my entire balance again.

Have a mechanism to stop this - will update it shortly.