SERVING THE QUANTITATIVE FINANCE COMMUNITY

tagoma
Topic Author
Posts: 18351
Joined: February 21st, 2010, 12:58 pm

### Interfacing R and Excel

Hi. Are there gentle ways to get data from-to R and Excel?I foremost wish to send my Excel data (in a table form) to R, receiving them into a dataframe.Is there a R package you recommend among the countless?Or do you advise not to do such things as it is a case for troubles of any kind? (and rather stick to save spreadsheets as *.csv files)

Ultraviolet
Posts: 1655
Joined: August 15th, 2012, 9:46 am

### Interfacing R and Excel

Do you mean DDE? There's tcltk2 with tk2dde (http://cran.r-project.org/web/packages/ ... tcltk2.pdf).You get the cell values via tk2dde.request("Excel", "{[Filename.xls]Sheet1}", "R1C2")R3C3 stands for row 1 column 2, but careful now, if you have another language version of Office (e.g. I have a Polish version), you should address the cell as "W1K3" (wiersz 1 kolumna 3). Only in Microsoft :-DEDIT: "R3C3 stands for row 1 column 2" - it's scary how unconscious I sometimes am when posting in this forum. Of course it should be R1C2, sorry.
Last edited by Ultraviolet on October 29th, 2013, 11:00 pm, edited 1 time in total.

Cuchulainn
Posts: 61518
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Interfacing R and Excel

I installed this without problemshttp://www.icis.cgiar.org/icis/index.php/Insta ... _RExcelAnd there's examples etc. I have not tested extensively yet.Maybe install RStudio as well. // R is cool env.
Last edited by Cuchulainn on October 29th, 2013, 11:00 pm, edited 1 time in total.
http://www.datasimfinancial.com
http://www.datasim.nl

Every Time We Teach a Child Something, We Keep Him from Inventing It Himself
Jean Piaget

Cuchulainn
Posts: 61518
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Interfacing R and Excel

QuoteOriginally posted by: UltravioletDo you mean DDE? There's tcltk2 with tk2dde (http://cran.r-project.org/web/packages/ ... tcltk2.pdf).:-DLink broke. Better to use COM link than DDE IMO. See my previous post.
Last edited by Cuchulainn on October 29th, 2013, 11:00 pm, edited 1 time in total.
http://www.datasimfinancial.com
http://www.datasim.nl

Every Time We Teach a Child Something, We Keep Him from Inventing It Himself
Jean Piaget

cemil
Posts: 221
Joined: September 16th, 2005, 7:44 am

### Interfacing R and Excel

QuoteOriginally posted by: CuchulainnQuoteOriginally posted by: UltravioletDo you mean DDE? There's tcltk2 with tk2dde (http://cran.r-project.org/web/packages/ ... tcltk2.pdf).:-DLink broke. try this Link
Last edited by cemil on October 29th, 2013, 11:00 pm, edited 1 time in total.

Hansi
Posts: 3300
Joined: January 25th, 2010, 11:47 am

### Interfacing R and Excel

QuoteOriginally posted by: edouardHi. Are there gentle ways to get data from-to R and Excel?I foremost wish to send my Excel data (in a table form) to R, receiving them into a dataframe.Is there a R package you recommend among the countless?Or do you advise not to do such things as it is a case for troubles of any kind? (and rather stick to save spreadsheets as *.csv files)http://cran.r-project.org/doc/manuals/r ... adsheetsIf massive data but one off: Use CSVIf complicated multi sheet, different ranges use XLConnect if you're okay with having Java on your box. If not use statconnDCOM + rcom package. If simple data and not a lot of it, use:
Last edited by Hansi on October 29th, 2013, 11:00 pm, edited 1 time in total.

Cuchulainn
Posts: 61518
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Interfacing R and Excel

QuoteOriginally posted by: cemilQuoteOriginally posted by: CuchulainnQuoteOriginally posted by: UltravioletDo you mean DDE? There's tcltk2 with tk2dde (http://cran.r-project.org/web/packages/ ... tcltk2.pdf).:-DLink broke. try this LinkCool.Just installed it from R (RStudio has tcl/tk only?)Looks easier to use than C++ DDE.
http://www.datasimfinancial.com
http://www.datasim.nl

Every Time We Teach a Child Something, We Keep Him from Inventing It Himself
Jean Piaget

tagoma
Topic Author
Posts: 18351
Joined: February 21st, 2010, 12:58 pm

### Interfacing R and Excel

Thanks all for your inputs. I wasn't sure which r package to choose among the many with some r-excel facilities. I'm going to dig the ways suggested.Hansi's trick that consists on actually exporting to R the Excel data via the clipbaord is smart.@Cuch I read good reviewes on Rstudio. But, I use R via Statet and eclipse.

tagoma
Topic Author
Posts: 18351
Joined: February 21st, 2010, 12:58 pm

### Interfacing R and Excel

QuoteOriginally posted by: HansiQuoteOriginally posted by: edouardHi. Are there gentle ways to get data from-to R and Excel?I foremost wish to send my Excel data (in a table form) to R, receiving them into a dataframe.Is there a R package you recommend among the countless`?Or do you advise not to do such things as it is a case for troubles of any kind? (and rather stick to save spreadsheets as *.csv files)http://cran.r-project.org/doc/manuals/r ... adsheetsIf massive data but one off: Use CSVIf complicated multi sheet, different ranges use XLConnect if you're okay with having Java on your box. If not use statconnDCOM + rcom package. If simple data and not a lot of it, use:I just saw that. A data.frame object is returned. Hard to do more simple