General Topics
Markets
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# Calculating Correlation with Excel

Discussion in 'Trading Software' started by J-S, Dec 4, 2002.

1. ### J-S

Does anyone know how to do this? I'm using OHLC data from CSI- Will excel be able to import this and run correlation tests between markets? I wonder whether anyone is able to tell me how to do this? Unfortunately I'm a complete novice with excel.

J-S

2. ### Avalanche

I think Hitman has a stock correlator macro on his website....but when I downloaded it a few months back it was in chinese or something.

You might check with him though because I think he still has it available for download you would just need to translate it.

3. ### DaveN

The challenge will be to align the price series so that you can measure the correlation. If it's daily bars that you are correlating, that task shouldn't be too difficult. But if you are using intraday data, make sure that the dates and times line up on the same row.

You can then use the Excel function "=Correl(series1, series2)" where the series are the ranges of data that you want to correlate with each other. Presumably, this will the the C data from your CSI data source. The function will return the correlation coefficient, which is the number that almost everyone uses when discussing correlation.

4. ### acrary

Put the data into separate columns and use the correl function.
You'll also need to know how many rows are in the data.

Ex. Column A = Open, B = High, C = Low, D = Close
252 rows of daily data

To check the open versus the close, type in =correl(a1:a252,d1:d252) in a separate cell.

5. ### H2O

It is possible,

I do it all the time but when you are using CSI data, use CSI, They have correlation matrixes on their website and in the multi market analyser.