I need help in building a database and  automate reports with the use of Power Query, Power Piv

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

I need help in building a database and  automate reports with the use of Power Query, Power Pivot, Pivot Tables and VBA.

 

 

ACFI3425 Advanced Business Intelligence – Coursework 2 2019/20

 

Coursework Description:

 

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. 

Relevant Learning Outcomes:

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.

 

 

 

 

Requirements:

Save your file regularly

Dynamic data 

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) Save your file regularly

 

Static data:

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)

Save your file regularly

 

13. Create a dashboard to show:

•      Two pivot tables

•      Two pivot charts

•      Two descriptive statistics tables

•      Two buttons for subroutines from step (7, 8)

 

(20 marks)

 

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme