Breaking out of the cells: banks’ long goodbye to spreadsheets

In February 2023, an employee of Norway’s sovereign wealth fund accidently keyed in “December 1” instead of “November 1” while working on an Excel file. This triggered a miscalculation of the composition of the index against which the fund was being measured. The mistake would end up costing the Norwegian state Nkr980 million ($92 million).

When the European Spreadsheet Risk Interest Group (Eusprig) meets in London in July for its 20th annual conference, errors like these will be top of mind. The group—which mainly comprises consultants, accountants and academics—has been encouraging best practice in Excel for almost a quarter of a century.

“Most companies have risk registers that deal with situations such as what happens if somebody falls off a ladder or if the chief executive gets run over by a bus,” says David Colver, Eusprig’s treasurer and the chief executive of financial modeling advisory firm Operis. “But the idea that one of the spreadsheets they’re depending on to make crucial decisions could just be wrong is often not there.”

To highlight the worst that can happen, Eusprig’s website includes a collection of the biggest horror stories (see box).

Didier Loiseau, global head of trading and financial engineering at Murex, does not believe there are any underlying problems with Excel itself. Instead, he says, problems arise from “the lack of control or the lack of consistency” within companies that rely on the program.

[Regulators] don’t disapprove of Excel. They just ask for formal security measures that make it unattractive to use because you’re losing the flexibility

Head of fixed income at a European bank

Banks are keen to safeguard against risks stemming from any applications in which the ownership falls outside the firms’ own IT governance. These applications are commonly referred to as end user computing apps, or EUCs, and Excel spreadsheets are a prime example.

Until the late 2010s, banks would use Excel to run critical functions, such as those related to derivative valuation adjustments written in the C++ progamming language. However, the program’s limited capabilities, insufficient oversight of the resulting models, key person risk, operational risks and human error are leading banks to curtail its use.

Stephane Salas, head of European and UK inflation at BNP Paribas, says that when he joined the bank seven years ago, “lists of zero-coupon inflation swaps were priced in Excel spreadsheets”. Today it uses an internally built model called Forward Pricer.

Yet there has not been a wholesale shift away from spreadsheets because Excel is still one of the easiest methods of visualizing data. Salas says BNP Paribas’s inflation desk uses the program to view the correlation between the UK’s retail price and consumer price indexes, natural gas futures and Brent crude futures.

“We use Excel to help us develop our thinking process about how we look at the market,” he says.

Salas adds that the bank’s IT department can then use the curve model built on Excel as a blueprint. The department can incorporate the curve model’s specifications into the systems it is developing, thereby speeding up the development process.

A head of fixed income at a European bank says it uses spreadsheets to “aggregate and beautify up the risk figures so they’re easier to digest on the trading desks”.

Regulators are also keen to deter traders from using Excel—even if only for visualizing datasets.

“They don’t disapprove of Excel,” says the head of fixed income at a European bank. “They just ask for formal security measures that make it unattractive to use because you’re losing the flexibility, the convenience and the speedy development.”

Those measures include identifying all EUCs that are used for business purposes; maintaining detailed documents and controls around changes made to EUCs; adhering to coding guidelines; having business continuity measures in place; and implementing controls to limit access to critical EUCs.

The requirements relate to spreadsheets being used as part of what regulators deem to be “business processes”. However, there is no clear definition of such processes, and it therefore falls to senior management to go into battle with regulators when the need arises.

“If the auditors or your regulators come along and argue that the bank is breaking the rules, it’s the trader’s or the trading head’s role to defend what they’re doing,” says the fixed income head. “At best, you’re going to have tedious, uncomfortable discussions. At worst, you’re going to be found to be violating the regulatory rules.”

Oops, something went wrong

Part of the problem is that errors are not flagged straight away on Excel.

Up until the late 2010s, traders, particularly on derivatives desks, would input pre-built functions from a bank’s quant analytics library into Excel. They would then build data processing frameworks, commonly referred to as logic, around those functions.

However, the head of fixed income at the European bank says that when spreadsheets aggregate data from different sources and something changes in a source’s system, the spreadsheet is unlikely to adapt to the change. At best, it will simply stop working; at worst, the spreadsheet will contain errors that go undetected.

At one European bank the push to move away from spreadsheets primarily came from the quant desk. Traders often built functionality in Excel as a means of bridging the gap between the bank’s core technology systems, which could be slow moving, and what they themselves needed on a day-to-day basis. The result was a complex network of spreadsheets that “created all sorts of problems”, according to the bank’s head of quantitative analytics.

A trader would build logic in their own spreadsheets for a particular task. However, if the trader left, no-one else at the bank would be able to understand how the model worked. “Traders would throw it over to the IT department and say, ‘make it work for us’,” says the quant head.

The head of fixed income says that their bank would delete the spreadsheets and start again rather than try to unravel them.

And now for something completely different

For the last four years, the quant head says the European bank has been replacing Excel spreadsheets with the Python programming language, which forms a middle layer between the pre-built functions in the analytics library and the tools the traders use. Whereas traders would previously have a spreadsheet with multiple tabs to look at curves in different ways, in Python a drop-down toggle enables them to view the data in whichever way they want.

“We’ve been successful so far,” says the quant head. “We’ve replaced a number of the spreadsheets, but there is still a long way to go.”

Then there are the oversight issues. When traders use their own individual Excel files, auditing, controlling and understanding what is taking place there becomes challenging. That individuality also makes it hard to conduct regression testing—where IT departments test software to ensure it is functioning correctly, update codes or fix bugs.

“It is very hard for an IT department to have a good overview of how spreadsheets are used by end-users,” says Hans-Peter Schoech, head of structured rates at Nomura. “The interplay of various EUCs on desktops cannot easily be regression tested, given that every individual user will have different combinations of EUCs, and usage behavior can vary a lot.”

Only a limited amount of data can be analyzed in a spreadsheet. As the world moves to larger datasets with machine learning and AI applications, using Excel might not be suited to the task.

Luis Martins, head of G10 rates and foreign exchange at BBVA, says: “In a world where scale is becoming important, where data is becoming important, that local flexibility does not necessarily fit very well with this relevance of data and advanced analytics.”

Rules-based system

Concerns around the controls and governance of data in Excel spreadsheets are an ongoing focus for regulators.

In September 2021, the UK’s Prudential Regulation Authority (PRA) voiced its disappointment at the “significant deficiencies” in some banks’ and building societies’ methods of reporting data. Firms that relied on spreadsheets for their regulatory reporting were found to be deficient when it came to risk and control assessments, documentation of key processes, and having robust processes and controls in place. In some cases, firms had not identified spreadsheets as EUCs and were not regularly reviewing the underlying logic.

Richard Thomson, head of desk-aligned strategists at Nomura, says that both the PRA and the UK’s Financial Conduct Authority have been regularly reaching out to banks to learn about their EUC policies. Whereas previously the two bodies were focused on policies around quantitative pricing models, the regulations have been expanded to include models that are not critical to banks’ operations.

In a world where scale is becoming important, where data is becoming important, that local flexibility does not necessarily fit very well with this relevance of data and advanced analytics

Luis Martins, BBVA

Germany’s financial regulator, BaFin, introduced the Supervisory Requirement for IT in Financial Institutions in 2017. Firms in the country now have to keep a central register of all EUCs, particularly those used for critical business functions, and ensure proper documentation and controls are in place.

The head of fixed income says their bank has been going through a multi-year process to locate all the spreadsheets that fall under BaFin’s criteria for EUCs: “Now we’ve even got robots crawling through the file system to identify if there are some Excel spreadsheets that fulfil certain kinds of criteria that would make them suspicious. If the robots find something, you need to explain why this is not an Excel file that falls under this restriction.”

The quant head at the European bank is going through a similar process for the UK regulators. However, locating all the spreadsheets can be difficult: “Traders have to confess that there is a sheet that they are using—because there isn’t any other way, basically. They talk about a few, but then there are a few that they forget.”

Cracking the code

With the gradual shift away from Excel, banks are trying to strike a balance between gaining greater oversight and control and giving up the flexibility that spreadsheets provide.

Whether a bank can strike the right balance will depend on whether it builds the code used to replace the spreadsheets—an option more prevalent among larger banks with bigger budgets—or relies on the standardized offerings from tech vendors.

The head of fixed income says either option will result in a loss of the flexibility offered by spreadsheets: “The system banks move to will always have some sort of one-size-fits-all approach, because you cannot be as individual as Excel can be.”

A head of markets at another European bank says it is working with a fintech to put in place a centralized system for all the sales and trading workflows. The aim is to enable traders to perform numerous functions, such as viewing intraday risk pricing and low latency pricing, in a single system.

From a regulatory perspective, using analytics libraries that contain a collection of pre-written code and high-level programming languages, such as Python, makes it easier to show regulators the audit trail of all the changes made in the code and the controls in place to monitor its functionality.

Satyam Kancharla, chief product officer at Numerix, says these analytical programming languages are more effective than Excel at handling real-time data.

Nomura’s Richard Thomson says banks have also been adopting inner sourcing—a framework that makes it easier for code to be shared between different employees while adhering to all the regulatory checks and balances: “The idea is that if I write code, other people can use it and benefit from it.”

Rather than unpicking spreadsheets after a trader has left, the quant head at the European bank says Python is much easier to understand if someone moves on because the source code is subject to review by the bank's IT department.

The onus is now on IT departments to come up with applications that are as good as or better than the models traders have created in their spreadsheets.

“If the trader gets something that’s less capable, obviously, they’re going to say, ‘No, thank you’,” says the quant head. “But if it does sort of exactly the same, they’ll still probably say, ‘No, thank you’, because they’re used to their spreadsheet.”

Excel might also be going out of fashion naturally. Younger traders moving up the ranks are better informed than many of their older colleagues on coding languages such as Python.

BNP Paribas's Salas says the transition to the bank’s new systems has been easy.

“They’re quite user-friendly,” he says. “We have a huge team that is here and that improves the systems through feedback and specifications from us on the front-office side. But they are also here to train new newcomers on the system.

“It’s better than saying, ‘here’s an Excel spreadsheet, just try to reverse engineer whatever it does’.”

 Editing by Daniel Blackburn