Doctor, Heal Thyself!

A client recently emailed me this link to an article about JP Morgan having discovered an error in their firm-wide calculation of Value-at-Risk, the industry standard measurement used to quantify risk.  His email concluded with:

“They are using a spreadsheet!!!”

You read that correctly.  JP Morgan – the firm that famously invented Value-at-Risk in the 1990’s – is apparently using a spreadsheet for this calculation.  This revelation is simply astounding.  If true, it would mean they really are sitting on a house of cards.  The article quotes the JP Morgan Task Force on VaR: “the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a  factor of two and of lowering the VaR…. It also remains unclear when this error was introduced in the calculation.”

Spreadsheets are great for quick, small ad-hoc calculations and estimates that need to be made on short notice.  But they are not production-grade.  The tool simply does not lend itself to steps that are essential to reducing errors: code review, version control, appropriate testing, or production change processes.  Instead, it promotes a culture of quick implementations, single-user ‘programming’ and copy-and-paste.  It’s very easy to make mistakes in Excel because the calculations are all hidden from view.  On purpose.  That doesn’t make Excel bad in-and-of itself.  It just makes spreadsheets an inappropriate choice for production systems.

At Investor Analytics, we code everything ourselves in Java.  Every piece of code is subject to review by someone who didn’t write it.  Once approved, it goes to “QA” (Quality Assurance), which means that other people who didn’t write it get a chance to test it.  We apply automated ‘scripted’ tests as well as manual ad-hoc tests to verify that the calculations are correct, that the interface works as its supposed to, and a host of other tests.  Then it goes to our production staff to give final testing before it’s scheduled for release by our program management group.  Excel spreadsheets skip all of these steps.

Back to the Task Force’s conclusions.  The fact that “it remains unclear when this error was introduced” tells me that they aren’t using a version control system which keeps track of every single change to code.  At IA, we’ve used a number of such tools in the past.  Today, we use Git and the Atlassian suite of tools to do the job.  Any developer can look back through the history of our entire code base to learn the date and time of any single change we’ve ever made.  This isn’t extraordinary — all professionals develop code this way.  Or, at least, they should!

The fact that the error likely muted the volatility by a factor of two means that the VaR was off by a similar amount. Although the author of the linked article describes that as a factor of 50%, I see that as being off by a factor of 100%.  If the correct number is 100, and I estimate 50, then I’m off by 50, which is 100% of my estimate.  So my estimate is off by 100%. Regardless of what you call it,  this tells me is that they weren’t backtesting their VaR.  Introducing an error as big as a factor of two into a calculation of a prediction should be caught very quickly.  Here’s how: VaR estimates how much money can be lost with a certain confidence.  Let’s say the 1-day VaR, at 99% confidence, is $10M.  That means that 99% of the time, the firm/bank/portfolio won’t lose more than $10M.  In other words, 1% of the time it will.  And that’s what can be easily tested!  Does the firm/bank/portfolio actually lose more than $10M more than 1% of the time?  If the VaR number is underestimating the risk by a factor of two, then your portfolio should lose that $10M twice as often as you’d be predicting — and that should be easy to catch!

But I think I’m getting ahead of myself, because these subtleties are swamped by a much bigger problem, and that’s exactly what my astute client wrote in his closing words: “They are using a spreadsheet!!!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: