Category: Data

An Excel calendar

Was asked the other day to create a spreadsheet for staff to record the leave on.    Sounded simple but as with most IT project scope creep made an appearance and the simple specification became a bit more complex.

Apparently the sheet didn’t allow the patterns of leave, etc to be easily viewed across the year.   This was due to having the date as the header and also having to have sufficient space for staff to enter their leave info.     Seemed simple enough to solve just by adding a second sheet with colour coding, referencing the first sheet.   This would allow for each column to be thinner thereby allowing for a larger date range to be viewable.   Job done?

Apparently not, the next addition to the spec was the need to be able to pick out a week and view just that week.   Now this was going to put my Excel skills, and my google searching skills to the test.

The answer lay in a couple of formula which I hadn’t used before:

Indirect

This formula allows me to retrieve the contents of another cell based on a cell reference.

=INDIRECT(Sheet1&P7)

The above retrieves the contents of a cell from Sheet 1 of my workbook, with cell P7 containing a reference.  E.g. P7 might contain !A1 leading to INDIRECT(Sheet!A1) would would therefore retrieve the contents of cell A1 on Sheet 1.

So using this I would make my View Week sheet retrieve the appropriate weeks data from the Master sheet by basically building the appropriate cell reference.   So if Monday of Week one was in column B, we know week two would be 7 letters further on.

Substitute

For the above to work I need to be able to work out the row letter based on a week number.    The Substitute function allows me to convert a number to an equivalent letter.

=SUBSTITUTE(ADDRESS(1,($E$2-1)*7+1,4),”1″,””)

The above takes a week number in cell E2 and from this works out which column the Monday for that week can be found in.   Note: the above dosents work for Week1 as part of the week was in 2018 rather than 2019.  This could be easily fixed.

Text

The final function I needed to use was the Text function.   Reason I needed this is I wanted to create a pull down list showing the week number and week commencing date.   The issue is I had both pieces of data in different fields and concatenating a date doesn’t work; it shows you the number equivalent of the date.      The Text function allowed me to convert the date into a string which could then be included in a concatenate function.

TEXT(B6,”dd-mm-yyyy”)

The above takes the date value in cell B6 and converts to a string of the date.   The above was then placed inside a concatenate function to combine with the week number as below:

=CONCATENATE(“Wk”,A6, ” – “, TEXT(B6,”dd-mm-yyyy”))

The above takes the week number in cell A6 and the date in B6 and combines together ready for display in a pull down list.

I must admit it took a little bit of thinking and a little but of work to get this spreadsheet working however I did enjoy trying to hack together a solution to this problem.     You can access the final calendar here.  I have unlocked all sheets for your editing, plus have made the working cells visible where in the final version I set the font colour as white to make it invisible.

 

Stream Transcripts

Have played around with Microsoft Stream a little recently but only briefly, when I spotted someone having issues exporting the automatically created transcripts.     The issue is the transcripts are formatted with time codes and the person wanted only the text of the transcript without all of the timecodes.   Removing manually was a pain but thankfully the format of the transcript files appeared uniform in nature…….time to roll out the Macro code and some VBA

As such I put together the below simple VBA code to delete the timecode lines and leave only the text.   Will admit the code isn’t particularly tidy but my intention was to simply hack together a solution as opposed to creating elegant code.

The Code:

Sub Macro1()

Dim introw As Integer

introw = 7

Do While Cells(introw, 1).Value <> “”

Rows(introw – 2).EntireRow.Delete
Rows(introw – 2).EntireRow.Delete
Rows(introw – 1).EntireRow.Delete
introw = introw + 1

Loop

End Sub

Now I will admit that I havent tested the code on more than a basic level, having run it on a couple of transcripts I have.   Please let me know if you have issues with it.

*Update: It would appear WordPress converts the minus ( – ) character in my code to a similar looking character in the above.   As such you may get a syntax error if copying and pasting.  If so just delete and replace the minus with the correct character in your code.

Microsoft PowerBI

Microsoft PowerBI is an excellent tool for use in presenting and analysing school data, allowing staff to explore and interact with data which traditionally may be locked away in complex and very flat spreadsheets.

Schools have access to a massive amount of data.   This includes information about each student, academic data from assessment and testing, or from professional judgments made by teachers.    Secondary schools will also have baseline data such as the Centre for evaluation and monitoring (CEM) MIDYIS or ALIS data.   You will have data on attendance and on where students have been acknowledged for their efforts, or where they have had to be warned regarding poor effort or behaviour.  The above only scratches the surface of the available data.   For me this has long been a challenge in that all of this data is usually in difficult to read spreadsheets, where without well developed skills in using excel for example, trends and patterns will not be easy to identify. Even with well developed spreadsheet skills, attempts to analyse and interpret will be time consuming.  In addition it is often extremely difficult to bring together data sets such as looking for possible links between academic data, behaviour, attendance, etc.

PowerBI allows you to take all of this data and start exploring it.    You can create reports which present the data in simple graphical form however allow for the data to be explored.    For example you might display the count of behaviour issues by gender.   Clicking on a given gender would then filter to this gender, thereby allowing you to see other graphs such as academic performance or attendance by the selected gender, but also still showing the full cohort average, thereby allowing you to see where a particular subset of students vary from the average.

The above example shows how PowerBI displays focus on a given subset of data within graphs.  The dark pink bars relate to the selected focus whereas the light pink show dark for the whole data set.

Clicking other graphs would then allow you to easily explore other subsets of the data.   You can create reports allowing filtering by SEN status, native language, gender, subject, year and any other fields for which you have data.

PowerBI also comes with its own analytics engine which will analyse your data and identify where subsets of your data deviate from the average.     It is clear Microsoft are continuing to develop this functionality as when I first used this it identified correlations which were obvious and therefore of little use however more recently when I have used the analytics it has identified some more useful correlations.   I suspect this area will be further developed over time bringing greater potential for how it could be used.

The one drawback with PowerBI at this point is licensing.    For free you can create your PowerBI reports for individual use or can share these as files for viewing in the Desktop application complete with full editing rights however the main potential as I see it is to centrally create PowerBI reports and share them via Sharepoint so that staff can access as and when required but without the ability to change the report and without the complexity of the desktop applications interface.   You basically present them with a web page of the data for staff to interact with and explore using the graphs and other visuals and filtering provided by the person who creates the report.   For this Microsoft are currently charging a per user per month cost.      Given the potential power which PowerBI could provide to schools my hope is that Microsoft will eventually reconsider this and make PowerBI more affordable for use by schools.

PowerBI for me is about putting school data in the hands of staff in a way that is quick and easily to interpret plus usable.   It is about being able to explore data by simply clicking on individual elements and about using the data we already capture more efficiently.    With carefully crafted reports, generated through discussion with staff, the time taken to manage and analyse school data can be reduced, yet staff can be empowered to know and use the available school data appropriately.   If you haven’t tried PowerBI with you school data I would recommend you give it a try.

 

Microsoft Forms: Sharing access to form data

Was playing around with MS Forms yesterday when I came across a feature I wasn’t aware of.   Basically you can create a link which will allow others to access the a summary of responses to a particular from.    I have been looking for this functionality for a while as it is often useful to allow multiple people to be able to review responses without allowing them access to change the form.

To do this simply create your form then click the responses tab.    On the responses tab click the … icon to reveal the option to “Get a summary link”.

You will then see a dialogue complete with a link to share with those who you wish to be able to access the data.

Now the above is useful however I must admit I would prefer the ability to access the data as a spreadsheet as opposed to as a summary list.   It would also be better if forms could provide specific access rights on a user level as opposed to in a link.   This is a feature I would also like to see where you are providing collaborative access to a form.   Here you are also able to create a link only, rather than to assign individual user level access.   Hopefully Microsoft are working on this.   For now, however, if you want to share responses as a spreadsheet or if you need user level access control you will need to look at using Google forms instead.

GDPR for schools

GDPR is now in effect.   As such I thought I would share some thoughts and advice on how schools might tackle some common issues which might arise.

 

USBs

The issue with USB, or other removal storage device, use in schools is that they are easily lost or stolen, plus even when data is deleted it may be possible to recover it.    In a time now passed, USBs were a near essential piece of kit in allowing sharing of data, lesson materials, etc, however now we have Office 365 and the G-Suite for education there is no need.    Using OneDrive or GoogleDrive users can now easily share files all within the confines of the schools IT systems and control.  As such my prevailing advice would be to include reference to avoiding USBs use for personal data in your Acceptable Usage Policy and in awareness or cyber security training.  I stop short of preventing USB use simply because some resources are still provided on USBs and they are still so very common.    They also continue to be useful for sharing images or video footage or for other large files.

Personal devices

Before discussing personal devices of staff I think we need to be clear on what constitutes using a personal device for school purposes.   As far as I am concerned, simply setting up email on your phone constitutes its use for school purposes as it will store your emails and any included school data.    Some, at this point, would suggest personal devices should be banned however I think this is a little heavy handed.   The benefits of staff having their email on their phone are huge.   Banning personal devices also totally removes the potential benefits associated with a BYOD (Bring Your Own Device) environment including the personalisation benefits which arise where the device belongs to the user and therefore is set up by them to meet their needs and preferences.    My approach again, like with USBs, is to ensure coverage of personal device use is included in the schools Acceptable Usage Policy plus ensure it is also covered in any training provided to staff.     I would also make sure the appropriate policies indicate a need to ensure personal devices have appropriate security such as device encryption plus passcodes, passwords or biometrics enabled.    There should also be a requirement for staff to report a lost or stolen personal device where it was setup or used to access school data or systems.

Photography

I have discussed photography before; you have read the post here.    It continues to be a concern.   The issue for me is that we all now carry a camera with us in our smart phones so it is easy for us to capture images for sharing via social media, email, etc.    There are lots of benefits in this, particularly the potential to capture impromptu photos which can be used in teaching and learning.    Schools need to provide some guidance on what is acceptable around the taking of and using of photographs.  This could be contained in the acceptable Use Policy or in a separate Photography policy.    Where staff use their own phones for taking photos this should be covered by the use of Personal device in the AUP as mentioned above.

Third Party sites

This is most likely the biggest area of concern as far as I see it.   Schools must know where they are sharing data so a process must exist to ensure that any sharing of student data is logged.   Schools must also ensure that the sites to which data is shared are secure.    Generally this will take the form of a review of the sites privacy or data protection policies to ensure key points in relation to security and sharing of data are covered.    Thankfully in most cases the sharing of data will be limited to a pupil’s school email address and name for the purposes of providing them an account to login to a particular service.   As such the risk associated with a breach is low and therefore a simple check of the services policies should suffice.    Records of these checks should be retained.    Where more data is being shared, such as date of birth, age, SEN info, etc, more questions should be asked of a service including if they carry out penetration testing and/or external auditing around their security, what their breach notification policy is, etc.

There a couple of third parties which all schools are likely to have to share with such as examination boards, local authorities or councils, social services, etc.     For these I think consideration should be given as to how data is shared making sure student details are not emailed unencrypted to such bodies.    Where possible an online portal provided by the body should be used and where this doesn’t exist an encrypted email service such as Egress might be considered.    I think schools should also review the data protection policies or privacy notices of these bodies, as they would do for third party websites using in lessons, just to show that they have done some due diligence.

Risk Assessment

I think a very important activity for a school to undertake is a risk assessment.   This should indicate the risks that are perceived and also any mitigation which has been taken, or may be taken in future.    Having a risk assessment in place, which is regularly reviewed and updated, can go some way to show that the schools is aware of risks in relation to IT and school data and is actively seeking to minimize risk where it exists.   This helps to prove “privacy by design”.

Conclusion

There is now single blueprint for being GDPR compliant.  It depends very much on the school and its processes.   The key for schools is to able to show that every reasonable measure is being taken and that decisions around risk associated with data processing or sharing are carefully thought through with evidence retained of the decision making process.

GDPR should not be a panic activity to try and get things “right”.   GDPR is an ongoing process showing a focus on data privacy and security at the heart of a schools operation.    All schools need to show not just how they “have” complied with GDPR but how they will continue to ensure GDPR compliance and treat the data of their students and other stakeholders with the utmost care.

 

PowerBI and School Data

Ever since I started playing around with PowerBI I have found it to be very useful indeed and I must admit that I am most likely only scratching the surface.

I came to experiment with PowerBI to try and address some issues I see with data management.    School data is often presented in colour coded spreadsheets showing student performance against baselines for example.   Different sheets are used to present different views on the data such as showing the performance by subject, by gender or the performance of students by SEN status or by EAL status.   Each additional view on the data, of which there are very many, presents us with another sheet of data.  The data is often presented as flat tables of figures however in some cases may involve pages upon pages of different graphs and charts each showing different views on the available of data.   The logic here being that each additional view on the data gives us more data that we can interpret and therefore a greater opportunity to draw insightful conclusions and from there develop actions.   I believe the reality is the reverse of this.

My belief is that teachers and heads of department don’t have a lot of time to analyse and interpret data, and therefore presenting them with so much data is counterproductive.  Having so many different views on the data presented at once also is difficult to process and to understand.   This in turn leads to either ignoring the data altogether or to giving it only a very cursory glance.   For those that love data it may lead to excessive amounts of time spent poring of the data, to data overload, where time spent planning actions, as opposed to analysing data, would be more productive.    As such I subscribe to the belief that “less is more”.

This is where PowerBI comes in.    PowerBI allows me to take my mountains of spreadsheet data and present it in a very easy to digest graphical format where each of these graphs and charts are interactive.    In PowerBI rather than one sheet by subject and another sheet for gender based data, you have just one set of graphs and charts.   You would just click on a gender or select a gender and all the graphs will change to show the results for that gender.   You might then click an SEN status to see how students who are male with SEN needs are doing compared to students on average.    This means we can combine all our different views which are normally represented by different sheets on a spreadsheet into a single set of graphs and charts.   The user then accesses the various views of the data by clicking on and through these graphs and charts.

The benefit of PowerBI is the ability to dynamically manipulate and explore the data by clicking through various graphs and filters.   You develop an almost tangible feeling for the data as you explore through it.   This is something that flat spreadsheets, even if graphs are included, lack.   Also, as you have less to look at, in one set of graphs rather than pages and pages of them, you have more time to explore and engage with the data.

The one current drawback to PowerBI is simply cost.   It is free to use as an individual both web based or via a desktop application, and you can share via sharing desktop app developed BI files however if you want to share via the web platform or if you wish to publish internally via SharePoint you will need a Pro license for each user.    Where you are sharing with a large number of users, even at educational pricing, this can become expensive.   Hopefully this is something Microsoft will be looking at and can resolve in the near future.

Schools continue to be sat on mountains of data.    PowerBI is a tool which allows us to present this data in a more user-friendly form which then allows it to be easily explored and manipulated, allowing more time to plan actions and bring about continuous improvement.  If you haven’t already done so I definitely recommend putting some of your school data in PowerBI and having a play with its capabilities.

GDPR and photos around school

Recently a member of staff popped in to discuss how she would like to share photos of a school sporting event with the various schools which were involved.   This got me thinking about GDPR and the implications for events and photography at such events.

Firstly, let’s consider the photos themselves.   They might show groups of students involved in a sport or gathered at the start or end.   They might also include spectators who attended the event including parents or visitors to the school.   My first piece of advice here is simply to ensure that it is clear to people that photography will be taking place and that such photos may be used by the school for various purposes including newsletters and other marketing or publicity materials plus that they may be shared with other organisations involved in the event such as other schools.    This notification can either be put on programmes or event marketing materials, or can be made clear at the event itself via posters or other displays.   I believe this should be sufficient as gathering specific consent from all in attendance would be impractical plus where consent is not provided, avoiding including individuals in action event photography would be very difficult indeed.    Taking a risk based view, given that no names are attributed to the photos, and therefore individuals are not clearly identifiable I see the risk of taking photos as events to be low.   As such I see the provision of notices of the intention to take and use photos as sufficient.

Once we start identifying individuals in photos, possibly by naming them, or given that the photo is of a small group of individuals who therefore are more identifiable, then I think we would need to look to have consent or some other basis for processing the data.    Schools usually have such a permission form or other method to gather permission from parents to use photos of children in their materials.  Key here is to ensure that a permission form makes clear the purposes for which photos might be used. E.g. marketing purposes, around school for display purposes, etc.

When the staff member popped in, the issue of event photography highlighted the inaccuracy of the frequently used term “GDPR Compliance”.    The term “compliance” to me conveys a sense of a binary outcome, either we comply or we don’t.    The issues in hand when looking at GDPR are not so clear.   Does compliance mean seeking permission from every individual in a photo, including members of the public?    I would think not.    As such I continue to believe in the need to take a measured risk based view on how we manage data and on our preparations for GDPR.   Where a risk exists, we need to decide whether we accept the risk.   If we do not we must seek to mitigate the risk through permission forms and notices in the case of school photography, to the point that we are then happy to accept, either this or we stop taking photos.

GDPR continues to result in confusion and contradictions of interpretation.   We seek the way, the one way, the best way to achieve compliance yet every school is different plus interpretations and attitude to risk vary.    For me the key is simply to consider your own environment, the risks and your schools appetite for risk, and to act from there.

 

 

GDPR: Third parties and training

As GDPR approaches I thought I would share some thoughts.   Now I must admit to not being a GDPR expect, instead the below represents my thoughts taken from the perspective of managing the prevailing risks around GDPR.

Two issues which currently occupy my thinking in relation to GDPR are managing the use of third parties which either supply software which is used in school or which provide a service where they store school data outside of the school.    Another issue which is currently at the front of my mind is the issue of awareness training and how we ensure staff are suitably informed and aware of GDPR, its implications and particularly what it means for them.

Third Party solutions

Schools may make use of third party software within the school, some of which is locally hosted and stored in the school and some are cloud hosted.

Locally hosted

Locally hosted solutions might include the school management system.    In these cases, we are relying on the third-party vendor ensuring that the software they have created has adequate security measures in place to protect any data held within it.    From a GDPR point of view schools need to show their efforts to comply and in this case, I would suggest the easiest way is to ask third party software vendors to provide details of how they have ensured the security of their product either through their policies or through independent reviews such as audits, vulnerability or penetrations testing.    Although the school is responsible for the security of the infrastructure on which the solution resides, it is the vendors responsibility to ensure the security of the platform itself, independent of where it is hosted.

Cloud hosted

Where cloud hosting is used we have the same issues as for local hosting, in that the vendor must have ensured the security of the platform, however we have the added issue of the vendor supplying the hosting and the infrastructure on which the platforms sits.  My first port of call in examining third parties is their policy documents looking specifically at any GDPR, Data protection, privacy, data privacy or information security policies they may have.    In the best cases this will address issues around security of data, sharing of data, deletion and retention of data.      In my experience, most vendors will quote the security compliance of their hosting service somewhere in their documentation or in response to questions on security.   This usually addresses physical security concerns in that the larger data centres must have tight security to comply with the relevant standards.   This still leaves a requirement to ask questions around business continuity and disaster recovery, in what processes the vendor has in place in the event of a serious incident.    It also leaves questions around ensuring the security of the network on which the service is hosted.   Like with local hosting we can address this by asking questions around any penetration testing or external auditing which has been conducted.

Breach, security incident or vulnerability notification processes are also an important thing to look for across both local and cloud hosted solutions.   If a service is handling student data it is important to know that they have a process in place for notifying service users if an incident occurs or if a vulnerability is identified plus that they have a clear timeline and method of notifying users.

Awareness Training

I think a key aspect of GDPR is making sure the overall school community is aware of the new legislation and what it means for them.   As such training is a key feature of preparations.    I know many companies and individuals are offering training ahead of the introduction of GDPR however I think it is important to establish the purpose of training.   If the purpose is simply compliance then an annual presentation to all staff will suffice as it will provide that all staff have received training.  The issue here is that staff in schools are very busy and therefore the content presented to them is unlikely to stick.   Equally an online resource in my opinion has the same limitation.   The staff will complete the materials however little will stick.    For me the key is a multi-honed approach using various delivery methods including whole school sessions, sessions where discussions and materials are disseminated to department level, broadcast communications such as email campaigns and online training materials.    An awareness of GDPR and more importantly an awareness of the risks associated with processing data needs to form part of the culture, “the way we do things around here”.

Conclusions

GDPR is now fast approaching and the above are just two issues out of a myriad of issues.   Not mentioned above are the implications around developing appropriate privacy notices, the issue of establishing data retention plans, dealing with subject access requests or requests for limitation of processing, handling requests to be forgotten, handling services where data is stored outside the EU and the issue of identifying the legitimate reason or justification for possessing.   The GDPR rules are complex to implement and my advice on this continues to be to take a risk based approach.   For me, currently, the two items above in third parties and awareness training, represent to of the big risks.

 

 

 

School Data: The tip of an iceberg

Schools gather a wealth of data in their everyday operation, everything from attendance information, academic achievement, library book loans, free school meals and a wide range of other data.    We use this data regularly however I think we are missing out on many opportunities which this wealth of data might provide.

The key for me lies in statistical analysis of the data looking for correlations.     Is there a link between the amount of reading a student does as measured by the number of library loans and their academic performance for example?     Are there any indicators which might help is in identifying students who are more likely to under perform?

The issue here is how the data is stored.   A large amount of the data is stored in tables within our school management system however no easy way exists in order to pull different data together in order to search for correlations.    I can pull out data showing which students have done well, which subjects students perform well in, etc. however I can’t easily cross link this with other information such as the distance the student travels to school or their month of birth.    Some of the data may exist in separate systems such as a separate library management system, print management system and catering system.    This makes it even more difficult to pull data together.

A further issue is that the data in its raw format may not make it easy for correlations to be identified.    Their postcode for example is not that useful in identifying correlations however if we convert this to a distance from the school we have a better chance of identifying a correlation.

In schools we continue to be sat on an iceberg worth of data although all we can perceive is that which lies above the water.   We perceive a limited set of possibilities in terms of what we can do with the data.    Analysing it in terms of pupil performance against baselines with filtering possible my gender, SEN status and a few other flags however given the wealth of data we have this is just the start of what is possible.    We just need to be able to look below the water as the potential to use the data better and more frequently is there, and in doing so we may be able to identify better approaches and more effective early interventions to assure the students in our care achieve the best possible outcomes.

Schools, data protection and online services

As we make greater use of technology in our schools we make greater use of online services.   We might make use of an online communication tool to improve on communications with parents.   We might make use of Google Apps or Office 365 to allow staff and students to have cloud storage so they can access their files when away from the school or on any device.    We might engage with an online maths tutorial site so students can undertake self directed study online and further develop their maths skills.    We might make use of a site to manage trips or resource bookings within our school.    The number of online services we are using in schools is increasing and therefore we are sharing more and more data with online service vendors.

The above is important to note given the new general data protection regulations are speeding towards us.    These new regulations will come into operation in May 2018 and will put a focus on all organisations to prove that they comply.     It is therefore important that all organisations including schools get a handle on the data which they have and how it is stored and processed.     For schools part of this includes examining where third party services are being used such that the schools data is processed and/or stored by these service providers.    We need to be asking what these service providers do to ensure the security of our data.

To aid the above, the need to review third parties, and the increasing use of third party online sites, the government has created their Self Certification process for vendors to self-certify their provision in relation to data protection where they offer cloud software services for schools.    You can view this here.     The thing that worries me is that as I write this there are only 38 vendors listed which appear to have submitted a self certification.     This represents only the very very tip of the iceberg which represents the vast range of services being used by school.

We all need to push vendors to answer questions in relation to the protection of our school data.   We need to push them to self-certify and to share what they are doing.   We need to ask the difficult questions now before they are asked of us later.

Have you considered the data protection of school data on third party services lately?    It is time you did!