Excel Hell II: If the sickness can't be fixed, it must be contained Three years ago almost to the day, El Reg reported on how Excel had contributed to pandemic chaos. Some 16,000 cases had vanished between labs and the UK health service due to a combination of file format failures, outdated software, and lack of error messages. Now comes a fresh hell. A doctor holding a coronavirus test What …
This is what you get when you try and use an application for something it was never intended to do.
If you want a database, use a database with a data exchange process.
One example I saw of "application abuse" was when I was working on an EU Fourth Framework project that required "CAN data to be provided in CSV format for post analysis".
I logged all the data to a CSV file and shipped it off on a DVD (internet was far too slow in those days).
A few days later I got an email to report that Excel crashed* when importing the file, so it must not be valid.
I replied with "it works with a proper data analysis tool" (we used Matlab) - there were millions of CAN messages in the file, and Excel couldn't cope...
* it may have been that they got bored waiting for it to "load".
It's getting a bit ridiculous if we're now requiring regulation to make a piece of software accept a format that no other spreadsheet uses and which nobody will use in practice anyway. A format which will not solve the problem. SQLite doesn't have the same type constraints that other SQL databases tend to have. This means that, if your spreadsheet mangles some data as you put it in, having SQLite as the backend format won't prevent that from being accepted, stored, and processed to your detriment. Of course, it would also break all the formulas because rows in a table do not update themselves when conditions change, the caller has to do that. You could put the formulas back by putting in a shim which would effectively overlay a typical spreadsheet over a table, in which case you would lose even those few benefits that remained when using SQLite, because now your table would just contain strings that were processed as cells.
SQLite is a good tool for some purposes, but just like spreadsheets, you have to know which purposes those are. This means that you have to know about its type handling and performance constraints (yes, there is also a performance problem trying to use that as a spreadsheet, but I didn't think I should spend another paragraph on it). If you don't, you may end up breaking something which needs a spreadsheet and not a database while simultaneously not fixing the problem where you need a real database frontend, not a spreadsheet which automatically puts things into a database.
I logged all the data to a CSV file and shipped it off on a DVD (internet was far too slow in those days).
On a much smaller dataset I was working supporting a retail establishment. They had anin branch stock management system that was connected to the Head Office back end but required user interaction at the local level. So for example if you returned stock to Head Office or a distribution centre you needed to wait for the email acknowledging that they had received it. Then once you had the email you had to enter the product code and how much of that piece you had returned as per the details on the email. I was introduced to this by the manager of this branch who said would I mind reading the code and number out so she could enter it into the system.
I looked at the email and the attached table in the Word document with interest, whoever had produced the Word one had done so without typing it themselves. So I suggested the stock system might have produced that list and might therefore have an import function. Low and behold it did and once the document and contents were converted I imported the resultant CSV file into the system. We did some spot checks and saved a bunch of work. Manager was very impressed with that because it was a major monthly pain to do the returns. I pointed out someone had obviously spent some time converting the outputted CSV file into a table in the Word document. Probably done like that deliberately to stop people realising it was possible and importing it back in using the import function.
Absolutely, this is a classic case of misuse of the tool rather than the fault of the tool (other than the one behind the keyboard). Excel does not need fixing by MS and isn't the problem. The way it is being used is. Anyone can open the application, create a new workbook, and start creating the next biggest abomination know to mankind.
Excel is no more at fault than a hammer is if I choose to stave someone's head in with it rather than use it to assemble the frame for a house.
Hear, hear!
The remedy is user education and training in data representation and manipulation, including when and why not to use spreadsheets (or the APL programming language) for data projects.
Giving the average undertrained office drone or manager access to a spreadsheet program is like giving a spider monkey a nail gun.
You could argue it does need some fixing as its defaults can cause problems
Trying to be helpful in guestimating data formats is fine, but problem is such "format" mangling is not properly flagged up to the user (cannot expect many users to be "expert" and aware that such data screw ups may occur) and so such faults can easily be missed
https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7#:~:text=The%20spreadsheet%20software%20Microsoft%20Excel,contain%20erroneous%20gene%20name%20conversions.
Sorry, but while I agree with the diagnosis, the proposed solution reminds me of the old rhyme (sung forever in my mind by Burl Ives) of the old woman who swallowed a fly.
What you actually describe is a failure to enforce the data model and the solution for this would be to help users to model the data they want so that this can be enforced and deviations immediately raise errors.
Spreadsheets are great for ad hoc reports but absolutely terrible for storage and workflows, so anything that uses them should remove all processing from Excel, leaving it to be a GUI for looking at tabular data. It's very good at this, which explains its enduring popularity.
From a programmer's perspective we think it should be easy to define the schema, implement whatever workflows are required and forget it. But decades of experience have shown us that many business are unable to do something like define a schema (and let's leave out some of the trickier questions of data types and indices), though they can come agonisingly close. We do now have the tools that give users more expressive power, I'm thinking here of the combination of Jupyter notebooks and Pandas. How about at adding some kind of iterative process allowing them to specify the schema by answering business questions? This allows them to bring their knowledge of the situation to the table rather than hoping ML will spot random anomalies. AI could certainly help suggest questions and drive a graph that would lead to the right kind of schema.
When you had a Data Processing department managing the computing systems, there used to be a whole job type to do this definition of a schema for a set of data, and how to process it. The job title was "Data Analyst", and they were the people who produced the procedural requirements to process data, for the programmers to write to.
I think we've just assumed that they are no longer required, as the tools that are available are so ubiquitous that we can let either the business users or the programmers carry out the function. But this is not really the case.
One of the jobs of the data analyst was to talk to the business users in as close a language as they would understand to try to distil the essence of the job from them, and then design the processes and write the requirements in the way that the programmers would understand to be able to write it correctly. It was a skill in it's own right, needing good understanding of the jobs on both sides, but not the full details of either.
With the advent of the PC and tools like Excel, it was assumed that the business users themselves could actually write data transforming processes, without actually understanding the nitty gritty of how the tools worked. And for simple problems, that was fine. But as soon as you try to use this data to feed into other processes, especially processes wirtten by other business users, what we see now is the result. And it's really not the case that you can just hand the task off to programmers. Good programmers can often have quite quirky mindsets, it helps them do the seemingly impossible. But it does not mean that they do or even can see enough of the picture to be able to understand the business case.
These are generalisations, of course. There will be exceptions, business users who can take into account how the machines work, and programmers who invest in a business process to understand it (these are the people who would have become Data Analysts, it was regarded as a step up from programming), but the normal people in either environment just don't hack it.
I feel that Agile and Dev. Ops and other similar tools have contributed to this role disappearing
I was never fond of Data Analysts (I had bad experiences in my first job with DAs who had become hidebound and completely ignored new methods of working that could have simplified the workflow), but I am prepared to admit that they fulfilled a vital niche, one that is still required but missing in so many environments.
Analyst/programmer was also a job title. I think it's what's needed now. But the CIO wants to be seen in charge of big projects - those are what justify the big salary and having a few A/Ps who can work individually with user departments doesn't fit into that world view. But the A/P can do a lot to solve the coal-face problems that can contribute a lot to the business, perhaps as much as or even more than the big shiny project. In fact you can see they CIOs aren't going to let that happen.
The role your describing, interpretation of business needs to the technical staff that has to implement them, sounds like an on-site customer or product manager. So I don't think that that role has disappeared, but that the siren song of Excel has convinced a lot of business users that they don't need all that discussion and rigamarole. They can just type what they want into a cell and it will work, no need to talk to IT.
At night, you can hear a blank Workbook whispering "It's just simple math, you don't need a QA process. You don't need a dedicated application. Put all your data in me. Do it now, before someone else hears! Think of the savings!"
"How about at adding some kind of iterative process allowing them to specify the schema by answering business questions? This allows them to bring their knowledge of the situation to the table rather than hoping ML will spot random anomalies. AI could certainly help suggest questions and drive a graph that would lead to the right kind of schema."
An approach might be to have a user enter some specimen data into a tabular interface. The system would then attempt to interpret the data and ask questions for clarification (is this a date? No What is it then?Name of a gene Should it be treated as text?Yes). Also check whether the data is entered column-wise or row-wise.
However a good start might simply be to require that every column (or row if appropriate) has a title and that on attempting to merge enforce a check that the titles match.
In a previous job I had P/A responsibilities, speaking with customers about how their data was encoded, and writing programs and scripts to read their old data from mainframes and minicomputers, process it, and import it into our PC-based fund-raising management app. I recall one client in particular with which I had had many meetings and conference calls over three or four weeks to extract their data encoding method. (Their minicomputer app was limited, but provided some user-defined character fields. The clients abused these to make up for the app's inflexibility.)
The staff sometimes would argue over the meanings of the codings. ("No, that's wrong. When UF1 is a '#', and UF3 is an '8', it means ...") There was no documentation of any of this, just peoples' verbal brain-dumps.
I got it done, somehow, correctly (the clients did visual comparisons of old-system and new-system printouts of however many donor records, and signed off on it), but there's no way any fake-AI/ML system would correctly derive the conversion specifications in a situation like that.
(Icon for using SED, AWK, C, and SNOBOL)
Jupyter notebooks and Pandas are great until you have to: 1) explain to the average desk jockey how to use them, and 2) replicate the environment being used.
Excel is simple, reasonably powerful, and everywhere in the enterprise (and most offices in general for that matter). It is easy and familiar. It has wizards for charts etc. The price to be paid for that is that its flexibility does not confine stupidity in any way. Naturally, it is always this unconstrained stupidity we hear about not the examples where it works perfectly - and I've been witness to many of those in financial environments (both).
"We do now have the tools that give users more expressive power, I'm thinking here of the combination of Jupyter notebooks and Pandas."
In my experience, these create the same problems that Excel has, just in a slightly different form and only for programmers. That's the case because nonprogrammers generally don't know how to use Jupyter, and even if you can teach them, they'll only learn to run existing ones. But for programmers, effectively you've given them a way for anyone to have their quick and dirty scripts. All of us have some, and they often have some deficiencies like no documentation or a particular input format without which they won't work. This is why all my temporary scripts are on my computer and my backup directory, but not anywhere where my colleagues can get them. If I'm releasing them for anyone to use, they need to be higher quality. If other people need to run them, then I'd better get started on that.
In the one environment where Jupyter was used as a production tool, we had lots of scripts that were supposed to be run. One, for example, would take an input XML file and populate a database. As long as you read the code first, you would be fine. If you didn't and uploaded a file you didn't want to, it would not fail well. It was not idempotent and would cheerfully clobber the database if you uploaded the same file multiple times. It did not do validity checking on most of the data in there. It didn't even preread the file to make sure it all worked before it started firing off SQL statements, and it didn't even have the courtesy to do this stuff in a single transaction that could be rolled back. In short, it had all the same fragility of spreadsheets but in the form of code. A problem with the script or an edit made by someone who shouldn't have been editing could easily have broken things.
While the actual failures weren't so catastrophic, I do remember the time when I ran an analysis script which returned spurious data, only to be told that I shouldn't have run the 2.0 script, I should be running the 1.5 script (nothing said this, so I had gone with the latest from version numbers). Then, when the 1.5 script also produced incorrect results, they mentioned that I had to run a script in a different folder, also labeled as 1.5, which had been forked months ago and was now the canonical one. To be clear, the scripts I was running were in a directory called "analysis", and the script I was supposed to run was under a directory named something like "dev/colleague_username/temp". The colleague in question had left the company. Is this the fault of Jupyter's products? Clearly, no. It was the fault of the process that made running a script like that the expected procedure. The remedy is the same, though. The process, not the tool, is the problem. Changing the tool is probably necessary, but if the process didn't change, the problem probably will stay around afterward.
The only problem is, manglement would then suggest what to ask the AI, reducing Artificial Intelligence into Artificial Idiocy by using the wrong questions and creating wildly incorrect schema, as history of custom business application development has well proved.
The comparison to biology is interest, but the author forgot to add a note about the Sept-9 and a few other genes which Excel mangels. The end result of this was the genes being renamed.
This is the problem of the inexperienced user. Experienced ones know Excel will f*ck up try to smartly interpret data you enter for typing purposes, especially where it can be construed to be a date. It is for this reason you can start a cell entry with the ' character to let it know "this is what I meant to type, do not f*ck with it"
"This allows them to bring their knowledge of the situation to the table rather than hoping ML will spot random anomalies. AI could certainly help suggest questions and drive a graph that would lead to the right kind of schema."
Sounds a lot like the "expert systems" that were a focus of the previous AI vogue in the 1980-90s.
I suspect some organization's "business rules" aren't entirely rational or internally consistent (not just X/Twitter.) Humans are pretty good at coping with this nonsense. Indeed the typical social media user believes far more than the Red Queen's six impossible things before breakfast.
Yes, I don't think I'm suggesting anything really new but I would argue that the tools now available are better at helping non-technical users through the discovery process: ML models that "understand" data structures from descriptions combined with generative ones for more probing; essentially imitating the interview process but allowing the user to set the pace.
For reliable data processing of the sort discussed in the article, the schema must be rigid. So the kind of human detection/intervention you suggest only comes into place when data is provided that doesn't fit. Now, it could then very easily be down to transposed columns that can be fixed just as easily; and perhaps a monitoring system could make the suggestion. But the important thing is that the data wouldn't be imported automatically. I.e. this is about combining ML and human approaches effectively while avoiding the mistakes of trying to automate everything.
I can't really agree. A generative AI can ask more questions and probably parse out the answers a bit better than previous generations, but it's still going to get hung up on the administrative problems that make Excel the process. It would also have been possible for companies to embrace one of the "low code" systems that allow some nontechnical people to set up a database and some forms and pipelines that can work with that. That mostly didn't get adopted, because such systems typically have a few problems that nobody, not even a programmer, can properly fix while not being quite as easy to use as the sales team said it would be. The AI software won't make that any easier, because it will still need to do such things as ensuring there is a server for all the automation and that there is a single point of truth for the data concerned. I don't think any new techniques available to us will fix our problem because they're really no better than what we already had. We have tools that work when people use them properly, and the problems come because some choose to use the wrong tool, not because the right tool isn't available.
So do good workers who are forced to use their inflexibly thinking employers tools; you know, the employer without which they would starve or spend ages looking for work. The quote comes from an age when most artesans were self employed; nowadays, in our complex society, the bar to self employment and the risks of doing so are much higher; thus most artesans are also wages slaves and do not always get to choose the tools.
A bad workman may blame his tools, but a good workman also does if they have bad tools. It doesn't automatically follow that he who blames his tools is a bad workman. If you disagree, I'm willing to have you do your job with the worst tools out there and see how long it takes you to blame them. If you work in IT, you will be setting up a full corporate network, including user PCs and servers, networking, and security using only MS-DOS, the original version from Microsoft though you can use the latest version. If your a programmer, reimplement every line of code in your projects with a Turing machine, and you can talk to the MS-DOS guy if you need two Turing machines to talk to one another, but synchronizing the one tape each for input and output that each gets to use yourself. You'll soon be blaming those tools for why you haven't gotten anything useful done.
Before I retired, I worked in a large public sector finance office, that paid fees and expenses to thousands of individuals each month. The payroll process consisted of emailing Excel spreadsheets monthly to the outsourced payroll provider. The emailed spreadsheet was made by copying over rows & columns from individual spreadsheets maintained by different sub teams. I was responsible for the final upload. One month I was on leave and came back to turmoil. My line manager, the office leader, who was always banging on about paying attention to what you are doing, had managed to paste the amounts payable to each of 6,000 people one row below where they should have been. Everybody got the amount due to the person above them on the sheet. Not to mention the tax and NI. What fun. How do you think the matter was fixed? Yep, another spreadsheet!
That's true, but it's still that the negative uses of Excel could be dead if we just took this simple, well relatively simple anyway, step. We all know that won't happen. It's not really about Excel's irritating type management capabilities. Those really don't help, but lots of spreadsheets manage to avoid having any numbers treated as dates and can still manage to completely screw up a process. An AI to detect the most common of problems is not likely to solve the problem of processes that use a tool with no error checking because the tool doesn't collect enough information to do error checking. The same problem happens when someone makes a database that doesn't check what they're throwing into it, since a database will make sure that the "how much to pay this employee today" column is a number but doesn't see a problem that it currently says 2147483647.
I sort of hope I never live to see flying cars. People have a hard enough time negotiating two dimensions with current cars, so adding in that third dimension....
Saw a photo of a sign that I think kind of sums it up: Before worrying about artificial intelligence, how about we address natural stupidity?
"I sort of hope I never live to see flying cars."
Excel doesn't know what data in an all-purpose freeform grid actually means, therefore errors are easy to make and commonplace. So, after a rather long winded metaphor comparing fixing user errors and bugs to cancer treatment, AI is proposed as a vague solution. But AI also doesn't know what data means, the best it could possibly do is to hazard a guess from comparing similar column names in different spreadsheets and then copying over formulas in an act of hope that "this might work, possibly". This will then require users to manually check if the resulting output is correct, which will probably require another spreadsheet. And if AI is used for that one, even more errors might creep in, ad infinitum.
a. “a sexy way to do this with an Excel-based process would be to train an AI with historical data and ask it to alert on deviation.”
b. “An even better way would have the system learning the data collation methods well enough to spot row or column transpositions.”
c. An even better way would be to not use a spreadsheet to do medical analysis.
"Here is yet another bodge workaround."
As ANYONE with any real world experience will tell you, use the right bloody tool in first place
Yes I can use an adjustable spanner, or mole grips to keep tightening the nut, or I can hammer a screw in, or use Excel to do a job it was never made for, but in the end it WILL go wrong.
The author points out several good examples, but then misses the forest for the trees with the conclusion. The problem here isn't Excel, it's that people keep trying to mash various shaped pegs into the round hole that is Excel. "If I just pound hard enough, I can get this square peg to fit, damnit!"
My current manager is an excellent (happy coincidence) example. I used Excel to prototype a quick and dirty data collection form, and then because Excel makes it such a PITA to do updates after a certain point, I try to move it to Access. Because, you know, data collection apps is one of the three main use cases for Access, and not one of Excel's strong-points. Manager blows a damn fuse and refuses to even entertain the idea that Access might be better for this particular situation. They like Excel, and don't like Access, so I'm going to use Excel, end of story. Doesn't matter that it takes me 2-3X longer to make simple changes, or even that my "customers" seem to like the Access version better; Access touched them in a bad place or something so they refuse to even entertain the idea.
Sometimes it's a case of what I'm experiencing now, and someone above you has an irrational aversion to the proper tool. Sometimes it's like a situation I faced at a job some years back, where I had to cobble together some reporting tools using VBA because that was literally the only option available. I'm sure there are other scenarios as well, but the thing they all have in common is that the wrong tool is being used for the job, and then people sit around genuinely confused as to why it blows up in their face. Sort of like the author of this article.
You're almost there, but you've missed one crucial link in the reasoning.
"People keep trying to mash various shaped pegs into the round hole that is Excel" - this much is true, and well put. What you're missing, however, is that this keeps happening, despite the ready availability of any number of otherly-shaped holes they could be using.
It follows that any solution of the form "we should stop doing this" qualifies as "well, duh" advice. Sure we should, much as we should stop eating meat, but we won't. So the question "what can we do to prevent/control/mitigate the resulting damage?" is a good one. I don't think the article comes up with any very good answers, mind, but I can respect the attempt.
"If I just pound hard enough, I can get this square peg to fit, damnit!"
Many years ago a friend had worked at Shorts in Belfast (the aircraft manufacturers). Someone asked him to create a square hole throgh a piece of wood - I suppose it was far too long for chisels or morticing machines. After whoever it was had gone he drilled a round pilot hole, then took a square section bar and hammered it through. The square section bar and hammer were the right tools for the job.
Someone screwed up with how they merged two datasets, this has absolutely NOTHING to do with the software they were using.
Given this was once again a *manual* data merge with once again little or NO oversite, or verification, then exactly the same fault could have happened regardless the software tools used (and I include in that pen and paper)
Indeed this particular error was down to methodology, and not scaling rankings, the previous error caused by (IIRC a size limit being exceeded)
Both cases appear to have been a function of asking someone who doesn't understand statistics, to merge datasets from multiple sources.
The person in question person dutifully did what was asked of them, but failed either because they didn't understand the data themselves and so couldn't determine that they were getting implausible results. Or they because they were incompetent and didn't even attempt to validate their results.
Either way, nobody appears to have checked the results before using them.
This is plain and simply PBKAC.
Now it could be argued that Excel should tell you if you exceed the maximum number of rows (or columns) in sheet. But in this case if you ask Excel (or for that matter any software) to do some specific maths it can hardly be blamed when the maths the user asked it to do was incorrect?
You then go on to argue that AI will save us from this mess.
How do you expect that to happen? If people can't produce a correct spreadsheet in the first place all you are going to do is train up your AI to make exactly the SAME mistakes, because you are going to give your AI system the same broken spreadsheets to learn from. Worse than that you are then going to sell this flawed system to other people with the claim that it isn't going to make the stupid mistakes that dumb humans do...
To be fair, if you show up to a construction site for a new house with a set of precision screwdrivers for working on jewelry and no other tools... You can probably manage to figure something out if you're sufficiently clever, but it's generally better if you show up with tools that are going to be more useful, like a hammer and Philips #2 screwdriver.
Also, Excel will tell you if you exceed the roughly 1.05 million rows you can have in any given sheet... on the 64-bit version.
That all said, I agree with the overall thrust of your comment. It's like the author showed up for their first day on the job as a construction worker with nothing but a swiss army knife and instead of blaming their choice of incorrect tool choice for everyone yelling at them for being slow, they decide it must be a problem with the screws.
I've been on IT installation jobs where I've been the only person who turned up with any (hardware) tools. Often several days in succession! You'd think that on finding on Monday that they need a screwdriver, on Tuesday they'd turn up with one? And Wednesday, Thursday and Friday? Or if they borrowed a box spanner from their polite colleague who HAD brought tools to site, they WOULDN'T CHEW THE HEAD OFF INTO UNUSABILITY BY NOT USING IT PROPERLY!
Sorry, that learned me, I never lend out my tools now.
That got a laugh out of me. Never been in quite that situation, but I do have a housemate who's a nice enough person, just rather inconsiderate and chronically ill-prepared. Like one time I was giving them a ride to the train station so they could go visit their son. No problem, not very far and I had nothing else pressing going on. Get to the station, and suddenly they remember they don't have a mask, so ask if I have any. I had a bunch in the back of my car. There were multiple little bags in a box, each bag had like 10-masks in it. One of them was already open because I'd taken 2-3 of the masks out. They went and opened up a whole new bag and took masks from there. It's sort of like, "Seriously!? Not only am I giving you a ride, I'm giving you a couple of masks because your lazy ass didn't think ahead, and that's not good enough for you? You're too good to take a couple of masks that have obviously never been used, just sitting in the back of my car in the box. You have to open up a whole new bag and take masks from there!?" Again, they're a nice enough person, and I have nothing against them except that they can be a bit on the inconsiderate side in several minor ways.
Actually, now that I think about it... Once upon a time I worked as a hardware tech for a retail chain. One time I went on vacation for a couple of days, so they had someone else work on units while I was out. No big deal, except when I got back like 3-4 bits from my screwdriver set were missing. Not sure if they were just floating around on the floor somewhere or the person they brought in stole whichever bits they were missing. Why they were using my tools to begin with is something I still can't figure out.
Both cases appear to have been a function of asking someone who doesn't understand statistics, to merge datasets from multiple sources.
Nope. Both cases are due to management decisions brought about by running IT on a shoe-string budget (another management decision).
Everything gets shoehorned into Office no matter whether it's appropriate or not.
I've said this already a few times in this thread. You don't have to treat everything as a big project. Deal with things at the right scale with the right tools and the right people.
If HMRC hadn't bolloxed up the freelancing profession the appropriate route here and in very many other cases would have been to engage a freelancer to set something up.
"Someone screwed up with how they merged two datasets, this has absolutely NOTHING to do with the software they were using."
If the spreadsheet had named and/or typed columns it could have warned of a mismatch. Mishandling of data in one way or another is s common post-mortem finding of spreadsheet-based disasters. The situation is common enough by now that spreadsheet wuthors should be aware of it and look at the possibility of adding typing and constraints as an option. That wouldn't, of course, prevent users failing to apply them but users can't be blamed for the option not being there.
You assume the "authors" weren't users tasked with "knocking something up in five minutes"
One of the more interesting things about dissecting this kind of mess is that you can see the thinking processes of the people involved and that in itself gives pointers to avoiding repeats
For a real horror story, how about the time a "director" of a DVLA equivalent attempted to dictate that the replacement for an aging national vehicle registration system running on an old mainframe (well past EOL) would be run on Excel? The same guy tried to email 80MB files over 2400bps modem links and complained when they didn't go through
He also swore black and blue that personalised plates would never happen and the programmers were NOT to include provision for them. 14 months later the government announced personalised plates would be rolled out within 12 months (the first the department knew of it was the TV announcement)
Thankfully for his skin the programmers had ignored him. He got his summaries in Excel but the backend engine was more situated to the task and they'd made provision for personalised plates plus all the other stuff we take for granted now (insurance lookups, etc etc) but didn't exist back then...
I think that cancer is a really poor metaphor for Excel problems. Okay, both are actually a large set of diverse problems, and... that's about it?
And then the solution is AI? Which is not a cancer therapy, nor does it look like a metaphor for one?
Is the only connection the fact that we're talking specifically about healthcare-related Excel problems, and therefore cancer?
I work with a nice modern implementation language that has all sorts of cool features for defining types and representing real-world concepts. But if I had a pound for each time a supposedly highly trained software engineer just jams everything into a formatted string I wouldn't be writing this. Most developers are incapable of thinking in terms of a domain model and fail to grok the need to map input and output to and from that. They might as well be using Excel, or BASIC.
The other side of the problem is that most end users/clients have a very poor understanding of their own business domain and the rules it should follow. They think that application==domain model. Whenever you hear an application name being used as a verb you know you've got one of those.
As in "play Nintendo". WTF is "playing Nintendo"? You don't play "Nintendo", you play GTA or Fifa22 or Subbuteo or PacMan *WITH* the ****ing Nintendo.
FGS, you don't play "cricket bat", you don't play "racket", you don't play "coins", you play *WITH* a cricket bat, you play *WITH* a racket, you play *WITH* coins.
Excel stopped being a useful little tool when Microsoft attempted to make it more useful, by second guessing and overriding the user.
Every little tweak to Excel threw up ever more disastrous outcomes, and the real bugbear was many of those tweaks could not be turned off or customised.
One benefit of Microsoft's hole digging exercise, is that my VB skills have had to improve to help make the sheets immune to the sewer of shite that flows out of Redmond's blue sky (more like black hole) development team.
Has it become an email client yet? :)
I thought the whoe reason for the issues with the career limiting spreadsheet was that they standardized on an excel workbook and vlookups from them but they never created and required a standard template. Each submitter had their own versions and some had added columns that were unexpected.
The problem in this case isn't excel, its the expected data source doesnt match the the expected model.
Am I missing something?
True but then that's the nature of Excel. A database can handle missing or extra columns as long as they are correctly named. If you send out a load of blank Excel files asking people to put their data in them, you'll get them back modified and incompatible with your master spreadsheet.
"True but then that's the nature of Excel. A database can handle missing or extra columns as long as they are correctly named."
No, a database cannot handle most of the types of misconfiguration that would come from not agreeing on the schema first. Insert columns firstname and lastname into a table that has one name column. Your statement is rejected. Merge two tables containing ID numbers and pay information, but one of them specifies pay as an hourly amount and one as a yearly amount. Someone is going to get the wrong amount when the winning code retrieves data from that. Merge ID numbers from a database where they were integers and one where they were strings of digits. Probably anybody with an ID starting with 0 will have some problems. At best, if you're using one of the less structured NoSQL databases, you can keep around the custom fields when you imported something with extra data in it and still retrieve them later, but it still won't help when you want to search on it later. Databases do more checking of what you're doing, but that doesn't prevent you shooting yourself in the foot.
And create multiple models that are trained on past data sets and hope they are good at identifying anomalous results and then hope the foundational models are sufficient to do so for other use cases without having to start from scratch, or... you could just have the business unit talk to IT?
Clearly they have business rules they can articulate, and those rules can be implemented into software that can be tested, delivered, and iterated on. Then, instead of having an ML model to babysit, you have a functional bit of software that does what its users need.
This is a wild new paradigm I am advocating for, I know, but I think it's worth a shot!
The UI bugs in recent incarnations are unusually infuriating. Cursor offset randomly by 3 inches? What's all that about?
Screens not updating on scroll, or even on forced refresh?
And bugs from 2003 still persist in pivot table totals.
I have lierally had to pull the plug on multi million pound applications because of excel hell causing chaos in the former. (Badly designed I/O to depend on Excel).
The IT guys in the biz are currently besotted with cloudy rubbish like Tableau, but unwilling to get enough or renew licenses. And I can't get my own server to dump stuff into a proper engine, at least not officially.
My own instances of MySQL and SQLLite, then, will have to suffice...
Once is a chance. Twice is enemy action.
The actual quotation is from the novel Goldfinger by Ian Fleming, later made into a film. It has the character Auric Goldfinger saying to James Bond:
Mr Bond, they have a saying in Chicago: "Once is happenstance. Twice is coincidence. The third time it's enemy action."
Then again, the novel was published in 1959, so slightly aged. The film (movie) was released in 1964.
Perhaps our American readership can confirm if such a saying was current in Chicago in the 1950s? Quote Investigator doesn't appear to have it.
NN
I wasn't around then, but the quote sounds like it was intended to be from the point of view of a criminal, as Chicago had been a city with a lot of organized crime groups in it. So if it was a real expression from the time, it's likely not to be an expression common to people from the city, but to criminals. Then again, I didn't read that particular book, so I am just guessing that context exists.
I've got the skills to do a lot of that. Sadly it all falls down on: who's gonna pay me? Being alive costs money, in order to be able to afford to stay alive I need to be paid for my labour. I could do it as an unpaid hobby, but again: who's gonna pay me to be able to afford to do this unpaid hobby?
Kill Macros and you kill off half the spreadsheets still hanging around from donkey’s years ago that are trying to do the job that databases should do.
Maybe bring back Access but “in the cloud”. It’s not the full answer but it would be a start.
Funny lately I’m trying a bunch of no/low code databases with interfaces to replace some archaic, yet clever, spreadsheets with macros and forms. And many of these products just “don’t get it” or are too complex.
Is data entry and reporting really that hard these days? Maybe there’s a gap in the market here.
The MS Excel spreadsheet is just a metaphor for the traditional big sheet of paper, ruled with rows and columns and folded down the middle, that has been a tool of accountants and auditors since the beginning of time. Numbers were written and calculations were made by hand. I'm old enough to have used them extensively. These manual spreadsheets were also prone to misuse and errors. There's nothing new under the sun. I could tell some real horror stories about MS Excel from the days when I did 2nd line support for ERP software. Customers would complain that the data they extracted from the ERP system was all wrong when, in fact, they mangled the extracted data in their spreadsheets. My company didn't support MS Excel (you have to call Redmond for that, haha) but every now and then I had to go over to a client for some handholding, because they couldn't be convinced otherwise that the ERP system was not to blame. Now, of course, these customers messed up in MS Excel, but most times I also had to remind them of the rudimentary principles of accounting. Having no clear picture of what you are doing, that's where the silly tinkering starts.
does not matter if one uses spreadsheets or DB if the data types are not common. Worked on a site where oracle gave inconsistent results sometimes. Digging into table definitions showed indexes and a few columns were different on multiple tables, long -char, integer,alpha-numeric ITIRC. Long time ago. This is the great advantage of older COBOL. The DATA section forced programmers to understand the data types in use and formally declare them. The cynic in me thinks that an old spreadsheet that did not "interpret" any entered data might be more failsafe. Since AI has no understanding of whats it is doing, I cant see it doing anything more than creating further PHB insistence that obviously erroneous figures are correct because the computer said so.
Some company will replace its programmers by AI, and find that unfortunately AI hallucinations have created backdoors, vulnerabilities, incorrect billing and payments to scammers. The created code is unintelligible, except by another AI. Customers will be lost, due to the directive "zero unsatisfied customers". No methods will be available to contact the company "minimise response time".
This is the most ridiculous article I have read on The Register.
It's a collection of assertions with no evidence, criticising a very good tool. Come on tell us the full details of the botched data transfer. Even if it wasn't human error it's unlikely there's an application anywhere of that complexity without bugs left hiding somewhere. Then we get to virtue signalling about doctors. People, of which a great many just have a good memory for following protocols because the system bends all except the strongest into unthinking robots. But don't worry most will be replaced by AI, ultimately to our detriment because that AI will soon get trained to further the financial benefit of a few.
AI could be the greatest tool ever, if only humans were perfect moral creatures.
Using Excel as a medium to transfer data to another organization has always left me feeling uneasy. Unfortunately, one company I used to assist with computer support had to provide data to insurance companies, their payroll company, HR support company, and state agencies. The spreadsheets were crafted by the outside organizations, often had errors in the computing fields, and sometimes made it easy for the end-user to accidentally damage computational cells. I especially disliked the sheets that required macros be enabled. I always felt that these agencies should provide a program where the data can be input into a local database that could then export the data into a CSV file (or some other appropriate format) that could then be sent to the requesting agency to load into their database, rather than extracting from spreadsheets. But, that would require hiring database management professionals.
Lotus Notes was the tool that people now use Excel for. If you want to quickly gather and organise data then create a Lotus Notes DB and email the link to your people. They can fill in the form you created and the data is all nicely arranged in your central database. Do that with Excel and you will have big problems.
The spreadsheet was re-invented with Lotus Improv. It was brilliant, but it died.
Not sure whether it was just too different that nobody wanted to relearn a new spreadsheet paradigm, or whether Lotus salesbods didn't want to eat into their 1-2-3 sales. They thought they were the incumbent with 1-2-3 & didn't see the Excel juggernaut bearing down on them. If they had stuck with Improv and given it some more love, they might have stayed the incumbent.
I mean yeah, spreadsheets are rarely used correctly, but the prevalent answer of "GeT Ai To dO It" is a shitty one. There isn't any "AI," it's just fancy auto-correct, and if you have to fact-check it because YOU ALREADY KNOW it get's things wrong, how is that an improvement? Do we need more AIs to check other AI's output? It's AI's all the way down, man...
Most complex business processes done in Excel would be better done in MS Access. However my clients have mostly not been computer literate enough to do these in a database. Excel is quite a good tool for business people to prototype what they want before I come along and turn it into a database.
MS Access is pretty easy to use but there are plenty of hurdles that the Excel user finds too challenging. If MS did some really good development work on the Access user interface Excel users could be creating competent business specific database applications.
I suspect there is a conspiracy against such a thing. Years ago Lotus had Lotus Notes which was a far better database generator that allowed the boss's personal assistant to knock up a database application in the morning and have data coming back by the afternoon from those emailed the link. That was 25 years ago but they killed Notes and replaced it with MS Office.
The contemporary equivalent of that is the secretary creates a spreadsheet template and emails it to people asking them to send it back filled in. She will then use copy and paste to merge these individual sheets into her main one. A time consuming and error prone task when the users can add and delete columns to their liking.
i have a really great idea, why don't we just get AI to solve all the world's problems? i mean how hard can it be? If it is hard we can just ask AI to design a better AI and solve it. Has the author of this piece ever actually worked with real data in a niche area? The stuff where CHAT-GPT would be 90% hallucination and DALL-E doesn't have a clue? what the 'ology' is about? Though in the case in question the obvious issue is not the Excel error - because hey, mistakes happen, but just the fact that no one eyeballed the output and asked the question -' does this look sensible?'
When I did my MSc back in 2003, I was contemplating on making my thesis: 'How the use of spreadsheets and QBE databases has destroyed the productivity of businesses,' I change it to mundane SCADA subject instead when I discovered, from initial interviews, that nobody would actually admit the truth!
That sounds like you might have had a predetermined conclusion there, which may not be the best way to write a thesis. Not that I necessarily disagree that Excel is harmful, having made that point in multiple comments in this topic alone. Still, you have to honestly compare it to what they would have, or were, using, not what the computer could do for them. Doing a process manually on paper, which was often used before the spreadsheets came along, wasn't necessarily any better. This is especially true if they were manually performing any calculations as they did it, since it's very easy to enter the wrong number when you're copying over from a calculator, let alone doing the calculation by hand. It's possible that spreadsheets didn't degrade productivity, but simply allowed it to continue to be flawed when there was a better option available. It's even possible that it concentrated the problem, making many processes more reliable but making the rarer failures bigger and more obvious. I don't know which, as I didn't do the research, but that is possible.
It's been documented several times that office productivity levels have declined considerably since the 1960s and very sharply since the introduction of computers
On the other hand I operated a company in the 1990s with a staff of 3 (one FT office person) which would have needed at least 5 people in the 1970s-80s just to handle accounts receivable/payable, so it appears the real solution is to automate the jobs out of existence
Hey ChatGPT, write a letter to my clients
I worked for an R&D organization with a staff of about 1000 scientists. Management had the bright idea of capturing all the information that might be useful to them in a gigantic Excel spreadsheet stored on a server in HQ that was accessed via Microsoft Remote Desktop. Among other things, we were supposed to to rely on it for financial data, but this was never consistent with what the financial and procurement systems told us, Many scientists wasted a lot of their time trying to make sense of these numbers on the naive assumption that it would make a difference (or was even possible). Moreover, non numerical information (e.g., project aims, status and milestones) also had little relation to reality, either because project leaders hadn't got around to updating their project information or because it had been crafted to give a positive impression. I figured it must have been the world's largest and most useless spread sheet.
"Among other things, we were supposed to to rely on it for financial data, but this was never consistent with what the financial and procurement systems told us, "
I ca show you a Russel Group University whose (eye wateringly expensive) Oracle systems can achieve that too - along with being nearly impossible to use without 6 weeks training because nothing is where you'd think it might be
Clusterfornications aren't unique to Excel, but they're certainly easier to achieve
This all falls back to DevOps not doing their job properly.
Excel is a powerful tool and int he right hands it is a great tool.
But you cannot have the wild west of users building spreadsheets that will feed data back into the primary business systems. That must be tightly controlled.
You can feed data into Excel that allows manager to manipulate any way they want but you don't allow them to feed that mangled data back in.
Any data interchange must be tightly controlled by the DevOps team.
"But you cannot have the wild west of users building spreadsheets that will feed data back into the primary business systems. That must be tightly controlled"
Bwahahahahahahahahahahahaaaa
As soon as you try, you'll have a geurilla IT system running that you don't know about adding to your woes
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
