steps for Q4, 5,6 in excel
1. Go to finance yahoo.com. Here you will choose three stocks and download their price histories for one year 2. On the right of the webpage, there is a Quote Lookup bar. Enter the ticker stock symbol) search here. How to choose the tickers 2. Type the first letter of your first name - the search bar will offer some suggested tickers - choose one b. Type the first letter of your last name - again choose a stock that begins with this letter If you have a middle name, choose your third stock based on your middle Initial, if not, use the second letter of your last name d. If in any of the case above, there is no stock available with your first letter, move to the next letter in your name, and so on. 3. For each stock: click on the one you have chosen-it will open the stock quote page. On this page, there is a top row consisting of several links, including Historical data." Choose this link, then check that the top row settings show a time period going back 1 year (starting today's date) and frequency - daily. Remember to check that you are getting one year of data. If this is not available, choose a different stock (still using the same initial for which 1 year of data is available. Now click on download data." You will have a csv file, which opens with Excel 4. Remember to check that the dates for the three stocks match - if there are misaligned observations, please remove them so that all three stocks have the same one year of data 5. Go to fred stlouisfed.org and search for UBOR. Choose the 3-month USD LIBOR and download the data in Excel for the past 1 year. Now take an average of the rate (note: the data needs to be cleaned of missing values first), and store this as Questions: MVPT 1. Use the Adj close column (these prices are adjusted by the dividend yieldto calculate the daily returns Rein P-IP- 2 Calculate the average returns, variance and the standard deviation of the returns in your sample 3. Calculate the covariance and the correlation coefficients of the stocks 4. For a range of weights between 1 and 1 for two of the stocks (the other is automatically determined so the weights sum to 1). calculate the average return and the standard deviation of the portfolio . You may do this manually, or use the Data Table functionality in Excel Consider w; to be a row input and w, to be a column ingut then w; 1-w-wy. You can then generate a range of values of standard deviation using the formula and the same for the return. You will have one matrix each for return and standard deviation. For the Data Table. please use the help and tutorial in Excel to see exactly how to enter the formula, in case you are not familiar with it. You will find Data Tables under "What-af analysis in the "Data" tabin the new version of Excel. 5. You can now create two columns using all the pairs of points (return, standard deviations and produce a scatter plot. You can now mark the efficient frontieron the graph 6. What is the global minimum variance portfolio? Please provide the approximate weights of each stock based on your excel calculations Questions: MVPT 1. Use the Adj Close column (these prices are adjusted by the dividend yield) to calculate the daily returns. Rp = In P - In Pt-1 2. Calculate the average returns, variance and the standard deviation of the returns in your sample. 3. Calculate the covariance and the correlation coefficients of the stocks 4. For a range of weights between-1 and 1 for two of the stocks (the other is automatically determined so the weights sum to 1), calculate the average return and the standard deviation of the portfolio. You may do this manually, or use the Data Table functionality in Excel. Consider w, to be a row input and w, to be a column input, then wz = 1 - W - W2. You can then generate a range of values of standard deviation using the formula and the same for the return. You will have one matrix each for return and standard deviation. For the Data Table, please use the help and tutorial in Excel to see exactly how to enter the formula, in case you are not familiar with it. You will find Data Tables under "What-if analysis" in the "Data" tab in the new version of Excel. 5. You can now create two columns using all the pairs of points (return, standard deviations) and produce a scatter plot. You can now mark the efficient frontier on the graph. 6. What is the global minimum variance portfolio? Please provide the approximate weights of each stock based on your excel calculations. CAPM 7. Using the covariances found earlier and the risk-free rate ry, please find the 1. Go to finance.yahoo.com. Here you will choose three stocks and download their price histories for one year. 2. On the right of the webpage, there is a Quote Lookup bar. Enter the ticker (stock symbol) search here. How to choose the tickers: a. Type the first letter of your first name - the search bar will offer some suggested tickers - choose one. b. Type the first letter of your last name - again choose a stock that begins with this letter C. If you have a middle name, choose your third stock based on your middle initial. If not, use the second letter of your last name. d. If in any of the case above, there is no stock available with your first letter, move to the next letter in your name, and so on. 3. For each stock: click on the one you have chosen - it will open the stock quote page. On this page, there is a top row consisting of several links, including "Historical data." Choose this link, then check that the top row settings show a time period going back 1 year (starting today's date) and frequency = daily. Remember to check that you are getting one year of data. If this is not available, choose a different stock (still using the same initial) for which 1 year of data is available. Now click on "download data." You will have a csv file, which opens with Excel. 4. Remember to check that the dates for the three stocks match - if there are misaligned observations, please remove them so that all three stocks have the same one year of data. 5. Go to fred.stlouisfed.org and search for LIBOR. Choose the 3-month USD LIBOR and download the data in Excel for the past 1 year. Now take an average of the rate (note: the data needs to be cleaned of missing values first), and store this as ry. 1. Go to finance yahoo.com. Here you will choose three stocks and download their price histories for one year 2. On the right of the webpage, there is a Quote Lookup bar. Enter the ticker stock symbol) search here. How to choose the tickers 2. Type the first letter of your first name - the search bar will offer some suggested tickers - choose one b. Type the first letter of your last name - again choose a stock that begins with this letter If you have a middle name, choose your third stock based on your middle Initial, if not, use the second letter of your last name d. If in any of the case above, there is no stock available with your first letter, move to the next letter in your name, and so on. 3. For each stock: click on the one you have chosen-it will open the stock quote page. On this page, there is a top row consisting of several links, including Historical data." Choose this link, then check that the top row settings show a time period going back 1 year (starting today's date) and frequency - daily. Remember to check that you are getting one year of data. If this is not available, choose a different stock (still using the same initial for which 1 year of data is available. Now click on download data." You will have a csv file, which opens with Excel 4. Remember to check that the dates for the three stocks match - if there are misaligned observations, please remove them so that all three stocks have the same one year of data 5. Go to fred stlouisfed.org and search for UBOR. Choose the 3-month USD LIBOR and download the data in Excel for the past 1 year. Now take an average of the rate (note: the data needs to be cleaned of missing values first), and store this as Questions: MVPT 1. Use the Adj close column (these prices are adjusted by the dividend yieldto calculate the daily returns Rein P-IP- 2 Calculate the average returns, variance and the standard deviation of the returns in your sample 3. Calculate the covariance and the correlation coefficients of the stocks 4. For a range of weights between 1 and 1 for two of the stocks (the other is automatically determined so the weights sum to 1). calculate the average return and the standard deviation of the portfolio . You may do this manually, or use the Data Table functionality in Excel Consider w; to be a row input and w, to be a column ingut then w; 1-w-wy. You can then generate a range of values of standard deviation using the formula and the same for the return. You will have one matrix each for return and standard deviation. For the Data Table. please use the help and tutorial in Excel to see exactly how to enter the formula, in case you are not familiar with it. You will find Data Tables under "What-af analysis in the "Data" tabin the new version of Excel. 5. You can now create two columns using all the pairs of points (return, standard deviations and produce a scatter plot. You can now mark the efficient frontieron the graph 6. What is the global minimum variance portfolio? Please provide the approximate weights of each stock based on your excel calculations Questions: MVPT 1. Use the Adj Close column (these prices are adjusted by the dividend yield) to calculate the daily returns. Rp = In P - In Pt-1 2. Calculate the average returns, variance and the standard deviation of the returns in your sample. 3. Calculate the covariance and the correlation coefficients of the stocks 4. For a range of weights between-1 and 1 for two of the stocks (the other is automatically determined so the weights sum to 1), calculate the average return and the standard deviation of the portfolio. You may do this manually, or use the Data Table functionality in Excel. Consider w, to be a row input and w, to be a column input, then wz = 1 - W - W2. You can then generate a range of values of standard deviation using the formula and the same for the return. You will have one matrix each for return and standard deviation. For the Data Table, please use the help and tutorial in Excel to see exactly how to enter the formula, in case you are not familiar with it. You will find Data Tables under "What-if analysis" in the "Data" tab in the new version of Excel. 5. You can now create two columns using all the pairs of points (return, standard deviations) and produce a scatter plot. You can now mark the efficient frontier on the graph. 6. What is the global minimum variance portfolio? Please provide the approximate weights of each stock based on your excel calculations. CAPM 7. Using the covariances found earlier and the risk-free rate ry, please find the 1. Go to finance.yahoo.com. Here you will choose three stocks and download their price histories for one year. 2. On the right of the webpage, there is a Quote Lookup bar. Enter the ticker (stock symbol) search here. How to choose the tickers: a. Type the first letter of your first name - the search bar will offer some suggested tickers - choose one. b. Type the first letter of your last name - again choose a stock that begins with this letter C. If you have a middle name, choose your third stock based on your middle initial. If not, use the second letter of your last name. d. If in any of the case above, there is no stock available with your first letter, move to the next letter in your name, and so on. 3. For each stock: click on the one you have chosen - it will open the stock quote page. On this page, there is a top row consisting of several links, including "Historical data." Choose this link, then check that the top row settings show a time period going back 1 year (starting today's date) and frequency = daily. Remember to check that you are getting one year of data. If this is not available, choose a different stock (still using the same initial) for which 1 year of data is available. Now click on "download data." You will have a csv file, which opens with Excel. 4. Remember to check that the dates for the three stocks match - if there are misaligned observations, please remove them so that all three stocks have the same one year of data. 5. Go to fred.stlouisfed.org and search for LIBOR. Choose the 3-month USD LIBOR and download the data in Excel for the past 1 year. Now take an average of the rate (note: the data needs to be cleaned of missing values first), and store this as ry
