By Codomo Singapore

How I saved 120 hours and $600 on Xero with Python Automation and Zapier

Money can’t buy you time, but Python can (for free).

xkcd comic on saving time with Phyton

Let's cut to the chase:

Automation of forex conversion: Saved 20 hours

Not using the multi-currency feature in Xero: Saved $600 💰💰

Automating receipts attachments: 100 hours ⏰⏰⏰⏰⏰

Background Story

I have been running a small preschool business in Jakarta, Indonesia for the past 5 years, and have been using Xero as my primary accounting tool. The main currency is Indonesia Rupiah, but I make several purchases through Singapore dollars from time to time as well. To handle this multi-currency, I have to subscribe to Xero’s most expensive package — which costs me $40/month.

Xero premiun packages

I’ll let Steve Carell replay my reaction.


Here are my three objectives

Objective 1:

Convert 🇸🇬 SGD to 🇮🇩 IDR based on the exchange rate of the day of purchase

Objective 2:

Lowest price possible (Hey, we’re running a business here) 💸

Objective 3:

Attach receipts for every transaction in the quickest possible way 📌

OBJECTIVE 1: CONVERT SGD TO IDR USING THE EXCHANGE RATE OF THE DAY OF PURCHASE

Tool used: Python 3

Transactions tabulated on a spreadsheet

Time-wasting-soul-numbing way: For each transaction, I get the forex-of-the-day from any forex website, multiply that with the SGD to get the corresponding IDR. Assuming I take 1 minute for each row, it will take me 20 minutes for 20 transactions.

Quick Math:

1 min x 20 transactions x 12 months x 5 years = 1200 minutes (20 hours)

Using Python: I would rather spend the 20 hours watching the entire Harry Potter series, so I pulled up my Python script and started coding, which took me an hour to complete. I am not going to explain my code in too much detail here as it requires certain basic understanding of programming on your end. 

Python automation code from Potato Pirates

Basically, the flow of the code is simple.

  1. Get Forex information from European Central Bank
  2. Read the CSV file
  3. For every row, get the date to find the currency rate
  4. IDR = currency rate * SGD
  5. Save it > Import it to Xero (done!)

How long does it take? Well, let’s take a look at the following GIF:

It only took me 20 seconds! That’s 3 times faster than any Nas Daily video!

OBJECTIVE 2: LOWEST PRICE POSSIBLE

Now that I can convert multiple currencies by myself (I mean Python), I can use the $30/month subscription plan instead. This means I have saved $600 in the past 5 years time and counting.

Quick Math:

$10 x 12 months x 5 years = $600 (but still cannot buy an iPhone 11)

Xero standard packages

OBJECTIVE 3: ATTACH RECEIPTS FOR EVERY TRANSACTION IN THE QUICKEST WAY POSSIBLE

Tool used: Zapier

It’s always a good accounting practice to attach receipts for every transaction you’ve made, even if it pisses some people off in the organisation. However, searching for those receipts is still a pain in the ass.

FAQs that I have in my mind whenever I search for those receipts:

  1. Which email address was the receipt sent to? Was it to email1@gmail.com or email2@gmail.com?
  2. What?! I need to login to get my receipt?! Can’t you just attach it to the email?
  3. What’s the username and password for this account again?
  4. Where the hell should I click to get my receipt?
  5. Hold on.. is this receipt for this payment period or the previous one? Both have the same amount.

It took me about 5 to 10 minutes to find one single receipt. Let’s do the math again…

Quick Math of the best-case scenario:

5 minutes x 20 transactions x 12 months x 5 years = 100 hours

So I used Zapier to do this. Zapier is an incredible tool to automate things without Python. One downside: Zapier is a freemium software and it costs $20/month if your automation process requires more than 2 steps (but for the time it saves, I don’t mind 🤷‍♂)

This is how a typical workflow in my Zapier account looks like:

Potato Pirates' Typical Zapier workflow

In plain English, these are the steps:

1. When I receive an email from my gmail
2. Only continue if the email’s subject head is <a_subject_head_that_you_need_to_figure_it_out>
3. Save the screenshot (technically the HTML) of the email and save it to a Google Drive folder.(Yes, saving the HTML is equivalent to saving the entire screenshot of the email view)
4. Locate that image in that Google Drive folder mentioned in step 3
5. Create a Bill in Xero that attaches the file mentioned in step 4
6. Move the file to an archive folder in Google Drive (Cleanliness is important ☝🏻)
 

Future Improvements

  1. If you are a programmer yourself, chances are, you would have probably noticed that I could have written a few lines of code that adds the converted currency directly to the CSV; there is no need to copy and paste it from the terminal. You’re absolutely right, I was being lazy at that time. 😅
  2. I’ll see if I can write a Python program to remove the reliance on Zapier. That’ll probably take a bit of work.

That’s it. That’s how I saved my money and time — the world’s most scarce resource.


Free stuff for you:

Enter The Spudnet, cybersecurity board games

Now if you’ll excuse me, I’m going to watch the Chamber of Secrets.

0 comments

Leave a comment

Please note, comments must be approved before they are published