Parse amounts from Discovery bank transaction emails

As of this writing, the Discovery transactions export does not contain card numbers, so it’s impossible to get a breakdown of which virtual credit card spent how much money in any given month.

(I was having so much fun withduckdb importing the exported excel, about to join with the relevant timestamps, when I saw that the transaction times were often far apart. Alas.)

Also unfortunately, the emails only have base64-encoded HTML bodies, because somebody at Discovery just keeps on making bad decisions.

After much experimentation, I can give you this script which takes the email file as input and extracts the amount which appears right below the ***** Card payment ***** line:

#!/bin/bash
# let's call this: extract_amount.sh
 
# - sed finds body (headers are terminated by blank line)
# - we base64 decode that to get the sad, so sad HTML
# - html2text to extract the text, with -nobs to strip control chars (this was breaking our flow), and -ascii to turn non-breakable
#   space into normal space between "R" and the amount
# - grep -A1 gets the line containing "Card payment" and the one right after which we want
# - in that last line, strip out the last field which is the amount. finally.
sed -n '/^$/,$p'  "$1" | base64 -d | html2text -nobs -ascii | grep -A1 "Card payment" | tail -n1 | awk '{print $NF}'

I use the Emacs mu4e email client, so I can search for all Discovery emails for a given month and a given card number, and then apply the script above to all of the emails satisfying the search query:

# 1. Replace `1234` with the last four digits you'll see in the email subject
# 2. modify date
mu find --exec=extract_amount.sh subject:"transaction update 1234" date:2024-11

This will yield a list of amounts, each on its own line.

If you don’t use mu4e to read your email, you’ll have to find another mechanism to return the list of downloaded email files in question.

Now we can pipe all of that through some more awk to get the total amount for that virtual card over the given month:

mu find --exec=extract_amount.sh subject:"transaction update 1234" date:2024-11 | awk '{gsub(",", ""); sum += $1} END {print sum}'