Cautionary Tales with Tim Harford - Wrong Tools Cost Lives

Episode Date: May 21, 2021

The British Government promised to create a "world-beating" system to track deadly Covid 19 infections - but it included an outdated version of the off-the-shelf spreadsheet software Microsoft Excel. The result was disastrous.When under pressure or lacking in expertise we can all be tempted to use a tool unsuitable for the job in hand. But whether fitting shelves or trying to halt a pandemic, we need to accept that cutting corners comes at a cost.Read more about Tim's work at http://timharford.com/ Learn more about your ad-choices at https://www.iheartpodcastnetwork.comSee omnystudio.com/listener for privacy information.

Pushkin. We will have a test, track and trace operation that will be world-leading. That's Boris Johnson, the British Prime Minister, speaking to Parliament with a typical jingoistic flourish in May 2020. The UK, he promised, would have a world-beating contact tracing system within a few days. The first wave of the pandemic was slowly receding, but the cost had been brutal. That spring, the country had suffered one of the deadliest outbreaks of COVID anywhere in the world, and so the British Prime Minister decided to cheer up the nation as only he

could by boasting of a contact tracing system that would be better than anything Johnny Foriner would have. A contact tracing system for COVID has three key elements. First, you need to be able to identify who's infected and isolate them. Then, you need to trace the recent contacts of the infected person. Finally, you need to be able to persuade those contacts to isolate themselves as well, to avoid any further spread. It's not easy. But if you get it right, you can keep the virus contained, while allowing everyone else to relax a little and go about their lives.

Taiwan managed this, so did South Korea, and Vietnam, and Japan. Anyway, the UK system didn't need to beat the world, just needed to beat the virus. In the summer of 2020, there seemed to be every chance of doing that. Infections had been beaten back by a long, strict and costly lockdown. Every day, there was just a handful of deaths and a quarter of a million daily tests were revealing just a few hundred cases. Surely, few enough for the contact traces to manage, but by September there were alarming signs that the virus was coming back. Cases rose. There were a thousand a day, then two thousand, then three thousand. The testing system was struggling to keep up. More than 90% of people weren't getting test results

back the next day. There's not much point in contact tracing if it takes days to even figure out who was infected. And then, on Sunday 4 October, my cell phone rang. On the line was a researcher from the UK's most influential news program. She told me that something very odd had happened, and they wanted me to figure it out and then come on the show the next morning to help them explain it. And what exactly had happened, nearly 16,000 positive cases had disappeared completely from the contact tracing system. 16,000 people who should have been warned that they were infected and are danger to others.

16,000 cases in which the contact tracers should be hurrying to figure out where that infected person went, who they met and who else might be at risk. None of that was happening. And why had the cases disappeared? Well, this was the real eye-opener. Apparently, Microsoft Excel had run out of numbers. I'm Tim Haafard and you're listening to cautionary tales. You cannot see a crow in a bowl full of milk. This is Francesco di Marco Dattini. He's a textile merchant who lives near Florence in Italy. I should probably tell you that courtesy of Iris O'Rigo's book, The Merchant of Prato, I've taken you back in time. It's 1396, and Dittini is furious.

You could lose your way from your nose to your mouth. What was going on? Well, Dittini's business associates were bungling the numbers. It was a common enough problem for any businessmen. At the end of the 1300s, Italian commerce was becoming complicated. Merchants were no longer mere travelling salesmen, able to keep track of profits by patting their purses. they were in charge of complex operations. D'Ateini, for example, ordered wool from the island of New Yorker two years ago, before the sheep had even grown it. That wool would eventually be processed by numerous subcontractors before becoming beautiful

roles of dyed cloth. The supply chain between shepherd and consumer ranged across Barcelona, Pisa, Venice, Valencia, North Africa, even back to New Yorker itself. It would be four years between the initial order of wool and the final sale of cloth. No wander the teeny insisted on absolute clarity, both about where the product was at any moment and his money. How did he manage this? Simple. Spreadsheets. The teeny of course did not use Microsoft Excel back in 1396, but he did use its direct

predecessor. Sheets of paper laid out according to the system of double entry bookkeeping, otherwise known as bookkeeping al-Avanet-Sianna. In double entry bookkeeping, every entry is made twice, the clues in the name. For example, if you spend 100 Florens on wool, that's recorded as a credit of 100 Florens in your cash account, and the debit of 100 Florens worth of wool, in your assets account. This extra effort makes it much easier to detect if a mistake has been made, the books won't balance. Double entry bookkeeping became an essential method for keeping track of who, of what, to whom.

For an exchange transactions, profits, losses, everything. It helped merchants such as Dittini ensure that no matter how incompetent their associates, nothing was lost. A century later, the master of double entry bookkeeping was a man named Luca Patigoli. He was a serious mathematician, a friend of Leonardo da Vinci, but he's best known today as the most famous accountant who ever lived. He literally wrote the book on the double entry method, back in 1494.

Pacholi once advised, if you cannot be a good accountant, you will grow pure way forward like a blind man, and may meet great losses. If you can't keep your spreadsheet straight, you may meet great losses. Remember that. Let's jump forward nearly 500 years to 1978.

We're at Harvard Business School. A student named Dan Bricklin is sitting in a classroom watching his accounting professor filling in rows and columns on the blackboard. The professor makes a change, and then works across and down the grid, erasing and rewriting other numbers to make everything add up. This erasing and rewriting is happening every day, millions of times a day, all over the world, as accounting clerks adjust the entries in what they call their spread sheets. Big sheets of paper spread across two pages of an accounting ledger.

Adjustments take a lot of work. But Dan Bricklin was a computer geek. A former programmer who immediately thought, I can do this on a computer. You would put a number in and hit return, and everything would recalculate, and you could watch it. You could watch the number change. Boom, boom, boom, it made a sound.

I had a real prototype. The rest is history. Bricklin and a friend called their spreadsheet program, Vizicelk. It went on sale on the 17th of October, 1979. It was a smash hit, soon followed by Lotus 123 and then in due course by Microsoft Excel itself. For accountants, digital spreadsheets were revolutionary, replacing hours of painstaking work with a tap on a keyboard.

But some things did not change. Accountants still had their professional training and their double entry system. But for the rest of us, well, Excel became ubiquitous, an easily accessible tool, a flexible tool, like a Swiss Army pen knife sitting in your digital back pocket. Any idiot could use it. And we did. Oh goodness me, we did. Nobody really knows what happened to the 15,841 positive Covid cases that disappeared from the spreadsheet. Public Health England, a government agency responsible for the process, hasn't published anything very informative on the issue. I asked them about it. The suggestion that any cases were lost is simply incorrect.

Oh? No cases were missed. There was a delay in referring cases for contact tracing and reporting them in the national figures. That delay was often four or five days. But experts will tell you that you really need to track contacts within 48 hours, a 5-day delay renders the test results almost useless. Look, guys, if you lose the positive cases for 4 or 5 days, you lose them. But how did they lose them? Somewhere somehow. Public Health England had used the wrong Excel file format, XLS rather than the more recent XLSX, and XLS spreadsheets simply don't have that many

rows, to the power of 16, about 64,000. That meant that during some automated process, cases had vanished off the bottom of the spreadsheet, and nobody had noticed. The idea of simply running out of space to put the numbers was rather amusing. The fact that Microsoft was never anyone's idea of cool simply added to the hilarity. Do you suffer from having to organize and analyze a small set of numbers? Is the undo function on a calculator for straightingly underpower for your calculations needs? Do you want to dabble in recreational mathematics? Then spreadsheets may be for you. That's a satirical advert from the Comedy and Mathematics YouTube channel, Stand Up Maths.

Please speak to your database developer before deciding if spreadsheets is right for you. advert from the Comedy and Mathematicsering lives and being fired. Spreadsheets is intended for short term use only. Stop using spreadsheets if you find yourself in charge of a government database with life and death ramifications. Spreadsheets from the makers of WordArt. A few weeks after the data lost scandal by a strange twist of fate, I found myself able to ask Bill Gates himself about what had happened. Bill Gates no longer runs Microsoft, and I was interviewing him about vaccines for a BBC programme called How to Vaccinate the World.

But the opportunity to have a bit of fun quizzing him about XLS and XLSX, it was too good to miss. I expressed the question in the nerdiest way possible and Gates' response was so straight-laced that I had to smile to myself. I guess the older format, you know, they overran the 64,000 limit, which is not there in the new format. So, you know, it's good to have people double-check things and I'm sorry that happened. Exactly how the outdated XLS format came to be used is unclear. Public Health England sent me an explanation, but it was rather vague.

I didn't understand it, so I showed it to some members of USEPRIG, the European Spreadsheet Risks Group. They spend their lives analysing what happens when spreadsheets go rogue, they're my kind of people. But they didn't understand what public health England had told me either. It was all a little light on detail. The basic problem was that whatever public health England had done wrong, they didn't have the right checks and controls to flag up problems. But I can just imagine what the merchant of Prato, Francesco di Marco D'Ateni might

have said. You could lose your way from your nose to your mouth. We'll explore how Excel became so eroprone after this message. Dr. Felina Hermanns is a researcher who studies spreadsheets. A few years ago she realised that there was a wonderful source of spreadsheets that she could study in their natural habitat. That source was a bankrupt energy company called NRON. NRON used to be huge, but two decades ago it collapsed, and various NRON executives were convicted of financial

crimes. Regulators extracted a large digital pile of half a million emails from NRON servers, and those emails are publicly available. Importantly for Dr. Herman's, thousands of those emails had spreadsheets attached. She started digging through them, looking at nearly 10,000 spreadsheets with calculations in them, she found that a quarter of them had at least one obvious error. The errors even seemed to multiply. If a spreadsheet had any mistakes at all, on average it contained more than 750. How can a spreadsheet acquire so many errors? I asked my friend Matt Parker, the man who literally wrote the book about mathematical mishaps and their consequences, a book with a delightful title, Humble Pie. Imagine cautionary tales, only with more jokes and more equations.

One spreadsheet problem is simple human error. For example, the time when candidates for a job in policing were listed alongside a column containing their scores on the test. When one column was resorted and the adjacent one was not, the test scores were effectively scrambled. All the time that the investment bank JP Morgan lost six billion dollars and when I say lost, I mean they lost the money not that they misplaced it for five days. They lost this $6 billion after several spreadsheet errors, notably one in which a risk indicator in a spreadsheet was being divided not by an average of two numbers, but by their sum. That made the risks look half as big as they should have done.

But Excel is happy to introduce errors without any help from us humans. Matt Parker told me that one common set of problems is produced by the Auto-Correct function. Excel loves to Auto-Correct. Type in an international phone number and Excel will strip off the leading zeros. They're mathematically redundant, but if you want to make a phone call, you'll find that they're not redundant at all. Or if instead you type in a 20-digit serial number, Excel will decide those 20 digits are a huge quantity and round them off, turning the last few digits into zeros.

If you're a genetics researcher, typing in the name of a gene such as March F1 or Septin 1, are generally abbreviated to March 1 or Sept 1? Well, you can imagine what Excel does with them. It turns those gene names into dates. Septin 1. And one study estimated that 20% of all genetics papers had errors caused by Excel's auto correction. Microsoft's response to the genes problem is that Excel's default

settings are intended to work in most day-to-day scenarios, which is the polite way of saying, guys, Excel was designed for accountants, not genetics researchers, but it's understandable that scientists picked up Excel and started to use it. It's right there on every computer, it's powerful, it's flexible, it's ubiquitous. The problem with ubiquitous tools is that we tend to use them even when they aren't the right tool for the job, even when we don't really know what we're doing. Come to think of it. Especially when we don't know what we're doing. I said earlier that Microsoft Excel is like a Swiss army knife.

As a boy, I was absolutely fascinated by these beautiful little red multi-tools. A pen knife with a can opener and three kinds of screwdriver and a bottle opener and a wire stripper and a tiny saw and some tweezers and even a toothpick, what a world of miracles and wonders. But as an adult geek who struggles to put up a bookshelf straight, even I've noticed something about people with practical skills. People such as plumbers, electricians, and carpenters. They don't use a Swiss Army knife. They bring a toolkit, with professional tools.

Microsoft Excel is a professional enough tool, if you're an accountant. Excel wasn't designed to run the entire contact tracing infrastructure of a once-proud nation, any more than a Swiss army knife, was designed to help you put up a set of shelves. The experts I've spoken to have different views about the deeper problem here. Some of them reckon that using Excel itself for contact tracing was the original sin that a different sort of software tool, a database, would have been much more appropriate. Others say, no, if you use Excel professionally with proper controls, it can easily handle the task of contact tracing, and a well-designed database would have taken time to implement. Excel was right there.

Professional carpenters don't use a Swiss Army knife, but if the shelves need to be put up immediately and you don't have a toolbox, why not give the Swiss Army knife a try? You just have to be aware of its limitations, and perhaps to redo the job properly when you have the tools to do so. Not long ago, I asked folks on Twitter if they could recommend some good books about the eradication of smallpox. Most people, instead, recommended books about Edward Jenner back in 1796 when he first demonstrated an effective smallpox vaccine. That's revealing, because I'd asked about the eradication of smallpox, and smallpox wasn't eradicated in 1796, not even close.

And while eradication would have been impossible without a highly effective vaccine, it also required the highly effective use of information, or as the merchant Francesco di Marco Dattini might have said, it required not losing your way from your nose to your mouth. Unlike COVID, smallpox infections are easy to detect, for the awful reason that smallpox does so much damage to the human body. Bill Faggy, one of the leaders of the fight against smallpox, says that you can even follow your nose. On at least two occasions, Smell alone alerted me to the presence of smallpox. As I walk down a hospital hallway in India, the dead animal odor stopped me in my tracks.

Following the smell, I located a smallpox patient. Another time, as I walked down an alley in an urban slum in Pakistan, the same smell hit me. There are competing smells in such places, but again, one smell stood out. Knocking on doors, I found two siblings with smallpox. Ever since the vaccine for smallpox was demonstrated in 1796, people dreamed of eradicating the disease, but those dreams kept failing to come true. The vaccinators would never manage to reach quite enough people. In poorer countries, smallpox would linger in isolated rural communities or neglected slums.

A generation of babies would be born without any immunity, and soon enough, the disease would be back. In the mid-1960s, smallpox was still killing 2 million people a year. This was the same number as died of COVID in 2020. The World Health Organization announced that it would re-double its efforts to eradicate the disease, and it planned to do so by intensifying the mass vaccination campaign. Bill Faggy was part of those efforts to fight smallpox. Faggy would show up in a village in eastern Nigeria, all six foot seven of him, and the local elders would put out the word, come and see the tallest man in the world,

and people would come. And Bill Faggy reckons he wants vaccinated 11,600 people in a single day. It wasn't enough. Still, the outbreak came. Late in 1966, Faggy received a radio message. This is a message for Dr. Faggy. A message for Dr. Faggy. Faggy speaking, what is it? We'll hear that message and why information matters if you want to eradicate smallpox after the break. The radio operator told Dr. Bill Faggy that there had been an outbreak of smallpox in a village about 100 miles away. He travelled there, found five cases, and vaccinated everyone

there had been in contact with. The handy thing about the smallpox vaccine is that it often still works, even if you vaccinate someone a few days after they've been exposed to the virus. Standard practice then would be to vaccinate everyone for miles around, but Fague's team just didn't have enough doses with them. So instead, he used radio and the local network of missionaries to try to work out where to use the vaccine. Every evening at 7 o'clock, let's switch on the radio and put the word out. This is Dr. Bill Faggy speaking. Here you'll Dr. Faggy. We've sent out all the runs, they've returned, and we have all the information you requested. That's amazing news. So are there any new cases?

Cases were identified in just four more villages. Faggy and his team quickly raced to the scene and administered the vaccine. The hope was that the vaccines would act like a fire break. The disease wouldn't find anyone to spread to, and it worked. Repeating the tactic, Fague's team eliminated smallpox from Eastern Nigeria within six months, just in time for the catastrophic civil war of 1967. Despite the chaos and enormous bloodshed of that war, smallpox did not return.

The secret to the success was to worry less about the blanket coverage that was never quite good enough, and worry more about quickly finding exactly where each outbreak was. Irradiation was all about information, and up until that point, information had been very patchy. As the WHO teams looked more closely, they realised they were missing the vast majority of the cases. Instead of 100,000 cases a year around the world, there were 10 million. Public health workers could beat smallpox by figuring out quickly where the outbreaks were and swiftly controlling the situation, isolating people with the disease and vaccinating their contacts. The strategy became known as ring vaccination and it has

a lot in common with Covid contact tracing. In both cases you need to rapidly isolate infected people and find their recent contacts. Ring vaccination worked and it didn't take long. The last gasp of smallpox in the wild was in Somalia, late in 1977. 1977. Ali Mayau Marlin, 23 years old, a cook and part-time vaccinator, had astonishingly not been vaccinated himself. One day, he was asked for directions to the local hospital by a man driving a Jeep with two sick children in the back. Soon enough, he started to feel unwell. He was wrongly diagnosed first with malaria, and then with chickenpox. It wasn't isolated or treated, until a friend of his a nurse made the correct diagnosis. Ali had the awful smallpox. His 91 friends and contacts were isolated and vaccinated.

None of them contracted the disease. Ali himself recovered and devoted his life to the fight against polio. I tell them how important these vaccines are. I tell them not to do something foolish like me. And the vaccines were important, essential, in fact. But so was quickly identifying and tracing contacts at risk. Smallpox had survived nearly two centuries of vaccination, but it couldn't survive a well-run system that targeted outbreaks and tracked potential cases. With hindsight, it seems so easy and simple, in

a way it was. But of course, keeping track of things is harder than it might first appear. Francesco Di Marco Dattini could have told you that, so could Bill Gates. If you really want proof that contact tracing works, how would you get it? If you were a mad scientist, praised with power and unchained by conventional ethics, you'd do an experiment. You'd hack into a country's contact tracing system, then you'd delete some of the positive cases, making sure that some regions lost a lot of cases and some lost very few. Then you'd compare what happened in the places where the contact tracing system was still

running smoothly to the places where thousands of cases had gone missing. If you weren't an evil genius of course, you wouldn't dream of doing such a thing. Instead, you'd keep an eye out for it happening by accident, because somebody bungled the formatting of Excel spreadsheets. Two economists, Timo Fetzer and Thomas Graber, did just that. They decided that no catastrophe should be allowed to occur without trying to learn some lessons which is very much in the spirit of cautionary tales. They combed through the evidence from public health England's mishap, and by comparing the different experiences of different regions, they concluded that the error had led to 125,000

additional infections. The story about Excel running out of numbers just seems so funny at first. Do you suffer from having to organize and analyze a small set of numbers? And Bill Gates' straight-faced, straight-laced response seemed funny too. Yeah, I guess the older format, you know, they overran the 64,000 limit, which is not there in the new format. You know, it's good to have people double check things and you know, I'm sorry that happened. But of course, it was Gates who'd seen through the joke on the surface to what Leibniz. He wasn't laughing, not because he had no sense of humor, but because he understood that this wasn't a comedy.

It was a tragedy. The economists, Fetzer and Graber have calculated a conservative estimate of the number of people who died, unknown victims of the spreadsheet error. They think the death toll is at least 1500 people. So the next time there's a pandemic, let's make sure we have our spreadsheets in order. After all, Hazliannard of Inch is friend, the father of accounting, Luca Pacholi, warned us more than 500 years ago. If you cannot be a good accountant, you will grow up your way forward like a blind

van and may meet great losses. 1500 people dead, great losses, indeed. Key sources for this cautionary tale include Planet Money Episode 606, Spreadsheets, Matt Parker's YouTube video when Spreadsheets Attack and Bill Faggy's book House on Fire. For a full list of our sources, see TimHalford.com Corsinary Tales is written by me Tim Halford with Andrew Wright. It's produced by Ryan Dilly and Marilyn Rust. The sound design and original music are the work of Pascal Weiss. Julia Barton edited the scripts. Starring in this series of cautionary tales are Helena Bonham Carter and Jeffrey Wright.

Alongside Nazar Eldorazi, Ed Gochan, Melanie Gutridge, Rachel Hanzhaw, Copna Holbrook Smith, Reg Lockett, Miseum and Roe and Rufus Wright. The show would not have been possible without the work of Mia Label, Jacob Weisberg, Heather Fabe, John Schnarrs, Carly McLeory, Eric Sandler, Emily Rustick, Maggie Taylor, Daniela Lacan and Maya Caning. Corsionary Tales is a production of Pushkin Industries. If you like the show, please remember to share, rate and review. Thank you.

you