The stranglehold spreadsheets have on Asset Managers
The investment industry’s love affair with spreadsheets costs millions every year and is stalling data transformations
A few months ago, Tim Harford of the ‘Financial Times‘ wrote an article entitled ‘Nasty Little Boxes’ which laid bare the potential real-world impact of mismanaged data. Pertinently for the Investment Management industry, in which use of spreadsheets to manage and manipulate data is endemic, Mr Harford homed in on the specific limitations of the spreadsheet – the ‘nasty little boxes’, as he dubbed them – which make them unsuitable tools to manage enterprise data and the consequent contribution to ‘data disasters’ in the real world.
Although users in the investment management industry are amongst the most skilled users of spreadsheets in the world, I have seen a number of 7-figure ‘data disasters’ in Asset Management caused directly or indirectly by spreadsheets. I am not alone, most people who have been in the industry for 10-20 years have their own stories.
One topical and sad example Mr Harford cited was the loss of 160,000 contact records on the UK’s track and trace system during the COVID pandemic. The loss of these contact records (representing 160,000 real souls in the UK), was a little known ‘product feature’ maximum rows which resulted in the spreadsheet ‘running out of rows’. Horrifically, this resulted in an estimated 1,500 avoidable deaths, as well as millions of pounds to remediate the problem and replace the data store with an Enterprise solution.
Turning to the Investment Management industry, where the spreadsheet is viewed as a kind of ‘Swiss army knife’ for data management. Ironically, it is this very utility of the spreadsheet that leads to its over-use as a ‘hammer’ to solve every data and analytical ‘nail’.
Spreadsheets are used by Distribution, Finance, Operations, the Front office, compliance and in every organisational pocket of an Asset Manager to create a slew of financial models, reconciliations and tactical data stores. Easy to design, update, and share, the simplicity and flexibility of spreadsheets feeds a culture of overconfidence in the fidelity of the data in them. Spreadsheets have rudimentary user access and data security and governance controls – especially where frequent data updates are required – and have limited capability to express complex data relationships.
Over-reliance on spreadsheets in asset management doesn’t have the life or death problem impact that occurs in other industries. However, the inherent weaknesses of 2-dimensional spreadsheets – which includes the lack of suitable data governance controls – causes millions of dollars of avoidable cost to Managers every year through the business impact of data errors & omissions, as well as the excessive labour costs of replicating, patching, updating and reconciling tactical data stores.
For example, if a manager has 7 spreadsheets describing firm-wide AUM & Flow data that need to be carefully reconciled each month the number of reconciliations between all the spreadsheets is 21 (6+5+4+3+2+1). If you make 20 reconciliation spot checks between each pair of spreadsheets that’s 420 checks every month across the seven spreadsheets, or 5,040 checks each year. Even with some automation, that volume of complex checking will cost $50,000-100,000 p.a. if it’s being completed by staff in London, New York, Paris, etc.. By comparison, using a database as the enterprise data with perhaps some workflow and data input controls the reconciliation liability is reduced to zero – a saving of quarter to half a million dollars over five years. There are tens of thousands of spreadsheets in use at each Asset Manager …
Five reasons why reliance on spreadsheets is a bad idea
1. Inaccurate data comes at a great cost
Remember when financial giant JPMorgan Chase lost around $6.5 billion in trading due to a copy-and-paste error on its spreadsheets? Spreadsheet horror stories involving millions of dollars are common. Spreadsheets are prone to errors, from broken links to wrong formulas or simple human slip-ups. Viewing all your raw data at once can cause inaccuracies and fluid data sets can’t be shared in real-time.
2. Building, updating and fixing spreadsheets takes time
Employees often manually copy and paste information from one sheet to another and check each individual formula. This productivity vacuum increases when employees duplicate files or accidentally overwrite spreadsheets. Spreadsheets’ inability to track real-time changes poses a risk to data integrity, especially in cases where multiple users manage a single document.
3. Your data security is at risk from all angles
Spreadsheets lack encryption features for safeguarding sensitive data – even if you add password protection to your spreadsheets, files are easily hacked. Spreadsheets create opportunities for accidental data loss, particularly when updating historical data, which makes it difficult to analyse historical data trends.
4. There’s a 1 in 4 chance your spreadsheet contains an error
A study of 10,000 spreadsheets with calculations found that 25% had at least one error. Another study estimated that 20% of all genetics papers had errors caused by spreadsheets’ autocorrect.
5. Spreadsheets lack the scalability required
Spreadsheets struggle with large file sizes so combining data sets is difficult – you might experience some lag when it performs the most basic calculations on large datasets. The most commonly used spreadsheet product now holds a maximum of 1,048,576 rows and 16,384 columns, meaning you’re limited to that. What if your organisation manages thousands of accounts in different cities or countries?
Data management tools that cause accuracy and consistency issues result in poor data integrity. This poor data integrity impacts your reputation and the trust your clients have in you. Cloud-based solutions mean you aren’t constrained by data storage as you grow and will help maintain the integrity of your data. Multiple users can update simultaneously and it doesn’t impact the accuracy of the data. Spreadsheets have had their day in investment management – it’s time to move on.
If you would like to speak to someone at Alpha Data Solutions about life after spreadsheets, please get in touch with Greg Glass at ADS; Email: Greg.Glass@alphafmc.com, LinkedIn: https://uk.linkedin.com/in/gregdglass
As Head of Revenue at Alpha Data Solutions, Greg is responsible for ADS’ growth strategy, sales, and strategic partnerships. Previous roles at Alpha include Head of Commercial & Product Strategy and Director in the Distribution consulting practice specializing in Distribution Data