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

MIDTERM 2: FINANCIAL MODELING

Objective

You are acting as a fund manager. The goal is to report on the performance of your mutual fund to your investors. Your fund includes 25 companies that I have chosen for your fund. Please find the list of companies that are assigned to your mutual fund in the file Company_Student_List.xlsx. The first sheet includes the ticker of the company and the strategy of your fund. The second sheet includes the names and other information of the companies. All firms are from the S&P500 index.

You should submit your work in an Excel (.XLSX file) and Python script (.PY file) on A2L before the deadline. THERE WILL BE NO EXTENSION. The files should be named by your name and ID: First_lastName_ID.

Deliverables and Output:

1. Download Data

· Download Historical stock prices for all companies in your portfolio at the daily frequency from January 2002 to December 2022 from Yahoo Finance. Save close and adjusted close prices as well as Volume in separate sheets in an Excel file; sheet_names “Price_daily”, “Adj_Price_daily”, and “Volume_daily”.

o If, for any reason, Yahoo Finance does allow you to access the data of a group of firms for the whole 21 years, download as much as it provides.

o The preferred method is to use the Pandas_datareader/yfinance to directly connect to the Yahoo Finance website from Python. In your code, read Company_Student_List.xlsx, and search for your name to find the list of ticker names assigned to you.

o If you cannot perform this with these libraries, search for the tickers assigned to you on the Yahoo Finance website and manually download the data from the Yahoo Finance website. Save the data for each firm in separate CSV files. Then read these files to Python and convert them to timeseries dataFrames. Select the Close price columns for each company and merge them all by Date. Save this dataFrame in the output Excel file, sheet “Price_daily”. Do the same for the adj. close prices and volume data.

· Download prices for the S&P 500 index (^GSPC) from Yahoo Finance similarly and save it in an Excel sheet “S&P 500” on the same file. This is our proxy for the market portfolio.

2. Calculate Firm Information

· Calculate the market capitalization of each firm per year. That is, multiply the number of shares outstanding by the close prices on the last trading day of each year to get the total market capitalization of each firm per year. The Nov 2023 shares outstanding data is in sheet S&P 500 Constituents in the Company_Student_List.xlsx file. [Yahoo Finance does not provide historical data for the outstanding shares, so here, we assume the companies did not issue new shares or had no share splits.] Save the total market capitalization of firms in a new sheet and call it “Size” in the excel file.

· Calculate the sum of daily Volume for each firm per year, divide it by the total shares outstanding, and save it in the sheet “Liquidity_annual”. This is a measure of how frequently a stock is traded, and generally, it is perceived as a measure of its liquidity.

· Using adjusted close prices at the annual, monthly, and daily frequencies compute the annual, monthly, and daily returns. Save them in new sheets labeled “Returns_annual”, “Returns_monthly”, and “Returns_daily”.

· Using Returtns_daily, calculate the standard deviation of each stock in each year. Save these in a new sheet called “Risk_annual”.

3. Provide summary statistics for your portfolio holdings (in sheet “Firm_Summary_Stat”):

· For each firm, report its Minimum, Maximum, Mean, and volatility of returns, annualized.

· For each firm, report the market capitalization (label it size) at the end of your sample,

· For each firm, report the industry of each company (use the information in sheet S&P 500 Constituents in the Company_Student_List.xlsx file for the industry information of each company).

· Compute and report the market beta for each firm using the last 5 years (2018:2022) return data, assuming the risk-free rate is 0 at the monthly frequency.

· For each firm, report the market Beta in Nov 2023 (use the information in sheet S&P 500 Constituents in the Company_Student_List.xlsx file).

· Compare the beta values that you calculate with the Beta information in the sheet S&P 500 Constituents (Nov 2023 prices). Report how different these values are in the same Excel sheet.

4. Portfolio Analysis

Each fund has its own investment strategy and rebalances its portfolio weights annually. The fund strategy is given in the sheet Student_Tickers in the Company_Student_List.xlsx file. Below are the general definitions of these strategies.

Strategy = Size: Every January, invest more in firms that were larger in December last year.

Strategy = Liquidity: Every January, invest more in firms that had a larger liquidity last year.

Strategy = Return: Every January, invest more in firms that had a larger return last year. If they had negative returns, do not invest in them this year.

Strategy = Risk: Every January, invest more in firms with a lower risk last year. Here, first, generate a new measure for the firms by dividing 1 over their last year's risk (as measured above), call it inverse_volatility. Then, invest more in firms with a larger inverse_volatility value.

Strategy = Equal: Every January, invest equally in all firms.

· Construct your portfolio by investing in each firm with respect to the fund’s strategy from January 2003 to December 2022. Save the monthly returns of your portfolio in the sheet “PortfolioReturn_monthly”. For this task, you need to

o Find the portfolio weights for each year based on firms’ information in the previous year. For example, for the Size strategy, the portfolio weight for firm j for the Year 2010 (is calculated from the equation below:

This means you need to find the sum of the market capitalization of all firms per year (the denominator). Then, calculate the above ratio, which shows the relative size of firm j with respect to the other firms. For the Liquidity strategy, substitute size with Volume per total share outstanding (Liquidity measure that you previously calculated) in the above equation. Do similarly, for Return and Risk strategies. For the Equal strategy, first, find how many of the firms had return data in the past year. Then invest equally in them. If there is no data for a firm in that year, then adjust the weight accordingly for all firms.

o Using this weight (which is calculated from the previous year’s data) and monthly firms’ returns ( calculate the fund return ( from January to December of this year

o Repeat the above process the next January.

· Report the summary statistics of your portfolio and S&P 500 index return in sheet “Fund_summary”:

o Report the average, standard deviation, minimum, and maximum of these portfolio returns in annual percentage rates,

o Calculate and report their Alpha, Beta, R2, with respect to S&P 500 index, using the whole length of data (i.e. since 2003)

o Calculate and report their Sharpe ratio, Treynor ratio.,

· Report industry composition in sheet “Funds_Holdings_Composition”

o In Excel, report at the end of the sample, how much you’ve invested in each firm, in percentage.

o In the same sheet, plot a pie chart which shows, at the end of the sample, how much you’ve invested in each industry, in percentage.

· Plot the fund’s performance

o Plot the histogram for your funds return as well as the S&P 500 index returns with 20 bins.

o Plot the cumulative return of your portfolio and S&P 500 from January 2003 to December 2022. This is equivalent to calculating the value of your fund if you start the fund with $1 in January 2003. For the S&P 500, find its monthly return similar to the other stocks.

o Plot the annual return of your fund (i.e. the sum of monthly returns per year) and the S&P 500 using bar plots, on the same graph.

Your code should be self-explanatory and should run on my device without an error. Answer each question in a separate cell and add a few lines to describe what you do in that cell. Do not randomly copy pieces of codes from the TeachingNote. Make sure the grader can understand your work and your calculations. If the grader cannot understand what you’ve done, then she cannot evaluate your work and you will lose points or lose the chance to get partial marks for your incomplete work.

If you use external files (say some excel files) submit them with your code too.

You should also submit the output of your code in Excel. Manually copy the graphs to an Excel sheet.