I have 2 tables, their format isn't set in stone yet, but they are:
1. a list of S&P 500 Index and S&P 500 Total Return Index closing prices(different from previous in that it includes dividends), and
2. a list of all the S&P 500 options (e.g. the options that are traded on the entire index at the CBOE) closing prices. #2 is thousands of lines long and each line is the following general format... (I've pasted the precise format below)
Trade date, option expiration date (month and year), option strike price, option type (call or put), that option's close price.
Each of these variable will have many duplicates, scores of distinct options traded on Aug. 31, 2005; some expire in Sept., some in December, etc., there are scores of option strike prices, each expiration and strike has puts and call, etc. but there is a distinct record for each distinct option on a single day once you've 'answered' all the variables.
What I want to be able to do is to get the following info. for every date over many 20 trading day periods: the S&P 500 Index close, the S&P 500 Total Return Index close and the close for 4 specific, unchanging options(for the 20 trading day period, the 4 options will change at the end of the 20 trading day period when I'll need to look at 4 new, unchanging options for each of the next 20 trading days). Each option is defined by an expiration month and year(this is in a single field so that options expiring in Sept. 2005 are distinct from those expiring in Sept. 2006) , strike price, and type (call or put). I'll also need to do some basic math to arrive at a daily index value based on the six closing prices.
At the end of the 20 trading days I'll identify 4 new specific options and repeat for the next 20 day historical period.
The best format for the results would be...
Date, S&P Index close, S&P Total Return Index close, Option1 close, Option2 close, Option3 close, Option4 close, Our calculated index value
Next Date, S&P Index close, S&P Total Return Index close, Option1 close, Option2 close, Option3 close, Option4 close, Our calculated index value
Next Date, etc.
When I say trading days I mean business days when the exchanges are open.
The options aren't on individual stocks in the S&P, rather they are on the S&P Index itself (ticker symbol SPX).
I've pasted an example of the data below...
75 12/02/2002 43300 Call 500 3/2003
75 12/02/2002 40 Put 500 3/2003
75 12/02/2002 120 Put 500 6/2003
75 12/02/2002 43390 Call 500 6/2004
75 12/02/2002 590 Put 500 6/2004
75 12/02/2002 260 Put 500 9/2003
75 12/02/2002 43520 Call 500 12/2002
75 12/02/2002 3 Put 500 12/2002
75 12/02/2002 43180 Call 500 12/2003
75 12/02/2002 450 Put 500 12/2003
75 12/02/2002 38920 Call 510 6/2004
75 12/02/2002 1000 Put 510 6/2004
75 12/02/2002 28 Put 600 1/2003
75 is the data vendor contract ID, 12/02/2002 is the trade date, the next number is the closing price for that option (without any decimal, divide by
100 to get the actual price), the next is the option
type (put or call) the next is the strike price for that option and the final column is the expiration for the option (all these options expire on the third Friday of the listed month/year). A trader would describe the last option listed by saying the "Jan. oh three, six hundred put closed at 28 cents"
There may be a total of approx. 100 prices for every trade date. Following the info. for 12/02/2002 would be all the prices for 12/03/2003. For every trading day in the month of Jan. 2002 we may want to know the daily closing prices for the March 2002 900 call; the March 2002, 950 call; the June 2002 900 call; and the June 2002 950 call; in addition to the S&P 500 Index and S&P 500 Total Return Index values. For every day in Feb. the 4 options would
change. I'd like to be able to input (manually is fine) the four options we're looking for and have the following data spit out in approx. this format.
Trade date, S&P Index close, S&P Total Return Index close, Option1 close, Option2 close, Option3 close, Option4 close, our calculated index value NEW LINE
Trade date +1, S&P Index close, S&P Total Return Index close, Option1 close, Option2 close, Option3 close, Option4 close, our calculated index value NEW LINE
Trade date +2, etc.