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).

http://money.cnn.com/data/markets/dow

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