Just in case you ever wondered about whether data quality in spreadsheets was really a big deal, then this little story should change your mind. A lawyer on the Barclays buyout of Lehman Brothers defunct assets had drawn up an Excel spreadsheet detailing the particular trading contracts that Barclays was prepared to accept as part of the deal. The lawyer took out 179 particularly undesirable contracts, but rather than deleting the cells, marked them as “hidden” cells in Excel. This was all fine until a first year legal associate who was helping to put together the final paperwork converted the spreadsheet to a PDF file and, you guessed it, managed to include all the hidden cells as well as the items Barclays was supposed to be agreeing to buy. The deal has now gone through, and Barclays are frantically trying to reverse this mistake. I imagine that the Lehman’s administrators will be most sympathetic when the case gets to court.
The original article in “Above the Law” does not detail the financial consequences of this and, let’s face it, Lehman’s well-paid traders seem to have only a sketchy notion of the value and risk of their trading positions or they would not have gone bust in the first place. However I am guessing that the ones that Barclays intended to exclude were not exactly the most attractive, juiciest ones.
The lesson here is that you can have all the fanciest data quality tools and controls applied to your mainstream transaction processing systems, but data quality is something that needs to be applied to Excel as well, yet is widely ignored. When I was at Shell I was in an office opposite a team of bright young things that used to draw up sophisticated Excel models that did all kinds of things, such as deciding on the right levels to bid on contracts, and how much to invest in certain projects in order to make a decent return. Some of these spreadsheets were fiendishly complex. At least this group had various standards for Excel model development, and a well established spreadsheet audit process (with code reviews by other team members), but Excel formulae can be pretty opaque, and I am betting that the odd error slipped through. We are used to applying fairly elaborate testing and code reviews to C++ code, but how many tools are there for Excel testing, and how widely are they applied?
Hat tip to Stephen for pointing out the article.
as a DWH Arcrhitect and a former Excel deveoper I can attest to this issue. The real problem is the actual *design*, not the development of the spreadsheets. I can structure database design using data modelling techniques and tools, but there are no good calculation modeling tools/techniques that are able to drive an *implementation* (in e.g. Excel).
We need more structure and rigor in calculation model design, together with good tooling isntead of stating the obvious.
I think nowedays everybody knows that Excel can be a higly rewarding but also dangerous place to find (numeric) insights.
With regard to spreadsheet tools, Philip Howard at Bloor Research is your man. He has written extensively on these tools and knows all about the horrors of which you speak.