hist_qry2.sql

Adjusts all prices and volumes to reflect the split events during a specified 300-day period for a specified set of 1000 stocks, assuming that events occur before the first trade of the split date.

To determine the split-adjusted prices and volumes, prices are multiplied by the split factor and volumes are divided by the split factor.

Output

The results returned by this query are:

TRADING_SYMBOL  TRADE_DATE  H_PRC  L_PRC  C_PRC  O_PRC  VOL
AAA  2005-03-03  793.52  728     740.88  740.88  27.46428571
AAA  2005-03-04  749.84  651     726.04  740.88  29.10714286
AAA  2005-03-07  764.4   672     733.32  726.04  29.10714286
AAA  2005-03-08  786.24  637     726.04  733.32  26.17857143
AAA  2005-03-09  707     637     711.48  726.04  26.67857143
AAA  2005-03-10  700     624.96  697.2   711.48  24
AAA  2005-03-11  749     618.24  711.2   697.2   23.75
AAA  2005-03-14  742.56  658     732.48  711.2   25.85714286
AAA  2005-03-15  757.12  686     725.2   732.48  26.85714286
AAA  2005-03-16  728     644     710.64  725.2   28.46428571
AAA  2005-03-17  721     700     724.92  710.64  30.14285714
AAA  2005-03-18  735     644     717.64  724.92  28
AAA  2005-03-21  707     665     710.64  717.64  28
AAA  2005-03-22  763     637     710.64  710.64  27.71428571
AAA  2005-03-23  763     658.56  696.36  710.64  24.92857143
AAA  2005-03-24  692.16  638.4   682.36  696.36  25.14285714
AAA  2005-03-25  725.76  651.84  689.36  682.36  26.89285714
AAA  2005-03-28  728     624.96  703.08  689.36  26.89285714
AAA  2005-03-29  728     686     710.08  703.08  25
AAA  2005-03-30  700     672     710.08  710.08  23.25
AAA  2005-03-31  735     672     717.08  710.08  21.35714286
AAA  2005-04-01  735     651     724.36  717.08  21.75
AAA  2005-04-04  735     665     724.36  724.36  19.78571429
AAA  2005-04-05  756     644     724.36  724.36  19.35714286
AAA  2005-04-06  778.96  679     738.92  724.36  19.35714286
...

SQL Statements

-- Adjust all prices and Volumes (prices are multiplied by the split factor
-- and Volumes are divided by the split factor) for a set of 1000 stocks to
-- reflect the split events during a specified 300 day period, assuming that
-- events occur before the first trade of the split date.
-- These are called split-adjusted prices and Volumes.

commit
;

SELECT B.TRADING_SYMBOL, TRADE_DATE,
B.HIGH_PRICE * IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) H_PRC,
B.LOW_PRICE * IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) L_PRC,
B.CLOSE_PRICE * IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) C_PRC,
B.OPEN_PRICE *IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) O_PRC,
B.Volume/IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) VOL
FROM STOCK_HISTORY AS B 
	left outer join SPLIT_EVENT A
	on B.INSTRUMENT_ID = A.INSTRUMENT_ID 
	AND B.TRADE_DATE < A.EFFECTIVE_DATE
WHERE  B.TRADING_SYMBOL BETWEEN 'AAA' AND 'BML'
	AND LENGTH(B.TRADING_SYMBOL) = 3
	and B.TRADE_DATE BETWEEN '2005-03-03'
	and '2005-12-03' 
GROUP BY B.TRADING_SYMBOL, 
TRADE_DATE ,
B.HIGH_PRICE,
B.LOW_PRICE,
B.CLOSE_PRICE, 
B.OPEN_PRICE,
B.Volume
;