How to Automate Data Retrieval from ChatGPT API in Excel Using VBA

How to Automate Data Retrieval from ChatGPT API in Excel Using VBA

This VBA script automates the process of fetching data from OpenAI’s ChatGPT API and populates an Excel spreadsheet with structured information. This tutorial breaks down the script, explaining its purpose, key features, and how to use it effectively.

Purpose

The script connects to the OpenAI API, sends user prompts, retrieves responses, and organizes the data into specific columns in an Excel sheet. It’s particularly useful for tasks like gathering summarized information for a list of items (e.g., countries).

Key Components of the Code

1. Initialization

vba

Dim http As Object, JSON As Object

Dim apiKey As String, url As String

Dim ws As Worksheet

 

• http: Handles HTTP requests.
• JSON: Parses the JSON response.
• apiKey: Your OpenAI API key for authentication.
• ws: Refers to the active worksheet where data is processed.

 

2. API Key and Endpoint

vba

apiKey = “your_api_key_here”

url = https: //api.openai.com/v1/chat/completions

Replace your_api_key_here with your OpenAI API key to authenticate your requests. The API endpoint is configured for ChatGPT models.

3. Worksheet and Data Preparation

vba

Set ws = ThisWorkbook.Sheets(1)

lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row

 

• Identifies the last row in column A (assumes it contains the list of countries).
• Clears existing data in relevant columns (B to G) to avoid conflicts.

4. Prompt Construction

prompt = “Provide details about ” & country & “: Short summary, language spoken, population, capital city, and continent. Separate each detail with a vertical bar.”

 

• Constructs a natural-language prompt dynamically based on the current country in column A.

5. Making the API Request

vba

With http

    .Open “POST”, url, False

    .SetRequestHeader “Content-Type”, “application/json”

    .SetRequestHeader “Authorization”, “Bearer ” & apiKey

    .Send requestBody

    responseText = .responseText

End With

 

• Configures and sends an HTTP POST request with:
  • Content-Type: Specifies JSON data.
  • Authorization: Includes your API key in the header.

6. Parsing the Response

vba

Set JSON = JsonConverter.ParseJSON(responseText)

If JSON.Exists(“choices”) Then

    result = JSON(“choices”)(1)(“message”)(“content”)

ElseIf JSON.Exists(“error”) Then

    result = “Error: ” & JSON(“error”)(“message”)

Else

    result = “Unexpected response format”

End If

 

• Parses the JSON response to extract the relevant content.
• Handles potential errors or unexpected formats.

7. Populating Excel Columns

vba

dataArray = Split(result, “|”)

If UBound(dataArray) >= 4 Then

    ws.Cells(i, 3).Value = Trim(dataArray(0)) ‘ Short sentence

    ws.Cells(i, 4).Value = Trim(dataArray(1)) ‘ Language spoken

    ws.Cells(i, 5).Value = Trim(dataArray(2)) ‘ Population

    ws.Cells(i, 6).Value = Trim(dataArray(3)) ‘ Capital city

    ws.Cells(i, 7).Value = Trim(dataArray(4)) ‘ Continent

Else

    ws.Cells(i, 2).Value = “Error: Response not structured as expected”

End If

 

• Splits the response by the vertical bar (|) and maps each segment to a specific column:
  • Column C: Short summary
  • Column D: Language spoken
  • Column E: Population
  • Column F: Capital city
  • Column G: Continent

8. Error Handling and Retry

vba

If ws.Cells(i, 2).Value = “Error: Response not structured as expected” Then

    ‘ Retry logic here

End If

 

If a response is not structured as expected, the script retries fetching data for that row.

How to Use the Script

1. Setup API Access
   • Get your OpenAI API key from the OpenAI platform.
   • Replace your_api_key_here in the script with your actual key.
2. Prepare Your Excel File
   • Column A: List of countries (or other entities).
   • Columns B to G: Reserved for responses and parsed data.
3. Add the VBA Script
   • Open the VBA editor (Alt + F11).
   • Insert a new module and paste the code.
4. Run the Script
   • Close the VBA editor.
   • Run the script (Alt + F8) and select FetchChatGPTDataVer2.

Expected Output

• Column A: Input (e.g., country name).
• Column B: Raw response from ChatGPT.
• Columns C to G: Parsed details:
  • Short summary
  • Language spoken
  • Population
  • Capital city
  • Continent

Potential Improvements

1. Error Logging: Save errors in a separate sheet for review.
2. Dynamic Prompting: Modify prompts based on user input or additional context.
3. Formatting: Automatically format columns (e.g., number formatting for population).

This script demonstrates the power of integrating OpenAI’s capabilities into Excel, streamlining data retrieval and analysis.

 

VBA and Video credit: Tryfon Papadopoulos