May 21, 2021 • 33 mins
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.com
See omnystudio.com/listener for privacy information.
Mark as Played
Transcript
Episode Transcript
Available transcripts are automatically generated. Complete accuracy is not guaranteed.
Speaker 1 (00:15):
Pushkin, we will have a test track and trace operation
that will be world beating. That's Boris Johnson, the British
(00:36):
Prime Minister, speaking to Parliament with a typical jingoistic flourish
in May twenty twenty. 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
(00:56):
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 trace system that would be better than
anything Johnny Foreigner would have. A contact tracing system for
COVID has three key elements. First, you need to be
(01:20):
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
(01:44):
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 twenty twenty,
there seemed to be every chance of doing that. Infections
(02:05):
had been beaten back by a long, strict and 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
tracers to manage. But by September there were alarming signs
(02:26):
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
ninety percent of people weren't getting test results back the
next day. There's not much point in contact tracing if
(02:47):
it takes days to even figure out who was infected.
And then on Sunday of the fourth of 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
(03:07):
next morning to help them explain it and what exactly
had happened. Nearly sixteen thousand positive cases had disappeared completely
from the contact tracing system. Sixteen thousand people who should
have been warned that they were infected and a danger
to others. Sixteen thousand cases in which the contact tracers
(03:28):
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
(03:48):
run out of numbers. I'm Tim Harford, and you're listening
to cautionary tales. You cannot see a crow in a
(04:19):
bowl full of milk. This is Francesco di Marco d'attini.
He's a textile merchant who lives near Florence in Italy.
I should probably tell you that courtesy of Iris Orego's
book The Merchant of Prato, I've taken you back in time.
(04:40):
It's thirteen ninety six and Dattini is furious you could
lose your weight from your nose to your mouth. What
was going on? Well, D'ttini's business associates were bungling the numbers.
It was a common enough problem for any businessman. At
the end of the thirteen hundreds, Italian commerce was becoming complicated.
(05:04):
Merchants were no longer mere traveling salesman able to keep
track of profits by patting their purses. They were in
charge of complex operations. The Teeney, for example, ordered wool
from the island of Mayorka two years ago before the
sheep had even grown. It That wool would eventually be
processed by numerous subcontractors before becoming beautiful rolls of dyed cloth.
(05:30):
The supply chain between shepherd and consumer ranged across Barcelona, Pisa, Venice, Valencia,
North Africa, even back to Mayorka itself. It would be
four years between the initial order of wool and the
final sale of cloth. No wonder, the Teeney insisted on
absolute clarity both about where the product was at any
(05:54):
moment and his money. How did he manage this simple spreadsheets?
The Teeny, of course, did not use Microsoft Excel back
in thirteen ninety six, but he did use its direct predecessor,
sheets of paper laid out according to the system of
(06:16):
double entry book keeping otherwise known as book keeping a
la vanetziana. In double entry book keeping, every entry is
made twice the clues in the name. For example, if
you spend a hundred florins on wool, that's recorded as
a credit of a hundred florins in your cash account
(06:39):
and the debit of a hundred florins 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, foreign
(07:00):
exchange transactions, profits, losses, everything. It helped merchants such as
deteining 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 Paccioli. He was a
(07:24):
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 fourteen ninety four. Paccioli once advised, if you
cannot be a good accountant, you will grope your way
(07:44):
forward like a blind man, and may meet great losses.
If you can't keep your spreadsheets straight, you may meet
great losses. Remember that. Let's jump forward nearly five hundred
years to nineteen seventy eight. We're at Harvard Business School
(08:06):
and a student named Dan Bricklin is sitting in 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
(08:30):
the world, as accounting clerks adjust the entries in what
they call their spreadsheets, 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
(08:51):
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. Bomb bomb bomb, It made
a sound. I had a real prototype. The rest is history.
Bricklin and a friend called their spreadsheet program VisiCalc. It
(09:15):
went on sale on the seventeenth of October nineteen seventy nine.
It was a smash hit, soon followed by Lotus one,
two three, and then in due course by Microsoft Excel itself.
For accountance, digital spreadsheets were revolutionary, replacing hours of painstaking
(09:36):
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
(09:56):
like a Swiss Army penknife, 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 fifteen thousand,
(10:16):
eight hundred and forty one 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.
(10:38):
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 forty eight hours.
A five day delay renders the test results almost useless. Look, guys,
(11:00):
if you lose the positive cases for four or five 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
(11:23):
simply don't have that many rows to to the power
of sixteen about sixty four thousand. 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.
(11:45):
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 undue function on a calculator frustrating the underpowered for
your calculations needs? Do you want to dabble in recreational mathematics,
then spreadsheets maybe for you. That's a satirical advert from
(12:09):
the comedy and mathematics YouTube channel Stand Up Maths. Please
speak to your database developer before deciding if spreadsheets is
right for you. Common side effects include accidentally sorting some
but not all of the data, slight cell loss when
selecting numbers, hashtag name, question mark, losing key medical data
during a pandemic and endangering lives, and being fired. Spreadsheet
(12:33):
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
word art. A few weeks after the data loss scandal,
by a strange twist of fate, I found myself able
to ask Bill Gates himself about what had happened. Bill
(12:56):
Gates no longer runs Microsoft, and I was interviewing him
about vaccines for a BBC program called How to Vaccinate
the World, but the opportunity to have a bit of
fun quizzing him about XLS and xlsx it's too good
to miss. I expressed the question in the nerdiest way possible,
and Gates's response was so straight laced I had to
(13:17):
smile to myself. Yeah, I guess the older format. You know,
they overran the sixty four thousand limit, which is not
there in the new format, So you know, it's good
to have people double check things, and I, you know,
I'm sorry that happened. Exactly how the outdated XLS format
(13:39):
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
use Brig, the European Spreadsheet Risks Group. They spend their
lives analyzing what happens when spreadsheets go rogue. They're my
kind of people. But they didn't understand what Public Health
(14:01):
England had told me either. It was all a little
light on detail. The basic problem was that whatever like
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 DiMarco D'ttini,
might have said. You could lose your way from your
(14:23):
nose to your mouth. We'll explore how Excel became so
error prone after this message. Doctor Felina Herman's is a
researcher who studies spreadsheets. A few years ago, she realized
(14:44):
that there was a wonderful source of spreadsheets that she
could study in their natural habitat. That source was a
bankrupt energy company called Enron. Enron used to be huge,
but two decades ago it collapsed and various Enron executives
were convicted of financial crimes. Regulators extracted a large digital
(15:06):
pile of half a million emails from and run servers,
and those emails are publicly available. Importantly, for doctor Herman's,
thousands of those emails had spreadsheets attached. She started digging
through them. Looking at nearly ten thousand spreadsheets with calculations
in them, she found that a quarter of them had
(15:29):
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 seven hundred and fifty How can
a spreadsheet acquire so many errors? I asked my friend
Matt Parker, the man who literally wrote the book about
(15:50):
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
(16:13):
a 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.
(16:34):
They lost this six billion dollars 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.
(16:56):
Matt Parker told me that one common set of problems
is produced by the auto correct function. Excel loves to autocorrect.
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
(17:20):
a twenty digit serial number, Xcel will decide those twenty
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 f one or sept in one are generally abbreviated
(17:40):
to march one or sept one. Well, you can imagine
what Xcel does with them. It turns those gene names
into dates, and one study estimated that twenty percent of
all genetics papers had errors caused by Xcel's autocorrection. Microsoft's
(18:04):
response to the genes problem is that Xcel'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
(18:24):
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
(18:46):
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
(19:08):
Gig 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
(19:28):
professional enough tool if you're an accountant. Excel wasn't designed
to run the entire contact tracing infrastructure of a wants
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
(19:49):
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. XCEL was right there. Professional carpenters
(20:14):
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
(20:35):
on Twitter if they could recommend some good books about
the eradication of smallpox. Most people instead recommended books about
Edward Jenna back in seventeen ninety six, 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
(20:58):
seventeen ninety six, not even close. And while eradication would
have been impossible without a highly effective vaccine, it also
required highly effective use of information, or, as the merchant
Francesco di Marco d'atini might have said, it required not
losing your way from your nose to your mouth. Unlike COVID,
(21:23):
smallpox infections are easy to detect. For the awful reason
that smallpox does so much damage to the human body.
Bill Fagy, 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 small pox. As I walked down a hospital hallway
(21:46):
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 slim
in Pakistan, the same smell hit me. There are competing
smells in such places, but again one smells stood out.
(22:11):
Knocking on doors, I found two siblings with smallpox. Ever
since the vaccine for smallpox was demonstrated in seventeen ninety six,
people dreamed of eradicating the disease, but those dreams kept
failing to come true. The vaccinators would never manage to
(22:33):
reach quite enough people in poorer countries, smallpox would linger
in isolated rural communities or neglected slums. A generation of
babies would be borne without any immunity, and soon enough
the disease would be back. In the mid nineteen sixties,
smallpox was still killing two million people a year. This
(22:56):
was the same number as died of COVID. In twenty twenty,
the World Health Organization announced that it would redouble its
efforts to eradicate the disease, and it planned to do
so by intensifying the mass vaccination campaign. Bill Fegi was
part of those efforts to fight smallpox. Fegi would show
up in a village in eastern Nigeria, all six foot
(23:19):
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 Fegy reckons he wants vaccinated.
Eleven thousand, six hundred people in a single day. It
wasn't enough. Still, The outbreaks came late in nineteen sixty six.
(23:43):
Vegi received a radio message. This is a message for
doctor Fagi, A message for doctor Segy Veggie speaking what
is it? We'll hear that message and why information matters
if you want to eradicate smallpox. After the break, This
(24:10):
is a message for doctor Pegi. A message for doctor
Segy Veggie speaking what is it? The radio operator told
doctor Bill Fegi that there had been an outbreak of
smallpox in a village about one hundred miles away. He
traveled there, found five cases and vaccinated everyone they'd been
in contact with. The handy thing about the smallpox vaccine
(24:34):
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 Vega'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.
(24:56):
Every evening at seven o'clock they'd switch on the radio
and put the word out, this is doctor Bill Fegy
speaking here. Doctor Pegi, which send out and we have
all the information you requested. That's amazing news. So are
(25:18):
there any new cases? Cases were identified in just four
more villages. Vega and his team quickly raced to the
scene and administered the vaccine. The hope was that the
vaccines would act like a firebreak the disease wouldn't find
anyone to spread to, and it worked. Repeating the tactic,
(25:39):
Vega's team eliminated smallpox from eastern Nigeria within six months,
just in time for the catastrophic civil war of nineteen
sixty seven. Despite the chaos and enormous bloodshed of that war,
smallpox did not return. The secret to the success was
(26:00):
to worry less about the blanket coverage that was never
quite good enough, and worry more about quickly finding exactly
where each outbreak was. Eradication was all about information, and
up until that point information had been very patchy. As
the WHO teams looked more closely, they realized they were
(26:21):
missing the vast majority of the cases. Instead of one
hundred thousand cases a year around the world, there were
ten 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
(26:44):
strategy became known as a 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
(27:07):
Somalia late in nineteen seventy seven. Ali Mayaw Marlin, twenty
three 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,
(27:30):
he started to feel unwell. He was wrongly diagnosed first
with malaria and then with chicken pox. He wasn't isolated
or treated until a friend of his, a nurse, made
the correct diagnosis. Ali had the awful smallpox. His ninety
one friends and contacts were isolated and vaccinated. None of
(27:54):
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
(28:18):
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
(28:41):
first appear. Francesco di Marco D'ttini 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
(29:05):
a country's contact tracing system. Then you'd delete some of
the positive case, 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,
(29:27):
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
(29:50):
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 one hundred and twenty five thousand additional infections. The
(30:11):
story about Excel running out of numbers just seemed so
funny at first. Do you suffer from having to organize
and analyze a small set of numbers? And Bill Gates's
straight faced, straight laced response seemed funny too. Yeah, I
guess the older format. You know, they overran the sixty
four thousand limit, which is not there in the new format.
(30:35):
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 lay beneath. 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
(30:57):
economists Fetza 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
fifteen hundred people. So the next time there's a pandemic,
(31:17):
let's make sure we have our spreadsheets in order. After all,
As Leonardo da Vinci's friend, the father of accounting, Luca
Paccioli warned us more than five hundred years ago, if
you cannot be a good accountant, you will grope your
way forward like a blind man and may meet great losses.
(31:39):
Fifteen hundred people dead, great losses indeed. Key sources for
(32:03):
this cautionary tale include Planet Money episode six h six, Spreadsheets,
Matt Parker's YouTube video whence Spreadsheets Attack, and Bill Feggy's
book House on Fire. For a full list of our sources,
see Tim Harford dot com. Cautionary Tales is written by
(32:26):
me Tim Harford with Andrew Wright. It's produced by Ryan
Dilley and Marilyn Rust. The sound design and original music
are the work of Pascal Wise. Julia Barton edited the scripts.
Starring in this series of Cautionary Tales are Helena Bonham,
Carter and Jeoffrey Wright, alongside Nazar Alderazzi, Ed Gochen, Melanie Gutteridge,
(32:51):
Rachel Hanshaw, cobnor Holbrook, Smith, Reg Lockett, Missiamunroe and Rufus Wright.
The show would not have been possible without the work
of Mia LaBelle, Jacob Weisberg, Hella Fane, John Schnarz, Carlie mcgliori,
Eric Sandler, Emily Rostock, Maggie Taylor, Daniella Lakhan, and Maya Kane.
(33:14):
Cautionary Tales is a production of Pushkin Industries. If you
like the show, please remember to share, rate, and review.
Pushkin, we will have a test track and trace operation
that will be world beating. That's Boris Johnson, the British
(00:36):
Prime Minister, speaking to Parliament with a typical jingoistic flourish
in May twenty twenty. 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
(00:56):
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 trace system that would be better than
anything Johnny Foreigner would have. A contact tracing system for
COVID has three key elements. First, you need to be
(01:20):
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
(01:44):
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 twenty twenty,
there seemed to be every chance of doing that. Infections
(02:05):
had been beaten back by a long, strict and 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
tracers to manage. But by September there were alarming signs
(02:26):
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
ninety percent of people weren't getting test results back the
next day. There's not much point in contact tracing if
(02:47):
it takes days to even figure out who was infected.
And then on Sunday of the fourth of 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
(03:07):
next morning to help them explain it and what exactly
had happened. Nearly sixteen thousand positive cases had disappeared completely
from the contact tracing system. Sixteen thousand people who should
have been warned that they were infected and a danger
to others. Sixteen thousand cases in which the contact tracers
(03:28):
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
(03:48):
run out of numbers. I'm Tim Harford, and you're listening
to cautionary tales. You cannot see a crow in a
(04:19):
bowl full of milk. This is Francesco di Marco d'attini.
He's a textile merchant who lives near Florence in Italy.
I should probably tell you that courtesy of Iris Orego's
book The Merchant of Prato, I've taken you back in time.
(04:40):
It's thirteen ninety six and Dattini is furious you could
lose your weight from your nose to your mouth. What
was going on? Well, D'ttini's business associates were bungling the numbers.
It was a common enough problem for any businessman. At
the end of the thirteen hundreds, Italian commerce was becoming complicated.
(05:04):
Merchants were no longer mere traveling salesman able to keep
track of profits by patting their purses. They were in
charge of complex operations. The Teeney, for example, ordered wool
from the island of Mayorka two years ago before the
sheep had even grown. It That wool would eventually be
processed by numerous subcontractors before becoming beautiful rolls of dyed cloth.
(05:30):
The supply chain between shepherd and consumer ranged across Barcelona, Pisa, Venice, Valencia,
North Africa, even back to Mayorka itself. It would be
four years between the initial order of wool and the
final sale of cloth. No wonder, the Teeney insisted on
absolute clarity both about where the product was at any
(05:54):
moment and his money. How did he manage this simple spreadsheets?
The Teeny, of course, did not use Microsoft Excel back
in thirteen ninety six, but he did use its direct predecessor,
sheets of paper laid out according to the system of
(06:16):
double entry book keeping otherwise known as book keeping a
la vanetziana. In double entry book keeping, every entry is
made twice the clues in the name. For example, if
you spend a hundred florins on wool, that's recorded as
a credit of a hundred florins in your cash account
(06:39):
and the debit of a hundred florins 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, foreign
(07:00):
exchange transactions, profits, losses, everything. It helped merchants such as
deteining 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 Paccioli. He was a
(07:24):
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 fourteen ninety four. Paccioli once advised, if you
cannot be a good accountant, you will grope your way
(07:44):
forward like a blind man, and may meet great losses.
If you can't keep your spreadsheets straight, you may meet
great losses. Remember that. Let's jump forward nearly five hundred
years to nineteen seventy eight. We're at Harvard Business School
(08:06):
and a student named Dan Bricklin is sitting in 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
(08:30):
the world, as accounting clerks adjust the entries in what
they call their spreadsheets, 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
(08:51):
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. Bomb bomb bomb, It made
a sound. I had a real prototype. The rest is history.
Bricklin and a friend called their spreadsheet program VisiCalc. It
(09:15):
went on sale on the seventeenth of October nineteen seventy nine.
It was a smash hit, soon followed by Lotus one,
two three, and then in due course by Microsoft Excel itself.
For accountance, digital spreadsheets were revolutionary, replacing hours of painstaking
(09:36):
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
(09:56):
like a Swiss Army penknife, 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 fifteen thousand,
(10:16):
eight hundred and forty one 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.
(10:38):
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 forty eight hours.
A five day delay renders the test results almost useless. Look, guys,
(11:00):
if you lose the positive cases for four or five 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
(11:23):
simply don't have that many rows to to the power
of sixteen about sixty four thousand. 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.
(11:45):
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 undue function on a calculator frustrating the underpowered for
your calculations needs? Do you want to dabble in recreational mathematics,
then spreadsheets maybe for you. That's a satirical advert from
(12:09):
the comedy and mathematics YouTube channel Stand Up Maths. Please
speak to your database developer before deciding if spreadsheets is
right for you. Common side effects include accidentally sorting some
but not all of the data, slight cell loss when
selecting numbers, hashtag name, question mark, losing key medical data
during a pandemic and endangering lives, and being fired. Spreadsheet
(12:33):
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
word art. A few weeks after the data loss scandal,
by a strange twist of fate, I found myself able
to ask Bill Gates himself about what had happened. Bill
(12:56):
Gates no longer runs Microsoft, and I was interviewing him
about vaccines for a BBC program called How to Vaccinate
the World, but the opportunity to have a bit of
fun quizzing him about XLS and xlsx it's too good
to miss. I expressed the question in the nerdiest way possible,
and Gates's response was so straight laced I had to
(13:17):
smile to myself. Yeah, I guess the older format. You know,
they overran the sixty four thousand limit, which is not
there in the new format, So you know, it's good
to have people double check things, and I, you know,
I'm sorry that happened. Exactly how the outdated XLS format
(13:39):
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
use Brig, the European Spreadsheet Risks Group. They spend their
lives analyzing what happens when spreadsheets go rogue. They're my
kind of people. But they didn't understand what Public Health
(14:01):
England had told me either. It was all a little
light on detail. The basic problem was that whatever like
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 DiMarco D'ttini,
might have said. You could lose your way from your
(14:23):
nose to your mouth. We'll explore how Excel became so
error prone after this message. Doctor Felina Herman's is a
researcher who studies spreadsheets. A few years ago, she realized
(14:44):
that there was a wonderful source of spreadsheets that she
could study in their natural habitat. That source was a
bankrupt energy company called Enron. Enron used to be huge,
but two decades ago it collapsed and various Enron executives
were convicted of financial crimes. Regulators extracted a large digital
(15:06):
pile of half a million emails from and run servers,
and those emails are publicly available. Importantly, for doctor Herman's,
thousands of those emails had spreadsheets attached. She started digging
through them. Looking at nearly ten thousand spreadsheets with calculations
in them, she found that a quarter of them had
(15:29):
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 seven hundred and fifty How can
a spreadsheet acquire so many errors? I asked my friend
Matt Parker, the man who literally wrote the book about
(15:50):
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
(16:13):
a 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.
(16:34):
They lost this six billion dollars 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.
(16:56):
Matt Parker told me that one common set of problems
is produced by the auto correct function. Excel loves to autocorrect.
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
(17:20):
a twenty digit serial number, Xcel will decide those twenty
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 f one or sept in one are generally abbreviated
(17:40):
to march one or sept one. Well, you can imagine
what Xcel does with them. It turns those gene names
into dates, and one study estimated that twenty percent of
all genetics papers had errors caused by Xcel's autocorrection. Microsoft's
(18:04):
response to the genes problem is that Xcel'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
(18:24):
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
(18:46):
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
(19:08):
Gig 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
(19:28):
professional enough tool if you're an accountant. Excel wasn't designed
to run the entire contact tracing infrastructure of a wants
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
(19:49):
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. XCEL was right there. Professional carpenters
(20:14):
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
(20:35):
on Twitter if they could recommend some good books about
the eradication of smallpox. Most people instead recommended books about
Edward Jenna back in seventeen ninety six, 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
(20:58):
seventeen ninety six, not even close. And while eradication would
have been impossible without a highly effective vaccine, it also
required highly effective use of information, or, as the merchant
Francesco di Marco d'atini might have said, it required not
losing your way from your nose to your mouth. Unlike COVID,
(21:23):
smallpox infections are easy to detect. For the awful reason
that smallpox does so much damage to the human body.
Bill Fagy, 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 small pox. As I walked down a hospital hallway
(21:46):
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 slim
in Pakistan, the same smell hit me. There are competing
smells in such places, but again one smells stood out.
(22:11):
Knocking on doors, I found two siblings with smallpox. Ever
since the vaccine for smallpox was demonstrated in seventeen ninety six,
people dreamed of eradicating the disease, but those dreams kept
failing to come true. The vaccinators would never manage to
(22:33):
reach quite enough people in poorer countries, smallpox would linger
in isolated rural communities or neglected slums. A generation of
babies would be borne without any immunity, and soon enough
the disease would be back. In the mid nineteen sixties,
smallpox was still killing two million people a year. This
(22:56):
was the same number as died of COVID. In twenty twenty,
the World Health Organization announced that it would redouble its
efforts to eradicate the disease, and it planned to do
so by intensifying the mass vaccination campaign. Bill Fegi was
part of those efforts to fight smallpox. Fegi would show
up in a village in eastern Nigeria, all six foot
(23:19):
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 Fegy reckons he wants vaccinated.
Eleven thousand, six hundred people in a single day. It
wasn't enough. Still, The outbreaks came late in nineteen sixty six.
(23:43):
Vegi received a radio message. This is a message for
doctor Fagi, A message for doctor Segy Veggie speaking what
is it? We'll hear that message and why information matters
if you want to eradicate smallpox. After the break, This
(24:10):
is a message for doctor Pegi. A message for doctor
Segy Veggie speaking what is it? The radio operator told
doctor Bill Fegi that there had been an outbreak of
smallpox in a village about one hundred miles away. He
traveled there, found five cases and vaccinated everyone they'd been
in contact with. The handy thing about the smallpox vaccine
(24:34):
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 Vega'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.
(24:56):
Every evening at seven o'clock they'd switch on the radio
and put the word out, this is doctor Bill Fegy
speaking here. Doctor Pegi, which send out and we have
all the information you requested. That's amazing news. So are
(25:18):
there any new cases? Cases were identified in just four
more villages. Vega and his team quickly raced to the
scene and administered the vaccine. The hope was that the
vaccines would act like a firebreak the disease wouldn't find
anyone to spread to, and it worked. Repeating the tactic,
(25:39):
Vega's team eliminated smallpox from eastern Nigeria within six months,
just in time for the catastrophic civil war of nineteen
sixty seven. Despite the chaos and enormous bloodshed of that war,
smallpox did not return. The secret to the success was
(26:00):
to worry less about the blanket coverage that was never
quite good enough, and worry more about quickly finding exactly
where each outbreak was. Eradication was all about information, and
up until that point information had been very patchy. As
the WHO teams looked more closely, they realized they were
(26:21):
missing the vast majority of the cases. Instead of one
hundred thousand cases a year around the world, there were
ten 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
(26:44):
strategy became known as a 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
(27:07):
Somalia late in nineteen seventy seven. Ali Mayaw Marlin, twenty
three 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,
(27:30):
he started to feel unwell. He was wrongly diagnosed first
with malaria and then with chicken pox. He wasn't isolated
or treated until a friend of his, a nurse, made
the correct diagnosis. Ali had the awful smallpox. His ninety
one friends and contacts were isolated and vaccinated. None of
(27:54):
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
(28:18):
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
(28:41):
first appear. Francesco di Marco D'ttini 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
(29:05):
a country's contact tracing system. Then you'd delete some of
the positive case, 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,
(29:27):
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
(29:50):
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 one hundred and twenty five thousand additional infections. The
(30:11):
story about Excel running out of numbers just seemed so
funny at first. Do you suffer from having to organize
and analyze a small set of numbers? And Bill Gates's
straight faced, straight laced response seemed funny too. Yeah, I
guess the older format. You know, they overran the sixty
four thousand limit, which is not there in the new format.
(30:35):
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 lay beneath. 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
(30:57):
economists Fetza 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
fifteen hundred people. So the next time there's a pandemic,
(31:17):
let's make sure we have our spreadsheets in order. After all,
As Leonardo da Vinci's friend, the father of accounting, Luca
Paccioli warned us more than five hundred years ago, if
you cannot be a good accountant, you will grope your
way forward like a blind man and may meet great losses.
(31:39):
Fifteen hundred people dead, great losses indeed. Key sources for
(32:03):
this cautionary tale include Planet Money episode six h six, Spreadsheets,
Matt Parker's YouTube video whence Spreadsheets Attack, and Bill Feggy's
book House on Fire. For a full list of our sources,
see Tim Harford dot com. Cautionary Tales is written by
(32:26):
me Tim Harford with Andrew Wright. It's produced by Ryan
Dilley and Marilyn Rust. The sound design and original music
are the work of Pascal Wise. Julia Barton edited the scripts.
Starring in this series of Cautionary Tales are Helena Bonham,
Carter and Jeoffrey Wright, alongside Nazar Alderazzi, Ed Gochen, Melanie Gutteridge,
(32:51):
Rachel Hanshaw, cobnor Holbrook, Smith, Reg Lockett, Missiamunroe and Rufus Wright.
The show would not have been possible without the work
of Mia LaBelle, Jacob Weisberg, Hella Fane, John Schnarz, Carlie mcgliori,
Eric Sandler, Emily Rostock, Maggie Taylor, Daniella Lakhan, and Maya Kane.
(33:14):
Cautionary Tales is a production of Pushkin Industries. If you
like the show, please remember to share, rate, and review.
Host
Tim Harford
Popular Podcasts
Dateline NBC
Current and classic episodes, featuring compelling true-crime mysteries, powerful documentaries and in-depth investigations. Follow now to get the latest episodes of Dateline NBC completely free, or subscribe to Dateline Premium for ad-free listening and exclusive bonus content: DatelinePremium.com
Amy Robach & T.J. Holmes present: Aubrey O’Day, Covering the Diddy Trial
Introducing… Aubrey O’Day Diddy’s former protege, television personality, platinum selling music artist, Danity Kane alum Aubrey O’Day joins veteran journalists Amy Robach and TJ Holmes to provide a unique perspective on the trial that has captivated the attention of the nation. Join them throughout the trial as they discuss, debate, and dissect every detail, every aspect of the proceedings. Aubrey will offer her opinions and expertise, as only she is qualified to do given her first-hand knowledge. From her days on Making the Band, as she emerged as the breakout star, the truth of the situation would be the opposite of the glitz and glamour. Listen throughout every minute of the trial, for this exclusive coverage. Amy Robach and TJ Holmes present Aubrey O’Day, Covering the Diddy Trial, an iHeartRadio podcast.
Stuff You Should Know
If you've ever wanted to know about champagne, satanism, the Stonewall Uprising, chaos theory, LSD, El Nino, true crime and Rosa Parks, then look no further. Josh and Chuck have you covered.