[sims] Ideas for automating analysis of SIMS assessment data in Excel

[ctaylor]

Hi All,

 

Think this is a long shot but thought I would post anyway.

 

I run assessment at a large sec. modern and currently produce a report (ring bound booklet) containing lists of specific pupils (pupils on target 5A*-C, pupils with <85% attendance, pupils arriving with KS2 L4 etc) along with headlines and various bits of analysis.

 

I have an excel spreadsheet set up which has a tab for the raw data (targets and attainment) and these feed in to the analyses to give me the headlines etc. The arduous part comes in when printing the report. There are 20 focus groups in all, which we have to filter from the raw data to start with on whatever the criteria is, then hide out the columns we dont need, then rename the header to say the name of the group then print page by page. For 20 focus groups this takes about a day or so to produce the whole thing. This is far too long and I can't help thinking there must be a quicker way.

 

Any feedback or input would be gratefully recieved. I've spent so long staring at it I've lost any sense of objectivity or idea!!!

 

Charlotte

[vikpaw]

Are you using an autofilter on the focus group column?

 

I'm not good with them, but i'm pretty sure Pivot Tables will be part of the answer. When you set them up, and make an analysis, you can then easily clone / export it to a new sheet.

 

It really depends on your raw data and the headings that you need. But it should be possible to set up the header to reflect the cell you use to filter with.

 

Can you not set up 20 sheets, and send the data out of SIMS in groups or is that equally as arduous? I take it Discover isn't the answer to all your prayers on the analysis front...?

 

Would like to see an example of your sheet, even if it's just with 5/6 fake students. Not sure why you need to hide the columns, is it just subjects they don't study..?

 

Perhaps you could set up a new sheet that has if formulae to only display columns with relevant data. So you filter, move to conditional sheet and print that.

[NorthernSands]

Hi Charlotte,

 

Am I correct in thinking the criteria in the first paragraph (5 A* - C, <85% att etc) are the 20 focus groups? And I presume you want these groups to be dynamic (ie, if a student suddenly drops below 85% attendance you want them included in that group)?

 

Mmm, have you looked at Discover yet? I've not had much time to fully digest it, but it does have ability to create dynamic user defined groups. These UDGs should then be able to be used in AM7 marksheets which, using formula columns, can produce a fair amount of headline data (such as 5 A* - C).

 

What I'm suggesting (assuming Discover works as I think) will take a huge amount of time to set up, but once done, it would simply be a case of export > print (well, ish!). Can anyone else see where I'm going with this? Or is it just me? Again.

 

Failing that, use fancy Excel formulae and multiple worksheets to do the work for you. Unless you run out of nested ifs. Or is that just me as well?

 

Seeing the Excel doc would help (with names changed etc).

 

Pete.

[ctaylor]

Thanks for your replies.

 

NorthernSands - yes you would be correct to say that the groups are dynamic and that after every data collection the pupils change.

 

I've been trying to get Discover sorted but ICT are dragging their heels saying they need another install before we can have it. I do think this may be the answer and I would be more than happy to do the set up if it helped during the year.

 

Vikpaw - can you eleborate on - So you filter, move to conditional sheet and print that.

 

I've lost two admin assistants in the last few weeks and am drowning as you can imagine - everyone wants every type of anaylsis and I simply can't provide using this method.

 

Charlotte

[resin1]

I've lost two admin assistants in the last few weeks and am drowning as you can imagine - everyone wants every type of anaylsis and I simply can't provide using this method.

Charlotte

 

I was about to write a longer reply, but having seen that comment, I'd only offer that you might find a way out of that situation, which is so common in so many schools, by getting a suitable dedicated tool in place. Although it of course means spending money when there is so little about, products such as 4Matrix seem very cost effective to me considering the amount of time many of us spend either becoming Excel "experts" and continually try to meet all the internal requests we get or wrestling with limited tools within our MIS that may help with the calculation side of things but are not that hot on presentation.

 

Sorry - doesn't help the immediate predicament, more thinking long term since you have to repeat that process time and again.

[ctaylor]

I have looked at 4Matrix and hated it. Looked at SISRA and loved it but couldn't get financial backing, hence I have tried to become an Excel expert which I am finding is starting to limit me. Discover may well be the key but I've been told that won't be until Summer Holidays so nothing I can do for the rest of the term other than work longer hours.

 

Just wanted to bounce around some ideas about how other people produce a report for their SMT etc.

 

Charlotte

[vikpaw]

So assuming you have some columns with various data like attendance, A*-C etc. Your filter them based on this data right?

I first thought you did simple filters like reg group / class group etc. However, you can still filter, i presume by sorting the rows...?

 

Once filtered, you need to remove other columns that may have no grades for those kids ... {i'm guessing so stop me if i'm on the wrong path}.

 

What i'm suggesting is that you set up another marksheet. put in the header. put in formulae which pulls through the relevant columns that you would normally leave behind and ignore those that you would hide. Make many of these sheets so they pre-populate once you order your first list.

 

You could even put a formula in to only pull through students onto the list if they meet said criteria. so "if(attendance < 85 , name, blank)" and just sort the rows in that sheet to pull data to top before printing.

 

The only issue would be if the columns you need to hide are also dynamic based and would need some more complex formula to pull through. I can't see how they would be dynamic though as that would make the hiding process almost a line by line process and then you really need to just employ some trained monkeys to help you out.

[resin1]

Very common story unfortunately isn't it Charlotte - you find something that will save many many person/hours and deliver exactly what SMT and teaching staff need, and decision makers perhaps prefer to be seen to be keeping costs down. Working longer hours is never the answer in my opinion as is simply helps to hide a problem.

 

Good luck with it though. Discover will be worth looking at, although I'm guessing you may have to implement a lot of the calculations you are currently doing in your "analysis", back inside SIMS for it to be able to "present" the results of those and then allow interrogation by focus groups etc.

[NorthernSands]

Fortunately the SMT in my current school aren't there yet. They've barely moved beyond Collect Data > Report to Parents > Pat selves on back! I'm dragging them forward, though, kicking and screaming.

 

In my previous school I produced different things for different people. I'd produce year based headline figures then work with the Dep Head in charge of Assessment on providing written analysis commentary to go with the data (ie no individual student data). For HOYs and HODs I'd produce student lists with all relevant data and some headline figures for their specific cohorts / subject areas only. Teachers would have a set of pertinent marksheets available to them with a range of data available. It all came from Assessment Manager (version 5 and 6 back then), with very little work done in Excel. It just worked. If the HOYs / HODs wanted to see which students had <86% attendance, well, they had the % attendance column and the option to use the filter function in excel. Same for 5 A* - C, FSM, KS2 Levels etc...

 

In my experience, the further up the ladder someone is when they request data, the less they know what they want. I give them what I think they want and work from there. I'm usually right. A lot of people want data for the sake of having data. But they don't think about the poor sap (thee'n'me) who has to spend loads of time preparing it for them. And if we make the mistake of doing a good job, they just want more. I've historically made the mistake of being too helpful. Never again!

 

That doesn't really help the OP, though! but an anonymised copy of the spreadsheet would be very useful.

 

Pete

[NorthernSands]

You could even put a formula in to only pull through students onto the list if they meet said criteria. so "if(attendance < 85 , name, blank)" and just sort the rows in that sheet to pull data to top before printing.

There is another way to bring through rows that only match certain criteria, but I can't remember the way I did it. It essentially took one worksheet of the whole school (or just a year group) then brought through only one reg group to a specific worksheet. Rinse and repeat on additional worksheets for the other reg groups. It used formulae, rather than VB, and it worked. I could paste in a new list of students and, if there'd been reg group changes, the individual worksheets would alter their lists dynamically, which would in turn change graphs that were based on the sheets.

 

In the OPs case, each sheet could, instead of being different reg groups, be other criteria (<85%, 5+ A* - C etc). Each sheet would also have it's own header, and only bring through the columns it would need. Job done?

 

Now if I could only remember how I did it... I'm sure I could work it out again.

[ctaylor]

Yes that sounds like the sort of thing I want to achieve, then it would be set up in advance of the report being needed.

 

I thought maybe a conditional vlookup would do it but then I'm not sure it's worth the risk because I find them to be temperamental.

[Metchley]

I'd write something in VB to do the job.

Treat the Excel file as a database, extract what you want with SQL, sort, print.

Would take about a week to write it!

But it might save time in the long run.

[ctaylor]

I would like to be able to write something like that but where do I start to learn?

[dhicks]

I'd write something in VB to do the job.

 

It's a good plan if treated as a proper software development project. However, the real issue here isn't that the tools aren't available to do the job but that the people who need to use them (your school's management team) don't know how to use them. For that matter, your management team probably can't even define what it is, exactly, that they want in the first place - they have a picture in their heads of some kind of snazzy-looking statistical analysis, probably involving graphs, and that's about it.

 

I spent the whole summer break for two or three years running at my last school trying to sort this issue in one way or another - either via a number of large Excel files, or by writing my own Python-based analysis package. The admin staff couldn't manage to use the Excel files on their own, hence the analysis package that was intended to simply read a bunch of data in CSV format and produce a bunch of graphs. Unfortunatly, being a school, I had a bunch of other jobs to do at the same time and the scope / details of the project kept changing as I worked.

 

This is the kind of project that really needs to be handled by an outside agency of some kind that the school's managment team can't distract. That could be an open source project of some sort (one of the duller ones) or a commercial enterprise - hmm, maybe a "cloud" based statistical analysis tool for schools?

[pcstru]

We don't use SIMS but we do have some nice results analysis done by excel using data pulled from CMIS. As an example We present 4 tabs to users. For KS4 :

 

On the first tab they can select the subject, an extended attribute (such as gender, ethnicity, FSM etc), the Assessment (period), and whether A-C calculations should include C/D (they can enter "C/D" into the system as a target). What they select here drives the data displayed.

 

The first tab displays data for the chosen subject at the school level broken down by tutor group, hall and the 'extended attribute'. A picture always helps - hopefully sufficiently anonymous but still informative :

 

 

The columns show; what we refer to as VA (which is just a delta between the target and the actuals in whole grades with positive numbers being actuals exceeding targets), MTG (Minimum target Grade) & WAG (last assessed grade) are analysed by the numbers and % achieving A-C. Moving along the sheet, the columns are repeated for the 'extended attribute', so people can look at the data broken down by gender, FSM, ethnicity etc. At the bottom is a simple whole school summary which just shows numbers achieving 5A-C's including english and maths.

 

The second tab follows exactly the same format but instead of tutor groups, looks at it by teaching group, again with the breakdown by whatever attribute has been chosen.

 

The third tab shows individual student results, MTG/WAGs, VA for the chosen subject, Total A-C's, 5 A-C's including English and maths, and some A-C indicators showing students to target (those who with a little more effort should be able to get 5 A-C including English and Maths). It also shows MTG's and WAG's for all subjects and can be filtered and sorted by teaching group, tutor group, attribute etc.

 

The fourth tab shows by tutor group a 5 A-C analysis over all subjects, again with the horizontal repeat for analysis by attribute.

 

Our KS3 sheets are very similar, comparing WAG/MTG to give a delta in sub-levels and showing these by tutor and teaching group with horizontal analysis by arbitrary attributes but missing out the A-C stuff (obviously).

 

I think this is a reasonably good compromise which allows people to see a lot of information in a reasonably efficient manner and do most analysis they might need for specific groups. There is very little actual VBA involved so changing subject or attribute is very fast (~1 second and all analysis is up to date). If someone wanted to print the analysis they needed, they can but they are better off with the sheet.

[ctaylor]

Thanks for your example - looks very nice and I can see that it would be very useful if people were using it themsleves.

 

I would be prepared to spend the time and effort coming up with something that would be a long term solution, it's just very daunting as I've hardly used VB let alone anything else.

 

Charlotte

[pcstru]

The only VB in the spreadsheet is to populate the teaching groups when the user changes subject and attributes when the user changes that. Everything else is done with standard excel functions -most of the 'clever' stuff folks would normally use SQL for is done with VLOOKUP and HLOOKUP. I've been meaning to document the process for some time but ... time ... where does THAT go.

[ctaylor]

Can anyone recommend a course on VB I could go - I think it could have many applications in school.

[Brucy]

Where abouts in the country are you based? have you got any programming experience?

[ctaylor]

No real programming experience but I'm good with stuff like that and I'm willing to learn.

[dhicks]

Can anyone recommend a course on VB I could go

 

No - the best way to learn to program in any language is going to be to simply use it to do something. Writing a statistical analysis package should be a pretty good place to start. If you want to do a course on anything, do a course on statistics.

[pcstru]

No real programming experience but I'm good with stuff like that and I'm willing to learn.

 

If you are good with stuff like that then you might be better off getting a book (search VBA on amazon) or try some of the many online tutorials (search VBA Tutorial on your favourite search engine).

[ctaylor]

Thanks - will do. I think it's probably worth the effort to learn something rather than get someone to do it.

[NorthernSands]

@ctaylor: Have a look at the attached. I've re-worked out how I did what I did all those years ago. It essentially uses vlookup(), row() and indirect() with if() and iferror() thrown in for good measure. The trick is get vlookup() on the row after a match to start it's array below said match in the raw data. So, when it's found a match, the next row starts looking for it's next match after the first match in the raw data. Clear as mud?!

 

It has one major limitation that might mean a switch to lookup() rather than vlookup(). The latter will only lookup data at the left of an array, whereas lookup() can be set to lookup a match anywhere. In English, for vlookup() to work you'd have to have your columns for the different focus types to the left of the rest of the data (they have to be the left most column of the vlookup() array). Actually, I don't think that made it any clearer!

 

Anyway, have a look at the attached...

ctaylor1.xlsx

[Brucy]

Indirect() (which allows you to reference cells via text) is really useful, for example I use it with a dropdown box to allow me to alter graphs on the fly (essentially telling the graph to look at different rows/columns in a table)