Home

   |

|  How to Integrate Microsoft Azure Cognitive Services with Microsoft Excel

How to Integrate Microsoft Azure Cognitive Services with Microsoft Excel

January 24, 2025

Learn how to seamlessly integrate Azure Cognitive Services with Excel for enhanced data analysis and AI-powered insights in this step-by-step guide.

How to Connect Microsoft Azure Cognitive Services to Microsoft Excel: a Simple Guide

 

Set Up Your Azure Cognitive Services Account

 

  • Log in to the Microsoft Azure Portal. If you don't have an account, create one.

    •  

  • Navigate to "Create a resource" and search for "Cognitive Services". Select it and click "Create".

    •  

  • Fill out the necessary details, like Subscription, Resource Group, and the Resource Name. Ensure you select the appropriate pricing tier and region.

    •  

  • After creation, navigate to the resource and find your "Key" and "Endpoint" – you'll need these for integration.

 

Prepare Microsoft Excel for Integration

 

  • Open Microsoft Excel and navigate to the "Developer" tab. If it's not visible, enable it from File > Options > Customize Ribbon > Check "Developer".

    •  

  • Visual Basic for Applications (VBA) will be used to run scripts, so ensure your Excel is ready to run macros.

    •  

  • Set up a worksheet where you'll input data you want to analyze with Azure Cognitive Services.

 

Create a VBA Script to Access Azure

 

  • In Excel, under the "Developer" tab, click "Visual Basic" to open the VBA editor.

    •  

  • In the editor, select "Insert" > "Module" to create a new module.

    •  

  • Paste the following VBA code into the module, replacing placeholders with your specific Azure details:

 

Sub CallAzureCognitiveService()
    Dim AzureKey As String
    Dim AzureEndpoint As String
    Dim Request As Object
    Dim JsonResponse As String
    Dim InputText As String
    
    AzureKey = "YOUR_AZURE_KEY"
    AzureEndpoint = "YOUR_AZURE_ENDPOINT"
    
    ' Example input
    InputText = Worksheets("Sheet1").Range("A1").Value
    
    Set Request = CreateObject("MSXML2.XMLHTTP")
    
    With Request
        .Open "POST", AzureEndpoint & "/text/analytics/v3.0/sentiment", False
        .setRequestHeader "Ocp-Apim-Subscription-Key", AzureKey
        .setRequestHeader "Content-Type", "application/json"
        .send "{""documents"":[{""id"":""1"",""text"":""" & InputText & """}]}"
        JsonResponse = .responseText
    End With
    
    MsgBox "API Response: " & JsonResponse
End Sub

 

  • Change the string concatenation to match the specific type of Cognitive Service you are using.

 

Test the VBA Script

 

  • Return to Excel, and in the "Developer" tab, click on "Macros".

    •  

  • Select "CallAzureCognitiveService" and click "Run".

    •  

  • Ensure you have some input in cell A1 (or adjust the VBA code to point to another cell) to analyze.

 

Interpret API Responses

 

  • Review the MsgBox output to understand the response from Azure. This output can include sentiment analysis, key phrase extraction, etc.

    •  

  • Based on this output, consider automating further analysis or result storage in your Excel worksheet.

 

Secure and Optimize Integration

 

  • Ensure your Azure Key is stored securely. Consider encrypting it with VBA or fetching it from a secure location.

    •  

  • Monitor Azure usage to manage your costs and ensure efficiency.

    •  

  • Consider using batch requests if you plan to analyze large datasets to reduce the number of HTTP requests.

 

Omi Necklace

The #1 Open Source AI necklace: Experiment with how you capture and manage conversations.

Build and test with your own Omi.

How to Use Microsoft Azure Cognitive Services with Microsoft Excel: Usecases

 

Automated Sentiment Analysis for Customer Feedback

 

  • Integrate Cognitive Services with Excel: Use Microsoft Azure Cognitive Services, specifically the Text Analytics API, to process and evaluate customer feedback data stored in Microsoft Excel spreadsheets.

    •  

  • Analyze Feedback Data: Automatically extract sentiment from customer comments and reviews by exporting customer feedback from Excel to Azure. This can provide insights into customer satisfaction and identify areas for improvement without manual data entry.

    •  

  • Display and Visualize Results: Import sentiment analysis results back into Excel. Utilize Excel’s powerful charting and graphing tools to visually represent the sentiment scores, allowing stakeholders to easily interpret the data through dashboards and reports.

    •  

  • Create Actionable Insights: Enable auto-flagging of negative sentiment feedback that needs urgent attention. Set up conditional formatting in Excel to highlight these entries, allowing customer service teams to prioritize follow-up actions efficiently.

    •  

  • Improve Decision Making: Integrate quantitative sentiment analysis into Excel-driven reports to enhance decision-making processes in areas such as product development, marketing strategies, and customer support enhancements.

 

// Sample code to integrate Azure Cognitive Services with Excel via JavaScript
const axios = require('axios');

// Function to send request to Text Analytics API
async function getSentimentFeedback(feedbackText) {
  const endpoint = "https://<your-resource-name>.cognitiveservices.azure.com/";
  const key = "<your-key>";

  const response = await axios.post(endpoint + "text/analytics/v3.1/sentiment", {
    documents: [{ id: "1", language: "en", text: feedbackText }]
  }, {
    headers: { 'Ocp-Apim-Subscription-Key': key }
  });

  return response.data;
}

 

 

Intelligent Document Processing for Business Reports

 

  • Integrate Cognitive Services with Excel: Utilize Microsoft Azure Cognitive Services, specifically the Form Recognizer API, to automatically extract and process data from scanned business reports and documents, importing this data seamlessly into Microsoft Excel for analysis.

    •  

  • Automate Data Extraction: Leverage Form Recognizer to identify key information and fields from financial statements, invoices, and other structured forms. This eliminates manual data entry, improves accuracy, and speeds up processing times.

    •  

  • Data Transformation and Cleaning: Import the extracted data into Excel and use Excel's functionalities to clean and normalize the data, making it ready for analysis. This can involve tasks such as correcting inconsistent formatting or handling missing values.

    •  

  • Create Interactive Dashboards: Use Excel’s PivotTables and graphing tools to build interactive dashboards and reports. This enables visualization of the processed data, providing insightful visuals that aid stakeholders in understanding trends and patterns swiftly.

    •  

  • Enhance Business Forecasting: Integrate the processed data into more complex Excel functions and analytics tools, allowing for precise business modeling and forecasting. This supports informed decision-making processes concerning financial budgeting, resource allocation, and strategic planning.

 

// Sample code to integrate Azure Form Recognizer with Excel via JavaScript
const axios = require('axios');

// Function to send request to Form Recognizer API
async function analyzeDocument(documentUrl) {
  const endpoint = "https://<your-resource-name>.cognitiveservices.azure.com/";
  const key = "<your-key>";

  const response = await axios.post(endpoint + "formrecognizer/v2.1/layout/analyze", {
    source: documentUrl
  }, {
    headers: { 
      'Ocp-Apim-Subscription-Key': key,
      'Content-Type': 'application/json'
    }
  });

  return response.data;
}

 

Omi App

Fully Open-Source AI wearable app: build and use reminders, meeting summaries, task suggestions and more. All in one simple app.

Github →

OMI NECKLACE + OMI APP
First & only open-source AI wearable platform

a person looks into the phone with an app for AI Necklace, looking at notes Friend AI Wearable recorded a person looks into the phone with an app for AI Necklace, looking at notes Friend AI Wearable recorded
a person looks into the phone with an app for AI Necklace, looking at notes Friend AI Wearable recorded a person looks into the phone with an app for AI Necklace, looking at notes Friend AI Wearable recorded
online meeting with AI Wearable, showcasing how it works and helps online meeting with AI Wearable, showcasing how it works and helps
online meeting with AI Wearable, showcasing how it works and helps online meeting with AI Wearable, showcasing how it works and helps
App for Friend AI Necklace, showing notes and topics AI Necklace recorded App for Friend AI Necklace, showing notes and topics AI Necklace recorded
App for Friend AI Necklace, showing notes and topics AI Necklace recorded App for Friend AI Necklace, showing notes and topics AI Necklace recorded