If you are part of an accounting close, you are probably no stranger to non-value-added manual processes. A classic one would be downloading some files off of our accounting software, copying and pasting into a working paper before transforming the data. These steps are critical but they are repetitive. They often don’t require any critical thinking other than trying to recall what we did last month. Yet, the time it takes to do those tasks add up. Thirty minutes per task multiply by ten tasks quickly become five hours you will not get back.
Since I have started using ChatGPT daily at work, I have been treating it like an all-in-one search engine and competent coworker. Recently, I decided to set aside some time to streamline our GST/HST working paper by applying what I learned about Power Query. However, since I am a Power Query beginner, I thought ChatGPT would be a handy tool to help me achieve what I want without the necessary knowledge. Spoiler alert, it did.
In this post, I want to share a step-by-step example of how I saved time with ChatGPT and Power Query in Finance. Specifically, how I streamlined our GST/HST working paper.
Want to learn more ways to leverage AI in Finance? Read more posts like this here.
The Old Process
- Download three files from our accounting software:
- vendors list
- GST/HST GL data dump for the month
- billings summary for the month
- Copy and paste each files from Step #1 to three different sheets
- Format data, filter and add/remove columns (some with formula)
- Review transactions for accuracy and compliance with tax laws
Step #2 and #3 are non-value-added steps that are time-consuming and repetitive. Also, these steps are prone to human error. My objective with Power Query is to automate these two steps so I can get more done with less time while maintaining or even improving accuracy.
The Power Query Process with ChatGPT
- Download three files from our accounting software:
- vendors list
- GST/HST GL data dump for the month
- billings summary for the month
- Go to Data > Refresh All
- Review transactions for accuracy and compliance with tax laws
While we only cut one step down, new Step #2 takes less than 5 seconds to do. Unlike the old Step #2 & #3 which used to take me at least 20 minutes (longer if it’s your first time).
The Step-By-Step Process of How I Saved Time with ChatGPT & Power Query
Wherever applicable, I put the name of the Power Query step at the front of the step. You will also see that I ask ChatGPT to be concise as I wanted the steps without explanation.
1. I staged the working paper (WP) with the following sheets: Summary Page, Vendor List, GST/HST GL and Billings Summary
2. I downloaded the three files off of my accounting software and saved them in the same folder as my WP
3. [Data Import] I needed to import the CSV files into my WP so I asked ChatGPT for instructions:
4. [Merge Queries] I then wanted to add the vendor category from Vendor List to the GST/HST GL by matching the vendor names in both queries. Here is how I phrased my question for ChatGPT:
5. [Enter Data] On the Billings Summary data, I wanted to add some calculation columns. To do so, I need to first add a database containing the tax rates. I did not want to add that table to the WP so I asked ChatGPT how to enter a lookup table directly on Power Query:
6. [Merge Queries] I followed the instructions from Step #4 and added the tax rates from TaxGrpRates to the Billings Summary by matching the Tax Group in both queries.
7. [Custom Column] I then wanted to add a new column on Billings Summary to calculate the tax amount by multiplying the net amount of the bill by the tax rate. I asked ChatGPT:
8. [Close & Load to] Now that I am satisfied with my tranformed data, I was ready to import them into my WP. I asked ChatGPT:
9. Now, whenever I download new source files from my accounting software, all I have to do is go to Data > Refresh All.
