How many times has MS Excel given you a hard time while building complex models or importing that extra-large data set into the spreadsheet? As a trader, I would love to see crisp formulas in my worksheets and more importantly, I would want that my models are less prone to errors when I am trading in live market. What if I tell you that there is a microlith that can tear through these shortcomings and leverage the power of R in Excel in a hassle-free and non-tedious manner? Friends, let me introduce you to RExcel, an add-in which allows you to use R functionalities in MS Excel.
The RExcel plugin can be used for:
- Using R functions via cell formula/macros
- Running R scripts through excel
- Transfering data between R and Excel.
Developed by Erich Neuwirth, RExcel works on Microsoft Windows with Excel 2003, 2007, 2010 and 2013. It uses the statconnDCOM server and the rcom package to access R from Excel.
Before you start using RExcel, you will need the following:
- A suitable version of R
- A matching version of rscproxy
- statconnDCOM or rcom with statconnDCOM
You can find the link to install these in your system at the end of this article along with the download link.
Let’s come back to our tutorial now. There are three ways of using RExcel –
- Worksheet functions
- Macro mode
- Scratchpad mode.
I will illustrate each of these modes with examples.
Method I: Using RExcel worksheet functions
As the name suggests, these functions call R functions in Excel worksheet cells. The list of functions include:
- Other argument modifier functions.
You can refer to the help and documentation link in RExcel help tab to see the complete list of worksheet functions.
How do we use these functions?
Let me demonstrate with some examples.
1) Calculating the mean:
Let’s say that I wish to calculate the mean of the OHLC prices for the two stocks using the RApply function.
How do I do that?
I will use RApply which will allow to call any R function as an Excel worksheet function. We call the mean function and apply it over the OHLC prices.
Pretty simple, isn’t it?
2) Defining functions:
Now, if I want to define my custom function and apply it using a given set of arguments, then I can do that as well. In this example, I round off the mean price to the nearest 0.05 paisa using the function shown below. The minimum tick for NSE stocks is Rs. 0.05.
3) Applying functions over a range for quick execution:
If we use multiple RApply calls then it slows down the computation considerably. To overcome this we can use Excel array formulas instead of multiple RApply’s, and speed up the computation. The example below illustrates the rounding using the array function. Check the link to learn how array formulas are written in Excel.
Method II: Using RExcel by Connecting R via Macros
RExcel has VBA procedures and functions to connect R via Macros. However, prior to starting in Macros you need to set the reference to RExcelVBAlib from the References tab (In the VBA window, see in Tools -> References).
Let me take a couple of examples to illustrate the macro method.
Example: Running an R script and generating the output in excel.
I am going to run an R script called “Top Gainers of the day.R” from the “RunRScript” macro (code shown below). When I execute this, the R script generates a list of top 5 NSE stock gainers of that day.
How does it do this?
It does this by sorting the percentage price change for all the given stocks in a descending order, and stores the top 5 in the “TopGainers_df” dataframe. We will run the macro and print the dataframe in our excel worksheet.
The commands mentioned in the above macro have the following meaning:
- The RInterface.StartRServer starts the R server.
- The RInterface.RRun executes the command string that follows.
- The RInterface.RunRFile executes the R script mentioned in the quotes.
- The RInterface.GetDataframe command is used to retrieve the output in Excel. This command takes two arguments, the name of the dataframe variable, and the location in Excel where we want to print the output.
- Finally, the StopRServer command stops the R server.
The output printed in Excel upon running the macro is shown below.
How do I call R functions in Macro?
There is another way of using R functions in macros. I have given macro code below where I have stored a list of stocks with their percentage price change on Sheet3 of the workbook.
I have used the RInterface.PutDataframe command to assign this range as a dataframe to R. Then I called the arrange function from the dplyr package, and got the top 5 NSE stock gainers of the day.
Finally, I use the RInterface.GetDataframe to print this dataframe onto sheet2 of the workbook.
Thus, upon running this “Arrange” macro I was able to produce the same result as obtained in the first example.
These macros can be attached to menu items or toolbar items for easy execution. Once again, I will advise you to refer to the help and documentation link in RExcel help tab to see the complete list of procedures and functions available.
Method III: Using RExcel’s Scratchpad method
In this method, I write the R expressions on an Excel sheet, and execute it using the buttons in the RExcel Menu. One needs to initiate R connection by selecting the “Start R” link from the RExcel Menu.
- We select the range (I3:I5) shown below
- Then click “Run R” from the RExcel menu
- Next, we select an empty cell (M3)
- Select “Get R Value” and when prompted, indicate the cell (I5 in this case) containing the final expression.
- The output from the expression gets printed in this empty cell.
I just used R’s cbind function and generated the output in Excel.
The scratchpad method can be applied to scalars, vectors, data frames or to a matrix. There are other additional operations that can be done using this method, and you know very well where you can learn these.
Combining the power of R with Excel via RExcel can surely simplify things for traders using R in Excel, and as a result, provide them with more firepower to backtest their strategies and execute them in MS Excel.
Learn the Index Tracking trading strategy where you observe price on the previous ‘n’ candlesticks and make your bets accordingly. The intuition is that MSCI FUTURES follows the ETF. Learn more about it here.
Sources & References:
For Installation of R, R(D)COM server and RExcel go to: https://goo.gl/MnZ2jA