Microsoft Excel has proved itself to be a powerful development platform, and the need for material on advanced Excel development has never been greater. In recent years, however, few books have afforded developers the instruction and depth required to create advanced Excel applications. Two titles in particular, Professional Excel Development by Bullen, Bovey, and Green and Excel Dashboard and Reports for Dummies by Michael Alexander, have given serious focus to these subjects; yet a dearth of quality material still remains. Where printed material has failed to fill this gap, thriving communities and blogs have spawned on the Internet over the last few years devoted to Excel development. Naturally, the last few years have also seen growing interest in data visualization, business intelligence, and data analytics.
The Data Problem
We are so immersed in data that its hard to make sense of it all. So far, our solution has been to rely on products and people who purportedly transform this data into something we can digest; vendors, smartphones, computers, and even the news media slice and dice this information to make it palatable for us to consume. But even these devices and people can, and often do, fail to deliver; the institutions weve entrusted for guidance through this mess have not always had our interests in mind. Where we should have met their work with skepticism, they have imbued in us a false sense of security. Intentional or not, they have led us astray.
Thus, we exist at an important and exciting time for professional Excel developers. With knowledge of Microsoft Excel spreadsheets, formulas and Visual Basic for Applications, and the desire to learn, developers can create powerful data and visualization platforms that rival the work performed by vendors. In other words, with the right toolsand the right mind-setwe can take control of our data. We can take the power back from the vendors.
However, the desire for more data visualization and analytics and the ability to create these products in Excel has not always translated into better products, especially in the last few years. Data visualization is often misunderstood by the vendors that sell it. This misunderstanding has crept its way into Excel. Some vendors and even Excel blogs sell products or instruct users on how to create fancy but useless widgets that sparkle with metallic finishes in three dimensions. But all that glitters is not gold. Just because we can add these things to our Excel spreadsheets does not mean we should.
At its core, the problem stems from a lack of clarity. At every turn we are confounded by information. When products and research fail to make us smarter, they add to our confusion. If we are to see clearly through the data storm, we must stick to what we knowwhat the research tells us is true. This book will not teach you how to make the newest flashy gadgets; instead, it will teach you established principles based on proven research. We will continue down the path so rarely taken by vendors: the path of research and best practices. Think of it as an adventure.
Enter Excel: The Most Dangerous Program in the World
No, really, its possible that Microsofts Excel is the most dangerous software on the planet.
Tim Worstall in Forbes Magazine
Worstall is referring to JPMorgans use of Microsoft Excel and its potential contribution to the global recession that began in 2008. The story is a familiar one. JPMorgan, one of the big banks, required a new way for their chief investment officer to model the risk associated with their portfolio of collateralized debt obligations (CDOs). CDOs, as you might recall, are those complex financial instruments that are comprised of a bundle of mortgages used to speculate and manage the risk for default. In the end, many homeowners eventually defaulted on their mortgages. Yet, JPMorgans model had not captured this at all. According to an internal report by JPMorgan, their model contained an error that likely had the effect of muting volatility by a factor of two and of lowering the VaR [value at risk].
What had happened? How could one of the biggest banks employ a model that was so absent from reality? According to the report, their model was 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 error was generated when After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended.
Worstall, like many others, blames Excel. Its too insecure, and theres no audit trail, he contends. Its flexibility is its greatest strength and Achilles heel. His argument isnt a new one, of course. Without proper internal governance and procedures regarding archiving, getting lost in a nightmare of error-ridden spreadsheets and mixed-up versions is a real possibility.
But is that really what happened in the case of JPMorgan? Are more audit controls needed? Im not so sure.
Not Realizing How Far Spreadsheets Have Come
Many organizations still operate with the mind-set that Excel is an incredibly hard tool to use. Early versions of Excel may have required manual updates, but these days Excel provides a lot of efficient and easy ways to pull in information. As well, what reason was there to maintain many different spreadsheets? Why did they require someone to copy and paste the information with every update to back-end data? Macros easily streamline these rote activities.
Its troubling then to think a large financial institutional such as JPMorgan didnt choose to use all the capabilities available in Excel. Instead, they tended to use it in a very antiquated way. But more than that, I submit that when you dont take advantage of the features that exist to address your needs, not only are you using Excel inefficiently, youre using it incorrectlyand dangerously.
One of the main reasons I still see organizations operate with the mind-set that they need to maintain many different spreadsheets with manual updates is because they want a historical imprint of every piece of information for every step in the process . These files become the historical records, or in other words, the audit trail of changes. According to their internal report, JPMorgan knew that operating with several spreadsheets was foolhardy. But for many organizationsand quite possibly for JPMorganoperating with several spreadsheets can give the appearance of having a trail of changes. JPMorgan knew they had to automate the models process but never did. Where was the urgency?
Are more audit controls to track every change really the solution? It would have certainly helped. But I dont think it addresses the problem in full.
Garbage In, Gospel Out
