Financial forecasting allows businesses to estimate future revenues, expenses, and other key financial metrics, which are crucial for making informed decisions. In this article, we will show how to build an AI-powered financial forecasting model in Excel. We will use a large language model like OpenAI’s GPT API to automate data analysis and predictions.

Let’s consider historical financial data (such as revenue, expenses, etc.) to display AI-generated forecasts.

Step 1: Get Your OpenAI API Key

• If you don’t have an account, sign up at OpenAI and get your key.
• If you already have an account, log in to the OpenAI account.
• From Dashboard >> select API Key >> click on Create new secret key.
• In Create new secret key box;
  • Name the key >> select Permissions >> click on Create secret key.

• In the Save your key dialog box;
  • Copy the generated API key and store it in a safe place to authenticate your API requests. It won’t be visible once you create an API key.
  • Click on Done.

Never share your API key publicly or expose it in client-side code.

Step 2: Integrating OpenAI via VBA in Excel

You can integrate OpenAI GPT with Excel using VBA (Visual Basic for Applications) to build an AI-powered financial forecasting model.

• Go to the Developer tab >> select Visual Basic.
• In the VBA editor, go to Insert >> select Module.
• Insert the following VBA code in the Module to create a user-defined function.

VBA Code:

Function CallGPTForecast(prompt As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP.6.0")
    Dim url As String
    Dim ApiKey As String
    Dim Response As String
    Dim Json As Object
    Dim result As String

    ' Set OpenAI API endpoint and key
    ApiKey = "YOUR_OPENAI_API_KEY"  ' Replace with your actual API key
    url = "https://api.openai.com/v1/chat/completions"  ' API endpoint

    ' Prepare the HTTP request
    http.Open "POST", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & ApiKey

    ' Format JSON body with prompt
    Dim requestBody As String
    requestBody = "{""model"": ""gpt-4"",""messages"":[{""role"": ""system"",""content"": ""You are a financial forecasting assistant.""}," & _
                  "{""role"": ""user"",""content"": """ & prompt & """}],""max_tokens"":300}"

    ' Send the request
    http.Send requestBody

    ' Check for successful response
    If http.Status <> 200 Then
        CallGPTForecast = "HTTP Error " & http.Status & ": " & http.statusText
        Exit Function
    End If
    ' Get response text
    Response = http.responseText  
    ' Use JSONConverter to parse JSON response
    Set Json = JsonConverter.ParseJson(Response)

    ' Ensure "choices" exists in JSON
    If Not Json.Exists("choices") Then
        CallGPTForecast = "Error: Invalid response format. 'choices' not found."
        Exit Function
    End If
    ' Extract the result from parsed JSON
    result = Json("choices")(1)("message")("content")
    CallGPTForecast = result
    Exit Function
End Function

• Replace “YOUR_OPENAI_API_KEY” with your own OpenAI API key.

Explanation:

• First, initialize the HTTP request and set the OpenAI API endpoint and API key.
• Sets headers for JSON content and authorization.
• Prepares the JSON body with the GPT-4 model, a system message (role: assistant), and a user message (the prompt). You can update the max_tokens as per your need.
• Sends the HTTP request and checks if the response status is successful (200).
• Then, use JsonConverter to parse the JSON response and extract the forecasted result from choices.
• Outputs the forecast content if successful, or an error message if the format is invalid.

Step 3: Install JSON Parsing Library

As Excel does not natively support JSON parsing you will need to download VBA-JSON from GitHub to parse JSON responses from OpenAI.

• Download JsonConverter.bas from GitHub.
• In the VBA editor, go to File tab >> select Import File.

• In the Import Box >> select JsonConverter.bas to add it to the project.

Enable References:

You will need to enable Microsoft Scripting Runtime reference it is necessary because the JsonConverter library relies on Dictionary objects to manage JSON data structures.

• Go to Tools tab >> select References.
• In the Available References box >> check Microsoft Scripting Runtime >> click OK.

Step 3: Generate a Forecast Prompt and Call Financial Model

• In your Excel sheet, create a cell to construct a prompt based on your historical data.
• Insert the following formula in a selected cell.

= "Given the following monthly revenue data from Jan 2023 to Dec 2023: " & TEXTJOIN(", ", TRUE, 'Data Input'!B2:B13) & ", show final forecast result of the revenue for the next 6 months."

Run the VBA Function to Call the Model:

Select cell B2 and insert the following formula to get the forecasted revenue.

=CallGPTForecast(A2)

In this formula, A2 contains the prompt we generated from the “Data Input” sheet.

Output:

We created two different prompts to show the variation of the results. It returns a forecast of the revenue for the next 6 months but the format and content are different. 

Output will vary on user prompts. This model will generate a forecast based on specific prompts.

Remember with each refresh in the Excel formula, GPT will update the content.

Analyze the Results

• Compare Forecasted Values: Review the AI-powered output and compare it with any expected values to understand its accuracy.
• Create Visualizations: You can use charts, such as line graphs, to visualize actual vs. forecasted data. This can help highlight trends and assess the accuracy of the model.

Conclusion

You can build an AI-powered financial forecasting model in Excel by integrating an AI model like GPT into Excel. It will enhance your financial forecasting capabilities without extensive programming knowledge. But your result will depend mostly on the prompts. Always try to provide concise accurate input in prompts. By incorporating more detailed features you can improve accuracy, making your forecasts more reliable for business planning.