Banging out expense reports like a boss

(with Python of course)

Let’s be honest, expense reports suck. There’s no ifs, ands, or buts about it. Having to take your personal time to put together a list of purchases for a manager to review feels like it wasn’t your money that was spent to begin with.

Photo by StellrWeb on Unsplash

So, how can we mitigate the suck? Well, friends, I have one suggestion that may help: let your computer do the work. With a little bit of Microsoft Excel mixed with a dash of Python, you can transform what would have previously taken you hours into a simple thirty minute task.

How?

I’ll break it down into a five step process.

Step 1:

Retrieve the mess of receipts you’ve been collecting over the last three months out from wherever you’ve been storing them. For me, I keep all of my receipts in the center console of my car.

Step 2:

Open up Excel, create a new workbook, and add a table that looks something like this.

Note: The ID column is simply a numerical count of the number of items in the list. We’ll use this data later.

Step 3:

Begin logging your expenses. From the mass of receipts you have, pull one out, log its data in excel, take a picture of it on your phone, set the receipt aside, and repeat this process until there is no more receipts left to log. Your excel table should now look something like this.

Your cost codes can be whatever you chose. I mostly expense food (F), gas (G), and office supplies (OS).

Nice. The hardest part is over. Now sit back and let the computer drive da boat.

Step 4:

Remember those pictures we were taking? They’re about to come into use. It’s time to export them from our phone to our computer. Personally, I have an iPhone so exporting the photos is as easy as signing into iCloud and downloading them.

Screenshot of file folder after downloading from iCloud and extracting all contents from the zip file.

Step 5:

As you can see, the photos we downloaded have various names, for example in my folder the first picture is “IMG_5497.JPEG” and the last picture is “IMG_5531.JPEG”. But that’s not really any use to us. Because in our report we started our numbering, or IDs, at 1. So we will need to write a program to rename the files in this file directory.

You can change the variable “files_location” to the path of your folder containing the photos you wish to rename. The program will rename each photo starting with the first and continuing until it is has renamed each file in the folder.

Run this program. And voila! You have just successfully renamed each receipt’s picture to its correct table ID number. Your file folder should now look something like this.

Step 6:

Don’t quit yet, because we’re almost done. All that is left to do now is display your data in an effective way. Not only will doing this show your boss how proficient you are at organization, but it may also allow you to sneak in a couple “questionable” line items. ~Thanks for the beers boss!

For this example, we’ll use a Microsoft Excel pivot table. If you haven’t played with pivot tables they’re really easy to use. Simply click any cell within your expense table, flip to the data “Insert” tab in excel, and in the top left select, “Pivot table”.

After doing this you’ll see a prompt from excel regarding the creation of a pivot table. Click, “Ok” and a new sheet within your workbook will be created.

Now that we have our pivot table created, we just need to drag the pivot table fields to the appropriate field areas. This will instantaneously categorize our data for us.

Column and row labels can be changed by selecting the cell and pressing F2.

Congratulations! You have just successfully listed your expenses, generated backup documentation for your claims, and categorized your expenses for easy interpretation. Now all that’s left to do is send out an email containing your newly created excel table and your backup documentation folder. But I’m sure you don’t need my help for that.