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. Thanks in advance J-S
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.
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.
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.
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. Currently I use special spread trading software. Hope this helps