Newbie with data retrieval questions

Thanks in advance. I'm experienced with Excel but new to Access and have
some basic questions. Could really use some basic guidance to get to where
I'm going.

I have 3 tables, their format isn't set in stone yet, but they are 1. a
list of S&P 500 Index closing prices by date, 2. a list of S&P 500 Total
Return Index by date (different from #1 in that it includes dividends), and
3. 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. #3 is thousands of lines long
and each line is the following format...

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 expired 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 a 30 day period: the S&P 500 Index close, the S&P 500 Total Return
Index close and the close for 4 specific, unchanging options (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 30 days I'll identify 4 new specific options and repeat
for the next 30 day historical period.

I can manipulate the data and get it into Access tables. I can creat
queries, forms, etc. but don't really know how to wring out the precise data
I need without wading through a bunch of other stuff. I could really use
some guidance on the best way to retrieve just the 6 values I'll need for any
given date. 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

Next Date, S&P Index close, S&P Total Return Index close, Option1 close,
Option2 close, Option3 close, Option4 close

Next Date, etc.

I've thought about advanced filtering, forms, queries, pages, etc. and I'm
looking for a little 'local knowledge' before I spend a ton of time going
down blind alleys.

Thanks a bunch. I realize this is different than most questions.

B. Box

Box wrote:
Thanks in advance. I'm experienced with Excel but new to Access and
have some basic questions. Could really use some basic guidance to
get to where I'm going.

I have 3 tables, their format isn't set in stone yet, but they are 1.
a list of S&P 500 Index closing prices by date, 2. a list of S&P 500
Total Return Index by date (different from #1 in that it includes
dividends), and
3. 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. #3 is
thousands of lines long and each line is the following format...

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 expired 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 a 30 day period: the S&P 500 Index close, the S&P 500
Total Return Index close and the close for 4 specific, unchanging
options (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 30 days I'll identify 4 new specific options and
repeat for the next 30 day historical period.

I can manipulate the data and get it into Access tables. I can creat
queries, forms, etc. but don't really know how to wring out the
precise data I need without wading through a bunch of other stuff. I
could really use some guidance on the best way to retrieve just the 6
values I'll need for any given date. 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

Next Date, S&P Index close, S&P Total Return Index close, Option1
close, Option2 close, Option3 close, Option4 close

Next Date, etc.

I've thought about advanced filtering, forms, queries, pages, etc.
and I'm looking for a little 'local knowledge' before I spend a ton
of time going down blind alleys.

Thanks a bunch. I realize this is different than most questions.

B. Box

Let's start by getting those tables straightened out.

It looks to me like you should have one or two tables not three. This
is not Excel. :-)

Having like data scattered around when it is not needed to be for good
normalization is bad design. Don't worry about large tables, you are not
even close to max size for Access. Access does not have the 64K limit.

I would guess you will need the list of the 500 and a primary key (may
be .. what do they call their stock name abbreviation?) then you would have
a second table linked by this key and containing the data and the three
numbers you are storing.

How dose that sound?

Could you give some examples of what those options are?

--
Joseph Meehan

Dia duit

Already I'm learning.

The first table could have: date, S&P 500 Index Close, S&P Total Return
Index Close

The second table could have all the option data. 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 but that's easy to fix), 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 NEW LINE
Trade date +1, S&P Index close, S&P Total Return Index close, Option1 close,
Option2 close, Option3 close, Option4 close NEW LINE
Trade date +2, etc.

Note sure what Access feature to use to get that information. Just using
some sort of search for each nugget for each day (total of 6) multiplied by
approx. 260 trading days per year multiplied by 15 years of data, well I'd
just be really old. Inputing the required options once for each month and
having data spit out in workable format would be great, since there are only
about 180 months.

Thanks,

BB





"Joseph Meehan" wrote:

Box wrote:
Thanks in advance. I'm experienced with Excel but new to Access and
have some basic questions. Could really use some basic guidance to
get to where I'm going.

I have 3 tables, their format isn't set in stone yet, but they are 1.
a list of S&P 500 Index closing prices by date, 2. a list of S&P 500
Total Return Index by date (different from #1 in that it includes
dividends), and
3. 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. #3 is
thousands of lines long and each line is the following format...

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 expired 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 a 30 day period: the S&P 500 Index close, the S&P 500
Total Return Index close and the close for 4 specific, unchanging
options (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 30 days I'll identify 4 new specific options and
repeat for the next 30 day historical period.

I can manipulate the data and get it into Access tables. I can creat
queries, forms, etc. but don't really know how to wring out the
precise data I need without wading through a bunch of other stuff. I
could really use some guidance on the best way to retrieve just the 6
values I'll need for any given date. 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

Next Date, S&P Index close, S&P Total Return Index close, Option1
close, Option2 close, Option3 close, Option4 close

Next Date, etc.

I've thought about advanced filtering, forms, queries, pages, etc.
and I'm looking for a little 'local knowledge' before I spend a ton
of time going down blind alleys.

Thanks a bunch. I realize this is different than most questions.

B. Box

Let's start by getting those tables straightened out.

It looks to me like you should have one or two tables not three. This
is not Excel. :-)

Having like data scattered around when it is not needed to be for good
normalization is bad design. Don't worry about large tables, you are not
even close to max size for Access. Access does not have the 64K limit.

I would guess you will need the list of the 500 and a primary key (may
be .. what do they call their stock name abbreviation?) then you would have
a second table linked by this key and containing the data and the three
numbers you are storing.

How dose that sound?

Could you give some examples of what those options are?

--
Joseph Meehan

Dia duit

Box wrote:
Already I'm learning.

The first table could have: date, S&P 500 Index Close, S&P Total
Return Index Close

The second table could have all the option data. 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 but that's easy to
fix), 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 NEW LINE
Trade date +1, S&P Index close, S&P Total Return Index close, Option1
close, Option2 close, Option3 close, Option4 close NEW LINE
Trade date +2, etc.

Note sure what Access feature to use to get that information. Just
using some sort of search for each nugget for each day (total of 6)
multiplied by approx. 260 trading days per year multiplied by 15
years of data, well I'd just be really old. Inputing the required
options once for each month and having data spit out in workable
format would be great, since there are only about 180 months.

Thanks,

BB


Sorry about not getting back sooner, I seem to have lost track.

The table looks good.

To find your information there are a number of options.

Queries are quick easy and versatile. I would start with a query as it
is possible to see the results easily and understand how you are getting
them. For a more structured output, try a form or report. Each has its
good points.





"Joseph Meehan" wrote:

Box wrote:
Thanks in advance. I'm experienced with Excel but new to Access
and have some basic questions. Could really use some basic
guidance to get to where I'm going.

I have 3 tables, their format isn't set in stone yet, but they are
1. a list of S&P 500 Index closing prices by date, 2. a list of
S&P 500 Total Return Index by date (different from #1 in that it
includes dividends), and
3. 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. #3 is
thousands of lines long and each line is the following format...

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 expired 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 a 30 day period: the S&P 500 Index close, the S&P
500 Total Return Index close and the close for 4 specific,
unchanging options (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 30 days I'll identify 4 new specific options and
repeat for the next 30 day historical period.

I can manipulate the data and get it into Access tables. I can
creat queries, forms, etc. but don't really know how to wring out
the precise data I need without wading through a bunch of other
stuff. I could really use some guidance on the best way to
retrieve just the 6 values I'll need for any given date. 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

Next Date, S&P Index close, S&P Total Return Index close, Option1
close, Option2 close, Option3 close, Option4 close

Next Date, etc.

I've thought about advanced filtering, forms, queries, pages, etc.
and I'm looking for a little 'local knowledge' before I spend a ton
of time going down blind alleys.

Thanks a bunch. I realize this is different than most questions.

B. Box

Let's start by getting those tables straightened out.

It looks to me like you should have one or two tables not three.
This is not Excel. :-)

Having like data scattered around when it is not needed to be
for good normalization is bad design. Don't worry about large
tables, you are not even close to max size for Access. Access does
not have the 64K limit.

I would guess you will need the list of the 500 and a primary
key (may be .. what do they call their stock name abbreviation?)
then you would have a second table linked by this key and containing
the data and the three numbers you are storing.

How dose that sound?

Could you give some examples of what those options are?

--
Joseph Meehan

Dia duit

--
Joseph Meehan

Dia duit