Excel as the Most Dangerous Software On the Planet

This Forbes article might date back to 2013, but no one seems to be heeding the warnings. Spreadsheets are becoming notorious for errors. It’s not only a problem for our financial markets, but it also affects your organization, and your ability to make trustable decisions.
​
One of the biggest reasons spreadsheets have errors is a tendency for users to copy/paste data from one spreadsheet to another. This Forbes article discusses the copy/paste problem (and how it impacted our financial markets) in very stark terms.

The issue is described in the appendix to JPMorgan’s internal investigative task force’s report. To summarize: JPMorgan’s Chief Investment Office needed a new value-at-risk (VaR) model for the synthetic credit portfolio (the one that blew up) and assigned a quantitative whiz (“a London-based quantitative expert, mathematician and model developer” who previously worked at a company that built analytical models) to create it. The new model “operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.” The internal Model Review Group identified this problem as well as a few others, but approved the model, while saying that it should be automated and another significant flaw should be fixed.** 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 . . .”

To translate that into the vernacular, the bank, JP Morgan, was running huge bets (tens of billions of dollars, what we might think of a golly gee gosh that’s a lot of money) in London. The way they were checking what they were doing was playing around in Excel. And not even in the Masters of the Universe style that we might hope, all integrated, automated and self-checking, but by cutting and pasting from one spreadsheet to another. And yes, they got one of the equations wrong as a result of which the bank lost several billion dollars (perhaps we might drop the gee here but it’s still golly gosh that’s a lot of money).

It might seem a harmless task to copy/paste data from one spreadsheet to another, but it is not. Consistently, it will cause errors. Is it happening in your organization? The chances are high that it is.

It isn’t the tool that is the problem; it’s the way the tool is being used. Firstly, keep in mind one reality, when it comes to managing information. Data is dynamic and managing it requires dynamic solutions.

Look at your data through the lens of evolution. Since data is dynamic, proper management requires the ability to evolve and transform. Microsoft created its data management products to accommodate information evolution. The first stage of data evolution is the spreadsheet stage. One person may need to track data specific to his/her job and just starts a new spreadsheet. As time passes, and more people need access to the information, the original spreadsheet application grows and multiplies. Before long, users find themselves copy/pasting data from one spreadsheet to another. Your network ends up storing spreadsheets for every reporting period in folders all over the place and no one has a clue where all the data is at, or even if it is accurate anymore.

Spreadsheets are not designed to store data. Spreadsheets are designed to analyze data. Copying/pasting data from spreadsheet to spreadsheet is a classic sign of a data storage issue. When copying and pasting is occurring you need to move the data to a database solution. The second level of data evolution is the native, local database level. At this level, data from various sources are often combined and streamlined into one core native, local database.
​
Microsoft’s local database solution is Microsoft Access. Access is designed to store data efficiently, thus eliminating the need to copy/paste between spreadsheets. Access is capable of automating data processing – as was needed in the JPMorgan’s Chief value-at-risk model outlined in the Forbes article.
​
In addition, MS Access is the best RAD (Rapid Application Development) tool on the market. So, it is possible to get an Access solution up and running pretty quickly, and at an affordable rate. Since Access is part of the Office Suite, it is designed to work seamlessly with other Office Suite products. This means that when the time comes to analyze the data, it is quite easy to dump data to Excel for analytical purposes. But, the data itself is stored and managed in a database application. This diminishes the Excel error rate dramatically, to say nothing of all the spreadsheets stored all over your network that everyone has lost track of.

More Data Management Articles

• ​Microsoft Access and Data Integration
• Yes – Microsoft Access works in a Multi-User Environment
• YES – Microsoft Access Can be Used Securely
• Deploying Microsoft Access in a Remote Environment
• Why You Should Care about RAD and How It Impacts Your Bottom Line ....
• Do you have questions about your own data management project? Contact Michelle.