Phil Hearn: Blogger, Writer & Founder of MRDC Software Ltd.

6 steps to using EPS in MRDCL to gain an advantage

EPS stands for Excel Productivity Scripting. It’s a technique built into MRDCL which allows you to construct your own templates in Excel, so that a generic MRDCL script can read instructions stored in an Excel workbook/worksheet that will generate variables, tables or whatever else you want in MRDCL.

The key steps are:

  • Consider what script you write that it is similar from project to project
  • Consider what might be different from project to project
  • Design an Excel template that is logical to use
  • Ensure that the template has all the variations different users want
  • Build the EPS template and MRDCL script to process it
  • Test, document and deploy to benefit all MRDCL users

What does that mean in plain English?

What this means is that MRDCL users have a tool at their disposal which allows them to produce anything that is laborious or complex to specify controlled by lists, settings or other keywords in an Excel spreadsheet. To use the template, the MRDCL user only sets a reference to the workbook and the worksheet and magically specifications that are laborious to script, repetitive or complex, such as variables, tables or other instructions can be automatically generated.

An example of an EPS template

Let’s bring this to life with a simple example. It may not be the most beneficial or practical, but it gives an example of what is possible. Let’s imagine that researchers produce table specifications for the DP team so that the DP team know what tables are needed. Traditionally, DP staff would ‘translate’ these instructions into MRDCL script. With EPS, these instructions can be read in situ. The specifications might like something like this:

Use EPS to automate tables and outputs from MRDCL

Ease of use

This example shows that you can easily train researchers to use this form to specify tables such that the DP team can just this spreadsheet without any alterations to produce the required analyses. You may note a few typical features of an EPS template in this example:

  • Researchers can specify what banner they want and any crosstab such as q4 by q5
  • Researchers can specify what titles they want
  • They can specify the base they want on the tables and any associated text
  • For Q6, they can specify they want some summary tables by using keywords meansummary and top2boxsummary.
  • Tables that are too complex to specify using this method can be referenced by no in the Use field with a note in the note field for the DP team. The DP team can use their own script in a file called q2table as shown in the dponly field.

Of course, this example could be extended to plug in many more features or specification details.

Benefits of this approach

This approach means that there is no duplication of effort. It also means that there is a clear, visible record of what is required, making it easier to look back on specifications if the project is repeated or part of a tracking study. Most importantly, it means that researchers are doing what they need to do and DP staff are doing what they are good at – this is typically defining variables that may be complex, tables that are non-standard and checking data issues and results. This template may not be appropriate for every company, but it shows how easy it is to design a system that will work for all sorts of data processing applications.

How EPS works with MRDCL script

Having designed an EPS template in Excel, you will need to write the MRDCL script that reads and processes the template. To do this, there is a necessity to learn another set of tools within MRDCL. These tools are not particularly easy to learn, but there are only a few tools to learn, so once understood, they become easy to apply to other situations and templates. We have a good set of videos that provide learning to understand both the theory and the practice of implementing EPS templates. Without doubt, there will be benefits to learning these techniques.

Examples of where EPS can benefit

EPS can provide this easy to understand interface for anything that is repetitive or complex. EPS templates are most commonly developed by one or two people to be shared amongst one or more users of MRDCL for use on a range of projects. They not only document specifications more clearly than a scripting language but make it easier to find and fix errors. EPS templates fall into two types – the more common type is used across a range of projects, but some can be project specific. Let’s look at each type.

Company-wide EPS templates

Three examples are:

  • The analysis of rating scales – we worked with one client who discovered that for rating scales they had 15 types of tables and summary tables they ever produced. This was developed as an Excel template so that DP staff simply had to ‘tick off’ which types of tables and what type of mean scoring they needed.
  • Open-ended questions – the production of tables from open-ended questions or from brand lists where you want sub-totals can be cumbersome where you want ranking with sub-totals and brands within each sub-total ranked. An easy to use EPS template reduces the time taken to specify sub-totals and achieve the two-level ranking by about 95%.
  • Tracking data locations – tracking studies often become difficult for DP as the questions, data locations and code lists change slightly from wave to wave. An EPS template can control all of these things, so that it either becomes a clerical task to maintain or simple software solutions can automate wave-to-wave updates saving users hours of time if it were specified manually by using script.

Project-based EPS templates

For this, I will give one example which reduced the time a client spent on a project by 15 times – yes, that’s reducing 30 days’ work down to two days. The project entailed have brands, sub-brands and pricing rates for each sub-brand which were liable to change from month to month. Each month, new sub-brands and pricing policies as well as offers had to be added to the MRDCL script to produce the required analysis. Occasionally, brands merged when there were takeovers. This required huge amounts of script that had to be edited each month. There was a large amount of recoding and merging of user data merging their purchase behaviour from an external database. EPS templates were built to manage the whole process. The task became a simple clerical task updating a few spreadsheets; the rest was automated from the EPS worksheets. It meant that rather than a highly-skilled MRDCL user managing the project, the project was managed by someone with good Excel skills, who was backed by the highly-skilled MRDCL user for about one hour per month on average.

Implementing EPS templates

To succeed with EPS, it is important to approach the task of implementation thoughtfully. Firstly, not all MRDCL users need to learn how to program EPS in MRDCL. Often, it is not the most talented MRDCL users that are best at building EPS templates, it is the person who can design tools well and think about where EPS can really benefit. Talking to colleagues and agreeing what tools would be of most benefit is an important starting point. Considering all the different variations that different users might want is better to program at the outset. Editing EPS templates can be difficult as the typical process is to develop an EPS templates, test it, document and then never look at the script that drives it again.

Managing EPS templates

Keeping the EPS templates in an organised fashion and documenting what they do and how they work is important. Making the templates logical to use is worthy of careful consideration and sharing ideas with colleagues. Once in place, you can expect some huge benefits. As one EPS user told me that they spent one day developing an EPS template that has been used about 700 times saving about an hour each time – that’s almost 100 days.

Need more help?

If you need more help to get started or suggestions on how to learn EPS, please contact me, phil.hearn@mrdcsoftware.com. There are also a series of videos that take you through the learning process – covering why to use, when to use and how to use.