Enhancements to ExcelRAddIn: Ease of Use Features and Function Wrappers

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.

Continue reading: Office365 AddIns for R (Part III)

Implications and Future Applications of ExcelRAddIn Updates

The ExcelRAddIn, an Office365 add-in that allows for the evaluation of R-scripts in Excel, recently received a helpful update. This patch introduced new features that improve ease of use and additional function wrappers that enhance the user’s experience when operating complex R functions in the Excel environment.

Long-term Implications

The long-term implications of these updates are vast. By further integrating R-function capabilities into Excel, the application becomes much more versatile and powerful. While previously, the use of R functions required a separate environment, ExcelRAddIn allows users to access these features within Excel itself. This has the potential to drastically increase productivity, as users can manipulate data and use statistical techniques in the same environment where data storage and initial analysis occur.

Predicted Future Developments

Beyond the impressive strides that have already been made, there appears to be potential for even more enhancements in the future. Currently, work is being done to improve default output display of results. This suggests that efforts are being focused on refining the user interface and experience, which can further bridge the divide between R and Excel functionalities. Future updates might streamline the integration process even more, implementing more innovative and user-friendly ways to interact with R functionality in Excel.

Actionable Advice

For developers and users of the ExcelRAddIn, the following recommendations could be beneficial:

1. Developers should continue to focus on improving user experience. This could be achieved through more comprehensive function wrappers and a refined interface that makes powerful R functions accessible, even to novice users.
2. Users should explore the full capabilities of the newly introduced functions and provide feedback on their functionality.
3. Training programs could be established or enhanced to use ExcelRAddIn efficiently and effectively. Particularly in academic or business settings, these types of education initiatives could lead to increased productivity and better utilization of both Excel and R functionalities.
4. Institutions that use Excel for data analysis should consider incorporating ExcelRAddIn into their workflow. This could make complex statistical analyses more accessible and save time on transferring data between work environments.
5. Developers may also consider cross collaboration with the creators of other widely used Excel add-ins. With a coordinated effort, these teams can create tools that efficiently perform tasks, respond to user needs and prevent redundancies.

Read the original article