SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
tagoma
Topic Author
Posts: 18354
Joined: February 21st, 2010, 12:58 pm

Interfacing R and Excel

October 30th, 2013, 10:22 am

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)
 
User avatar
Ultraviolet
Posts: 1655
Joined: August 15th, 2012, 9:46 am

Interfacing R and Excel

October 30th, 2013, 10:46 am

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.
 
User avatar
Cuchulainn
Posts: 62129
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Interfacing R and Excel

October 30th, 2013, 1:39 pm

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.
 
User avatar
Cuchulainn
Posts: 62129
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Interfacing R and Excel

October 30th, 2013, 1:39 pm

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.
 
User avatar
cemil
Posts: 221
Joined: September 16th, 2005, 7:44 am

Interfacing R and Excel

October 30th, 2013, 2:08 pm

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.
 
User avatar
Hansi
Posts: 3300
Joined: January 25th, 2010, 11:47 am

Interfacing R and Excel

October 30th, 2013, 2:09 pm

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.
 
User avatar
Cuchulainn
Posts: 62129
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Interfacing R and Excel

October 30th, 2013, 2:22 pm

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.
 
User avatar
tagoma
Topic Author
Posts: 18354
Joined: February 21st, 2010, 12:58 pm

Interfacing R and Excel

October 30th, 2013, 9:17 pm

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.
 
User avatar
tagoma
Topic Author
Posts: 18354
Joined: February 21st, 2010, 12:58 pm

Interfacing R and Excel

March 16th, 2014, 2:54 pm

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
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On