Daily expenses: Serverless with AWS.

I have an obsession with my daily expenses. I need to control what I purchase. I started with an Excel table. I recorded my daily transactions every day. Date, hour, place and value.

I realized that I needed a process to register my transactions in cloud. So, I migrated my Excel to a google sheet. It was good at the beginning, but after a while, It was a headache to have to register all my transactions “by hand”. I asked to myself, how can I automate this process?

I remembered that my personal bank sent me a email with a purchase resume:

I decided to build my own ETL to automate this process with the power of cloud in AWS.

I divided the problem in 4 parts:

1. Extract the emails.
2. Transform the emails from unstructured data to tabular data.
3. Send the transaction with an API.
4. Re-send my bank’s emails to the solution.

Extract the emails.

AWS SES is a service that allow me configure a rule that sends a message to a s3 bucket. To configure this service I had to:

1. Create a subdomain in Route53. (ses.alejofig.com)
2. Verify the subdomain with SES. I had to create two rules. One with MX to redirect the emails and another with TXT to verify. Hack: In my case, I bought the domain with AWS so It handled all the verification process.
3. I created a rule in the Rule Sets menu. I putted an email with my domain “transacciones@ses.alejofig.com”. I selected the s3 action (I want an object in the s3 bucket for every email in an specific prefix).
4. I sent an email to try the new integration to “transactions@ses.alejofig.com” and this was the result:

Transform the emails from unstructured data to tabular data

I deployed a lambda with a s3 as a trigger. Every object that have been created in the bucket + prefix, will activate my code. That code extract the object with boto3, transform the data from unstructured data to tabular data with pandas and regex.

Send the transaction with an API

I built a webhook integration in Zapier. It allowed me sent the data by a http post and put the result in a google sheet.

Re-sent my bank’s emails to the solution

I configured a rule with microsoft flow to re sent every bank email to “transacciones@ses.alejofig.com”.

Results

I bought something in an App and the bank sent me the email. My solution pushed a new row in the google sheet:

Summary

I created a process to register my bank’s transactions with serverles AWS and with a almost 0 cost (only the domain). Thank you so much for reading. Let me know if you like the article in the comments section ;).