Question

Obtain the last five years (ending in December 31, 2022) of weekly stock prices for symbols SPY (S&P500 tracking stock) and SHY (short government bonds index tracking stock) and two other stock of your choice (pick large companies, for example Dow Jones Industrial Average components). In Excel, using the prices adjusted for dividends compute the stock weekly returns. A. Using Excel function (Average(..), VAR.S(..), STDEV.S(…)) report on a separate sheet report the weekly average return, variance and standard deviation. b. For each stock create a Histogram (make the histogram with at least 20 or more bins (make the same bins for all securities, make sure the bin wide enough for all securities. If you make all bins the same, the histogram will show risk very clearly). Compare and discuss briefly, the results for SPY and SHY.

Answer 1

The student is tasked with analyzing historical weekly stock prices for SPY and SHY, along with two other large stocks, and to calculate and compare the weekly returns using Excel. This includes generating a histogram for each to assess their risk profiles based on return distributions. The S&P 500 and DJIA are key benchmarks for measuring stock market performance based on large companies' stock prices.

Step-by-step explanation:

The question pertains to performing stock analysis using historical data for specific tickers SPY (S&P 500 tracking stock) and SHY (short government bonds index tracking stock) along with two other large companies. The data analysis involves computing weekly returns, average returns, variance, and standard deviation of the stock prices adjusted for dividends using Excel functions. Additionally, you will create a histogram with at least 20 bins for each stock to visually compare the distribution of returns, which can provide insights into the risk profile of each security.

The Standard & Poor's 500 (S&P 500) and the Dow Jones Industrial Average are essential measures to understand how the stock market performs by following the prices of a specific number of the largest U.S. companies' stocks. The selected stock prices formulating the S&P 500 are based on the weighted average market capitalization, while the DJIA is a price-weighted average, both demonstrating overall market trends.