+ Post New Thread
Page 1 of 3 123 LastLast
Results 1 to 15 of 31
MIS Systems Thread, Ideas for automating analysis of SIMS assessment data in Excel in Technical; Hi All, Think this is a long shot but thought I would post anyway. I run assessment at a large ...
  1. #1

    Join Date
    Jan 2011
    Posts
    28
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Blog Entries
    1
    Rep Power
    0

    Ideas for automating analysis of SIMS assessment data in Excel

    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

  2. #2

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  3. #3
    NorthernSands's Avatar
    Join Date
    Mar 2011
    Location
    Qatar
    Posts
    134
    Thank Post
    21
    Thanked 19 Times in 19 Posts
    Rep Power
    10
    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.

  4. #4

    Join Date
    Jan 2011
    Posts
    28
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Blog Entries
    1
    Rep Power
    0
    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

  5. #5

    Join Date
    Dec 2009
    Posts
    59
    Thank Post
    2
    Thanked 8 Times in 6 Posts
    Rep Power
    14
    Quote Originally Posted by ctaylor View Post
    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.

  6. #6

    Join Date
    Jan 2011
    Posts
    28
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Blog Entries
    1
    Rep Power
    0
    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

  7. #7

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  8. #8

    Join Date
    Dec 2009
    Posts
    59
    Thank Post
    2
    Thanked 8 Times in 6 Posts
    Rep Power
    14
    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.

  9. #9
    NorthernSands's Avatar
    Join Date
    Mar 2011
    Location
    Qatar
    Posts
    134
    Thank Post
    21
    Thanked 19 Times in 19 Posts
    Rep Power
    10
    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

  10. #10
    NorthernSands's Avatar
    Join Date
    Mar 2011
    Location
    Qatar
    Posts
    134
    Thank Post
    21
    Thanked 19 Times in 19 Posts
    Rep Power
    10
    Quote Originally Posted by vikpaw View Post
    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.

  11. #11

    Join Date
    Jan 2011
    Posts
    28
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Blog Entries
    1
    Rep Power
    0
    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.

  12. #12

    Join Date
    Nov 2008
    Location
    Birmingham
    Posts
    22
    Thank Post
    0
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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.

  13. #13

    Join Date
    Jan 2011
    Posts
    28
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Blog Entries
    1
    Rep Power
    0
    I would like to be able to write something like that but where do I start to learn?

  14. #14

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,622
    Thank Post
    1,240
    Thanked 777 Times in 674 Posts
    Rep Power
    235
    Quote Originally Posted by Metchley View Post
    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?

  15. #15


    Join Date
    May 2009
    Posts
    2,930
    Thank Post
    259
    Thanked 773 Times in 588 Posts
    Rep Power
    284
    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 :

    SchStats.png

    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.

SHARE:
+ Post New Thread
Page 1 of 3 123 LastLast

Similar Threads

  1. The Analysis of Assessment Data in your school
    By Kozan in forum MIS Systems
    Replies: 88
    Last Post: 3rd April 2012, 06:36 PM
  2. Assessment Data(CMIS)
    By Kained in forum How do you do....it?
    Replies: 7
    Last Post: 2nd April 2011, 12:47 PM
  3. Replies: 4
    Last Post: 4th November 2010, 11:02 AM
  4. [MS Office - 2003] Cross curriculum ideas for Excel
    By laserblazer in forum Office Software
    Replies: 0
    Last Post: 25th January 2010, 12:09 PM
  5. Promissor - data collection & analysis
    By j17sparky in forum Educational Software
    Replies: 3
    Last Post: 16th May 2008, 02:03 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •