Writing advanced Excel macros with ChatGPT-4 – Journal of Accountancy
This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.
AICPA & CIMA:
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". Williams used ChatGPT-3.5 (GPT-3.5) for that article. ChatGPT-4 (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 the most sophisticated AI system, known as 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 GPT-4. 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 chat 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 GPT-4, and then change the resulting code in the spreadsheet. Or you can describe the problem in the GPT-4 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:
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:
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:
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. 
The Journal of Accountancy is now completely digital.
SPONSORED REPORT
The new accounting standard provides greater transparency but requires wide-ranging data gathering. Learn more by downloading this comprehensive report.
FEATURED ARTICLE
CPAs assess how their return preparation products performed.
NEWS APP
This quick guide walks you through the process of adding the Journal of Accountancy as a favorite news source in the News app from Apple.
© Association of International Certified Professional Accountants. All rights reserved.
Reliable. Resourceful. Respected.
