Structuring Options Data by Contract for Time Series Analysis in R
Article Quant Q&A · Author: Richard Herron
Summary
The document discusses how to organize option quotes in R when many contracts share each date. One suggested approach creates a separate time series for every contract, identified by underlying, option type, strike, and expiry. Another keeps observations in data frames, joins calls and puts on date, expiry, and strike, then shifts prices and underlying values within expiry-and-strike groups to attach future observations for return calculations.
The examples show how these structures can support matched call-put portfolios and forward price comparisons. The author reports that the grouped data-frame workflow works for the task, while per-contract time series become error prone and longer tests across expiries remain difficult. A functional, mapped data structure is offered as an alternative that can simplify processing and parallelization, though with slower calculations than array-based methods. The discussion is practical rather than a benchmark: it provides no general performance comparison, and the preferred structure depends on the analysis and data scale.
Key ideas
- Option observations can be stored as separate time series for each underlying, type, strike, and expiry.
- A data-frame workflow can join calls and puts on date, expiry, and strike before calculating later prices.
- Future observations should be shifted within contract groups so prices remain aligned with the correct option.
- Mapped functional processing can simplify longer analyses and parallel work, with a potential speed tradeoff.
Tags
Full text
# Is there a technique for using xts or zoo objects with options data (i.e., many entries per date) in R?
# 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)
> head(subset(call.l.new, strike == 615))
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
```
## Answer by glyphard (score 4, accepted)
https://quant.stackexchange.com/a/453
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).
## Answer by Richard Herron (score 5)
https://quant.stackexchange.com/a/564
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).Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.