Wanted: A Rules Engine for Excel

Excel Error

James Kwak writes about the role of Excel in the JPMorgan 2012 trading loss

After the London Whale trade blew up, the Model Review Group discovered that the model had not been automated and found several other errors. Most spectacularly,

“After subtracting the old rate from the new rate, 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 . . .”

So @SUM instead of @AVG and boom – $2B in trading losses.

I am exaggerating of course. It didn’t quite happen that way, but it does appear that this Excel error made the trade appear much less risky than it actually was. [You’ll find a full analysis of the JPMorgan trade here. A lot more interesting is this history of Excel bloopers.]

Excel is the weapon of choice for financial analysts of all hues. You could be an analyst evaluating complex derivative instruments or an investor projecting a public company’s future earnings. If you are a financial analyst, you probably spend a big part of your life looking at a grid of tiny grey cells.

During my startup days, we built a product that would pull in data from our library directly into their spreadsheets and keep it current. Since we worked with our users to design the product we got to see many, many spreadsheets.

As a rule, these spreadsheets are massive, multi-MB beasts. They start big. Over time they gather more and more data and complex analyses and become massive. In their full glory, they are inscrutable to anyone except their owners. And often even that is doubtful.

Not surprisingly, we regularly found errors in the spreadsheets. As expected, data errors were common. But errors in formulas, of the kind above, were not uncommon at all.

Now think about it. Companies are making decisions worth tens, sometimes hundreds of millions on the backs of financial analyses done in Excel spreadsheets, that are understood solely by their owners and are impervious to scrutiny by anybody else.

Excel was never designed to be anything more than a personal productivity tool. If the stakes of getting a risk model right are that high, then shouldn’t that risk model be treated like enterprise software – with development standards, commented code, versioning, unit and integration testing?

It’s not as if enterprise software doesn’t have its share of messes. The CIO of a print publication recently told me that they did not think they knew all the different offers on the publication that were available out on the internet. Apparently, there was a link on some forum that was still allowing a special offer and they didn’t know how to turn it off! We recommended re-engineering the whole application and putting a rules engine in front of it.

Perhaps that’s what financial modeling needs. A rules engine that drives all the analysis below. The problem with Excel is that the formulas and the data are all mushed together into a blob of grey cells. They need to be layered. Data in one place, rules in another. If I want to project a company’s revenues by applying the average year-on-year growth in the last four quarters, that’s written in the rules area. The historical revenues are in the data area. If the gross profit is a fixed percentage of revenue, that too is written in the rules area. When I change the rule, the computation changes and not otherwise. There are no computations that aren’t in the rules.

When I review my model with my team members, we are just looking at the rules, confident that the spreadsheet itself is simply a manifestation of the rules and data. Once in a way, we do look at the data sets as well, just to make sure we are using the right ones and that they are current. And we test the model from time to time to see if it gives out expected results.

But this sounds more and more like a database application. And database applications need programmers. In the real world that we live in, no financial analyst will let a programmer stand between him and his model. But perhaps that is the challenge here. Can we build a rules engine that is enterprise strength, does not require a programmer and sits on top of the WYSIWYG goodness of Excel?

Excel is one of the most powerful applications of our times. It is the killer app in MS Office. It is ubiquitous and everyone who will ever build a financial model already knows how to use Excel. Which is why we do too much with Excel. Which is why we end up betting hundreds of millions on the backs of black box Excel financial models. This needs to change.

3 thoughts on “Wanted: A Rules Engine for Excel”

  1. Think it’s a bit presumptuous if not totally naive to assume an app like Excel would’ve been designed without a Rules Engine or whatever they’re called. Could it have had such a fab run rate minus such an elementary design check…?

    If anything it’s the complexity in financial modeling that needs fixing, not the App itself. Remember Oliver Stone’s lines flawlessly executed by Michael Douglas ( Gordon Gekko) in Wall Street “Money itself isn’t lost or made, it’s simply transferred from one perception to another. This painting here. I bought it 10 years ago for 60 thousand dollars. I could sell it today for 600. The illusion has become real and the more real it becomes, the more desperately they want it.”

    There rests my case.


  2. I couldn’t agree more. It needs to change and it will change in due course. Afterall, nothing is permanent in life. However, I don’t think answer is really going to be an enterprise strength rules engine that would sit on top of Excel. Likely it would be another app designed and built completely differently. Something that would have a uniquely different approach to this problem.


  3. Funny the way the authority thinks. When colossal World Wars triggered economic recession, people didn’t vow for peace, they dropped Gold Standard instead – and then used banking system to provide unlimited expansion of credit to bridge deficits…. So much so, now they blame Excel for sub-prime crisis…!!!

    Good going. Aren’t we so, so, very lucky to be here and now to witness it all… :-(


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s