sorry for that noob question but my mathematical knowledge is just basic. I have a bunch of stocks and I want to find out which pair is having the best negative correlation right now. I was reading about covariance, correlation and correlation coefficient, but which of those do I need for that and how do I calculate?
Kind regards
The formula that relates correlation coefficient, correlation with covariance is: $$ \rho _{X,Y}=\mathrm {corr} (X,Y)={\mathrm {cov} (X,Y) \over \sigma _{X}\sigma _{Y}}={E[(X-\mu _{X})(Y-\mu _{Y})] \over \sigma _{X}\sigma _{Y}} $$ If you are unfamiliar with using such equations, you can use Excel to calculate this for you.
First generate a return series, arranged in columns, for each of the stocks you are interested in analyzing. The top row should be the names of your stocks.
You can then use the "Data Analysis" add-in to generate a correlation matrix for you. Go to the Data tab in Excel. In the Analysis area of the tool bar, you will see "Data Analysis". Click on this. A window will open up. Highlight "Correlation" and hit "OK". Another window will open.
In this new window, put in as the input range the columns that contain your return series, including the labels in the first row. Click the check box that says "Labels in First Row". Click "OK".
You should get a correlation matrix in a new sheet in the same workbook with the pairwise correlations of each stock you are evaluating. Look in this matrix for the highest negative number. The labels on the top and left edges that intersect at this "highest negative number" cell will give you the stocks with the highest negative correlation for the period under evaluation.