Data analytics

Escape the prison of Excel

Like Plato’s cave dwellers, spreadsheet devotees may not be getting a clear view of reality.

Howard Rees
April 28, 2021

In his famous allegory, Plato describes a group of prisoners in a vast subterranean cave, chained and facing a blank wall. All they can see are the shadows cast by objects passing before a huge fire burning behind them.

The shadows are their only view of reality, and they spend their time speculating about the world outside using this imperfect and limited data: Why does one shadow behave as it does? Are some of the shadows influencing the others? Do they move independently? Are there other shadows we can’t see?

The cave of Excel

Re-reading the allegory recently, it struck me that Plato’s prisoners are not unlike many number crunchers today –trapped in the proverbial cave of the Excel spreadsheet, scrutinising the figures dancing across the rows and columns, but struggling to see what the data truly reveals.

Of course, Excel has played a critical role in the evolution of economic and financial analysis. It is deservedly prized for automating painstaking processes and collecting and aggregating data quickly and flexibly.

But as tasks become more complex, and the teams and organisations using it grow larger, Excel’s great strengths go on to become its greatest weaknesses. Here are four reasons why:

1. Excel creates silos

Excel does not support collaboration or discovery of other people’s work. When Excel users create spreadsheets on their desktop, they create individual pockets of knowledge. Sure, they can share their files via email or other networks, but spreadsheets created by others often include calculations that are complex, hard to audit, or reverse engineer. As a result, your firm’s valuable intellectual property becomes ‘dark data’ that is hoarded rather than shared. It creates key person risk and deprives the wider team of data and insights they could use.

2. Excel’s simplicity breeds complexity

Because users can simply click “save as” each time they want to keep a newly created or updated worksheet, the organisation ends up with multiple versions of the same document, with no master copy or version control. In a large firm, this can quickly amount to tens or even hundreds of thousands of near identical spreadsheets – breeding confusion over which ones contain the costly market data your firm has paid for.

3. Complex spreadsheets are prone to errors

Any process requiring manual input is vulnerable to human error – and that risk is multiplied with every added functionality. While Excel can highlight glaring errors, it can be difficult to spot smaller ones amid the dizzying array of rows, columns and pivot tables.

Such minor oversight can lead to major unintended consequences, as was the case with the 2010 economics paper, Growth in a Time of Debt, by Carmen Reinhart and Kenneth Rogoff. Their study, which claimed a causal link between high debt levels and low economic growth, was cited by right-wing politicians to justify harsh austerity policies.

When economists at the University of Massachusetts, Amherst tried to replicate the results with their own data, they discovered that the Reinhart/Rogoff theory was flawed – due to a basic coding error on their spreadsheet. It was supposed to calculate average values across 20 countries in rows 30 to 49, but instead, had calculated values across 15 countries in rows 30 to 44.

That simple error, using the formula AVERAGE(L30:L44) rather than the correct one of AVERAGE(L30:L49), went on to negatively impact the lives of millions of people.

4. Excel was not designed for time-series analysis or charting

Anyone who has tried to build a simple regression model or add a seasonal adjustment analysis in Excel will know the painful steps required to create and update that model. This has two costs: the time it takes to create these models and the opportunity cost of all the models not being created due to the time it takes to make them!

Adobe’s CFO Mark Garrett recognised this early on by reducing or even eliminating Excel from the firm’s workflows, saying: “I don’t want people spending their time importing and exporting and manipulating data. I want them to focus on what the data is telling us.”

Prisoners of Excel

It’s clear that Excel is not the best tool for a complex modern business, especially one that requires time-series analysis. So, what’s stopping firms from exploring different options?

In Plato’s allegory, the cave dwellers who had spent a lifetime analysing the shadows had become so convinced of the reality they presented that they tried to kill anyone who challenged it.

Excel users, having invested years in meticulously creating their library of spreadsheets, have similarly become prisoners of the software – reluctant to contemplate change even when better alternatives exist.

This is where Macrobond can help. We understand the pain of manually downloading, organising, analysing and visualising data - and the time it takes to create or rebuild charts each time new data is released. That’s why we’ve automated routine tasks and simplified workflows – enabling users to spend less time chasing shadows and more time producing valuable analysis and commentary.

Isn’t it time to take the next step in the evolution of data analytics?