Constructing a Covariance Matrix

futureoptions · June 6th, 2012, 6:25 pm

Apols if this question has already been answered but my search couldn't find from archives. Given the percentage change in daily prices for N stocks for 250 trading days in a year, how do I construct the covariance matrix for those percentage price changes among those symbols? I am rummaging the internet for ideas, but was wondering if anyone can point me to an authoritative and accurate source of how to go about doing this. Simplest method preferred.

amit7ul · June 7th, 2012, 6:48 am

convert price series into series of log returns for each of the N stocks ( you will have N series, each of 249 elements)..suppose A1:A249 has log returns of stock 1 and B1:B249 of stock 2 and so on. Then N by N covariance matrix has these entries(1,1) = COVAR(A1:A249,A1:A249)(2,1) = COVAR(B1:B249,A1:A249)(3,1) = COVAR(C1:C249,A1:A249)learn how to dollar-ize in excel (use of key F4) or write a small vba code to do this.

futureoptions · June 7th, 2012, 10:38 am

Thanks Amit - Would it be appropriate to use just the prices and not long returns if the aim is to find covariances between various time lags of the same parameter? So the aim is to estimate covariances between prices at t and price at t-1, and covariance between price at t with price at t-3 and so on.

bearish · June 7th, 2012, 10:52 am

QuoteOriginally posted by: futureoptionsThanks Amit - Would it be appropriate to use just the prices and not long returns if the aim is to find covariances between various time lags of the same parameter? So the aim is to estimate covariances between prices at t and price at t-1, and covariance between price at t with price at t-3 and so on.No, that will essentially produce nonsense. You can try and google "unit root" or "spurious correlation", but you might as well just accept it as a fact.