Dow_Jones ? You are an intern for Hicks Financial, a small trading company ?located in Toledo, Ohio. Your inter
Exp19_Excel_Ch10_ML1_Dow_Jones
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.
Use Get & Transform tools to import and transform Table 0 located in the file e10m1Index.txt. Split the company column using space as the delimiter at the left-most occurrence. Rename the respective columns Symbol and Company. Remove the columns Change, %Change, and Volume. Name the table Dow and load it to the existing worksheet.
Hint: Get Data is located on the Data tab in the Get & Transform Data group.
Name the worksheet Current_Price.
Hint: Double-click the worksheet tab to edit the sheet name.
Use Power Query to import trade data located in the workbook Exp19_Excel_Ch10_GRADER_ML1-TradeInfo.csv. Before loading the data, if necessary use the Power Query Editor to remove the columns that contain NULL values. Split the company column using the left most space as the delimiter and rename the respective columns Symbol and Company Name.
Hint: Get Data is located on the Data tab in the Get & Transform Data group. Click Edit on the Import window to load the Power Query Editor.
Rename the worksheet Trades.
Hint: Double click the worksheet tab to edit the sheet name.
Add the Dow table and the Exp19_Excel_Ch10_GRADER_ML1-TradeInfo table to the Data Model.
Hint: Manage Data Model is located on the Data tab in the Data Tools group.
Use Power Pivot to create the following relationship:
Table
Exp19_Excel_Ch10_GRADER_ML1-TradeInfo
Field
Symbol
Table
Dow
Field
Symbol
Hint: Relationships is located on the Data tab in the Data Tools group.
Use Power Pivot to create a PivotTable with the EXP19_Excel_Ch110_GRADER_ML1_TradeInfo Date field as a Filter, Last Price as a value, and the Dow table Company Name as Rows.
Hint: Power Pivot is an Excel add-in that must be loaded. Power Pivot can be loaded by accessing COM add-ins in Excel options. Once loaded, the Power Pivot tab is located on the Office ribbon.
Create a Clustered Column PivotChart based on the PivotTable that compares the trading price of Apple and Coca-Cola stocks.
Hint: PivotChart is located on the Analyze tab in the Tools group.
Add the chart title Trading Comparison, apply Accounting Number Format to cells C4:C5, and name the worksheet Price_Comparison.
Hint: Chart elements can be added or edited on the Design tab in the Chart Layouts group.
Delete Sheet 1, if necessary.
Hint: Delete is located on the Home tab in the Cells group.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.