Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

FIN558 Data Science and Python for Finance

Fall 2023 Project, 20% of total grade.

Instructions.

A. This is a group project. Less than or equal to three people can form a group.

a. Join a “project” group in Canvas ‘people’ menu. A group leader (randomly selected) will submit the files before the deadline.

b. In the past, groups earned better scores than individuals.

c. DO NOT SHARE or DISCUSS your project with anyone who is not in your group.

B. Complete the project and submit the following files to Canvas. Do not compress them.

a. One Excel file that contains all data.

b. One Excel file that contains optimal portfolio’s constituent weights.

c. Python script file (in txt format) with comments. Comment major lines of code so the grader can easily locate each problem. Save the file in TXT format and submit. Python script must work smoothly to earn any credit. If your grader cannot run the python script, your script is incorrect.

d. Run the python script and save the results on the Console as a html file.

e. Summary in MS Word format, Max 3 pages, font size 11, line space: 1.5, moderate margin.

Data Preparation.

1. S&P500 constituent data: You can find the list of ‘permno’ and company names in the “sp500constituent.csv”. In the WRDS query form, select “PERMNO” and check the box before “Browse” and select the constituent file. Download their monthly returns between 2010 and 2022 from WRDS. Query variables to choose: PERMNO, MthRet.

2. S&P500 index monthly return data from WRDS2 from 2010 to 2022. Variable to download is ‘mthtotret’.

3. 30-day treasury bill: download 30-day treasury bill monthly return from the CRSP in the WRDS for the same period.

4. Save each data in separate worksheet in a MS Excel workbook. You do not need to write a code to this part. Feel free to use MS excel to save all data. You will submit this file.

5. From the next part, you must perform all computations using Python and save the script in txt format.

Optimal Risky Portfolio

Part A. data preparation, 10 pts.

1. Open the spreadsheet of the constituent companies returns in python to make a pivot table of return (sp500_rtns). Convert dates in datetime format.

2. Compute average monthly return of S&P500 constituent companies from the pivot table.

3. Construct variance and covariance matrix of returns of the constituent companies from the pivot table.

4. Save the number of constituents to a variable ‘N’.

Part B. Custom functions, 15 pts.

5. Write a function that returns the expected return of a portfolio: F_PortRtn(r_i , w_i)

a. Input parameters are average returns of stocks and their weights in a portfolio.

b. It should return the expected return of a portfolio.

6. Write a function that computes the standard deviation of a portfolio: F_PortStd(cov, w_i)

a. Two input parameters: 1) cov: variance-covariance matrix of stock returns, 2) weights of stocks in a portfolio.

b. Hint: matrix multiplication is easier than using ‘for’ or ‘while’ loop.

7. Write a function that returns the Sharpe ratio of a portfolio: F_Sharpe(r_p, r_f, s_p). E[rp − rf]/σp

a. Three input parameters: 1) r_p: return of the portfolio (Output from F_PortRtn), 2) r_f: risk free asset return, 3) s_p: standard deviation of a portfolio (Output from F_PortStd).

b. For the numerator of the Sharpe ratio, it should compute the excess returns of each month to find the average.

c. Denominator is the standard deviation of the portfolio returns.

Part C. Simulation, 15 pts.

8. Generate 1,000 sets of ‘N’ random numbers following uniform distribution between 0 and 1. 3 Set the seed of the random number generator as the lowest student ID number of your group members’. In each set, divide random numbers by the total of the random numbers in the set. Now, you have random weights of stocks.

9. Using ‘for’ or ‘while’ loop, compute Sharpe ratio of each simulated portfolio.

a. Maintain good records of portfolio weight combination and Sharpe ratios.

b. You need to use functions in Part B.

c. You must have 1,000 Sharpe ratios.

10. Find the maximum Sharpe ratio and the corresponding weights of constituent stocks.

11. Report the following in a Separate MS Excel spreadsheet.

a. Optimal portfolio’s constituent weights. Provide their “permno” and names.

Part D. Report, 10 pts.

Write a report that summarizes what you have done. It should consist of 1) introduction, 2) description of data, 3) Methodology, 4) Result, 5) Conclusion. Make sure to write members’ names and title.