Automating Reports with Power Query

Building a Database and Automating Reports using Power Query, Power Pivot, Pivot Tables and VBA

Answered

Relevant Learning Outcomes

This assessment engages you with the process of building a database, automate reports and utilise your experience in using Power Query, Power Pivot, Pivot Tables and VBA.

a) Apply presentation and visualisation methods for efficient and dynamic reporting on share performance against market index.

b) Formulate Macros and VBA codes to automate calculations and reporting.

c) Consolidate multi-dimensional data in a database using Power Query features in Excel.

Individual coursework - please check the company allocated to you
The coursework weighs 50% of total module grade
Due date: Friday 03/04/2019 by 11:59 PM
Submission Link: Assessment tab – Coursework 2
You need to use Windows system not Apple IOS.
Save your work regularly.
Save your Excel file using your PNUMBER as your file name.
There will be penalties imposed for the late hand in of coursework without permission.

Penalties will be imposed for unauthorised late hand-in of work, as follows:

a) Up to 14 days late without permission Max. mark 40%

b) More than 14 days late without permission 0%

1. Create two Excel queries to import the monthly historical data (from 01/01/2017 to present) from yahoo finance for:

FTSE100 index
The company allocated to you (check student-company list and yahoo finance link).

2. In a new worksheet - name it (PIVOT TABLES- CHARTS), create two pivot tables for FTSE100 and the company to show:

a) % Change in adjusted close price per year and month

b) % Change in volume per year and month

c) Format pivot tables to show in tabular form and remove grand totals.

3. Create a macro which will insert a pivot chart and appropriately automate formatting the charts (remove gridlines, lock size by not allowing it to move or size with cells, hide all field buttons). Hint: record the macro while inserting 1 combo pivot chart for FTSE100 pivot table. Test your macro to insert a pivot chart for the other pivot table.

This part of your report is linked to yahoo finance and the query helps update the pivot tables and charts whenever you refresh the data. (30 marks)

For the following part, things to keep in mind:

Decide whether you record a macro or write a subroutine in absolute or relative mode. Write a description for subroutine/macro in the visual basic editor or macro recorder.

Ensure that you integrated debugging techniques while writing your subroutine code or recording your macro.

4. Load the data from the two data queries as tables in a new worksheet (or multiple sheets as you wish) – name the worksheet: STATIC DATA

5. Create a macro which calculates a new column “rate of return”. Rate of return=LOG(current month’s adjusted close/ previous month’s adjusted close). LOG is a mathematical function. Ensure all numbers appear as a percentage with two decimal places only and that there are no error values in all cells.

6. Create two macros: one for your allocated share and one for FTSE100, which automates a table creation, and shows the following descriptive statistics for the rate of return:

a. Minimum

b. Maximum

c. Standard deviation

d. Average

Format numbers as percentage with two decimal places only.

7. Create a subroutine which returns a message box to show how many months the rate of return is below the average rate of return.

8. Create a subroutine which returns a message box to report on whether the standard deviation of the company’s share is greater/ less than the standard deviation of that for the FTSE100.

9. Create a macro to format background of a dashboard with a solid colour.

10. Create a macro to copy and paste charts to a dashboard.

11. Create a macro which will copy and paste pivot tables as linked pictures and format table background with white fill.

12. Assign a button for each macro and subroutine you created in step 3, 5, 6, 7, 8, 9, 10, 11. Label each button appropriately and place the buttons where the user can use them. (50 marks)

13. Create a dashboard to show: