RExcel doesn't

RExcel is a commercial product (free for educational use) that is supposed to allow using R from within Excel. The website is here. Unfortunately, it's a pain to install and in the end I couldn't get this to work using R 2.15.3 and Excel 2010. I get the error message "RServer seems to have been disconnected or closed." If you Google this phrase, others have had the same problem.

There are undoubtedly commercial uses for R within Excel, but R has far better programability, better graphics, and better reproducibility. (It's a lot easier to share R code with someone than to share a spreadsheet formula). R can make Excel better, but you shouldn't think that using Excel as a crutch will make it easier to learn R.

RExcel might work, but it also might just waste your time. Even if it works, you'll end up with an Excel/R hybrid that might not work for your colleagues, friends and coworkers, and that has separate licenses distinguishing noncommercial, commercial, and educational uses. (You'll get a headache just trying to figure out the difference between the licenses.)

If you have a business need for RExcel, buy a license (approximately $450 per computer) and get support. However, if you want to learn R, learn R. Don't worry about Excel integration. Excel is platform-dependent and clumsy to work with if you're doing anything non-trivial. If you need to pass data back and forth between R and Excel, use CSV files. It's an old technology, but at least it works.

Why I Love R

I'm updating the volatility portion of my Derivatives notes for class. I wanted to freshen a plot of the VIX index. Here is the R code to grab the complete VIX times series from Yahoo and create a pdf file:

 vix <- read.csv("http://ichart.finance.yahoo.com/table.csv?s=^VIX",  
         stringsAsFactors=FALSE)[, c(1, 7)]  
 pdf("vixplot.pdf")  
 plot(as.Date(vix[, 1]), vix[, 2], main="VIX, 1990 to Present",  
    xlab='Date', ylab='VIX', cex=0.3, type='p', ylim=c(0, 80))  
 dev.off()  

And it's a decent looking plot:

R and explicit loops

How much difference does it make if you use explicit or implicit loops in R? By "implicit loop", I mean using a sequence within a function. Here is a code snippet illustrating three ways to draw 200,000 normal random variables. Two are explicit loops within functions, the third is an implicit loop, simply supplying an argument to rnorm. The second function states the size of the vector when creating it.

 randnorm <- function(n) {  
  y <- vector()  
  for (i in 1:n) y[i] <- rnorm(1)  
  return(y)  
 }  
 randnorm2 <- function(n) {  
  y <- vector(length=n, mode='numeric')  
  for (i in 1:n) y[i] <- rnorm(1)  
  return(y)  
 }  
 n <- 2e05  
 system.time(y <- randnorm(n))  
 system.time(y <- randnorm2(n))  
 system.time(y <- rnorm(n))  

Here are the results:

 > n <- 2e05  
 > system.time(y <- randnorm(n))  
   user system elapsed   
  28.170  0.352 28.618   
 > system.time(y <- randnorm2(n))  
   user system elapsed   
  0.856  0.000  0.858   
 > system.time(y <- rnorm(n))  
   user system elapsed   
  0.032  0.000  0.031      

What do we conclude?

• The implicit loop (rnorm(n)) is much faster than the alternatives.
• If you do have to allocate a vector, it is better to create the vector in advance, as in randnorm2, specifying the length and mode
• The unsized vector in randnorm creates a very slow function. Moreover, in tests it appears to become exponentially slower as the size of the vector grows. A function written like randnorm should be a last resort.

The moral is, if possible, to to avoid constructs like that in randnorm.

R and the year 2038 problem

I am teaching an R workshop, which has met twice. There are things I either don't get to in class, or fumble when discussing them. I'm going to occasionally discuss a few of these issues here for the benefit of students, for me (this will serve as a reference in the future), and for anyone else who may be interested.

Dates in base R can be represented using the Date class, POSIXct, or POSIXlt. We can examine date handling by considering the year 2038 problem. The year 2038 problem arises as follows: if you use a signed 32-bit integer to represent the number of seconds since January 1, 1970 (as Unix systems traditionally have), you exhaust the capacity of that integer on January 19, 2038. Using R to verify this requires first that we know what it means to use a "signed 32-bit integer."

Background: Suppose you want to represent an integer as a "signed 3-bit integer". You can do this by representing integers in binary, with the leftmost integer telling you whether the integer is positive or negative. So we have that decimal 0 is 000, decimal 1 is 001, decimal 2 is 010, and decimal 3 is 011. What happens when we go to the next value? Binary 100 is a negative number because the leftmost number is a 1. Thus, when we have a 3-bit signed integer, the largest positive value we can represent is 3 = 2^2 - 1. In general, for an n-bit representation, the largest value is 2^(n-1) - 1. With a 32-bit signed integer, the largest (positive) value you can represent is 2^31 - 1.

R's POSIXlt class provides a general purpose function for displaying and manipulating dates. The Unix epoch (0 seconds) occurred on January 1, 1970. When will the 32-bit time integer be exhausted? We can use POSIXlt to find out:

 > as.POSIXlt(2^31 - 1, origin = '1970-1-1', tz="GMT")  
 [1] "2038-01-19 03:14:07 GMT"  

Thus, unpatched 32-bit UNIX systems will come to a screeching and erratic halt on January 19, 2038, a few seconds after 3:14am, GMT. Note that as.POSIXlt assumes that the number you supply is in seconds, and performs a reasonable (and correct) calculation. The function will try to guess at what you intend.

The POSIXlt calculations in R continue to work for larger integers. You may wish to amuse yourself by discovering the largest integer for which as.POSIXlt will continue to provide a valid date. (Hint: it's between 2^45 and 2^50, and it is millions of years in the future.)

R in the New York Times

The New York Times graphics department maintains a fascinating blog providing a behind-the-scenes look at the construction of graphics appearing in the paper and on the web site. Many of the posts deal with R, which the graphics department apparently uses a lot.

In this post they show you some graphs pertaining to political party dominance in state government, and this subsequent post shows the R code used to design the charts. The post also provides the raw data, so you can recreate the plots. What I really like about this post (and the blog in general) is that it doesn't just advertise the finished product (which is typically excellent), but it shows you the process by which the team arrived at the finished product.

Two of my favorite R-related posts are those depicting all passes made by each player on the US Women's Soccer team, and this one comparing the Facebook IPO to other IPOs (this post doesn't mention R, but I'm 99.9% sure that the graphs are produced in R --- axis variable names like test$date give it away).