Playground

Artificial Intelligence with Excel? Sure you can!

By

Nowadays, everybody talks about Artificial Intelligence (AI), especially when chatbots are involved (briefly I will publish an article about this theme). AI could be another buzzword, but the practical applications - and concerns - of machine learning algorithms are becoming more common from day to day. At this moment, AI is dealing with tasks that require the processing of great amounts of data that no human could process and executing ordinary and repetitive tasks that most of us do, so it's quite obvious that soon machines will take our place.

So, to satisfy our future overlord, the all mighty machines, I decided to write an article that explains the practical use of this technology with one of the most common and powerful tools all companies have: Microsoft Excel. For this proof of concept, the AI will be in the very capable hands of IBM Watson Developer Cloud, which has a set of APIs for language, vision, speech and data, from which I will use the Conversation service. You don't need to know how to code to use this example. Just follow these instructions and everything will be fine.

Now, imagine you have a form on your website contact page that has an open text input box, and you don't have a drop down for the user to select the reason for contact. Being a contact form, you must reply as quickly as possible, but depending on the reason or reasons (it's certain that some users with fill the text box with multiple topics) of contact, some answers may be more urgent than others. So, how do you make the screening of these answers without reading them all? You guess it! With artificial intelligence!

The starting point for this exercise will be an Excel sheet with some fictional data you could receive on this form.

Next, let's set up the IBM Watson services:

  1. Go to the Conversation service and sign up for a free Bluemix account or log in
  2. After you log in, type "conversation-tutorial" (or whatever you want) in the Service name field of the Conversation page, select the free plan, click Create, and then click Launch tool.
  3. In the Conversation tool, click Create a new workspace.
  4. Give your workspace the name "Conversation example" (or other), select the conversation language (it also supports Portuguese!) and click Create. Youʼll land on the Intents tab of your new workspace.
  5. Now it's time to create the intents that will be attributed to each customer question. Give a name to the intent and insert some examples of conversation for that intent (at least 5 examples). The more examples you provide, the more accurate the service can be at recognizing user intents.

For my Excel example, I will only need two intents: #login and #search. As you can see by the screenshot above, it's quite simple to define intents. When doing this, if possible use pieces of real text users/customers sent you (leaving out sensitive data), keeping in mind that customers might phrase the same kind of question in many different ways. Also, do not make your intents too similar.

It's not mandatory to create entities, but you could. An entity represents a term or object in the user's input that provides clarification or specific context for a particular intent. For example, on line 6 of the Excel, you have "The login doesn't work on Safari". The word "Safari" could be a value of the entity @browser. This way, when looking and expressions within the #login intent, Watson can also return (if available) the browser on that sentence. For each value, you can also define synonyms, as the synonym "ie" for the value "internet explorer".

At this moment you can already test your conversation. Click on the balloon shape on the top right corner and enter some phrases. The first time I used line 6 expression, Watson correctly understand the #login intent and the @browser entity. Perfect! If the intent was not right, I could correct it with the drop down and Watson would start learning! Love these machines (wink)!

Before leaving Watson, we have to do one more thing. Click on the Deploy button on the left. On this screen, you'll see all the credentials you need to access the conversation service. Copy the workspace ID, Username and Password to notepad or other, because you'll need it later.

Now that we have set up the Watson Conversation, let's make magic with an Excel macro. Activate the Developer tab on Excel and click on the Visual Basic button, or press Alt + F11.

What you'll see next is the Visual Basic for Applications (VBA, if you want to search for related information on Google) editor.

  1. Right click on the project on the left bar and select Insert Module.
  2. On the top menu, select Tools/References and check the Microsoft XML v 6.0 if you have the latest version of MS Office (you need this to make the HTTP request) and click OK.
  3. Paste the following code on the module editor and fill the workspace ID, Username and Password with the Watson credentials you saved earlier.
Function INTENT(query As String) As String
' IBM WATSON conversation service

    If Len(query) > 0 Then 'If query is not empty
        Dim xmlhttp As New MSXML2.XMLHTTP60, workspaceID As String, workspaceURL As String, authUsername As String, authPassword As String, response As String, body As String
        
        ' WATSON Conversation Credentials
        workspaceID = "{insert workspaceID here}"
        workspaceURL = "https://gateway.watsonplatform.net/conversation/api/v1/workspaces/" + workspaceID + "/message?version=2017-05-26" 'API requests require a version parameter
        authUsername = "{insert username here}"
        authPassword = "{insert password here}"

        
        body = "{""input"":{""text"":"" " + Replace(query, Chr(34), "") + " ""}}" 'The user input in JSON format. Also removed double quotes from input
        
        xmlhttp.Open "POST", workspaceURL, False, authUsername, authPassword
        xmlhttp.setRequestHeader "Content-Type", "application/json"
    
        xmlhttp.Send body
        
        If xmlhttp.Status = 200 Then 'Request success
            response = xmlhttp.responseText
            INTENT = parseINTENT(response)
        Else 'Request fail
            INTENT = "#ERROR: " & xmlhttp.Status & " - " & xmlhttp.responseText
        End If
    End If
End Function

Function parseINTENT(text As String) As String
'Parse intent and confidence from JSON response

    Dim p1 As Integer, p2 As Integer
   
    p1 = InStr(text, "[{" + Chr(34) + "intent")
    p2 = InStr(p1, text, "}]")
    parseINTENT = Mid(text, p1, (p2 - p1) + 2)
    
End Function

Now you're ready to query the Watson Conversation service with an Excel formula. Easy, right? On the Excel sheet use the following formula in the next column.

=INTENT(A2)

The result is this:

As you can see, Watson nailed it! The function also returns the confidence of each answer, which was quite high. The Watson Conversation service returns more information but, to simplify, my parseINTENT function parses only these two bits of information. If you want to get the @entity, change this function to your needs.

I know this is a basic example, with short sentences, so test it with more complex phrases and see the result. I tested it and was amazed by the results. If the service fails to identify the right intent, go to Improve section on Watson, select the User conversations tab and rectify the wrong ones. The message "Watson is learning..." will appear. So cool!

Since this service is more suitable to understand natural language on chatbots there's a Dialog section I didn't cover, where you can define the response to the dialog accordingly to the intents and entities.

Watson Developer Cloud has several free and paid services you can explore, or at least try the demo. The services documentation is very extensive and will almost certainly help you if you have any problem and, with this article, you have the core functions to apply them to data you have on Excel. Try it and give me feedback about new test cases. I will love to know.

 

Update, 21 November 2018

IBM renamed the Watson Conversation service to Watson Assistant, so you must update the workspaceURL variable on VBA for the function to work. Here’s the updated value:

workspaceURL = “https://gateway.watsonplatform.net/assistant/api/v1/workspaces/" + workspaceID + “/message?version=2018–07–10” ‘API requests require a version parameter

 

Update, 17 March 2020

IBM changed the authentication method for Watson Assistant. Instead of the username and password, now you must use the API key you can find in the skill details. Also, you can take this opportunity to update the service to the latest version.

authUsername = "apiKey"
authPassword = "{insert skill api key here}"

workspaceURL = “https://gateway.watsonplatform.net/assistant/api/v1/workspaces/" + workspaceID + “/message?version=2020–02–05”

Related posts