Anthony's VBA Forum

[Jay]

Hi,

Firstly, thank you for posting VBA tutorials and code online. I think this site is a wonderful resource and I hope to learn a lot from it.

I created my own portfolio optimisation spreadsheet, but I did it the hard way - i.e. mostly using formulae in cells - so the VBA version intrigues me. (Also, my macros have an annoying tendency to crash the system!)

The issue I have with the code on this site is very minor and infuriating; namely, I don't know what values (data) to enter in the "Input" sheet.

I have access to all the stock data in the world, and my own spreadsheet calculates projected volatility using the equation:

SQRT [ Sigma {Weight.n^2 * Standard Deviation.n^2} + 2 * Weight.a * Weight.b * Cov(a,b) + 2 * Weight.a * Weight.c * Cov(a,c) ... ]

Etc.

I can't quite follow the code - although I've not yet really dug into it - but I don't know what to enter as 'Variance'.

I have calculated the standard deviation for stocks over 15 years (in most cases), and the variance and the covariance between stocks a and b (say), but I can't see how the former would apply in this instance and I get nonsensical results when I use each of the latter!

Any ideas?

Thanks,

Jay[/b]