How to use ChatGPT to write Excel Macro for Finance

ChatGPT image prompt: “Generate a banner for the article named How to use ChatGPT to write Excel Macro for finance that shows Excel spreadsheet and Visual Basic Code”.

By Artem Rymar
Business Analyst

Intro

Automation and efficiency are the keys to today’s rapidly evolving financial technologies. This article introduces how finance professionals can use ChatGPT, a state-of-the-art AI developed by OpenAI to write Excel macros, enhancing productivity and accuracy in financial tasks. The code in this article automates the conversion of SHIBOR rates data into a format suitable for Kyriba, facilitating both manual uploads and integration via Open Reports Studio.

Summary

Prompt generation: Discuss the first steps in retrieving the data in the proper format for further processing.
Generating the prompt for Kyriba’s Data Requirements: Analyze the data format Kyriba requires for integration and build the right prompt that will be understood by GPT.
Execute the generated code and improve functionality: Check how the Generated Macro is used in Excel, so the data transformation is completed correctly.
Test full Macro functionality: Run the whole macro and review the generated spreadsheets.

Step 1: Initial Prompt for Macro Generation

Defining the Financial Task: The first step involves clearly defining the specific task that the macro should perform. In this case, it is the conversion of SHIBOR rates data from its native format to a structured format that can be uploaded into Kyriba. The data that we will use is in the Excel table format that we retrieve from this website.

Click “Save to Excel” and you will be able to see the following spreadsheet:

Now inside the excel file, let's go to the “Developer” tab, then “Visual Basic” button. You will see the empty workbook:

Crafting the Prompt: Let's create a detailed prompt to generate the initial VBA code.
- Initial Prompt: "Provide me an excel macro that will transform my input data from the sheet following my requirements. I have an input with the SHIBOR Market rates in an xlsx table format where the first "Date" column is a date of the rate, and other columns are rate names (O/N 1W 2W 1M 3M 6M 9M 1Y). Perform a data transformation so that the output table has 5 columns: businessDate - business date is (comes from the Date and in the format of DD-MM-YYYY), transactionTime - leave empty, rate - the defined rate (1.72), productCode - transformed header name from the input table. For example, O/N will be SHIBORON, uniquely for this case, for others 1W - SHIBOR1W, 2W - SHIBOR2W. The prefix SHIBOR should be preserved. Columns securityId and productDescription - leave empty."

Step 2: Generating the Basic Macro

ChatGPT’s Response: ChatGPT uses the provided prompt to generate a basic VBA macro. This macro includes commands to access the API, parse the received data, and populate an Excel worksheet.

Initial VBA Code Example:

Sub TransformSHIBORData()
    Dim wsInput As Worksheet
    Dim wsOutput As Worksheet
    Dim inputRow As Long
    Dim inputCol As Long
    Dim outputRow As Long
    Dim lastRow As Long
    Dim lastCol As Long
    Dim rateName As String

    ' Set worksheets
    Set wsInput = ThisWorkbook.Sheets(1)
    Set wsOutput = ThisWorkbook.Sheets.Add(After:=wsInput)
    wsOutput.Name = "TransformedData"

    ' Set headers for output sheet
    wsOutput.Cells(1, 1).Value = "businessDate"
    wsOutput.Cells(1, 2).Value = "transactionTime"
    wsOutput.Cells(1, 3).Value = "rate"
    wsOutput.Cells(1, 4).Value = "productCode"
    wsOutput.Cells(1, 5).Value = "securityId"
    wsOutput.Cells(1, 6).Value = "productDescription"

    outputRow = 2 ' Start writing output from the second row

    ' Get the last row and last column of the input data
    lastRow = wsInput.Cells(wsInput.Rows.Count, 1).End(xlUp).Row
    lastCol = wsInput.Cells(1, wsInput.Columns.Count).End(xlToLeft).Column

    ' Loop through each row of the input data
    For inputRow = 2 To lastRow
        ' Loop through each rate column
        For inputCol = 2 To lastCol
            rateName = wsInput.Cells(1, inputCol).Value

            ' Write the transformed data to the output sheet
            wsOutput.Cells(outputRow, 1).Value = Format(wsInput.Cells(inputRow, 1).Value, "DD-MM-YYYY")
            wsOutput.Cells(outputRow, 2).Value = "" ' Leave transactionTime empty
            wsOutput.Cells(outputRow, 3).Value = wsInput.Cells(inputRow, inputCol).Value ' Rate value
            wsOutput.Cells(outputRow, 4).Value = "SHIBOR" & rateName ' Product code
            wsOutput.Cells(outputRow, 5).Value = "" ' Leave securityId empty
            wsOutput.Cells(outputRow, 6).Value = "" ' Leave productDescription empty

            outputRow = outputRow + 1
        Next inputCol
    Next inputRow

    MsgBox "Data transformation complete!", vbInformation
End Sub

Step 3: Refining the Macro for Kyriba-Specific Needs

Enhanced Prompting: Based on the initial code, we need additional functionalities like specific fetching the right data, data formatting, and replacing old data with updated one after running. This could involve additional prompting:
- "There occurred a few issues after compiling the code. First one, it took last row Data source that took the wrong data in the format: Data source: China Foreign Exchange Trade System (CFETS) SHIBORO/N Data source: SHIBOR1W. In our case we shouldn't include this into the output table. Another one, O/N productCode should be SHIBORON, and not SHIBORO/N. Finally, remove the TransformedData sheet in case it already exists, so it could be overwritten by the newly generated spreadsheet with the table."
Refined VBA Code: This section will detail the improvements made to the initial code to ensure it meets all of the requirements.

Final code:

Sub TransformSHIBORData()
    Dim wsInput As Worksheet
    Dim wsOutput As Worksheet
    Dim ws As Worksheet
    Dim inputRow As Long
    Dim inputCol As Long
    Dim outputRow As Long
    Dim lastRow As Long
    Dim lastCol As Long
    Dim rateName As String
    Dim dateValue As String
    Dim formattedDate As String

    ' Delete the "TransformedData" sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Set ws = ThisWorkbook.Sheets("TransformedData")
    If Not ws Is Nothing Then ws.Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' Set worksheets
    Set wsInput = ThisWorkbook.Sheets(1)
    Set wsOutput = ThisWorkbook.Sheets.Add(After:=wsInput)
    wsOutput.Name = "TransformedData"

    ' Set headers for output sheet
    wsOutput.Cells(1, 1).Value = "businessDate"
    wsOutput.Cells(1, 2).Value = "transactionTime"
    wsOutput.Cells(1, 3).Value = "rate"
    wsOutput.Cells(1, 4).Value = "productCode"
    wsOutput.Cells(1, 5).Value = "securityId"
    wsOutput.Cells(1, 6).Value = "productDescription"

    outputRow = 2 ' Start writing output from the second row

    ' Get the last row and last column of the input data
    lastRow = wsInput.Cells(wsInput.Rows.Count, 1).End(xlUp).Row
    lastCol = wsInput.Cells(1, wsInput.Columns.Count).End(xlToLeft).Column

    ' Loop through each row of the input data
    For inputRow = 2 To lastRow
        ' Skip rows that contain "Data source:"
        If InStr(wsInput.Cells(inputRow, 1).Value, "Data source:") = 0 Then
            ' Loop through each rate column
            For inputCol = 2 To lastCol
                rateName = wsInput.Cells(1, inputCol).Value

                ' Format the date
                dateValue = wsInput.Cells(inputRow, 1).Value
                formattedDate = Format(dateValue, "DD-MM-YYYY")

                ' Write the transformed data to the output sheet
                wsOutput.Cells(outputRow, 1).Value = formattedDate
                wsOutput.Cells(outputRow, 2).Value = "" ' Leave transactionTime empty
                wsOutput.Cells(outputRow, 3).Value = wsInput.Cells(inputRow, inputCol).Value ' Rate value

                ' Transform the product code
                If rateName = "O/N" Then
                    wsOutput.Cells(outputRow, 4).Value = "SHIBORON"
                Else
                    wsOutput.Cells(outputRow, 4).Value = "SHIBOR" & rateName
                End If

                wsOutput.Cells(outputRow, 5).Value = "" ' Leave securityId empty
                wsOutput.Cells(outputRow, 6).Value = "" ' Leave productDescription empty

                outputRow = outputRow + 1
            Next inputCol
        End If
    Next inputRow

    MsgBox "Data transformation complete!", vbInformation
End Sub

Step 4: Testing and Validation

Running the Macro: Instructions on how to run the macro within Excel, ensuring that it performs as expected.
Validation Points: Steps to validate the data format, content accuracy, and error handling mechanisms. It’s important that the macro correctly handles scenarios like API downtime or data format changes.

Result file:

This one can be turned to csv and uploaded to Kyriba, or by using Open Reports Studio.

Conclusion

Highlight the benefits of using AI-generated macros, such as time savings, reduced error rates, and the ability to handle complex data transformations efficiently. Emphasize how ChatGPT can assist finance professionals in not just automating repetitive tasks but also in refining data processes to align with technological advancements.

Documentation

API

Community