The investment industry’s love affair with spreadsheets costs millions each year and is stalling vital data transformations.
In July 2021, Tim Harford of the Financial Times published an article titled ‘The Tyranny of Spreadsheeets’ that laid bare the potential real-world impact of mismanaged data. Pertinently for the investment management industry — in which the use of spreadsheets to manage and manipulate data is endemic — Harford homed in on the specific limitations of a spreadsheet that makes them unsuitable tools to manage enterprise data and the contribution they have had to data disasters in the real world.
Harford cited a particularly devastating example of the loss of 160,000 contact records on the NHS’ ‘track and trace’ system during the COVID-19 pandemic, which represented 160,000 real people in the UK. The system’s spreadsheet simply ‘ran out of rows’, resulting in an estimated 1,500 avoidable deaths as well as millions spent to remediate the situation and replace the data store with an enterprise solution.
A ‘Jack of all trades’
For years, the spreadsheet was viewed as a ‘Swiss army knife’ within the investment management industry. Ironically, it is this very utility that has led to its overuse as a solution for every data and analytical issue.
Spreadsheets are used by distribution, finance, compliance and operations teams as well as the front-of-house staff. They are in every organisational pocket of an asset manager and are used to create a mass of financial models, reconciliations and tactical data stores. However, their ease of use, flexibility and simplicity has resulted in a culture of overconfidence in their data. Spreadsheets’ user access, data security and governance controls are rudimentary, particularly where frequent data updates are required. They also have a limited capability to express complex data relationships.
Overreliance on spreadsheets in asset management may not pose a risk to life, but the inherent weaknesses of two-dimensional spreadsheets cause severe avoidable costs to managers each year through the business impact of data errors and omissions. There are also excessive labour costs associated with replicating, patching, updating and reconciling tactical data stores.
To keep on top of data in spreadsheets, most reconciling is manual. Even with some automation, the volume of conducting complex checks can cost hundreds of thousands of dollars. By comparison, using a database for the enterprise data reduces the reconciliation liability to zero.
Keeping with the times
Spreadsheets may once have been the most effective way to store and manage data. But as data becomes more complex, systems have been developed that are designed to handle data of increasing intricacy. Data management tools that cause issues with accuracy and consistency result in poor data integrity, impacting your reputation and the relationship of trust between you and your clients.
So, why do asset managers need to stop relying on spreadsheets?
- Inaccurate data comes at a cost
Spreadsheet horror stories involving millions are common. In YEAR, JP Morgan Chase lost close to $6.5 billion in trading due to a copy-and-paste error on its spreadsheets. Spreadsheets are prone to faults, from broken links to incorrect formulae, and are more likely to result in a simple human error. What’s more, viewing every piece of raw data at once can cause inaccuracies, and fluid datasets cannot be shared in real-time.
- Time-consuming from start to finish
Building, updating and fixing spreadsheets takes time. Employees often manually copy and paste information from one sheet to another and check each formula. This productivity vacuum increases when files are duplicated, or spreadsheets are overwritten accidentally.
You are also unable to track real-time changes on a spreadsheet, posing a risk to data integrity — predominantly when multiple users manage a single document.
- Data security is at risk
Spreadsheets typically lack encryption features for safeguarding sensitive data. Passwords can be hacked easily.
- Prone to errors
There is a one-in-four chance that your spreadsheet contains an error. A study of 10,000 spreadsheets containing calculations found that 25% had at least one error, with another study estimating that 20% of all genetics papers had errors caused by autocorrect in spreadsheets.
- Lack of scalability
Spreadsheets struggle with large files, making it challenging to combine datasets. Users often experience a lag when performing even the most basic calculations on large datasets. Their capacity is also limited: the most commonly used spreadsheet product holds a maximum of 1,048,576 rows and 16,384 columns, limiting businesses that manage thousands of accounts in different locations.
Cloud-based solutions mean asset managers are not constrained by data storage as they grow. Multiple users can update the systems simultaneously without impacting the data’s accuracy. Get in touch with Aiviq to find out more about our advanced data management solutions.