HW3 – Sports Data

This exercise will involve importing NCAA Football sports data from a comma separatedvalue document and from an online web source. Once the data has been formatted, you willneed to make changes to the structure of the data as instructed using text functionsdiscussed in chapter 10. Additionally, you will create a form to make filtering data simplefor 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 thefollowing format: Lastname, Fristname For example, Smith, Joe Be sure to includethe space.
3. Create an additional column called “Player Class” after the Class column, thattransforms 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 playersfrom 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. Moreinformation can be found here:https://en.wikipedia.org/wiki/American_football_positions6. Create a new column after the Hometown column called “Hometown-City”. Use anExcel function to extract only the Hometown city. Do not include the state.7. Create a column after Hometown-City called “Hometown-State”. Use an Excelfunction to extract the state from the Hometown column. Include no more than 4Characters to identify the state. Verify your data for consistency. Manually fix anyissues.8. Create a new column after the Class column named Basic class. Use an excelfunction 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 sortsthe 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