Greetings,

I have written an application in my spare time that allows the user to take the Excel data for the Wisconsin Department of Public Instruction’s (DPI) Accountability Report Cards, and to aggregate the data to get some more meaningful data. Why have I done this? Because from what I’ve been able to tell (at least, until an official corrects me on this), the DPI’s cut-score threshold standards for the Rating Categories are obfuscated and have already changed and been reduced at least once before, as I have reported previously.

With the DPI’s official Rating Categories seeming to be essentially arbitrary and changeable on a whim, the only thing that we have to rely on to judge schools on is the raw data itself, and how the data for one school or district compares to another, or how the data for one school or district compares to ALL of the others.

Purpose

That is what this program is intended to accomplish. The program takes the original spreadsheets (which you can download here yourself) and aggregates the five main scores upon which schools are judged:

• Overall Accountability Score – which is based upon a calculation derived from the other four scores
• School/Student Achievement Score – students’ level of knowledge and skills attained compared against state academic standards in English language arts (ELA) and mathematics
• School/Student Growth Score – how rapidly students are gaining knowledge and skills from year to year, focusing on the pace of improvement in students’ performance
• School/Student Target Group Outcomes Score (known in previous years as the “Closing Gaps” Score) – outcomes for students with the lowest test scores in their school – the Target Group. Outcomes are displayed for achievement, growth, chronic absenteeism, and attendance or graduation rate
• School/Student School On-Track (to Graduation) and Postsecondary Readiness Score – how successfully students are achieving educational milestones that predict later success

For each of these five metrics, my program creates the following additional metrics which can be used to compare school/district performance:

• Total Number of Schools/Districts – The total number of schools or districts that were considered in the creation of this metric
• State Average – The average (mean) score of ALL schools/districts for this metric
• Rank – This school/district’s rank in the list of all such entities
• Percentile – This metric essentially what percentage of schools/districts are ranked HIGHER than this entity in the ranking. For this metric, a lower number is better. For example, a Percentile of 5.3 would mean that 5.3% of schools/districts rank ABOVE this one, making it a “top class” entity. It’s entirely calculated by dividing the metric’s Rank by the Total Number of Schools/Districts

By creating these metrics, we can better see how schools “stack up” against each other.

Example

I will take an example year for the Fall Creek school here, for illustration purposes:

In the example of Fall Creek Middle School, for the School Year of 2011-2012, the school’s Overall Accountability Score was 70.5 (the value provided by the DPI), compared to the State Average of 70.21, making it ranked 152 out of 326 middle schools (more on this part later). Or to say it in another way, the school was in the 46.6th percentile, with 46.6% of middle schools above it, and 53.4% of schools ranking below it. So, right around in the middle, state-wide.

I hope this example helps you understand what this program is doing, and what its purpose is.

The Program

This is the main screen of the program, as well as a breakdown of how to operate it:

1. Input Directory – Click this to choose the input directory where you’ve stored the specific DPI Excel files that you wish to process
2. Files List – After choosing your input directory, this list will be automatically populated with all files found in the directory. From here you can choose which files to include in the processing.
3. Select All/None Buttons – Allows you to select all files, or clear the selection of files completely.
4. Group Entity Types For Ranking – This checkbox decides whether the metrics the program calculates will lump ALL schools of ALL types together for the purpose of its calculations, or will split the rankings based on the type of school or district. For example, if this is unchecked, Fall Creek Middle school would be compared against all schools in the state, whether they be Elementary, Middle, or High Schools. If it’s checked, the school will ONLY be compared to other Middle Schools
5. Pull Data From Files – This button is where the “magic” happens. Once you’ve selected the files to process, and clicked this button, the program will then scour these files for all data, and calculate all of the metrics listed above, storing that data in memory. If you choose different files later and click the button again, those previous values will be wiped from program memory and recalculated based on the current file selection. This process takes the longest, as all the data must be collected at once from ALL files/records chosen to properly rank the entities. During my testing, it took a little over two minutes to process just the 2020/2021 file, and a little over ten minutes to process ALL fifteen current school and district files that the DPI provides.
6. Entity List – This list shows all of the Entities (schools and districts) discovered within the files chosen in the Files list above. You can select or de-select entities from this list to include or exclude them from the resulting Excel file outputs.
7. Select All/None Buttons – Allows you to select all entities, or clear the selection of entities completely.
8. Export – This button executes the actual export of the data that you’ve chosen to process. This part is fairly quick, as all of the data has already been collected and calculated at the “Pull Data” step, so this step just spits it back out to a new Excel file of your choosing. This new Excel file will have a unique worksheet for every year/file chosen, and will ONLY include the entities that you chose in the Entities list, filtering out all those that weren’t selected.
9. Status Bar – This bar shows you the current status of the program. Below is what it looks like when it’s processing:

Conclusion and Download

I hope that this explanation helped explain to you the purpose and operation of my DPI Summary Consolidation application. The below file will allow you to download not only the working program itself, but also the Source Code for the program, for those “techie” types that want to see how things work under the hood and/or don’t trust that I’m artificially “fudging” the numbers. I believe in full transparency. There’s no gimmicks or tricks here, and I want you to be able to see that.

Download DPI Summary Consolidation and Source Code