Tag: computing (page 1 of 2)

Short Course Taster Evening 26 March 2024

 

Join us this March 26 for our free taster event, where you’ll have the chance to speak to the team, find out more about our courses and ask any questions.

You can even take part in a free 45-minute taster session to get a flavour of what it’s like to learn with us.

We will have a choice of tasters available, including:

There will also be a Novel Studio enquiry desk for anyone who wants to find out more about how to apply for our flagship year-long novel writing course.

And as a bonus, we are also offering a 10% discount on all our short courses for anyone who attends the open evening and enrols with us on the night.

Attendance at City events is subject to our terms and conditions.

What Are the Most Useful Skills for Adapting to AI?

Intelligent life?

 

Technology is advancing so rapidly, it can sometimes feel bewildering, especially when thinking about what the future jobs market might look like. How do we ensure we keep pace and equip ourselves to adapt to the change? Read on for the subjects that could help you future-proof your career in the AI frontier.

Become an Expert in Data Analysis

Data has become supremely valuable, not least for its importance in training AI. Gaining expertise in data analysis is a smart way to stand out in the jobs market, and stay relevant. City has a range of courses designed to help you gain knowledge of, and insight into, data analysis. From Introduction to Data Analytics and Machine Learning with Python to our new Spreadsheet Data Analysis and Automation with Python  or Introduction to R for Data Analysis, our short courses in data analysis will give you a head start in this rapidly changing landscape.

Codebreaking

Colour coded

Sometimes known as programming, coding is essential for anyone wanting to work effectively with AI. City has a great range of short coding courses in all the major programming languages. Take our courses in C/C++, or the ever-popular Python short courses. You can also learn JavaScript or Java, used by 42.9% and 37.8% of web developers respectively working in artificial intelligence or machine learning, according to a recent survey by Evans Data Corporation.

Softly, Softly

It’s not all about technical skills. Working with AI requires soft skills too, from communication to creativity. Our short courses in Presentation SkillsEffective Communication and Stoicism will improve your interpersonal skills, while our vibrant range of  writing courses will kickstart your creativity. Adaptability is also a key soft skill, vital for coping with the pace of technology change; our highly interactive Leadership and Management course will sharpen your team-leading skills and ensure you get the most from your teams.

Keep it Safe

AI systems often deal with sensitive data and are vulnerable to cyber attacks. It’s important for businesses and individuals to understand the risks involved, and learn how best to mitigate them. City’s Cybersecurity Fundamentals short course provides a great foundation in the cyber security domains of networking, security engineering, risk management, incident response, governance control and legal practicalities.

Get Developing

Web development is an important skill in working with AI. From visualising AI outputs, to integrating AI with other systems, being skilled in web development will ensure you can utilise AI models in the most powerful ways. City’s short course in web development will teach you how to install Bootstrap and how to use its key components most effectively. Our Building Websites short course will enable you to plan, design, develop and publish a website that adheres to current industry standards and best practices, while our PHP course is best suited for back-end web development and can be embedded into HTML.

Storytelling

Turn the Page

As technology advances, it becomes more important than ever to differentiate what makes us human. Storytelling is one of our oldest skills, and remains an integral part of our lives. City’s short creative writing courses are designed to help you understand how stories are built, how they’re written and how they can be edited. Try our introduction to creative writing course or learn how to craft non-fiction with our Narrative Non-Fiction or Memoir Writing course.  For our full range, visit our home page HERE.

 

For all City’s short courses visit our home page HERE.

Top Nine Courses to Help you Develop your Digital Skills

The digital revolution is well and truly under way, and it’s transforming the way we live, and work. According to the World Economic Forum’s report on the future of jobs, 97 million high-skilled jobs will be created by 2025, and almost all will require strong digital skills. From reducing the digital skills gap, to fostering innovation and increasing productivity, it’s now more important than ever for employees to acquire digital skills, and for employers to invest in those skills for their teams. Read on for City’s top nine courses to help you develop your digital skills and remain competitive within today’s ever changing jobs market.

1.Introduction to Data Analytics and Machine Learning with Python

The fields of data analytics and machine learning are vast and fast expanding. By leveraging the most widely used Python libraries, this short online evening course will give you the foundations to enable you to get a junior position as a data analyst and/or machine learning engineer.

2.Creating Mobile Apps with Android

There’s An App For That

There’s an app for that. City’s Android app developer short course gives you a comprehensive understanding of the Android development platform and the skills required to develop and publish your own applications. By the end of thecourse you will have created your own Android app which you can publish in Google Play.

3.Digital Marketing Fundamentals

Spread The Word

Digital platforms have become the primary medium for marketing and it’s now essential for all marketers to have good digital knowledge. Our short course will equip you with the principle digital skills required to ensure you know how to maximise your marketing across websites, social media and digital advertising..

4.Writing for the Web and Digital Media

Being able to write effectively for digital will give you the edge so you can attract, and keep, the attention of your online audience and successfully present written content. The course also covers editing and proof-reading skills, best practice for titles and subheadings, blogging, editorial planning, content marketing and SEO.

5.Building Websites with HTML5 and CSS3

Having your own online presence is fast becoming an essential in today’s jobs market. City’s short course will teach you how to plan, design, develop and publish your own fully functional website which adheres to current industry standards and best practices.

6.Cybersecurity Fundamentals

Cybercrime is a growing global menace costing companies millions in lost revenue each year. This online short course will ground you in the essential cyber security practices, such as networking, security engineering, risk management, incident response, governance control and legal practicalities.

Securing Cyberspace?

7.Photoshop: An Introduction

Being able to create and manipulate a digital image can increase your productivity and enhance your workflow. On this short online courseyou’ll be given a comprehensive overview of Photoshop—the industry’s most flexible photo editing software—and learn the fundamentals of digital imaging, including how to make your own digital creations.

8.Digital Filmmaking: An Introduction

The digital revolution has transformed the way films can be made. On this short course, led by an award-winning film director, producer and screenwriter, you’ll be guided through the processes of making a short film. You’ll also develop a good understanding of the creative interconnection between writing, shooting and editing.

9.Introduction to Branding

Aimed at entrepreneurs, small business owners, communications and marketing professionals or anyone interested in learning how to communicate their brand more effectively, this short course will explore a full introduction to making your online brand a success—from online brand strategy to writing on-brand social media messaging and digital marketing.

For more on our short courses provision, visit our home page HERE.

On Brand

Or come along to our virtual Open Evening next week on 28 March to talk to one of our coordinators where you can also try out a free taster course.  Register HERE.

The top five computing languages – what they are and why you need to learn them

Computing languages are essential for anyone looking to work in today’s growing technology. But with new languages being developed every day it can be difficult to keep up to date and decide which to learn.

Read on for the top five computing languages you should be learning now, and why…

Speaking my language?

  1. Python – Python is still the number one computing language, and for good reason. It’s extremely versatile and can be used in many different fields, from machine learning to data science and web development. It’s also easier to learn than some of the other languages due to its unique structure and syntax. Plus there are a ton of resources for those new to the language.
  2. JavascriptJavascript is a front-end language used to create interactive web applications. If you are looking to work in web development or mobile app development, this is the language for you.
  3. JavaJava is a back-end language used in many large corporations. Employers value the versatility and security of Java and it’s an excellent language to learn to improve your job prospects.
  4. PHP/MySQLPHP/MySQL is a widely used open-source scripting language especially suited for back-end web development. It can be embedded into HTML and is very popular within the industry. PHP has been used to create many websites, including Facebook, Wikipedia, Slack, Etsy and WordPress.
  5. C and C++C and C++ are languages often used in game development and system programming. They are both very powerful and can be challenging to learn, but invaluable for anyone looking to progress within the technology industry.

Improve your prospects

If you’d like to find out more about learning a computing language and how it can open up your job prospects and ability to progress within the technology industry, come along to our Open Evening on March 28. It’s all online, so you can join from the comfort of your home. There are free tasters available for Python and Database Design, or you can simply speak to our Computing Coordinator to see which course would be the best fit for you. Register HERE.

Open Evening March 28th 2023

For our full range of Computing Courses, visit our home page HERE.

 

Or visit our main short course home page HERE for all the subject we offer.

 

 

Meet our Indesign and Illustrator tutor, Helen Pummell

Following our series of interviews with the team behind City’s short courses, today we meet Helen Pummell, tutor on our Adobe Indesign and Illustrator short courses.

Portrait of tutor Helen Pummell

Helen Pummell, Adobe Indesign and Illustrator Tutor

1.Please tell us about yourself and your background

Since 1996 I’ve been a creative art worker and graphic designer specialising in print media and advertising. And for more than twenty years now I’ve also been a part time creative software lecturer at City, University of London.

I’ve prepared in-store graphics, bus and taxi wraps, brochures, flyers, posters, branding and adverts in every major newspaper in the UK.  I’ve worked with educational establishments of world renown like The University of Brighton and Oxford University, also some of the UK’s best-known high street brands like Coca-Cola, Miss Selfridge and Tesco.

2. What do you teach at City?

I teach the Adobe InDesign and Illustrator courses at City. They cover all the fundamentals needed to begin using the software professionally. The design world can be daunting to break into and learning some industry language and processes can give students a valuable advantage, so my courses cover more than just software. My aim with teaching is to give students the tools to do their own creative thing.

3. Why do you think it’s important to learn skills like Adobe Illustrator?

Illustrator is a fundamental part of the Adobe Creative Cloud suite, the industry standard software. It can be used for print, digital, motion graphics, 3D, logo design as well as a broad range of illustrative purposes.

4. What are your top three tips for learning Adobe Illustrator?

  1. Regular practice as often as possible – even if just for twenty minutes. Familiarity will improve everything.
  2. Look at professional examples. Anywhere you can follow vector artists such as Instagram, Dribble and Behance. Exploring professional portfolios is brilliant for keeping up to date with the latest trends and getting an idea of what is possible with the software.
  3. Try using Illustrator’s Harmony Rules to build colour palettes for all your creative projects. It’s a powerful and underused feature that can make your work stand out and accelerate your design skills.

5. Why would you recommend learning Adobe Illustrator at City?

The format is excellent for a wide range of different learners. Setting aside weekly time over 10-weeks really gives learners the opportunity to develop their knowledge and new skills. It’s a great length of time to get to grips with the fundamentals. The opportunity to practice new digital skills with guided face to face support allows students to learn at their own speed. I also make extensive notes and practice files available to all students on Moodle, City’s online learning platform, to support any personal practice during the week and allow them to prepare for, or revise, lessons as suits them best.

Thank you, Helen! For more on the Adobe Indesign and Illustrator courses Helen teaches, visit our design courses page.

For more on all City’s computing short courses, visit our home page here.

 

Meet our Photoshop tutor, Pete Polanyk

Portrait of Pete Polanyk

Pete Polanyk, City’s Photoshop tutor

In another interview with the team behind City’s Short Courses, today we meet Pete Polanyk, our Introduction to Photoshop short course tutor.

Please tell us about yourself and your background

I’m Pete Polanyk and I’ve been working in the design industry for near on 30 years and have been teaching at City for 20 years. How I actually got into the field was in the early 1980’s I wrote a music fanzine and sometime later undertook a night class in Magazine Design & Production at The London College of Printing (now the LCC) and it evolved from there. I’ve worked in national newspapers, advertising agencies and publishing houses as well as working for myself.

In my spare time I write a music/gardening blog, compile musical mixes for a shortwave radio show and produce electronic music, self-releasing it on my own label and designing the associated visual material.

What do you teach at City? 

I teach the Introduction to Adobe Photoshop course on a Wednesday evening. The course is an introduction to one of the industry’s top image creation and photo editing software.

Over the ten weeks we learn the fundamentals of the application, how to create and edit your own digital image/artwork using a variety of techniques and I also cover some Graphic Design related topics. We start right from the bottom and work our way up.

Why do you think it’s important to learn skills like Photoshop?

It’s a very versatile programme. By learning it, you could add it to your present work skills, you could develop your own creative output or it could be a way of getting your foot in the door into the design industry. Who knows where it might lead you.

Three surfers in the water at sun set

Learn Photoshop to understand how to manipulate images

Over the years we’ve had students wanting to learn it for many reasons. We had a person who wanted to use it just to do some simple amends on promotional material for her own business as she was fed up with paying a designer an extortionate amount for doing small corrections. We had a deep sea diver who wanted to learn the skills to make a poster to promote his local diving club and a vicar who wanted to correct photographs for a parish magazine.

What are your top three tips for learning Photoshop?

  1. Learn the keyboard shortcuts rather than using the pull-down menus, it’s a lot easier and will save you a lot of time.
  2. Set yourself tasks like simple design jobs, something that may be useful for yourself or your workplace and take it from there (promotional material, a simple web banner or greetings card etc). If you’re working on a project with a purpose it will keep your concentration and hold your interest for longer.
  3. Practice, practice and practice! It’s like anything, you get better by putting more hours into it. Little and often is a good thing too. Keep at it and you’ll get there.

Why would you recommend learning Photoshop at City?

The application is taught in small groups in a friendly atmosphere over ten weeks, two hours a week. I set three simple projects that you can complete over the term which gives you more practical skills with the programme as well as some work to show for the course.

It can be a diverse class with students from different walks of life (it’s not just people from a design related background) and it does you no harm being around people from other disciplines who take different approaches when it comes to learning something new or tackling photoshop projects.

Thank you, Pete!

Pete Polanyk teaches City’s Introduction to Photoshop Short Course. For City’s  other graphic design short courses, visit this page.

To see our full range of Computing Short Courses, visit our short course home page.

 

Meet Our AutoCAD tutor, Thomas Haycocks

Cavity 777 Sculpture by Nick Ferguson

Continuing our series of interviews with some of the team behind City’s short courses, today we meet our AutoCAD tutor Thomas Haycocks.

Please tell us about yourself and your background

Macena Octopus Sculpture in conjunction with artist Suzie Wright

I come from a creative background in design and have used AutoCAD (computer Aided Design) in a wide range of projects over many years. I very much enjoy the accuracy and visual quality that AutoCAD brings. The drawings I produce are used by companies and often transferred directly to Computer Aided Machinery to be manufactured. I have worked on large scale building projects, exhibition designs and public sculptures. I have taught for over 20 years to all levels and very much enjoy the interaction that teaching brings.

 

What do you teach at City?

I run the AutoCAD courses. We offer a range of courses starting from beginners through to courses that provide students with a greater depth of knowledge and a higher professional level of skill.

Drawing by City AutoCAD Short Course Student

At City, the courses are taught through demonstrations using AutoCAD, the knowledge learnt is then reinforced and embedded through AutoCAD based tasks. The AutoCAD classes are structured so that each week a new topic is covered. Together, these classes combine to make the whole course.

Why do you think it’s important to learn skills like AutoCAD?

AutoCAD is used across many industries and professions. The programme is at the forefront of Computer Aided Design. Whether you want to use it for your own personal use or to build your employment skill set, it offers the ability to accurately draw up your tasks and manipulate the drawings to exactly how you want them.

What are your top three tips for learning AutoCAD?

  1. Understand the AutoCAD interface – This will enable you to gain confidence in using the programme so that you can draw and present exactly what you want.
  2. Use AutoCAD to draw up your own projects – This will enable you to become fluent in using the tools, commands and features of AutoCAD that are relevant to you.
  3. Explore AutoCAD – There are many features in AutoCAD that can be found by exploring the commands. Not all of them are easy to find, but once you know them, they can help in the construction and presentation of drawings

Why would you recommend learning AutoCAD at City?

The tutors are experienced and highly knowledgeable in their fields. The team at City running the short courses are very professional, organised and provide a strong support service.

Thank you, Thomas!

To find out more about the courses Thomas teaches at City, check out AutoCAD beginners and more advanced short courses.

For more information on all our Computing Short Courses visit our web page here.

What really caused the Excel error in NHS Test and Trace COVID-19 system? An in-depth technical analysis.

Introduction

This is an in-depth analysis of the reasons that led to the COVID-19 positive results Excel error of the NHS Test and Trace system. The analysis is done using knowledge that a student can gain after studying a series of computing short courses at City, specifically Applied MS Excel, the series of VBA in Excel courses and the Database Design with SQL Server short course.

We have collated information published by the government and reported by news media to recreate, as faithfully as possible, the process that failed importing all COVID-19 positive test results.

We are also recommending steps that every company should follow when importing data from external partners, and the learning path prospective computing short courses students should take to gain enough knowledge to solve similar integration problems effectively.

Background

On Monday 5th October 2020 UK newspapers were reporting of a technical error in NHS’s test-and-trace system. The error meant that more than 15,000 positive cases of COVID-19 infections between 25th September and 2nd October were not included in daily statistics and thousands of people who had come in contact with infected individuals were not alerted.

In this post we are going to focus on the technological aspects of the error. We will try to figure out what might have gone wrong, by putting together information published by the government and newspapers and will give recommendations on what you can do to avoid facing similar errors when importing third party data or integrating your systems with external partners.

Information gathering

We will base our assumptions on a note describing the methodology used for COVID-19 testing data, published by the UK government [gov.uk-note]. It appears that testing is categorised into four pillars. According to the Mirror [Mirror], the error happened while handling ‘Pillar 2’ data. According to [gov.uk-note], pillar 2 is testing for the wider population collected by commercial partners. The dataset for pillar 2 testing comprises of:

  • nose and throat swabs, which are counted together as one sample
  • tests counted as they are dispatched
  • ‘in-person’ tests processed through laboratories, excluding the ones counted at dispatch
  • positive cases.

According to the note, there have been a couple of revisions to pillar 2 metrics and methodologies.

On the positive test results, which was the dataset where the error occurred, methodology was updated on 2nd July to remove duplicates across pillars 1 and 2, to ensure that a person who tests positive is only counted once. Specifically for England, the lab surveillance system for pillar 1 and 2 results removes duplicate records by running a complex algorithm that identifies individuals and only uses their first positive result for the metric. The algorithm uses the following properties to uniquely identify an individual:

  • NHS Number
  • Surname and Forename
  • Hospital Number
  • Date of Birth
  • Postcode

News media presented a series of explanations of what is believed that had gone wrong.

  • According to Daily Mirror and Daily Mail, “Excel spreadsheet reached its maximum size” [Mirror] [Mail]
  • Daily Mirror also reports that “Outdated Excel spreadsheet format that was not capable of displaying all the lines of data” was the issue. [Mirror2]
  • Daily Telegraph [Telegraph] goes into more details: “The problem emerged in a PHE (Public Health England) legacy system. Public Health England was reportedly using an automatic process to pull the testing data it received from commercial firms carrying out virus swabs into Excel templates. But the old Excel file format being used – XLS – could only handle 65,000 data rows. The files have now been split into smaller multiple files to prevent the issue happening again”.
  • The Guardian [Guardian] on the other hand reports that the process is not completely automated and a lot of work is still done manually. It appears that CSV files are sent from labs to PHE, which are then loaded into Excel.
  • Finally, BBC reports that each test result created several rows of data. In the same article, there is also a comparison between the XLS and XLSX file formats of Excel, claiming that the new format would be able to handle 16 times more cases than the older XLS one. [BBC]

In depth analysis of what caused the COVID-19 Excel error

Public Health England has not yet published exact details of what went wrong. What we will do is to try and simulate what might have happened, by putting together pieces of information from the governmental website and news media reports.

To do so, we will create a dummy CSV file that contains the properties(fields) [wikipedia-csv] used as unique identifiers for each person tested, together with some dummy fields that represent test results. We will then go through the most plausible scenarios and discuss what could have gone wrong, to produce the error experienced by the NHS Test-and-trace team.

A CSV file is a text file that represents tabular data. This means that it contains a specific number of columns and one or more rows. According to the basic rules for CSV files [wikipedia-csv] and the 2005 technical standard RFC4180 which formalises the CSV file format, “All records should have the same number of fields, in the same order”.

This is an example of what data would definitely exist in the CSV file (first represented as a table and then in CSV format – Disclaimer: NHS numbers are random):

NHS Number Surname Forename Hospital number Date of Birth Postcode
485 777 3456 Smith John HN3829904 12/03/2001 HD7 5UZ
943 476 5919 Smith Jane 21/12/1958 HD7 5UZ

This is a CSV representation of the above tabular data:

NHS Number,Surname,Forename,Hospital number,Date of Birth,Postcode
485 777 3456,Smith,John,HN3829904,12/03/2001,HD7 5UZ
943 476 5919,Smith,Jane,,21/12/1958,HD7 5UZ

Further columns could be added to represent test results, but each row (record) should have values for each column (or at least simply a comma if a value is missing).

In order to test importing CSV files that are very large for Excel to handle, we created a dummy CSV file with 1,050,001 rows that has the following fields: NHS Number, Surname, Forename, Hospital number, Date of Birth, Postcode, Test number, Test result. The number of rows is larger than the limit of 1,048,576 rows that newer versions of Excel have [Excel-limitations].

The file contains random data that do not conform to data types of individual attributes. Specifically, the NHS Numbers generated are 10 random digits, where the 10th digit is not the control digit, postcodes simply follow the rule of having two letters-one or two numbers-space-one number-two letters format to look like postcodes but are not verified to be valid postcodes. You can download the dummy file from our Covid-19 Excel error analysis GitLab repository, where you will also find the Excel VBA code used to generate the test data.

Importing a CSV file that Excel cannot handle

Let’s try to import the generated CSV file into Excel. We do not know the version of Excel PHE is using, so we are going to go with the latest Excel 2019. News reports do mention that XLSX format could be used, so we assume PHE is using an Excel version after Excel 2007, but we are expecting similar error messages will appear in all Excel versions.

Opening CSV file directly in Excel

Here we see the error message we get if we try to open the generated CSV file directly in Excel. The way we opened it was by double clicking on the CSV file in the File Explorer, as the CSV extension is associated with MS Excel automatically during typical installation. An alternative way of opening the CSV file from within Excel would be to use the Open dialog, navigate to the directory that the CSV file is stored in and open the file from there.

Excel error message when trying to import CSV file with more rows than Excel can handle in one worksheet

The error explains clearly that when the user clicks OK, Excel will truncate the file and only show the part that fits the rows and columns available in one worksheet.

Importing CSV using Power Query (also called Get and Transform or Get Data)

If the user tries to use this new Excel functionality to import the CSV file she will be faced with the following error:

Excel error explaining that CSV file being imported will be truncated as it has more rows than an Excel worksheet can handle

Again here we see a very clear error message, which explains that when the user clicks OK the data will be truncated and Excel will only display as much data as it can fit in a worksheet. Clicking Cancel will not import any data at all.

We see that both ways of opening a file in Excel, without using VBA code, show an error message notifying the user that data will be truncated. Clicking OK and continuing with only the data that fit in a worksheet is obviously human error.

Importing CSV using VBA in Excel

News reports mention that there is a (semi)automatic way of importing data in CSV format. Such automation can be done in many different ways. One automation could be that the user opens the CSV file normally and then, using a central dashboard, instructs Excel which worksheet represents the CSV file that was just opened and should be imported. A variation of this kind of automation could be that the user points to a Table in Excel as the input that represents the imported CSV file (a Table is created when Power Query is used to import a CSV file). Both of these scenarios expect the user to open the file with one of the ways we describe above.

Another way of importing a CSV file would be using Visual Basic for Applications (VBA) code in Excel. Again here there are many valid ways that VBA code can be written to import text files. In order to test this scenario, we created a VBA subroutine that reads a CSV file one row at a time. Each row that is read is split into attribute values and entered in the next available row of a worksheet. No error handling was implemented in the code.

Below you can see the type of error the user would get if the CSV file was imported via VBA code. This is the error message shown by the VBA interpreter:

Visual Basic for Applications error shown while importing a CSV file that has more rows than an Excel worksheetThis error message is definitely a lot more cryptic than the two errors seen above. The choice of buttons is also quite difficult to work with, by an untrained user. I am not sure whether the user would click on “Help” (only to get further unhelpful information – as shown below), or simply click “End” to stop the execution of the VBA automation. I am fairly certain though that either way the thought that first came to the user’s mind would be “HELP! I don’t know what to do.”.

Help page from Excel explaining the VBA error caused while importing a very large CSV fileIn every way we see this, an error message would have appeared on screen, which means a user clicked OK without understanding the implications, possibly due to no relevant training. There is one possibility that the user importing the CSV file might have not been shown an error message. In this scenario, a VBA developer chooses to suppress all error messages shown from the VBA interpreter (like the one above). This is usually done either in an effort to avoid scaring the end user, believing that no error messages will be thrown by the VBA code written and if any is thrown it won’t affect the end result. In this case, human error is still the cause of the truncated dataset. However it is not the end user importing the CSV file that caused the error, but the VBA developer.

Remarks on the process

Storage structure of test results in CSV file

BBC [BBC] reports that each test result generates more than one row of data. We have two interpretations of what this could actually mean, based on the fact that data is delivered in CSV format.

  1. Each test generates time based results, i.e. one value in 30 minutes, another value in 90 minutes etc. and the decision whether the test is positive or negative comes after a simple calculation between these values.
  2. The process was misunderstood by the reporter. What really was meant is that in the same dataset there might be two tests (with two individual test IDs) for the same patient. This might happen if for example the first test became contaminated or a second test was done the same day for whatever reason.

As mentioned in Wikipedia “CSV formats are best used to represent sets or sequences of records in which each record has an identical list of fields. This corresponds to a single relation in a relational database, or to data in a typical spreadsheet”. The relational model used in relational databases and spreadsheets is most often represented as a table, where a header defines the attribute(field) names and each row has attribute values for each attribute name. In the relational model each row represents a unique record. This is the reason we are sceptical about the premise that a test result generates more than one row of data. Each row needs to be unique in some way, by a combination of attribute values. The use of a relational format to represent data that are not following the relational model does not make sense. This is how our assumption was made that each result must be unique either by including a timestamp or some other unique identifier or attribute, if two or more rows of the dataset are for the same test. On the other hand, we believe it is catastrophic if two rows cannot be uniquely identified as an individual entity, but still give two values for the same attribute.

Use of CSV for transportation of results

CSV is a very widely used format. It is not known when it was first created, but it already existed in 1972 [IBM-Fortran]. Even though it has been used for at least five decades, CSV support is varying across software. Its flexibility means that it is very easy to create CSV files that do not conform to all expected characteristics of CSV files. It is also very easy to break. A badly generated CSV file with the wrong value for one of its attributes, for example a comma to denote thousands in a number, i.e. 1,532.25, would not be imported correctly by any software, unless a different separator was used instead of a comma, a practice that is quite common. Usually the structure of CSV files is documented within a project, so that both the exporting and importing applications can correctly support the files generated.

Taking into consideration the limitations and old age of CSV format, as well as the potential duplication of data between multiple rows in the CSV file, we believe a different file format should be used (e.g. XML or JSON).

Use of Excel

There has been a lot of criticism on the use of Excel for COVID-19 test results, given that PHE already has a robust database, used for years, to collate test results for various diseases [Sky-news]. From this Sky News article we see that Pillar 2 data are probably the only data not directly sent to the database. It appears that Excel is used to open and upload the CSV data to the database.

Is the use of Excel valid in the case of getting COVID-19 test results from Pillar 2 privately-run labs and converting them and sending them to the main PHE database? We need to think of all the requirements and limitations that existed at the time of conception of this use of Excel, before we decide:

  • First of all, in March 2020, with the need to increase COVID-19 tests rapidly, privately-run labs were set up. We believe that each lab is using its own software to record test results. It is expected that most if not all of this software was able to export to CSV format quite easily, maybe with minimal set up.
  • Second, uploading data to any database needs to pass some validation, so that the database does not become corrupt. Such checks are best performed on the side of the database, instead of the side of the user – where user is each lab.
  • Third, new software needed to be created in almost no time to be able to handle the data sent by the labs. It would also need to be used by users that would require almost no new training. This means that an extension for a software that users already know how to use is the best option.

Excel is probably the software all PHE users knew how to use, in varying degrees, depending on their position. For time zero, a VBA extension in Excel seems like the first logical step. Excel VBA is commonly used as a rapid application development tool to test an idea.

VBA is a quite flexible language that, by leveraging the power of Excel, can help create very powerful extensions in very short time. We believe a very first version of a VBA extension that could handle CSV files sent by private labs could be created in a few hours, to handle the first data coming in, needing processing and uploading to the database.

Once a primitive way of importing data was set up, two parallel processes should have begun:

  • One should revise, expand and vigorously test functionality of the VBA extension, with a focus to eliminate human error from the process as much as possible.
  • The other should be to create an implementation that bypasses Excel all together and allows privately-run labs to use it to upload test results directly. A great way to do so would be through a restricted secure web service.

We believe that if the importing VBA process was correctly designed and tested, even an old version of Excel from 20 years ago could handle any CSV file size. The limitation of 65,536 rows that Excel has for each worksheet is not something that should stop an experienced VBA developer in creating a robust VBA add-in that can import CSV files of any size.

  1. If the contents of the CSV file are converted by an Excel template to be uploaded to the PHE database, then the VBA procedure should read in memory one row of data at a time and upload it, instead of importing the whole file in a worksheet. This approach has two limitations. The amount of RAM available on the PC to hold one row of data in memory and the amount of hard disk space available to allow storing the CSV file. We believe that both of these are sufficient on the PC where the error occurred, given that it successfully loaded sixty five thousand rows into Excel.
  2. If the user needed to view the raw data of the CSV file in Excel then, depending on the screen size, only about a hundred rows of data would need to be displayed at any one time. This can be achieved using a sliding window technique. Again, this is something that Excel could handle in pre-2007 versions, as it is far lower than the 65,536 rows available.

Our conclusion is that Excel was correctly used as a solution that satisfied all requirements at the time. A correctly designed and implemented Excel VBA add-in is also able to handle any number of rows from a CSV file.

What should you do to avoid this happening to your company?

Let’s explore best practices when importing data and integrating processes with an external company. If your company is collaborating with an external partner and prepares to import their data, then you need to have a bulletproof process to handle the incoming data. It is important to create an automated process and remove user involvement as much as possible to minimise or even eliminate human error. It is very important to test your automation vigorously, especially at edge cases and around known limitations.

If you are starting a new partnership and you want to test a satisfactory integration solution before implementing a full system that will cost a lot, Excel is a great choice. Most IT users already have some exposure to Excel. With minimal training you can train your end users to use VBA add-ins. Excel has grown and matured to become a tool that can handle any amount of data, limited only by system resources, provided that data is loaded judiciously.

You need a specialist that understands data, Excel, VBA and databases in depth.

What computing short courses will provide required knowledge?

A computing short courses student that has taken Excel, VBA and Database short courses will be able to design and implement a system that can import any amount of data from a CSV file into Excel and store it in a large database. Our recommended learning path would be:

Conclusion

A robust automated system could have been created using Excel and VBA to handle importing of COVID-19 test results from CSV files of any size. Both Excel and VBA are able to handle this, if the automation is correctly designed, implemented and tested. A computing short courses student that has studied City’s Applied MS Excel for Business course, VBA in Excel series of short courses and optionally the Database Design course would have enough knowledge to design and implement such a system.

Furthermore, if end users of the NHS Test and Trace system were trained on the way the CSV importing automation works for COVID-19 test results from privately-run labs, they would be able to alert immediately that one of the CSV files could not be handled by the automation, saving precious time in the tracing of contacts of infected individuals.

We conclude that it was definitely human error that caused the COVID-19 positive cases to be missed, either at the user level while importing the data, or at a developer level where limitations of Excel were not taken into account. A well informed and trained Excel VBA specialist would be able to design and implement a CSV import and conversion system correctly.

References

[BBC] https://www.bbc.co.uk/news/technology-54423988, retrieved 10/Oct/2020.

[Excel-limitations] https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3, retrieved 10/Oct/2020.

[gov.uk-note] https://www.gov.uk/government/publications/coronavirus-covid-19-testing-data-methodology/covid-19-testing-data-methodology-note, retrieved 10/Oct/2020.

[Guardian] https://www.theguardian.com/politics/2020/oct/05/how-excel-may-have-caused-loss-of-16000-covid-tests-in-england, retrieved 10/Oct/2020.

[IBM-Fortran] http://bitsavers.trailing-edge.com/pdf/ibm/370/fortran/GC28-6884-0_IBM_FORTRAN_Program_Products_for_OS_and_CMS_General_Information_Jul72.pdf, retrieved 10/Oct/2020.

[Mail] https://www.dailymail.co.uk/news/article-8805697/Furious-blame-game-16-000-Covid-cases-missed-Excel-glitch.html, retrieved 10/Oct/2020.

[Mirror] https://www.mirror.co.uk/news/politics/16000-coronavirus-tests-went-missing-22794820, retrieved 10/Oct/2020.

[Mirror2] https://www.mirror.co.uk/news/politics/spreadsheet-blunder-meant-48000-potentially-22797866, retrieved 10/Oct/2020.

[Sky-News] https://news.sky.com/story/coronavirus-data-can-save-lives-data-can-cost-lives-and-this-latest-testing-blunder-will-likely-prove-it-12090904, retrieved 10/Oct/2020.

[Telegraph] https://www.telegraph.co.uk/technology/2020/10/05/excel-error-led-16000-missing-coronavirus-cases/, retrieved 10/Oct/2020.

[wikipedia-csv] https://en.wikipedia.org/wiki/Comma-separated_values, retrieved 10/Oct/2020.

About the author

Dionysis Dimakopoulos is the subject coordinator for the computing short courses at City, University of London. He has been teaching Visual Basic for Applications in Excel since 2003. He is an experienced software engineer, IT integrations consultant and published researcher. He has decades of experience creating systems that combine the power of web services with the familiar interface of Excel for engineering or financial applications, or offer interoperability with Office and other applications. His latest work is on the Learning Designer, an open online learning design tool for teachers in all sectors of education and subject areas, used around the globe, where he is the lead developer.

Getting to know you: Our winter short courses open evening

City, University of London were proud to host our winter Open Evening on Tuesday 10th December 2019.

Our Open Evenings are a great opportunity to visit our campus and meet out tutors – and this December we had a great turnout of guests, looking to find out more about all the evening and weekend courses that we offer at City. We offer light refreshments and some free gifts to take away with you.

City Short Courses also offers a range of taster sessions – 40-minute classes to give you a flavour of what it is like to study at City. We are hosting our next Languages Taster Event on Wednesday 15th January 2020. We will be running taster sessions in seven languages – Arabic, Chinese Mandarin, French, Italian, Japanese, Russian and Spanish. Find out more and book your place on the webpage.

We have over 120 courses across subject areas – business, computing, creative industries, languages, law and writing. Next term starts Monday 20th January 2020, enrol online!

A taste of learning with City

City, University of London proudly hosted our first ever open evening and taster sessions event on Thursday 11th July 2019. Thank you to everyone who made the evening such a success and to all of our attendees – we hope that you found it interesting.

Throughout the evening we offered a series of 30-minute taster sessions in a select number of our short course programmes to give students a feel for what it is like to study a short course with us.

We started the evening on a high with a taster session in one of our most popular courses, Introduction to Programming with Python. Lead by programming expert, Philip De Grouchy, this session was packed out with young professionals looking to try their hand at coding.

Our digital guru, Elliott King, ran a parallel session in Strategic Digital Marketing, combining theoretical knowledge with step-by-step guidance on delivering online marketing campaigns.

Katy Darby lead an interactive session in Short Story Writing, for those looking to nurture their creative flare while Marian Wancio delivered a more practical course in Project Management.

Ping Chai, leading a Chinese Mandarin taster session

We also ran sessions in Immigration Law, Adobe InDesign, JavaScript Programming, Writing for Business, Writing for the Web, Curating & Exhibition Management, Japanese; and Chinese Mandarin.

Feedback from our attendees was overwhelming positive, with the vast majority stating that their questions were answered adequately by our staff. Our taster classes were also well received, rating the quality of the sessions highly.

However, there is always room for learning and improvements! As a result of our feedback, we intend to replace the current format with two separate events. In December 2019, we will be hosting a ‘meet our tutors’ open evening, an excellent opportunity to speak to our experts one-to-one about the wide variety of courses we offer at City. In the summer of 2020, we will be continue to run a full evening of taster sessions, offering a glimpse of what is it like to learn at City.

We will also be extending out taster sessions from 30 to 45 minutes to allow more time for learning. See the Visit Us section of our blog to find out more about our visitor events or book your place on our December open evening.

Older posts

© 2024 City Short Courses

Theme by Anders NorenUp ↑

Skip to toolbar