At 14:07 -0500 on 05/25/2004, <mysql@stripped> wrote about need
help with a complicated join:
>I am trying to come up with a query that takes two tables, one with
>non-split-adjusted historical stock prices, and one with information on
>splits, for instance:
>
>CREATE TABLE quotes (
> symbol VARCHAR(127) NOT NULL,
> date DATE NOT NULL,
> quote FLOAT NOT NULL,
> PRIMARY KEY (symbol, date),
> INDEX (date),
>);
>INSERT quotes VALUES ("A", "2004-01-01", 3);
>INSERT quotes VALUES ("A", "2004-01-02", 3);
>INSERT quotes VALUES ("A", "2004-01-03", 3);
>INSERT quotes VALUES ("A", "2004-01-04", 3);
>INSERT quotes VALUES ("A", "2004-01-05", 2);
>INSERT quotes VALUES ("A", "2004-01-06", 2);
>INSERT quotes VALUES ("A", "2004-01-07", 2);
>INSERT quotes VALUES ("A", "2004-01-08", 1);
>INSERT quotes VALUES ("A", "2004-01-09", 1);
>
>CREATE TABLE splits (
> symbol VARCHAR(127) NOT NULL,
> date DATE NOT NULL,
> split_from INT UNSIGNED NOT NULL,
> split_to INT UNSIGNED NOT NULL,
> PRIMARY KEY (symbol, date),
>);
>INSERT splits VALUES ("A", "2004-01-05", 2, 3);
>INSERT splits VALUES ("A", "2004-01-08", 1, 2);
>
>I need to be able to pull out split-adjusted quotes, like this:
>
>SELECT symbol, date, ...some magic... FROM quotes WHERE symbol = "A" ORDER
>BY date;
>
>+--------+------------+-------+----------------+
>| symbol | date | quote | adjusted_quote |
>+--------+------------+-------+----------------+
>| A | 2004-01-01 | 3 | 1 |
>| A | 2004-01-02 | 3 | 1 |
>| A | 2004-01-03 | 3 | 1 |
>| A | 2004-01-04 | 3 | 1 |
>| A | 2004-01-05 | 2 | 1 |
>| A | 2004-01-06 | 2 | 1 |
>| A | 2004-01-07 | 2 | 1 |
>| A | 2004-01-08 | 1 | 1 |
>| A | 2004-01-09 | 1 | 1 |
>+--------+------------+-------+----------------+
>
>Split-adjusting means that on a split date all previous prices are
>multiplied by "split_from/split_to" ratio. In my example two splits took
>place, one on 2004-01-05, which multiplied all previous prices by 2/3 and
>another one on 2004-01-08, which multiplied all previous prices (including
>those already affected by first split) by 1/2.
>
>Any help would be appreciated.
Question: Are you doing this direct in MySQL or is it being done as a
Web Inquiry that is doing the MySQL Select "Under the Covers" and
then displaying the result? If the latter, then you can do it by
first building a Temp Table of all records where symbol="A" (fill in
the requested symbol from the user query) AND date=as-of-date (again
supplied by user) creating an adjusted field equal to the quote. The
temp table now has only the requested table rows and ends at the
as-of-date. You then read the splits table for all records dated
before or on the as-of-date and do the updates to the adjusted field
for each adjusted row (you can have the loop update the factor as
needed so you only need to run the table once). Then just read and
display the temp table.
One additional question. From your definition when you say
"non-split-adjusted historical stock prices" I assume that you mean
that on the day a split occurs, that day's quote HAS BEEN adjusted
for the split and all subsequent quotes are based on the status of
the latest split. What you are trying to do with the adjusted column
is adjust for the splits and have the quotes reflect the quote in
terms of a block of stock quoted on day one in the table. IOW: If the
first quote in the table was for 1 share and due to splits that block
is now 5 shares, you want to multiple the todays (1-share) quote by 5
to get a constant based price and do the same on the other day by
using the then current block size as the adjustment factor.
