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
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:
Or if you already have an account with Watson, just click the Create button to add the service:
Find the Watson Natural Language Understanding service in the catalogue and select the lite version:
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.
Download the source code example workbook
Click here to download the sample Excel file
Please comment or share this article on LinkedIn. Here is a link to the original post:
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:
Once you have enabled Macros, the “Call Watson” toolbar will appear (see image above)
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
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.
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:
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.
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:
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",
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
If WatsonGetEmotion Then
JsonObject("features").Add "emotion", New Dictionary
JsonObject("features")("emotion").Add "document", True
JsonObject("features")("emotion").Add "targets", New Collection
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
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
JsonObject("features").Add "sentiment", New Dictionary
JsonObject("features")("keywords").Add "document", True
BuildWatsonJSON = ConvertToJson(JsonObject)
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 = ""
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 = "-"
Sentiment = "~"
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)
KeywordString = KeywordString & Keyword("text") & IIf(SoughtSentiment = "*", "/" & Sentiment, "")
If Emotions <> "" Then KeywordString = KeywordString & "|" & Emotions
KeywordString = KeywordString & ","
On Error GoTo 0
If KeywordString <> "" Then KeywordString = Left(KeywordString, Len(KeywordString) - 1)
KeywordString = "#Error"
oResults("keywords") = KeywordString
Set oResults("sentiment") = ParseJson(Response.Content)("sentiment")
oResults("language") = ParseJson(Response.Content)("language")
Set ProcessNLP = oResults
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.
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 email@example.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