Guaranteed Higher Grade!
Free Quote
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.
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:
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:
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:
NTU Grading System in Singapore: A Complete Guide for Students
NTU in Singapore is considered to be one of the most popular educational institutions across the gl
Top 9 Private Universities in Singapore: Your Guide to Higher Education
If there is one thing Asian countries never compromise on, it is education. Particularly, Singapore
How to Format an Essay: MLA, APA, Chicago Styles
Many of you know how to write an essay. However, you guys struggle when it comes to formatting the
Extemporaneous Speech – Examples & Writing Guide
“According to most studies, people’s number one fear is public speaking. Number two is death. D
