Long R, Short Excel

R is very speedy statistical package that’s like an F-18A Hornet, versus Excel which is like a paper airplane. R is professional sports, Excel is Pop Warner. R is Mona Lisa, Excel is stick figures. R is … okay, you get the idea. I’m long R, and short Excel. For traders that are analyzing vasts amount of data, it’s time you made the switch to the trader’s statistical package of choice. You have to work a little at understanding the scripting language, but it’s nothing that you can’t wrap your mind around in a few nights after the kids go to bed. I’m just getting acquainted with R, and I’m already putting my copy of Excel on eBay.

Cut-and-paste versus read-in:

After you’ve generated vast amounts of data from an optimization run, you find yourself with the loathsome task of cutting and pasting the data into an Excel spreadsheet that you’ve probably already pre-formatted to look at the data. It requires you to select the data and not miss any rows, and it opens up the possibility that something gets left out. With R, you don’t touch it once you’ve converted it to a .csv file. You simply reference it in your script.

Manipulating columns of data:

In Excel, you know the drill. Select a cell to compute the mean of a row and then select the elements of a column that you’d like to apply the formula to. When you have over 1,000 rows, it takes a while to drag that stupid mouse. Just as you approach the end of your values, the cursor goes wild and runs down to infinity. Ugh. Well, with R that is no longer an issue since you define the column of data (called a vector) to a script that quickly returns the value you’re looking for.

Add-in packages:

Remember how excited you were to install the Data Analysis pack in Excel and how it gave summary statistics for a column of data that included mean, standard deviation and other stuff you had no idea what they were? Well, that’s a standard part of R. And as you graduate from Statistics 101, you’ll find that there are add-in packages in R that range from neural networks to Bayesian statistics, from genetic algorithms to copulas (side note: the geniuses who created tranched derivatives that came home to roost last year used gaussian copulas, amongst other things).


I think I paid in the neighborhood of $250 for my copy of Excel. I paid nothing for R, since it is open-source and free.

The issue of crashing Windows:

I have crashed my computer by asking Excel to compute too much data. So far, R hasn’t produced the same results. And based on its architecture, I’m not expecting it.

Graphs and dynamic data:

If you want to create a chart or graph of something in Excel, you need to navigate through that stupid menu of crazy pictures to get something that’s clunky and difficult to maintain. In R, you use scripts to create charts and graphs, and the data file feeding the chart is dynamic so that each time data is added, R will incorporate the new data. No offsetting shenanigans.

There are video tutorials available at the usual places you’d find that sort of thing, and a very robust community of R users to help get you started. So put down your toys and get to work. There is much data that needs analyzing.


Tags: ,

5 Responses to “Long R, Short Excel”

  1. Jez Liberty Says:

    I just been through a lot of Excel semi-manual manipulation to analyse data from TradersStudio (which I'll put on the blog soon) so I see where R could become very useful!

    Problem is time (you seem to be very good at making time – I am impressed by how quick you went into this R thing! 😉

  2. Anonymous Says:

    The price is right for R. Be careful with some of the financial related packages as the formulas are not always perfectly correct. It is great to see guys developing packages for Interactive Brokers.

    Also take a look at the Mathematica Home Edition. V.7 is a powerful package.

  3. steveplace Says:

    can you run R off a webserver? i'm assuming so

  4. Milktrader Says:

    Not sure about R and web servers. As you can see, my web programming skills are in their infancy.

  5. Milktrader Says:


    I'm planning a compare and contrast with how Excel and R present backtest and optimization data from TradersStudio. Here is a sample graph I''m working on: http://chart.ly/9d4gz7

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: