Codomo

How I Saved 120 hours And $600 on Xero With Python Automation And Zapier

Cut to the chase:

  • Automation of forex conversion: Saved 20 hours ⏰
  • Not using 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 at 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. If you are a geek like me, go here to get the full documentation and code.
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 see the following GIF.
It 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:
  1. Which email address was the receipt sent to?
  2. Was it to email1@gmail.com or email2@gmail.com?
  3. What?! I need to login to get my receipt?! Can’t you just attach it to the email?
  4. What’s the username and password for this account again?
  5. Where the hell should I click to get my receipt?
  6. 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 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 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:

  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.
    Now if you’ll excuse me, I’m going to watch the Chamber of Secrets.

Tags

Topics of interest