This may be a long read, but it’s easy to follow, and I promise you can have everything set up in ~20 minutes. Grab your favorite beverage and settle in.
This post is for you if:
- You live in a country where you can’t connect your bank and Mint (or similar) accounts to automate expense tracking
- When you pay for stuff with a card, your bank sends a detailed transaction alert via email
- You’re comfortable experimenting with unfamiliar tech products
Some definitions, before we begin
- Mint is a personal money management app — you can set budgets, savings goals, etc.
- Airtable is like Google Sheets or MS Excel, but with a lot more options to categorize and organize your data.
- Zapier helps you connect 2 or more apps together to automate tasks
Background info
I track my expenses using an Airtable base, which I’ve customized to my liking. I have one sheet for recurring expenses, another for one-offs, and an overview sheet where I can set budgets and see a total of how much I’m spending per month, per year. Each expense is categorized so I can shift things around to show how much I’m spending per category.
All of that is great, but the secret to good expense tracking is to diligently record said expenses. Everything must be accounted for, and sometimes I forget or am too exhausted to record my purchases — which is why I was happy to discover Zapier’s robot, Parser. Here’s how it describes itself:
Parser extracts data from emails that come in a consistent template, e.g. transaction alerts from the bank, or ticket reservations from your favorite airline. That means if it works well, Parser can (partially?) record my expenses, as long as I remember to forward the debit alert emails to it. So that’s what I’m going to do, and for the rest of this post, I’ll explain how to use Parser to make expense tracking easier.
Required tools
Step 1. Sign up for Parser.
After signing up, you’ll arrive at this page:
Step 2. Create a Mailbox
Click the ‘Create Mailbox’ button. It will generate a random email/mailbox address and the page should look like this.
Sidebar: I’m hiding my mailbox address (and more details in this post) for privacy reasons
Click on ‘Mailboxes’ in the top right part of the page. You should see your mailbox address along with the ‘Create Mailbox’ button and the ‘use this app in Zapier’ link.
Step 3. Send an email to your Parser mailbox
Go to your personal email inbox, select 2–3 transaction alerts your bank has sent in the past, and forward those to your Parser mailbox address.
Step 4. Tell Parser what to extract
If everything has gone as planned, you should see this page:
Click ‘Edit’.
In the main body of this page, you’ll find the contents of the first email you forwarded in Step 3. Now you need to select the bits of this email that are important to you. For me, those bits are amount (spent on purchase), date (of transaction), and remarks (i.e. description of where I spent that money). Selecting is easy:
- Highlight a value you’re interested in
- Give it a name
- Hit ‘Save’
After you’ve saved a value, it changes to the name you saved it as. For example, I selected the value (3,600.00) of ‘Amount’ in the previous image, named it ‘Amount spent’, and now in the image below, 3,600.00 has been replaced with {{amountspent}}.
When you’ve named all the values you need, save the template.
After saving, you should be redirected to the home page (which should also now include a confirmation message).
Select “use this app in Zapier”.
Step 5. Sign up for (or log in to) Zapier
Zapier is a platform that lets you automate tasks between web apps. For example, you can set up a ‘zap’ such that all your mentions on Twitter are automatically saved to a Google Sheet spreadsheet. There are 750+ web apps you can play around with, and I think it’s a pretty nifty tool for businesses, but that’s not why we’re here. Moving on…
Log in if you have an account, and if not, click the small ‘Sign up!’ link at the bottom right of that page — which will bring you to this one:
Remember to uncheck “Email me about new features” if you’re not keen on such things.
Step 6. Create a Zap (I)
a. Once you’re logged in, this is the page you should be seeing:
If that’s not what you see, click here.
b. Type ‘zapier’ in the search bar on the right, and scroll till you see ‘Email Parser by Zapier’. Select that.
c. Now select the ‘New email’ trigger, then hit ‘Save + continue’ (the button at the bottom right will turn blue once you’ve selected the trigger)
d. You’ll be required to connect your Parser account, like so:
…and authorize it — the permissions page opens in a new window
Once that’s done, hit ‘Save + Continue’
e. Select a mailbox. On the next page (see image below), click the dropdown button…
At the top of the list, you’ll see the mailbox you set up in Parser.
Select that, then hit ‘Continue’.
f. You’ve made the 1st part of your zap! You’ll need to test it, and be sure to double check all the things.
Hit ‘Fetch & Continue’ when you’re ready.
g. Review the test results
Looks like Parser has successfully extracted the info I want from one of the emails I forwarded earlier. You can hit “Re-test Email Parser…”, or ‘Continue’. Mine looks good, so I’m moving on.
Step 7. Create a Zap (II)
a. Start Step 2 of your Zap. Type ‘airtable’ in the search bar, and select it when you find it.
Sidebar: Airtable is a database web app which, on the surface, is very similar to Google Sheets or MS Excel. We could use Google Sheets for this, but I’m choosing to go with Airtable in this case because that’s where I already track my expenses.
b. For the next portion, you’ll need to have an Airtable record. If you don’t already have one, here’s a template:
To copy this template, click ‘Copy base’ at the bottom of the embedded file above. If you don’t already have an account, you’ll be guided to create one.
c. Once you’re all set, go back to Zapier and select ‘Create record’.
What we’re trying to do is have Parser create a new row in our Airtable base (sheet) every time we get a debit alert, and fill in the most important columns for that row (which for me are amount spent, date, and description). We can update the other fields (category, etc) later.
Hit ‘Save + Continue’ once you’ve selected ‘Create Record’.
d. On the next page, click ‘Connect a new account’, and you’ll be directed to a page asking for an API key (this page may open in a new window)
To get the API key, go to your Airtable settings, and select ‘Account’
Scroll till you see ‘Generate API key’. Click that, and copy the 17-character string that’s generated.
Paste that key in the authorization window (see image captioned ‘Zapier x Airtable’ above) to allow Zapier access your Airtable account, and click to continue.
Once that’s done, this should be the result:
Hit ‘Save + Continue’.
e. The next step is to select the base you want to connect (to create a new record in). I’m using the base embedded above, Expense Tracker x Parser.
Select a table from the base for your record to go in. I have 3 tables — recurring, one-time, and budget. I’m selecting ‘Recurring’, because that’s where most of my expenses go.
f. Once you’ve selected a base and table, you get to choose the particular columns/fields where your Parser content should go.
And if you click on any of the fields, you can select what particular value goes there. I’ve selected the ‘Amount spent’ field, and now I have…
…a bunch of variables to choose from, including the ones I saved in Parser!
There’s still a bunch of stuff that’s not shown in the image, but basically everything you saved in Step 4 will show up in this dropdown, and each saved variable will be titled ‘Parse Output [what you saved value as]’. For example, I saved something as ‘amount spent’ so it comes out as ‘Parse Output Amountspent’.
After plugging in my Parser content, here’s what I have:
g. Review everything, and test! (click ‘Create & Continue’)
Everything looks okay on this end…
…but I need to see how it looks in Airtable.
And there it is — complete with a description, date, and amount. All that’s left is to fill in the actual expense (groceries in this case) and category.
Step 8. Turn on the zap
Now for the easy part: Once you’re satisfied, hit ‘Finish’ in Zapier,
then give your Zap a name,
and turn it on!
Step 9. Remember to forward transaction alerts
You’ve accomplished quite a bit at this point, well done! In the future, you only need to remember to forward your debit alerts to Parser for everything to work as planned — or set up an email filter to forward it automatically. Well, that and some occasional ‘housekeeping’ in Airtable where you define the expenses, categories, and month.
Sidebar: The ‘month’ field in the Airtable template I shared is tied to the ‘Budget’ table, so if you’re looking for a particular month (say, Feb 2017) and it doesn’t appear on the list, go to ‘budget’ and add it. Add a ‘Projected expenses’ amount too (the other columns are calculated automatically).
Conclusion
Thank you for reading! This post is the result of an experiment aimed at enabling my laziness, but I hope you found some parts of it useful. If you have questions, I’m more than happy to answer.
