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:
- Extract the emails.
- Transform the emails from unstructured data to tabular data.
- Send the transaction with an API.
- 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:
- Create a subdomain in Route53. (ses.alejofig.com)
- 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.
- 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).
- 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 ;).