- Paste BIL X X AWAY = = = = =" Normal No Spacing Heading 1 Heading 2 Title Dictate Sensitivity Editor Styles Pane Read Only To save a copy of this document, click Duplicate. D Download the historical stock price data of two firms (you can choose any firm you like) and S&P500 (which is the average market price) from Yahoo finance or other public source. Download the daily yield of 1 year (US T-bill rate) from the Department of US Treasury. You could choose any length of period, but it must be longer than six months. Questions: 1. Using those data to estimate the stock's return, volatility, beta; and compare the return, total risk and systematic risk between two stocks. 2. And given the weight you want for each stock, estimating your portfolio's return, volatility, beta. 3. Show me the relation between stock's three elements and your portfolio's. Upload the EXCEL worksheet and explanation or other files that you think helps. You could refer the example that I upload. *Excel formula you might need: Average: = AVERAGE(:) Standard deviation: = STDEV(:) Square root: = SQRT:) *Excel DATA analysis tool you might need: Regression: for bate estimation (both stock and portfolio) Correlation: for correlation to calculate std of portfolio using formula that we learned. Page 1 of 2 495 words English (United States) o Focus E E - Paste BIL X X AWAY = = = = =" Normal No Spacing Heading 1 Heading 2 Title Dictate Sensitivity Editor Styles Pane Read Only To save a copy of this document, click Duplicate. D Download the historical stock price data of two firms (you can choose any firm you like) and S&P500 (which is the average market price) from Yahoo finance or other public source. Download the daily yield of 1 year (US T-bill rate) from the Department of US Treasury. You could choose any length of period, but it must be longer than six months. Questions: 1. Using those data to estimate the stock's return, volatility, beta; and compare the return, total risk and systematic risk between two stocks. 2. And given the weight you want for each stock, estimating your portfolio's return, volatility, beta. 3. Show me the relation between stock's three elements and your portfolio's. Upload the EXCEL worksheet and explanation or other files that you think helps. You could refer the example that I upload. *Excel formula you might need: Average: = AVERAGE(:) Standard deviation: = STDEV(:) Square root: = SQRT:) *Excel DATA analysis tool you might need: Regression: for bate estimation (both stock and portfolio) Correlation: for correlation to calculate std of portfolio using formula that we learned. Page 1 of 2 495 words English (United States) o Focus E E