How to create a macro to calculate this formula?

[Joker_Hazard]

Hello guys. 

I am trying to create a macro to calculate the covariance between the benchmark and the Stock 1, then Benchmark and Stock 2....

Visual example:

Pearson Tool that calculates the covariance:

At the end, there would be a new column with all the covariance that was previously calculated

Is is possible to create a macro for this? I have 2500 stocks so it would be impossible to flag each box individually.

Any input is appreciated!! Thanks 🙂

[Christina_H · Accepted solution]

I don't normally use the Pearson tool so you might need to make some adjustments, but it seems to me that you can transpose the data and run it through a batch macro.

[Joker_Hazard]

Christina, thank you so much! 

That's an awesome solution!!