[OpenSpending] Interesting budget visualisations from around the globe

Thu Jun 7 09:55:51 UTC 2012

Hi Matthias,

Thanks for your message! Comments inline.

On Wed, Jun 6, 2012 at 5:25 PM, Matthias Stürmer
<matthias.stuermer at opendata.ch> wrote:
> Hi Lucy
>
> Sorry for not communicating earlier. Thomas Preusse (http://t.preus.se/)
> is the author of the first Swiss Open Budget App showing the budget of
> the city of Bern (http://t.preus.se/bernbudget2012/). I've organized the
> data from the city council. Thomas' work was portrayed this week in the
> news paper (German):
>
> http://www.digitale-nachhaltigkeit.ch/2012/06/sonntagszeitung/
>
> All the project information is listed here:
>
> http://make.opendata.ch/doku.php?id=project:bern_budget
>
> As a side note: We're doing a workshop (in German) on June 28th at the
> Opendata.ch conference regarding open budget and procurement:
>
> http://opendata.ch/opendata-ch-2012-konferenz-4-par%C2%ADal%C2%ADlele-sessions-1330-1600/

Brilliant - is it open for anyone to attend? Id be happy to put a
short announcement on the OpenSpending blog if you wanted to jot down
a few lines to promote it?

> Thomas will be working to improve the Swiss app and adapt it to our
> national requirements in politics (flow of money from one entity to
> others, fixed vs. politically variable budget items, different years
> etc.). If things go well our city government will officially use the app
> in Fall for the budget discussion in parliament.
>
> One thing we're interested in is putting the data into a format used by
> other open budget apps. Can you provide us some hints and links?

Fantastic! I can talk about the format required for OpenSpending very
briefly here:

1. OpenSpending takes CSV files. OpenSpending is very flexible with
regard to structure, for example, it does not specify the column
order, however the content of the columns must be standardised.
2. OpenSpending requires there to be one header row in the file. This
is what the software will look for to identify the names of your
columns. All other rows are treated as data rows.
3. Rows should contain only one type of information i.e. one budget
line (or “fact”). In many files, you will see that any individual row
contains data for multiple years, as well things such as both budgeted
and actual spend. One individual row contains a maximum of one time
period, and contrasts have been created within columns. For example,
it is now possible to filter column H by revenue / expenditure to see
only line items that relate to revenue, likewise for other criteria
such as reporting type. Note that formatting data for OpenSpending
often means creating many more rows than were found in the original
document.
4. No blank rows or cells. Data imported into OpenSpending should be
fairly de-normalized: while there may be references to external code
sheets/master data, each row should contain all the information
required to construct the resulting item. Columns (particularly
classifications) should have a value for each row; they will not
automatically “fill down”.
5. No pre-aggregated totals (e.g. sub-totals or “roll-ups”) within the
data (OpenSpending will do the maths and compute these automatically)
6. There must be a combination of columns or an individual column
which constitutes a ‘unique identifier’: OpenSpending was built with a
view to re-loading entries into the database at any time, even when
existing data is loaded. This means that there must be some way to
calculate a unique fingerprint for each row in the data which
OpenSpending can use to determine whether it should update an existing
row or create a new one. The easiest way to do this is to just add a
dummy column to the dataset in which you put a number that increases
for each row (you can do this in Excel by typing the numbers into the
first two rows, selecting both cells and dragging down the lower right
corner of the cell to extend the series).
7. Amounts should be presented in purely numerical format (i.e. no
commas, points (“.”) should be used as decimal separators only).
8. Amounts should be real amounts i.e.

Amount
1657000

rather than
Amount (in thousands)
1657

9. Dates have to be in the format YYYY-MM-DD or YYYY.

There are 3 example datasets below to help further:

http://wiki.openspending.org/CSV_Format_Example1
http://wiki.openspending.org/CSV_Format_Example2
http://wiki.openspending.org/CSV_Format_Example3

We're currently working on some screencasts to help people load into
OS and we're happy to help out if anyone wants to give it a go.

I'd also be very interested to hear of other formats people have been
using to load data into budget apps!

> If you or anyone else has questions or comments regarding our Swiss app
> please let us know.

I personally really like it - as with many of these things (some of
the OS visualisations also do not do this) I think it can be helpful
to think about ordering bubbles in such a way to allow people to see
which is largest, which is smallest, how ones which look similar in
size actually compare. Just my 2c :)

Lucy
>
>
> Am 06.06.2012 15:49, schrieb Lucy Chambers:
>> Hi Ivan,
>>
>> Great to hear from you!
>>
>> On Tue, Jun 5, 2012 at 1:29 PM, Ivan Begtin <ibegtin at gmail.com> wrote:
>>> Hi Lucy!
>>>
>>> It's really great  and I like it very much. Is this visualization open
>>> source?
>>
>> Not sure which one you are referring to, but we have spoken to the
>> Australian guys about possibly using the visualisation in future
>> versions of OS so I think they are quite open to sharing. Obviously,
>> I'd also love to experiment with different views of Russian data in
>> OpenSpending, so if you have some, would love to see it!
>>
>> If you're interested in the Swiss one, I'm not exactly sure who is
>> behind it, but the source code appears to be available on the website.
>> Any further questions, asking on the Swiss mailing list will probably
>> find you the people you need
>> (http://lists.okfn.org/mailman/listinfo/okfn-ch)
>>
>>> We have some data to try.
>>>
>>> Take a look at russian visulalization of budget. This one was made by Moscow
>>> city administration - http://budget.mos.ru/project_main - not so bad but not
>>> interactive.
>>>
>>> and this one by federal government (http://info.minfin.ru/fbrash.php) - not
>>> so good as visulalization, but still better than nothing.
>>
>> Thanks for these, you've probably seen I tweeted them yesterday - I'm
>> wondering whether we should find a place for these all to live as it
>> would be great to keep a record of them - a wiki perhaps? Would it be
>> helpful to have a list (I have many!) ?
>>
>> Lucy
>>
>>
>>
>>
>>>
>>> Best Regards,
>>>    Ivan Begtin
>>>
>>> Director of NGO "Informational Culture"
>>> http://infoculture.ru
>>>
>>>
>>> 2012/6/5 Lucy Chambers <lucy.chambers at okfn.org>
>>>>
>>>> Dear all,
>>>>
>>>> Over the weekend, we've been sent some very interesting budget
>>>> visualisations to have a look at - I thought a couple of them may be
>>>> of interest to the list. Both display hierarchical information clearly
>>>> without the need to drill down.
>>>>
>>>> The first is the product of the OpenData.Ch Camp:
>>>>
>>>> http://t.preus.se/bernbudget2012/
>>>>
>>>> The second is 'Where are my Taxes Going?' from Australia. Notice the
>>>> comparison planned/actual, quite elegant.
>>>>
>>>> http://theopenbudget.org/
>>>>
>>>> We're still hunting examples of people who are building great
>>>> visualisations and for your thoughts on how effective they are at
>>>> conveying information! Let us know any thoughts,
>>>>
>>>> Lucy
>>>>
>>>>
>>>>
>>>> --
>>>> Lucy Chambers
>>>> Community Coordinator,
>>>> OpenSpending & Data Journalism
>>>> Open Knowledge Foundation
>>>> Skype: lucyfediachambers
>>>> Twitter: @lucyfedia
>>>>
>>>> _______________________________________________
>>>> openspending mailing list
>>>> openspending at lists.okfn.org
>>>> http://lists.okfn.org/mailman/listinfo/openspending
>>>
>>>
>>>
>>>
>>
>>
>>
>
> --
> Opendata.ch - Enabling Open Government Data in Switzerland
> Dr. Matthias Stürmer // Founding Board Member // Politics
> +41 76 368 81 65 // matthias.stuermer at opendata.ch

-- 
Lucy Chambers
Community Coordinator,
OpenSpending & Data Journalism
Open Knowledge Foundation
Skype: lucyfediachambers
Twitter: @lucyfedia