# Help avoid Cursor to calc Correlation - Microsoft SQL / MS SQL Server

This is probably a lenghtly post but its mostly data If you are familiar with correlations - I am essentially trying to create a correlation matrix. MyTable ======== Date Symbol Price ===== ====== ====== 1/1/99 MSFT 25.00 1/1/99 INTC 21.00 1/1/99 AMD 23.00 1/2/99 MSFT 22.00 1/2/99 INTC 23.00 1/2/99 AMD 21.00 1/3/99 MSFT 24.00 1/3/99 INTC 23.00 1/3/99 AMD 26.00 1/4/99 MSFT 28.00 1/4/99 INTC 24.00 1/4/99 AMD 23.00 1/5/99 MSFT 25.00 1/5/99 INTC 21.00 1/5/99 AMD 28.00 ----- ----- Desired Output Table ================= SymbolA SymbolB CorrelationCoefficient ======= ======= ================= MSFT INTC 0.7 MSFT AMD 0.8 INTC AMD 1.2 ...

1. ## Help avoid Cursor to calc Correlation

This is probably a lenghtly post but its mostly data

If you are familiar with correlations - I am essentially trying to create a
correlation matrix.

MyTable
========

Date Symbol Price
===== ====== ======
1/1/99 MSFT 25.00
1/1/99 INTC 21.00
1/1/99 AMD 23.00

1/2/99 MSFT 22.00
1/2/99 INTC 23.00
1/2/99 AMD 21.00

1/3/99 MSFT 24.00
1/3/99 INTC 23.00
1/3/99 AMD 26.00

1/4/99 MSFT 28.00
1/4/99 INTC 24.00
1/4/99 AMD 23.00

1/5/99 MSFT 25.00
1/5/99 INTC 21.00
1/5/99 AMD 28.00

-----
-----

Desired Output Table
=================

SymbolA SymbolB CorrelationCoefficient
======= ======= =================
MSFT INTC 0.7
MSFT AMD 0.8
INTC AMD 1.2

Basically, I need to calculate the correlation coefficient between each
symbol pair in MyTable

This is what I am doing right now:
==========================

I create two identical cursors that simply contain a list of distinct
symbols.

Basically, Cursor1 = Cursor2 = {MSFT, INTC, AMD} and use a nested loop. I
then use the following logic:

SELECT A.Date, A.Price, B.Price
FROM MyTable A JOIN MyTable B ON ......Date
WHERE A.Symbol = Cusror1.Symbol AND B.Symbol = Cursor2.Symbol

Essentially, I get temp table #Temp in this format to calculate correl.

Date Price_SymbolA (X) Price_SymbolB (Y)
==== =============== ===============

Now I can easily calculate the Correlation using stadard formula

I use Corrrel = SELECT SUM().../...SQUARE() etc FROM Temp

Then:
INSERT INTO Output Values (Cusror1.Symbol, Cursor2.Symbol, Correl)

-- End of Loop --> loop though the next symbol pairs.

Jay Guest

2. ## Re: Help avoid Cursor to calc Correlation

SELECT
rd1.Symbol as SymbolA,
rd2.Symbol as SymbolB,
FancyFormula as CorrelationCoefficient
FROM RawData rd1 CROSS JOIN RawData rd2
GROUP BY rd1.Symbol, rd2.Symbol

and then once you get the fancy formula worked out,
you can switch the query to be an insert query.

Bye,
Delbert Glass

"Jay" <com> wrote in message
news:%xbTa.11536\$news.prodigy.com...

Delbert Guest

3. ## Re: Help avoid Cursor to calc Correlation

and say the dates have to be the same.

BTW, I hope from several view points
that you do not really desire those
particular values for the correlation coefficients.

Bye,
Delbert Glass

"Jay" <com> wrote in message
news:qQhTa.11578\$news.prodigy.com... [/ref]
> create [/ref][/ref]
each [/ref][/ref]
loop. [/ref][/ref]
correl.
> >
> >[/ref]
>
>[/ref]

Delbert Guest

4. ## Re: Help avoid Cursor to calc Correlation

I used a CROSS JOIN as you suggested but with the clause A.Date = B.Date. I
will try it with INNER JOIN too.

No, I just used sample data. My actual data uses daily returns on some FX
futures.
Thanks.

"Delbert Glass" <com> wrote in message
news:phx.gbl...
> > create [/ref]
> each [/ref][/ref]
distinct [/ref]
> loop. [/ref]
> correl.
> >
> >[/ref]
>
>[/ref]

Jay Guest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•