HELP: Calculate serial covariance on Excel with bid-ask spread

As explained in the old thread that you posted in (https://www.excelforum.com/excel-for...ovariance.html ), the Excel side of the answer seems to be to use one of Excel's build in COVARIANCE functions (COVAR() or COVARIANCE.P() or COVARIANCE.S()). The big unanswered question from the old thread was how to determine exactly what two ranges should go into the covariance function. I have no familiarity with Roll's method, so I have no idea how one determines what ranges to feed into the chosen covariance function. My guess at "covariance between close at t and close at t-1" might be COVAR(A3:A10,A2:A9), but that is just a guess.

As also explained in the other thread, we are good with Excel, but we are not always good at the specific financial model under consideration. If you can help us understand enough about Roll's method, we should be able to help you program that into Excel. At this point, all I can say is that I expect it to involve one of the covariance functions.

Help file for the COVARIANCE.S() function (note that the syntax of all three of Excel's covariance function is the same): https://support.office.com/en-us/art...f-1f5320314977