https://baselinescenario.com/2013/02/09/the-importance-of-excel/#
By James Kwak
I spent the past two days at a financial regulation conference in Washington (where I saw more BlackBerries than I have seen in years—can’t lawyers and lobbyists afford decent phones?). In his remarks on the final panel, Frank Partnoy mentioned something I missed when it came out a few weeks ago: the role of Microsoft Excel in the “London Whale” trading debacle.
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 . . .”
I write periodically about the perils of bad software in the business world in general and the financial industry in particular, by which I usually mean back-end enterprise software that is poorly designed, insufficiently tested, and dangerously error-prone. But this is something different.
Microsoft Excel is one of the greatest, most powerful, most important software applications of all time.** Many in the industry will no doubt object. But it provides enormous capacity to do quantitative analysis, letting you do anything from statistical analyses of databases with hundreds of thousands of records to complex estimation tools with user-friendly front ends. And unlike traditional statistical programs, it provides an intuitive interface that lets you see what happens to the data as you manipulate them.
As a consequence, Excel is everywhere you look in the business world—especially in areas where people are adding up numbers a lot, like marketing, business development, sales, and, yes, finance. For all the talk about end-to-end financial suites like SAP, Oracle, and Peoplesoft, at the end of the day people do financial analysis by extracting data from those back-end systems and shoving it around in Excel spreadsheets. I have seen internal accountants calculate revenue from deals in Excel. I have a probably untestable hypothesis that, were you to come up with some measure of units of software output, Excel would be the most-used program in the business world.
But while Excel the program is reasonably robust, the spreadsheets that people create with Excel are incredibly fragile. There is no way to trace where your data come from, there’s no audit trail (so you can overtype numbers and not know it), and there’s no easy way to test spreadsheets, for starters. The biggest problem is that anyone can create Excel spreadsheets—badly. Because it’s so easy to use, the creation of even important spreadsheets is not restricted to people who understand programming and do it in a methodical, well-documented way.***
This is why the JPMorgan VaR model is the rule, not the exception: manual data entry, manual copy-and-paste, and formula errors. This is another important reason why you should pause whenever you hear that banks’ quantitative experts are smarter than Einstein, or that sophisticated risk management technology can protect banks from blowing up. At the end of the day, it’s all software. While all software breaks occasionally, Excel spreadsheets break all the time. But they don’t tell you when they break: they just give you the wrong number.
There’s another factor at work here. What if the error had gone the wrong way, and the model had incorrectly doubled its estimate of volatility? Then VaR would have been higher, the CIO wouldn’t have been allowed to place such large bets, and the quants would have inspected the model to see what was going on. That kind of error would have been caught. Errors that lower VaR, allowing traders to increase their bets, are the ones that slip through the cracks. That one-sided incentive structure means that we should expect VaR to be systematically underestimated—but since we don’t know the frequency or the size of the errors, we have no idea of how much.
Is this any way to run a bank—let alone a global financial system?
* The flaw was that illiquid tranches were given the same price from day to day rather than being priced based on similar, more liquid tranches, which lowered estimates of volatility (since prices were remaining the same artificially).
** But, like many other Microsoft products, it was not particularly innovative: it was a rip-off of Lotus 1-2-3, which was a major improvement on VisiCalc.
*** PowerPoint has an oft-noted, parallel problem: It’s so easy to use that people with no sense of narrative, visual design, or proportion are out there creating presentations and inflicting them on all of us.
Update 2/10: There is an interesting follow-on discussion that includes a lot of highly-informed technical people, including some who work in finance, over at Hacker News.
=================
我在华盛顿度过了两天的金融监管会议（在那里我看到了比我多年看到的更多的黑莓手段 - 律师和游说者不会买得起体面的手机吗？）。弗兰克·帕特诺伊（Frank Partnoy）在最后一个小组的发言中提到了几个星期前我错过的一件事：微软Excel在“伦敦鲸鱼”交易中的作用。
摩根大通内部调查小组的报告附录中描述了这个问题。总而言之，摩根大通首席投资办公室需要一个新的风险价值（VaR）模型，用于综合信贷投资组合（即爆炸式投资组合），并指定了一名定量专家（“伦敦的定量专家，数学家和模型开发人员”谁曾在一家公司建立分析模型）来创建它。新模型“通过一系列Excel电子表格进行操作，这些电子表格必须通过将数据从一个电子表格复制并粘贴到另一个电子表格的过程来手动完成。”内部模型审查小组认识到这个问题以及其他一些问题，但批准了这个模型，同时说它应该是自动化的，另一个重大的缺陷应该被修正。**在伦敦鲸鱼贸易爆炸之后，模型评估小组发现模型没有被自动化，并且发现了其他几个错误。最壮观的是，
“从新税率中扣除旧税率后，电子表格按照建模者的意图除以他们的总和而不是平均值。这个错误很可能会影响波动率2倍和降低VaR。 。 “。
我定期撰写关于商业世界（尤其是金融行业）中不良软件的危害的文章，我通常指的是后端企业软件设计不佳，测试不足，而且容易出错。但是这是不同的。
Microsoft Excel是有史以来最伟大，最强大，最重要的软件应用程序之一。**许多业内人士肯定会反对。但是，它提供了大量的定量分析能力，可以让您从具有成千上万条记录的数据库的统计分析到具有用户友好界面的复杂估算工具。与传统统计程序不同的是，它提供了一个直观的界面，让您可以在操作数据时看到数据会发生什么。
因此，Excel在商业世界中无处不在，尤其是在人们数量增加的领域，如市场营销，业务发展，销售以及财务。对于所有关于SAP，Oracle和Peoplesoft等端到端财务套件的讨论，人们最后都会从这些后端系统提取数据，并在Excel电子表格中进行财务分析。我已经看到内部会计师在Excel中计算交易收入。我有一个可能的无法证明的假设，如果你想出一些软件产出的单位，Excel将是商业界最常用的程序。
但是，虽然Excel的程序相当健壮，但人们使用Excel创建的电子表格却非常脆弱。没有办法跟踪你的数据来自哪里，没有审计跟踪（所以你可以改写数字，而不知道它），并且没有简单的方法来测试电子表格，对于初学者。最大的问题是任何人都可以创建Excel电子表格。因为使用起来非常简单，甚至重要的电子表格的创建也不局限于懂得编程的人员，而是以一种有条不紊的方式进行编写。
这就是为什么JPMorgan VaR模型是规则，而不是例外：手动数据输入，手动复制粘贴和公式错误。当你听到银行的定量专家比爱因斯坦聪明，或者说复杂的风险管理技术可以保护银行免于被炒得沸沸扬扬时，这也是你停下来的另一个重要原因。在一天结束的时候，这都是软件。虽然所有软件偶尔会中断，但Excel电子表格一直在打破。但是当他们突破时，他们不会告诉你：他们只是给你错误的号码。
这里还有另外一个因素。如果错误发生了错误，那么该模型错误地将其波动的估计加倍了呢？那么风险价值会更高，CIO不会被允许进行这么大的赌注，而数量会检查模型，看看发生了什么事情。这种错误会被捕获。降低VaR的误差，允许交易者增加投注，是通过裂缝滑落的错误。单边激励结构意味着我们应该期望系统地低估风险价值 - 但由于我们不知道错误的频率或大小，所以我们不知道有多少。
这是否有办法运作银行，更不用说全球金融体系？
*缺点是非流通档案每天都以相同的价格获得，而不是基于类似的更流动的档次进行定价，降低了对波动的估计（因为价格保持不变）。
**但是，和许多其他微软产品一样，它并不是特别的创新：它是Lotus 1-2-3的一次剥离，这是对VisiCalc的一个重大改进。
*** PowerPoint中有一个常见的并行问题：使用起来非常简单，没有任何叙述，视觉设计或比例感的人在那里创建演示文稿并将它们强加给我们所有人。
更新2/10：有一个有趣的后续讨论，其中包括许多高度信息化的技术人员，其中包括一些在金融工作，在黑客新闻。