Hi All,
Thanks in advance for your help. I have a set of about 100 daily stock returns listed chronologically. However, I have selected only limited data from each stock and therefore some of the data begins/ends earlier than others (for example Stock A may have trading data from 2003 to 2006, Stock B from 2004 to 2007, and Stock C from 2007 to 2012).
I am now trying to find the covariance of the monthly returns of each stock with the corresponding monthly returns of the S&P 500 (but only during the selected period). Is there a way to automatically have the "covar" formula select only the periods with data for each respective stock and have it match up with the same time frame of S&P returns?
I have attached a sample spreadsheet (with fake randomized returns)to better help you visualize the problem. I calculated the 1st covariance in column C manually to show an example of what I need across the full 100 columns of data.
Thanks,
Ben
Bookmarks