# Is there a technique for using xts or zoo objects with options data (i.e., many entries per date) in R?

I am starting to work with options data from optionmetrics. I use data frames, but it seems like xts or zoo objects are the way to go for features and speed. I can't figure out the best work-around to get 1 row per date. Should I be doing a list of xts objects with one object per optionid?

Here's my current approach: I am trying to price higher moments by looking at the returns to hedged portfolios of S&P 500 index options, so I really need to match next period's option price with this period's date. I use a data frame with the dates as a Date class and peel off the date, optionid, and option price (boa) columns. I reshape to wide with the optionid as the column names and shift the date back (here I do daily, but it can be any holding period). Then I melt back to long dataframe and merge back on the original data frame. I will give code below.

If I do this for both calls and puts, then I can merge on date, strike, and expiry and form hedged portfolios. This approach works well enough, but it doesn't seem very extensible and probably ignoring a lot of good tools in R. Right now I am just looking at index options, but maybe later I'll be looking at the cross-section, so speed will come in handy.

How do you go about working with options data in R? Thanks!

Here's the code:

> head(call.l)
date     exdate dte optionid strike close   Xms    boa    delta
1 1996-01-04 1996-03-16  72 10003226    600 617.7 -17.7 25.250 0.768930
2 1996-01-04 1996-02-17  44 10016457    570 617.7 -47.7 48.750 0.000000
3 1996-01-04 1996-06-22 170 10019107    595 617.7 -22.7 39.000 0.732626
4 1996-01-04 1997-06-21 534 10050656    700 617.7  82.3 15.750 0.294790
5 1996-01-04 1996-02-17  44 10060564    615 617.7  -2.7 11.000 0.599153
6 1996-01-04 1996-02-17  44 10091463    655 617.7  37.3  0.375 0.046054

temp.l <- call.l[, c("date", "optionid", "boa")]
temp.w <- dcast(temp.l, formula = "date ~ optionid", value_var = "boa")
temp.w <- temp.w[ order(temp.w[, "date"]), ]
temp.w.shift <- tail(temp.w, -1)
temp.w.shift[, "date"] <- head(temp.w[, "date"], -1)
temp.l.shift <- melt(temp.w.shift, id.vars = "date", variable.name = "optionid", value.name = "boa.fut", na.rm = T)
call.l.new <- merge(call.l, temp.l.shift)

date optionid     exdate dte strike  close   Xms    boa    delta boa.fut
5   1996-01-04 10060564 1996-02-17  44    615 617.70 -2.70 11.000 0.599153 10.6250
43  1996-01-04 10372012 1996-03-16  72    615 617.70 -2.70 15.000 0.593709 14.6250
80  1996-01-04 10823593 1996-01-20  16    615 617.70 -2.70  7.750 0.597414  6.0000
144 1996-01-05 10060564 1996-02-17  43    615 616.71 -1.71 10.625 0.577407 12.2500
182 1996-01-05 10372012 1996-03-16  71    615 616.71 -1.71 14.625 0.578089 16.2500
219 1996-01-05 10823593 1996-01-20  15    615 616.71 -1.71  6.000 0.584558  7.0625

-
First R question here, I think. Please let me know if SO is better for this, but it seems particularly quant finance focused. I had a dput but it was huge; please let me know if it would help. Thanks. –  Richard Herron Feb 9 '11 at 16:00
I think that it's perfectly acceptable here. –  Shane Feb 9 '11 at 16:04
Related: Jeff Ryan, who wrote xts, also wrote indexing and mmap so ... that he could use lightning fast indexing (think KDB or OneTick) directly in (binary) R data structures. He presented about that at last year's R/Finance conference but used a 'movie' on his macbook to show the speed, so the slides don't fully do it justice. –  Dirk Eddelbuettel Feb 9 '11 at 16:14
@Dirk -- Thanks for the lead. I keep the larger data set in an SQLite db and used to do this self-join SQLite. It was faster, but the coding was tedious and when it came to doing greater than one day returns it was really easy for me to make mistakes because of limited date support in SQLite. indexing looks really promising. –  Richard Herron Feb 9 '11 at 20:15
My question would be: is it better to encode option prices as "chords" (a specific chain at each timepoint) or "melodies" (independent timeseries at each strike) ? You'd want some obvious relations to be built into the object...(eg, check to make higher strike at same time is more expensive; derive a comparison of same strike at two timepoints) –  isomorphismes Jan 1 '13 at 21:10

I haven't seen a framework for options specifically, however... The way I have done this in the past is to essentially setup a timeseries(xts or zoo) for each option(underlying,type,strike,expiry). Obviously doing this via code is important because it is intensely error prone.

We use a build function to put those into the workspace. It is still difficult and brittel to do longer tests that span multiple expiries.

We eventually gave up on R and matlab in favor of a functional programming approach. This way as we evaluate the code scans a mapped structure, instead of an array.

Clearly this is slower, but really simplifies the programming, and is easily parallized. It performs reasonably well on a live data feed. Probably not tractable for HFT ( calcs are in ms, not microseconds).

-
Thanks. I think that is the approach I will use. My approach above (one wide data frame) introduces a lot of NAs because, unlike equities, none of the options run the entire sample. A list of data frames works much better. The plyr package makes this really easy and so far has given me a big time saving over my first solution above. –  Richard Herron Feb 14 '11 at 15:03

FWIW, here's the approach I used. I keep the dates as an integer in YYYYMMDD form and merge the calls and puts in to a data frame both. Then I use ddply to operate on each matched call and put to find the future SPX close and call/put bid-offer average boa.

library(plyr)
both <- merge(calls, puts[, c("date", "exdate", "strike", "boa", "delta", "vega")], by = c("date", "exdate", "strike"), suffixes = c(".calls", ".puts"), sort = T)


Which gives:

> head(both)
date   exdate strike  close    Xms boa.calls delta.calls vega.calls
1 19960104 19960120    490 588.97 -98.97   100.375    0.965229   8.954686
2 19960104 19960120    495 588.97 -93.97    95.375    0.964108   9.199321
3 19960104 19960120    510 588.97 -78.97    80.375    0.960100  10.064710
4 19960104 19960120    515 588.97 -73.97    75.375    0.958478  10.407010
5 19960104 19960120    525 588.97 -63.97    65.500    0.949837  12.130740
6 19960104 19960120    530 588.97 -58.97    60.625    0.942449  13.559780
boa.puts delta.puts vega.puts
1  0.03125  -0.002597  0.960498
2  0.03125  -0.002725  1.003923
3  0.09375  -0.007769  2.548534
4  0.12500  -0.010310  3.279840
5  0.28125  -0.021612  6.162532
6  0.28125  -0.023069  6.512128

both <- transform(both, date.ym = date %/% 100, exdate.ym = exdate %/% 100, exdate.d = exdate %% 100)
both <- ddply(both, .variables = c("exdate", "strike"), .fun = my.shift, direc = "fut", value.col = c("close", "boa.calls", "boa.puts"), .parallel = in.parallel)


Which gives:

> head(both)
date   exdate strike    close      Xms boa.calls delta.calls vega.calls
1 19960109 19960120    565 582.7998 -17.7998  18.50000    0.953215   9.323605
2 19960109 19960120    575 582.7998  -7.7998   9.06250    0.834628  23.915020
3 19960109 19960120    580 582.7998  -2.7998   6.06250    0.626327  36.502140
4 19960109 19960120    585 582.7998   2.2002   3.81250    0.445721  38.105320
5 19960109 19960120    590 582.7998   7.2002   2.06250    0.287108  32.854870
6 19960109 19960120    595 582.7998  12.2002   1.03125    0.166120  24.049020
boa.puts delta.puts vega.puts date.ym exdate.ym exdate.d close.fut
1   3.1875  -0.220331  28.53901  199601    199601       20    581.99
2   5.1875  -0.345956  35.53591  199601    199601       20    581.99
3   7.0000  -0.432961  37.89640  199601    199601       20    581.99
4   9.6250  -0.525538  38.34777  199601    199601       20    581.99
5  12.8750  -0.610547  36.90346  199601    199601       20    581.99
6  17.1250  -0.671490  34.82305  199601    199601       20    581.99
boa.calls.fut boa.puts.fut
1      17.62500      0.34375
2       8.06250      1.12500
3       4.43750      2.40625
4       1.84375      4.93750
5       0.71875      8.93750
6       0.28125     13.37500


Where the my.shift function is:

my.shift <- function(x, date.col = "date", value.col = NULL, steps = 1, direc = NULL) {

if (direc == "fut") {
x.shift <- tail(x[, c(date.col, value.col)], -1 * steps)
x.shift[, date.col] <- head(x[, date.col], -1 * steps)
} else {
x.shift <- head(x[, c(date.col, value.col)], -1 * steps)
x.shift[, date.col] <- tail(x[, date.col], -1 * steps)
}

x <- merge(x, x.shift, by = date.col, suffixes = c("", paste("", direc, sep = ".", collapse = "")))
return(x)
}


HTH (I use the .ym and .d columns later for picking the right time to expiry).

-