[TRU Research] Discrepencies between Budget Archives and Open Data

Jim Walseth jim.walseth at gmail.com
Sat Jun 13 16:01:11 PDT 2020 

Oh thank god something to do! I'm going crazy here reading social media.

Well, is this too simple?
https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudget2008-2020_15920891310250/Dashboard5?publish=yes

A benefit is that it will work well on phone screen I think.

On Sat, Jun 13, 2020 at 3:09 PM Katie Wilson <katie at transitriders.org>
wrote:

> Jim, those are good points.
>
> On the second point, though, I think even if the actual trend line ends up
> looking similar, there is a good story to be told:
>
>    - City human services spending has risen in the context of cuts to
>    human services spending at other levels of gov’t (per McGinn’s email), so
>    cities like Seattle are shouldering more of that role.
>    - Because of rising rents and income inequality over the past decade,
>    there is way more work to be done in this area, and the spending hasn’t
>    nearly kept up.
>    - By contrast, crime has not been increasing appreciably. In fact,
>    there’s a decades-long trend of falling crime.
>    - Because we’re not funding appropriate responses to poverty and
>    homelessness, we end up funding more police to deal with the social
>    disorder that results from this failure. In other words, we criminalize
>    poverty.
>
>
> But clearly this story is a little complicated, and you can’t tell it in a
> single graph.
>
> I am working on writing something about the police budget and I would also
> like to start sharing some of our work with the defund SPD folks. Because
> we haven’t yet figured out the best way to present HSD numbers, *Jim,
> could you make a page just containing the SPD graph starting in 2012 (in
> whatever type of visual you think is most compelling)*, so I can link to
> it from my article and also share it with our allies? I think we should use
> 2012 as the starting point rather than 2008 or 2012 because it’s a low
> point for the inflation-adjusted and per capita numbers, and also 8 years
> is a nice round number in political terms. Spearking of per capita numbers, *could
> you also make a graph (maybe a bar graph?) showing the per capita figures
> from 2012 to 2020?* That could go on the same page I think.
>
> - Katie
>
> On Jun 11, 2020, at 5:49 PM, Jim Walseth <jim.walseth at gmail.com> wrote:
>
> Regarding the pass through line item, here are two considerations:
> 1. RIght now the data attribution is simply
> "From the CIty of Seattle budget archives
> https://www.seattle.gov/city-budget-office/budget-archives, adjusted for
> inflation."
> If we start tinkering with the numbers the attribution should explain what
> we did it and might appear to the casual reader that we are "juking the
> stats".
> But in reality...
> 2. If removing the pass through has the effect of removing the 'bump'  in
> early years, the Human Services* trend line *will look a lot more like
> the SPD one.
>
> On Thu, Jun 11, 2020 at 5:16 PM Katie Wilson <katie at transitriders.org>
> wrote:
>
>> One more thought on the human services data, remembering what I found out
>> before about the $26 million dip from 2011 to 2012, as explained here
>> <https://www.seattle.gov/Documents/Departments/FinanceDepartment/12adoptedbudget/HealthHumanServices_2012AdoptedBudget_000.pdf>
>> :
>>
>> "While the 2012 Adopted Budget preserves and increases General Fund
>> support for HSD, the Department is experiencing reductions in State and
>> Federal funding. The 2012 Adopted Budget recognizes impacts from changes in
>> State funding in the Aging and Disability Services Division. Beginning
>> in October 2011, *pass-through funding for home care program health plan
>> reimbursements is redirected to home care agencies and is no longer
>> administered by HSD, resulting in a significant budget reduction to HSD but
>> no impact on direct services, as it was a change to the method of payment.*
>>
>>
>> Given that this is an accounting thing, it seems like maybe we should try
>> adjusting the 2008-2011 numbers so that they don’t include this line item,
>> which (it sounds like) doesn’t appear in the later years (unless it was
>> added back in at some point)?
>>
>> On Jun 11, 2020, at 2:07 PM, Jim Walseth <jim.walseth at gmail.com> wrote:
>>
>> Tip: you can go to https://public.tableau.com/profile/katie.wilson8886#!/
>> to browse all the samples.
>>
>> On Thu, Jun 11, 2020 at 1:51 PM Jim Walseth <jim.walseth at gmail.com>
>> wrote:
>>
>>> Here's another version. Personally I find the filled overlay a bit
>>> misleading in the sense that the Human Services polygon covers part of the
>>> SPD spend, making it visually smaller.
>>> Here is the same viz just using lines.
>>>
>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample6/Dashboard3?publish=yes
>>>
>>>
>>> On Thu, Jun 11, 2020 at 1:03 PM Jim Walseth <jim.walseth at gmail.com>
>>> wrote:
>>>
>>>> Katie, Taking a stab at your request:
>>>>
>>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample5/Dashboard3?publish=yes
>>>>
>>>> On Thu, Jun 11, 2020 at 11:43 AM Katie Wilson <katie at transitriders.org>
>>>> wrote:
>>>>
>>>>> Looking good Jim! Visually I think I like the first graph better than
>>>>> the bar graph— is there a way to overlay the human services budget on that
>>>>> one in the same manner?
>>>>>
>>>>> On Jun 11, 2020, at 10:38 AM, Jim Walseth <jim.walseth at gmail.com>
>>>>> wrote:
>>>>>
>>>>> I fussed with both of those some more. I'm going back to my day job
>>>>> now and will check in later for feedback.
>>>>> Jim
>>>>>
>>>>> On Thu, Jun 11, 2020 at 9:25 AM Jim Walseth <jim.walseth at gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Fantastic work on the spreadsheet. Here are updates to the two vizzes
>>>>>> I made yesterday, using inflation-based numbers.
>>>>>> I also added data attribution, fixed up labels and things like that.
>>>>>> Happy to make any changes that I am able to make!
>>>>>>
>>>>>>
>>>>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample3/Dashboard1?publish=yes
>>>>>>
>>>>>>
>>>>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample4/SeattleCumulativeSpending?publish=yes
>>>>>>
>>>>>> On Thu, Jun 11, 2020 at 7:30 AM Katie Wilson <katie at transitriders.org>
>>>>>> wrote:
>>>>>>
>>>>>>> Re what police do with their time… This guy Bryan Kirschner who’s a
>>>>>>> tech employee (he’s the one organizing the Tech4Recovery group) used to do
>>>>>>> community policing work & he’s writing a series about policing/public
>>>>>>> safety. He sent me a link to this piece
>>>>>>> <https://medium.com/@contact_28544/everything-you-know-about-policing-is-wrong-part-1-2639186e101e> which
>>>>>>> I found enlightening.
>>>>>>>
>>>>>>> On Jun 11, 2020, at 1:02 AM, Douglas Sexton <
>>>>>>> sextondouglas at comcast.net> wrote:
>>>>>>>
>>>>>>> Jumping off of Harry's last contribution.I tied to get homeless data
>>>>>>> for Seattle from 2008-2019 using the point in time count but there are
>>>>>>> years that only have data for King County and not specifically Seattle.
>>>>>>> Additionally as the years go on more areas are searched resulting in more
>>>>>>> thorough higher counts but I think the methodology in the last 3 years is
>>>>>>> relatively consistent. I added a column for King County # unsheltered
>>>>>>> homeless which was the only consistent measure I could find (though early
>>>>>>> years look very undercounted). A rough rule of thumb is ~60% of the count
>>>>>>> is Seattle. It's definitely unclear on if investment in housing helped with
>>>>>>> the deceleration of homelessness in the latest years but it sure is clear
>>>>>>> that police aren't helping.
>>>>>>>
>>>>>>> The salary per cop stat is super compelling to me. Maybe a breakdown
>>>>>>> of what a cop gets paid as a percentage of how their time is actually spent
>>>>>>> would be interesting.Thinking about that confessions of a bastard cop post
>>>>>>> that has been going around recently (
>>>>>>> https://medium.com/@OfcrACab/confessions-of-a-former-bastard-cop-bb14d17bc759).
>>>>>>> My understanding is a lot of the time these cops might be responding to
>>>>>>> crimes after they happen and not catching anyone and possibly creating
>>>>>>> unnecessary citations.You have to click the "all other crime" stats on the
>>>>>>> dashboard here (
>>>>>>> http://www.seattle.gov/police/information-and-data/crime-dashboard)
>>>>>>> and one thing that jumps out is there are as almost as many trespassing
>>>>>>> crimes as violent crimes so far in 2020. That seems... odd (unless you
>>>>>>> already know cops exist to protect property interests). Maybe I can dig
>>>>>>> into that more later.
>>>>>>>
>>>>>>> Doug
>>>>>>>
>>>>>>> On June 10, 2020 at 10:38 PM Harry Maher <harryb.maher at gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>> Cool graphs, thanks, Jim! And yep, you're right re: inflation,
>>>>>>> Katie. I added that and the rest of the annual budgets.
>>>>>>>
>>>>>>> I also looked at inflation adjusted $/population in another set of
>>>>>>> columns. I was thinking that, in addition to inflation, it might make sense
>>>>>>> to normalize things by population growth as well. To Jim's point, they are
>>>>>>> all experiencing the same population growth, but to Katie's point, the
>>>>>>> narrative can change/get a sense of clarity when you normalize the #s. (Or,
>>>>>>> at the very least, we can just report the amount that population has
>>>>>>> increased over the same period b/c there remains the open question if
>>>>>>> normalizing budget by population makes as much sense for all of these
>>>>>>> programs--if the cops are smart, they may argue that they "need to patrol"
>>>>>>> both ppl who live here as well as those visiting from surrounding areas for
>>>>>>> work/tourism/crime/etc., while housing and human services would mostly only
>>>>>>> serve Seattle residents.)
>>>>>>>
>>>>>>> Anyway, when we look at inflation adjusted $ spent per resident, it
>>>>>>> looks like we were defunding human services for a while there and
>>>>>>> essentially barely changing funding for housing over the years while
>>>>>>> substantially increasing funding for the police. We can look at the # of
>>>>>>> folks experiencing homelessness in Seattle over the same period and the #
>>>>>>> of Seattlites estimated to be rent burdened or severely rent burdened (both
>>>>>>> I assume have increased substantially more than population growth) to add
>>>>>>> to a compelling story. That said, it's also a little misleading when we
>>>>>>> can't use 2020 numbers due to a lack of 2020 population estimates from the
>>>>>>> Census thus far (and 2020 is the year they increased funding for human
>>>>>>> services and housing).
>>>>>>>
>>>>>>> Another thought on the fact that salaries make up ~80% of the budget
>>>>>>> and will need to get cut, or personnel will need to get laid off: we can
>>>>>>> likely get some good traction if we compare what cops earn to what human
>>>>>>> services and housing employees earn. Something like, "for each cop that we
>>>>>>> have to lay off, we can hire 2 essential housing employees" or whatever the
>>>>>>> actual number is.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Wed, Jun 10, 2020 at 9:16 PM Katie Wilson <
>>>>>>> katie at transitriders.org> wrote:
>>>>>>>
>>>>>>> One reason I’m interested is that I’m pretty sure once you adjust
>>>>>>> for inflation, human services spending is more or less flat between 2010
>>>>>>> and 2018 (taking into account the 2011-2012 dip which is an accounting
>>>>>>> thing), and I think that’s a good story to tell.
>>>>>>>
>>>>>>>
>>>>>>> On Jun 10, 2020, at 7:46 PM, Jim Walseth < jim.walseth at gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>> Yes I filtered the data to start at 2013 because only the spd data
>>>>>>> exists from years before that in the spreadsheet. Once we have housing and
>>>>>>> services data for all years I can open it up.
>>>>>>>
>>>>>>> I question whether inflation is needed when doing comparison since
>>>>>>> all would be affected. But yeah I always appreciate that effort.
>>>>>>>
>>>>>>> Jim
>>>>>>>
>>>>>>> On Wed, Jun 10, 2020 at 7:19 PM Katie Wilson <
>>>>>>> katie at transitriders.org> wrote:
>>>>>>>
>>>>>>> Cool!!
>>>>>>>
>>>>>>> Jim, on the bar graph in the first link, is there a reason you
>>>>>>> started with 2013 instead of 2008 or 2010?
>>>>>>>
>>>>>>> I think we should play around with adjusting for inflation, too,
>>>>>>> perhaps using this:  https://www.usinflationcalculator.com
>>>>>>>
>>>>>>> I would be interested in seeing that bar graph starting in 2008 and
>>>>>>> adjusting for inflation (to 2020 dollars, I guess).
>>>>>>>
>>>>>>>
>>>>>>> On Jun 10, 2020, at 7:10 PM, Jim Walseth < jim.walseth at gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>> Ooh I'm liking this
>>>>>>>
>>>>>>> Sample 2
>>>>>>>
>>>>>>>
>>>>>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample2/SeattleCumulativeSpending?publish=yes
>>>>>>>
>>>>>>> On Wed, Jun 10, 2020 at 6:55 PM Jim Walseth < jim.walseth at gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>> I made it "tall" so scroll down to see part 2. This seems to be the
>>>>>>> fashion with web stuff now, rather than tabs, for instance.
>>>>>>>
>>>>>>> On Wed, Jun 10, 2020 at 6:53 PM Jim Walseth < jim.walseth at gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>> First sample viz
>>>>>>>
>>>>>>>
>>>>>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample1/Dashboard1?publish=yes
>>>>>>>
>>>>>>> On Wed, Jun 10, 2020 at 6:52 PM Harry Maher < harryb.maher at gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>  Omg Ben. WHAT?
>>>>>>>
>>>>>>> And ok, I've been lazily plugging in numbers while listening to the
>>>>>>> radio and now preparing dinner, hope to be done at some point, but feel
>>>>>>> free to jump in anyone who wants this done more quickly...
>>>>>>>
>>>>>>> And that's great, Jim!!
>>>>>>>
>>>>>>>
>>>>>>> Some notes:
>>>>>>> - These pdfs are still v. opaque and we may have to wait until we
>>>>>>> get more specific details from the city council. Definitely no line item
>>>>>>> for "military weapons" or other definite cuts...
>>>>>>> - Personnel was 80% of the $398,561,697 budget in 2019 (I can't
>>>>>>> quickly find the % for 2020) and they report 2,172.35 FTE (with an asterisk
>>>>>>> that it's approximated, but...). That puts the average salary at $146,776
>>>>>>> if I'm not mistaken. That's insane. I get that it's a hard job, but yeesh,
>>>>>>> also supposedly one of public service. Since they are city employees, I bet
>>>>>>> we'd be able to look at individual salaries and look at how overtime pay is
>>>>>>> allocated b/c I know that they get a huge chunk of their pay from overtime.
>>>>>>> I'm sure getting rid of overtime pay will go a very long way towards
>>>>>>> reducing the budget, but have no numbers to back that right now.
>>>>>>> - Slightly in jest, but... maybe we can completely slash the budget
>>>>>>> for East Precinct in addition to overtime cuts and see where that gets us.
>>>>>>> Keep on the facilities/maintenance/custodial staff, see what the Black
>>>>>>> community wants to do with the building, and reallocate those workers to
>>>>>>> the appropriate division(s).
>>>>>>>
>>>>>>> On Wed, Jun 10, 2020 at 6:30 PM Jim Walseth < jim.walseth at gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>> I am throwing this into Tableau Public and will make a few basic
>>>>>>> samples. I removed the first row which just said "adopted budget numbers"
>>>>>>> because it was confusing Tableau.
>>>>>>> Jim
>>>>>>>
>>>>>>> On Wed, Jun 10, 2020 at 3:53 PM Katie Wilson <
>>>>>>> katie at transitriders.org> wrote:
>>>>>>>
>>>>>>> Hey folks,
>>>>>>>
>>>>>>> Since the Open Data site is unreliable, it looks like our best bet
>>>>>>> for accurate budget data is the PDFs archived here:
>>>>>>> https://www.seattle.gov/city-budget-office/budget-archives
>>>>>>>
>>>>>>> Here is a mini-project if anyone has time for it.  I created a
>>>>>>> spreadsheet here
>>>>>>> <https://docs.google.com/spreadsheets/d/1uDjqVID4gkBFfatMTM-hUbO-OAzR70Zc1-3GJEFZask/edit?usp=sharing>,
>>>>>>> where we can compile data from the *adopted budgets *(not proposed
>>>>>>> or endorsed) for SPD, human services, housing, and any other areas of
>>>>>>> interest. I think the years 2010-2020 are key, but I’ve started the
>>>>>>> spreadsheet in 2008 just to match with the recession, in case that shows us
>>>>>>> anything interesting. Filling out this spreadsheet will enable us to make a
>>>>>>> graph like the ones we were making from the Open Data site, only correct
>>>>>>> this time.
>>>>>>>
>>>>>>> I am in touch with some of the folks leading the Defund SPD effort
>>>>>>> and they would love our help digging up data and making graphs etc. that
>>>>>>> tell a good story to boost the case. So, rest assured our work on this will
>>>>>>> be useful and appreciated! For example, I was able to send along this bit
>>>>>>> earlier today, which I believe was then passed along to a councilmember via
>>>>>>> a council aide to be shared aloud during the council meeting this
>>>>>>> afternoon:
>>>>>>>
>>>>>>> The SPD budget has *increased by * *43% since 2010*,  *after* you
>>>>>>> account for inflation. (Not accounting for inflation, it’s a 68% increase.)
>>>>>>>
>>>>>>> Here’s the math and sources:
>>>>>>>
>>>>>>> 2020 SPD adopted budget:  *$409 million* -
>>>>>>> https://www.seattle.gov/Documents/Departments/FinanceDepartment/20adoptedbudget/SPD.pdf
>>>>>>>
>>>>>>> 2010 SPD adopted budget:  *$243 million* -
>>>>>>> https://www.seattle.gov/Documents/Departments/FinanceDepartment/10adoptedbudget/PUBLIC_SAFETY.pdf
>>>>>>>
>>>>>>> 2010 SPD adopted budget, injusted for inflation to 2020 dollars: * $286
>>>>>>> million* -  https://www.usinflationcalculator.com
>>>>>>>
>>>>>>> Increase from 2010 to 2020, i.e. 286 to 409, is (409 - 286)/286 x
>>>>>>> 100 =  *43%*
>>>>>>>
>>>>>>>
>>>>>>> - Katie
>>>>>>>
>>>>>>>
>>>>>>> On Jun 9, 2020, at 5:34 PM, Katie Wilson < katie at transitriders.org>
>>>>>>> wrote:
>>>>>>>
>>>>>>> Budget office director to the rescue, kind of...
>>>>>>>
>>>>>>> Begin forwarded message:
>>>>>>>
>>>>>>> *From: * "Noble, Ben" <Ben.Noble at seattle.gov>
>>>>>>> *Subject: * *RE: Discrepencies between Budget Archives and Open
>>>>>>> Data*
>>>>>>> *Date: * June 9, 2020 at 5:31:02 PM PDT
>>>>>>> *To: * Katie Wilson <katie at transitriders.org>
>>>>>>>
>>>>>>> Katie – long and painful story here, but the Open Data site is not
>>>>>>> accurate.  I am frankly embarrassed by that reality.  Short-term we may
>>>>>>> take down the site, but obviously that is not a positive step in terms of
>>>>>>> transparency.  It was something that got set up in a rush a few years back
>>>>>>> and has been neglected since (and frankly has not been a priority in the
>>>>>>> last few months).  I say that by way of explanation not excuse.  In the
>>>>>>> term, the Budget Archives, while clunky, are the right source.  Based on
>>>>>>> your flagging of the issue, I have just asked the team whether we can get
>>>>>>> something else up in the near-term.  I will keep you posted.
>>>>>>>
>>>>>>> -ben.
>>>>>>>
>>>>>>> *From:*  Katie Wilson < katie at transitriders.org>
>>>>>>> *Sent:*  Tuesday, June 9, 2020 4:29 PM
>>>>>>> *To:*  Noble, Ben < Ben.Noble at seattle.gov>
>>>>>>> *Subject:*  Discrepencies between Budget Archives and Open Data
>>>>>>>
>>>>>>> *CAUTION: External Email*
>>>>>>> Hi Ben,
>>>>>>>
>>>>>>> Sorry to bother you with this, but I emailed the Open Data people
>>>>>>> first and they told me to ask the City Budget Office. I’m wondering why
>>>>>>> there are significant discrepencies between the  Budget Archives
>>>>>>> <https://www.seattle.gov/city-budget-office/budget-archives> and
>>>>>>> the budget numbers on the  Open Data site
>>>>>>> <https://openbudget.seattle.gov/#!/year/default>.
>>>>>>>
>>>>>>> To give one timely example,  this Open Data page
>>>>>>> <https://openbudget.seattle.gov/#!/year/2020/operating/0/department/Police/0/service?vis=barChart> puts
>>>>>>> the 2020 SPD budget at ~$357m, whereas the number in the  2020
>>>>>>> adopted budget
>>>>>>> <https://www.seattle.gov/Documents/Departments/FinanceDepartment/20adoptedbudget/SPD.pdf> and
>>>>>>> reported in  various news articles
>>>>>>> <https://www.seattletimes.com/seattle-news/crime/after-days-of-seattle-protests-city-will-withdraw-request-to-remove-police-force-from-federal-oversight/> is
>>>>>>> $400m+. I have been digging around and making graphs and there are a lot of
>>>>>>> discrepencies like this. I understand that proposed and adopted and actual
>>>>>>> budgets are all different, but that doesn’t seem to account for it.
>>>>>>>
>>>>>>> Please feel free to direct me elsewhere if there’s someone else I
>>>>>>> should be asking about this.
>>>>>>>
>>>>>>> Thanks!
>>>>>>> Katie
>>>>>>>
>>>>>>> Katie Wilson
>>>>>>> General Secretary
>>>>>>> Transit Riders Union
>>>>>>> https://transitriders.org
>>>>>>> <https://protect2.fireeye.com/v1/url?k=23520a2f-7de29737-2352229f-86b2e136ff17-644b0fdb8c75a238&q=1&e=c19e905a-6ec5-4436-a6cd-f7acc13cf097&u=https%3A%2F%2Ftransitriders.org%2F>
>>>>>>> 206-781-7204
>>>>>>>
>>>>>>> *The Transit Riders Union is a democratic organization of working
>>>>>>> and poor people, including students, seniors, and people with disabilities,
>>>>>>> taking control over our own lives, and building up the power we need to
>>>>>>> change society for the good of humanity and of the planet. We will fight to
>>>>>>> preserve, expand, and improve the public transportation system in Seattle
>>>>>>> and beyond, so that every human being has access to safe, affordable, and
>>>>>>> reliable public transit.*
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.transitriders.org/pipermail/research/attachments/20200613/03c0b635/attachment.html>

More information about the Research mailing list