Download data on a company’s stock history. From this data, create scatterplots, histograms, and calculate the mean, median, mode, and standard deviation of some data points. Write a 5-8 page report including the graphs and descriptive statistics you have created.
Business analytics techniques are used to facilitate decision making by transforming large amounts of raw data into meaningful information. Many businesses rely on analysis of relevant historical data to make key strategic and operational decisions. Therefore, understanding how to use techniques such as graphical representation and descriptive statistics to translate raw data into useful information can be a valuable skill in an organization.
In this assessment and the next, you will have the opportunity to sharpen your analytics skills by locating and interpreting real-life stock data.
You have been learning about how to explore data. In this assessment, you will apply those skills by downloading a practical dataset and creating graphical representations of that data. The work you do in this assessment will lay the foundation for future assessments in which you analyze and interpret those graphical representations. Since the purpose of business analytics is to make sense of large quantities of raw data, this assessment helps you develop skills in applying analytics to business contexts by practicing the exploration and display of data.
In addition to graphical and tabular summary methods, numeric or quantitative variables and data can be summarized numerically using various techniques of description and display.
Descriptive methods, which describe existing data, are also methods for using a subset of the available data to estimate or test a theory about a measurement on a larger group. This larger group is called the population, and the measurement being studied is the parameter. The smaller group, or subset, of the population that is taken in order to make an inference (to make an estimate or test a theory) is referred to as the sample. The measurement taken on that sample is then referred to as the statistic, which is usually the best single-number estimate for the population parameter of interest. Most often, however, the estimate should not be restricted to a single number that would be exactly correct or incorrect. Instead, it is preferable to calculate some range of possible values between which there can be a certain percent confidence that the true population parameter falls. These are referred to as confidence intervals.
Scenario
Business analytics techniques are used to facilitate decision making by transforming large amounts of raw data into meaningful information. Many businesses rely on analysis of relevant historical data to make key strategic and operational decisions with the goal of gaining or maintaining competitive advantage. Therefore, understanding how to use techniques such as graphical representation and descriptive statistics to translate raw data into useful information can be a valuable skill in an organization.
In this assessment and the next, you will have the opportunity to sharpen your analytics skills by locating and interpreting real-life stock data, creating a business report, and presenting the information from the business report with your supervisor and colleagues as part of a decision-making effort.
Your Role
You are a member of a business analyst group interested in a publicly traded company. Your supervisor has asked you to create a presentation, including graphical representations from raw stock data. From that raw stock data, you are to create a business report for a company-wide meeting at the end of the quarter. Your work and the work of others will result in a Business Report, which will be utilized to help company leadership make decisions.
Your first task is to pick a publicly held company with only one business platform. So do not pick Apple, Amazon, Disney, et cetera. You want a company that plays in only one industry. Then you are to provide an overview of the company, including business context. Remember that business context includes many aspects of the company, industry, competition, et cetera.
The second task is prepping stock history data from the business or company and creating scatter plots and a histograms.
The third task is to calculate mean, median, mode, and standard deviation of the adjusted daily closing stock price and the stock volume.
The fourth task is to provide a summary of the information you provide (including data analysis) without bias and with factual information including citations.
It is your responsibility to present visually and to interpret the data into meaningful information using analysis and descriptive statistics.
Instructions
Select a publicly traded business or stock that plays in only one industry in which you have interest. Download the raw data on the company’s stock history.
Follow these steps to locate and download stock history from Yahoo! Finance:
Go to Yahoo Finance.
Search for and find the stock information of your chosen company. Remember do not use a company that plays in multiple industries.
Once you pull up the general data on the company, review the screen links throughout until you find the link for Historical Data. Click on the Historical Data link. Then select the following settings above the table:
Select Time Period of one year.
Select “Historical Prices.”
Select Frequency as “Daily.”
Click Apply.
Click Download Data. Go to the bottom of your screen or your Downloads folder to open the Excel file you just downloaded. Open the Excel file. Check to be sure that you have enough lines to show the whole year. If not, reset the settings at the top of the Historical Data chart and try again.
Once you are sure that you have a year’s worth of data, save the Excel file.
Using the Excel file with the year’s stock data, conduct descriptive analysis as follows:
Create a scatter plot of the highest stock price (in the column labeled “High”) against time. Write several sentences explaining the process/steps by which you created this graph.
Create a scatter plot of the lowest stock price (in the column labeled “Low”) against time. Write several sentences explaining the process/steps by which you created this graph.
Create a histogram of the adjusted daily closing stock price (in the column labeled “Adj Close”). Make sure the histogram is meaningful by adjusting the bin size so you can see the shape of the histogram. Write several sentences explaining the process/steps by which you created this graph.
Create a histogram of the stock trading volume (in the column labeled “Volume”). Make sure the histogram is meaningful by adjusting the bin size so you can see the shape of the histogram. Write several sentences explaining the process/steps by which you created each graph.
Complete the following for each of the four graphs:
Make sure the x and y axis have appropriate labels—“Stock Price in USD” or “Date D/M/Y” for example.
Change the title of the graph to communicated what the graph is communicating.
Add options—color, trendlines, legend, other?
Calculate the mean, median, mode, and standard deviation of the adjusted daily closing stock price.
Put answers of calculations in table format for easy review.
Write several sentences explaining the process by which you calculated these statistics.
Calculate the mean, median, mode, and standard deviation of the stock volume. Put in table format for easy review. Write a sentence explaining the process by which you calculated these statistics.
Prepare a 5-8 page report that you would present to your supervisor, including the following:
An APA-formatted title page.
A 1-2 page introduction describing the background of your chosen company and its practical extensive business context. You should use at least four sources of information on the company, industries the company participates in, history, mission, platforms, products, competitive advantage, and competitors by industry.
A section labeled Graphical Representations of Data, in which you include the four graphs you created above and short descriptions of the process you used to create each graph.
A section labeled Descriptive Statistics, in which you include the statistics you calculated above and explanation of the procedures you followed to calculate the statistics.
A summary of what the data suggests. No opinion please. See textbook information on how to interpret data. Please remain unbiased in your summary. You may use additional resources (and cite) to help you interpret the data. For example: What does Standard Deviation say about stock volatility?
Your paper should be APA-formatted with in-text citations and a corresponding references page. Remember to cite the sources of your financial data. Include at least four sources of information for your page and reflect on reference page.
Walkthrough: You may view the following media piece to help you understand concepts addressed in this assessment: TRANSCRIPT:
In this video, we will learn how to create histograms and scatterplots for a company’s stock history data using Microsoft Excel 2016. We will also learn how to use Excel 2016 to calculate the mean, median, and mode of the company’s stock history data. For this walkthrough, we have selected Microsoft Corporation. The company’s stock history for 2018–2019 is graphically represented and statistically enumerated. The data set for Microsoft’s stock history can be accessed from Yahoo Finance in the Excel 2016 format. The stock history data is represented in Excel 2016 as a table comprising seven columns: column 1 represents dates, column 2 represents opening stock prices, column 3 represents highest stock prices, column 4 represents lowest stock prices, column 5 represents closing stock prices, column 6 represents adjusted closing stock prices, and column 7 represents the stock trade volume.
Scatterplots
A scatterplot is a two-dimensional data visualization tool used to represent the relationship between two variables as a series of unconnected dots. A dot in a scatterplot represents a correlation between a point on the x-axis and another on the y-axis. In cases with a dependent relationship between variables, the independent variable is marked on the x-axis and the dependent variable is marked on the y-axis. Scatterplots in which the arrangement of dots shows a distinct pattern indicate definite trends and relationships.
The scatterplot of the daily highest stock prices for Microsoft Corporation can be developed by marking the highest stock prices on the y-axis and the time (in days) on the x-axis for the period February 2018 to February 2019.
To create the scatterplot of the highest stock prices using Excel 2016, first ensure that the data from the columns titled Date (column 1) and High (column 3) are placed in two adjacent columns and selected. Next, select the Insert tab and select the Charts option from the drop-down menu. Subsequently, select the More Scatter Charts option to select the appropriate scatter chart for the data.
This is the scatterplot created using Excel 2016 for Microsoft Corporation’s highest stock prices:
The scatterplot for the lowest stock prices against time can be developed in Excel 2016 using the same steps as those used for the highest stock prices. The only difference is that while selecting the data in Excel 2016, the data from the columns titled Date (column 1) and Low (column 4) are selected.
This is the scatterplot created using Excel 2016 for Microsoft Corporation’s lowest stock prices:
Histograms
Histograms represent the frequency of a phenomenon (for example, closing stock price) within consecutive value ranges. A histogram is used to visualize the probability distribution of a single continuous variable. It is a two-dimensional chart in which the x-axis represents ranges of values distributed in equal, continuous intervals and the y-axis represents the number of occurrences of a phenomenon. The continuously distributed intervals are called bins, and their width (representing the range of the value) is determined based on the size of the data set to ensure the development of meaningful histograms.
To construct a histogram for daily closing stock price, first determine the lowest and highest adjusted closing stock prices. Based on the difference between the two, determine the size of the continuously distributed intervals to ensure an adequate number of bins to derive valuable meaning. The bins, which resemble bars, are then constructed by marking the frequency of the adjacent closing price falling within the continuous price ranges.
The histogram for the adjacent closing stock price of Microsoft Corporation can be created in Excel 2016 using the Analysis ToolPak add-in. To activate this add-in, first select the File tab in the home page and then select Options. select the Add-ins tab in the left column and select Go. From the available add-in options, select the Analysis ToolPak option and select OK.
Once this is done, go back to the home page and select the Data tab. Then, select the Data Analysis option, select the Histogram option, and select OK. This opens the histogram settings page. For the Input Range, select the data set for adjacent closing stock price (column 6). For the Bin Range, create a data set in a separate column. To do so, consider the lowest and highest adjacent closing stock prices, based on which we can determine the size of the bins. In the case of Microsoft Corporation’s adjacent closing stock prices, there is no value lower than 85 and no value higher than 115; it would, therefore, be appropriate to consider the bin size to be 5, which will result in a histogram with 6 bins. In this case, column H has 6 data values, namely 90, 95, 100, 105, 110, and 115. These data values are selected to constitute the bin range. Output Range, just select any empty cell. Subsequently, check the Chart Output and select OK, which will result in the generation of the histogram.
This is the histogram created using Excel 2016 for Microsoft Corporation’s adjacent closing stock price:
The histogram of stock trade volume can be developed in Excel 2016 using the same steps as those used for the histogram of adjacent closing stock price. This is the histogram created for stock trade volume using Excel 2016:
Mean, Median, Mode and Standard Deviation
Mean is the average value of a data set. It can be calculated by dividing the sum of all the values in a data set by the total number of values. Mean is a popular statistical measure to determine the central value in a data set.
The mean of adjusted closing stock price can be calculated in Excel 2016 by selecting the Formulas tab on the home page and then selecting the More Functions option in the drop-down menu. Next, select the Statistical bar and select Average from the list. In the Functions Argument page for the average, fill the Number 1 option by selecting the data set for adjacent closing stock price found in column 6 and then select OK; this leads to the generation of the mean. It is important that an empty cell is selected before selecting the More Functions option. The mean of Microsoft Corporation’s adjacent closing stock price for the period February 2018 to February 2019 is 101.933 U.S. dollars.
Median is another common measure for determining the central (or middle) value of a data set. The number of data points above the median is equal to that below the median. Median is calculated by sorting the data points in ascending order and selecting the middle value. If the total number of data points is an even number, the average of the two middle values is the median.
The median of adjusted closing stock price can be calculated in Excel 2016 by selecting the Formulas tab; select the More Functions option, select the Statistical bar, and choose Median from the list. In the Functions Argument page for the median, select the data set for adjacent closing stock price and then select OK, which will generate the median. It is important to select an empty cell before generating the median to avoid overwriting an existing data point. The median of Microsoft Corporation’s adjacent closing stock price for the period February 2018 to February 2019 is 103.24 U.S. dollars.
The Mode is the number that occurs most often in a set of numbers. For example: {4,5,3,7,6,5,4,5,3,5,8,7}. The number 5 is the mode because it occurs four times in the set which is more than any other number in the set. To find the mode which is sometimes known as the modal value, arrange the numbers in the set in order. Then count how many of each number is present. The number that appears the most is the mode. When using Excel the formula for mode is =MODE(C2:C8) when data is in column C in cells 2-8. To calculate the mode using Excel 2016, highlight the data you want to use for the calculation, select the Formulas tab, select the More Functions option, select the Statistical bar, and select MODE SNGL. Then enter the cell numbers as noted above.
Standard deviation is a measure of the dispersion or separation of values from the mean. It is a measure of volatility within a data set. Standard deviation is calculated by first calculating the mean of the data set. This is followed by subtracting each data point from the mean, squaring the results, and then calculating the mean of the results. The square root of this calculated mean is the standard deviation.
To calculate the standard deviation of the adjusted closing stock price in Excel 2016, select the Formulas tab, select the More Functions option, select the Statistical bar, and select the Standard Deviation Sample (STDEV.S) option. In the Functions Argument page, select the data set for adjacent closing stock price as the Number 1 option and select OK to generate the standard deviation. As with mean and median, ensure that an empty cell is selected before generating the standard deviation. The standard deviation of Microsoft Corporation’s adjacent closing stock price for the period February 2018 to February 2019 is found to be 6.953.
The mean, median, and standard deviation for stock trade volume can be calculated in Excel 2016 by using the same steps as those used for the mean, median, and standard deviation of adjacent closing stock price. The mean, median, and standard deviation of Microsoft Corporation’s stock trade volume are found to be 31,210,598, 28,123,200, and 12,909,909.8, respectively. With the MSFT volume data, the mode cannot be calculated as there is no number that occurs most often (even twice) in the data set. An error occurs as the large volume numbers do not repeat and therefore there isn’t a number that occurs more frequently number to return when the mode formula is applied.
Additional Requirements
Length: 5-8 pages, double-spaced. Include a title page and the graphical representations of the data selected.
Written communication: Written communication should be free of errors that detract from the overall message.
Evaluation
By successfully completing this assessment, you will demonstrate your proficiency in the following course competencies through corresponding scoring guide criteria:
Competency 2: Use analytic and statistical techniques to make meaning of large quantities of data.
Create four different graphical representations of data.
Calculate descriptive statistics for two different variables.
Summarize the processes by which each graph and statistics were created and calculated.
Competency 4: Present the results of data analysis in clear and meaningful ways to multiple stakeholders.
Introduce the company and practical business context.
Correctly format citations and references using current APA style.
Write content clearly and logically with correct use of grammar, punctuation, and mechanics.
Using Analytic Techniques to Add Meaning to Data Scoring Guide
Criteria
Non-performance
Basic
Proficient
Distinguished
Describe the company background and the practical business context.
Does not describe the company background and the practical business context.
Describe a part of the company background or a part of the business context, but not a complete description of the company.
Describe the company background and the practical business context.
Describe the company background and a detailed business context to give a well rounded view of the company in the marketplace.
Create four different graphical representations of the data, including scatterplots and histograms.
Does not create four different graphical representations of the data, including scatterplots and histograms.
Creates graphical representations of the data with errors or that do not include at least one scatterplot and histogram.
Creates four different graphical representations of the data, including scatterplots and histograms.
Creates four different graphical representations of the data that are professionally formatted and clearly labeled, including scatterplots and histograms.
Calculate descriptive statistics for two different variables, including mean, median, mode, and standard deviations.
Does not calculate descriptive statistics for two different variables, including mean, median, mode, or standard deviations.
Calculates descriptive statistics for two variables, but there are errors in mean, median, mode, and/or standard deviations.
Calculates descriptive statistics for two different variables, including mean, median, and mode, standard deviations.
Calculates descriptive statistics for two variables, including mean, median, mode, and standard deviations, that are clearly labeled and professionally presented.
Summarize the processes by which graphs and statistics were created and calculated.
Does not summarize the processes by which each graph and statistics were created and calculated.
Sentences begin to describe the process of creating the graphs or calculating parts of the statistics, but does not fully explain all of the elements.
Summarize the processes by which each graph and statistics were created and calculated.
Detailed summaries of the processes by which each graph and statistic were created and calculated in language that shows an advanced understanding of the concepts.
Format citations and references correctly using current APA style.
Does not format citations and references using APA style.
Formats citations and references with errors in APA style.
Formats citations and references correctly using current APA style.
Formats citations and references flawlessly in current APA style.
Present content clearly, professionally, and logically for the identified audience.
Does not present content clearly, professionally, or logically for the identified audience.
Presents content with some flaws in organization or clarity that affect professional delivery for the identified audience.
Presents content clearly, professionally, and logically for the identified audience.
Presents content with exceptional clarity, organization, professionalism, and appropriateness for the identified audience.
The post Download data on a company’s stock history. From this data, create scatterplots, appeared first on PapersSpot.
