calling R from excel VB

R is a very good tool for data visualization and for generating reports apart from the large number of statistical packages it has to offer.

While in legacy systems data manipulation is done in excel.

This post will describe how to make these two systems talk - calling R code from Excel.


The utility for this tool would be when there is a legacy system that is difficult to change because of a number of dependencies on the up or downstream but one needs a capability that R is very strong at - be it statistical or data visualization and when one needs to execute the R code once certain steps are finished in excel




Here's the steps to follow:

1. Have the R code that you want to execute at a location: lets' say
C:/Users/kishorea/Desktop/summary_variance_code.txt
here, summary_variance_code.txt is the text file which contains R code

2. Execute the shell command from excel macro: Developer -> Create macro

Call Shell("C:/Program Files/R/R-2.12.2/bin/Rscript.exe C:/Users/kishorea/Desktop/summary_variance_code.txt")

Step 2 opens up the command window and executes the R script.


There you go, R running from excel

Number of tweets across US

Plot of number of tweets at the end of each hour during the different days of a week.
Data is taken from 29th november, 2011 to 5th December 2011
Please ignore the 19 in x-axis. It was conceived to visualise data through years, but for this application it is the nth hour of the day.
For example, 1920 would be 20:00 hrs or 8PM.


(The chart and maps will take a few seconds to load, please scroll down to see the chart)

1. Please click on the line chart icon at the top right corner

2. Select unique_colour in color section to contrast between weekdays.


Optional 3. Setting opacity to 0% in the settings option in the bottom right corner of the chart would give a better contrast.

Sorry for the inconvenience, I will fix it asap.

Hover your mouse over the states to see the number of tweets in each state.
The states without any tweets are not even mentioned

The tweet count includes the tweets generated by bots

Number of Positive words in a tweet:

One should be wary of the low number of tweets in certain states, while ascertaining the positive tweet ratio.