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

    See how AI can be used to create macros in Excel and save valuable time.

    Marcin Pezowicz

    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.

    1. 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.

    1. 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.

    1. 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.
    1. Input file structure
    • 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:

     

    MODEL RESPONSE

    To merge multiple XLSX files into a single summary file using VBA in Microsoft 365, we need to create a macro that meets the stated objectives. Below is the detailed VBA code that accomplishes this goal:

    1. Open Excel and go to the VBA editor (Alt + F11).
    2. Navigate to ThisWorkbook (double-click on “ThisWorkbook” in “VBAProject”).
    3. Paste the following code into ThisWorkbook


    Sub MergeFiles()
        Dim FolderPath As String
        Dim outputWorkbook As Workbook
        Dim inputWorkbook As Workbook
        Dim outputSheet1 As Worksheet
        Dim outputSheet2 As Worksheet
        Dim inputSheet1 As Worksheet
        Dim inputSheet2 As Worksheet
        Dim FileName As String
        Dim LastRow As Long
        Dim lastRowOut1 As Long
        Dim lastRowOut2 As Long
        Dim okresVAT As String
        Dim SavePath As String
        Dim i As Integer
      
        ‘ Path to folder with input files
        FolderPath = “C:\Przykłady\1\Input\”
      
         ‘ Create a new output workbook
        Set outputWorkbook = Workbooks.Add
        Set outputSheet1 = outputWorkbook.Sheets(1)
        outputSheet1.Name = “VAT naliczony”
        Set outputSheet2 = outputWorkbook.Sheets.Add(After:=outputWorkbook.Sheets(1))
        outputSheet2.Name = “VAT należny”
      
        ‘ Downloading a list of files in a folder
        FileName = Dir(FolderPath & “*.xlsx”)
      
        ‘ Initialization of header column flag
        Dim headerSet As Boolean
        headerSet = False
      
        ‘ Processing of each file
        Do While FileName <> “”
            Set inputWorkbook = Workbooks.Open(FolderPath & FileName)
          
            ‘ Downloading bookmarks from the input file
            Set inputSheet1 = inputWorkbook.Sheets(“VAT naliczony”)
            Set inputSheet2 = inputWorkbook.Sheets(“VAT należny”)
          
            ‘ Set headers in the output file (if not already set)
            If Not headerSet Then
                For i = 1 To 10
                    outputSheet1.Cells(1, i).Value = inputSheet1.Cells(1, i).Value
                    outputSheet2.Cells(1, i).Value = inputSheet2.Cells(1, i).Value
                Next i
                outputSheet1.Cells(1, 11).Value = “Okres VAT”
                outputSheet2.Cells(1, 11).Value = “Okres VAT”
                headerSet = True
            End If
          
            ‘ Copying data from the tab “VAT naliczony”
            LastRow = inputSheet1.Cells(inputSheet1.Rows.Count, 1).End(xlUp).Row
            lastRowOut1 = outputSheet1.Cells(outputSheet1.Rows.Count, 1).End(xlUp).Row + 1
            inputSheet1.Range(“A2:J” & LastRow).Copy Destination:=outputSheet1.Range(“A” & lastRowOut1)
            outputSheet1.Range(“K” & lastRowOut1 & “:K” & lastRowOut1 + LastRow – 2).Value = FileName
          
            ‘ Copying data from the tab “VAT należny”
            LastRow = inputSheet2.Cells(inputSheet2.Rows.Count, 1).End(xlUp).Row
            lastRowOut2 = outputSheet2.Cells(outputSheet2.Rows.Count, 1).End(xlUp).Row + 1
            inputSheet2.Range(“A2:J” & LastRow).Copy Destination:=outputSheet2.Range(“A” & lastRowOut2)
            outputSheet2.Range(“K” & lastRowOut2 & “:K” & lastRowOut2 + LastRow – 2).Value = FileName
          
            inputWorkbook.Close SaveChanges:=False
            FileName = Dir
        Loop
      
        ‘ Question to the user about where to save the resulting file
        With Application.FileDialog(msoFileDialogSaveAs)
            .Title = “Zapisz plik wynikowy”
            .FilterIndex = 1
            If .Show = -1 Then
                SavePath = .SelectedItems(1)
                outputWorkbook.SaveAs FileName:=SavePath
            End If
        End With
      
        outputWorkbook.Close SaveChanges:=True
      
        MsgBox “The file was saved as ” & SavePath
     
    End Sub



    Explanation:
    Variable declaration:     We declare variables needed to store paths, file names, workbooks and worksheets.  
    Retrieving the path and name of the output file: The user is asked for the location and name of the output file.  
    Creating a new output file: A new workbook is created with two sheets “VAT naliczony” and “VAT należny”.   P
    rocessing files in a folder: The loop goes through all the XLSX files in the folder, opening them and copying the corresponding data to the output file.  
    Copying headers: Column headers are copied only once, from the first input file.  
    Copying data: Data is copied to the corresponding sheets in the output file, and the name of the source file is added to the eleventh column.
    Closes the files and saves the output file: The output file is saved to the location you selected.  

    Make sure you have the proper permissions to read and write files to the specified locations and that all files are as intended (i.e., have the correct sheets and headers).

    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.
    2. How to turn on developer mode

    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.

    3. VBA Editor View

    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.
    4. How to run a macro

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

    5. Confirmation of saving the final file

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

    6. Output file structure

    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.

    Marcin Pezowicz

    BPO Consultant. Z wykształcenia księgowy oraz automatyk i robotyk. Specjalizuje się w usprawnianiu procesów z obszaru księgowości i podatków.

    Share

    You might be interested in