Hey, this is Overpass Insights! My name is Eric and I make apps! Now today, I want to talk a
little bit about the COVID reporting glitch in England and why it is important to log everything.
Okay, so yesterday morning, I'm reading the news, and apparently, there's been this big fiasco where
due to a computer glitch, sixteen thousand cases of COVID have gone unreported. And it's kind of
a serious thing because they've been missed off like track and trace. So, they have to go
back and see if they can catch these people, the infection can keep going out and all of it's put
down to a computer glitch. Now, as a technology person whenever I hear a computer glitch, I always
wonder what it is. Is it - was it like a server failure? Was it a Cron job that didn't go on?
Was it a bug? Is it like the developer forgot to do something or is it somebody who was supposed to
do something a certain time didn't do it? I always wondered what it is. But, um, anyway, it was kind
of a serious story here. Now, if you don't live in England you probably don't care about it. But
the reasons for it were fascinating because there was a story on BBC yesterday about how
Excel- Microsoft Excel was the reason for those 16 000 Coronavirus cases to go unreported. So,
basically what was happening was everything was coming in a CSV file which is fine,
right? A lot of us store things in the CSV file and they were being aggregated into a Microsoft
Excel Workbook. However, it wasn't an XLX, XLXS notebook, um XLSX notebook. You know, from
the later versions of Excel it was the early one xls so xls files could only hold like 65 000 rows
so even though you were having the csv files with you know whatever a lot more than that when it was
imported into excel to aggregate it it was losing a lot of that data of course if they use xlsx
xlsx then they would have like over a million rows right uh and it was it was kind of strange how
like everything there was like this one weak point in the chain it was like using the wrong format
of excel caused all of these problems and the reason i thought it was interesting was because
i had a similar thing happen to me working at an investment bank although this was back in like
2006 right so basically what happened we had there was this mis system like these
management reporting i was kind of brought in to help put it on line so we there was like
one guy who was not a developer but he was really good with excel he was really good with vba
or he kind of learned as he went it was kind of like their junior guy and he built up this
elaborate reporting system which pulled in data from all these different places uh and gave a you
know ran all these macros it took it took hours to run i mean excel is really good for reporting
um it's really good for reporting if you've ever worked on a system where somebody gives you
something that was done in excel and they say okay we want to put this into an app or we want to put
this online the difficulty is is the flexibility of excel you have to say okay you can't do
just like a pivot table whenever you want you can't just you know whatever you have to tell us
what you want to do so we can build that into the system i mean excel is really really flexible it's
just not very scalable so he was having this all these excel reports which are running um
uh it was excel and access and all this vba stuff was going and every day he's reported doing this
report and it was taking him you know six hours every day just doing all this kind of stuff mainly
because the macros were taking so long to run and everything like that right which was fine so
me and another developer we were brought in to basically duplicate that online so we were
writing the cron job or the windows task scheduler because it was a windows based uh application just
to take those files and then pull them into a central database and then we're building so once
we get everything in the central sql database we can start doing the front end there so i was my
job was to write the import routine and of course i had like you know try to make it very flexible
so whatever format they bring in in the future we just have a config file pull everything in
uh and one of the things that i i did which i wasn't asked for this is one of the things
as a developer i wasn't asked to do this but i wanted to have like one table which was just
the manifest table i call the manifest because every time we import it i just want to say
what time was it imported what what locate because sometimes it's ftp to a certain drive
you pick it up from a certain drive sometimes it's pulling it from a different database
sometimes you have an api feed there was like all these different connectors coming in
right and so i kind of wanted to know how many records i'm actually pulling in so i
just put that in the manifest table so how many records are actually coming in
and we had a few of them which were x they weren't excel files they were csv files right um and it
was after like five days of running this or doing all the other stuff i realized that this csv file
always had the same number of rows right it was new data every single time but it was always 65
365 it was like it was a specific number which i thought was strange so i googled that number and
find out that's the exact limit of an xls file right and so the first thing i did was i went
to the uh the head of the project and i said this is really strange because this data is coming in
as 65 000 and it's really strange that it's it's new data every day these transactions are the same
every day but it's always that same number and that's the exact same number as the limit of an
excel file and he was like well how many should it be and i was like i don't know because like that's
further down the stream somebody's producing something is getting there uh and he was kind
of like i wonder if i should tell anybody about this right because it was like like it's like
business decisions were being made on this this is how flimsy the reporting system was like
it was hugely important to the to the investment bank but like it was just there was like this weak
leak in the chain uh in the end it was like you know i think it finally was reported it went like
it was this big i mean it wasn't i was so glad i was so glad it wasn't my code
like it was somebody else was doing something else into excel and then putting it out there
right but if we didn't log those things we never would have found it and who knows how many years
that that had been running that way or they just been working off the wrong data i mean there's
one good thing about this story which is that they caught it early so that they didn't have this this
discrepancy in the reporting figures because they did say oh you're using xls which the 65 000 row
limit instead of you know using a later version of excel which you gotta question whether or not
they should be using excel anyway but it depends on who's doing the job i guess right which leads
me to logging right one of the things that i learned pretty early on and i learned this not
i learned this a bit from experience but i learned a lot from other developers
which is the importance of logging things right the importance of saying okay this especially when
it's automated we have an automated process it's like this happened at this time this is the data
we brought in this is how long the process took this is uh any exceptions that happen
right you need to have that someplace it doesn't need to be available to everybody
but you need to be able to get to it right and the same with you know deleting transactions the
same with uh you know user activity if you can as a developer i've always been where i want to
have everything logged if i can unless it's like it gets ridiculous and it really slows down the
system i want everything done because this is what happens and you don't do the logs for your
client you don't do it because they ask you for it you do it because you do it for yourself because
this happens again if you've been a software developer for a while if you've done any projects
for anybody for any particular amount of time you this kind of thing will happen uh eric there's
something wrong with your system oh it's my system all of a sudden okay uh eric there's something
wrong with your system uh uh the other day we put in all these rows and um and then they've
just gone missing right it's just we talked to everybody and nobody's you know uh nobody's been
dealing with them and and the va or or the values just change and so there's something weird going
on there god can you investigate it and you're like well surely somebody's changed something and
i no no we talk to everybody we talk to everybody who has access to the system nobody's touched it
right and then like when you have logging i mean it's nothing more satisfying than being able to go
in and say okay actually those rows were deleted by jill at 10 13 she also did this this and this
so she's the one who did it oh but she said she didn't and then of course you know maybe she did
something different whatever and then we track it down from there but it's always better to have
more information than not and it's especially important when you have an automated system that
runs without you looking at it and it's really important with mobile applications too because
these things fail without you having you're not able to say you know look at the logs or whatever
you have to like say send me a screenshot or so having crashlytics and analytics even if you don't
check it every day. It's important having those applications, so it was an interesting thing about
Excel. I just - I was fascinated by it, um, you know just from a technical point of view. I mean,
I've been there like I think you know, I hope nobody gets fired over it. But you know,
anyway, interesting story and logging always. As a developer you always have to log things,
you always have to have that manifest, that what's happening and when
so - that when somebody comes back to you says, "How come it's not working?", you can say - you
can either say why it's not working or why, you know, track everything down. Anyway,
just my thoughts for today. I hope you, guys, have a fantastic day! That is it for today! Stay
healthy stay safe. Subscribe, like, all that stuff! And I'll talk to you again next time!