CIS 2640 Computer Science Data Collection & Manipulation Excel Project

CIS 2640
ASSIGNMENT 3
See the due date on e-learning
Objective
Now that you are fairly comfortable with pulling data from many external data sources into pivot
tables (please go through the “Importing Data from Web.pptx” file), we will further our data
analytical skills to a higher level. In this assignment, you will pull job statistics data from Bureau
of Labor Statistics web site, cleanse it and then analyze it. I will try not to give step-by-step
instructions in some areas so that you will have an opportunity to explore different possible
answers. This is an essential part of your analytical training.
Section 1: Data collection
1. First, review the Web Queries tutorial to be comfortable with pulling data from web
pages.
2. In the first Excel worksheet, pull data from the May 2021 National Occupational
Employment and Wage Estimates table from the following address:
https://www.bls.gov/oes/current/oes_nat.htm
a. Before importing it to Excel, perform the following data filtering in Power Query.
i. Keep only Occupation title, Employment, Employment per 1000 jobs,
Mean hourly wage and Annual mean wage columns.
ii. Remove negative salaries.
iii. Remove the record with ‘All Occupations’ as the Occupation title.
iv. Rename the first column to Occupation title by dropping the parentheses
after it.
v. All of the above must be done in Power Query.
b. Rename the resulting table to Full_List.
c. Make sure the Workbook Queries are shown on the side.
1
CIS 2640
If it doesn’t show, you may show it by clicking the following:
d. Name this worksheet Full List.
e. In the second worksheet, pull data from the Fastest growing occupations table
from the following address: https://www.bls.gov/emp/tables/fastest-growingoccupations.htm Name this table Fastest Growing.
f. Create the Median Pay column in the table to create the numeric version of data
from the Median Pay column. The formula for this column must use table
structured reference.
g. Name this worksheet Fastest Growing. The resulting worksheet looks like:
Section 2: Data Manipulation
1. In the Full list tab, create the Management column as column F that shows if an
occupation is a management position.
a. A management position is defined as a title with either “Executive” or “Manage”
in it.
b. Use table structured reference together with the SEARCH function and IF
statement to create this column. Never count or point to any specific cell
manually. The formula should look the same for all cells in this column.
2. Create the Fastest growing column as column G.
a. If an occupation is among the fastest growing in the FastestGrowing table, it is a
fastest growing job.
b. Use table structured reference together with IF, ISTEXT and VLOOKUP to create
this column. Never count or point to any specific cell manually. The formula
should look the same for all cells in this column.
2
CIS 2640
3. A portion of the third worksheet is shown below.
Section 3: Dynamic Summary
1. Create a job lookup table called KeywordsTable in a worksheet called Keywords. This
table is going to serve as the lookup table for data validation of another worksheet (see
bullet point number 2 below).
This is an illustration of
the format. Your numbers
may not match mine.
2. Create a job comparison table like the following in a worksheet called Comparison:
a. B1:D1: each of them is a drop down list box with values from the
KeywordsTable.
b. B2:D2: Number of jobs mentioning the term in B1, C1, and D1 respectively. This
is created using COUNTIF and table structured reference. The data source for
this and the next two bullet points is the Full List table.
c. B3:D3: Average salary of jobs mentioning the term in B1, C1 and D1 respectively.
Be sure to use table structured reference in your formula. Conditional formatting
is applied to B3:D3 with max being orange and min being blue.
d. B4:D4: Same requirements as B3:D3, but for Employment per 1000 jobs.
3
CIS 2640
3. Create charts based on the above table. Pay attention to details in the charts. You must
replicate every detail in the charts. This helps you review a few things about charts.
This is an illustration of
the format. Your numbers
may not match mine.
4. The beauty of setting up the summary this way is that you can dynamically select any
keywords in B1:D1. Figures, numbers and conditional formatting will change according
to your selection. You can even expand the KeywordsTable to allow more keywords in
B1:D1. For example, you may add Sales, Helpers, and/or Transportation to expand the
list. Remember, every row you add to an Excel table becomes part of the table.
5. I will change the values in cells B1:D1 when I check your assignment. Everything should
still work correctly.
Section 4: Data Aggregation through Pivot Tables
1. Create the first simple Pivot Table to show average annual salary between management
and fastest growing jobs. Save this worksheet as Pivot Table.
a. The pivot table shows column and row headings (i.e., Management and Fastest
growing). This is done by changing the layout of pivot tables.
b. All numbers have dollar sign and only two decimal places.
4
CIS 2640
c. The YES category is displayed before the NO category. Usually Excel displays NO
before YES. (Why?) You will drag the border of a category to move it to the
desired location.
What to submit?
1. You will submit only one Excel file called Assignment3_XXX.xlsx, where XXX is your last
name.
5
Importing Data from
Web
Book: Microsoft Excel 2016 Data Analysis and Business Modeling
Web Import Wizard
 Data | Get External Data | From Web
2
Web Import Wizard
 Link:
https://finance.yahoo.com/quote/%5EGSPC/history?p=%5
EGSPC
3
You may need authentication for some
web content
Use anonymous access for this web content
4
Choose a Table
Excel detects
different tables in
the webpage. Select
the one that you
are looking for.
Then click on load
and select “load to”.
5
Identify where you want to import the data
6
You can go through connection
settings to identify the update
intervals.
The data that you have imported is
linked to the webpage. It will be
updated when there is a change
in the website.
7