Dashboards for Excel Data Workstation Version 3.0 (aka Mondrian)
There is a new VERSION 3.0 of the Excel Plugin. This post refers to VERSION 2.0 compatible dashboards but the information herein still applies to VERSION 3.0 compatible dashboards – To install Excel Plugin VERSION 3.0 and download compatible dashboards, please go here.
Looking for INDUSTRY Mondrian 3.0 – Please go here.
Quick Links
Overview
Oxford Economics Excel Data Workstation has proved to be immensely popular with customers for automating downloads of economic forecast data from Oxford Economics’ various data services platforms.
We would like to introduce you to Mondrian Dashboards Version 3.0 - a new set of compelling examples of high quality dashboards built for Excel Data Workstation.
Mondrian Dashboards v3.0 reproduces all the built-in dashboards of the new Global Model Workstation and Global Data Workstation:
- Forecast/Scenario Overview
- GDP and its components
- Inflation and its determinants
- Trade and balance of payments
- Labour market
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 display properly across all supported Excel platforms, regardless of the number of data series being plotted. Thanks for your feedback which helped greatly.
(Mondrian Dashboards v3.0 uses the latest Excel Power Query and Power Pivot technology in Excel 2013 and Excel 2016. If you don’t have access to this, then please look at Version 1.0 and Version 2.0 which support Excel 2010 and lower.)
Global Data Workstation Dashboard
Data Source
Global Economics Databank
Location Mnemonics
All Locations available for both Quarterly and Annual data. This can be changed in the configuration worksheet.
Variable Mnemonics
Level Values | PY | P | GR | DY : GDP, C, CPI, PSH, UP, RLG, RCB, WCR, BCU%, GB%, GGDBT%, IF, GC, IS%, DOMD, X, M, PGDP, ER, ERREAL, PROD, WC, RX, WPO, WPC, XG, MG, XS, MS, WT, BVI%, BCU%, ET, LS, PART, POPW
Prerequisites
Important - Please Read
- You have installed Oxford Economics Excel Data Workstation (>= Version 2.5).
This is a free add-on to any Oxford Economics data subscription.
Please go here for more details: https://www.oxfordeconomics.com/techlabs/excel-data-workstation-v2-0-excel-plugin - You are using at minimum Excel 2013
- Excel 2013 64-bit is recommended, but it should work with Excel 2013 32-bit, however will run slower or run out of memory - You have installed Excel Power Query matching the bitness of your installed version of Excel
- Power Query is included in Excel 2016
- You can install Excel 2013 64-bit & 32-bit Power Query from here: https://www.microsoft.com/en-gb/download/details.aspx?id=39379
Please note, since we use Power Query together with Power Pivot’s data model, this workbook is not compatible with Excel 2010 and lower. - You have enabled Power Pivot functionality in Excel (via Options | Add-ins | COM Add-ins)
- You have a subscription to Oxford Economics Global Economics Databank
- You have installed Oxford Economics Global Data Workstation.
You do not require a subscription to all countries and region aggregates and the dashboards will automatically adjust to accommodate this.
Chart Definitions
ID |
Dashboard |
Chart Title |
Location Mnemonic(s) |
Variable Mnemonic(s) |
Frequency |
Measurement |
Chart Style |
D1.1 |
Forecast/Scenario Overview | GDP |
All |
GDP |
A | Q |
PY, P, GR ,DY ,L |
Line |
D1.2 |
Forecast/Scenario Overview | Consumer spending |
All |
C |
A | Q |
PY, P, GR ,DY ,L |
Line |
D1.3 |
Forecast/Scenario Overview | Fixed investment |
All |
IF |
A | Q |
PY, P, GR ,DY ,L |
Line |
D1.4 |
Forecast/Scenario Overview | Unemployment rate |
All |
UP |
A | Q |
L, DY |
Bar |
D1.5 |
Forecast/Scenario Overview | CPI |
All |
CPI |
A | Q |
PY, P, GR ,DY ,L |
Line |
D1.6 |
Forecast/Scenario Overview | Central Bank policy rate |
All |
RCB |
A | Q |
L , DY |
Line |
D1.7 |
Forecast/Scenario Overview | Bond yields |
All |
RLG |
A | Q |
L , DY |
Line |
D1.8 |
Forecast/Scenario Overview | Equity prices |
All |
PSH |
A | Q |
PY, P, GR ,DY ,L |
Line |
D1.9 |
Forecast/Scenario Overview | Competitiveness |
All |
WCR |
A | Q |
PY, P, GR ,DY ,L |
Line |
D1.10 |
Forecast/Scenario Overview | Current account balance (% of GDP) |
All |
BCU% |
A | Q |
L, DY |
Line |
D1.11 |
Forecast/Scenario Overview | Government balance (% of GDP) |
All |
GB% |
A | Q |
L, DY |
Bar |
D1.12 |
Forecast/Scenario Overview | Government debt (% of GDP) |
All |
GGDBT% |
A | Q |
L , DY |
Bar |
ID |
Dashboard |
Chart Title |
Location Mnemonic(s) |
Variable Mnemonic(s) |
Frequency |
Measurement |
Chart Style |
D2.1 | GDP and its components | GDP |
All |
GDP |
A | Q |
PY, P, GR ,DY ,L |
Line |
D2.1 | GDP and its components | Consumer spending |
All |
C |
A | Q |
PY, P, GR ,DY ,L |
Line |
D2.3 | GDP and its components | Fixed investment |
All |
IF |
A | Q |
PY, P, GR ,DY ,L |
Line |
D2.4 | GDP and its components | Government Consumption |
All |
GC |
A | Q |
PY, P, GR ,DY ,L |
Line |
D2.5 | GDP and its components | Stockbuilding (% of GDP) |
All |
IS% |
A | Q |
DY, L |
Bar |
D2.6 | GDP and its components | Domestic demand |
All |
DOMD |
A | Q |
PY, P, GR ,DY ,L |
Line |
D2.7 | GDP and its components | Exports |
All |
X |
A | Q |
PY, P, GR ,DY ,L |
Line |
D2.8 | GDP and its components | Imports |
All |
M |
A | Q |
PY, P, GR ,DY ,L |
Line |
ID |
Dashboard |
Chart Title |
Location Mnemonic(s) |
Variable Mnemonic(s) |
Frequency |
Measurement |
Chart Style |
D3.1 | Inflation and its determinants | Consumer Price Index |
All |
CPI |
A | Q |
PY, P, GR ,DY ,L |
Line |
D3.2 | Inflation and its determinants | GDP deflator |
All |
PGDP |
A | Q |
PY, P, GR ,DY ,L |
Line |
D3.3 | Inflation and its determinants | Average earnings |
All |
ER |
A | Q |
PY, P, GR ,DY ,L |
Line |
D3.4 | Inflation and its determinants | Productivity |
All |
PROD |
A | Q |
PY, P, GR ,DY ,L |
Line |
D3.5 | Inflation and its determinants | Unit labour costs, whole economy |
All |
WC |
A | Q |
PY, P, GR ,DY ,L |
Line |
D3.6 | Inflation and its determinants | Effective exchange rate |
All |
RX |
A | Q |
PY, P, GR ,DY ,L |
Line |
D3.7 | Inflation and its determinants | World oil price $pb |
All |
WPO |
A | Q |
PY, P, GR ,DY ,L |
Line |
D3.8 | Inflation and its determinants | Non-oil commodity prices |
All |
WPC |
A | Q |
PY, P, GR ,DY ,L |
Line |
ID |
Dashboard |
Chart Title |
Location Mnemonic(s) |
Variable Mnemonic(s) |
Frequency |
Measurement |
Chart Style |
D4.1 | Trade and balance of payments | Exports of goods, volumes |
All |
XG |
A | Q |
PY, P, GR ,DY ,L |
Line |
D4.2 | Trade and balance of payments | Imports of goods, volumes |
All |
MG |
A | Q |
PY, P, GR ,DY ,L |
Line |
D4.3 | Trade and balance of payments | Exports of services, volumes |
All |
XS |
A | Q |
PY, P, GR ,DY ,L |
Line |
D4.4 | Trade and balance of payments | Imports of services, volumes |
All |
MS |
A | Q |
PY, P, GR ,DY ,L |
Line |
D4.5 | Trade and balance of payments | World trade |
All |
WT |
A | Q |
PY, P, GR ,DY ,L |
Line |
D4.6 | Trade and balance of payments | Relative unit labour costs |
All |
WCR |
A | Q |
PY, P, GR ,DY ,L |
Line |
D4.7 | Trade and balance of payments | Visible trade balance (% of GDP) |
All |
BVI% |
A | Q |
L , DY |
Bar |
D4.8 | Trade and balance of payments | Current account balance (% of GDP) |
All |
BCU% |
A | Q |
L , DY |
Bar |
ID |
Dashboard |
Chart Title |
Location Mnemonic(s) |
Variable Mnemonic(s) |
Frequency |
Measurement |
Chart Style |
D5.1 | Labour market | Total employment |
All |
ET |
A | Q |
PY, P, GR ,DY ,L |
Line |
D5.2 | Labour market | Labour supply |
All |
LS |
A | Q |
PY, P, GR ,DY ,L |
Line |
D5.3 | Labour market | Unemployment rate |
All |
UP |
A | Q |
L , DY |
Bar |
D5.4 | Labour market | Participation rate |
All |
PART |
A | Q |
L , DY |
Bar |
D5.5 | Labour market | Productivity |
All |
PROD |
A | Q |
PY, P, GR ,DY ,L |
Line |
D5.6 | Labour market | Average earnings growth |
All |
ER |
A | Q |
PY, P, GR ,DY ,L |
Line |
D5.7 | Labour market | CPI |
All |
CPI |
A | Q |
PY, P, GR ,DY ,L |
Line |
D5.8 | Labour market | Population of working age |
All |
POPW |
A | Q |
PY, P, GR ,DY ,L |
Line |
Dashboard
Excel File: Global Data Workstation Dashboards v3.3.xlsm
Example below shows: GDP and its components dashboard – GDP chart is zoomed | Quarterly data series values | Different measurements | 5 years forecast | Four countries (United Kingdom, France, Germany and United States)
Dashboard Configuration
A handy configuration sheet is provided which contains a form and data tables which enables you to configure data measurements and locations to download. The dashboards adjust themselves automatically.
Global Model Workstation Dashboard
Data Source
Desktop Model Databases
Location Mnemonics
All Locations available for both Quarterly and Annual data. This can be changed in the configuration worksheet.
Variable Mnemonics
Level Values | PY | P | GR | DY : GDP, C, CPI, PSH, UP, RLG, RCB, WCR, BCU%, GB%, GGDBT%, IF, GC, IS%, DOMD, X, M, PGDP, ER, ERREAL, PROD, WC, RX, WPO, WPC, XG, MG, XS, MS, WT, BVI%, BCU%, ET, LS, PART, POPW
Prerequisites
Important - Please Read
- You have installed Oxford Economics Excel Data Workstation (>= Version 2.5).
This is a free add-on to any Oxford Economics data subscription.
Please go here for more details: https://www.oxfordeconomics.com/techlabs/excel-data-workstation-v2-0-excel-plugin - You are using at minimum Excel 2013
- Excel 2013 64-bit is recommended, but it should work with Excel 2013 32-bit, however will run slower or run out of memory - You have installed Excel Power Query matching the bitness of your installed version of Excel
- Power Query is included in Excel 2016
- You can install Excel 2013 64-bit & 32-bit Power Query from here: https://www.microsoft.com/en-gb/download/details.aspx?id=39379
Please note, since we use Power Query together with Power Pivot’s data model, this workbook is not compatible with Excel 2010 and lower. - You have enabled Power Pivot functionality in Excel (via Options | Add-ins | COM Add-ins)
- You have a subscription to Oxford Economics Global Economic Model
- You have installed Oxford Economics Global Model Workstation.
You do not require a subscription to all countries and region aggregates and the dashboards will automatically adjust to accommodate this.
Please note, you must be connected to Global Model Workstation and have the View & Download Data application open in order to refresh the model dashboards.
Chart Definitions
As above.
Dashboard Configuration
As above.
Dashboard
Excel File: Excel File: Global Model Workstation Dashboards v3.3.xlsm
Example below shows: GDP and its components dashboard | Annual data series values | 5 years forecast | Two scenario bases (Base_Jan16 and Fed_Jan16) | Three of four countries have been selected (United Kingdom, France and Germany)
Download
Mondrian Excel dashboards are packaged as ZIP files and are available for download in the Oxford Economics website archive.
- Go to the archive: https://www.oxfordeconomics.com/archive
- Click the Globe icon
- Type Excel Dashboard in the left hand search box.
- Click the search icon or hit Enter.
- Choose Version 3.0 of the specific dashboard(s) to download.
-- Databank users should choose Excel Dashboard for Global Data Workstation – Version 3.0
-- Model users should choose Excel Dashboard for Global Model Workstation – Version 3.0 - (You will also see links for Version 1.0 and Version 2.0)
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
LOOKING FOR MONDRIAN 2.0 – PLEASE GO HERE.
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 Global Data Workstation Dashboards v3.3-20160520.zip’, unblock it as described above, and then extract ‘Global Data Workstation Dashboards v3.3.xlsm’.
Open it. You may need to enable macros.
Go to the Overview worksheet tab and please ensure you satisfy all prerequisites.
Click ‘Refresh Dashboard’. You will be advised to login if you are not already logged in, and to install the latest version of Excel Data Workstation if you don’t have it.
Global Model Workstation
Download the zipped Excel File ‘Mondrian Global Model Workstation Dashboards v3.3-20160520.zip’, unblock it as described above, and then extract ‘Global Model Workstation Dashboards v3.3.xlsm’.
Open it. You may need to enable macros.
Go to the Overview worksheet tab and please ensure you satisfy all prerequisites.
Go to the Config worksheet tab to configure measures and locations you wish to download.
Click ‘Refresh Dashboard’ on any dashboard worksheet or the Config worksheet. You will be advised to login if required, and 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
Power Query and Power Pivot functionality
The dashboards are created using standard Excel Pivot Charts capabilities. The raw data is manipulated using the Power Query tool, and the Pivot Table data model is built using the Power Pivot tool. The combined use of Power Query and Power Pivot is not compatible with Excel 2010 and lower. Excel 2013 64-bit is recommended, though it should work with Excel 2013 32-bit as well, but will perform more slowly and could run out of memory.
Automation
Although the dashboards leverage 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 Data’ command is implemented in the Main module. Other useful code is in Miscellaneous and ZoomCharts modules.
Performance
Although Power Query and Power Pivot technologies greatly minimise the memory footprint required to produce these complex dashboards in Excel, if you have other applications running at the same time they can negatively impact dashboard performance.
The dashboard for Global Data Workstation is refreshed via a script executed by Excel Data Workstation in the raw data sheet. This script has a predefined set of indicators, period, data measures, and locations. Any changes to the script which results in an increase of the amount of data downloaded, will affect performance.
The limits of your Excel application performance depends on your PC environment, so please contact your local IT support to increase computer memory, upgrade to 64-bit Excel 2013, or simply restrict the number of countries and/or measurements in the Config worksheet.
The settings in the Config worksheet create a download script under the covers. For example, instead of this script which downloads all quarterly data for all locations and all measures:
get @level, @pach, @pch, @pchy, @diff from db.Desktop where Indicator in GDP, C, CPI, PSH, UP, RLG, RCB, WCR, BCU%, GB%, GGDBT%, IF, GC, IS%, DOMD, X, M, PGDP, ER, PROD, WC, RX, WPO, WPC, XG, MG, XS, MS, WT, BVI%, BCU%, ET, LS, PART, ERREAL, POPW and Year from @now - 1 to @now + 4 with Frequency = Quarterly
… you can use the Config worksheet to create a much more conservative script like this for just two locations in two measures. When this script is run it will replace all data in the data worksheets:
get @level, @pch from db.Desktop where Location in USA, GBR and Indicator in GDP, C, CPI, PSH, UP, RLG, RCB, WCR, BCU%, GB%, GGDBT%, IF, GC, IS%, DOMD, X, M, PGDP, ER, PROD, WC, RX, WPO, WPC, XG, MG, XS, MS, WT, BVI%, BCU%, ET, LS, PART, ERREAL, POPW and Year from @now - 1 to @now + 4 with Frequency = Quarterly
Note, the script uses db.[Global Economics] instead of db.Desktop when connecting to Global Data Workstation.
You will definitely get better performance using 64-bit Excel with lots of computer memory. To free memory, close as many applications as you can before starting Excel.
Maintenance
As each dashboard is in a separate worksheet, it adds to the complexity of the Excel workbook and could pose a maintenance overhead if you’re not careful when making changes.
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 Menu | General | Arrow | User Guide).
Oxford Economics can provide support and training to help you create your own dashboard customisations. Please contact your nearest sales representative.
Arvindra Sehmi is Chief Information Officer and Director of IT at Oxford Economics.
Shweta Das is Technology Marketing and Support Analyst at Oxford Economics.