Serving the Quantitative Finance Community

 
User avatar
closdubois
Topic Author
Posts: 0
Joined: September 26th, 2008, 1:12 am

Using Excel as a frontend for R

September 28th, 2013, 1:57 am

I want to be able to manually load some (already cleaned up) data and input some parameters into Excel, press a button, and have fancy statistical analysis done in R, then spit the results (along with pictures) back into Excel.I'm aware of a few existing solutions integrating R and Excel, and some more potential solutions, e.g.- RExcel //won't work due to licensing issues; not compatible with new R versions- ExcelDna + F# RProvider with R.NET //seems a little convoluted, and might be overkill since our 'input data' are usually nice and simple- ExcelDna + C# with Rserve //seems promising, and probably won't be too difficult to write up(??)- Excelsi-R //homebrew open-source Excel addin written in C++ that talks to Rserve, haven't tested it but it doesn't run on 64-bit. Perhaps can be extended.If anyone else was unfortunate enough to have to tackle this problem, I'd appreciate your feedback!
 
User avatar
Hansi
Posts: 41
Joined: January 25th, 2010, 11:47 am

Using Excel as a frontend for R

September 28th, 2013, 10:54 am

1) We use RExcel for the quants on the team that want this (I prefer just doing everything in R and spitting out to excel only for output directly via RCOM if need be),you can get custom licences if you just e-mail the statconn guys, we got a commercial licence for everyone on the team for a modest fee.This is the default commercial licence but I think legal asked for some modifications to ours that they complied with and provided an updated one:http://www.statconn.com/licenses/REXCEL ... ional.txt2) is okay, I've used the provider with a ASP.NET web app rather than ExcelDNA, some issues getting it going but runs pretty much without errors.3) I found is a bit more troublesome than 2 actually, but works too. Tested it out about 2 or 3 years back with marshaling to C++. RserveCLI seems to make it easier now.4) Never heard of this.For on desk tinkering I find RExcel easiest to handle as it's fairly easy for people that only know R and VBA to make changes to the code themselves.For user consumption stuff setting up ExcelDNA -> ASP.NET Webservice on a box with 2 or 3 might be a good idea as long as the input data isn't massive.
 
User avatar
Govert
Posts: 0
Joined: January 28th, 2006, 10:02 am

Using Excel as a frontend for R

September 28th, 2013, 2:18 pm

Throwing in F# just to use the type provider seems a bit gratuitous.Maybe just glue R.NET directly through an Excel-DNA C# (or VB.NET) wrapper. There is a write-up on this (with some Japanese): http://mockquant.blogspot.com/2011/07/a ... et.html.Of course, if you are using F#, then the R type provider (with Excel-DNA) makes a lot of sense. Natalie Baikovitch posted a sample on how to do this: http://luajalla.azurewebsites.net/excel ... es/-Govert