Generate a time series plot of the data showing closing prices over time on a single graph

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

OMGT 6123 Quantitative Methods

Exercise #1

Data Wrangling and Visualization

Download the Excel file titled Exercise 1 for this assignment. The file contains several tabs with the data required for the assignment. For this exercise you will be asked to download data, perform basic data manipulation, and create multiple graphs and tables. Unless otherwise specified, you may use any tool you chose to create the graph.

Problem 1 (10 points)

In this problem, we will analyze stock market data. Go to Yahoo Finance to download quotes. (https://finance.yahoo.com/lookup) Download daily data on the S&P500 (^GSPC), GameStop. (GME) and Tesla. (TSLA) for the last 12 months. Cut and paste the Adjusted Closing Price into a single worksheet. (Note: alternatively use the tidyquant library in R to retrieve the data.) Do the following:

• Generate a time series plot of the data showing closing prices over time on a single graph. (Plot all 3 series on the same graph) Paste this chart into your template.

• Generate a plot of GameStop and the S&P500. Paste this chart into your template.

• Generate a plot of GameStop and Tesla. Paste this chart into your template.

• Create a new column for each series showing the normalized price, i.e., the percentage gain relative to the first day. (Hint: calculate the change in price and then divide each series by the price Day 1, use absolute referencing). Generate a plot of this data. (Plot all 3 series on the same graph) Paste this chart into your template.

• Calculate the % daily change for each asset from for the series. (Hint: %Chg=(Today's Price - Yesterday's Pice)/Yesterday's Price ) Note: you will not have a % change for the first day.

• Generate a scatter plot of GameStop vs S&P 500. (Hint: points only, no lines) with the % change of GameStop on the vertical axis and the % change of the S&P 500 on the horizontal. Add a linear trendline and display the equation and R 2 on your chart. Paste this chart into your template.

• Generate the corresponding graph for Tesla vs S&P 500. Paste this chart into your template.

• Generate a scatterplot matrix for the % change of all 3 securities. Paste this chart into your template. OMGT 6123 2 Problem 2 (10 points) Consider the data set on Tab 1. This tab contains daily call volume at a call center over a six-month period.

• Create a Line Chart of the column Calls. Paste this chart into your template.

• Create a Relative Frequency Histogram of the column Calls.

• Create a Box Plot using Excel that shows calls segregated by DOW. Paste this chart into your template.

• Create a Box plot using either XLMiner or R that shows calls segregated by DOW with a separate panel for Holiday. Paste this chart into your template.

• Copy the non-holiday call volume then bin that data into quartiles. (4 bins of equal count) Copy the Intervals section from the Bin_Output tab into the template.

• How many of the days shown in the data set were holidays?

• How many of the holidays occurred on a Friday? Problem 3 (10 points) In this problem we will examine a data set of odds and scores for ECU football games over the last several seasons shown on Tab2. Each row in the data file represents the data for one of the teams in the game. Games can be identified by the GameID.

• The column VH designates if the game was played at home (H), away (V) or at a neutral location (N). Document the number of games played by ECU at each venue using a pivot table and paste the table into the template. (Hint: filter the pivot table to only include rows with ECU as the team) • Create a column chart that shows the average points scored by ECU per quarter. (Hint: this can be done using a Pivot Chart, or using a Averageif function)

• Create a column chart that shows the average points scored by ECU’s opponent per quarter.

• Count the number of times ECU scored more than 42 points in the game. (Hint: this can be done filtering, or using a Countifs function)

• Count the number of times ECU scored 10 or more points in both the 2 nd quarter and the 4th quarter.

• Most college football games are played on Saturdays. But ECU sometimes plays on Thursday nights. Count how many games in this data set occurred on a Thursday night

• The field WinProb indicates the probability of winning calculated from the Moneyline Odds (ML). Eliminate rows where the value for WinProb is missing. Report the number of records eliminated.

• Calculate the proportion of games was ECU predicated to win.

Attachments:

Expert's Answer

666 Times Downloaded

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme