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

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

Typical expense on Aug 2019

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.

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.

Python script to automate the forex conversion

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.

Python at work

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.

$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

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 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:

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.


View Original