A million ways to connect R and Excel

In quantitative finance both R and Excel are the basis tools for any type of analysis. Whenever one has to use Excel in conjunction with R, there are many ways to approach the problem and many solutions. It depends on what you really want to do and the size of the dataset you’re dealing with. I list some possible connections in the table below.

I want to… R function/package
Read Excel spreadsheet in R gdata
RODBC
XLConnect
xlsx
xlsReadWrite
read.table(“clipboard”)
RExcel
Read R output in Excel write.table
RExcel
Execute R code in VBA Custom function
RExcel
Execute R code from Excel spreadsheet RExcel
Execute VBA code in R Custom function
Fully integrate R and Excel RExcel

 

1 – Read Excel spreadsheet in R

  • gdata: it requires you to install additional Perl libraries on Windows platforms but it’s very powerful.
require(gdata)
myDf <- read.xls ("myfile.xlsx"), sheet = 1, header = TRUE)
  • RODBC: This is reported for completeness only. It’s rather dated; there are better ways to interact with Excel nowadays.
  • XLConnect:  It might be slow for large dataset but very powerful otherwise.
require(XLConnect)
wb <- loadWorkbook("myfile.xlsx")
myDf <- readWorksheet(wb, sheet = "Sheet1", header = TRUE)
  • xlsx:  Prefer the read.xlsx2() over read.xlsx(), it’s significantly faster for large dataset.
require(xlsx)
read.xlsx2("myfile.xlsx", sheetName = "Sheet1")
  • xlsReadWrite: Available for Windows only. It’s rather fast but doesn’t support .xlsx files which is a serious drawback. It has been removed from CRAN lately.
  • read.table(“clipboard”):  It allows to copy data from Excel and read it directly in R. This is the quick and dirty R/Excel interaction but it’s very useful in some cases.
myDf <- read.table("clipboard")

2 – Read R output in Excel
First create a csv output from an R data.frame then read this file in Excel. There is one function that you need to know it’s write.table. You might also want to consider: write.csv which uses “.” for the decimal point and a comma for the separator and write.csv2 which uses a comma for the decimal point and a semicolon for the separator.

x <- cbind(rnorm(20),runif(20))
colnames(x) <- c("A","B")
write.table(x,"your_path",sep=",",row.names=FALSE)

3 – Execute R code in VBA
RExcel is from my perspective the best suited tool but there is at least one alternative. You can run a batch file within the VBA code.  If R.exe is in your PATH, the general syntax for the batch file (.bat) is:

R CMD BATCH [options] myRScript.R

Here’s an example of how to integrate the batch file above within your VBA code.

4 – Execute R code from an Excel spreadsheet
Rexcel is the only tool I know for the task. Generally speaking once you installed RExcel you insert the excel code within a cell and execute from RExcel spreadsheet menu. See the RExcel references below for an example.

5 – Execute VBA code in R 
This is something I came across but I never tested it myself. This is a two steps process. First write a VBscript wrapper that calls the VBA code. Second run the VBscript in R with the system or shell functions. The method is described in full details here.

6 – Fully integrate R and Excel
RExcel is a project developped by Thomas Baier and Erich Neuwirth, “making R accessible from Excel and allowing to use Excel as a frontend to R”. It allows communication in both directions: Excel to R and R to Excel and covers most of what is described above and more. I’m not going to put any example of RExcel use here as the topic is largely covered elsewhere but I will show you where to find the relevant information. There is a wiki for installing RExcel and an excellent tutorial available here.  I also recommand the following two documents: RExcel – Using R from within Excel and High-Level Interface Between R and Excel. They both give an in-depth view of RExcel capabilities.

The list above is probably not exhaustive. Feel free to come back to me for any addition or modification you might find useful. All code snipets have been created by Pretty R at inside-R.org

22 Comments

  1. Michael Bach says:

    Thank you, very helpful! Best, Michael

  2. Michael Bach says:

    Thanks to your suggestion I tried xlsx’s read.xlsx2. Initially, the results were quite different from gdata’s read.xls. The reason: default data type is “Character”. Adding “colClasses=NA” ended up in read.xlsx2 correctly guessing all data types for me.

    Best, Michael.

  3. Sam Austin says:

    Fantastic! I’ve really been trying to get some traction on using R with Excel. Thanks so much.

  4. Paul says:

    Another handy and quick output to Excel method – just as you can read.table() from the clipboard, you can also use write.table() with clipboard!

    It can spare you the trouble of creating a .csv, when you just want to quickly paste a data table into an existing Excel worksheet.

    write.table(x,”clipboard”,sep=”\t”)

  5. kmod says:

    Why even bother with EXCEL? It is not a serious data analytic tool, it is proprietary and does not work on all platforms (not on Linux, Unix or BSD)

    A most important feature of R is its opennes, that’s why it is so robust, versatile and infinitely extendible. The philosophy of EXCEL is the complete opposite, I can’t think of anything more incompatible with R. People using proprietary formats like xlsx for no good reasons are helping to entrench this closed format while undermining open stadards, thereby enabling MicroSoft’s monopoly.

    The sooner people kick their addiction to EXCEL (and MicroSoft Office in general) the better.

    • The R Trader says:

      Whether Excel is a serious analytic tool or not isn’t the point. Everybody in the financial industry is using it and we have to accept it shall we want to communicate efficiently.

  6. kmod says:

    If EXCEL users desire to communicate efficiently with R or other tools that respect open standard maybe it should be up to them to change. After all R costs them $0.

    I am sorry, I am having a problem with using open source, cross platform software to create yet another “Windows only” application and thereby sustaining and spreading MicroSoft’s closed ecology.

  7. pe@rson says:

    Thank you a very helpful.

    Another way to easily connect R and excel is published here:

    http://www.sthda.com/english/articles/articles-2-8+r2xlsx-how-to-read-write-and-format-excel.php

  8. Govert says:

    An alternative to RExcel is to use the R.NET project, and then create an Excel Add-In in C#, F# or VB.NET that calls R through R.NET. This has the advantage of eliminating the RExcel dependency, which talks to R through COM and can cause frustrations.

    Write-ups on how to use R with Excel-DNA are here: http://mockquant.blogspot.com/2011/07/another-way-to-use-r-in-excel-for-net.html
    and via F# and the F# type provider: http://luajalla.azurewebsites.net/excel-dna-three-stories/

  9. Irucka Embry says:

    Hi, thank you for the blog post.

    I have a couple of suggestions for additional options for “Read Excel spreadsheet in R”:

    1) openxlsx (https://github.com/awalker89/openxlsx)

    2) psych (read.clipboard functions) [http://cran.r-project.org/web/packages/psych/index.html]

    Thank you.

  10. […] there are R packages for importing Excel data directly (see here, here, and here), it’s generally best to export the Excel data to a .csv file and then import the .csv file […]

  11. […] Getting data from Excel into R is typically accomplished by one of the many data import R packages. These work well for repetitive data import and export but are often cumbersome for a quick look at some Excel data in R or to get a couple of R variables into Excel. […]

  12. Michael says:

    Hi,

    Thanks for the blog post. Very informative.

    At http://www.xldatawise.com we have created a connector between R and Excel that natively exposes R methods. eg If you have a user function defined like this:
    exampleRFunction <- function(param1, param2) {
    param1 + param2
    } it will expose an Excel function called dw.r.f.GlobalEnv.exampleRFunction(param1, param2) which you can call directly from your sheet.

    Hope this is helpful.

    Michael

  13. […] Getting data from Excel into R is typically accomplished by one of the many data import R packages. These work well for repetitive data import and export but are often cumbersome for a quick look at some Excel data in R or to get a couple of R variables into Excel. […]

  14. CorReg says:

    RExcel is not alone anymore.
    BERT (Basic Excel R Toolkit) is available now licensed under the GPL v2. So it can be used for free even for commercial studies.
    http://bert-toolkit.com/

  15. Patrick Williams says:

    very good summary of ways to connect R with Excel. Very helpful.

    I have found the Java dependency of many of the Excel packages in R to be limiting. Do you use Openxlsx?

Leave a Reply