Using MySQL Workbench and frameworks used in class, provide advice and recommendations for the following High Net Worth Client.
Your investor is an Ultra High Net Worth client, based in Palo Alto, CA. Your investor has the following assets:
– Liquid assets – total of 95M (mainly listed on exchanges in the US):
TickerName% allocationAsset classIXNIshares Global Tech EtfLinks to an external site.17.5%EquityQQQNASDAQ 10022.1%EquityIEFiShares 7-10 Year Treasury Bond ETF28.5%Fixed IncomeVNQVanguard Real Estate ETF8.9%Real AssetsGLDSPDR Gold Shares 23%Commodities
Please provide full return and risk analysis for this portfolio. Make sure to use frameworks and metrics covered in class. Provide recommendations on rebalancing.
Details regarding what’s expected for your deliverable:
Submit one PDF file with screenshots, SQL code, results, your descriptions, explanations, recommendations, and advice :
Step1: Download data for client tickers – this should be daily pricing for all the tickers (you can use the quantmod library from R covered in class, or a different Python package) – please include your R code and screenshots of results in your PDF paper.
Step 2: Load that data (from step1) into a new table in your own database, creating a new schema. Please include a screenshot of your new table in MySQL Workbench.
Step 3: Using SQL syntax, answer 5 groups of questions listed below (you will get a total of 100 points, 20 points per question):
Here are 5 groups of questions that will help you structure your paper (worth 100 points) :
1. (20 points) What is the most recent 12M**, 18M, 24M (months) return for each of the securities (and for the entire portfolio)?
2. (20 points) What are the correlations between your assets? Are there any interesting correlations? – the most recent MySQL environment cannot execute the corr() function as of 2022, though it might be fixed and working by the time you take the assignment. If it is not working, then feel free to calculate the variance for each ticker and compare the variances side by side for the most recent few months (you decide how large time window to use).
3. (20 points) What is the most recent 12M sigma (risk) for each of the securities (and for the entire portfolio)?
4. (20 points) Based on the previous 3 questions, which holdings would you sell, which holdings would you buy? Are there any outside securities that you would recommend adding to the portfolio?
5. (20 points) How will your portfolio risk and expected returns change after rebalancing (selling and buying) including any new security?
Each of the 5 questions is worth 20 points – giving a total of 100 points. You will not get additional points for steps 1 and 2. However, failure to perform steps 1 or 2 will results in a failure of the assignment.
Make sure to provide very detailed recommendations for your client. Explain each result in a simple fashion. I will be looking for correct SQL procedures, syntax, logic, correct results for different time periods, your descriptions, explanations, recommendations, advice, and logic in your paper.
Footnotes (very important):
* make sure you use the time window (12M, 18M, 24M) for your calculations as procedure parameter/input.
**12M means 12 months = 1 year.