The exercise is to build a portfolio optimization system that can display efficient frontiers to a decision maker. As this includes many repetitive tasks, one should try to automate them with macros as much as possible.
Content-wise, the project involves
- Retrieval of price data
- Calculation of covariance matrix and historical mean return
- Minimum of 20well distributed points used to characterize efficient frontier
- Display efficient frontier
- Table describing each portfolio option, the associated expected return and the portfolio standard deviation
- Bonus:
- Allow re-runs with different numbers of points, different forecasted returns, and maybe even different constraint right hand sides
- option to override historical mean returns with forecasted returns that may be different
For good results, you probably need to work with 20 or more securities. Try to include in your model realistic-type sector, industry, and upper bound constraints, but don't make them too tight.