Looking for MACRO Mondrian 3.0 – Please go here.

Looking for INDUSTRY Mondrian 3.0 – Please go here.

Quick Links

Download | Installation

Overview

Recently Oxford Economics released Excel Data Workstation which is one of three complementary platforms in Oxford Economics Global Data Services.

In this post I want to introduce you to Mondrian Version 2.0 - a compelling example of high quality dashboards built for Excel Data Workstation which:

• Displays high quality charts of 12 key macro-economic indicators for 46 countries.
• Retrieves forecast data series from either Global Data Workstation orGlobal Model Workstation.
• Refreshes data on-demand using built-in automation of Excel Data Workstation commands.

The primary difference from Version 1.0 is zero configuration. You can download the Excel workbooks and get going immediately. Of course, you need the latest version of our Excel add-in but the dashboards themselves run OOTB - out-of-the box! We placed the big dashboard refresh button in a more obvious position and improved the styling of pivot charts so they are stable across all supported Excel platforms, regardless of the number of data series being plotted. Thanks for your feedback which helped greatly.

Global Data Workstation Dashboard

Data Source

Global Economics Databank

Location Mnemonics

SAU, ZAF, ARG, BRA, CAN, CHL, MEX, USA, AUS, CHN, HKG, IND, IDN, JPN, MYS, PHL, SGP, KOR, TAI, THA, AUT, BEL, BGR, HRV, CZE, DNK, FIN, FRA, DEU, GRC, HUN, IRL, ITA, NLD, NOR, POL, PRT, ROM, RUS, SVK, ESP, SWE, CHE, TUR, GBR, EUZ, ARE

Variable Mnemonics

Level Values: UP, RCB, RLG, PSH, WCR, BCU%, GB%, GGDBT%, CPI%YR
PY|P|GR Values: GDP, C, CPI, IF

Prerequisites

1. You have installed Oxford Economics Excel Data Workstation (version 2.1 and above).
2. You have a subscription to the Global Economics Databank in Oxford Economics Global Data Workstation.
3. You have created two predefined saved selections in your Global Data Workstation account. Running a dashboard refresh will take you through the steps required to add them.

 

Chart Definitions

No.	Chart Title	Location Mnemonic(s)	Variable Mnemonic(s)	Frequency	Measurement	Chart Style
1	GDP (% change)	All	GDP	Quarterly	PY, P, GR	Line
2	Household consumption (% change)	All	C	Quarterly	PY, P, GR	Line
3	Investment (% change)	All	IF	Quarterly	PY, P, GR	Line
4	Unemployment rate	All	UP	Quarterly	L	Bar
5	CPI inflation (% change)	All	CPI (CPI%YR)	Quarterly	PY	Line
6	Central Bank policy rate	All	RCB	Quarterly	L	Line
7	Bond yields	All	RLG	Quarterly	L	Line
8	Equity prices	All	PSH	Quarterly	L	Line
9	Competitiveness	All	WCR	Quarterly	L	Line
10	Current account (% of GDP)	All	BCU%	Quarterly	L	Line
11	Government balance (% of GDP)	All	GB%	Quarterly	L	Line
12	Government debt (% of GDP)	All	GGDBT%	Quarterly	L	Bar

 

Dashboard

Excel File: Mondrian Dashboard (Excel Pivot)-GDW 2.xlsm

Example below shows: Nine charts – One zoomed | Quarterly data series values | Multiple measurements | 5 years forecast | Four countries (United Kingdom, France, Germany and United States)

 

Global Model Workstation Dashboard

Data Source

Desktop Model Databases

Location Mnemonics

SAU, ZAF, ARG, BRA, CAN, CHL, MEX, USA, AUS, CHN, HKG, IND, IDN, JPN, MYS, PHL, SGP, KOR, TAI, THA, AUT, BEL, BGR, HRV, CZE, DNK, FIN, FRA, DEU, GRC, HUN, IRL, ITA, NLD, NOR, POL, PRT, ROM, RUS, SVK, ESP, SWE, CHE, TUR, GBR, EUZ, ARE

Variable Mnemonics

Level Values: UP, RCB, RLG, PSH, WCR, BCU%, GB%, GGDBT%, CPI%YR
PY|P|GR Values: GDP, C, CPI, IF

Prerequisites

1. You have installed Oxford Economics Excel Data Workstation (version 2.1 and above).
2. You have a subscription to Oxford Economics Global Economic Model.
3. You have installed Oxford Economics Global Model Workstation.
4. You have created two predefined saved selections in Global Model Workstation. Running a dashboard refresh will take you through the steps required to add them.

 

Chart Definitions

As above.

Dashboard

Excel File: Mondrian Dashboard (Excel Pivot)-GMW 2.xlsm

Example below shows: All 12 charts | Annual data series values | 10 years forecast (some years deselected) | Two scenario bases (Baseline and RiskOff) | Two countries (United Kingdom and United States)

 

Download

Mondrian Excel dashboards are packaged as ZIP files and are available for download in the Oxford Economics website archive.

1. Go to the archive: https://www.oxfordeconomics.com/archive
2. Click the Globe icon
3. Type Excel Dashboard in the left hand search box.
4. Click the search icon or hit Enter.
5. Choose Version 2.0 of the specific dashboard(s) to download.

 

 

ZIP files should be “unblocked” before their contents are extracted. After you have downloaded a Mondrian Dashboard ZIP file right-click it in Windows Explorer, choose Properties from the context menu, click the Unblock button in the lower right-hand corner of the resulting dialog, and hit OK.

 

Installation

Please note: Dashboards are distributed with randomised forecast data. To load data you must have a trial or full subscription to Oxford Economics Data Services. Please contact us for more information.

Global Data Workstation

Download the zipped Excel File ‘Mondrian Dashboard (Excel Pivot)-GDW 2-20150301.zip’, unblock it as described above, and then extract ‘Mondrian Dashboard (Excel Pivot)-GDW 2.xlsm’. The script files included with the download are for your information only and are built-in to the dashboard already.

Open it. You may need to enable macros.

Go to the Overview spreadsheet tab and please ensure you satisfy all prerequisites.

Click ‘Refresh Dashboard’. You will be advised to install the latest version of Excel Data Workstation if you don’t have it.

Global Model Workstation

Download the zipped Excel File ‘Mondrian Dashboard (Excel Pivot)-GMW 2-20150301.zip’, unblock it as described above, and then extract ‘Mondrian Dashboard (Excel Pivot)-GMW 2.xlsm’. The script files included with the download are for your information only and are built-in to the dashboard already.

Open it. You may need to enable macros.

Go to the Overview spreadsheet tab and please ensure you satisfy all prerequisites.

Click ‘Refresh Dashboard’. You will be advised to install the latest version of Excel Data Workstation if you don’t have it.

That’s it… it really is as simple as that!

Implementation

Automation

Although Mondrian leverages a comprehensive Excel Data Workstation VBA automation interface, only interested users and those who want to make breaking changes will ever need to look at the code. All the code is available to inspect and reuse as you wish, but most of it is encapsulation of Excel Data Workstation’s interop interface and shouldn’t be touched by the end-user.

The ‘Refresh Dashboard’ command is implemented in the Main module. Other useful code is in Miscellaneous and ZoomCharts modules.

Standard pivot functionality

To make this dashboard broadly useable across several versions of 32-bit and 64-bit Excel 2007 | 2010 | 2013, it is implemented as simply as possible. Only standard Excel capabilities like Pivot Tables and Pivot Charts were used. Under the covers quite a number of Pivot Tables are required to model the different data projections required for each dashboard chart.

If you want to see the dashboard’s pivot tables, open the Overview workbook tab and click Unhide Pivot & Data Sheets button. When you’re done, click Hide Pivot & Data Sheets button.

Performance

Pivot Tables (and Pivot Charts) take up memory and can negatively impact performance as they grow larger, especially as more countries (and scenario bases) are selected for viewing. The need for both quarterly and annual pivots further compounds memory utilisation and performance pressure.

The dashboard for Global Data Workstation is refreshed off a selection with a predefined and limited set of countries. The one for Global Model Workstation is similar, but has the additional ability to display data from all bases simultaneously loaded in Global Model Workstation. If you increase the number of countries in the source selections (in either Global Data Workstation or Global Model Workstation) and load more bases then you could dramatically increase the amount of data downloaded into the dashboard.

Of course, the above changes will increase the size of Pivot Table data and could cause Excel to struggle. Where your Excel breaking limits are depends on your PC environment, so please contact your local IT support to increase computer memory, upgrade to 64-bit Excel 2013, or you can simply reduce the selection size.

You will definitely get better performance using 64-bit Excel and having lots of computer memory. To free memory, close as many applications as you can before starting Excel.

Maintenance

As each Pivot Table is in a separate spreadsheet, it adds to the complexity of the Excel workbook and could pose a maintenance overhead if you’re not careful when making changes.

Power Pivot

The advanced data modelling features available in Power Pivot could have been used to create a single data model underpinning and driving all Pivot Charts and slicers (data filters), eliminating the need for individual Pivot Tables in the standard Excel design. However, Power Pivot works best in 64-bit Excel 2013 drastically limiting the usefulness of this solution to Oxford Economics’ customer ecosystem.

If you’d like a proof-of-concept version of a dashboard using Power Pivot, then send me an email (asehmi [at] oxfordeconomics.com).

Support

If you encounter any problems with Excel Data Workstation, please contact Oxford Economics for support via email at support [at] oxfordeconomics.com, but before you do so, please attach a copy of the Log file. You can find the Log file by following the instructions in the User Guide.

Oxford Economics can provide support and training to help you create your own dashboard customisations. Please contact your nearest sales representative.

 

Let us know what you think by leaving us a comment on this post.

Enjoy!