Class summary sheets - bringing in data from multiple sources to one easy PDF summary

(Not real data)

This was my first "big" coding project at the school.

The mission, as given to me, was to create a way for teachers to get a succinct, one page overview for each of their classes, with all the breakdowns required for their data analysis.

At the time, I had not yet begun to learn Python and most of my programming experience was in Visual Basic, Java, PHP and Javascript. As the data was Excel-based, my immediate thought was to use Visual Basic as I could then have the option of developing it into a full plugin in future (which was actually done by a colleague later on).

What was needed

They needed to know the number of students for:

• Progress (AOB), measured as A (Above target), O (On target) or B (Below target) and
• Attitude for Learning (ATL), measured simply by A, B, C or D.

They needed each of these for all students in the class, as well as broken down by:

• Gender,
• Pupil Premium status
• SEND status
• Attainment Banding (High Attainer, Middle Attainer, Low Attainer)

The data

The core student data came in from a SIMS report - Gender, Pupil Premium and SEND, as well as every student's class codes for each subject. The Student Services team then added columns to the sheet for each student's AOB and ATL going across and that was my starting point. The goal was to save teachers spending a lot of time filtering the sheet for different classes and trying to spot trends or areas for improvement themselves and give them an easy way to tell at a glace.

(Not real data - a mockup I made to demonstrate how the data came to me)

Creating the template

The first step was to programmatically create a template sheet - this way the code could be run just from the data above without needing to supply template files or anything to it (plus the template was very unlikely to change, and in fact hasn't in five years!)

Here is a section of this code:

and this is the template the whole thing created in a new sheet:

Creating the class sheets

The next step was to get a list of all the classes. To keep this simple (and make error checking easy) I created a temporary sheet called "UniqueList". I then iterated through each of the Subject columns from the main sheet, took each class code and put it into an array. This list was then put into column A of my UniqueList sheet which was filtered to only show unique values. Looking back and knowing what I have learned since, I know there are more efficient ways, such as checking if a code is already in the array before adding it.

I then went through the values in column A and for each class code I copied the student data for that class into a temporary sheet, using filters on the main sheet to get just the students I needed.

Filling in the data

Now I have the data for that class, I created a sheet, copied across from my template layout, renamed it to the class code and then just started filling in the data, simply using the COUNTIF function. Example:

Tidying up

Once that had finished running, all that was left was to tidy up! Delete the temporary data sheet, the template sheet and the UniqueList sheet and remove the filters applied to the main sheet.

Converting to PDF

Now that the program itself had finished, we wanted to have it all in PDF format for ease of printing. My template had already been exactly sized to fit on a sheet of A4, so it was simply a case of printing the whole workbook to a PDF! If I was doing this project again now, I would find a way to automate this part as well.

This was the end result:

(Again, this is sample, made up data)

Conclusion

The end result was something I was pretty happy with - 470 lines of Visual Basic that turned an Excel worksheet of incomprehensible data into separate, clear A4 summary sheets.

It saved the school a lot of money on a piece of analysis software we had trialled that didn't quite get the data we wanted out of it and it saved the admin team a lot of time preparing, formatting  and restoring data (when someone inevitably accidentally edited and saved the file!)

Finally, it saved the teachers a huge amount of time filtering, re-filtering and sorting data in Excel, trying to establish meaningful trends. Now they just had one sheet that gave them the most relevant data at a glance and was easy to compare with previous years or even other classes.

Not long after this, I began to help teach the new Computer Science GCSE. For this I had to teach myself Python, which is something I am very grateful for, as shown by the fact that every single one of my coding projects since has been done in Python!