SQL

The following script contains the SQL statements for this query.

-- Find the pair-wise coefficients of correlation in a set of 10 securities
-- for a 2 year period. Sort the securities by the coefficient of correlation,
-- indicating the pair of securities corresponding to that row.

commit
;

SELECT a.TRADING_SYMBOL,b.TRADING_SYMBOL, 
(Count(*) * sum(a.CLOSE_PRICE * b.CLOSE_PRICE) - sum(a.CLOSE_PRICE) * sum(b.CLOSE_PRICE)/
sqrt(count(*) * sum(a.CLOSE_PRICE * a.CLOSE_PRICE ) - (sum(a.CLOSE_PRICE) * sum(a.CLOSE_PRICE))) *
sqrt(count(*) * sum(b.CLOSE_PRICE * b.CLOSE_PRICE ) - (sum(b.CLOSE_PRICE) * sum(b.CLOSE_PRICE)))) as CORRELATION
from (Select TRADING_SYMBOL, TRADE_DATE,CLOSE_PRICE 
from 
STOCK_HISTORY AS B 
WHERE B.TRADING_SYMBOL BETWEEN 'AAA' AND 'AAJ'
AND LENGTH(B.TRADING_SYMBOL) = 3
and B.TRADE_DATE BETWEEN '2005-02-08'
and '2007-02-07' 
) a,
(Select TRADING_SYMBOL, TRADE_DATE,CLOSE_PRICE 
from 
STOCK_HISTORY AS B 
WHERE B.TRADING_SYMBOL BETWEEN 'AAA' AND 'AAJ'
AND LENGTH(B.TRADING_SYMBOL) = 3
and B.TRADE_DATE BETWEEN '2005-02-08'
and '2007-02-07'   ) b
where a.TRADE_DATE = b.TRADE_DATE
group by a.TRADING_SYMBOL, b.TRADING_SYMBOL
order by correlation
;

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF