Using the Azure Machine Learning Excel Add-in in Excel 2016

We assume that you have an Azure Machine Learning experiment and that you published it as a predictive web service so you can call it from an Excel 2013/2016 spreadsheet. You want to consume it (call it) from an Excel 2013/2016 spreadsheet.

If you have not installed yet the Azure Machine Learning Excel Add-in, follow this guide before continuing:

http://azureblogger.com/2016/04/installing-the-azure-machine-learning-excel-add-in-a-detailed-guide/

This ML experiment is simple: based on the aggregate average score of a set of self-tests, the number of distinct self-tests studied (in this case a maximum of five) and the total number of times these self-tests were done by students, predict whether the students will pass or fail a final exam. The experiment was trained on historical data.

Here is what we see in our Azure Machine Learning experiment. Yours will be different of course.

https://studio.azureml.net/Home

Training experiment:

TE

This experiment was published as a predictive web service. Two “Project Columns” actions were added in the predictive experiment pane to specify the columns we wanted to pass to the web service and the columns we wanted in return. Note that the default schema/behavior is to return all the columns in the original dataset + the predictions.

Be aware that the free tier sets by default a maximum of 20 concurrent executions but this can be increased to a maximum of 200. In order to change this value, open your Machine Learning Workspace, click on Web Services tab, double-click on your web service, double-click on your default endpoint, click on Configure tab, change maximum of concurrent executions and click on Save button.

PE

Here is the schema of this ML web service (Web Service Groups – double-click on Web Service – Configuration)

Output columns were renamed to Outcome and Probability.

schema

We need two additional pieces of information: the API Key and the Request URI.

Click on Dashboard, and copy the API key: (save it in notepad)

apikey

To find the request URI, click on Batch Execution

batch

and copy the request URI: (save it in notepad)

requesturi

Open Excel 2016. Verify that when you click on Insert Function:

fx

The functions for Azure Machine Learning Excel Add-in are available. (If not, go click on the URL listed at the top of this page to verify your installation.)

mlfunc

Add some data to your spreadsheet and make sure the columns listed are in the same order as the schema defined for the web service input.

demo1

To keep the Machine Learning formulas separate, a new sheet was added and renamed “Machine Learning”:

sheet

Enter the RequestURI, the APIKey  and a calculated column we called Schema:

column

Enter formulas in the ‘Results’ sheet to retrieve predictions.

Let’s verify one more time that the column order matches the parameter order found in the schema file.

As there is no need to recalculate the entire sheet each time a row is updated, the azureFastPredict function will be used in each row.

Here is the result:

callml

Column E:

=azureFastPredict(A2:C2)

In column F and G the following two formulas were created to split the returned values in Column E using the comma delimiter. We also wanted missing values to return as blanks.

Column F:

=IF(ISNUMBER(SEARCH(“,”,E2)),IF(LEFT(E2,(FIND(“,”,E2,1)-1))=”0″,”Fail”,”Pass”),””)

Column G:

=IF(ISNUMBER(SEARCH(“,”,E2)),VALUE(MID(E2,FIND(“,”,E2)+2,256)),””)

Copy those formulas down to match the number of rows in the spreadsheet.

If we change in cell B3 the number of attempts for 5 to 9, the web service is immediately queried for this row only and prediction changes from a Fail to a Pass.

failpass

The ML column E can be hidden if desired.

hidden

As we demonstrated in this post, it is very easy to call an Azure Machine Learning web service from Excel 2016.

Leave a Reply