Automating expense reports
November 24, 2016 at 10:34 PM by Dr. Drang
This is a relatively long post about a relatively short script. It synthesizes several small workflows I’ve used over the past few years, and to explain the synthesis all the parts have to be explained, too.
Time was when making an expense report was easy. I had a preprinted form that I filled out by hand, stapled receipts to, and turned in for reimbursement. When those expenses were billable to a client, a line item in the invoice describing all the expensed items was sufficient.
Sometime in the early 00s, this started to change. Certain clients insisted on seeing the original expense report and associated receipts. This was fine with me, but I wasn’t especially pleased with the way they did it. I’d sent off an invoice the old way, without receipts, and then follow up when payment was in arrears. “Oh, we have a policy of not paying until we have receipts for expenses,” I’d hear. This would piss me off because I’d never been told the policy, and it seemed clearly to be a way of slowing down payment. I decided to attach expense reports to every invoice that went out to forestall this delaying tactic.
At about this same time, my invoices switched from being pieces of paper sent in the mail to PDFs delivered via email. This meant scanning the receipts and assembling a final PDF for delivery from the individual PDF files for the invoice, the expense reports, and the receipts.
The iPhone changed how I handled paper receipts, especially when traveling. Apps like Readdle’s Scanner Pro, which rectify and boost the contrast of photographed documents automatically,1 meant I could scan receipts as I got them and didn’t have to worry so much about keeping the paper versions organized. (I still haven’t quite gotten comfortable with throwing them away right after I scan, but I’m sure that’ll come with time.)
Eventually, I evolved to the system I’ve been using for the past few years:
- For each trip, I create a folder in Dropbox for the receipts.
- Receipts gathered at my computer—airline tickets, for example—are put directly in the Dropbox folder. PDF receipts are dragged there from the emails they’re attached to; receipts that appear on web pages are captured by screenshot and saved there as PNGs.
- Paper receipts gathered on the road are scanned immediately and uploaded to the Dropbox folder.
- When the trip is done, the receipt files are dragged into a Numbers spreadsheet, resized and rearranged for easy viewing. All the expenses are entered in the spreadsheet table, and the document is exported as a PDF.
- When an invoice PDF is generated for the project, all the expense reports are concatenated onto the end of it, and it’s sent off to the client.
The most time-consuming part is the penultimate step. Reading the scanned receipts and entering the values into the spreadsheet certainly isn’t hard work, but it does require a lot of back and forth between the receipt images and the spreadsheet. This is the task I’ve automated, turning the gathering of the receipt files and the data entry into a single step.
As I said above, to understand how the automation works, you have to understand all the parts. Let’s start with my expense report template. It looks like this:
From top to bottom, these tables are:
- A small table with the date of the expense report.
- The main table with all the expense entries.
- A reminder table with expenses I use often. These are the mileage and tolls to the two airports I use and the current IRS mileage reimbursement rate.
Because of the way they happen to be layered in the document, AppleScript sees the reminder table as Table 1, the main table as Table 2, and the date table as Table 3. This will be important when we get to the script.
By the way, because the reminder table has no business being in the final expense report, I delete it before exporting the report to PDF. Because I do this a lot, I made a Keyboard Maestro macro for it.
Now let’s look at how I set up Scanner Pro. In the settings, I have the default name set this way:
The name of the scan starts with the date on which it’s made, in yyyy-mm-dd format. Then there’s a place for the description of the expense and the amount. These are separated from the date and from each other by plus signs.
When I scan a receipt, Scanner Pro assigns the default name to it and gives me a chance to edit that name. I go in and change the desc and amt parts.
I then tap the Share button and upload the scan to the Dropbox folder I set up for receipts. If, for some reason, I don’t have a good enough connection for the upload, that’s OK. The scan is saved to my phone, and I can upload it later.
Here’s the automation: When time comes to create an expense report, I run an AppleScript called “Expense Report,” and it does the following:
Asks me for the project number.
Asks me to select all the receipt image files.
Creates a new expense report, entering today’s date.
Goes through each receipt file in turn, adding it off to the side of the main table and entering the date, project, description, and amount to the main table.
The receipt images are staggered to make them easy to drag around, rotate, and resize.
Because there are usually entries, like mileage and tolls, for which there are no receipts, this script doesn’t do everything, but it does the bulk of the tedious work.
Here’s the script itself:
applescript:
1: set today to (do shell script "date \"+%b %-e, %Y\"")
2:
3: -- get the project number from the user
4: display dialog "Project number:" default answer "G/B"
5: set project to text returned of result
6:
7: -- get the receipt image files
8: set dfolder to ((path to home folder as text) & "Dropbox:") as alias
9: set receipts to (choose file with prompt "Select the receipt files" default location dfolder with multiple selections allowed)
10:
11: -- open the report template and set the date
12: tell application "Numbers"
13: activate
14: set doc to (make new document with properties {document template:template "Expenses"})
15: delay 1 -- might need to wait for Numbers to launch
16: tell doc
17: tell sheet 1
18: tell table 3 to set value of cell 1 of column 2 to today
19: end tell
20: end tell
21: end tell
22:
23: -- set up variables for placing images and entering values
24: set x to 750
25: set y to 50
26: set i to 2
27:
28: -- cycle through all the receipt files
29: repeat with imageFile in receipts
30: -- get the file name w/o extension
31: tell application "Finder"
32: set fileName to name of imageFile
33: set fileExt to name extension of imageFile
34: set fileName to text 1 thru -((length of fileExt) + 2) of fileName
35: end tell
36:
37: -- extract the date, description, and amount from the file name
38: set oldDelimiters to AppleScript's text item delimiters
39: set AppleScript's text item delimiters to "+"
40: set {rdate, desc, amt} to text items of fileName
41: set AppleScript's text item delimiters to oldDelimiters
42:
43: -- back to the spreadsheet
44: tell application "Numbers"
45: tell doc
46: tell sheet 1
47: -- add the image to the spreadsheet
48: set thisImage to make new image with properties {file:imageFile}
49: tell thisImage
50: set position to {x, y}
51: set height to 400 -- retains aspect ratio
52: end tell
53: -- enter the values
54: tell table 2
55: set value of cell i of column 1 to rdate
56: set value of cell i of column 2 to project
57: set value of cell i of column 3 to desc
58: set value of cell i of column 4 to amt
59: end tell
60: -- update for the next receipt
61: set x to x + 20
62: set y to y + 20
63: set i to i + 1
64: end tell -- sheet 1
65: end tell -- doc
66: end tell -- Numbers
67:
68: end repeat
Line 1 uses the date command to get today’s date in a format I like. I know AppleScript has its own date commands (and the spreadsheet itself knows how to format the date), but I feel comfortable with date so I use it.
Lines 3–9 handle the user interaction in a fairly standard way. The default project number is “G/B,” which stands for “general business.” The file picker starts in the main Dropbox folder, so I’ll always be just one folder level away from the receipt files.
Lines 11–21 creates a new Numbers document from the Expenses template and adds today’s date to it. I’m not entirely sure the delay in Line 15 is necessary, or that it’s set to the minimum value, but the script has never failed with a delay of 1 second, and I don’t mind the wait.
Lines 23–26 set the initial values for the x and y coordinates of the receipt images and the row number of the spreadsheet entries.
The rest of the script is a loop that processes each of the receipt files chosen in Lines 7–9. Lines 31–35 get the name of each file and strip off the extension. Lines 37–41 pull out the date, description, and amount from the file name. This is done through the usual “text item delimiters” rigamarole, and you can now see why I decided to separate the date, description and amount with pluses instead of hyphens.
Lines 44–66 return to Numbers to do the rest of the work.
Lines 48–52 put the image off to the right of the main table and resize it. The receipt images typically come in much larger than life sized, so it’s helpful to automatically shrink them down a bit. I was happy to learn that if you just change the height of the image in Numbers, the width changes to keep the aspect ratio constant. That eliminated the need for a few lines of math.
Lines 54–59 fill out a row of the main table, using the values extracted from the receipt file name.
Lines 60–63 update the coordinates for the upper left corner of the next image and update the row number for the next set of data entry.
I wanted the script to save the document, but that caused nothing but grief. First, I wanted to be able to save it to iCloud, but no matter how I tried to get it to save into a subfolder of ~/Library/Mobile Documents, I got an error message, usually about permissions. Saving to my Desktop folder sometimes worked, but sometimes also led to permissions errors. My sense is that this is a sandboxing thing, and that I’ll never figure it out. So I gave up. Saving the document manually isn’t that hard.
One thing that’s nice about this script is that I can run it remotely from my iPad using Screens connected to my office iMac.2 All the receipts are on the iMac because of Dropbox syncing, and I can then save the report to iCloud and switch to Numbers on my iPad to rearrange and resize the receipt images. This gives me a chance to get an annoying but obligatory task done at the end of a trip while I’m on the flight home.
I’ve thought about rewriting the script to act as a Service. That way, instead of selecting the receipt files from a dialog box, I could select them in the Finder first and then run the Service to make the report. I’m not sure that’s any more efficient, but it might be worth trying.
In any event, I now have a system for taking most of the drudgery out of a necessary task. It requires a bit of setup to get the files named right, but it’s really fun to watch the report build itself, especially when I have a dozen or more expenses to enter.
Update 11/25/2016 9:37 AM
An updated version of the script now saves the report to iCloud. Full details here.