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).

Importing data into Google Calendar

Entering data into Google calendar can be tedious, but it turns out that there is a relatively easy way to perform a bulk import of data. At the bottom left corner of the calendar page, there is an "Add" button. Click on this, and one of the options is "Import Calendar". The Google Calendar import function is expecting a comma-separated values (CSV) file. Apparently if you export your Outlook calendar information using CSV format, Google will accept it. (I don't use Outlook, but I'm told it works.) However, you can also create the data manually. In this post I will provide an example of what such a file looks like, and then tell you how I created it.

To enter my son's baseball games, I used a file with the following format (what you see below is the first three lines of the file. You can have as many lines of data/events as you want.):

"Subject","Start Date","Start time","End Date","End time","All day event","Description","Location"
"Game at KWN (Away)",06/27/07,06:00:00 PM,06/27/07,08:00:00 PM,0,"Kenilworth North","Sears School 542 Abbotsford Rd, Kenilworth, IL"
" Game at J1 ( Away)",07/07/07,11:00:00 AM,07/07/07,01:00:00 PM,0,"James #1","James Park, Evanston IL"

The "subject" item shows up under "What" in Google Calendar (the event title), the date and time information under "When", "location" under "Where", and "Description" under "Description".

I haven't done a lot of experimenting, but I tried various things and the format above did work correctly. I haven't been able to find documentation of the import function, so this was based on trial and error and experimenting with a Perl script from this very useful page.

Note that Google calendar automatically creates a map link based on the "location" field.

You can create the example above with a text editor, or in theory, with any program that writes CSV files. I tried to use Excel, but Excel CSV files don't work since Excel doesn't put quotes around text. There is no option to format the files differently. The program that did work is the spreadsheet in OpenOffice, which provides CSV formatting options. Perhaps Excel works, but it didn't work for me when I tried it.

Accessing Google Calendar data offline

In order to use Google calendar you must be online, i.e., connected to the internet. The only required software is a web browser. However, if you install some extra software you can work with your calendar when you are offline.

To view and edit your calendars when you are not connected to the internet, you must have two additional pieces of software. First, you need a calendar application that can read and write iCalendar files. Second, you need software that can synchronize your calendar data files with the Google server. Setting up all of this requires that you edit configuration files and engage in similar Unix-istic behavior. (I hope this will change before long.) Once you have it set up, it is trivial and transparent to use. It is magical to make a change on your laptop calendar and have it show up on Google calendar.

I have been using Mozilla Sunbird as the calendaring application, and GCALDaemon as the connector between Sunbird and Google. I am not going to provide instructions here because the documentation that comes with GCALDaemon is exceptionally clear. (What an admirable piece of software!) I will only suggest that you wait until Mozilla Sunbird 0.5 is released. This should occur very soon. GCALDaemon works with any calendar software that can use iCalendar files. (There are numerous alternatives for Windows, OS X and Linux, but I don't believe you can use Outlook. I could be wrong.)

You should be aware that all of this is beta software. Lots of things can go wrong. Make backups!

If you're not sure what to back up or how to back it up, or how to figure out what and how, you should either wait a while before you try to make this work or else you should be prepared to roll up your sleeves.

If you're asking whether this really works, the answer is yes. My wife and I are both using Sunbird and sharing a set of Google calendars. It's very cool. And my wife has no idea (and more importantly, no need to know) what's happening under the hood.

More on Google Calendars

It turns out that Google's calendaring application has lots of very cool features. I will illustrate a few here. The main point of what I will say, however, is that if you create a calendar, there are lots of ways for it to be helpful to others, even if they don't have a Google calendar account.

1. You can create an HTML link that displays the calendar in any web browser. To see this with the District 65 calendar, click here. (You will see the actual URL after you click. You could easily send this in an e-mail message to a group.) Since the District 65 events don't start until September, click ahead to September to see dates with entries. Be sure to click on the "agenda" tab on the top right to see a list of the events in any given month.



2. You can embed a calendar in a web page. Google has a tool that writes the code to do this.
   



3. You can invite people to a specific event. You supply a list of e-mail messages and Google calendar keeps track of who responds. You can see the list by clicking on the event.



4. For a private calendar, you can decide who sees the calendar and who doesn't, who gets to edit the calendar, and even who gets to decide who sees it. (For all of this to work, everyone involved needs a google calendar account.)



5. You can synchronize Google calendar both ways with Mozilla Sunbird! The trick is to use an open-source program named GCALDaemon. This is not for the faint-hearted or the impatient, but it can be done. I will say more about this some other time.
   

The point is that good-enough shared calendaring is here now. It's easy to use and potentially very useful.

Scheduling is heck

It's time to do something about scheduling. I hope the following will be of help to at least some District 65 parents.

I have created a Google calendar based on the 2007-2008 District 65 calendar (Evanston/Skokie). I may have missed a date here and there; the paper calendar is pretty confusingly presented (come on folks, you can do better than that!). Below I will present some caveats. First, here are directions for accessing the calendar.

1. You need a Google calendar account. If you don't have one, go to calendar.google.com and follow the directions to register. You can use any e-mail address to create a log in.
   
   
2. In the Google calendar screen: On the left hand side, under "calendars", there is an "Other calendars" section. Click on the "+".
   
   
3. Enter "District 65 2007" in the "search public calendars" box. You should then see "District 65, Evanston/Skokie 2007-2008", with a button to add this calendar to your calendar.
   
   
4. Any changes I make to the calendar will then be reflected in the calendars of whoever subscribes.
   
   
5. After you've added the District 65 calendar, click on the "+" again and select "Browse Calendars" in the Add Other Calendar dialog. You'll want to add the US Holidays calendar.
   

The Google calendar is free and does not come with guarantees. They obviously have amazing folks working there, but there is no guarantee that a google-based calendar will not vanish forever due to some technological hiccup. There is no guarantee that it will not be hacked (boy, wouldn't some enterprising 8th grader love to hack Google's servers and create extra vacation days!) There is (to my knowledge) no way to hook a Google calendar into Outlook (but see this page for an ambitious attempt to integrate things).

Having said this, I expect the non-Microsoft world to move towards standards-based calendar integration. Mozilla Sunbird has a Google calendar connector, but Sunbird is still very much a work in progress. I would hope (with Google's CEO on Apple's board and the iPhone coming) that the next edition of Apple's iCal will connect to Google, but this is just a hope.

I hope that by creating and sharing calendars we can begin to attack the enormous inefficiency of everyone keeping identical lists of events.