Sports Data

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. 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.
Extra Credit: Create an addition

Last Completed Projects