Take Away Spreadsheets? From My Cold, Dead Hands

Microsoft Excel spreadsheets will continue to be widely used at asset management firms for the foreseeable future. But new regulations, technological advancements, and risk management concerns are leading buy-side firms to examine ways to lessen their dependencies on these ubiquitous, manual solutions. By Anthony Malakian

There are many culprits to blame for the “London Whale” fiasco, which saw JPMorgan lose $6 billion on a poor series of credit default swap index (CDX) transactions. There’s Bruno Iksil, the London Whale trader himself, who took the risky positions. There’s Barry Zubrow, JPMorgan’s chief risk officer at the time. And, of course, there’s Jamie Dimon,  JPMorgan’s top boss, whose reputation was sullied by the loss.

But there was another bit player in this drama: Microsoft Excel spreadsheets.

After the debacle was revealed, the bank launched a task force to find out what went wrong. A 129-page report produced by the task force and published last year, points to several mistakes that occurred due to erroneous inputs into spreadsheets that prevented risk models from running properly.

The report states that one risk model failed because a trader had “made certain adjustments to formulas in the spreadsheets he used. These changes, which were not subject to an appropriate vetting process, inadvertently introduced two calculation errors, the effects of which were to understate the difference been the valuation control group (VCG) mid-price and the traders’ marks.”

Additionally, according to the report, models run to calculate Value at Risk (VaR) “required time-consuming manual inputs to entries and formulas, which increased the potential for errors. One risk model was operated through a series of Microsoft Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.”

The group also found that a simple spreadsheet error “caused the VaR for April 10, 2012, to fail to reflect the day’s $400 million loss in the Synthetic Credit Portfolio.” But when they realized the error later, it was viewed as a one-off mistake, and “it did not trigger further inquiry.”

While JPMorgan did not respond to repeated requests for comment, the task force’s report recommended an immediate review of all manual spreadsheets and the implementation of enhanced controls for key spreadsheets.

It was a $6 billion dollar disaster, and part of the blame can be directly assigned to error-prone spreadsheets that lack automation and control.

Useful and Risky

It is unlikely that asset managers will ever fully relinquish their beloved spreadsheets. Many people interviewed for this story paraphrased the famous National Rifle Association (NRA) creed: “I’ll give you my spreadsheets when you pry them from my cold, dead hands.”

Professor and author James Kwak calls Microsoft Excel “one of the greatest, most-powerful software applications of all time.” There are cost considerations for going down the spreadsheet path, as well. Why would a start-up hedge fund invest in expensive third-party platforms when most portfolio managers are able to manipulate and tweak spreadsheets to the same end?

What the London Whale debacle brought into stark relief, however, is that investment firms have developed an over-reliance on spreadsheets. This is dangerous because a full 88 percent of spreadsheets contain errors, according to a 2008 research project conducted by Raymond Panko, an authority on spreadsheet studies, and professor at the University of Hawaii.

For investment functions like modeling and portfolio analysis, Excel will continue to remain a powerful and useful tool for portfolio managers and traders. But when those spreadsheets start to creep into the middle and back offices, or when they’re relied on to manage risk in the front office, problems can arise. With advancements in cloud-based technologies, data warehousing, storing, reporting, and data analysis, asset management firms are increasingly looking to lessen their dependencies on Excel.

The Alarm

Vincent Kaminski famously sounded the alarm at Enron back in 2001, shortly before the energy giant went belly-up. A respected expert in the field of risk management, he’s now a professor at Rice University in Texas. Kaminski acknowledges the usefulness that spreadsheets provide quants for modeling and analytics, although he says they should not be relied on for risk management.

“Spreadsheets are one of the hidden risks in big corporations across the board,” he says. “I’ve seen so many big snafus due to errors hidden in Excel spreadsheets, which for all practical reasons cannot be audited and validated.”

Excel has permeated operations and has become an accounting fix-all. It is used as a bootleg portfolio management system (PMS), execution management system (EMS) and order management system (OMS). And perhaps most confounding, it’s often used as a risk management platform.

Pete Peterson, head of technology at Los Angeles-based investment manager Causeway Capital, says there are certainly areas where buy-side firms can increase their levels of automation, but he says they are largely in the middle and back offices. Causeway, launched in 2001 and now managing $28 billion, focuses on international equities. To automate its reconciliations processes, which were previously managed on Excel spreadsheets with manual “hand matching,” it turned to vendor Electra and its Stars reconciliation platform. It has since added Quantum, Electra’s revenue-management tool, for further automation.

Peterson says that mission-critical investment functions will continue to be run via spreadsheets. “You can make it do whatever you want to do,” he says. “And it’s going to adjust to your environment dynamically, which most systems won’t do.”

However, if the business is running on a T+1 basis, which costs firms money if processes lag behind the market, then an automated solution is required.

“Where you will see spreadsheets stay is on the investment side, for research and development, and modeling,” Peterson says. “But once somebody makes that decision about how much of a security to target, everything after that moment should be automated.”

The Challenges of Growth

To manage its risk, Pomelo Capital, a New York-based investment advisor, which was spun out of Barclays’ credit arbitrage desk in 2012, turned to TS Imagine. At its launch, Pomelo’s founders wanted to have a tested third-party system to manage its book of vanilla derivative products, says Raj Sood, portfolio manager at Pomelo, which manages $775 million. Sood says TS Imagine’s platform is highly customizable, which will become important as Pomelo grows.

Eventually, Sood says, Pomelo will look to implement its proprietary structural risk model into the TS Imagine platform in order to have an overall view of its portfolio risk. The firm did not want to run its risk management through a spreadsheet because while Sood describes Pomelo’s investment strategy as being “plain vanilla,” it’s also not running an equity long–short strategy, either.

Sood says it’s necessary to be able to look at every metric to manage cross-asset risk. He wants to be able to strip out equity and equity derivative risk and view it as one element, and then strip out and look at all of the book’s credit risk, independently. Then he may want to aggregate them and look at them in a structural model.

“If you don’t do it that way, there are a lot of unknowns,” he says. “You’re taking a lot of risk not knowing each one of those product risks independently. Can it be done using a spreadsheet or by building something organically? Sure. Can it be done well and quickly? I’d argue no. You’d have to hire at least two guys to program something that would be meaningful, and for a book like ours, it would take you at least a year to build something.”

Gang of Four

Four Capital Partners, a London-based hedge fund with $2.3 billion under management, started out in 2006 investing exclusively in UK equities. In 2009, it expanded its portfolio to include European equities. Then, last year, it launched its first multi-strategy book.

Like most start-ups, it relied heavily on Microsoft Excel for a variety of functions. But as it grew, it needed to invest in a third-party solution to handle the massive swaths of data it was producing and consuming. Valerie Evans, head of operations at Four Capital, says it used to take 15 minutes or more to simply open an Excel spreadsheet. To address this challenge, the fund turned to Linedata and installed the vendor’s Global Hedge Trading platform, designed to handle multiple asset classes and run order management operations.

Evans says that if the firm hadn’t made a change, it would have had to build more spreadsheets and user-developed processes, which would have added latencies to its various strategies.

“In mid-January 2014, we traded our first fixed-income instrument and it was a very painless process, because everything was already set up for us in the system,” she says. “The spreadsheets that we were using for pre-trade involved a manual process that took a lot of resources from the investment teams. So, whereas it may have taken 10 to 15 minutes to process a trade on our original spreadsheets, it can now be done in under a minute on the new system.”

The IBOR Bump

Another key change agent in favor of automation is a renewed vigor among asset managers to build an investment book of record (IBOR). In order to replace its fragmented infrastructure by retiring two legacy systems and consolidating three business lines, BMO Global Asset Management turned to SimCorp and implemented the vendor’s flagship Dimension platform as part of its IBOR strategy.

Todd Healy, vice president at BMO Asset Management, which has $515 billion under management, says the implementation took about three years and required portfolio managers and traders to cede some of their spreadsheets. It was a collaborative effort, but to see them become comfortable with the new system and release their spreadsheets was one of the best parts of the transition, Healy says.

“I’ve had guys on our team who spent an hour, two hours-plus each morning looking through spreadsheets to try and get a good start-of-day position in their mind,” he says. “That’s a giant waste. They’re not researching anything, they’re not looking at opportunities, they’re not sensing what the market’s directionally doing at that time—they were doing nothing but accounting and recordkeeping tasks.”

Ontario Teachers’ Pension Plan (OTPP), with approximately $125 billion under management, is about a third of the way through developing its own IBOR. Andrew Weston, director of technology account and application management, says OTPP will still incorporate spreadsheets into its system for modeling and analytics, because spreadsheets are fundamental to the firm’s strategy. He adds, though, that they would never be used for order or risk management.

“Fundamentally, spreadsheets facilitate a degree of personalization, customization and agility that is difficult to match in other systems—not impossible, but difficult,” he says. “If you’re going to allow the level of flexibility that a sophisticated investment manager does, from an alpha-generation perspective, it’s about the capabilities of Excel, but it’s even more about the agility. In a single day I can change my model constantly all day long. I can build instant gratification—instant response—as to what that tweak will do.”

In 1994, Jay Vyas worked at Wells Fargo Nikko Investment Advisors, which merged with Barclays Global Investors (BGI). In the process of merging the two institutions, the first industrial-grade IBOR was created to manage the two institutions’ positions. So the idea of an IBOR is hardly new, but it also never spread across the industry, especially in the US.

Vyas is now the vice president and head of quantitative investing at the Canada Pension Plan Investment Board (CPPIB), which manages approximately $200 billion, prior to which he launched his own hedge fund in 2005. He says that everywhere he’s been, spreadsheets have been used sparingly because the firms always built IBORs to better manage and centralize positions, rather than trying to manage that process via a series of spreadsheets or user-developed applications.

Vyas says it’s important to adopt as many best practices as possible for software—which is challenging when a firm relies on spreadsheets—in order to ensure that it is robust and bug-free. He says this can involve testing against spec as well as against hand-built prototypes, adding that it’s important to pay attention to source-code versions and change management. After that, the solution can be automated in order to avoid creating a piece of software that only the developer can manipulate, thus creating key-person risk, he says.

“From a risk management standpoint, you actually have an appropriate balance between robustness and flexibility,” Vyas says. “Because it’s designed by the users and even coded by the users, they can modify it, but it’s not a free-for-all where anybody can change the formulas or steps. So it’s not a one-person IBOR—it’s a team IBOR.”

Not Dead Yet

Advancements in technology and new regulations, most recently stemming from Dodd–Frank in the US and Mifid II in Europe around auditing and reporting requirements, is set to push firms to abandon comfortable, if time-consuming, manual processes. And Microsoft hasn’t taken these threats to Excel lightly.

According to Bruce McKee, UK industry lead for financial services at Microsoft, 10 years ago Excel supported 64,000 rows of data; now it can handle 100 million rows. Advancements in PC processing power have also helped execution speeds, while Microsoft has launched cloud-based Power BI, a business intelligence tool that shows how data changes and grows over time, and Power Map, where users can see temporal data over the course of time.

Now the goal, according to McKee, is to push firms to Windows Azure, Microsoft’s cloud platform. “When they do run up against capacity issues as data models and spreadsheets are being built, we now actually have a really good solution—Azure—that can help,” he says. “This still enables the flexibility on the front-end to allow the quants to design and build models, but then scale it out to the back-end through the cloud to provide a whole other capability.”

If Not Now, When?

JPMorgan’s $6 billion loss should have provided every buy-side institution with an incentive to run their own reviews of all manual spreadsheets. While it’s human nature to expect bad things to affect only others, there have been sufficient technology advancements to make reexamining these strategies feasible, practical, and more importantly, necessary. Firms are not required to undergo massive IT overhauls; rather, it’s a desk-by-desk, portfolio-by-portfolio investigation that’s required. And, if a catastrophic error does arise, how comforting will it be if all that’s left in your company’s cold, dead hand is an erroneous spreadsheet?

Salient Points

• While not solely to blame, Microsoft Excel processes—and a lack of automation and control—are at least partially to blame for JPMorgan’s $6 billion London Whale fiasco.
• As firms grow, they need to invest in automated solutions so that they aren’t overly reliant on spreadsheets for middle- and back-office functions. 
• Asset managers are partially investing in the development of an IBOR in order to help them better manage manual, spreadsheet-based processes to get a better view of their portfolios.
• Microsoft has answered concerns over the lack of an audit trail, in addition to latency and capacity issues with the launch of Power BI and adding integration with Windows Azure.

Read Article Here