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.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
