Get SMART with best practices for building highly scalable Excel spreadsheet solutions

Get Smart - Issue #3, The Nuclear Gumball Caper (November 1, 1966)

Spreadsheets have long prevailed in the world of trading strategies, valuation, risk and operations. The primary reason being that they are the easiest and quickest way to transform ideas from abstract concepts to concrete solutions. Still, uncontrolled spreadsheets resulting in disastrous consequences have dominated news headlines in various high profile cases within the past few years.

While a prohibition of spreadsheets would be an extreme application of blunt force trauma at the expense of product delivery times to market, unfettered spreadsheet development swings the pendulum too far to the other end of the scale. As such it is very important to practice intelligent design principles and implement reasonable audit controls.

Described below are the fundamentals of good design that should be central to all spreadsheet development strategies:

Standardization

Inconsistency is the mother of mistakes. The introduction of errors in the design and development process can be reduced by establishing standards and standardizing design patterns. Listed below are a few spreadsheet development best practices which should ideally be followed whenever it is reasonable to do so:

• Follow the universal formatting guidelines for user interfaces within the context of each business application.
• Object names (such as worksheets, named ranges and tables) should apply relevant standard naming conventions as far as possible.
• Use Hungarian prefixes if applicable depending on invidual use cases.
• Apply good coding practices such as meaningful variable declarations and format VBA code in macro enabled spreadsheets with proper indentation.
• Err on the side of verbosity when commenting on code.

Many spreadsheet solutions co-mingle business logic and data. This can introduce major operational risks as a design change request which takes a few days to implement needs to be reconciled with the production version if new data is created and saved on a daily basis. Facilitate the use of standard design patterns by separating business logic and data through user defined configurations, this will greatly reduce the opportunities to commit transposition errors.

Minimalism

The concept of minimalism in design involves stripping everything to its most essential simplistic form while still retaining completeness and elegance in function without compromise.

Keep it simple! Financial computations are complex and can require bespoke solutions even when dealing with the simplest of exchange traded financial products and markets. This is unavoidable, yet it is still usually possible to decompose each problem into simpler digestible pieces, incorporating standard practices and design concepts.

In other words, break a complicated business or financial model into simple isolated modules which handle the minimal functional requirements of each individual component. This not only reduces operational risk but also allows the spreadsheet developer to quickly react to changing environments, which brings us to the next point - agility.

Agility

Agile methodologies use incremental, iterative work sequences that are known as sprints. Clearly delineate business logic and data through the use of read-only spreadsheets, so that spreadsheet design is kept highly adaptable in response to business requests. Spreadsheet developers are thus able to focus on design and development without having to constantly merge new business logic updates with current data from active production versions. This fosters an Agile “iterative change” mindset by reducing deployment times and facilitating communication with the business as everyone can be inspecting and testing with the same data set.

Robustness

Robustness and reliability should always be front and center of any production environment. Ensure that users launch the official “golden copy” of a spreadsheet (even when copied to local drives offline) every time the file is opened. Spreadsheets should be configured to automatically closed at midnight at the end of the day. This not only ensures that the latest updates are applied but also handles situations where unforeseen rollbacks to previous versions are necessary.

Log all end user actions autonomously with VBA code in an easily track-able centralized repository, helping production support teams anticipate problems and keeping them operationally ready to handle potential system breaks on a timely basis.

Teamwork

Encourage collaboration and teamwork, by granting end users the freedom to make copies of the spreadsheet workbooks or templates to experiment on new ideas, without compromising on the integrity of the production environment. These can later be shared and discussed with developers who can then incorporate the changes into the official versions after they have been rigorously tested.

Do share anecdotes on how these best practices have helped you or if you have any personal insights to improve the spreadsheet development processes below!