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
;