Scoring R Models with Excel

by Joseph Rickert

In a post late last year, my colleague and fellow blogger, Andrie de Vries described enhancements to the AzureML R package that makes it easy to publish R functions that consume data frames as Azure Web Services. A very nice consequence is that it is now feasible to develop predictive models in R and enable the Excel powered business analysts in your organization to use your model to generate predictions with new data. This is made possible by an Azure feature that integrates a published web service into an Excel workbook. Once you publish your R model as a web service and set up the Excel workbook anybody you give the workbook to will be able to score new data copied into the workbook.

Now, I'll walk through the steps required assuming you have already set up an Azure ML account. The AzureML package vignette gives a detailed example of publishing a new model. For convenience, I reproduce the necessary code from the vignette here:

The first part of the code fits a generalized boosted regression model (gbm) to the Boston data set from the MASS package that contains features characterizing the housing values for suburban Boston. The prediction function, mypredict() is set up to take in a data frame containing new data and use the gbm model to predict the median value of owner-occupied homes. Notice that the function includes the statement require(gbm). This ensures that the Azure environment will have access to the gbm package when making predictions. 

The rest of the code "tests" the prediction using a data frame containing the first five lines of the Boston data set and then publishes the prediction function as a web service using the function publishWebService().

Once you have gotten this far there are only a few more steps to set up the Excel workbook. Login to your Azure Machine Learning account and go to the web services page. You should see something like this:

Notice that the name used in the publisWebService function appears on the list of available services. Clicking on this will bring you to a page like this next one. Go ahead and select Excel 2013 or later workbook in the REQUEST/RESPONSE row.

This should bring you to the Sample Data screen below that pretty much explains what you are about to do.

Now we almost there. A couple more clicks will bring you to an empty workbook like the one below. To get this screen I manually pasted in test data from the Boston file. 

Then I used the input boxes on the right to set the range for the input data and select range of cell to place the output.

Once the file is built you can distribute it to your colleagues to begin making predictions. You can download my Excel file here Download AzureML-vignette-gbm-3_15_2016 12_13_33 AM and begin making predictions.