Any part with a single asterisk (*) is for extra credit; any part with a double asterisk (**) will not be graded and only serves as food for thought.
For all questions involving coding, you are free to use existing codes and spreadsheet models for the numerical examples in notes/slides; you can choose either VBA or Python.
For all questions involving coding, hand in the spreadsheet together with the VBA codes or Python codes. You may print the spreadsheet and the VBA codes, or Python codes, as hard copies and hand in it with solutions/answers for other problems. You can also include the spreadsheet with VBA codes in a single .xlsm file (if you choose VBA), or putting all Python codes in a zipped file (if you choose Python), and email it to the Teaching Assistant (remember to include your name in the email).
Students are allowed to discuss and collaborate for the problems, but each student is required to independently prepare the codes and the answers.
Dr. Wang, Liao
Assignment 2 (Part 1)
Due: January 16, 2024 (Tuesday), 11:59pm
MSBA7017 Financial Engineering
Coding: Build Your Own Markowitz Portfolio (40%)
Download the file stockData.xlsx from Moodle. Below is a description of data:
The data contains (i) stock prices of the component firms of S&P500 index; and (ii) prices of a fund that tracks S&P500, and the ticker is “IVV” (“ticker” means the symbol for that instrument); IVV is a proxy for market portfolio.
There are two spreadsheets in the dataset. On each sheet, the first column is the row number; the second column is the date on which the prices are observed (the dates are
Code Help, Add WeChat: cstutorcs
already sorted from the oldest to the newest); the last column contains the prices of IVV. All columns in between are the prices for the individual stocks.
Each row contains prices observed for all tickers. For example, on the first sheet “allStock daily”, the second row means: on 1/3/2012, “ORCL” (this is for Oracle Corporation) has price USD25.865, “MSFT” (this is for Microsoft Corporation) has price USD26.765, and so on; and the price for one tradable unit of IVV is USD128.02.
The first sheet, “allStock daily”, contains daily prices; and the second sheet, “all- Stock monthly” contains monthly prices.
Complete the following tasks:
(a). Write a function, getMarkowitzWeights Stocks, which implements Markowitz Model 1. That is, the inputs are μ (the mean vector of returns for risky assets), Σ (the co- variance matrix of returns for risky assets), and μ (the investor’s required return). The output is a portfolio weight vector that achieves an expected return at μ. Note the output portfolio does not include the risk-free asset.
(b). Pick at least two tickers. You may randomly select the tickers; you may also select the tickers based on your own selection criteria, and if you choose to do so, state your criteria. You may choose to or not to involve the market portfolio (i.e. IVV); but it is recommended that you include IVV since you need this ticker for Black-Litterman Model in part (c) below. Also, you cannot pick exactly the same set of tickers as those in the numerical examples in class.
* You will receive extra credit if you impose some reasonable stock picking criteria. If you want to look into the data to help with stock picking, use data over 2012-2015 only. The cut-off points for this range are highlighted on both spreadsheets.
(If you choose not to, that is, if you choose to randomly select tickers, you will not be penalized in grading.)
(c). On the tickers you pick, specify in total at least one view under Black-Litterman Model. You may express the view(s) randomly; or express views based on some reasoning deemed reasonable by you, and if you choose to do so, state your reasoning.
Programming Help, Add QQ: 749389476
* You will receive extra credit if your view(s) are well-motivated. If you want to look into data to generate views, use data over 2012-2015 only. The cut-off points for this range are highlighted on both spreadsheets.
(If you choose not to, that is, if you choose to randomly express the view(s), you will not be penalized in grading.)
(d). Implement Black-Litterman Model based on (b) and (c). That is, the assets include all the tickers you pick in (b), and the views are those expressed in (c). To do this, you need to estimate Σ, specify π, specify P and ν. If you do not have an idea on how to set τ, use τ = 1/48; likewise, if you do not have an idea on how to set c, set c = 1. You may impose other values on τ and c deemed reasonable by you.
To do this part, use data over 2012-2015 only. The cut-off points for this range are highlighted on both spreadsheets. The investment period is one month, and the one- month risk-free rate is μf = 0.004%.
(e). Combine the outputs from (d) with Markowitz Model 2 for a one-month investment period. That is, pick an investment need μ, use getMarkowitzWeights to generate the optimal portfolio weights.
(f). Perform portfolio analytics on monthly data over 2016 for your portfolio in (e). You can do it in the same format as Slide 61 of Topic2a.
Note: some tickers have experienced share splits during the past years. For example, one share of $100 becomes two shares of $50. This does not affect the value of the stock holding, but the dataset is not adjusted for that. For example, the price in the dataset before the split is $100, and after the split becomes $50, but this does not imply a return of −50%. IVV did not experience the splits. Only if you want to use machine learning techniques for extra credit parts, you may want to exclude the outliers in the return realizations.
Code Help