u/awongpublic

If you're bored, here's today's board meeting!

tl;dr: We have graphs of 10 years of district data. STARS formula is a mess. There's a game you can play to understand STARS.

Over the past few weeks, I used claude to create a dashboard covering school transportation funding in Washington state since 2016-17 forward.

https://www.sps-by-the-numbers.com/analyses/yellow_bus_ledger.html

It doesn't answer every question but it does provide quite a bit of clarity. I have spot-checked some of the results, but do remember this is a AI created data extraction so DOUBL CHECK ANYTHING before coming to a hard conclusion.

The dashboard has school-level granularity data for routes, ridership, and bus fleets for every district in the state. The data is joined with fiscal data from the F195 (budget) and F196 (actuals) spending and allows us to get a better picture of revenues and spending across the entire state while also giving features for zooming down into a district level. It also provides a more user friendly version of OSPI's STARS Simulator spreadsheet and displays the formula in a clear way that lets you understand what's happening.

There is a LOT of information on this dashboard so I will make 2 big callouts. First is the STARS Formula breakdown int he STARS explorer that, very clearly, lays out the inputs used assign the majority of the transportation funding to a district.

Screenshot of STARS formula showing that funding grows has an exponential relation to the number of schools + route distance, but a sub-linear relation number of riders and land area.

The formula can be found in the hidden tab of the STARS Simulator spreadsheet. It got some semantic...quirks (more in the FAQ at the end) because funding growth is exponential with respect to the route distances and number of schools, but sublinear to the ridership.

Playing with this formula will help folks get a better feel for how Transportation policy changes actually impact funding

The second callout is that, state-wide, the largest category of spending is Ops Staff because many districts own and operate their buses.

State wide transportation expenditure breakdown showing that Ops Staff (mostly bus drivers) is the highest spend. This is because most districts operate their own buses.

This is a complete mismatch for Seattle where bussing is outsourced so almost all the spend is in "Purchased Services"

Spending breakdown by component for Seattle showing that Purchased Transportation is the largest item. There is no Ops Staff spending at all.

If you want to compare across districts, there are also some fun scatter plots that let you see it all. For example, this is showing districts of medium size up for all years in a per-pupil (er...that should say per-rider I think) spend by category. It uses the plasma color scheme so you can visualize how a highlighted district (seattle in this one...those are the dots with black borders) change over time and how they compare to other districts.

Expenditure by component scatter graph of larger districts showing how seattle compares to other districts and to itself over the years.

There's a lot here here. Please look it over. And remember that is is AI generated so double check numbers before jumping to conclusions. I have spot-checked stuff but I have very much not checked everything.

====== FAQ ======

How much would it cost to produce 2,979 episodes of Sanctuary Moon?
About $500M. If it makes no sense why one would ask that, read last questions of the FAQ. Then play.

Why hasn't there been a dashboard before?

While this information has always been available on the OSPI website on the STARS reports, it was largely inaccessible for longitudinal analysis because, unlike the SAFS financial data, or the Washington State Reportcard data, there is no way to download all of it at one go. Instead, you have to click through the UI to download each PDF. And then you have the process the PDF. This is where Claude + computer science came in.

I had Claude code up a rate limited content-script scraper (similar the one I used to do for Panorama data before that dropped off line) which downloaded all of the STARS reports PDFs over the course of 2 days. This yielded 15,477 PDFs and DOCX totaling 1.4G of data. I then used Claud Code to

(1) sort the files
(2) pointed it at the schemas I created for the finance dashboard
(3) generate a similar set of schema and extractor code
(4) output the data as CSVs with various domain tables.

the result was 15 CSVs (of which 7 have real data, the rest are domain tables) totaling 74Mb containing ALL of the published STARS Washington state transportation data. This and all other data is available for download on the dashboard methodology page.

Did you just post AI slop?

Basically. Yes. But it's useful slop. Making the finance dashboard took me 1 year. This is an equiv level of complexity so using AI allowed us to make the data available to the general public. However, it means we need to all be careful in consuming it.

Okay, different topic..does that STARS formula remind anyone else of just a really bad game economy scoring system?

You know...now that you mention it. It does kinda look like that doesn't it?

How in the world did we end up with that formula? Did something just [#$#$#$#]?

Okay. So I'm making fun of it here, but let's take a step back. Somehow the need to use a regression analysis was literally encoded into RCW 28a.160.180. I don't know why, but it's there. Also, if you look at the dashboard, overall the funding does roughly track for most districts even if it systemically seems to underfund. So even if the input variables and formula construction imply a lot of weird things (in a sense, taking ln(riders) philisophically says the larger the student, the less important each student is), it might be the best people can do.

Someone recently pointed out there's a bit of an ad-homenin attack trend on this forum, esp against civil servants. There'll be a mod update about that a bit more going forward, but start on this point...let's ensure that if we're gonna roast something, we roast policy or choices and avoid roasting people. Especially folks who cannot respond back.

But, it still looks like a bad video game economy formula. You gonna do anything about that?

Ahem...what do you think?

Of course you would.

Glad you know me. Without further ado, I present

STARS Exploiter

STARS Exploiter screenshot.

the latest in AI Slop Vibe-coded Video Games.

It's actually just a cute wrapper around the STARS formula. You can add schools, remove schools, increase route distances, change the ridership, etc., and then see in real time how it impacts allocations.

Play with it. It'll give you a very fast, visceral, sense for what policy actually impacts revenue. And if you figure out how to game it, you can see how easily adding news schools into SPS will net you enough money from STARS allocations to, say, cover the entirety of the cost for the Ballard Light Rail Extension within one year. Or if you go just a bit further, produce a bunch of trashy...I mean...premium media for your local SecUnit's viewing pleasure.

OMG Did you really Vibe code a game about bus funding???

YERP. Here's the Claude transcript to show how easy it was. I'm sorry.

Is each school in SPS really worth 1/2 a million dollars?

Yup. At least for now. The transit funding value if a school is exponential. So with SPS at ~100 schools, each is about 500k. If you add another 100 schools, each is worth millions.

It's of course possible I made a huge mistake somewhere in understanding, but if you mess with the official STARS Simulator spreadsheet, you can break it exactly like this. It's just not as amusing.

Can we really make STARS allocate enough to SPS to produce all 2,979 episodes of Sanctuary Moon, yearly?

Sure. Add some schools. Like ~130 of them.

Agenda: https://www.seattleschools.org/board-meetings/may-13-2026-regular-board-meeting/

Prompted from a comment by u/Nomadic_Houseplant that their 2nd grade was going to have a class ratio of 28:1, far above the WSS ratio, it occurred to me that folks might want to dice their own purplebook data. In the past, I've written custom parsers, but that's not super accessible. So let's see how to do this with Claude really fast!

First, you download the purplebooks you want from SPS's website. I usually seach "SPS Purple book 2026" and the first link has it.

Then I opened up claude and uploaded the file gave it this prompt:

This is the "Purple book" for SPS district.  After the intro text, it is a 
repeated format listing funding and staffing allocations per school.

I want you to go through all Elementary Schools (first section of schools) and 
for each school:
  (1) Projected enrollment for K-3, and 4-5 cohorts
  (2) find #FTE of Teacher K-3 and #FTE of Teacher 4-5
  (3) Produce me a table of # students per FTE for both cohorts for each school.

Sort the table in order of highest # students per FTE.

This produced a table like the following

Initial Table view of Elementary ratios based on Claude data

Next, I downloaded the two prior year purple books and uploaded them with the following prompt

here are the last 2 years data. Please add to table for comparisons with the current
year. Sorting should be done by current year data.

and yes, I don't bother copy editing my prompts. (It's not really understanding the grammar directly anyways :P). That created an artifact the following where you can see ratios over the years. It's informative to look at best and worst average student teacher ratio. Here it is sorted by worst ratio

3 years of purplebook ratio data put side by side sorted by worst student/teacher ratio in K-3

Here it is sorted by best ratio where you can see shuffling around of FTE along with some dramatic improvements in certain schools

3 years of purplebook ratio data put side by side sorted by best student/teacher ratio in K-3

Of course some of this seems nonsensical. The original comment prompting this was citing two 28-children classrooms this year in the allocation model. So, let's ask claude to do more. Next prompt was

For each school in each year, attempt to assign the teacher FTE to the grade-levels
of students.  Create a table of the results and highlight any situation that ends
up causing a grade to get over target ratio listed in their purplebook.

This produced a graph that is starting to show some interesting things but which also has numbers that seem insane (48 students to one teacher in North Beach 4th grade? No way)

AI Attempt to assign teachers to classes to produce class-ratios per grade

So I fed this back into claude and had a dialogue with 4 more prompts (mid-point artifact elided)

some of these ratios seem impossibly high. 48:1 and 45:1 seem impossible. Update your
assignment logic. Also ensure that the total assigned per grade sums to the right FTE
from the purplebook for the school.

does that mean they have to create a split class?

can you update the highlight background to get more intense as the over-targeting gets
worse?  And can you ensure you are using a color-blind friendly colorscheme?

make this into a single standalone html publishable artifact

Add big caveat that is an AI guess and may be entirely wrong.

The final result was a graph that looked this like the following:

Final chart of AI assignments for grade levels

Some of these still seem insanely high (eg 39:1 in Concord for 5th grade), but as an exploration tool, it can guide focus for questions.

Anyways... point is, if you want to crunch data, you can use AI to help produce these things. But, unless you've verified it is doing the right thing, use it as a way to think of new questions instead of as a definitive analysis. Cause I really don't think we have so many 36:1 teaching ratios across the board.

Here is the final artifact with the AI attempt to assign teacher to grades (low confidence in correctness):
https://claude.ai/public/artifacts/44b7b0aa-2c8e-496b-8e34-ed99af0cefb5

Here is the initial Visual Table for just one year (medium confidence in correctness):
https://claude.ai/public/artifacts/a86bbe47-f5ec-4b62-8c92-62a81e06975c

And here's the multi-year variant:
https://claude.ai/public/artifacts/b16138de-a86d-4998-b2ba-ea99254fad13

Somehow the cellphone policy got the airtime out of the April 29th meeting, but for me the far more wide-reaching and rich presentation was the "Top 5 in 5" goal framework and the related North Star of

>One North Star: Make SPS the best urban school system in America — where any family can confidently enroll any student and expect a district that is top five in performance within five years, with performance visible across every student group.

Now clearly we're not there and I'm sure there's a bunch of snark that people can throw at this, but at the same time, this is the first time I've seen such a crisp set of goals presented.

Curious folks think?

Also, just as a person note, this is the first time I've *ever* seen the district actually present the General Fund Balance actuals displaying that the Actuals deficit is "only" about 40M, 20M, 30M each year instead of the inflated 100M budget deficit they keep citing.

Screenshot of last slide in dec showing Fund Balance from FY22 to FY25 and projections to FY31

If you want to compare to the SPS by the numbers charts, look at the Vitals dashboard for Beginning Balance and zoom in.

Screenshot of SPS By The Numbers Vitals dashboard for Beginning Balance showing the same numbers. 2023 here is FY22 in the SPS presentation.

This is heartening for me to see the real numbers show up.

And not to tangent too far away from the 5x5 goals, but one thing that worries me still about the fund balance is that it does indeed swing by ~100M from the high in Sept to the low. in June. So looking at the Feb 2026 report page 4 chart, we are not in great shape.

Feb 2026 Fiscal report chart of General Fund balance by month. June low is the balance low-point. 2 years ago it was ~40M. Last year it was ~20M. This year consistently ~15M lower or more at every data point. What will happen?

I wonder what Jun will look like. We're skirting $0.

[ARGH Title should have said SBAC 5th Math... and now I can't change it.]

I made some clean-ups to the rushed-out Assessment dashboard (thanks u/DistanceRude9275 for the feedback! super helpful) and figured I'd show how to use the thing a bit.

Overall, there is just way too much data to scan it easily. You have to group by schools to make any sense, and then likely use "Add comparison" to look at things side-by-side. So as a working example, and because it directly relates to the goals presented tomorrow + the community discussion of the past long while, here's how I would take a first cut look at the score performance of students identifying as Black/AA versus All students at a district wide level.

First, Toggle the "Render Charts" switch that I just added to Off. There's too much data so if you don't do this, every click costs 30s.

Second, change the graph facet from "School" to the newly added "Middle School Area" which groups schools by which Middle School Attendance Area they are geographically located in (screenshot of map at end).

Like breaking down by Closure Region, it doesn't always match what I think of as seattle subdivisions, but it is much closer to representing the geographic divides than just "NW/NE/Central/SW/SE."

Third, reduce the Test + Grades to just show 1 thing for one demographic. I'll use SBAC Math for 5th for Black/AA students. This will make the sort order more reflective of the data you want

Fourth, and here's the key, click "Add Comparison" on the lower eft side to create a second column of charts to look at. Then in the left, change the demographic from "Black/AA" to All Students.

Fifth, go to Disclosure Avoidance and select "Drop". We will revisit in a sec.

Now toggle "Render charts" back on and you'll get a graph like this:

Screenshot of tool showing comparison 5th SBAC Math % Met Standard by school, faceted by \"Middle School Attendance Area.\" It highlights the divergent performance of the demographic group when comparing left and right columns. Missing data on the left also how many schools do not even have enough of the Black/AA demographic to produce a result. This highlights the city's segregation sharply.

with a complex URL that you can pass around encoding your settings:

https://www.sps-by-the-numbers.com/finance/assessments?d=c.17001~g.spsbtn~cn.amount~sn.fte~s.QA~gl.IABA~ta.YAP4~sg.IACA~ts.YAXw&d=c.17001~g.spsbtn~cn.amount~sn.fte~s.QA~gl.IABA~ta.YAP4~sg.IAQA~ts.YAXw&c=f.5~l.0~so.d~st.1~so.d~st.1~ys.0~csg.0~cy.1~da.1

Key things to notice. First, the left side has a bunch of missing data. This is because, when the number of students within a category (grade, subject, year, school, demographic) is too small. OSPI suppresses the data. Since we selected Black/AA at 5th grade, barring pass opt-out of testing, this visually shows when a school just has very very few Black/AA kids in 5th for a year.

Now if this seems improbably sparse for Aki Kurose, you're partially right! The "Disclosure Avoidance" setting to Drop is aggressive. When the # of students is small, there are multiple levels of suppression. The first is to change the % to a fudged bound. So it might say ">95.3%" of all Black/AA students met standard. That could be 99.999% or it could be 95.31%. We don't know. If you select "Drop" all these data points are removed. Switching it to "Best Guess" we see a plot like this

Same graph as above but now with \"Best Guess\" Disclosure Avoidance. It shows more data being possibly inaccurate

The graph looks fuller on the left now but some of those data points are bounds, not actual data which makes it harder to interpret. Also, arguably, if there is a low enough population of a demographic to require Disclosure Avoidance Techniques, the impression of the first graph with "Drop" of that population being very isolated in that school is probably correct.

Anyways, moving on... the next thing to notice of course is how the scores on left column visually band at a lower point than the right column even across the COVID disruption. This is achievement gap that we have been able to solve as a community forever.

Lastly, scroll down the charts and you will see VERY quickly that the left column starts getting blanker and blanker. By rows 3 and 4, you start to see that we mostly cannot get enough data, even with Best Guess, to chart on the left.

Rows 3 and 4 of Middle School Area facet start having very few datapoints on the Black/AA column.

In fact, 6-7 of the 12 middle school areas have effectively blank graphs on the left. I grab a subsample here cause it's too hard to screenshot it all

Subsample of areas where you can see many attendance areas just don't have sufficient Black/AA students for test scores

This isn't to reopen some huge soul-searching debate about redlining etc (really had enough of that right now) but to remind folks that policy which isn't aware of this divide is gonna not do the right thing. Specifically, if you do any "all schools will XXXX to help YYY" kind of thing, well, double check that YYY actually attend most schools. Cause if I had to make a major criticism at a lot of advocacy and policy badness for the past decade, it's been folks trying to make district wide changes for the benefit of Black/AA (really African American Males per strategic plan) without recognizing and adjusting for that fact that over 50% those efforts would be touching places that rather literally cannot reach the intended students.

Anyways, have fun with the data dashboard. And post links + screenshots to anything interesting. You should be able compare across grades within a school. Or maybe do things like facet by grade and compare vs other districts, etc.

And continue to give me bugs. I will fix as I get time.

Here's the middle school attendance area chart for reference:

SPS 2025-26 Middle School Attendance Area Map