Measuring correlation using Excel
- Thread starter nizar
- Start date
Nyden
G.E. Money Genie
- Joined
- 23 May 2007
- Posts
- 1,368
- Reactions
- 1
http://www.bized.co.uk/timeweb/crunching/crunch_relate_illus.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
- Joined
- 26 February 2007
- Posts
- 358
- Reactions
- 0
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
=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
doctorj
Hatchet Moderator
- Joined
- 3 January 2005
- Posts
- 3,271
- Reactions
- 8
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.
=correl(array1,array2)
Go to wikipedia for info on what the correlation coefficient means and how it can be used.
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).
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).
Yes doc thank you.
You are correct
http://www.businessbookmall.com/Excel_RP_Pearson's_Correlation_Coefficient.htm
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.
A wizard will start and ask for your input range etc.
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.
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.
A wizard will start and ask for your input range etc.
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.
Similar threads
