hw3 sports data

ITIA – 2200

Macomb Community College

Homework – Importing, Cleaning, and Coding

This exercise will involve importing NCAA Football sports data from a comma separated value document and from an online web source. Once the data has been formatted, you will need to make changes to the structure of the data as instructed using text functions discussed in chapter 10. Additionally, you will create a form to make filtering data simple for an end user.

Complete the following objectives:

1. Import the data from scoring_rush_rec.csv into a new Excel workbook titled “MSU-Football-Data-YourLastName.xlsx” Give the worksheet a descriptive name.

2. Create a new column after the Player Name column that lists the players name in the following format: Lastname, Fristname For example, Smith, Joe Be sure to include the space.

3. Create an additional column called “Player Class” after the Class column, that transforms the text for the class to include a first letter capitalized. For example, “freshman” should become “Freshman”. Be sure to do this using a function.

4. Using the same workbook from above, import the full roster of MSU football players from the following site: https://www.ourlads.com/ncaa-football-depth-charts/roster/michigan-state/91142 Be sure to expand all rows.

5. Create a new column after the player number that indicates if the player is “Defense”, “Offense”, or “Special Teams” depending on the player position. More information can be found here: https://en.wikipedia.org/wiki/American_football_positions

6. Create a new column after the Hometown column called “Hometown-City”. Use an Excel function to extract only the Hometown city. Do not include the state.

7. Create a column after Hometown-City called “Hometown-State”. Use an Excel function to extract the state from the Hometown column. Include no more than 4 Characters to identify the state. Verify your data for consistency. Manually fix any issues.

8. Create a new column after the Class column named Basic class. Use an excel function to display only the 2 letter class acronym. Do not include RS.

9. Create a new macro that filters only Freshman players (RS FR or FR) and then sorts the data by player number.

10. Extra Credit:

Create a set of buttons that identify only players by their class (Freshman, Sophomore, Junior, or Senior) by using Macros or VBA invoked by a button control. Think of this as a simple filter that can be applied. OR Create an additional method of your choice using a Macro or VBA that does not involve filtering or sorting data. Describe your method and how to invoke it below:

When complete, upload this solution to Canvas and also attach a copy of your Excel file.