You are beyond the realm of SQL. What you would need for something like
this is a dynamically-generated case statement that would apply different
multipliers based on the date of the quote you are trying to adjust. For
those quote values that exist BEFORE multiple splits you must adjust by the
PRODUCT of those splits to achieve your adjusted split.
There is currently no PRODUCT() keyword that is analogous to the SUM()
keyword or you would be able to get your historical multipliers through
sub-selects.
It may be possible to write a UDF that takes the stock symbol and a target
date and computes the adjustment ratio for that date (but I haven't built
one yet so I don't know for sure)
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
<[EMAIL PROTECTED]
com> To: <[EMAIL PROTECTED]>
Sent by: "Fyodor cc:
Golos" Fax to:
<[EMAIL PROTECTED] Subject: need help with a complicated
join
com>
05/25/2004 03:07
PM
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
