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.
---
Fyodor Golos
Stockworm, Inc.
