Use the API from MS Excel

Once you've downloaded the setup file to your computer, click on it, and follow the installation instructions:

Step 1. Run the setup file

installation-1

Accept the License Agreement.

installation-3

Complete the installation.

installation-4

 

Step 2. Add API credentials

Before using the addin, you need provide your Mashape credentials. These should be specified in settings.xml, a file copied into your user folder (usually, C:\Users\<USERNAME>\AppData\Roaming\Microsoft\AddIns\settings.xml) after the installation.

Open it in a plain-text editor like Notepad.

installation-5

If you've subscribed to GetSentiment API on Mashape, paste your Mashape Access Key (found on your Mashape application's page, see the "Get the keys" button) into the access_key field, and https://getsentiment.p.mashape.com/ into the access_key_url field. Delete text within the username, password, and login_url

If you are going to use a domain sentiment analyser, type the name of the domain, e.g. "retail", into the domain field.

Step 3. Check the add-in is installed and enabled

In Excel 2013, under File -> Add-Ins, find the "Manage" section, click on "Go..."

use-1

Make sure GetSentiment Add-in is there and the box next to it is checked:

use-2

 

 

Once you've run the setup file and supplied your credentials in settings.xml, start Excel.

Suppose you have lots of verbatims (text snippets), and you wish to determine which categories of issues are discussed in each verbatim, and which sentiment score is associated with each.

Suppose verbatims to be analysed are in one column of an Excel spreadsheet. The column next to it ("Response") will hold the JSON response from the API - this is the analysis of the text in a machine-readable format. The following columns - "price" and "service" - will hold sentiment scores for each category (the list of categories in terms of which verbatims are analysed, depend on the domain you specified in settings.xml, see the list of categories for each domain here). The last column, "Overall", will hold the overall sentiment score of the verbatim.

use-3

The addin implements two functions: GetSentiment and ExtractScore. In the Response column, against the first verbatim, type in: =GetSentiment(RC[-1]) if the RC format is enabled, else =GetSentiment(A2). Press "Enter". The addin will send the contents of the 2nd column to the GetSentiment API and store the result in the same cell.

use-4

In the column for each category, type

=ExtractScore(<cell with API response>, <category name>)

where the first argument refers to the cell with the API response, and the second to the title of the column. For example, for the first category, type =ExtractScore(RC[-1], "price"); for the second category, type =ExtractScore(RC[-2], "service"). For the overall sentiment score of the verbatim, use "overall" as the second argument in the function.

use-5

Select the cells against the first verbatim, where the functions were typed in. Place the cursor at the right bottom corner of the selection. When the cursor changes, drag it down across all the verbatims.

use-6

Each verbatim will be analysed and sentiment scores stored in the corresponding cells.

Now you can sort the verbatims by sentiment in specific categories or overall, calculate average scores within each category, create graphs showing sentiment breakdowns, or export the analysis into a new format.