Dashboards for Excel Data Workstation

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

A | Q

PY, P, GR ,DY ,L

Line

D1.3

Forecast/Scenario Overview Fixed investment

All

A | Q

PY, P, GR ,DY ,L

Line

D1.4

Forecast/Scenario Overview Unemployment rate

All

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

A | Q

PY, P, GR ,DY ,L

Line

D2.3 GDP and its components Fixed investment

All

A | Q

PY, P, GR ,DY ,L

Line

D2.4 GDP and its components Government Consumption

All

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

A | Q

PY, P, GR ,DY ,L

Line

D2.8 GDP and its components Imports

All

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

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

A | Q

PY, P, GR ,DY ,L

Line

D3.6 Inflation and its determinants Effective exchange rate

All

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

A | Q

PY, P, GR ,DY ,L

Line

D4.2 Trade and balance of payments Imports of goods, volumes

All

A | Q

PY, P, GR ,DY ,L

Line

D4.3 Trade and balance of payments Exports of services, volumes

All

A | Q

PY, P, GR ,DY ,L

Line

D4.4 Trade and balance of payments Imports of services, volumes

All

A | Q

PY, P, GR ,DY ,L

Line

D4.5 Trade and balance of payments World trade

All

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

A | Q

PY, P, GR ,DY ,L

Line

D5.2 Labour market Labour supply

All

A | Q

PY, P, GR ,DY ,L

Line

D5.3 Labour market Unemployment rate

All

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

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

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.

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