Overview

Skill Level: Beginner

Knowledge of Office VBA

I will provide you a set of VBA routines which allow you to query Watson Natural Language Understanding directly from Excel spreadsheet with source code.

Ingredients

I developed a tool I call "Cognitive Analyzer". For this Macro enabled Excel workbook you will need:

I will describe the entire process necessary to provision the account and get started.

Step-by-step

  1. Set up a BlueMix account

    To use Cognitive Analyzer, you will need to have an active account with IBM Bluemix. We are going to use Watson Natural Language Understanding. With it you can understand the written language, extract meaning and keywords, assess sentiment, parse phrases into grammatical components and more. Try the demo to understand what this service can do for you. First, we will show you how to set up an account with Bluemix and then get you on your way to use the Cognitive Analyzer.

    You can set up a free account with IBM Bluemix. This gives you several thousand free API calls to Watson Natural Language Understanding which is more than enough for most starter projects. You may want to pay for a subscription or add a payment method so that you can do this analysis on a recurring basis.

    Start by going to https://console.bluemix.net  and set up an account

    Step-1-Bluemix-Account

  2. Provision Watson Services

    You will be adding the service “Natural Language Understanding” to your account. To do this, click on the Catalogue button in the main dashboard:

    Step-2a-Menu-Bar

    Or if you already have an account with Watson, just click the Create button to add the service:

    Step-2b-Create-Service

     

    Find the Watson Natural Language Understanding service in the catalogue and select the lite version:

    Step-2c-Add-Service

    At the time of this writing, the Lite version will allow you to call Watson API up to 30,000 times per month at no cost. Below is a screenshot that will resemble what you see. Leave all values as they are and click on the Create button at the bottom of the screen.

    Step2d-Provisioned-Service

     

  3. Download the source code example workbook

    Click here to download the sample Excel file

    Watson Excel NLP Tool

    Please comment or share this article on LinkedIn. Here is a link to the original post:

    https://www.linkedin.com/feed/update/urn:li:activity:6462956041339486208

     

  4. Stop and reflect!

    The provided worksheet is based on the LinkedIn article describing how to scrape Glassdoor’s website using Excel. The code is easily accessible (not password protected) and is based on an open source API library for VBA by Tim Hall which you can download here: https://github.com/VBA-tools/VBA-Web.

    I added a custom function to authenticate against BlueMix and access the keyword extraction and sentiment analysis features of Watson NLU. Let’s start by getting my example up and running in the next few steps. 

    Open the workbook and make sure you enable Macros:

    Step-4-Macro-Enabled

    Once you have enabled Macros, the “Call Watson” toolbar will appear (see image above)

  5. Configure Watson Credentials in the Custom Application

    Click on the “Call Watson” button and a dialog box will open up. This is where you enter the credentials from BlueMix (Step 3) so that the tool knows how to use your account to access Watson API. No need to include a trailing “/” at the end of the WatsonAPI URL. Enter your Username and Password, and confirm the URL is correct. Do not include quotes. Hit the Test button to make sure it is working. If the 3 fields at the top are correct the test will pass and you can hit Save to store your credentials. They are saved along with this spreadsheet so you will not need to enter them again unless they change

    Settings

  6. Process the Worksheet Data

    I have created a tab (worksheet) named “Sample Data” which contains 50 Glassdoor reviews for Verizon Wireless. Feel free to use this data before merging with your own data set.

    Step-6a-Worksheet-Data-smaller 

    This data is organized in an Excel Table (known also as a list object). You can set the name of a table by clicking anywhere within the table and then activating the “Design” ribbon. Here is where you set the table name:

    Step-6b-Data-Table 

    Excel allows you to have more than one table per spreadsheet so in the NLP dialog you can choose the worksheet and the table name for use by the Watson services. Once you understand how the table works you will be able to create your own worksheets, create your own tables and run the code against your own text. As long as you do this within the same workbook file you are good to go. There are some caveats which will require you to understand VBA programming, such as the fact that the 3 Glassdoor text elements are hard coded. That means that you will be sending 3 columns whose name is Pros, Cons and Advice for analysis by Watson unless you alter the code.

    To try the provided sample data, make sure the “Sample Data” worksheet is selected and “DataTable” is the active table. The “Test” button near these two fields confirms all the fields needed to analyze and store the results exist in the table. You will be told if a field is missing and will have to try again once you added the field to the table. This app is hard wired to analyze 3 fields: Pros, Cons, Advice. These fields map to Glassdoor’s free form text which users can enter to describe the pros and cons of working for the company and the advice to management.

    Step-6c-Table-settings

    Each row in the table has a field called “NLP Enabled” This is a flag you can set to true or false. I have set them all to False so you will need to set one or more of them to True in order to see any results! When you hit the “Run” button – any row with TRUE in the “NLP Enabled” field will be pushed to Watson. It will retrieve the sentiment score for each of the 3 review fields (I mentioned in the prior paragraph). It will also retrieve relevant keywords identified in the text for those review fields.

    After hitting the Run button, the results are posted into the 6 columns on the right, named “Pro Sentiment”, “Con Sentiment”, “Advice Sentiment”, and 3 keyword columns. The sentiment score is a positive score for positive sentiment and <0 for negative sentiment.  Note that the “NLP Enabled” field in that row is automatically reset to “FALSE” to prevent you from repeating the call to Watson multiple times. Here is a screenshot of the table with one record analyzed:

    Step-7-Review-Results

  7. How does it work?

    Open the Developer IDE and look at modNLPRoutines. At the heart of this application is the construction of the Watson API JSON. This is done within the function “BuildWatsonJSON”. If you pass on a sample string, the result will be:

     

    {
    "text": "this is a sample text string to be sent for Watson Analysis",
      "features": {
    "keywords": {
    "limit": 50,
      "sentiment": true,
        "emotion": false,
        "document": true
      },
      "sentiment": {}
    }
    }

    I do this by calling Tim’s JsonObject to incrementally. Here’s the source code:

    Public Function BuildWatsonJSON(AnalyzedText As String) As String
    Const WatsonGetEntities = False
    Const WatsonGetConcepts = False
    Const WatsonGetEmotion = False
    Const WatsonGetKeywords = True

    Dim JsonObject As Object
    Set JsonObject = New Dictionary
    JsonObject.Add "text", AnalyzedText
    JsonObject.Add "features", New Dictionary

    If WatsonGetConcepts Then
    JsonObject("features").Add "concepts", New Dictionary
    JsonObject("features")("concepts").Add "limit", 8
    End If

    If WatsonGetEmotion Then
    JsonObject("features").Add "emotion", New Dictionary
    JsonObject("features")("emotion").Add "document", True
    JsonObject("features")("emotion").Add "targets", New Collection
    JsonObject("features")("emotion")("targets").Add "string"
    End If

    If WatsonGetEntities Then
    JsonObject("features").Add "entities", New Dictionary
    JsonObject("features")("entities").Add "limit", 50
    JsonObject("features")("entities").Add "model", "alchemy"
      JsonObject("features")("entities").Add "sentiment", True
      JsonObject("features")("entities").Add "emotion", True
    End If

    If WatsonGetKeywords Then
      JsonObject("features").Add "keywords", New Dictionary
      JsonObject("features")("keywords").Add "limit", 50
      JsonObject("features")("keywords").Add "sentiment", True
      JsonObject("features")("keywords").Add "emotion", IncludeEmotion
    End If

    JsonObject("features").Add "sentiment", New Dictionary
    JsonObject("features")("keywords").Add "document", True


    BuildWatsonJSON = ConvertToJson(JsonObject)
    End Function
  8. Parsing the response

    In the same file you will find “ProcessNLP” which is a VBA routine to…process the response. Here’s the source code:

    Private Function ProcessNLP(Response As WebResponse, SoughtSentiment As String) As Object
        If Response.StatusCode = WebStatusCode.ok Then
            
            Dim oResults As New Dictionary
            Dim KeywordString As String
            Dim Keywords As Collection, Keyword As Dictionary
            KeywordString = ""
            
    '
    ' [Keywords]'
            Set Keywords = ParseJson(Response.Content)("keywords")
            
            For Each Keyword In Keywords
                Dim Sentiment As String
                Dim Emotions As String
                Emotions = ""
                If Keyword("relevance") > 0.8 Then
                    On Error GoTo NoSentiment
                    If Keyword("sentiment")("score") > 0.25 Then
                        Sentiment = "+"
                    ElseIf Keyword("sentiment")("score") < -0.25 Then
                        Sentiment = "-"
                    Else
                        Sentiment = "~"
                    End If
                    On Error GoTo 0
                    If SoughtSentiment = "*" Or SoughtSentiment = Sentiment Then
                        If IncludeEmotion Then
                            If Keyword("emotion")("sadeness") > 0.8 Then Emotions = "Sadeness"
                            If Keyword("emotion")("joy") > 0.7 Then Emotions = Emotions & "/joy"
                            If Keyword("emotion")("fear") > 0.8 Then Emotions = Emotions & "/fear"
                            If Keyword("emotion")("disgust") > 0.7 Then Emotions = Emotions & "/disgust"
                            If Keyword("emotion")("anger") > 0.6 Then Emotions = Emotions & "/anger"
                            If Left(Emotions, 1) = "/" Then Emotions = Right(Emotions, Len(Emotions) - 1)
                        End If
                        KeywordString = KeywordString & Keyword("text") & IIf(SoughtSentiment = "*", "/" & Sentiment, "")
                        If Emotions <> "" Then KeywordString = KeywordString & "|" & Emotions
                        KeywordString = KeywordString & ","
                    End If
    NoSentiment:
                    On Error GoTo 0
                End If
            Next
            If KeywordString <> "" Then KeywordString = Left(KeywordString, Len(KeywordString) - 1)
        Else
            KeywordString = "#Error"
        End If
        oResults("keywords") = KeywordString
        Set oResults("sentiment") = ParseJson(Response.Content)("sentiment")
        oResults("language") = ParseJson(Response.Content)("language")
        
        Set ProcessNLP = oResults
    End Function

    This code can process the sentiment score, emotions detected in the text and keywords. Note that I am passing along a “sought sentiment”: this is because I am using this to analyze the “pros” and “cons” of working for a company. When analyzing the cons of working for a company I look for a negative sentiment associated with a keyword to know it is relevant and for positive sentiment associated with the pros of working for a company. This eliminates alot of questionable results because you expect that when a user can structure their company feedback in either pro or con terms that the sentiment will align to the type of feedback being provided.

  9. Conclusion

    Calling Watson API from with any of the Microsoft Office products is extremely powerful. You can use NLP to analyze a word document as it is being written and highlight the sentiment or suggest rephrasing of a few words to make the letter more relevant to a written style. You can analyze massive amounts of unstructured data in Excel and much more. Now that you have the tools – go build, and send me a note at tiran@tirandagan.com to let me know what you are building and provide feedback.

    Please do not send me technical questions via mail. Use the online forum at: https://www.tirandagan.com/forum

     

1 comment on"Calling Watson APIs from Excel"

  1. Free1ancer April 02, 2019

    There doesn’t seem to be an excel attached to the link. Is there somewhere else I can find it?

Join The Discussion