Go To Google Finance Historical Q&A Nasdaq G00G First Set

Go Tohttpwwwgooglecomfinancehistoricalqnasdaqgoogfirst Set

Go Tohttpwwwgooglecomfinancehistoricalqnasdaqgoogfirst Set

Go to First, set the date range to be for exactly 1 year ending with the Monday that this course started. For example, if the current term started on 04/01/2014, then use 04/01/2013 – 03/31/2014. Your dates will going back exactly 1 year. Next, click the link on the right that says Download to Spreadsheet and then save the file to your computer. This project will only use the Closing Values.

Assume that the closing prices of the stock form a normally distributed data set. This means that you need to use Excel to find the mean and standard deviation and then use those numbers and the methods you learned in sections 5.2 and 5.3 of our text book for Normal distributions to answer the questions. Complete this assignment within a single Excel file. Show your work or explain how you obtained each of your answers. Answers with no work and no explanation will receive no credit.

Paper For Above instruction

The task involves analyzing the historical closing prices of Google stock over a specific year-long period, assuming the data follows a normal distribution. The analysis begins with data collection by setting the date range to exactly one year ending with the first day of the course, downloading the relevant data, and focusing solely on the closing prices.

Using Excel, the first step is to compute the mean and standard deviation of the closing prices for that year. These statistical measures provide the basis for answering probability questions and understanding the distribution's properties. The assumption that the data is normally distributed allows us to apply properties of the normal distribution to estimate probabilities and identify unusual values.

The specific questions address various angles of the data distribution:

  1. Probability that the stock closed below the mean: Since the data is normally distributed, the probability that a randomly selected closing price falls below the mean is 50%. This aligns with the properties of the symmetric normal distribution, where the mean divides the data into two equal halves. Therefore, without calculating the mean explicitly, one can infer that this probability is 0.5.
  2. Probability that the stock closed above $600: To find this, calculate the z-score for $600 using the mean and standard deviation, then look up or compute the corresponding probability in standard normal distribution tables or Excel functions. This quantifies the likelihood of observing a closing price greater than $600 within the distribution.
  3. Probability that the stock closed within $45 of the mean: This requires determining the interval from (mean − $45) to (mean + $45). Calculating the z-scores for these bounds and using the standard normal cumulative distribution function yields the probability that a closing price falls within this range.
  4. Unusual closing price at $450: Using the textbook's definition of unusualness, typically defined as values more than 2 standard deviations from the mean, assess whether $450 falls within the typical range. If not, it is considered unusual.
  5. Price thresholds for anomalous closes: Determine the boundary values lying 2 standard deviations below and above the mean. Closing prices outside this range are deemed statistically unusual.
  6. Quartiles (Q1, Q2, Q3): Use Excel functions to compute the first, second, and third quartiles of the dataset without relying on the normal distribution assumption. These quartiles divide the data into four equal parts, offering insights into the data distribution's spread and skewness.
  7. Validity of the normality assumption: Construct a histogram with approximately 10–12 classes to visualize the data distribution. Assess whether the histogram exhibits the bell-shaped curve characteristic of normality, considering skewness, kurtosis, and outliers. Although perfect normality is rare, the data should roughly follow the theoretical shape to justify the assumption.

In executing this analysis, ensure that the date range is correctly set, and accurately compute statistical measures with Excel functions such as AVERAGE, STDEV.P, and QUARTILE.INC. Document each step, calculation, and reasoning clearly, as points are awarded for showing work. The final report must be submitted in a single Excel file containing all calculations, charts, and explanations necessary to substantiate your answers, adhering to the specified format and instructions.

Paper For Above instruction

Analyzing the historical stock data of Google (Alphabet Inc.) over a designated year provides an insightful glimpse into the stock's behavior and distribution properties. In this study, the focus is on the closing prices, which are assumed to follow a normal distribution—a common assumption that simplifies statistical analysis and probability estimation. The entire analysis hinges upon accurate data collection, computation, and interpretation within the framework of normal distribution properties.

The initial step involves selecting the appropriate date range. If the course started on April 1, 2014, the interval would span from April 1, 2013, to March 31, 2014. Utilizing Google Finance or other financial data sources, the closing prices within this period are downloaded and prepared for analysis in Excel. The data's focus is solely on the closing prices, which are extracted into an Excel worksheet for statistical computation.

After data collection, calculating the mean and standard deviation of the closing prices is critical. These measures describe the central tendency and dispersion of the data, respectively. Using Excel functions such as =AVERAGE(range) and =STDEV.P(range), the calculations are straightforward and provide the basis for probability estimation and identifying unusual values.

Question 1 asks about the probability that a randomly selected day within that year had a closing price less than the mean. Under the properties of the normal distribution, this probability is always 0.5, due to its symmetry around the mean. This result holds regardless of the specific data, assuming normality, confirming that 50% of the observations fall below the mean on any given day.

Question 2 focuses on the probability that the closing price exceeds \$600. To answer this, the z-score for \$600 is computed as (600 - mean) / standard deviation. Using Excel's NORM.S.DIST or NORM.DIST functions, the probability of exceeding this value can be determined. This method quantifies the rarity of such high closing prices under the assumed normal distribution.

Question 3 estimates the probability that the closing price is within \$45 of the mean. This involves defining the interval: (mean − \$45) to (mean + \$45). Calculating the corresponding z-scores and applying the cumulative standard normal distribution function gives the probability of closing within this range. This measure indicates typical daily price fluctuations relative to the mean.

Question 4 assesses whether a closing price of \$450 is "unusual," where unusual is often defined as being more than 2 standard deviations from the mean. For this, the mean and standard deviation are used to determine the boundaries: mean ± 2 × standard deviation. If \$450 falls outside this range, it qualifies as an unusual event, indicating a significant deviation from typical price levels.

Question 5 extends this by identifying the exact price points that mark the thresholds of statistical unusualness—those that are 2 standard deviations away from the mean. Consequently, any closing price below (mean − 2 × SD) or above (mean + 2 × SD) is considered statistically unusual, capturing extraordinary market movements during the period.

Question 6 involves calculating quartiles, which divide the dataset into four equal parts. Using Excel's QUARTILE.INC function, Q1 (25th percentile), Q2 (median or 50th percentile), and Q3 (75th percentile) are obtained. Unlike previous calculations, these values are purely data-driven and do not depend on distributional assumptions, providing fundamental insights into the data's spread and skewness.

Finally, Question 7 explores the validity of assuming normality. This involves constructing a histogram with approximately 10–12 classes to visualize the data distribution. If the histogram exhibits a bell-shaped curve, the normality assumption holds reasonably well. Deviations such as skewness, outliers, or bimodal patterns suggest the data may not be perfectly normal, but it could still be close enough for the assumption to be acceptable.

Throughout the analysis, proper use of Excel functions ensures accuracy, and each step is documented thoroughly to demonstrate understanding. The combined insights from probability calculations, data distribution examination, and visualizations enable a comprehensive evaluation of Google's stock performance over the specified year, aligning with the analytical goals outlined in the assignment.

References

  • Devore, J. L. (2015). Probability and Statistics for Engineering and the Sciences (9th ed.). Brooks/Cole, Cengage Learning.
  • Newbold, P., Carlson, W. L., & Thorne, B. (2013). Statistics for Business and Economics (8th ed.). Pearson.
  • Ott, R. L., & Longnecker, M. (2010). An Introduction to Statistical Thinking. Brooks/Cole, Cengage Learning.
  • Wald, R. (2014). Applied Statistics and Probability for Engineers (7th ed.). Pearson.
  • McClave, J. T., & Sincich, T. (2014). Statistics (12th ed.). Pearson.
  • Microsoft Support. (2020). Use Excel functions for normal distributions. https://support.microsoft.com
  • Google Finance. (n.d.). Historical stock data. https://www.google.com/finance
  • Gelman, A., Carlin, J. B., Stern, H. S., & Rubin, D. B. (2013). Bayesian Data Analysis (3rd ed.). Chapman and Hall/CRC.
  • Everitt, B. (2002). The Cambridge Dictionary of Statistics. Cambridge University Press.
  • Kass, R. E., & Raftery, A. E. (1995). Bayes Factors. Journal of the American Statistical Association, 90(430), 773-795.

Related Assignments