hist_qry8.sql

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

Output

The first 25 rows returned by this query are:

TRADING_SYMBOL    TRADING_SYMBOL    CORRELATION
AAI               AAH                 -4.29E+09
AAI               AAC                 -3.65E+09
AAF               AAH                 -3.04E+09
AAI               AAB                 -2.92E+09
AAF               AAC                 -2.58E+09
AAD               AAH                 -2.57E+09
AAG               AAH                 -2.56E+09
AAD               AAC                 -2.24E+09
AAG               AAC                 -2.22E+09
AAF               AAB                 -2.07E+09
AAJ               AAH                 -1.80E+09
AAA               AAH                 -1.79E+09
AAD               AAB                 -1.72E+09
AAG               AAB                 -1.71E+09
AAE               AAH                 -1.68E+09
AAJ               AAC                 -1.67E+09
AAE               AAC                 -1.63E+09
AAA               AAC                 -1.62E+09
AAI               AAE                 -1.29E+09
AAA               AAB                 -1.21E+09
AAJ               AAB                 -1.15E+09
AAE               AAB                 -1.11E+09
AAF               AAE                 -9.21E+08
AAI               AAJ                 -8.34E+08
AAG               AAE                 -6.35E+08
...

Note:

The output displayed in this section is derived by running the query with the large set of sample data provided in the Sybase RAP Samples component.

SQL Statements

The SQL statements for this query are:

-- 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
;