Correlation or r-squared to determine if a stock has specific movement relative to an index?

Question

In Excel you have the daily prices of all the stocks in the S&P 500 for 3 years; and the index itself.

Using all the data how might you determine which stocks had company specific movement during the 3 year period?

The simplest thing I can think of is to regress each stock against the index and then filter down to those with r-squared closest to 0. Another option is to use the correlation and filtering for those closest to -1, but negative beta isn't necessarily company specific movement.

I'm trying to make this first iteration as simple as possible and easily visible using Excel. Is there a better approach?

Answer 1

Short answer: to find the companies with the higher specific risk, look for the regressions with higher mean squared residuals.

Long Answer: If you are interested in company specific risk, what you are doing is kind of right. The lower the $R^{2}$ is, the higher the ratio of company specific risk to total risk is.

Looking at the $R^{2}$ alone is not sufficient to compare company specific risks between companies, because some of them might have very different levels of total risk. After each regression you should compute the company specific risk (which is just the Mean squared residuals).

The risks are connected by following equation:

$$\sigma^{2}_{total}= \beta^{2} \sigma^{2}_{market}+ \sigma^{2}_{specific} $$

Answer 2

Company specific movement can be estimated as the standard deviation of monthly residuals. So, regress each firm with the index and estimate the monthly residuals. Calculate the standard deviation of monthly residuals for each firm. Then use residual risk as an estimate of the magnitude of firm specific movement.