Excel and Track & Trace

The UK’s world class “Track & Trace” application “lost” 16,000 cases for over a week, as reported in the Register. Plenty of people have decided to comment and so I thought I’d join in and posted my thoughts in a linkedin blog, although I start this post with a quote from the Register.

The howls of disbelieving, horrified laughter caused by the news of the latest pandemic data cock-up yesterday were well deserved.

16,000 cases lost – purportedly in a blunder involving CSV data, row limits, and an out-of-date Excel file format? In a multibillion-pound, “world-beating” contact-tracing system? Unnoticed for a week of rising infection? In a system known to be broken for months but still not fixed?

Ridicule and despair, those shagged-out nags of our Johnsonian apocalypse, once again trudged exhaustedly across the plaguelands of England.

Rupeet Goodwins, The Register – 6th Oct 2020

Much has been made of the fact that the “dashboard” seems to have been implemented in an old version of Excel which has significant element array limitations and as pointed out to me by the Register, significant calculation errors which may lead to error program logic processing. This article talks a bit about why such decisions might be made and also how to perform good architectural practice and good program deployment and thus what might have been missing. It’s unlikely that such a mistake won’t be repeated, the people at the top, have not been through the painful process of failing in this way and paid a price, unlike many businesses. Once again, we know how to do this properly, not doing so is a choice based on ignorance or greed.

Any organisation of size needs an IT architecture plan. This maximises the opportunity for systems interoperability, compliance measurement within the supply chain, future scalability and cost control of both acquisition and support. Most architectural plans will include a spreadsheet as a desktop/user tool. Excel is the obvious and most popular tool, but reasons for worry, are expressed in their usual robust style, in this article, “Excel Hell: It’s not just blame for pandemic pandemonium being spread between the sheets”, from the Register, who also argue that Excel has no role in regulatory compliance software. However, at times Excel Basic has been the most popular development language in the world, and today, it still encourages a [structured] data driven analysis  but it is poor for many to many relationships, graph semantics and list handling and MS are rebundling the tools, to charge more, for instance by issuing advanced functionality and new languages in Power BI. Using Excel is ubiquitous, and it fascinated me that this week, an article in efinacialcareers, bemoaned the lack of KDB & Q skills, identifying a lack of industry training as the cause because Universities won’t touch it because of the licencing costs, to be followed by a TES scoop about the end of the Union Learning Fund as the Govt. withdraws support, but just because it’s there, doesn’t mean you should use it.

Apart from architectural governance, the final piece of the jigsaw is the software development life cycle. A lot of effort has gone into understanding how to do this well with much written about requirements management of functional and non-functional qualities, testing of functional and non-functional qualities and release management. The processes of vendor and vulnerability management are also important parts of ensuring software does what’s needed safely.

Again, we, i.e. the industry know how to do this well, and even then there will be bugs.