使用 Excel VBA，给定 50 只股票的每日价格，选择 10 只股票，使它们具有最小相关性
高层目标是从 50 只股票中选择 10 只彼此相关性最低的股票，这样我就可以拥有一个多元化的投资组合。
我设法编写了一些 VBA 宏来从雅虎财经下载过去 3 年的每日价格数据，然后使用每日收盘价作为数据来计算 50x50 相关矩阵（使用 Correl 函数）。
到目前为止我所尝试的只是一些局部最大启发式：
- 对于彼此相关性最高的两只股票，删除其中一只。在两者之间，删除与所有其他股票平均相关性较高的一只。
- 当我从池中删除股票时，我只需删除相应的行和列，即可得到较小的矩阵。
- 重复此操作，直到只剩下 10 只股票（10x10 矩阵）。
我想知道是否有某种算法已经解决了这样的问题并给出了最佳解决方案？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
根据您的描述，我不太确定，但我认为您想查看 STDEV 函数的帮助。更多信息还可此处。
大多数人用它来筛选像差，而您将筛选出非像差（这也是一种非常常见的用途，例如识别批次中的“问题案例”）。
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).
MATRIX 包有一些有用的功能，可以让您进行主成分分析（ PCA）甚至独立成分分析（ICA）来做更通用和原则性的方法。
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.