Historical Options Data File Structures

File Structures

How many files per day

For our end of day service, and for the historical data, there are three CSV files per day. For Bare Bones data the history only includes the Options file.

Options file - one row per option - approximately 620,000 rows each day. (see details below)
Stock file - one row per underlying stock (or Index, ETF) - contains symbol, date, open, high, low, close, volume (not part of Bare Bones)
IVStats - one row per underlying stock - a summary of options data. (see details below) (not part of Bare Bones)

Overview

The most popular purchase from HistoricalOptionData.com is delivered, at its core, in a format known as CSV (Commas Separated Values). Whether it is files from our historical data set, or our end of day service, the files contain multiple rows of data, with each item of data separated from the next by a comma.

Example Classic CSV:
      UnderlyingSymbol,UnderlyingPrice,Exchange,OptionSymbol,Blank,Type,Expiration, DataDate,Strike,Last,Bid,Ask,Volume,OpenInterest,IV,Delta,Gamma,Theta,Vega,Alias
      A,48.74,*,A130921C00050000,,call,09/21/2013,9/16/2013 04:00:00 PM,50,0.13,0.08,0.1,36,726,0.2146,0.1577,19.7203,-2.96,1.3732,
      A,48.74,*,A130921P00050000,,put,09/21/2013,9/16/2013 04:00:00 PM,50,1.51,1.34,1.42,0,266,0.256,-0.7987,19.274,-4.0819,1.601,

Example Bare Bones:
      UnderlyingSymbol,UnderlyingPrice,Exchange,OptionSymbol,Blank,Type,Expiration, DataDate,Strike,Last,Bid,Ask,Volume,OpenInterest
      A,48.74,*,A131019C00050000,,call,10/19/2013,09/16/2013,50,0.71,0.67,0.7,20,2359
      A,48.74,*,A131019P00050000,,put,10/19/2013,09/16/2013,50,1.98,2.04,2.08,37,154

Download Samples

The set of rows are grouped into files, and then in many cases the text files are compressed in “zip” files. Once you receive your files, you can either use them by opening directly in a spreadsheet such as Excel, or import them into your own database.

Calculation

All greek values calculated use the standard Black Scholes model and use the Fed Fund Rate as the interest rate. The Bare Bones has no greek values.

Most Common Structures

Here is a list of the most common formats. Please note that for each example, there is one line for the header row, and one line of data. In most cases, the line will be too long to display on the page and will wrap to the next line. The structures displayed below show our current format which we are using beginning in 2010. End of Day and Historical Data The most common row structure is used in several places. You will most likely encounter files with this file structure first.

underlying, underlying_last, exchange, optionsymbol, blank, optiontype, expiration, quotedate, strike, last , bid, ask, volume, open interest, implied volatility, delta, gamma, theta, vega, alias
MSFT,28.6,*,MSQ100320C00030000,,call,03/20/2010,2/25/2010 04:00:00 PM,30,0.11,0.11,0.12,2738,54203,0.1919,0.1672,18.1826,-0.7533,1.7966,MSQCF

Definitions

Underlying	The stock, index, or ETF symbol
Underlying_last	The last traded price at the time of the option quote.
Exchange	The exchange of the quote – Asterisk(*) represents a consolidated price of all exchanges and is the most common value.
Optionsymbol	The option symbol. Note that in 2010 the format of the option symbol has changed to the longer formatted name.
Optiontype	Call or put
Expiration	The expiration date of the option.
Quotedate	The date and time of the quote. Most of the time, the time will be 4:00 PM. This only means that it is at the close, even though some options trade until 4:15 PM EST
Strike	The strike of the option
Last	The last traded price of the option which could even be from a previous day.
Bid	The bid price of the option
Ask	The ask price of the option
Volume	The number of contracts traded
Open interest	Open Interest – always a day behind. The OCC changes this number at 3:00AM every morning and the number does not change through the day
BELOW THIS LINE, THESE COLUMNS NOT CONTAINED IN BARE BONES PRODUCTS
Implied volatility	The implied volatility (a measure of the estimate of how much the price could change. A high number means that traders believe the option could make a large change)
Delta	The delta. (a measure of how much the option price would change in relation to the underlying stock price. A delta of .50 means the option would change 50 cents for every 1 dollar the stock moves)
Gamma	The gamma. (a measure of how fast the Delta will change when the stock price changes. A high number means this is a very explosive option, and could gain or loss value quickly)
Theta	The theta (a measure of how fast the option is losing value per day due to time decay. As the expiration day arrives, the theta increases)
Vega	The vega (a measure of how sensitive the option price is to a change in the implied volatility. Options that are way out of the money, or have a long time until expiration are more sensitive to a change in implied volatility)
Alias	If possible, the old name of the option. Because of the new OSI Symbology, it is important to know what the old symbol name was. If this can be determined, it will list the old name, otherwise it will display the same value as the option symbol. This is only meaningful for expiration dates prior to 2012.

Daily Option Statistics Files

Statistics are not included in options only, Bare Bones products

For each day with the historical data as well as the end of day subscription, there are three files created for each day; the options file, the option stats file and the stock history file. The option stats file is a summary file for the options data. There is one row of data per each underlying symbol (one per stock, ETF or index). Along with the symbol and the date of the quote, the file also contains summary data concerning implied volatility surface, option volume and option open interest.

A typical row for this data looks like this:
Symbol, date, CallIV, PutIV, MeanIV, CallVol, PutVol, CallOI, PutOI MSFT,20100309,0.2028,0.1991,0.201,82161,31645,1465910,1104266

Symbol	The underlying symbol of the stock, ETF or index
Date	The date of the quote.
CallIV	The surface Call IV.
PutIV	The surface Put IV.
MeanIV	The surface Mean IV.
CallVol	Total call volume for the current day.
PutVol	Total put volume for the current day.
CallOI	Call Open Interest (number of open call contracts) at the beginning of the trading session.
PutOI	Put Open Interest (number of open put contracts) at the beginning of the trading session.

Surface Implied Volatility

Not in Bare Bones product

In order to gauge how expensive or cheap options for a symbol are, a value called the surface IV is calculated. This is the same relationship as the old VIX calculation had to the OEX, in fact, it is the exact same formula.

In brief, for the Call IV, four call options are used in the calculation. The goal of the formula is to estimate the implied volatility of an option for that stock as if it always expired 30 calendar days in the future and its strike exactly matched the underlying stock price. For the Put IV, it is the same thing expect for the puts. The Mean IV is the average of the Put IV and Call IV.

The formula we use is the old VIX formula created by Robert Whaley from Vanderbilt.

Sometimes the values will be zero if there are not sufficient options to make the calculation. There must be an option with a strike below and a strike above the current stock price. This is true for both the front month and the second month. If four options are not available, then we print a zero.

European or American Style expiration?

All optionable stocks and exchange-traded funds, such as SPY and QQQQ have American-style options. All the broad-based indexes, such as SPX, RUT and NDX, are European style. Only the S&P 100 index (OEX, OEF) has American-style options.