I have some weekly stock return data that looks like this:
I've hidden a lot of rows there, obviously. But basically there are 200ish different stocks and their weekly returns over the course of 10 years. They are paired with a return in column E for the same period for the S&P 500. I want to calculate betas with these Covariance(Stock, S&P 500)/Variance(S&P 500). I'm struggling just to create the Covariance portion right now:
Column A in my new sheet is a unique list of all the ticker symbols and my formula is as follows:
=COVARIANCE.P(INDIRECT("'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!F:F,0)&":'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!G:G,0)),INDIRECT("'Weekly Data'!E$"&MATCH(A2,'Weekly Data'!F:F,0)&":'Weekly Data'!E$"&MATCH(A2,'Weekly Data'!G:G,0)))
Getting a #REF error.