using Excel VBA, given the daily price of 50 stocks, choose 10 stocks such that they have the minumu
- by correl
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?