Office365 AddIns for R (Part III)

Introduction

A while back, I introduced the ExcelRAddIn (Office365 AddIns for R (Part I)). This is an Office365 AddIn that allows you to evaluate an R-script from within Excel and use the results. This blog-post describes some of the recent updates to the ExcelRAddIn. I focus on two specific areas. Firstly, I describe some ease of use features. Then I describe the function wrappers.

Ease of use features.

• As a convenience, users can now specify packages to load when the add-in is initialised. This is available from the Settings button on the R Tools AddIn ribbon.

In the previous version, packages were loaded by executing the R-script library(<package-name>). In this version, default package loading takes place on the first call to RScript.Evaluate(...), so the first time any R-script is evaluated, there may be a slight delay depending on which and how many packages are loaded. Any issues with the package loading are reported to the R Environment AddIn panel (see below).

• In the previous version, the three functions (CreateVector, CreateMatrix, and CreateDataFrame) which are used to pass data from Excel to R, used a final parameter ‘Type’. This indicated the corresponding R-type (which can be ‘character’, ‘complex’, ‘integer’, ‘logical’, or ‘numeric’). This is now optional; the R-type is determined from the data, if possible. This makes it somewhat easier to create objects to pass to R from Excel. For example, given an Excel table called ‘GalapagosData’ (from the faraway dataset), we can create a data frame simply by passing in a name (“gala”), the data and the headers:

• Two generic calls have been added: RScript.Params and RScript.Function. RScript.Params returns a list of parameters for the requested function and RScript.Function evaluates the specified function, possibly using some or all of the parameters retrieved from the call to RScript.Params.

• Some additional functions for querying models (i.e. objects returned from calls to ‘lm’, ‘glm’ etc) have been added: Model.Results outputs a list of results from the model. Model.Result outputs the result obtained from one item of the list of model results. Optionally, the result can be formatted as a data frame. This is somewhat more convenient than having to evaluate scripts of the form 'model name'$coeffcients, etc. Model.Accuracy returns a number of statistics relating to measures of model accuracy.

Wrapper functions.

One of the motivations for updating the ExcelRAddIn was to provide an improved experience when using more complex R functions in an Excel worksheet. The idea was to avoid building up a script by providing wrapper functions that can handle the variety of parameters passed to the underlying R functions. The option of using a script is always available. However, for a complex function like auto.arima (which can take up to 35 parameters) or glm, it is easier to setup a parameter dictionary with the appropriately named parameters and their values (as shown below)

rather than creating a script, for example: logModel = glm(Purchase~Income+Age+ZipCode, data = purchase, family = binomial(link='logit'))

This also makes it easier to see the effects of any updates to model parameters. As described above, the parameter names and their default values can be retrieved by using the RScript.Params function.

At the moment, wrapper functions have been provided for a number of the functions in the forecast library and for the following two ‘workhorse’ functions:

• Regression.LM – Fit a linear model to the data
• Regression.GLM – Fit a generalised linear model to the data

A spreadsheet with examples based on the underlying packages can be downloaded from here: Forecast.xlsx.

Wrap-up

In this blog-post I have described two sets of enhancements to the ExcelRAddIn. Firstly some ease of use features were described. Secondly, I outlined some function wrappers that provide an improved user experience when using complex R functions in Excel. I am still working on improving the default (‘summary’) output display of results. Overall, the ExcelRAddIn seeks to provide access to R functionality from inside Excel in a way that is somewhat more flexible than the existing Data Analysis Toolpak.