Module 3 Stock Price Data Background In this module you will be working with stock price data for Amazon com This stock is listed on the NASDAQ Stock Market the second largest U S stock market after the New York Stock Exchange You will analyze the change in the stock prices over time as well as the value of investments over time The Excel workbook for this module consists of 2 tabs 1 The source tab 2 The Table tab contains the full history of Amazon stock prices from its start in May 1997 until November 2017 For each trading day of the stock a record of each of the following is posted Open the price when the market opened in the morning Close the price when the market closed in the afternoon High the highest price during that trading day Low the lowest price during that trading day Volume number of shares of the stock traded that day Adj Close Adjusted Close a price adjusted to make prices comparable over time Note that the dates are listed from most recent to least recent The Adjusted Close price on specific day reflects all the dividends and splits since that day If no such dividends or splits have occurred since that day the adjusted close equals the close on that day The change in adjusted closing price is used to calculate the total return of an investment made on some previous date in history Expressing the percent or relative change is often more effective when you are concerned with comparisons over time than the absolute change of the quantity For an example an absolute Part 0 Percent Change 1 change of 10 units is almost unnoticeable when the value of a quantity observed is 1000 units However that same 10 units more than doubles the value of a quantity of 8 units To begin this Module view the screencast Percent Growth Models Screencast Part 1 Daily Changes in Stock Prices 1 Create a column in Excel that shows the difference in dollars and cents between the High and Low prices for each day Call the column Daily Range Note you will write an Excel formula in the first row of data and drag it down the whole column a What was the Daily Range on 4 23 15 2 Create a column in Excel that shows the percentage increase of the High over the Low Call the column Daily Range Show your answer as a percentage with two decimal places e g 10 12 a What was the Daily Range on 5 27 15 3 Create a column in Excel that shows the difference in dollars and cents between the Adjusted Close that day and the Adjusted Close the previous day Call the column Daily Change 2 a What was the Daily Change on 3 17 16 4 Create a column in Excel that shows the percentage change either increase or decrease of the Adjusted Close that day compared to the Adjusted Close the previous day Call the column Daily Change Show your answer as a percentage with two decimal places e g 10 12 a What was the Daily Change on 6 6 14 5 For this problem the Excel function XLOOKUP is useful a Which day in the data set had the largest Daily Range b Which day in the data set had the smallest Daily Range c Which day in the data set had the largest Daily Range 3 d Which day in the data set had the smallest Daily Range e Which day in the data set had the largest positive Daily Change i e biggest increase from the previous day f Which day in the data set had the most negative Daily Change i e biggest drop from the previous day g Which day in the data set had the largest positive Daily Change h Which day in the data set had the most negative Daily Change i e biggest percentage drop 6 For what percentage of days in the data set was the Adjusted Close on the day lower than the Adjusted Close the previous day Show your answer as a percentage with two decimal places e g 10 12 4 7 For what percentage of days in the data set was the Adjusted Close on a day higher than the Adjusted Close the previous day Show your answer as a percentage with two decimal places e g 10 12 Is the sum of the answer to this question and the previous question equal to 100 Why or why not 8 For what percentage of days in the data set was the Daily Change percentage a strictly greater than 0 b greater than or equal to 2 00 9 For what percentage of days in the data set was the Daily Change percentage strictly greater than 2 00 and the volume traded was strictly less than 8 000 000 5 6 Part 2 Graphing Using all the trading days on the Table tab create a graph of Adjusted Close price as a function of time Label the horizontal axis with dates in a readable manner Here is an example of a nicely labeled graph using Close prices for 2015 Create a graph for all the trading days in the data set AMZN Close Price 2015 Jan Feb Mar Apr May Jun Aug Sep Oct Nov Dec Jul Date Check your work by going to finance yahoo com and examining the historical price charts for AMZN over the whole life of the stock D S U e c i r P 700 650 600 550 500 450 400 350 300 250 7 Part 3 Applying Adjustments The data set shows the Adjusted Close prices Adjustments help make the prices comparable over time and are necessary when the stock splits or pays dividends For this class don t worry if you don t understand those actions The data set does not show the adjustments for the other prices given Open High and Low However you can adjust those other prices using the ratio of the Adj Close to the Close price 1 Find the Adjustment Ratio Adj Close Close price for every trading day in the data set Fill in the blank cells in the table below Date 6 2 97 8 23 99 1 5 01 10 25 05 3 4 16 Adj Close Close 0 083 1 000 8 2 Create a line graph for the Adjustment Ratio as a function of trading days Hint Use the data for all the days in the data set not just the values from the table in Question 1 3 Looking at the graph how many adjustments were made 4 Create columns in the data for Adjusted High price Adjustment Ratio x High and Adjusted Low price Adjustment Ratio x Low Use those columns to fill in the blank cells in the following table Date 8 21 97 7 7 99 8 1 03 Adj High Adj Low 60 00 41 63 5 Create a line graph showing both the Adjusted High and Adjusted Low prices for days from the first trading day 5 15 97 until December …
View Full Document