Writing advanced Excel macros with GPT-4

Many finance and accounting departments rely on spreadsheets to complete critical tasks. Until now, only elite spreadsheet users had the skills to write macros to streamline repetitive tasks. But with the introduction of advanced natural language artificial intelligence (AI) technology, practically anyone with a little programming experience can create complex scripts that automate many repetitive tasks. This can increase productivity and free up time for work on more value-add projects.

Kelly Williams, CPA, wrote about her early use of ChatGPT in the January 2023 JofA article “Using ChatGPT With Excel“. Because the example in that article was based on the free version of ChatGPT, the code in the article was generated by GPT-3.5. GPT-4, launched in March 2023, is reportedly trained on more than 100 trillion data points, compared to 175 billion for GPT-3.5. The more recent version has better comprehension skills and better skills for writing and debugging software. Additionally, GPT-4 accepts longer and more detailed prompts. With these advancements, we can now use GPT-4 to take on bigger macro writing challenges.

It is common practice to download data from one system into a spreadsheet, edit and clean it, and then upload it into another system. Another typical procedure entails obtaining data, cleaning it, appending data from other systems, and then summarizing it with the aid of PivotTables and other tools. When working with large amounts of data, this can be time-consuming and error prone. Usually, most of these steps are tediously repeated in the same order month after month for years.

With the right prompts, natural language AI can create complex macros to automate processes like data cleansing, data summarization, carrying out intricate computations, building PivotTables, and producing unique reports. While most of the generative AI tools can probably create macros, this article focuses on how to write prompts for OpenAI’s GPT-4 to create complex Excel macros.

Step 1: Create an account

You will first need to create an account on openai.com. You should sign up for a premium account, which costs $20 per month and offers you access to GPT-4. Free accounts only gain access to GPT-3.5.

Step 2: Develop your prompt

You can tell GPT-4 what duties to carry out by giving it a prompt. The simplest way to create your prompt is to carefully outline the actions you do in Excel in a simple text editor like Notepad. It is crucial to number the stages in the prompt and ensure that this numbering copies and pastes into the ChatGPT interface. Try to be as specific and thorough as you can. Save this document because, while creating the macro, you’ll probably need to add more steps or change the ones you already have. Your instructions might not be interpreted by GPT-4 as you planned.

Step 3: Enter your prompt

It’s time to enter the prompt once you’ve created one with a thorough and accurate description of the actions. Open a ChatGPT window on openAI.com and choose the GPT-4 model. The prompt from Step 2 should be copied and pasted into the prompt box. A code will be generated by the system along with instructions on how to enter it in Excel or Google Sheets.

The system might halt if the output code is particularly long. The system will produce the remaining code if you simply type continue.

Step 4: Testing and adjusting

Copy the code into the spreadsheet after that, then save. Now that you’re prepared, run the macro. GPT-4 can assist you in troubleshooting any issues you run into and can also offer comprehensive instructions on how to add the macro to the spreadsheet.

It is crucial to have a backup of your files saved before executing a macro because there is no way to undo it once it has been started.

Additionally, the macro’s initial version might not function precisely how you want it to. In that case, you would modify the prompt in Step 2, enter it again into ChatGPT, and then change the resulting code in the spreadsheet. Or you can describe the problem in the ChatGPT prompt box, and the system will adjust the code. You keep repeating these steps of testing and adjusting as necessary until the macro produces the required results.

All macros saved in your Personal.xlsb worksheet in Excel are accessible from any open workbook, unlike macros created in other workbooks, which are only accessible when the relevant workbook is open.

Privacy cautionary note

Never type any confidential information into an AI prompt. Any prompt entered may be examined by a person and compared to the response to find areas for improvement. Your prompts should always remain as generic as possible.

Example: Data cleanse, complex computation, and summarize

In our example, we are tasked with calculating commissions for a global sales force based on the kind of product, where it is located, and how many units are sold.

After we obtain the raw data, we basically follow the same procedure every month.

Rows of data that are unrelated to the current month must be removed, and the product code must be parsed to determine the product type and sales location.

We use these guidelines to calculate the commission:

• Commissions are 5% of sales for Europe and North America.
• APAC commissions are 7% and LATAM commissions are 6% of revenue.
• Commissions for in-person sales are an additional 1% of sales.
• Anyone who sells more than 50 units gets an additional 1% of sales commission.

This is the sample raw data:

The prompt

This is the prompt we would enter in GPT-4:

Write an Excel VBA Macro called CalcSalesCommission to complete the following steps:

1. Remember the current sheet.
2. Identify the highest date in Column A.
3. Prompt the user to confirm they wish to process the date identified in Step 2. The default should be the date from Step 2 with the option to overwrite the choice (use the mm/dd/yyyy date format).
4. Delete any row that does not have the date identified in Step 3.
5. In row 1, starting with column F, put the following titles “Product Code,” “Region Code,” “Delivery,” “Commission Rate,” and “Commission Amount.”
6. For each row in the table do the following:
   1. Put the first 7 characters of Column B in Column F.
   2. If Column B contains “-EU-,” put “EU” in Column G.
   3. If Column B contains “-NA-,” put “NA” in Column G.
   4. If Column B contains “-LATAM-,” put “LATAM” in Column G.
   5. If Column B contains “-APAC-,” put “APAC” in Column G.
   6. If Column B contains “InPerson,” put “InPerson” in Column H.
   7. If Column B contains “Online” put “Online” in Column H.
7. For each row in the table do the following:
   1. If Column G contains EU or NA, put 0.04 in Column I.
   2. If Column G contains APAC, put 0.07 in Column I.
   3. If Column G contains LATAM, put 0.06 in Column I.
   4. If Column H contains “InPerson,” increase Column I by 0.01.
   5. If Column D is equal to or greater than 50, increase Column I by 0.01.
8. For each row in the table insert a formula in Column J to multiply Column E by Column I.
9. Format Column I to percentages with 1 decimal place.
10. Format Column J to Currency with 2 decimal places.
11. Fit all columns to the data width.

If the response from GPT-4 is longer than normal, when it stops, type Continue, and carefully combine the two responses together when entering into Excel.

The response from GPT-4

Responses from GPT-4 can vary slightly for the same identical prompt, but will look something like this:

Sub CalcSalesCommission()
  Dim ws As Worksheet
  Dim highestDate As Date
  Dim processDate As Variant
  Dim rng As Range
  Dim cell As Range
  Dim lastRow As Long
  ‘ Step 1: Remember the current sheet
  Set ws = ActiveSheet
  ‘ Step 2: Identify the highest date in Column A
  highestDate = Application.WorksheetFunction.Max(ws.Range(“A:A”))
  ‘ Step 3: Prompt the user to confirm they wish to process the date identified in Step 2
  processDate = Application.InputBox(“Please confirm the date you wish to process (mm/dd/yyyy):”, “Process Date”, Format(highestDate, “mm/dd/yyyy”), Type:=2)
  If IsDate(processDate) Then
             processDate = CDate(processDate)
  Else
    MsgBox “Invalid date. Exiting.”
    Exit Sub
  End If
  ‘ Step 4: Delete any row that does not have the date identified in step 3
  lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
  Set rng = ws.Range(“A2:A” & lastRow)

  For Each cell In rng
    If cell.Value <> processDate Then
      cell.EntireRow.Delete
      Set rng = ws.Range(“A2:A” & lastRow – 1)
    End If
  Next cell

  ‘ Step 5: In row 1 starting with column F put the following titles
  ws.Range(“F1:J1”).Value = Array(“Product Code”, “Region Code”, “Delivery”, “Commission Rate”, “Commission Amount”)
  ‘ Step 6: For each row in the table do the following
  For Each cell In rng
    If cell.Value = processDate Then
      With cell.Offset(0, 5)
        .Value = Left(cell.Offset(0, 1).Value, 7) ‘ Step 6(a)
        If InStr(cell.Offset(0, 1).Value, “-EU-“) > 0 Then .Offset(0, 1).Value = “EU” ‘ Step 6(b)
        If InStr(cell.Offset(0, 1).Value, “-NA-“) > 0 Then .Offset(0, 1).Value = “NA” ‘ Step 6(c)
        If InStr(cell.Offset(0, 1).Value, “-LATAM-“) > 0 Then .Offset(0, 1).Value = “LATAM” ‘ Step 6(d)
        If InStr(cell.Offset(0, 1).Value, “-APAC-“) > 0 Then .Offset(0, 1).Value = “APAC” ‘ Step 6(e)
        If InStr(cell.Offset(0, 1).Value, “InPerson”) > 0 Then .Offset(0, 2).Value = “InPerson” ‘ Step 6(f)
        If InStr(cell.Offset(0, 1).Value, “Online”) > 0 Then .Offset(0, 2).Value = “Online” ‘ Step 6(g)
      End With
    End If
  Next cell
  ‘ Step 7: For each row in the table do the following
  For Each cell In rng
    If cell.Value = processDate Then
      With cell.Offset(0, 8)
        Select Case cell.Offset(0, 6).Value ‘ Step 7(a), 7(b), 7(c)
             Case “EU”, “NA”
                        .Value = 0.04
                        Case “APAC”
                        .Value = 0.07
                        Case “LATAM”
                        .Value = 0.06
            End Select
      If cell.Offset(0, 7).Value = “InPerson” Then .Value = .Value + 0.01 ‘ Step 7(c)
      If cell.Offset(0, 3).Value >= 50 Then .Value = .Value + 0.01 ‘ Step 7(d)
    End With
  End If
Next cell
‘ Step 8: For each row in the table insert a formula in Column J to multiply Column E by Column I
For Each cell In rng
  If cell.Value = processDate Then
    cell.Offset(0, 9).Formula = “=E” & cell.Row & “*I” & cell.Row
  End If
Next cell
‘ Step 9: Format Column I to percentage with 1 decimal place
ws.Range(“I2:I” & lastRow).NumberFormat = “0.0%”
‘ Step 10: Format Column J to Currency with 2 decimal places
ws.Range(“J2:J” & lastRow).NumberFormat = “$#,##0.00”
‘ Step 11: Fit all columns to the data width
ws.Cells.Columns.AutoFit
End Sub

Adding the code to your Personal Macro Workbook

To enter the VBA code in your Excel Personal Macro Workbook (PERSONAL.XLSB), follow these steps:

1. Open Excel. You don’t need to open any specific workbook since the Personal Macro Workbook is available across all Excel workbooks.
2. Press Alt+F11 to open the Visual Basic for Applications (VBA) editor.
3. In the VBA editor, locate the VBAProject (PERSONAL.XLSB) in the Project Explorer window (usually on the left side). If you don’t see the Project Explorer window, you can enable it by going to the View menu and clicking Project Explorer.
4. Click the + symbol or double-click VBAProject (PERSONAL.XLSB) to expand it.
5. Right-click on Modules under VBAProject (PERSONAL.XLSB) and select Insert > Module to insert a new module into the Personal Macro Workbook. A blank code window should appear.
6. Copy the entire CalcSalesCommission macro code provided in the previous responses, and paste it into the blank code window in the VBA editor.
7. Close the VBA editor by clicking the X in the upper-right corner, or by pressing Alt+Q.
8. The macro is now added to your Personal Macro Workbook and can be accessed in any Excel workbook on your computer. To run the macro, press Alt+F8 to open the Macro dialog box, select CalcSalesCommission from the list, and click Run.

Remember that Excel automatically saves any changes made to the Personal Macro Workbook. If you are prompted to save changes when closing Excel, click Save to ensure your new macro is stored in the PERSONAL.XLSB file.

Final results

This macro executes in less than a second due to the small amount of raw data. Large datasets with tens of thousands or more lines, however, can take up to a minute. In our example, when the macro is finished, the spreadsheet will appear as follows:

Google Sheets Apps Script

Apps Script is Google Sheets’ equivalent of the Excel macro. The prompt to GPT-4 can also be modified to write Apps Script for Google Sheets and Gmail. These Apps Scripts generally run much slower than Excel macros when handling large amounts of data but can be extremely powerful when handling collaboration tasks such as emails, sharing sheets, granting viewing and editor rights, and routing documents for approval. 

Conclusion

The example shown is just one of many ways that this tool can be used. Macros can be used to open workbooks, use VLOOKUP to grab and append data from other workbooks, slice data into multiple workbooks, and build PivotTables and graphs, among many other tasks. You are limited only by your creativity and persistence.

The way we all work will change due to AI. AI can help small and medium-size finance and accounting departments create macros to accelerate repetitive activities without the need for expensive solutions.

— Fidel Dhana, CPA, MBA, is CFO for Zumba Fitness LLC. To comment on this article or to suggest an idea for another article, contact  Jeff Drew at jeff.drew@aicpa-cima.com.