Deliverables and Output33

pdf

School

McMaster University *

*We aren’t endorsed by this school

Course

1DA3

Subject

Finance

Date

Jan 9, 2024

Type

pdf

Pages

Uploaded by Rabiddog

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. With tickers STZ ADP JCI BKNG AMGN CPT JKHY ALGN AMZN LUV COP ORLY BIIB MS GD MTB HRL TEL ABNB ISRG TROW KO RSG NRG LIN 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 tickers STZ ADP JCI BKNG AMGN CPT JKHY ALGN AMZN LUV COP ORLY BIIB MS GD MTB HRL TEL ABNB ISRG TROW KO RSG NRG LIN . 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 with tickers STZ ADP JCI BKNG AMGN CPT JKHY ALGN AMZN LUV COP ORLY BIIB MS GD MTB HRL TEL ABNB ISRG TROW KO RSG NRG LIN . • 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 with tickers STZ ADP JCI BKNG AMGN CPT JKHY ALGN AMZN LUV COP ORLY BIIB MS GD MTB HRL TEL ABNB ISRG TROW KO RSG NRG LIN • 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. Write code for all parts in a way an amateur coder should write. 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 ([Equation]is calculated from the equation below: 𝑤? ,2010= ??𝑧?? ,2009∑ ???𝑧?? ,2009 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 ([Equation] calculate the fund return ([Equation] 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. Write code for all parts in a way an amateur coder should write.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version