Automate text mining and sentiment analysis processes with Excel VBA

Steps:

1. Sign up for Azure Cognitive Services:
   • First, you need to sign up for the Text Analytics API from Microsoft Azure.
   • Once you sign up, you will get an API key and a endpoint URL.
2. Prepare Your Excel File:
   • Create an Excel file with text data (for example, in column A).
   • In column B, we will display the sentiment analysis result (positive, negative, or neutral).
3. VBA Code for Text Cleaning and Calling the Azure API.

Detailed VBA Code:

Sub AnalyzeSentiment()
    ' Variables
    Dim http As Object
    Dim url As String
    Dim apiKey As String
    Dim text As String
    Dim jsonRequest As String
    Dim jsonResponse As String
    Dim sentiment As String
    Dim i As Long   
    ' Insert your API Key and Endpoint URL here
    apiKey = "YOUR_API_KEY"
    url = "https://<your_endpoint>.cognitiveservices.azure.com/text/analytics/v3.0/sentiment"
    ' Create HTTP object to send the request
    Set http = CreateObject("MSXML2.XMLHTTP")   
    ' Loop through each row and analyze the sentiment
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        text = Cells(i, 1).Value       
        ' Clean the text (remove unnecessary spaces and convert to lowercase)
        text = Trim(LCase(text))       
        ' Create JSON request
        jsonRequest = "{""documents"":[{""id"":""1"",""text"":""" & text & """}]}"
        ' Send the HTTP POST request
        http.Open "POST", url, False
        http.setRequestHeader "Content-Type", "application/json"
        http.setRequestHeader "Ocp-Apim-Subscription-Key", apiKey
        http.Send jsonRequest
        ' Get the response JSON
        jsonResponse = http.responseText       
        ' Extract the sentiment from the response
        sentiment = GetSentimentFromResponse(jsonResponse)
        ' Output the sentiment in column B
        Cells(i, 2).Value = sentiment
    Next i
    ' Release the memory
    Set http = Nothing
End Sub
' Function to extract sentiment from the JSON response
Function GetSentimentFromResponse(response As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim sentiment As String
    ' Find the position of sentiment in the JSON response
    startPos = InStr(response, """sentiment"":""") + Len("""sentiment"":""")
    endPos = InStr(startPos, response, """")
    ' Extract sentiment value
    sentiment = Mid(response, startPos, endPos - startPos)
    ' Return the sentiment
    GetSentimentFromResponse = sentiment
End Function

Detailed Explanation of the Code:

1. Variable Declarations:
   • http: An object used to send HTTP requests.
   • url: The endpoint URL of the Azure Cognitive Services Text Analytics API.
   • apiKey: Your API key to authenticate requests.
   • text: The text you want to analyze.
   • jsonRequest: The JSON structure containing the text to send in the request.
   • jsonResponse: The JSON response returned by the API after analyzing the text.
   • sentiment: The sentiment (positive, negative, or neutral) extracted from the API’s response.
2. Sending Request to Azure API:
   • The text is first cleaned by trimming spaces and converting it to lowercase.
   • A POST request is sent to the Azure API with the cleaned text in JSON format.
   • The API processes the text and returns a JSON response containing the sentiment analysis.
3. Extracting the Sentiment from the Response:
   • The function GetSentimentFromResponse extracts the sentiment value (positive, negative, or neutral) from the JSON response by searching for the « sentiment » key.
4. Displaying the Result:
   • The sentiment value is placed in column B of the Excel sheet, corresponding to the text in column A.

Prerequisites:

1. Reference to Microsoft XML Library:
   • Open the VBA editor (Alt + F11).
   • Go to Tools > References.
   • Check the box for Microsoft XML, v6.0 (or similar) to enable the use of MSXML2.XMLHTTP object.
2. Error Handling:
   • The code does not handle all potential errors. It’s a good idea to check if the API responds correctly and implement error handling for issues such as invalid API keys, API downtime, or network errors.

How to Test:

1. Put some text data in column A (e.g., customer reviews or feedback).
2. Run the AnalyzeSentiment macro to analyze the sentiment of each text. The sentiment (positive, negative, or neutral) will be displayed in column B.

Limitations:

• API Quotas: Ensure you stay within the limits of your Azure API plan (e.g., the number of requests per month).
• Language: The Azure Cognitive Services API supports multiple languages. Make sure the text you’re analyzing is supported by the API.

This process allows you to automate sentiment analysis directly within Excel using VBA and external Azure services, which can be very powerful for analyzing large volumes of textual data.