'Please download in Microsoft Excel': Meet the tech set to monitor IT performance across central UK government The UK's Cabinet Office has launched a new approach designed to assess the IT resource needs of central government departments and measure their performance: emailing a spreadsheet and asking for multiple replies. In a letter seen by The Register, Joanna Davinson, executive director at the Central Digital and Data Office (CDDO …
Spreadsheets are ideal for data. But it is a pity they could not use a generic spreadsheet that will work equally with Libre Office, Google Office, Softmaker Office, ONLYOFFICE, or even good old Gnumeric.
When I was claiming Gift Aid tax rebates for a small charity, I downloaded the appropriate spreadsheet from HMRC. There was one version for Excel and one for Libre Office Calc. Other spreadsheets were unsupported. The problem seems to be the manic desire to incorporate macros within spreadsheets.
One hopes there is no GDPR / Confidential data in it otherwise it will be spread faster than Chlamydia!
Doing migrations I love data from spreadsheets, the user will change the layout a couple of days before go live, multiple copies will be sent and of course there are technical issues with data and conversion.
10-15 years ago I was replacing such things with SharePoint and similar. Coding took a few days the main problem was documenting the rules that the users made up as they went along.
Excel, sadly doesn't with most users.
You want the data from some people to be put in a spreadsheet or database. Use a form. Your participants fill in data, you get one output in a format of your choice. Most organisations large organisations have a self-hosed form application or deal with a big provider. If not the European Commission has a really well secured one with 2FA enforced that they let anyone use.
They aren't targeting general users with this one. And given that it isn't a spreadsheet but an Excel Workbook, the "the manic desire to incorporate macros within spreadsheets" will be in full force.
A well crafted workbook is significantly cheaper and easier to implement than a database application which captures the same data.
Plus, while email might not be the most secure, an outward facing database application is a juicy target vector for hackers.
"A well crafted workbook is significantly cheaper and easier to implement than a database"
I agree, so easy for anyone to knock something up quickly in Excel, especially when your boss wants it done and deployed by the end of the day. Problem is, you then give everyone access to those spreadsheets (because you want them to enter their data, right?) and anyone can bring to bear the full data mangling capabilities of Excel. Before you know it, someone's sorted a data column or something, or not filtered with the correct formatting and the whole thing is falling over.
Easy solution, you restrict features and lock it down, so you spend ages checking and rechecking that you've locked down just what you need to and not what you don't to keep it working. All the while knowing full well that the brains you work with merely see a locked spreadsheet as an intellectual challenge and there are many more brains than yours to take on that challenge!
That's just my experience.
The time and effort you need to go to to produce this "well-crafted workbook" is likely to be approaching the time and effort needed to do it properly in the first place. In doing it properly you end up with something that's easier to understand, maintain, evolve and is going to be much more flexible and standards compliant.
My customer used Lotus Notes back in the 1990's. When the boss needed to collect some data from the employees his PA would create a questionnaire in Notes and email it to as many as 15,000 employees. They would dutifully answer the questionnaire, which might be an invite to the corporate picnic. The PA would then have a database full of answers which she can present to the boss as charts or tables or allow access to the database.
This would typically take about 10 minutes to create. This is now lost technology. I think there are survey tools that the average person can quickly push out a survey but emailing a spreadsheet is not it.
It was not the solution back in 1997 nor has it got any better in the last quarter century.
The problem is if you want to do any kind of decent B.I. the alternatives just don't cut it! Sorry they don't, I've tried.
Excel, with PowerQuery/B.I. has some really great data handling capabilities that allow users who are not "database" capable to do these things. There is no reason why you should not do these things.
The issue is more of security and data spread. Different copies of disparate workbooks floating around that are not kept track of.
I've been involved in projects like this and workbooks are a very useful tool. The key is to have separate workbook for user data entry and a master that this data in imported into. Advise staff not to send these sheets to others. The key here is to get the information as fast as possible and get the project done! Developing some Web/Database app takes too long, has way too many end user support issues and will probably never get used again! (sorry devs)
just from memory without re reading your kindly supplied url ,
wasnt that because they were using Excel 1972 or something?
hopefully in their quest to hunt down unsupported software they've pushed the boat out installed office 2017 or similar and now can do more than 256 rows !
good to go!
It was actually when they ran out of rows not columns, and only when someone saved it in an older Excel file format which didn't support the requisite number of rows. Whoever did the file save probably got the "you're using an older file format that may not save all your data" and dismissed it.
Then, as now, I'm inclined to give someone the benefit of the doubt, that they were working under tight deadlines in a really difficult time, using a process that had been thrown together quickly because something was needed immediately, rather than wait 6 months for a fully tested, well produced product.
I always laugh when IT consultants say something along the lines of "We could modernize these 3 simple spreadsheets that don't have any problems, replace them with a bespoke SAP-integrated cloud blockchain solution!".
Cue a project director, 3 months of contract haggling, endless training and the end product is just as shitty as the original .XLSX that at least mere mortals could open without 18x IT helpdesk requests.
Whenever there is an IT project like this, I always ask the group: "if this was your money, would you stick with a simple spreadsheet, or spend £100k replacing the simple thingy with a complicated thingy?
1. Depends how costly a mistake in those spreadsheets is and how likely it is that a mistake is made.
2. If the spreadsheets really are that simple, why do you even need a bespoke SAP integrated cloud blockchain solution which involves 3 months of contract haggling and endless training? Either do it in-house or the requirement has been over specified in the modernisation effort - like adding in blockchain for no readily apparent reason.
3. If the thing you do, question you ask, is as you say, then you aren't an IT consultant (or at least not one from a company that stands to gain from getting the contract to produce the solution). In my experience IT consultants like that are actually from sales.
#2: He was being sarcastic, which you oblivious did not get.
#3: Of course he is not an IT consultant, he's the poor guy who has to deal with the IT consultants. You know, those consultants who could care less of they they provide is a quality product just so long as they can bill you for as many hours as they can.
As far as #1 is concerned, any data collection method is going to result in someone making a mistake. No amount of data validation is going to prevent a mistake.That is what project managers are for. Review the information, ask questions. Do dome real "human" work.
"He"?
I didn't get any hint of sarcasm, no. Sorry. Not in that bit. Well #2, perhaps I was restating what they asserted, which would have been me agreeing with them. I wasn't being sarcastic. I do know what you mean about the IT consultants who are happy to take you out for an expensive lunch on expenses, knowing that THEIR expenses are actually going to be YOUR expenses if they get the contract.
And the point of #1 is that the decision to change must be based on risk analysis. It's not PURELY data collection, it's also data handling and data processing... it can be awfully easy to make a slip with a spreadsheet that can be very hard to spot, and if the ONLY validation of the output is that it roughly looks right or not, then that output better not be used for anything too important. Simple spreadsheets don't separate data collection from data processing.
For all of the obvious foibles of open file exchange, any-user editing, weak input controls and audit trails... One can never underestimate the value of a good spreadsheet!
For the specific problems being addressed here a whopping great ERP system to merge all ERP systems is let's face it, a patent non starter
At least not until we can tell computers to go away and build something on their own - and figure out the often terrible scope and specifications one has to work with.
Presumably this is the "build one to throw away" idea (which is from The Mythical Man Month, well before agile descended upon as the ultimate meaningless buzzword). This argues that you should build a quick-and-dirty prototype for any major system, in order to understand the requirements more fully, then throw it away and build it in a proper robust manner.
However, what they have misunderstood is that you don't build your prototype using a completely different technology (excel) to the one you plan to use to build the application with, as you don't learn anything about how it should be done that way.
I wouldn't normally defend the use of Excel for anything that you couldn't otherwise do on squared paper*, but there is an argument to be made in this case.
Often, the biggest issue with exercises of this kind is determining whether the data you want actually exists, whether the responsible people are capable of collecting it and whether you can learn anything useful from the data when they do.
It may well be worth finding the cheapest possible solution for collecting trial data before you build an elaborate system to collect information that doesn't exist or isn't comparable across different locations.
*Though I suspect some may struggle even with that.
@Warm Braw,
"Often, the biggest issue with exercises of this kind is determining whether the data you want actually exists, whether the responsible people are capable of collecting it and whether you can learn anything useful from the data when they do."
Wise words that should be embedded or tattooed on the head of every consultant - sigh!
Coincidentally was looking up Ncurses which I believe would be useful in cases of prototyping what type of entry data can be collected - but most likely be dismissed as too 'old-hat' for the trial data tests.
Too many Excel experts think it is the Ultimate Answer for data entry.
>However, what they have misunderstood is that you don't build your prototype using a completely different technology (excel) to the one you plan to use to build the application with, as you don't learn anything about how it should be done that way.
What you learn is what the business and technical requirements are and what challenges are with respect to data collection, technology and design of the capture system. Then you can select an appropriate implementation technology.
I've got the t-shirt from the number of software led projects - that failed to deliver because the developers effectively said "MS/SAP/Oracle etc. are the solution what is the problem?"...
This isn't one of those projects. This is a data gathering project gathering information from disparate departments. What they are doing is getting a "where are we now" point in time view. Why would you spend the time to prototype anything when you have a tool that gets you the information now!
Just because your dealing with IT people doesn't mean they are all smart! I've known IT managers that are just as incapable as regular users and need just as much support.
Once you have all the data if you then want an everyday use system to keep the data up to date and everyone informed then its time to approve the budget and begin a real product development project. This is the part that fall over most of the time. Management won't spend the money and the "initial data gathering" solution becomes the every day "mission critical" solution.
Click on the Data Tab
then "From Web"
enter the url of the file you would like to download
Do stuff in PowerQuery
Click "Close & Load"
then you have downloaded data using Excel
This has the benefit that if your remote datasource is changed, then the changes will update on your spreadsheet.
If only I got so far as opening a file with the 365 thing I've been blessed with at work. Every other program opens after you (double) click its icon. Not Excel. It registers your wish and files an entry to its internal event loop, only to pop a new fenster in your face a second, minute or half an hour later, exactly at the moment calculated to do most damage to your workflow. But all is good now I know about this - it used to be two to two hundred windows all at once before I came to realize this is a new undocumented feature and kept pecking at the icon in vain hope.
Observers might wonder why the team, supposedly the leaders of Whitehall's thrust towards digital modernisation, is using spreadsheets to share data, as well as preferring a method (download) whereby multiple copies of data may be created – making them inconsistent and insecure if shared via email.
well 7 billion will only get you so far ....
is when I've been called into some autopsy meeting because something bad has happened due to a mistake made in an administrative task based around Excel spreadsheets. Actually, that's not the thing that makes me cry... the thing is when I've spent a month or more converting that administrative process into a database backed web app with auditing, sanity checking, roll-back features, immutable backup to the cloud offsite, user input warnings, multi-user capability, record-locking etc, importing all the historic data kept in the excel spreadsheets, checking it, fixing it, double checking it, getting someone else to triple check it, testing it, getting the most IT illiterate I can think of to test it for UI/UX quality, THEN taking something I think is finally a complete package that would work well even if it became the final version because of the way these things tend to work out, presenting it to the administration team as a beta for feedback and further refinement etc. (I think they call it AGILE now, but it's really how I've always worked), and getting back comments that it's absolutely brilliant but lacking only one thing - the ability to filter the data and export it as an Excel file.
And then I spend the next week crying into my teacup as I tack on my pre-built Excel export interface code and tweak it to match the application, knowing full well that all they're going to do is open the exported file in Excel and start adding new data to it instead of using the web app because that's the way they've always done it and that's the process they are familiar with no matter how closely I've tried to match the Web Application to the way I've observed the process being done. I know from experience that even if I create the export file as read-only and password locked, they'll demand the password to unlock it and make it read/write.
Actually, re-reading that, I'm being unfair to my wonderful colleagues in the administration team.
They usually ask for the ability to filter and export as Excel because they often have to communicate their activities to other administration teams in the wider organisation, and they only seem to use Excel judging by the over-elaborate, Macro-laden, looks-like-shit "Applications" that come from there.
The conversation usually goes something like... "Your summary looks great, but can you supply those figures as an Excel file instead of a PDF please? Makes it easier to transfer it into a spreadsheet when we produce the summary figures for the end-of-year report."
I said "Applications" earlier... because I don't know the word for what they actually are. "Web Apps" are just websites with additional interactivity and functionality like a traditional application... what I see from Central are "Excel files with additional interactivity and functionality"... and I don't know what to call these "Excel-based data capture arrangements"... "Excrements" perhaps?
My company goes one further i.e. Power Point to spreadsheet back to Power Point. They are addicted to the Power Point. Its quite scary since the people who look at these Power Points are the senior decision makers
The cycle goes kinda like this:
Everything goes into Power Point typed in manually. From Power Point the data is down loaded(typed) into a spreadsheet and then we load into a very, very expensive Data Science software App that feeds into a reporting system. The users have the ability to export into a CSV file that is imported into a spreadsheet
From this point forward its Power Point, no control, cottage industries galore, re-calculation that appear to be done on the way the wind is blowing, and to top it all off my team are regularly told we are sh1t because the data is wrong.
While I'm not saying that the subject of the article are wrong in what they are doing I do can relate to your post.
When we move from a somewhat archaic Construction Management software that everyone was exporting data into Excel to JD Edwards, the buzzword was "No more Excel workbooks!". So what happened after the migration to JD Edwards? Everyone is exporting data to Excel!
Years ago I took part in developing a scada* application. We used to get dozens of change requests any one week, these often being changes to freshly requested and not yet developed features.
Once we added the possibility to export data to excel and import them back again, the change request rate dropped by an order of magnitude. We understood it as people had started using Excel as an alternative user interface to our program.
* Software for Control and Data Acquisition, or something like that.
"knowing full well that all they're going to do is open the exported file in Excel and start adding new data to it instead of using the web app because that's the way they've always done it "
because that's the way they've always done it
AAAAAAAARRRRRGGGHHHHH !
IKR?
Oftentimes they dont even know what they are doing , they just blindly following "what they always do"
I say to them things like "you could miss out steps 3 , 5 and 9 and it would not make a blind bit of difference" and they wont do it .
Or it was requested that you MUST collect data on gender now (add an extra step that was previously optional) in order to ensure a balanced representation e.g in meeting planning - previously the quality of the data was so poor as to be unusable - so you made it a compulsory field. And when they discovered they couldn't leave that field blank they stopped using the app.
Yes, I feel your pain.
Of course, when the 12 month transition period is over and the ONLY input to the higher process is from the application as you retire the excel import module that's cranky as fuck and is no longer supported except by paying £eyewatering bespoke support fees, they complain that they suddenly have to cope with this new interface overnight and there was no warning or training. And there's no way that you'll get away with "You remember that day everyone went out to X hospitality venue for software training... no... they had coronation chicken sandwiches that you really loved... and Prosecco in the evening? NOW you remember... yes... THAT training... Yes, I know it was quite long ago and that you've forgotten now..."
This business of sharing files from a central repository (or even a distributed one) while keeping a journal of file changes and managing who can access it seems to be something that should be familiar to software developers.
Version control systems work on files. Not source code. But in a world where files are now 'documents' and directories are 'folders' with everything being conveniently hidden in a maze of pseudo-simplification it might be said that many people can't see the wood for the trees.
scammers are going to have a field day
From :HMGovernment.co.ru
To:Victim
Please open and complete this excel file detailing your legacy systems.
Please not do mind it scanning your system for errors , or the increace in network traffic as we use your computer as a spam relay
Please pass this file along to your friends/colleges as HMGovernmnet would wish for a full survay
!!! RANT ALERT !!!
It is unfair to consider the absence of a clear plan for dealing with 'legacy' IT equipment and systems a major failing, (and no, not because this government doesn't have any clear plan about anything), but because this is horrendously complicated. Any overarching 'clear plan' would be so simplistic as to be virtually meaningless, and any detailed plan so complicated that it would be incomprehensible to anyone without a first in Systems Engineering and a PhD in advanced cleverness from the university of clever.
It would have to deal both with innumerable PCs on versions of Windows all the way back to XP, the Cobol system that only 'old Ron' knows how to maintain, but is essential to keep the boiler running in a building that cannot be updated because it is grade 1 listed. It would have to cope with the ICL, Unisys, Amdahl and Prime computing systems used in seriously underground, former mines, oh and the Lotus Notes implementations still in use*, and, of course the outsourced systems that EDS runs on behalf of the department or agency and won't even tell you which site it is in because that was not in the original contract and they'll need a meeting of all the top brass to decide how much to charge for that information (and the meeting will cost you US$10,000 just to arrange, even though the customer won't be there).
So I don't actually blame anyone for not having a clear plan, because a lot of the time any government (Con or Lab) has had a clear plan about major IT infrastructure changes we've seen headlines all over the place about cost overruns, mission creep and failure. It is actually not a simple task, and like quantum mechanics, anyone who says they understand it, doesn't.**
!!! END RANT ALERT !!!
*I honestly have no idea if any of this is actually current, but I bet some of it is.
** R. P Feynman: "Anyone who says he understands quantum mechanics, does not understand quantum mechanics."
I've tried to kill legacy apps at half a dozen places and I've always failed. I usually saved some money but there are a lot of things that are just cheaper to leave alone. Lotus Notes isn't going away without a lot of willpower from management, it's usually licensed by IBM under the same enterprise deal covering z/OS so it effectively costs nothing but takes rare skills to reverse engineer any workflows out of and implement in your choice of future legacy.
From https://www.theguardian.com/books/2005/oct/15/featuresreviews.guardianreview33
From a review of a biography of Mrs Beeton*:
"If Beeton was not what we think she was, neither was her book. Household Management is popularly seen as extravagant ("take 12 dozen eggs") or as representing a lost rural way of life ("first catch your hare"). In fact, it is neither. Although the book does contain a few conspicuously extravagant recipes, if anything it errs on the side of frugality, with many pages devoted to plain family dinners and the use of leftovers."
*"The short life and long times of Mrs Beeton", by Kathryn Hughes.
"The current Excel-based data commission is being used to develop an early pilot of the Dashboard, ahead of moving to a more performant solution over time, in line with agile delivery best practice," he said.
"The Dashboard metrics have been developed in collaboration with the cross-government Digital, Data and Technology (DDaT) community."
Anyone for a game of buzzword bingo?
Civil Service names and initialisms for technology are as obscure and numerous as euphemisms for a latrine.
Government departments seem to be always trying to fill vacancies for a "Digital Developer". Is this a developer who counts on his fingers? Or are they just making sure they don't see applicants whose only experience is on a MONIAC?
Can't really comment without seeing the spreadsheet.
I've seen these sorts of questionnaires from central governments departments before. Often they are so badly designed that you can't actually provide the information they have requested. Unless of course the reason is that they don't really want data that would be inconvenient for them.
Then of course there's the question of how they are going to collate and and analyze the results. If they are going to try to do it by pulling everything into one big spreadsheet for the entire country then I don't hold out much hope.
I do worry that the wrong questions will be asked, the wrong answers being given and then people trying to fit their preconceived solution to the data.
eg
Q:What is the impact of replacing locally created solution with the corporate one? (Thus saving one person year of effort to support it)
A:We will need to develop a new process.
Result: It took 5PY to effort to develop and test a new process.
Success:! The 1py of effort decrease from the IT budget, (- lets not talk about the 5py of effort - it was "lost" in a different budget)
Because this is how you get a ransomware infection...
"We kindly [request] that your department shares data with our team by completing the attached workbook; please download the workbook in Microsoft Excel for the best user experience,"
Don't forget to enable macros.
I wanted to downvote your comment, because everyone knows that government departments always ensure that their files are free of malware. But then I remembered an HMG client that placed a Word document on their web site, for download which actually did contain a virus macro. And they took quite some persuading of that. I think the then Inland Revenue* eventually 'explained' to them that they had a problem, and it was taken down.
Then, of course, there was the senior person at a major HMG department who used a home computer to do some extra work, brought in the files to work, and that is how the entire department got shut down for 3 days while the system administrators cleaned Melissa out. (Only afterwards did the department in question accept that maybe I had point in suggesting that they have internal partitioning of their network with firewalls, but then, as a highly respected and competent consultant, I was used to being ignored.)
*Now part of HMRC
Hopefully they are not requiring macros to be enabled in this spreadsheet - NCSC advice is that macros are disabled, if that is not possible, consider code signing for approved files or limit execution to only those that need it. Windows ASR and AMSI also worth looking at - vba is a big attack vector.
https://www.ncsc.gov.uk/guidance/macro-security-for-microsoft-office
"NCSC advice is that macros are disabled"
I'm sorry, I can't read that NCSC website link you posted as this is the same NCSC whose website *requires* Javescript to be enabled to see *anything* at all!
They're apparently good at giving advice that they don't seem to follow themselves...
I wonder how many government IT projects include a future upgrade path in the design phase. You know, a way of ensuring that at some unspecified data in the future the application can be ported to another, currently unknown, hardware platform, or operating system? I do not recall in any of the procurement exercises that I participated in there was any mention of actual budget for 'future proofing'.
Oh, of course, wasn't Java meant to provide that? A platform independent programming language? How is that going (genuine question, I'm retired now, so have no way to find out)?
Quote: "...in line with agile delivery best practice..."
Quote: "...a 34-year-old ICL mainframe system was one of the causes of a scandal which led to more than £1bn of state pensions not being paid..."
So....now we know what "agile" means when mentioned by a senior civil servant....
....it means that each "sprint" is at least 34 years long....and saves the government at least £1 billion....
George Osborn would approve!!!!
Dominic Cummins whole aim was to get people who have the knowledge and experience into the civil service in order to stop this kind of r’tarded shit from happening.. but obviously the status quo would never allow that and so they kicked him out eventually.
It’s pathetic and sad to see this nonsensical garbage being pushed by the same old set of talentless friends of friends.
Just looked at a tech job for a major luxury boat builder who use excel spreadsheet NCR (non-conformance reports).
My son in law has just taken delivery of a 78 foot motor yacht (their largest) from this mob and it came complete with lots of teething problems from minor to fairly major, which you wouldn't expect for over $6M.
Otherwise an excellent employer building amazing boats.
Only because of the Excel NCR reports I didn't apply.
Lots of worthy discussion here around platforms, security etc.
How about the timescale. Send in December before Christmas, expect results in January. List all legacy technology, out of support platforms etc. etc.
Hilarious.
I work with enterprises that have been trying to work that out for years without success.
Just so hackers are not allowed set the password, which they won't guess, to Password. Problem solved. My invoice for the consultation (£$ a large amount) should be paid to my account at nigerea495@ivegothemoney.com. Shush (Whispers, bribes will be considered).
Excel is ubiquitous in the world... Business people and Scientists use it alike.
My guess is that the people who "don't understand" why anyone uses Excel, probably has either never used it, or have a limited understanding of it.
Excel is a very powerful product used for data manipulation and data analytics.
It is completely fine to use it in this way, my guess is they have the Excel sheet locked down and are enforcing data integrity in the fields.
They also probably have a process where they will batch import this data into a central Excel sheet or database. (or SharePoint?)
It is interesting to see people bash someone else's solution without understanding what their process is or the motivations or constraints behind why people choose the solutions they do.
I worked for a government R&D organization that tried to use excel to manage their whole program, including text describing projects, their status, and funding. Among other things, you would see financial figures for your expenditures that you knew could not possibly be correct. I still have a recollection of accessing the spreadsheet after one of the managers announced minor adjustments in the allocation of project funding at 5 minutes to 5 on a Friday. I was shocked to find that half my project funding had disappeared completely without warning.
Anyways, I think we could claim to have the worlds largest and most useless spread sheet.
Spreadsheets are great for adding a few numbers together.
The amount of time they end up being a database substitute is astounding. See I blame accountants, they learn spreadsheets, and everything can fit into a spreadsheet. The end !
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
