[TRU Research] Discrepencies between Budget Archives and Open Data

Jim Walseth jim.walseth at gmail.com
Thu Jun 11 14:07:23 PDT 2020 

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/20200611/cb85dbbd/attachment.html>

More information about the Research mailing list