This has been my profession for the last 15 years in a large consulting firm. We do this work for financial institutions (some other industries too). Despite how this group feels about banks, US banks have set up internal groups that literally review (validate) and test quantitative models, many of which live in Excel. This was driven by SR11-7 and OCC2000-16 both regulatory guidance. The term for this group / department / function is Model Risk Management (or model validation). Most banks now hold an inventory of models that exist in the entire bank (or at least the ones that have been declared) and they run a battery of testing procedures on them annually. It’s pretty exhaustive and sometimes too much but it certainly finds errors both in implementation and in logic.
You’d be amazed at the types of errors we’ve found over the years. Formulas referencing blank cells, relative references formulas that were meant to be static when the formula was copy and pasted to other cells. These were in some pretty serious models (e.g. trading models, risk monitoring models).
One point on Excel, it’s wildly powerful. We’ve done things in Excel that would impress most of this group. Things like 3D formulas (formulas that go through tabs), array functions (which are very infrequently used but are very powerful). I’d argue that most pieces of code (with the exception of recursion and other advanced things) can be replicated in Excel.
Half of the time when validating complex models implemented outside of Excel, we’d use Excel to replicate the logic to benchmark the result.
My big question to this group is, does this type of practice exist in non-banks? Do other industries do this?
You’d be amazed at the types of errors we’ve found over the years. Formulas referencing blank cells, relative references formulas that were meant to be static when the formula was copy and pasted to other cells. These were in some pretty serious models (e.g. trading models, risk monitoring models).
One point on Excel, it’s wildly powerful. We’ve done things in Excel that would impress most of this group. Things like 3D formulas (formulas that go through tabs), array functions (which are very infrequently used but are very powerful). I’d argue that most pieces of code (with the exception of recursion and other advanced things) can be replicated in Excel.
Half of the time when validating complex models implemented outside of Excel, we’d use Excel to replicate the logic to benchmark the result.
My big question to this group is, does this type of practice exist in non-banks? Do other industries do this?