To begin with go to the ticker assignments tab. Find your name at the top of one of the columns. In the column below that are the tickers that go into your portfolio. If you go to the Source Data tab you will see over 300 firms listed and each one has data, in one column each per firm (Ticker). What you need to do is to copy the each firm’s return data column that matches your ticker assignments over to the Prjct Data page. This will be easier to do if you split your screen and add a new view of your spreadsheet so that you can see both tabs at the same time. (copy from View:2 to View:1)
Once you have the source data page completed with 40 stocks in it, you can click the green square in the upper left corner called Create VCOV. Click that and it will create your covariance matrix on a new tab and it will name that matrix (“var_cov”) so you are all ready to go.
Next, on the Market Data page you are going to have to look up the price and the beta of each of your 40 stocks I tried to find some web links but apparently they are not easy to get those anymore. There is already data there, but it is artificial. (I just made up random numbers so that the rest of the project would run).
Finally go to the Equity page which is the actual portfolio page. Go ahead and take a look all around but where you are going to start is in columns B and C and D in rows 61. There, you need to enter the portfolio return and standard deviations for two portfolios made up of the stocks above. The Co-variance is already calculated. The Stock Returns are in a vector named “Stk_Ret”, the covariance matrix is called “var_cov”, and the two sets of security weights are called “W_1” and W_2”. Use these portfolios to construct your graph and add your arbitrarily weighted portfolio, maximum utility portfolio and and maximum Sharpe Ratio portfolio. The Sharpe Ratio is (Rp – Rf)/p. The result portfolios will end up in cells J67 through L71. You can plot these on the graph as well. Finally, add a tangent CAL and an indifference curve. I computed the CAL for you. At this point your graph should look a lot like what we ended up with in our labs and home works (plus the CAL). Next comes the fun part!
Go back to the top portfolio (lines 6 – 45). If you done everything so far, then this top portfolio should be reflecting a final client portfolio. If it does not, you have to go back and repeat the first parts of and re-optimize the two optimal portfolios anyway.
IMPORTANT!!! This top portfolio is set dirrerently than the others. When you optimize it you are going to change the number of shares NOT the weights. The weights will automatically add 100%, but the value of the portfolio is in cell C2 and it will have to be constrained to be less than C1 and greater than C3.
Next record three macros. One will maximize the return on the portfolio by changing the number of shares held, another will maximize the utility, and the third will maximize the Sharpe ratio. Plot this single resulting portfolio on the chart, (so that when you optimize in different ways the portfolio will move to different locations – Not three different dots on the chart).
Next, make a Userform. It should contains buttons to run the macro that optimizes the two portoflios, and one each for Max Return, Max Utility and Max Sharpe Ratio.
Then add some bells and whistles like a scrollbar that changes the arbitrary weight in cell J67, and another scrollbar the changes the risk aversion parameter a in cell C50 also called A. As a final matter, you can experiment with putting constraints on industry classifications. In the blue cells in rows 50 and 51 you can set the minimum and maximum values for an industry sector weight.
Here is my form:
It is not very pretty, but it works
Module 1 contains my code, but you should write your own. I’d prefer you remove Module 1 before you start. Keep the Var_Cov_Macro module. it contains code for making the covariance matrix.
If there are duplications of firms in your list, or if any of the firms have data like price or beta that cant be found, then just pick another firm. The idea is that you each have 40 different sets of stocks (it is OK to have overlap).
