November 7, 2017

EasyER, Using R in Excel

Facebooktwittergoogle_plusredditlinkedin

Running for the first time

Once installed the Excel ribbon will show the EasyER tab. The Add  In is by default inactive when Excel starts. Once one of the menu options that require a R connection is clicked for the first time after opening Excel the Add In will start up and attempt to connect to the local R installation.

EasyER requires the R package R.Devices  and ggplot2 to be installed. If these are not found then they will be downloaded and installed automatically, if the option of allowing EasyER to install packages is set, see here for details. The installing of packages has been improved for version 1.2 but can still be temperamental on some systems and may require Excel to be restarted before the installed packages area picked up correctly.

The following screen shot shows EasyER installing R packages. This is a one time process for each R installation. Package installing will also happen it you have function files installed that require packages not installed in R. For example the time series demonstration functions require several.

 

The Excel message bar will inform on progress. If you have R installed using the defaults then their shouldn’t be a problem and the Add In will show the Functions pane. Wait until the message bar return to ‘ready’ before clicking any menu options.

The Installer also generates a folder in the users MyDocuments folder, ‘EasyER’. This is the default folder for the Add Ins function files to be saved. There is also a sub folder generated also called ‘EasyER’ where R code and images generated by the Add In are saved. This allows inspection of R code that the Add In has generated.

Setting up

The Set-Up button on the ribbon allows the location of the function files to be set and the R installation to connect to.

Function Folder

The Function Folder is where EasyER will look for function files. If this is left blank then it will be set to the default when EasyER is started for the first time. The demonstration function files that are bundles with the Add In are copied into this folder if it is found to be empty. This check is done every time EasyER starts so if another folder is selected then if left empty will be populated with the demo functions.

CRAN Mirror

This is the CRAN mirror that will be used to download any missing packages.

R Connection

Clicking on the button in the ribbon will display the details of the R version connected to.

The r session excel has connected to.

The R connection is for the Add In only. There is no way to access the R session EasyER is using.

Data Size

EasyER is a tool for Excel so doesn’t do ‘Big Data’. There is a ‘Test Data’ function in the the ‘Tests’ functions which generates 10 random variables the length of which can be changed. This is to enable testing the Add In with the size of data expected, and to see if the speed is suitable. On our development machines, EasyER handles columns of data with 10 to 100 thousand values without trouble. When the data point count gets to the millions then it becomes a bit sluggish.

Here are some further details on how to use EasyER

 

 

Facebooktwittergoogle_plusredditlinkedin