· 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).
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.
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
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.
Basically, the flow of the code is simple.
- Get Forex information from European Central Bank
- Read the CSV file
- For every row, get the date to find the currency rate
- IDR = currency rate * SGD
- 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)
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:
- Which email address was the receipt sent to? Was it to email1@gmail.com or email2@gmail.com?
- What?! I need to login to get my receipt?! Can’t you just attach it to the email?
- What’s the username and password for this account again?
- Where the hell should I click to get my receipt?
- 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:
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
- 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. 😅
- 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:
- How to create a chatroom in 5 minutes with Python
- A card game to teach sorting algorithms
- A board game to teach how the internet works
Now if you’ll excuse me, I’m going to watch the Chamber of Secrets.