using Excel VBA, given the daily price of 50 stocks, choose 10 stocks such that they have the minumum correlation

Question

The high-level goal is to choose 10 stocks that have the lowest correlation among one another, out of a pool of 50, so that I can have a well-diversified portfolio.

I have managed to write some VBA macro to download the past 3 years of daily price data from Yahoo finance, and then compute the 50x50 correlation matrix (using the Correl function), using the daily close as the data.

What I have tried so far is just some local-maximum heuristic:

• For the two stocks that have the highest correlation with each other, remove one of them. Between the two, remove the one that has the higher average correlation with all the other stocks.
• When I remove a stock from the pool, I just delete the correponding row and column, to give a smaller matrix.
• Repeat until I have just 10 stocks remaining (a 10x10 matrix).

I was wondering if there is some algorithm that already solves such a problem and gives the optimum solution?

Answer 1

I'm not totally sure from your description, but I think you want to check out Help for the STDEV function. More information also here.

Most people use this to screen out aberrations, whereas you will be screening out the non-aberrations (and this is also a pretty common use, for example identifying "problem cases" in a batch).

Answer 2

The MATRIX package has some useful functions, that would allow you do to principal component analysis (PCA) or even independent component analysis (ICA) to do a more general and principled approach.