Excel in Finance - More than a Tempest in a Teapot

Dilbert_Licensed 102820-2

It has been more than 35 years since Microsoft Excel (originally for the Macintosh!) was first introduced to the world. Yet here we are in 2020 and there are firms across the globe that still use Excel to support at least some crucial elements of their workflows, in spite of the known risk for errors or incomplete data. By this point I would expect more firms to be forward-thinking and farther along in planning for the future, yet even after more than two decades of selling sophisticated investment management software to financial institutions, I still find myself “competing” with some version of a makeshift solution created in Excel.

More often than not, the system was built by someone who left the company years ago, having passed on only minimal knowledge to future team members and leaving behind a solution that is not fully understood. Yet so long as their legacy system mostly works, and the information is complete enough, the current team is satisfied that they can avoid any major operational risk. I find this astounding.

So, in honor of the 35th anniversary of Excel, and in the spirit of helping the industry to consider a more sophisticated and future-proof approach, I present you with these headline-making, cringe-worthy Excel errors.

1. “Microsoft's Tool Caused Covid-19 Results to be Lost”

The most recent example comes courtesy of the British Government, more specifically the Public Health England (PHE). If you’re not already familiar with this very recent story, here is a summary:

Thanks to lack of user knowledge or oversight of the limitations on Excel rows in an earlier Excel file format (XLS – which dates back to when Margaret Thatcher was Prime Minister), thousands of Covid-19 cases were inadvertently excluded from what was supposed to be a comprehensive list to be used for contact tracing. Fortunately, the error was caught rather quickly, but it was a basic mistake that could have been avoided with a more recent format (XLSX - and by recent, I mean 2007).

While this error was not specifically in the Financial Services space, it still illustrates a serious downside of Excel that is known, but far too often ignored and/or forgotten. At the end of the day, Excel is a relatively straightforward spreadsheet solution that is a jack of all trades, but master of none. There are very real limitations to Excel, but as PHE can attest, there is no notice provided when something is deleted or truncated. If key data is simply omitted, or an incorrect number is provided, there are limited auditing capabilities to flag things that might go amiss.

Willett blog excel functionsImage Source: https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_excel_limits.html

2. The London Whale

A discussion about egregious Excel errors simply cannot exist without highlighting the London Whale incident of 2012, which cost JP Morgan at least $6.2 billion. This Bloomberg Quick Take does a nice job of covering the incident, but to summarize: A London based trader accumulated a series of positions in Credit Default Swaps under the guise of hedging for the bank; a strategy that became unsustainable as JP Morgan worked to offset the risk and the broader market became aware of the bets.

While Excel may not have been the primary culprit in this fiasco, Excel and the associated key-man risk were clearly an important driver behind the incident, as summarized here by The Baseline Scenario:

JPMorgan’s Chief Investment Officer 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.’

Excel, like all systems, is data hungry, but also quite limited when it comes to auto-loading capabilities. As evident in the London Whale incident, the reliance on manual data entry is a significant risk factor. And while this story made the headlines for weeks, even months after the incident, consider all the shortcomings and risks that have not made it into the public domain. Even more concerning, consider all those undiscovered risks still looming within existing spreadsheets.

Evolving Beyond Excel

The impact of the current pandemic, including long-term work from home situations and ongoing market volatility, has created an environment rife with the possibility for similar incidents within firms still heavily reliant on spreadsheets being manually passed often literally by hand or email. Virtual collaboration means increased transferring of worksheets between groups, having more than one person simultaneously editing a sheet, and siloed data and knowledge – all factors that can increase the risk of errors and miscommunication, particularly when paired with the ups and downs of the market from day to day.

While there are trillions of dollars - from family offices to state pension systems - managed via Excel, spreadsheets have inherent limitations and make it very difficult to maintain a unified line of sight across functions and across asset classes. Spreadsheets also limit the allocator's ability to act on data in real-time and readily answer client questions with accurate information, rather than having to say, "I'll check on that and get back to you."

Further, Financial Services firms that rely on Excel for core functions must rely on key staff to spend an inordinate amount of time on manual input and maintenance of a wide range of data from performance to exposure, NAVs and more. While good financial analysts are worth their weight in allocations, paying them to perform data harvesting and maintenance tasks is a poor use of their talents. Analysts should be analyzing, not shuffling data, writing functions, v-lookups, pivots, and more; alternative solutions offer these analytic features already embedded in the system code.

Undoubtedly, asset allocators make better investment decisions when equipped with robust data and analytics, and when they are not bogged down with data entry and information integration. Organizations already empowered with sophisticated, agile SaaS systems have shown that they can more easily adapt with better controls and have proven to be superior in business continuity situations.

An Investment Allocator’s Guide to Portfolio Management Solutions

There are far better options for asset allocators, ranging from basic platforms to full-service solutions, but which solutions are best for your organization?  

Earlier this year, Vidrio Financial published An Investment Allocator’s Guide to Portfolio Management Solutions | 2020 Edition, an in-depth analysis of options for allocators to consider when spreadsheet systems and other patchworks start to hinder fund management. This resource outlines the advantages and disadvantages of various options, sheds light on the variation in implementation time, and highlights expected costs associated with each option. 

It should be noted that while the guide has indeed been deemed a valuable resource, its ideas are not unique. Other industry consultants and organizations have compiled similar reports with a similar message: It’s time for the industry to evolve past its dependency on Excel and other patchwork solutions to embrace innovation and invest in new, agile, and robust integrated systems.

Vidrio can help you to evaluate your current systems and processes, assess budgets and review total cost of ownership. Request a Consultation

Willett joined Vidrio in the Summer of 2020 as a global Sales Director. Prior to joining Vidrio, Willett was based in Hong Kong for nearly two decades. Most recently, Willett oversaw FIS Global’s (formerly SunGard) Asset Management and Commercial Banking efforts in North Asia. Prior to that, Willett began his career at FactSet where he worked for 17 years. Starting off as a consultant, Willett held multiple positions during his FactSet career. Most notably, he started and built the Asian Portfolio and Quantitative Analytics team before moving into a role where he oversaw FactSet’s Southeast Asian operations. Willett completed his undergraduate studies at Georgetown University and finished a joint degree MBA from the Northwestern Kellogg School and the Hong Kong University of Science and Technology in 2010. Willett also holds the Chartered Financial Analyst (CFA) designation.

News and Updates