Automating Expenses

macOS
1

Hi guys,

I’m fairly new to automation, but I have a particular manual process that I would love to automate, and would save me hours of tedious work each week!

I’m a consultant who works on the road, and so I have various expenses including travel, hotels, meals, etc. Each week I have to track those expenses in a standard Excel sheet provided by my company, each entry requires a date, description, and the correct amount entered in an appropriate category column. I usually end up doing it all at the end of the week, tracking down emails and sorting bunches of paper receipts, organizing them and then tediously entering them all into the expenses sheet.

I regularly book the same hotel and travel tickets, and get email confirmations/receipts, whilst for meals, it’s usually a case of getting paper receipts from restaurants.

Ideally, I’d like to be able to automatically have something where the hotel/travel emails are automatically added to my expenses spreadsheet when they arrive in my inbox (and are then forwarded to Evernote for archiving/later reference if needed). Unfortunately, the rates/prices of the room and travel tickets can vary week to week, so I’d need to ideally be able to grab these values directly from the email/PDF attachments.

I currently use Airmail 3 on my MacBook Pro, and also have Hazel, IFTTT and Dropbox accounts. I’m a programmer by trade, so don’t mind getting my hands dirty (although, I’m pretty new to AppleScript, but could pick it up).

I realise this is an ambitious first automation project, so I’m happy to break it down into smaller steps:

  1. Identify potential expenses email receipts (easily done through Airmail 3 rules given they’ll always come from known address and/or contain specific subject lines).
  2. Do something with those emails to extract the required data.
  3. Add entries to a spreadsheet - the problem here may be entering the data in the right format into my specific company spreadsheet, but I’m happy to just get the data into a temporary spreadsheet and then manually copy/paste into my company spreadsheet if necessary.

This would be a great first step for me, and I can deal with paper receipt entry at a later date!

I appreciate any pointers in the right direction of how I might go about steps 2 & 3 above.

Thanks!

2

I would suggest using Airmail to export these emails as PDFs and then use Hazel to grab the information out of them. @MacSparky has the Hazel video field guide which has a great section on date matching and extracting information - I did something similar to extract expense information from PDF invoices for my Mum, but the exact rules I have are likely useless to you (unless you’re in the UK and buy lots of train tickets on Great Western Rail…!).

3

Thanks for the quick reply and the pointer to David’s field guide Rosemary, it sounds like a great start!

I guess I could also do something where I could maybe use my iPhone camera to capture my paper receipts, drop them into Dropbox and have Hazel do something similar? I shall have a play about!

(BTW - I am in the UK and my travel is entirely West Coast Mainline train tickets and London Oyster Card!)

4

At least Oyster card will let you grab all your journeys! :smiley:

I would highly recommend something like Scanner Pro or ScanBot to scan and OCR your paper receipts. I like Scanner Pro as it has automations built it - e.g. “Name it like this and save it to this Dropbox folder”, ideal for expenses.

5

Yes, I just set up my Oyster Card account to send me my weekly journey history as PDF and CSV email file attachments as a step towards full automation. Finding that option on the Oyster site was really what sparked the idea to automate all of this admin stuff I have to do.

Scanner Pro looks great! I’m literally downloading it now! :slight_smile:

1 Like
6

Hey @RosemaryOrchard, I just wanted to give you (and others!) a heads up on my progress on this.

I think I’m on my way to building a complete system that works for me and my situation, but it’s proving to be a larger exercise that I first anticipated! That said, it’s slowly coming together.

So, I receive receipts through various channels each week, some email, some PDF, some paper. So, my initial aim is to get everything in a common format in a common location. To do that, I wrote a simple iOS Workflow to convert emails to PDF and save them to a folder on Dropbox. PDF receipts also get dropped in there.

And based on your recommendation, I bought ScanBot Pro for iOS and scan/OCR my paper receipts as I receive them. I built a small workflow in ScanBot Pro to save the scanned images to the same DropBox folder. I’m having some difficulty with OCR’ing scanned paper receipts as I’m having a lot of mixed results, but I’m going to play about with better lighting/contrast/settings/etc.

So, once all the PDFs are in a DropBox folder, I have a set of Hazel rules that are looking for specific receipt types (I generally use the same transport ticket/hotel sites, and eat in regular group of restaurants), the rules extract appropriate information from the PDF (ie, date and amount) and then rename the PDF into the format ||.pdf and moves it to another folder for further processing (receipt type is hardcoded into the individual rule).

This is where I’m up to at the moment. My next step is to process the renamed files and create a CSV file containing each element of the filename (which is why the filename format uses pipes to delimit each data item). I’m not sure how I’m going to do this yet, but Hazel rules executing AppleScript seems likely to me!

As I said, I have a few issues with OCR at the moment, so I built another iOS Workflow which allows me to select a scanned PDF receipt from my Dropbox folder, opens it as a quick view, then prompts me for the date, receipt type (from a predefined list) and amount. It then renames the PDF to the same format as the Hazel rules above and moves it to the second directory for processing.

If this is all I get to, then it’ll save me a huge amount of time and work each week. However, I have plans for further improvements, I can see how easy it is to get carried away with automation once you start seeing serious benefits for a small amount of work up front!

1 Like
7

Do we need a separate thread to discuss the automation merits of Scanbot an Scanner Pro?

(Somehow I ended up with both, which is so often the way.) :slight_smile:

1 Like
8

It’s just occurred to me that I said I got “ScanBot Pro”, when actually I got Scanner Pro!

I may take a look at ScanBot and see if I have any more success. Also, a discussion of how to fit them into automation process would be useful!

9

Hi Rose, do you know if it will work with OneDrive?