{"id":546,"date":"2014-02-28T13:18:38","date_gmt":"2014-02-28T18:18:38","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=546"},"modified":"2014-02-28T13:18:38","modified_gmt":"2014-02-28T18:18:38","slug":"a-million-ways-to-connect-r-and-excel","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2014\/02\/28\/a-million-ways-to-connect-r-and-excel\/","title":{"rendered":"A million ways to connect R and Excel"},"content":{"rendered":"<p>In quantitative finance both R and Excel are the basis tools for any type of analysis.\u00a0Whenever 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\u2019re dealing with. I list some possible connections in the table below.<\/p>\n<table>\n<tbody>\n<tr>\n<th>I WANT TO\u2026<\/th>\n<th>R FUNCTION\/PACKAGE<\/th>\n<\/tr>\n<tr>\n<td>Read Excel spreadsheet in R<\/td>\n<td><a title=\"gdata\" href=\"http:\/\/cran.r-project.org\/web\/packages\/gdata\/index.html\" target=\"_blank\" rel=\"noopener\">gdata<\/a><br \/>\n<a title=\"RODBC\" href=\"http:\/\/cran.r-project.org\/web\/packages\/RODBC\/index.html\" target=\"_blank\" rel=\"noopener\">RODBC<\/a><br \/>\n<a title=\"XLConnect\" href=\"http:\/\/cran.r-project.org\/web\/packages\/XLConnect\/index.html\" target=\"_blank\" rel=\"noopener\">XLConnect<\/a><br \/>\n<a title=\"xlsx\" href=\"http:\/\/cran.r-project.org\/web\/packages\/xlsx\/index.html\" target=\"_blank\" rel=\"noopener\">xlsx<\/a><br \/>\nxlsReadWrite<br \/>\nread.table(\u201cclipboard\u201d)<br \/>\n<a title=\"RExcel\" href=\"http:\/\/rcom.univie.ac.at\/download.html\" target=\"_blank\" rel=\"noopener\">RExcel<\/a><\/td>\n<\/tr>\n<tr>\n<td>Read R output in Excel<\/td>\n<td>write.table<br \/>\n<a title=\"RExcel\" href=\"http:\/\/rcom.univie.ac.at\/download.html\" target=\"_blank\" rel=\"noopener\">RExcel<\/a><\/td>\n<\/tr>\n<tr>\n<td>Execute R code in VBA<\/td>\n<td>Custom function<br \/>\n<a title=\"RExcel\" href=\"http:\/\/rcom.univie.ac.at\/download.html\" target=\"_blank\" rel=\"noopener\">RExcel<\/a><\/td>\n<\/tr>\n<tr>\n<td>Execute R code from Excel spreadsheet<\/td>\n<td><a title=\"RExcel\" href=\"http:\/\/rcom.univie.ac.at\/download.html\" target=\"_blank\" rel=\"noopener\">RExcel<\/a><\/td>\n<\/tr>\n<tr>\n<td>Execute VBA code in R<\/td>\n<td>Custom function<\/td>\n<\/tr>\n<tr>\n<td>Fully integrate R and Excel<\/td>\n<td><a title=\"RExcel\" href=\"http:\/\/rcom.univie.ac.at\/download.html\" target=\"_blank\" rel=\"noopener\">RExcel<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p><strong>1 \u2013 Read Excel spreadsheet in R<\/strong><\/p>\n<ul>\n<li><em>gdata<\/em>:\u00a0it requires you to install additional Perl libraries on Windows platforms but it\u2019s very powerful.<\/li>\n<\/ul>\n<div>\n<div>\n<pre><a href=\"http:\/\/inside-r.org\/r-doc\/base\/require\" target=\"_blank\" rel=\"noopener\">require<\/a>(<a href=\"http:\/\/inside-r.org\/packages\/cran\/gdata\" target=\"_blank\" rel=\"noopener\">gdata<\/a>)\nmyDf &lt;- read.xls (\"myfile.xlsx\"), sheet = 1, header = TRUE)<\/pre>\n<\/div>\n<\/div>\n<ul>\n<li><em>RODBC<\/em>: This is reported for completeness only. It\u2019s rather dated; there are better ways to interact with Excel nowadays.<\/li>\n<\/ul>\n<ul>\n<li><em>XLConnect<\/em>:\u00a0\u00a0It might be slow for large dataset but very powerful otherwise.<\/li>\n<\/ul>\n<div>\n<div>\n<pre><a href=\"http:\/\/inside-r.org\/r-doc\/base\/require\" target=\"_blank\" rel=\"noopener\">require<\/a>(XLConnect)\nwb &lt;- loadWorkbook(\"myfile.xlsx\")\nmyDf &lt;- readWorksheet(wb, sheet = \"Sheet1\", header = TRUE)<\/pre>\n<\/div>\n<\/div>\n<ul>\n<li><em>xlsx<\/em>: \u00a0Prefer the\u00a0<code>read.xlsx2()<\/code>\u00a0over\u00a0read.xlsx(), it\u2019s significantly faster for large dataset.<\/li>\n<\/ul>\n<div>\n<div>\n<pre><a href=\"http:\/\/inside-r.org\/r-doc\/base\/require\" target=\"_blank\" rel=\"noopener\">require<\/a>(<a href=\"http:\/\/inside-r.org\/packages\/cran\/xlsx\" target=\"_blank\" rel=\"noopener\">xlsx<\/a>)\nread.xlsx2(\"myfile.xlsx\", sheetName = \"Sheet1\")<\/pre>\n<\/div>\n<\/div>\n<ul>\n<li><em>xlsReadWrite<\/em>: Available for Windows only. It\u2019s rather fast but doesn\u2019t support .xlsx files which is a serious drawback. It has been removed from CRAN lately.<\/li>\n<\/ul>\n<ul>\n<li><em>read.table(\u201cclipboard\u201d)<\/em>: \u00a0It allows to copy data from Excel and read it directly in R.\u00a0This is the quick and dirty R\/Excel interaction but it\u2019s very useful in some cases.<\/li>\n<\/ul>\n<div>\n<div>\n<pre>myDf &lt;- <a href=\"http:\/\/inside-r.org\/r-doc\/utils\/read.table\" target=\"_blank\" rel=\"noopener\">read.table<\/a>(\"clipboard\")<\/pre>\n<\/div>\n<\/div>\n<p><strong>2 \u2013 Read R output in Excel<\/strong><br \/>\nFirst 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\u2019s\u00a0<em>write.table<\/em>. You might also want to consider:\u00a0<em>write.csv<\/em>\u00a0which uses \u201c.\u201d for the decimal point and a comma for the separator and\u00a0<em>write.csv2<\/em>\u00a0which uses a comma for the decimal point and a semicolon for the separator.<\/p>\n<div>\n<div>\n<pre>x &lt;- <a href=\"http:\/\/inside-r.org\/r-doc\/base\/cbind\" target=\"_blank\" rel=\"noopener\">cbind<\/a>(<a href=\"http:\/\/inside-r.org\/r-doc\/stats\/rnorm\" target=\"_blank\" rel=\"noopener\">rnorm<\/a>(20),<a href=\"http:\/\/inside-r.org\/r-doc\/stats\/runif\" target=\"_blank\" rel=\"noopener\">runif<\/a>(20))\n<a href=\"http:\/\/inside-r.org\/r-doc\/base\/colnames\" target=\"_blank\" rel=\"noopener\">colnames<\/a>(x) &lt;- <a href=\"http:\/\/inside-r.org\/r-doc\/base\/c\" target=\"_blank\" rel=\"noopener\">c<\/a>(\"A\",\"B\")\n<a href=\"http:\/\/inside-r.org\/r-doc\/utils\/write.table\" target=\"_blank\" rel=\"noopener\">write.table<\/a>(x,\"your_path\",sep=\",\",<a href=\"http:\/\/inside-r.org\/r-doc\/base\/row.names\" target=\"_blank\" rel=\"noopener\">row.names<\/a>=FALSE)<\/pre>\n<\/div>\n<\/div>\n<p><strong>3 \u2013 Execute R code in VBA<\/strong><br \/>\nRExcel is from my perspective the best suited tool but there is at least one alternative.\u00a0You can run a batch file within the VBA code. \u00a0If R.exe is in your PATH, the general syntax for the batch file (.bat) is:<\/p>\n<div>\n<div>\n<pre>R CMD <a href=\"http:\/\/inside-r.org\/packages\/cran\/batch\" target=\"_blank\" rel=\"noopener\">BATCH<\/a> [<a href=\"http:\/\/inside-r.org\/r-doc\/base\/options\" target=\"_blank\" rel=\"noopener\">options<\/a>] myRScript.R<\/pre>\n<\/div>\n<\/div>\n<p>Here\u2019s an\u00a0<a title=\"example\" href=\"http:\/\/stackoverflow.com\/questions\/9403341\/running-a-batch-file-in-a-given-directory-using-vba\" target=\"_blank\" rel=\"noopener\">example<\/a>\u00a0of how to integrate the batch file above within your VBA code.<\/p>\n<p><strong>4 &#8211;\u00a0Execute R code from an Excel spreadsheet<\/strong><br \/>\nRexcel 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.<\/p>\n<p><strong>5 \u2013 Execute VBA code in R\u00a0<\/strong><br \/>\nThis 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\u00a0<code>system<\/code>\u00a0or\u00a0<code>shell<\/code>\u00a0functions. The method is described in full details\u00a0<a title=\"here\" href=\"http:\/\/stackoverflow.com\/questions\/19404270\/run-vba-script-from-r\" target=\"_blank\" rel=\"noopener\">here<\/a>.<\/p>\n<p><strong>6 \u2013 Fully integrate R and Excel<\/strong><br \/>\n<a title=\"RExcel\" href=\"http:\/\/rcom.univie.ac.at\/download.html\" target=\"_blank\" rel=\"noopener\">RExcel<\/a>\u00a0is a project developped by Thomas Baier and Erich Neuwirth, \u201cmaking R accessible from Excel and allowing to use Excel as a frontend to R\u201d. It allows communication in both directions: Excel to R and R to Excel and covers most of what is described above and more. I\u2019m not going to put any example of RExcel use\u00a0here\u00a0as the topic is largely covered elsewhere but I will show you where to find the relevant information. There is a\u00a0<a title=\"wiki\" href=\"http:\/\/homepage.univie.ac.at\/erich.neuwirth\/php\/rcomwiki\/doku.php?id=wiki:how_to_install\" target=\"_blank\" rel=\"noopener\">wiki<\/a>\u00a0for installing RExcel and an\u00a0excellent tutorial available\u00a0<a title=\"here\" href=\"http:\/\/rcom.univie.ac.at\/RExcelDemo\/\" target=\"_blank\" rel=\"noopener\">here<\/a>. \u00a0I also recommand the following two documents:\u00a0<a title=\"RExcel - Using R from within Excel\" href=\"https:\/\/www.unt.edu\/rss\/class\/splus\/UsingRWithinExcel.pdf\" target=\"_blank\" rel=\"noopener\">RExcel \u2013 Using R from within Excel<\/a>\u00a0and\u00a0<a title=\"High-Level Interface Between R and Excel\" href=\"http:\/\/www.r-project.org\/conferences\/DSC-2003\/Proceedings\/BaierNeuwirth.pdf\" target=\"_blank\" rel=\"noopener\">High-Level Interface Between R and Excel<\/a>. They both give an in-depth view of RExcel\u00a0capabilities.<\/p>\n<p>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<a title=\"created by Pretty R at inside-R.org\" href=\"http:\/\/www.inside-r.org\/pretty-r\" target=\"_blank\" rel=\"noopener\">created by Pretty R at inside-R.org<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In quantitative finance both R and Excel are the basis tools for any type of analysis.\u00a0Whenever one has to use Excel in conjunction with R,&hellip; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-546","post","type-post","status-publish","format-standard","hentry","category-r"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/546","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=546"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/546\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}