111|EXCEL – Investment Company Excel Quest

Challenges
, , , , ,
1

BYU Student Author: @Erick_Sizilio
Reviewers: @Alex_Garrett, @klayton, @Donovon
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

  • Excel

Need a program? Click here.

Overview
Congratulations! You have recently been hired by a small investment company, and your boss has given you a mission: provide a comprehensive spreadsheet with information on 4847 company tickers registered with the SEC. The stakes are high, and your boss wants you to complete this task by the end of the day.

But that’s not all. Your boss has also given you the note below with a list of questions that he wants answers to. Can you rise to the challenge and deliver a spreadsheet that not only includes all the requested information but also provides answers to your boss’s questions?

  1. Which industry has the highest aggregated market cap?
  2. Which companies have the highest market cap?
  3. Which companies have the largest number of employees?
  4. Which state has the most companies headquartered?
  5. Which zip code has the most companies headquartered?

You will need to use all your Excel skills to complete this mission successfully. From organizing data to using Pivot tables, you will need to be creative and efficient. And the clock is ticking!

Are you up for the challenge? Show your boss what you’re made of and create a spreadsheet that will impress even the most seasoned investors!

Instructions

  • Use Excel’s “Stocks” tool found under the “Data” tab, as explained by Microsoft at this link to get the following fields: 1) Official Name, 2) Company Description, 3) Industry, 4) Year 5) Incorporated, 6) Shares Outstanding, 7) Price, 8) Market Cap, 9) Number of Employees and 10) Headquarter Address.
  • Extract the State and zip code from the HQ address.
  • Insert a PivotTable to answer the questions above.

Data Files

Suggestions and Hints
  • You can use Excel’s Flash fill to extract the state and zip code. You can learn how to use it here.
  • You can also accomplish the same task by using a combination of string functions, including “mid()”, “len()”, and others.

Solution

Challenge111_Solution.xlsx
(You will need to go the “Data” Tab and click “Refresh All” so that the numbers update to the most current data)
Solution Video: Challenge 111|EXCEL – Investment Company Excel Quest

2

Time to complete: 25 mins
Difficulty: Intermediate
Challenge111_Data.csv (5.2 MB)
Challenge111_DataPivotTableSheet.csv (464.3 KB)