Mitigating End User Computing Risk with Excel
The risk associated with End User Computing, or EUC, is something that companies more and more are taking seriously. End User Computing is, put simply, any system that allows and end user to perform complex computations outside of the prescribed solutions available to them.
A typical example in the finance industry is when a trader develops their own trading model in Excel, which they then use to put on trades in the market. This model is outside of the firm’s risk system, and so the risk management team or model validation teams have no way of verifying the model, and they may not even know of its existence.
There have been several high profile cases where complex Excel spreadsheets have been found to have errors that have later cost those businesses millions.
It’s important to point out that in the vast majority of cases users do not use End User Computing to intentionally circumvent policies or procedures set by their employer. The most common motivation is that the end user knows the most about the problem they are trying to solve, and often they are the best equipped to solve that problem and they use the tools available to them.
Why End User Computing can be a problem
Reports and data generated as the result of End User Computing (e.g. Excel) is accepted and trusted by management, other end users, and potentially clients.
Errors in calculations are hard to track down and can be propagated between different versions of the same report, in the case of spreadsheets. Even if one user identifies and fixes a bug in one spreadsheet, how many times has that same bug been copied and pasted into other sheets?
Testing of code written in spreadsheets is virtually impossible. In the case of Excel, VBA (Excel’s scripting language) is embedded in the spreadsheet and so cannot be tested in the same ways as are standard practice in professional software development.
Code written in an EUC environment cannot be audited effectively. When code is embedded in spreadsheets there is no central location you can go to for the code used to run your business, it is scattered about in different spreadsheets across different users and teams, often with little accountability for the quality and correctness of the code.
It is not uncommon for an individual to build their own complex ‘master sheet’ containing everything they need to perform their job. With it they are super-productive and everything’s great, but what happens when that person has to hand over to someone else? Suddenly that spreadsheet that they have carefully maintained for years is revealed for what it is — a house of cards that no one else dares touch!
What’s the Goal?
In certain software development circles the End User is sometimes seen as The Enemy. If you are a developer you may be thinking to yourself that banning the use of Excel would be a good solution, but let me explain why it’s not.
Why are End Users using Excel in the first place? Excel is an extremely powerful tool and it helps them do their job more effectively than they would be able to without it — that’s why they use it!
If we were to take Excel away, every little piece of functionality in their spreadsheets would have to be replaced with a number of different applications. To work effectively all of those applications would need to be neatly integrated. Furthermore, each time a user needed to do some ad-hoc calculation or experiment with a new idea, that would require a developer to write new functionality to perform that. Do you have the spare development resources to deal with that?
Even if this was feasible (and companies are trying this) ask yourself, does it make the final experience better or worse for the End User? If you don’t know the answer to that yet just wait for the requirement to come in for an “Export To Excel” button to be added to your fancy new app! Now you have two problems — you have a lot more stuff to maintain and you have Excel spreadsheets!
What’s actually important in all of this?
- Empowering End Users with a set of tools to help them perform.
- Ensuring critical code is tested and auditable.
- Maintaining control over business logic and data outside of Excel.
- Keeping a level of flexibility without endangering the integrity, auditability and reproducability of results.
What’s the Solution?
Rather than thinking of Excel as a tool with built-in functions for performing low level operations (which can quickly spiral out of control), what if we were to use Excel as a front-end or interactive calculator to higher level functions, built specifically for the needs of the End User?
A common source of errors in Excel spreadsheets is where data is copied and pasted from another system. This is especially true when copying data from multiple different systems, or from other spreadsheets. Keeping track of the source, and integrity, of the data used in a spreadsheet is a difficult problem.
Imagine if instead of copying and pasting the data needed, Excel had functions for retrieving the data from these external systems directly. No more copy and paste errors, the source of the data is clear from the function used, and the integrity of the data is ensured because it’s sourced directly.
The other main problems with complex Excel spreadsheets are unwieldy VBA code and spaghetti like cell relationships. These are a by-product of using too low level functionality to achieve complex results. What if we could replace these with higher level functions designed and built specifically to solve problems in our specific domain?
For example, you wouldn’t write VBA code or create a complex sheet full of intermediate results to compute the IRR of a set of cashflows, you would use the built-in “IRR” or “XIRR” functions that are part of Excel. What about other problems, for example the pricing of an Interest Rate Swap? Instead of the End User creating a spreadsheet to do this repeated task, no doubt involving the use of untestable VBA Code, what if they were provided a high level function to perform this specific calculation instead?
Using high level domain specific functions reduces complexity in the Excel Workbook itself. The complexity is moved to these worksheet functions which, if written properly, can be tested, audited, and distributed to everyone that needs them. By keeping these high level functions outside of a specific workbook, fixes and updates can be deployed to everyone easily without having to modify each individual workbook.
Writing an Excel Add-In isn’t as hard as you think!
What we’ve described above, writing custom Excel functions, is achieved by writing an Excel add-in.
Traditionally, there were only two ways to write an Excel add-in; in VBA or in C or C++. These days there are more options to choose from and solutions exist for many different programming languages including Python, Java, C#, JavaScript, Scala, Kotlin, Clojure and even D.
Writing the add-in in VBA does give us some advantages over embedding VBA directly in workbooks. The VBA add-in can be written once and used by multiple users and workbooks, but it still has some downsides. VBA add-ins are hard to test and saved as binary files that don’t work well with version control systems. Some of these problems are addressed by RubberDuck VBA, and so if you have a particular need to keep using VBA then that is certainly worth a look.
For integrating with existing systems you can choose one of the popular add-in technologies that works with the languages you are already using. Rather than provide “Export to Excel” buttons or scheduled reports, make the data available as an Excel function with options to query or filter the data. Reducing the amount of work done in Excel will make your workbooks simpler, and therefore easier to understand.
- Python: PyXLL, https://www.pyxll.com
- Java and other JVM languages: Jinx, https://exceljava.com
- C#: Excel DNA, https://excel-dna.net
Python is a very popular choice at the moment. It’s easy to learn, but once you get started you will realise there’s a lot of depth to the language and a wealth of third party packages for virtually every field. It’s developer friendly syntax lends itself to those just starting out. Most users already familiar with VBA will not find the transition too daunting and will find their effort is richly rewarded.
Java and C# are two of the most commonly used languages in enterprise environments. Exactly the same environment where Excel usage is at its highest! Using these languages to extend Excel with higher level functionality specific to the needs of the business’ End Users is a painless way to reduce the dependence on highly complex Excel spreadsheets, while maintaining a highly flexible way for End Users to work.
Whatever language you choose, moving code outside of Excel opens the door to unit testing, regression testing, continuous deployment and ultimately bringing your End User Computing back under control.
A Final Note: Deployment
In order to ensure that each End User is accessing the correct version of the functions written for them there needs to be a way of deploying updates to them.
For example, if an error is found in a function used by several spreadsheets, after sufficient testing has been performed, that change needs to be made available to everyone using that function. Since the code is in an Excel Add-in and not duplicated in each workbook that task is now much simpler and less error prone.
The simplest deployment method is to host the Excel add-in on a shared network drive. When you need to make a change, you can update the add-in and everyone will get the latest version when they restart Excel. This relies on everyone having access to the network drive where-ever they use Excel, and any disruption with the network drive would result in the add-in becoming unavailable. This may not be suitable in all situations and there are better deployment methods that you might want to consider instead.
Both PyXLL (for Python) and Jinx (for Java and other JVM languages) support custom scripts to facilitate self-updating. This can be used to download and install the latest version of the add-in each time Excel starts.
C# Add-ins written with Excel-DNA can be packaged into an MSI which can be rolled out using a Group Policy.
In Summary
With business logic and data access exposed as high level functions to Excel users, spreadsheet complexity is greatly reduced. Freeing these functions from individual workbooks and deploying them as Excel add-ins allows for:
- Integration with existing systems
- Automated testing of code used in spreadsheets
- Centralised version control
- Continuous deployment
The problem of End User Computing in Excel is mitigated by ensuring the End User has the tools they need. The development cycle of an Excel add-in is typically much faster than that of a custom application as no UI changes are needed when adding new features. This greatly reduces the time to impact for new features. Staying in Excel results in high productivity, especially where tasks are varied and require analysis and experimentation. Maintaining an Excel add-in with high level functions ensures that spreadsheets don’t need to grow out of control.
