Automation with AI (Part 1) | Creating VBA Scripts with ChatGPT

Will ChatGPT replace accountants? No, but it can make their lives easier, especially in areas that previously required really good relations with the IT department.

Technology is advancing at an incredible pace, and with it, the amount of data in our organizations is growing. What else has significantly increased in recent years? The number of new obligations resulting from regulatory changes. The effect? Piles of additional, often tedious and repetitive work, and massive amount of data to process. I will show you examples of how we can perform these routine and tedious processes more efficiently by automating them appropriately. This way, we can dedicate more time to more interesting tasks that bring value and satisfaction. This is here generative artificial intelligence comes to our aid, so that using only natural language (and therefore no programming) we can, among other things, create effective macros or find inspiration for improvements in other processes.

APPLICATION OF AI IN ACCOUNTING

Let’s take a look at how the capabilities of ChatGPT can be utilized in the field of accounting and taxes. Let’s break down the data workflow in these areas into four sections:

1. Data Collection

With ChatGPT, we can create intuitive and clear templates for collecting high-quality data more quickly. For example, data can be gathered through forms, questionnaires, or readable and easy-to-fill tables.

2. Data Cleaning

In this step, AI-generated scripts can help remove duplicates, fill in missing information, standardize formats, and remove special characters that are not accepted by our systems in subsequent stages.

3. Data Processing

After collecting and cleaning the data, AI-generated scripts can assist us in merging data, creating financial reports, or adjusting files to the structure required by our financial accounting system.

4. Verification

The increasing complexity of regulations means that more and more transactions require more detailed analyses. AI-generated scripts can assist accountants by automatically checking for threshold exceedances or compliance with specified conditions. With the help of scripts, we can receive a list of transactions that require further in-depth analysis.

Practical Example – Convenient Data Merging

Let’s demonstrate with a simple example how we can use ChatGPT to create a script in Visual Basic for Applications (VBA) that merges data from multiple files. The task is as follows: combine data from dozens of files and tables into one file. We have 24 files, 48 tables, and thousands of rows. Let’s work with ChatGPT to create a VBA script that will merge this data with a single click.

1. Defining Needs and Assumptions

Let’s start by analyzing the process we want to automate and define our objectives. Describe this as precisely as possible.

• Combine multiple files saved in xlsx format, each containing 2 tabs with data, into one consolidated file for further analysis.
• The structure of the input files is consistent – they contain 2 tabs with 10-column tables. The table headers are in the first row.

• Use the tools already included in the Microsoft 365.
• The files to be merged are located in the directory [C:\Examples\1\Input].
• The number and names of files change
• The tab names in the files do not change: “VAT naliczony” (Input VAT) and “VAT należny” (Output VAT).
• Data merging will be performed periodically on a varying number of files.

To increase the chances of receiving a fully functional script on the first response, it’s important to remember a few key things. First and foremost, precisely define the assumptions, input data structure, and expected output format.

2. Creating the Prompt

Based on the collected information, we create a prompt, which is a query to ChatGPT. Clarify in the prompt that during table merging, we want the header names not to be duplicated and for the program to ask the user where to save the generated file. Remember that general and ambiguous terms can lead to less precise responses and decrease the chance of obtaining a satisfactory result.

Here is the prompt along with the model’s response:

3. Verification

Before entering your first code into an Excel workbook, you must first add a Developer tab to the Ribbon. To do this, you need to:

1. On the File tab, go to Options and Ribbon Customization.
2. In the Customizing Ribbon and Home tabs area, select the Developer checkbox.

After activating the developer’s tab, just use the Alt + F11 keyboard shortcut to open the VBA editor. Enter the code in the main editor window.

After entering the code, we can close the VBA editor. To run the macro you should:

1. Select Developer Tab.
2. Click the Macros button
3. Select the appropriate macro name and click the run button.

After running the macro and specifying the location to save the file, I received the following message:

Let’s look at what our output file looks like:

The macro worked as expected and did not require any changes to the code, although this is not always the case. Sometimes the code generated by ChatGPT may be faulty and not work. In such cases, you should describe the error and ask the model to fix it. Usually, after a maximum of a few iterations, the error is located and corrected.

We obtained an output file with combined data in two tabs related to VAT Incurred and VAT Due, with an additional column [VAT Period]. Providing detailed descriptions of our requirements and assumptions in the prompt, including information about paths, tab names, and file structure, allowed us to obtain a working macro.

4. Adding more functionalities

After merging the files, we can add further elements to automate the process. For example, ChatGPT can create a script that generates a file with a list of transactions that meet the criteria we set. Let’s discuss this using the example of transactions subject to the split payment mechanism. VAT taxpayers are required to apply this mechanism if:

1. They sell or purchase “sensitive” goods or services, which are listed in Annex 15 to the VAT Act, and simultaneously,
2. The total invoice amount, i.e., the gross value of the entire invoice, exceeds 15 000 PLN.

The split payment mechanism must be applied if even one item on the invoice, whose total gross value exceeds 15 000 PLN, concerns sensitive goods or services.

To get a list of transactions that meet the above conditions, we could define in the prompt when a given transaction should be included. Looking at the file structure, we could specify that we want to receive items that meet the following conditions for individual rows:

1. In column G, the value equals 1 (The transaction is marked with GTU code 02, which covers sensitive goods).
2. The sum of the values of items in columns from I to J is greater than 15 000 PLN.

We can apply a similar approach to creating scripts that verify and search for items that meet other conditions specified by us.

SUMMARY

If you are just starting your adventure with creating macros, it is worth starting with simple examples and gradually moving on to more complex tasks. Maybe you want the macro to create a new file with selected tabs and contain values instead of formulas? Or do you need a macro that copies data from one file to another? Or maybe you want to automatically create a PDF file and save it in a specified location? The potential of generative artificial intelligence in accounting is enormous. Its incredible advantage is that we can get solutions perfectly tailored to our needs. And all of this by having a conversation in natural language, as if we were asking a more experienced colleague to perform a task, not an AI model. Something that was rather the domain of science fiction movies not long ago is now within our reach.