project-description-8

Project Description:

You are an intern for Hicks Financial, a small trading company located in Toledo, Ohio. Your intern supervisor wants you to create a report that details all trades made in February using current pricing information from the Dow Jones Index. To complete the task, you will import and shape data using Power Query. Then you will create data connections and visualizations of the data.

Steps to Perform:

Step

Instructions

Points Possible

1

Download the file named e10m1Dow.xlsx and save is as e10m1Dow_LastFirst.

0

2

Use Power Query to begin to import the Dow Index information located in Table 2 from the following URL (you will continue the import in the next steps).

6

3

Use the Query Editor to remove the columns P/E, Volume, and YTD change. Name the query Dow and load the data.

14

4

Name the newly created table Dow and name the worksheet Current_Price.

12

5

Use Power Query to import trade data located in the downloaded workbook e10m1TradeInformation.xlsx

3

6

Use the Query Editor to remove the NULL value columns (4-8), ensure that Use First Row As Headers is selected, and split column 3 (the Symbol column) by the left-most space delimiter.

12

7

Use the Query Editor to set the Date column Data Type to Date, if necessary.

8

8

Use the Query Editor to rename the fourth column Company Name. Rename the third column Symbol. Load the data and rename the worksheet Trades.

16

9

Create a Power View report with a Stacked Bar Visualization that compares the trading price of Apple and Coca-Cola stocks. Use the Company (as the Axis) and Price fields from the Dow table. The worksheet will be named Power View1 automatically; if necessary, rename the sheet as Power View1.

18

10

Add the title Trading Report.

2

11

Create a footer for all worksheets with your name on the left side, the sheet name code in the center, and the file name code on the right side.

9

12

Save and close the workbook. Submit e10m1Dow_LastFirst as directed.

0

Total Points

100