Update your financial ledger by email in python

Table of Contents

Abstract

Each time I buy something and pay it later, my expenses and liabilities increase.

In this blog post I’m using the example of the reservation of a tennis court.

Once a year, the club sends an invoice for every reservation made. In order to reflect this in my finances, I note each reservation as a transaction into my ledger. The final goal is to create such a transaction into my ledger file completely automatic.

Goal

 2021/3/27 Tennis
     Expenses    CHF 10
     Liabilities

Manual process briefly explained

In order to keep my ledger up, a couple of steps are necessary. For a better understanding of what’s happening in the python script below, I briefly go over the manual process:

Query mu for emails

 mu find from:no-reply@t.gotcourts.com subject:Reservation --fields 'd s l'

Read the email headers

Backslash and line-break added manually for better reading

 Sat 27 Mar 2021 06:54:07 PM CET \
 GotCourts - Reservation confirmation \
 /home/ra/Maildir/AnyOtherProvider/INBOX/new/1616867898.105333_1.x,U=3935:2,

Extract the html content if plain-text is not provided

 mu extract --parts=1 /home/ra/Maildir/AnyOtherProvider/INBOX/new/1616867898.105333_1.x,U=3935:2,

Read html content

 </p><p>Partner: </p><p>&nbsp;</p><p>Price: 10 CHF<p>

Write the ledger post

Parse and fetch all information. Write the post as shown in Goal.

Script - Step by step

We start by importing the necessary modules

 import subprocess
 from datetime import datetime
 import re

Query mu and return the fields (d)ate, (s)ubject and fi(l)e

 MU_FIND_QUERY = "mu find " + myemailAlias + " --fields 'd s l'"

Run the current mu query

 def mu_command(MU_FIND_QUERY):
     return subprocess.check_output((MU_FIND_QUERY), shell=True).splitlines()

Create a list of results returned by mu

 EMAILS = [mystr.decode("utf-8").split(" ") for mystr in mu_command(MU_FIND_QUERY)]

In our case, the sender’s unable to provide plain-text. We tackle this obstacle by extracting the html part.

 MU_EXTRACT_QUERY = "mu extract --target-dir=/tmp/ --overwrite --parts=1 "

We subsequently store it temporarily /tmp/1.msgpart. Finally we read and return it’s content.

 def html_extract(file):
     mu_command(MU_EXTRACT_QUERY + file)
     with open("/tmp/1.msgpart") as f:
         return f.read()

We parse the content for the price. A match contains at least one positive integer followed by another zero based integer followed by CHF. Thus e.g. 10 CHF is a match.

 PRICE_REG = r"[1-9]+[0-9]*(?= CHF)"

If found, we return the price match

 def search_price(file):
     text = html_extract(file)
     if text:
         match = re.search(PRICE_REG, text)
         if match:
             return match.group(0)

mu returns the date’s month name abbreviated in English e.g. JAN. Thankfully python’s strptime method cover’s this format.

 def month(text):
     return str(datetime.strptime(text, "%b").month)

Now we’re all set regarding the necessary information to create a proper ledger posting.

For each parsed email we write/print a posting to our ledger file. But only if the email contains a price.

{email[3]}/{month(email[2])}/{email[1]} prints the year/month/day e.g. 2021/12/31.

email[-1] prints the emails file path, we pass it as argument to get the price by {search_price(email[-1])}.

 def parse(query_file):
     with open(query_file, "w") as f:
         [
             print(f'''\
 {email[3]}/{month(email[2])}/{email[1]} Tennis
     Expenses:Tennis    CHF {search_price(email[-1])}
     Liabilities:Tennis
             ''',
                   file=f) for email in EMAILS if search_price(email[-1])
         ]

Catch and print any exception

 try:
     parse(query_file)
 except BaseException as e:
     print(e)

Complete script

 import subprocess
 from datetime import datetime
 import re
 MU_FIND_QUERY = "mu find " + myemailAlias + " --fields 'd s l'"
 def mu_command(MU_FIND_QUERY):
     return subprocess.check_output((MU_FIND_QUERY), shell=True).splitlines()
 EMAILS = [mystr.decode("utf-8").split(" ") for mystr in mu_command(MU_FIND_QUERY)]
 MU_EXTRACT_QUERY = "mu extract --target-dir=/tmp/ --overwrite --parts=1 "
 def html_extract(file):
     mu_command(MU_EXTRACT_QUERY + file)
     with open("/tmp/1.msgpart") as f:
         return f.read()
 PRICE_REG = r"[1-9]+[0-9]*(?= CHF)"
 def search_price(file):
     text = html_extract(file)
     if text:
         match = re.search(PRICE_REG, text)
         if match:
             return match.group(0)
 def month(text):
     return str(datetime.strptime(text, "%b").month)
 def parse(query_file):
     with open(query_file, "w") as f:
         [
             print(f'''\
 {email[3]}/{month(email[2])}/{email[1]} Tennis
     Expenses:Tennis    CHF {search_price(email[-1])}
     Liabilities:Tennis
             ''',
                   file=f) for email in EMAILS if search_price(email[-1])
         ]
 try:
     parse(query_file)
 except BaseException as e:
     print(e)