Measuring correlation using Excel - Aussie Stock Forums

# Thread: Measuring correlation using Excel

1. ## Measuring correlation using Excel

Im not sure if this is possible but using Excel, can you put up 2 sets of data like in 2 columns and then use some sort of function to measure their correlation?

Any suggestions?

2. ## Re: Measuring correlation using Excel

Originally Posted by nizar
Im not sure if this is possible but using Excel, can you put up 2 sets of data like in 2 columns and then use some sort of function to measure their correlation?

Any suggestions?

http://www.bized.co.uk/timeweb/crunc...s.htm#excelexp

Guess it depends on the variables in question though? Dates, or comparing 2 stocks; or a certain commodity to the price of one stock?

I guess an overly simplistic way would be to have 2 charts overlapping each other, & do it through the eye

3. ## Re: Measuring correlation using Excel

Just about anything is possible using excel. Depending on what sort of correlation you're looking for you could have a third column to display whether both stocks moved up or down together on a given day. eg..

=IF(OR(AND(A2-A1>0,B2-B1>0),AND(A2-A1<0,B2-B1<0)),1,"")

That will display a 1 in the third column if both stocks went up or down together. Then you can add up the 1's and get a % of the total days that the stocks moved up or down together.

or something like that anyway

4. ## Re: Measuring correlation using Excel

Am I missing something? Why not just use the correlation coefficient?

=correl(array1,array2)

Go to wikipedia for info on what the correlation coefficient means and how it can be used.

5. ## Re: Measuring correlation using Excel

The purpose is to be able to input results from 2 systems and determine their correlation.

eg. Say you've got a table with 2 columns, and the data in these columns is the monthly profits for N number of periods, and you want to see what sort of correlelation there is between -1 (perfect negative correlation) and 1 (perfect positive correlation).

6. ## Re: Measuring correlation using Excel

Originally Posted by doctorj
Am I missing something? Why not just use the correlation coefficient?

=correl(array1,array2)

Go to wikipedia for info on what the correlation coefficient means and how it can be used.
Yes doc thank you.

You are correct

7. ## Re: Measuring correlation using Excel

Excel has a data analysis add in. It comes standard with excel although needs to be enabled.

On Excel 2003
Tools--> Add Ins -->Tick data Analysis Toolpak
and
Tools--> Add Ins -->Tick data Analysis Toolpak - VBA

Once these are enabled.

Then you can have your two data series in separate columns and go
Tools --> Data Analysis
And
Then select correlation, you can do a heap of other functions as well.

Do a google for excel data analysis toolpak and you should be able to find a few good sites that explain what all the functions do.

#### Posting Permissions

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