Wanted: A Rules Engine for Excel

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.