The Berkeley Options Data Base

Institute of Business and Economic Research #1922

University of California, Berkeley

Berkeley CA 94720-1922

Release 3.0

Revised August 1998

The Berkeley Options Data Base (BODB) is a complete record of trading activity on the floor of the Chicago Board Options Exchange (CBOE). Derived from the CBOE's Market Data Retrieval (MDR) tapes, the database contains every bid-ask quote and every trade recorded on the floor of that exchange, time-stamped to the nearest second. The database begins on August 23, 1976 and is updated annually, usually in March or April. At the time of this printing (June 1, 1995), the database is available through December 31, 1994.

This document is intended to be a comprehensive user's guide to the Berkeley Options Data Base. It describes the database in detail, and contains valuable information on how to acquire, access, and conduct research using the database. As the database is currently designed to be accessed through a unix workstation, specific technical details are offered only for the unix platform. For use on other systems, it is necessary to find a utility that can read a unix tarfile. If you have any further questions about the database, or comments about this guide, please contact the database manager.

The Market Data Retrieval (MDR) file is produced by the CBOE, and constitutes a complete record of bid-ask quotes and trades recorded on the floor of that exchange. Each record is time-stamped to the nearest second, and contains the contemporaneous price of the underlying security.

The entire MDR may be purchased from the CBOE, by the month, on 6250 bpi magnetic tapes. As of January, 1995, the rate was $500 per month of data, with a 10% discount on orders of six months or more, and a 15% discount on orders of one year or more. The MDR data for S&P 100 (OEX) index options may be purchased separately at a price of $450 for three years of data. Data for S&P 500 (SPX) index options may be purchased at the same rate. Other individual securities from the MDR may be purchased at the rate of $100 per month for the first security and $30 per month for each additional security.

The CBOE also offers various summary files. The ``Option Summary'' file contains daily high, low, and closing prices, trading volume and open interest, since October 1, 1985. One year of the Option Summary file for up to ten securities may be purchased for $125 on 6250 bpi tapes or $100 on floppy disk or hardcopy. The ``Expanded Option Summary'' file contains all the information in the Option Summary file plus the underlying stock price. One year of the Expanded Option Summary file for up to ten securities may be purchased for $150 on 6250 bpi tapes or $125 on floppy disk or hardcopy. The ``Index Summary'' file contains daily information for the various indexes on which options are traded at the CBOE, including the daily high, low, and closing index value, the change from the previous close, total trading volume on calls, total trading volume on puts, total open interest on calls, and total open interest on puts. The Index Summary file may be purchased at the rate of $25 per index on floppy disk, in which case data are available beginning October 1, 1985, or $15 per index hardcopy, in which case the data are available beginning March 11, 1983.

The MDR contains four main types of records: trade records, quote records, cancel records, and underlying records. Quote records contain bid and ask prices, while trade records contain transaction price and volume. Cancel records, as the name indicates, cancel previous records on the same underlying contract. Trade, quote, and cancel records are all time-stamped, and contain a contemporaneous observation of the underlying stock price. Underlying records contain information about the underlying stock that is recorded on the MDR without a trade, quote, or cancel having occurred.

Some quote records are recorded on the floor of the exchange by a Quote Reporting Terminal Operator, who enters bid-ask quotes as they are shouted in the trading crowd. The reporting lag for quotes should be very short, only as long as is required for the terminal operator to enter the option identification and the quote, which should be less than five seconds. In addition, many options are now quoted through the ``autoquote'' system. In this case, a market maker chooses the input parameters for a Black-Scholes or Cox-Ross-Rubinstein pricing model, and bid-ask quotes are automatically updated by computer whenever the underlying stock price changes. The autoquote system has led to a large increase in the volume of data recorded on the MDR over the last few years. A large portion of the recent MDR data is made up of quotes on index options, where the underlying index is recalculated every 15 seconds, and the autoquote system continually spits out fresh quotes.

Trade records are recorded by a Price Reporting Terminal Operator. The reporting delay for trades may be considerably longer than for quotes. After a verbal agreement to a trade has been consummated between two members of the trading crowd, the seller writes up the trade on a blank ticket he is carrying, and deposits a copy of the sell ticket on a conveyor belt at the post. This process generally takes from 5 to 40 seconds, depending on the number of traders involved, how fast they write, and how far they are from the conveyor belt. When trading is particularly active, traders might hold onto these tickets for up to several minutes before depositing them on the conveyor belt. Upon receiving the ticket, the Terminal Operator immediately removes the ticket from the bin, with a single key stroke simultaneously enters the stock symbol, expiration month, and strike price, and then separately enters the number of contracts traded, the transaction price, and the identifying symbols of the buying and selling floor traders.

The MDR may be acquired from the CBOE on standard or non-labelled 6250 bpi magnetic tapes. The Berkeley Options Data Base receives the MDR on non-labelled tapes, on which the MDR is stored as a large fixed-length EBCDIC file, on multiple tapes, with record length 61 and blocksize 32757. Because the file is stored as a multiple-tape file, each MDR tape is crammed full of data. On a unix system, with 9-track tape drive designated /dev/rst1, the data may be transferred to hard disk using the command:

dd if=/dev/rst1 of=mdrdata bs=32757 cbs=61 conv=ascii

The if= option specifies the input file or device, the of= option specifies the output file, bs designates the blocksize, conv instructs the program to convert the data (in this case from EBCDIC to ASCII), and cbs (conversion buffer size) tells the program to split the file into lines as it converts it. Note that the cbs option only works when the conv option is specified.

Within the MDR, records are sorted first by date, then by underlying security. Within each underlying security calls are listed before puts. The calls (puts) are sorted in order of expiration month, within each expiration month by strike price, and within each strike price the records are listed chronologically. In summary, records are sorted according to the scheme

DATE : UNDERLYING ASSET : CALL/PUT : EXPIRATION : STRIKE : TIME

Here are a few sample lines from the MDR file:

920922GPSOG142911000000000000040000040040003320102 PMAR035

920922GPSOG143914000000000000040000040120003310102 PMAR035

920922GPSOG144049004004000050000000000000003310101 PMAR035

920922GPSOH083237000000000000070120070240003330102 PMAR040

920922GPSOH083602000000000000070160070280003330102 PMAR040

920922GPSOH083651000000000000070120070240003330102 PMAR040

920922GPSOH084614000000000000070080070200003340102 PMAR040

920922GPSOH085117000000000000070120070240003350102 PMAR040

920922GPSOH085611000000000000070080070200003350102 PMAR040

These transactions were recorded on September 22, 1992. This can be ascertained from the first six characters of each line [1-6], which contain the date in the form YYMMDD.

Between the date and time are five characters [7-11] containing a ticker symbol that completely identifies the option contract.Note that only the first three of these characters appear in the Berkeley Options Data Base.

The first three letters are unique to the underlying asset. One or two of these characters may be blank. In the example above, the letters GPS signify that these are equity options on Gap Stores stock. For options on stocks traded on the New York Stock Exchange or the American Stock exchange, these three letters will generally be the same as the underlying stock market ticker symbol. For options on NASDAQ stocks, the option ticker usually contains two letters from the NASDAQ ticker (not necessarily the first two), plus the letter Q. So, for example, NASDAQ ticker symbol AAPL might become AAQ. Other ticker symbols indicate index options, LEAPS, interest rate options, or other types of options. For more details, see the section on ticker symbols, below.

The fourth letter of the ticker symbol identifies the contract type and expiration date. The letters A through L designate call options expiring in January (A) through December (L). The letters M through X represent put options expiring in January (M) through December (X). In this example, the letter O designates a March put option.

The fifth letter identifies the last two digits in the option's strike price. The letter A signifies a strike price ending in 05, B is a strike ending in 10, and so on. In the example above, the first three records are for options with a strike price ending in 35 (G), and the last six are for options with a strike price ending in 40 (H).

Immediately to the left of these last seven characters is a six-character field [49-54] including a numeric record type,(indicating whether it is a trade, quote, cancel, or underlying record) and on occasion a four-letter ``Prefix'' code, indicating additional information about the record. The four record types are:

For trade records, the transaction price is recorded at [18-23], with the integer portion of the price recorded in the first three bytes [18-20] and the fractional part, recorded in thirty-seconds of a dollar, in the next three

The Berkeley Options Data Base (BODB) is associated with the Institute of Business and Economic Research at the University of California, Berkeley. Through a contractual arrangement with the CBOE, BODB offers a reprocessed

version of the Market Data Report, beginning August 23, 1976. The database is updated annually, and is currently available through December 31, 1994. The database is managed by a graduate student in the finance group at the Haas School of Business, under the direction of professor Mark Rubinstein. At the time of this printing, (August 17, 1998) the current database manager is Kehong Wen. You may contact BODB via e-mail to options@haas.berkeley.edu, by phone at (510) 643-8893, by fax at (510) 642-5018, or by mail to the Berkeley Options Data Base, Institute of Business and Economic Research MC #1922,

The Berkeley Options Data Base is available on 8mm magnetic tapes, sometimes known as ``Exabyte'' tapes. For data prior to 1990, one year of data are stored on a single 8mm tape. Beginning with 1990, six months of data are stored on a single tape. To access the data, it is necessary to use the unix ``tar'' (tape archive) utility. In addition, at least 200 megabytes of hard disk space are required. Normally, the tapes are written using an Exabyte 8500 drive, and cannot be read by the (lower-density) Exabyte 8200 drive. However, the tapes may be written at low-density through special arrangement with the database manager.

Due to the increasing volume of data, data beginning in January 1994, will be compressed using the {bf gzip} compression facility.

Plans are underway to also offer the database on 4mm ``dat'' tapes and eventually, CD ROM. Through special arrangement, the data may also be purchased through the internet via ftp. The database is no longer available on 6250 bpi 9-track tapes.

The Berkeley Options Data Base may be purchased at the rate of $200 per month of historical data, with a minimum purchase of six months. In addition, there is a processing charge of $80 per 8mm tape.

Customers in California must also pay sales tax. Academic institutions may acquire the data at a special rate of $150 per month. Academic institutions qualify for a volume discount rate of $120 per month if they have cumulatively purchased 36 months of data. BODB does not currently offer subsets of the database. If you want more than one copy of the tape, for example if one of your tapes is lost or damaged, there is a processing charge of $80 per tape.

The BODB is stored in fixed-length ASCII files, archived in unix tarfiles on 8mm tapes. To restore a file onto hard disk on a unix system where the 8mm tape drive is designated /dev/rst5, place the tape in the drive, make sure there is more than 161 MB of disk space in the current partition, and issue the command

tar xvf /dev/rst5 filename

where filename is the name of the file you wish to restore. To list all the files on a tape, issue the command

tar tvf /dev/rst5

This might take as long as two hours. If the filenames end with a .gz suffix, they have been compressed, and must be uncompressed using the publicly available program gunzip.

Prior to December, 1993, the filenames on the tape are of the form resXXX, and are the same as the names of the 6250 bpi tapes on which the data were previously stored. The first file (from 1976) is res01, the December, 1993 file is res227, and the file numbers in between are consecutive. The only anomalies are the files for August, September, and October, 1987 (the first three months that originally required more than one 6250 bpi tape), which are named res98A and res98B, res99A and res99B, res100A and res100B. For a complete listing of filenames, see Appendix B. Beginning in January, 1994, filenames are of the form resYYXXX, so the first file in 1994 is res94001, and the last is res94046.

The Berkeley Options Data Base is currently available only in the ``resorted'' format. It is called the resorted format because it is little more than a resorted version of the MDR. The processing program alters certain fields of the MDR records to make them easier to interpret, performs a few screens for bad or duplicate records, and changes the sorting order. While the MDR is sorted according to five-letter ticker symbol, BODB is sorted according to three-letter ticker symbol. This means that in the BODB, all the day's records on the same underlying stock are ordered chronologically, regardless of expiration or strike, unlike the MDR, where records are further sorted by option contract.

In summary, the BODB is sorted according to the scheme

DATE : UNDERLYING ASSET : TIME

Beginning in January, 1994, records occuring in the same second are further sorted according to record type and option contract. The new sorting scheme is:

The resorted data are contained in a fixed-length file with the following

(undelimited) fields:

Here are a few sample records from the Berkeley Options Data Base:

43IBM930104084001 1-08500033500337505140

2IBM930104084014 2 04500007500080005140

1IBM930104084021 1-05500003880000305140

2IBM930104084021 2-04500001250013105140

2IBM930104084034 2 05000004000042505140

1IBM930104084038 2 05000004000000405140

2IBM930104084040 2-05000002880031305140

1IBM930104084044 1 05500000630004105140

2IBM930104084052 2 05500001750018105140

1IBM930104084053 1 05500000630001005140

In the Berkeley Options Data Base, the underlying ticker symbol, which is located in field [3-5], is copied directly from the first three characters of the MDR ticker symbol. As mentioned above, this ticker usually corresponds to

the stock exchange ticker symbol for NYSE and AMEX stocks, but not for NASDAQ stocks. More details on ticker symbols are given in the ``Research Guide''

Instead of using the CBOE's alphabetic expiration codes, the Berkeley Options Data Base reports the expiration month in numeric form in characters [18-19].

Because the MDR trade fields are always empty for quote records, and its quote fields are empty for trade records, the BODB is able to save space by recording both fields in the same location. For trade records, the price is recorded

at [26-30] and contract volume is recorded at [31-35]. For quote records, the bid price is recorded at [26-30] and the ask price is recorded at [31-35].

For most ticker symbols, the Berkeley Options Data Base contains an exact copy of the last four digits of the MDR's underlying price field, plus an additional zero as a place-holder. Thus, the price of IBM in the above records

was $51 1/2 (05140). You may notice that some of the previous BODB documentation

describes this field as being recorded in dollars and cents. This is no longer correct--beginning with the January, 1986 data, the field is recorded in dollars and eighths for equity options.

For index options such as the OEX and SPX series, the BODB contains an exact copy of the first five digits of the MDR's underlying price field, so that an OEX value of 455.31 would be recorded as 45531. Not all underlying values for index options are recorded correctly in release 2.01 of BODB (January 1986--December 1993). In some cases, such as the SPZ overflow ticker, the version 2.01

processing program mistakenly treated the MDR record as an equity option. Consequently, the BODB contains only the last three digits of the underlying price. An SPZ value of 415.65, for example, was mistakenly recorded as 56500.

This problem has been corrected in the new version 3.0 program, so data beginning in January, 1994 are fine.

As changes occur in the MDR tapes, or as errors are discovered in the data, new versions of the BODB are sometimes released. BODB does not guarantee the accuracy of the data, and has no responsibility to replace old data when a new version is released. However, under our current policy we will replace data for

anyone who has purchased our data, at any time, and for any reason. The replacement charge is $80 per 8mm tape. Unfortunately, we can no longer sent replacement data on 9-track tapes.

Version numbers for the BODB correspond to the processing program used to convert MDR data to BODB format. It is costly and difficult for us to go back and process old MDR data with a new processing program. Consequently, different periods of the database were processed using different programs, and there are slight format differences. The current processing program, version 3.0 is written in PERL and runs under a sunOS implementation of unix system V release 4. Currrently, the version 3.0 release begins in January, 1994. Unlike previous versions, version 3 does not alter record types to indicate an underlying stock split.

Moving to the unix platform involved a lot of new programming, and we are still debugging the processing program. We are aware of one error in version 3.0: there are a few extra unidentified control records in the MDR data, which should have been discarded but were included in the BODB with no record type or with type 4. These records contain no useful information and should be disregarded. If you find any other problems with the data, please contact the database manager.

The processing program for Version 2.1, which is the current release from January 1986 through December 1993, was written in FORTRAN and REXX, and operated under VM/CMS. This and previous versions altered the record type for options whose specifications had been modified because of an underlying stock split. The procedure for affecting this modification was imperfect, however,

and not all splits were correctly specified. Rather than continuing to provide faulty split information, we elected to discontinue this practice. To obtain correct information on stock splits, you must contact the exchange and obtain the memo corresponding to the split. For more information, see the section on stock splits, below. Another problem with version 2.1 was its incorrect treatment of underlying prices for index options, descibed in the ``underlying asset'' section above. Plans are underway to reprocess this data under version 3.

Version 2.0, which covers December 1979 through December 1985, differs from version 2.1 in that (1) the usused portion of each numeric field is filled

with blank spaces instead of zeros, and (2) underlying stock prices are recorded in dollars and cents instead of dollars and eighths. Starting September 30, 1985, the market began opening at 8:30 instead of 9:00. In version 2.0, time stamps for the first half hour of trading were incorrect: transactions occuring X seconds before 9:00 A.M. were mistakenly recorded as having occurred X seconds

after 9:00 A.M. This problem only exists from September 30, 1985 through

December 31, 1985.

Until 1987, BODB offered a condensed version of the MDR file, called the consolidated format. Instead of reporting each individual record, this format summarized all trades and quotes occurring within each block of time during which the underlying asset price did not change. Each record contained the date, ticker, strike price, and time to expiration, a beginning and ending time, the underlying stock price, high and low quoted prices, a summary of all trades during the period, information on the stock prices preceding and following the record and the approximate elapsed time between these price changes, and the number of original transaction records from which the consolidated record was created. The consolidated format was discontinued due to lack of interest.

All the information in the consolidated format may be derived from

BODB formerly offered a ``supplemental'' tape which included several daily interest rate series, dividend information on all CBOE underlying stocks, and daily closing levels of stock market indexes. This service was discontinued due to high maintenance costs. Dividend information is available from CRSP. Interest rates may be obtained from the Wall Street Journal, or else implied interest

rates may be calculated from the prices of S&P 500 index options using the put-call parity relationship. Daily index information may be purchased from the CBOE.

Transactions data is ideal for empirically testing market-microstructure models, and extremely useful for any type of research investigating bid-ask spreads, order flow, trading volume, price discovery and the lead-lag relationship between options and their underlying stocks, price discreteness, or intraday dynamics. It is also very useful in more traditional asset pricing, in that it can be used to measure the biases introduced by microstructural frictions.

In options markets, it is particularly important to recognize the severe problems associated with asynchronous data. Since option prices are so sensitive to the underlying asset price, unobserved intraday movements in the underlying price will render asynchronously recorded closing prices incomparable. In fact, it is not uncommon to observe apparent static arbitrage opportunities among reported closing prices. Results of studies based on closing option prices without correcting for changes in the contemporaneous stock price are often viewed as highly suspicious, and are unlikely to be accepted for publication.

The best way to identify option ticker symbols is to contact the options clearing corporation and obtain their pamphlet ``Directory of Exchange Listed Options.'' This pamphlet contains the ticker symbols for options traded

on the CBOE, as well as those trading on the New York, American, Philadelphia, and Pacific stock exchanges. The pamphlet may also be obtained through the CBOE, and is also available, for a fee, in electronic form.

The New York and American Stock Exchanges use three-letter ticker symbols to identify securities. Options on stocks traded on these exchanges generally use the same three-letter symbol. NASDAQ ticker symbols are longer than three letters, and for these stocks, options are traded using a ticker symbol consisting of two letters from the NASDAQ ticker plus the letter Q.

Index options are normally given a three letter ticker symbol ending in X, with the first two letters chosen to describe the underlying security. For instance, the ticker symbol for the S&P 500 index is SPX, And the ticker symbol for the S&P 100 index, originally known as the CBOE 100 index, is OEX.

Because options are traded using a single letter to designate the option's strike price, there is a limit to the number of different strike prices that may be assigned to a single ticker symbol. For the most popular option contracts, such as the SPX and OEX indexes, it became necessary to assign a second ticker symbol to accommodate the wide range of strike prices. Secondary ticker symbols are usually created by changing the last letter of the ticker symbol to Z. Ticker SPZ serves as the overflow symbol for SPX, and OEZ is the overflow for OEX. Sometimes, additional option classes are added that are similar to an existing class, and these are assigned ticker symbols that are as descriptive as possible. For example, options on the SPX index that expire at the end of each

quarter rather than the customary dates were given the ticker symbol SPQ.

Options with long-term expiration dates, known as LEAPS, are also assigned their own ticker symbols, which resemble the original tickers. LEAPS are traded with one expiration date per year, either in December or January depending on whether they are Equity or Index LEAPS. Generally a letter is chosen to designate all LEAPS expiring in the same year, such as ``V'' for 1995 or ``L'' for 1996,

and each LEAPS ticker symbol is created, if possible, by substituting this letter for one of the letters in the original ticker. For example, the IBM 1995 LEAPS are traded under the ticker symbol VIB, and IBM 1996 LEAPS under the symbol LIB. Sometimes the letters have to be twisted around to avoid duplicating

another ticker symbol. Ticker symbols for some Index and Equity LEAPS are listed in Appendix C. You may wish to contact the exchange for a more current list.

Nearly all options expire on the Saturday following the third Friday of the expiration month. One exception are the S&P 500 end-of-quarter options (SPQ). Equity options are listed for the nearest two exercise dates, plus the next two

For example, at the beginning of April, a January-cycle stock will have options expiring in April, May, July, and October, a February-cycle stock will have options expiring in April, May, August, and November, and a March-cycle stock will have options expiring in April, May, Jun, and September. On the Saturday following the third Friday of April, the April options expire, and a new set of contracts is introduced (June contracts are opened for January-cycle and February-cycle stocks, and December contracts are opened for March-cycle stocks).

Some index options, such as SPX, are assigned to expiration cycles like equity options. Others, such as the OEX, are always traded for the closest four expiration dates. In addition, long-term options with maturities of up to

Organized exchanges are usually required to keep a permanent record of all their transactions. Many of these exchanges have made their transactions data available to the public, or at least to academics for research purposes.

In general, the only way to acquire this data is directly from the exchange, usually on 9-track tapes.

Prior to January, 1993, transactions data for stocks traded in the United States were available on 9-track magnetic tapes from the Institute for the Study of Securities Markets (ISSM). Beginning in January 1993, the New York Stock Exchange instituted the ``Trades And Quotes'' (TAQ) database, containing trades and quotes for all stocks on the New York and American Stock Exchanges, as well as the Regional exchanges and NASDAQ. The TAQ database comes on PC-readable CD-ROM, with built-in access programs that run under DOS. The FORTRAN source code is included for these access programs. The data may be purchased from the New York Stock Exchange for $200/month, with each month stored on a separate CD.

The Berkeley Options Data Base contains some data on currency options, which were traded on the CBOE during part of 1985 and 1986. European-style options were traded on the Japanese Yen, Deutchemark, British Pound, Swiss Franc, and Canadian Dollar. Due to the low trading volume on these contracts, the CBOE sold them to the Philadelphia Stock Exchange (PHLX), where they are still traded.

American-style currency options are also traded at the PHLX. For a charge of $75, academic researchers may purchase either a daily summary database on 30 floppy disks or a transactions database on one 9-track magnetic tape. The transactions database begins in 1984, and contains only trades, not quotes.

American Stock Exchange

Derivative Securities

86 Trinity Place

New York, NY 10006

1-800-THE-AMEX

Chicago Board Options Exchange

LaSalle at Van Buren

Chicago, IL 60605

1-800-OPTIONS

(312) 786-5600

New York Stock Exchange

Options and Index Products

11 Wall Street

New York, NY 10005

1-800-692-6973

(212) 656-8533

The Options Clearing Corporation

440 South LaSalle Street

Suite 2400

Chicago, IL 60605

1-800-537-4258

(312) 322-6200

Pacific Stock Exchange

Options Marketing

115 Sansome Street, 7th Floor

San Francisco, CA 94104

1-800-TALK-PSE

(415) 393-4028

Philadelphia Stock Exchange

1900 Market Street

Philadelphia, PA 19103

1-800-THE-PHLX

(215) 496-5404

Chicago Board of Trade

Market Data Services [Larsenia R. Williams]

141 W. Jackson, Ste. 2313

Chicago, IL 60604-2994.

(312) 341-3163

Chicago Mercantile Exchange

Records Management [Andr'e Gibson]

30 South Wacker Drive

Chicago, IL 60606-7499

(312) 930-3178

If you are using a unix system, it is very easy to extract a list of ticker symbols contained in a BODB file. To create a new file ticklist from BODB file res227, containing an alphabetical list of all ticker symbols in that file,

simply type the following at the unix command line:

cut -c 3-5 res227 | sort -u -o ticklist &

Suppose you are not interested in all the data fields, but only wish to extract a few fields. You can accomplish this from the unix command line using the cut and paste utilities. For example, suppose that you only want the ticker symbol, date, time, and stock price. Because the ticker, date, and time are adjacent, you can cut them out and place them in a separate file with a single command,

cut -c 3-17 res227 > firsthalf

then cut out the stock prices with a similar command,

cut -c 35-40 res227 > secondhalf

and glue them back together in a single file:

paste firsthalf secondhalf > outfile

The simplest way to extract records from a BODB file is using a computer program that reads the file one line at a time, checks to see whether the line meets the selection criteria, and if so, writes the line to an output file. Simple jobs, such as extracting all records for a single ticker symbol, can easily be handled using built-in unix utilities such as grep. For example, to extract all OEX records from BODB file res193 and place them in a new file called oex193,

simply type

grep OEX res193 > oex193

at the unix command line. When extracting one- or two-letter ticker symbols

using grep, you may accidently extract unwanted symbols. For example, if you were to try grep T res193, you would not only extract ticker symbol T but also every other ticker symbol containing the letter T. To get around this problem, you must tell the grep program to look for the string T preceded by a number from 0 to 9 and followed by a space. How you specify this may depend on the syntax for regular expressions in your unix shell. In the sh shell, issue the command

grep [0-9]T[verb+ +] > t193

The grep command is limited to string comparison, and is adequate only for the simplest extraction problems. For more complicated extraction criteria, you may wish consider using a unix programming language such as sed, awk, or perl, an SQL or other database package, or a compiled language such as FORTRAN or C.

Following is a template extraction program in C. The program takes, as input from the command line, an input file name, a starting record number, and an ending record number. It reads the designated records from the input file,

and outputs them to standard output. You may insert whatever selection criteria you wish, setting the variable keepdummy=1 if you wish to output the record.

To read the entire input file, you may either modify the program or run the program with beginning record number 1 and an ending record number larger than the number of records in the file.

/* Extractor

C Program to Extract Records from the Berkeley Options Data Base

Copyright (C) Stewart Mayhew, March 1995. */

#include <string.h>

#include <stdlib.h>

#include <stdio.h>

/* Main Function Begins Here */

main (int argc, char **argv)

{

char buf[256];

FILE *fp;

char record[60];

long date,thisdate=0,count,ibegin=1,iend;

long offsetnum, numrecs, beginp, endp;

int keepdummy;

/* Check for proper input */

if (argc != 4) {

printf("usage: %s <input file> <first record> <last record>n"

, argv[0]);

exit(0);

}

if ((fp = fopen(argv[1], "r")) == NULL) {

printf("Can't Find file %sn",argv[1]);

exit(0);

}

beginp=atol(argv[2]);

endp=atol(argv[3]);

offsetnum=(beginp-1)*41;

fseek(fp,offsetnum,SEEK_SET);

numrecs=endp-beginp+1;

/* Loop Begins Here */

for (count=1;count<=numrecs;count++) {

if (fgets(buf,256,fp)==NULL)

exit(0);

keepdummy=0;

/* Read Record */

sscanf(buf, "%41c%",

record);

record[41]='0';

/* ..........Insert Search Criteria Here...........

Set keepdummy=1 to output the record */

/* Write Record to Standard Output */

if (keepdummy) printf("%s",record);

} /* End of Loop through records */

fclose (fp);

return(0);

}

Both grep and the Extractor template program will tend to be slow, because they have to read each record sequentially. A single BODB file may contain up to 4 million records, and since you will probably be running the program multiple times on different files, it may take quite a long time to extract all the data you need. In the long run, you can save a lot of time by creating an index that stores information about where the different ticker symbols are located within the file. You will have to read the data sequentially to create the index file, but once created it will dramatically decrease extraction time.

Following is a program that reads a BODB file and writes an index to standard output. The index is simply a list of beginning and ending record numbers for each date/ticker symbol combination.

/* MakeIndex

C Program to create an index for a BODB file

Copyright (C) Stewart Mayhew, March 1995 */

#include <string.h>

#include <stdlib.h>

#include <stdio.h>

#include <math.h>

/* Main Function Begins Here */

main (int argc, char **argv)

{

char buf[256];

FILE *fp;

char rtype[10];

char ticker[10];

char thisticker[10];

char datebuf[10];

long date,thisdate=0,count,ibegin=1,iend;

/* Check for proper input */

if (argc != 2) {

printf("usage: %s <input file> n", argv[0]);

exit(0);

}

if ((fp = fopen(argv[1], "r")) == NULL) {

printf("Can't Find file %sn",argv[1]);

exit(0);

}

/* Loop Begins Here */

for (count=1;;count++) {

if (fgets(buf,256,fp)==NULL)

break;

/* Read Record */

sscanf(buf, "%2c%3c%6c",

rtype, ticker, datebuf);

rtype[2]='0';

ticker[3]='0';

datebuf[6]='0';

date=atol(datebuf);

/* Check to see if there is a new ticker. If so, output index info. */

if ((strcmp(ticker,thisticker)!=0) || (date != thisdate)) {

iend=count-1;

if (iend>0)

printf("%ld %s %ld %ldn",thisdate,thisticker,ibegin,iend);

ibegin=count;

strcpy(thisticker,ticker);

thisdate=date;

}

} /* End of Loop through records */

if (count>=1) {

iend=count-1;

if (iend>0)

printf("%ld %s %ld %ldn",thisdate,thisticker,ibegin,iend);

}

fclose (fp);

return(0);

This section contains a unix shell program and a modified version of the template Extractor program, that together with an index file, may be used

to extract data from the Berkeley Options Data Base. The shellscript reads in a list of ticker symbols from the file ``bodbread.in'' and a list of BODB filenames from the file ``bodbread.files.'' For each BODB file resXXX, it assumes there exists an index file ``index.resXXX,'' created by the program MakeIndex above. For each file named in bodbread.files, the shellscript creates a temporary extraction file called ``templist'' by grepping the appropriate lines out of the index file. Then, it calls the C program ``IndexExtractor,'' which extracts the specified records from the BODB file.

To extract data, modify the IndexExtractor program as you wish, compile the code using an ANSI-C compiler, create the input files bodbread.in and bodbread.data, then run the shell script:

#!/bin/sh

XX=`cat bodbread.in`

YY=`cat bodbread.files`

for Y in $YY

do

for X in $XX

do

egrep [ ]$X[ ] index.$YY | cut -c 12-26 >>templist

done

IndexExtractor $YY templist > bodbread.out

rm templist

done

Here is the code for the Extraction Program:

/* IndexExtractor

C Program to Extract Data from an indexed BODB file

Copyright (C) Stewart Mayhew, March 1995.

#include <string.h>

#include <stdlib.h>

#include <stdio.h>

#include <math.h>

/* Main Function Begins Here */

main (int argc, char **argv)

{

char buf[256];

char buftix[256];

FILE *fp;

FILE *fp2;

char record[60];

long date,thisdate=0,count,ibegin=1,iend;

long offsetnum, numrecs, beginp, endp;

int loop;

/* Check for proper input */

if (argc != 3) {

printf("usage: %s <datafile> <extractionfile>n"

, argv[0]);

exit(0);

}

if ((fp = fopen(argv[1], "r")) == NULL) {

printf("Can't find data file %sn",argv[1]);

exit(0);

}

if ((fp2 = fopen(argv[2], "r")) == NULL) {

printf("Can't find extraction file %sn",argv[2]);

exit(0);

}

for (loop=1;;loop++) {

if (fgets(buftix,256,fp2)==NULL) exit(0);

sscanf(buftix, "%ld %ld", &beginp, &endp);

offsetnum=(beginp-1)*41;

fseek(fp,offsetnum,SEEK_SET);

numrecs=endp-beginp+1;

/* Inner Loop Begins Here */

for (count=1;count<=numrecs;count++) {

if (fgets(buf,256,fp)==NULL)

exit(0);

/* Read Record */

sscanf(buf, "%41c%",record);

record[41]='0';

printf("%s",record);

} /* End of Loop through records */

}

fclose (fp);

fclose (fp2);

return(0);

}

You can decrease the amount of space required to store BODB files by a factor of about 8:1 using a simple compression program. The unix compress program will work fine for this purpose, but we recommend using gzip, which is nearly as universal as compress but uses a more efficient compression algorithm.

In addition, you can reduce storage space by reducing the amount of redundant information in the database. For example, each record contains the date and ticker symbol. If you use the indexing program suggested above, dates and ticker symbols can be recovered from the index file, so you can remove nine characters [3-11] from each record, reducing storage size by 1/4.

If you resort the data by option series, you can modify the index program to create one entry for each series. This will greatly increase the size of the index file, but will save you another eight characters per record. In the vast majority of cases, the last two digits in the strike price field are ``00''. Exceptions are for options on low-priced stocks with strikes separated by $2.50, and options on stocks which have recently split. If you are storing a subset of data that only contains even-dollar-incremented strike prices, you can remove these two characters. If you are storing only equity options, you can remove character [40], which is always zero. If you try hard enough you should be able to reduce a 150 megabyte BODB file to about 10 megabytes. Please be sure to carefully document all formatting changes you make, and be sure never to disturb the data on the original tapes.

A large number of published articles and working papers have used BODB or MDR data to study securities pricing, market microstructure, and other similar topics. Here is a partial list of these many papers. [This section of the user's guide is still under construction.]

Aggarwal, Raj and Edward Gruca, ``Intraday Trading Patterns in the Equity Options Markets,'' Journal of Financial Research v14 n4 (Winter 1993): 285-297.

(Examines intraday patterns in Volume, proportion of small trades proportion of transactions on upticks, quoted price levels, and bid-ask spreads in the options market.)

[Data: BODB (Jul-Dec, 1986)]

Ancel, Esther Weinstock and Ramash K. S. Rao, ``Stock Returns and Option Prices: An Exploratory Study,'' Journal of Financial Research v13 n3 (Fall 1990): 173-185.

(Uses Options Data to back out implied parameters of an option pricing model)

[Data: BODB (Feb-Jul, 1979)]

Bhattacharya, Mihir, ``Empirical Properties of the Black-Scholes option-pricing Formula Under Ideal Conditions,'' Journal of Financial and Quantitative Analysis v15 n5 (Dec 1980): 1081-1105.

(Uses stock returns data on CBOE traded options to test whether the discretely-rebalanced Black-Scholes hedging strategy truly replicates the option)

Bhattacharya, Mihir, ``Transactions Data Tests of Efficiency of the Chicago Board Options Exchange,'' Journal of Financial Economics v12 (1983): 161-185.

(Uses transactions data to test the arbitrage boundary conditions imposed by rational option pricing and the uniformity of implied volatilities across options imposed by the Black-Scholes model)

[Data: BODB (Aug 1976-Jun 1977)]

Chan, Kalok, Y. Peter Chung and Herb Johnson, ``Why Option Prices Lag Stock Prices: A Trading-based Explanation,'' Journal of Finance v48 n5 (Dec 1993): 1957-1967.

(Finds that stock prices lead option prices and attributes this to the larger relative tick size in option markets)

[Data: BODB (Jan-Mar, 1986)]

Diz, Fernando, ``Long and Short-Run Dynamics of Volatility Formation in the S&P 100 Index Options Market: An Empirical Examination,'' Working Paper (1993),

[Data: MDR OEX (1985-1988)]

Diz, Fernando and Thomas J. Finucane. ``Index Options Expirations and Market Volatility,'' Working Paper (1994).

(Examines index option volatility near expiration dates)

[Data: MDR OEX (1985-1988)]

Diz, Fernando and Thomas J. Finucane, ``The Rationality of Early Exercise Decisions: Evidence from the S&P Index Options Market,'' Review of Financial Studies v6 n4 (Winter 1993): 765-797.

[Data: MDR OEX (Apr 1983-Dec 1988)]

Diz, Fernando and Thomas J. Finucane, ``The Time Series Properties of Implied Volatility of S&P Index Options,'' Journal of Financial Engineering (June 1993).

[Data: MDR OEX (Jan 1984-Aug 1987)]

Frankfurter, George M. and Wai K. Leung, ``Further Analysis of the Put-Call Parity Implied Risk-Free Interest Rate,'' Journal of Financial Research v14 n3 (Fall 1991): 217-232.

(Uses option prices to back out the interest rate implied by the put-call parity relationship, and compares the implied rates with T-bill rates)

[Data: BODB (5 stocks, 1982-1983]

Kamara, Avraham and Thomas W. Miller, Jr., ``Daily and Intradaily Tests of European Put-Call Parity,'' Journal of Financial and Quantitative Analysis v30 n4 (Dec 1995): 519-539.

(Tests the put-call parity relationship for SPX options)

[Data: BODB (SPX Jan-Mar 1989)]

Mayhew, Stewart, Atulya Sarin and Kuldeep Shastri, ``The allocation of informed trading across related markets: An analysis of the impact of changes in equity-option margin requirements,'' Journal of Finance v50, n5 (Dec 1995):1635-1653.

(Examines the effect of option margin requirements on the underlying

stock market.)

Peterson, David R., ``A Transaction Data Study of Day-of-the-Week and Intraday Patterns in Options Returns,'' Journal of Financial Research v13 n2 (Summer 1990): 117-131.

(Examines intertemporal patterns in options returns)

[Data: BODB (consolidated, 1983-1985)]

Rubinstein, Mark and Anand M. Vijh. ``The Berkeley Options Data Base: A Tool for Empirical Research,'' Advances in Futures and Options Research v2 (1987): 209-221.

(Describes the Berkeley Options Data Base)

Sheikh, Aamir M. and Ehud I. Ronn, ``A Characterization of the Daily and Intra-day Behavior of Returns on Options,'' Journal of Finance v49 n2 (Jun 1994): 557-579.

(Examines intertemporal patterns in options returns, correcting for changes in underlying stock prices)

[Data: BODB (Jan 1986-Sep 1987)]

Ticker Index Expiration

OAX S&P 100 Index 1993

OBX S&P 100 Index 1994

OLX S&P 100 Index 1992, 1995

OCX S&P 100 Index 1996

LSW S&P 500 Index 1993

LSY S&P 500 Index 1994

LSX S&P 500 Index 1992, 1995

LSZ S&P 500 Index 1996

WRU Russell 2000 Index 1994

VRU Russell 2000 Index 1995

LRU Russell 2000 Index 1996

WBG CBOE BioTech Index 1994

VBG CBOE BioTech Index 1995

Ticker Index Expiration

LBG CBOE BioTech Index 1996

VEX CBOE Mexico Index 1995

VNX Nikkei 300 Index 1995