Stock Price History Criteria

[Justin Tyme]

I need help creating a query criteria that would reveal any sequential
price range increase of $3 or more over a period of so many days. So,
if we have a closing price history going back several years and we're
trying to see any consecutive increase, like from $33 on 4/1/01 up to
$36 or more on 4/5/01 or whatever date within a week or two range of
dates and prices. But we're looking to reval all the price increases of
$3 or more in the entire table, displayed in rows My math's not
accomplished enough to figure out the correct formula. Can anyone help
me with this? Thanks!

 

[Tom Ellison]

Dear Justin:

As I understand this, you will have, at a minimum, these columns:

Stock identity (ticker?)
Date
Quoted Price

Do you have high, low, and closing prices, or just one price from which to
work for each stock for each day?

Now we need to discuss the "given" values. Perhaps you have a target date
and a number of days over which to run this. Then, there must be a
threshhold, say a minimum increase or decrease of so many dollars.

Or is there a target date? Do you want to consider ALL the periods covered
by the data?

Now, realize this, If you have 5000 stocks and 200 trading days in a year
for 5 years, that represents 5 million datapoints. If you want to find the
high and low over every 10 day period (2 weeks) you must look at 50,000 data
points for each day, or 50,000,000 values for the 5 years. This is going to
take a LOT of time to produce.

If you want the analysis for 1, 2, 3, and 4 week intervals the whole thing
will be 5 times as bad.

With very judicious choices of databases, database construction, and
computer equipment, I believe this could be done in less than an hour.
Without considerable care, it could be a lot worse.

It is possible, however, to store the historic analysis. This assumes the
daily data is accurate and won't need corrections. The change in each stock
for a set of time periods can be stored, and this "partially cooked" data
can be searched for differing price change amounts, and much more quickly.
The invariability of historical data makes this a good candidate for
breaking the rules about storing derived values.

Because of the potential challenge of making hundreds of millions of
calculations, I'm going to recommend for this project some careful analysis
before you proceed.

Tom Ellison

 

[Justin Tyme]

Tom,
Thank you so much for responding. I'll try to narrow this down. This
calculation would be done one stock at time. I download the price
history in csv format for ONE stock from yahoo, open that file with
openoffice spreadsheet and copy that data to a openofffice db table.
We're only dealing with these past numbers and no future numbers, so
these historic prices are indeed accurate and stored in the database.
For example the price history for ACI (Arch Coal, Inc.) extends back
from 3/24/06 to 3/23/01 which is when they first posted earnings
results.
That equals 1257 records including the column headings (Date, Open,
High, Low, Close, Volume). We're only putting the criteria equation in
the close column since the close price is all were interested in. We're
then looking for a move up (or down for "Puts") of at least $3 dollars
over whatever time frame. The $3 is the threshold. Now we could do only
50 records (dates) at a time as a target but I was hoping to run the
query criteria for all the dates in the table (ACI=1257 dates in the
table). I hope this clarifies the parameters somewhat. Thanks again for
your help!