Perhaps this question is too difficult. But I’m going to ask it anyway. It is part of a Markowitz mean variance optimisation using Solver. So perhaps only people who do investment stuff will be able to answer it.
Every month I want to form a portfolio of 10 stocks chosen from a market of 50 stocks. I have already chosen the 10 stocks for month 1 based on their expected return. But I don’t want them equally weighted, I want to have them in the proportions that give me the most efficient portfolio, that is the highest return for the lowest possible standard deviation. So I also have a variance covariance matrix that is used in calculating the standard deviation. I also have a constraint that no individual stock can make up more than a certain proportion of the portfolio. This is all pretty straightforward. I have done this before using matrix multiplication in Solver.
However this time I have to deal with an added restriction & I can’t work out how to do it. Each stock belongs to a particular industry. The constraint is that in the efficient portfolio for the month, stocks belonging to a certain industry must have a total weight in the portfolio that is within a boundary set by the proportion of that industry in the entire market.
An example. In the market of 50 stocks there are 7 that belong to “Auto” & together they make up 11% of the market, based on market capitalisation. So in my portfolio, any “Auto” stocks, when totaled, must have a weight that is within 3% of the market proportion. So in this case all the “Auto” stocks in my portfolio must have a combined representation in this months portfolio of between 8% and 14%. No lower & no higher.
My expected return column has 2 columns next to it. The first gives the stocks identifying number (rather than its name, we just label them 1 to 50). The next column gives the industry it belongs to. What I can’t figure out is how to set up Excel so that Solver will apply this constraint. Someone showed me that I can set up 3 rows, where the stocks are divided into their industries. The first row is the stocks in the portfolio, the second is the stocks in the market & the third is the difference between them. But I can’t figure out how Solver can still identify the stocks once I have put them out of order. In the expected return column they have to stay in the order of expected return as this also relates to the variance covariance matrix. I even thought of running the industry restriction as a second run of Solver, but I don’t think that would work. I think it will only be efficient if I run the whole optimisation in one go.
Is anyone game to take this on?
Every month I want to form a portfolio of 10 stocks chosen from a market of 50 stocks. I have already chosen the 10 stocks for month 1 based on their expected return. But I don’t want them equally weighted, I want to have them in the proportions that give me the most efficient portfolio, that is the highest return for the lowest possible standard deviation. So I also have a variance covariance matrix that is used in calculating the standard deviation. I also have a constraint that no individual stock can make up more than a certain proportion of the portfolio. This is all pretty straightforward. I have done this before using matrix multiplication in Solver.
However this time I have to deal with an added restriction & I can’t work out how to do it. Each stock belongs to a particular industry. The constraint is that in the efficient portfolio for the month, stocks belonging to a certain industry must have a total weight in the portfolio that is within a boundary set by the proportion of that industry in the entire market.
An example. In the market of 50 stocks there are 7 that belong to “Auto” & together they make up 11% of the market, based on market capitalisation. So in my portfolio, any “Auto” stocks, when totaled, must have a weight that is within 3% of the market proportion. So in this case all the “Auto” stocks in my portfolio must have a combined representation in this months portfolio of between 8% and 14%. No lower & no higher.
My expected return column has 2 columns next to it. The first gives the stocks identifying number (rather than its name, we just label them 1 to 50). The next column gives the industry it belongs to. What I can’t figure out is how to set up Excel so that Solver will apply this constraint. Someone showed me that I can set up 3 rows, where the stocks are divided into their industries. The first row is the stocks in the portfolio, the second is the stocks in the market & the third is the difference between them. But I can’t figure out how Solver can still identify the stocks once I have put them out of order. In the expected return column they have to stay in the order of expected return as this also relates to the variance covariance matrix. I even thought of running the industry restriction as a second run of Solver, but I don’t think that would work. I think it will only be efficient if I run the whole optimisation in one go.
Is anyone game to take this on?
