This is how you “open” data

u4_cash_expenditures_top_10

I am halfway through a project of making all Unit 4 check regsiters truly accessible online. First, let me briefly provide an overview of what curretly exists, why it is not enough, and what I am doing about it.

 

Since about 2010, the Unit 4 Finance team has been posting check registers online via the district’s BoardDocs portal. If you have ever looked at the portal, you know that it is obscenely difficult to find documents unless you have some idea of where to look. For instance, Check Registers are always posted during the Regular Meeting under the Action agenda item “Bills and Treasurer’s Report”. It will always be for the prior month; so for the June Board Meeting, they posted the check registers from May. So, to give credit where credit is due, kudos to Unit 4 for posting check registers online – not many school districts do that.

But here is the problem – who looks at them? Among the small handful of people that actually take the time to hunt them down and scan through several pages of checks, who actually can make any use of it? If you want to hunt down all the transactions for a given “vendor”, you have to manually find and open each month’s check register yourself. I tried it once – it was quite painful. 🙂

 

In May of 2013 I asked Unit 4 to provide an index or some type of archive for all check registers. Stephanie Stuart and Matt Foster quickly responded and now we have a very tidy Check Register Archive. It isn’t perfect (some months are missing), but it is yet another step in a good direction, so for that I give Unit 4 another kudo. There is still an issue of tracking vendors and payments across months, which is a very manual process. Several times in the past year I have asked for read-only access to the data in one form or another, but I am always hitting legal obstructions along the way.

 

So here is my solution. Given that Unit 4 is providing a very nice central repository for check registers, and given that the PDF documents are true exports from a reporting tool (as opposed to those notorious PDF images of data that are so prevelant), without asking Unit 4 to lift a finger I can now provide the entirety of those check registers via a mildly reverse-engineered database. This is the first half of my project. The next half is to provide a handy dandy php-driven web page to allow dynamic access to those who have no idea how to connect to a database and write queries.

 

Sounds exciting, right? You are anxious to go look at the data, right? Unfortunately, the pretty stuff isn’t my forte. I can provide excel spreadsheets of the data for you, which is probably “good enough” for most everyone:

 

Why two different files? About the time I asked for the check register archive, it appears that Unit 4 changed the format of the output. The PDF files are in what’s called “fixed-width fields”, and the number and size of those fields changed near May of 2013. So another nice thing about having a database is that I can combine all these lines into one resource and make the formats a moot point.

 

More fun stuff to come later this week. I have been inspired by Adam Andrzejewski and his “Open the Books” campaign.

 

Technical details

connection string = jdbc:mysql://onyx.csit.parkland.edu:35002/u4finance

user = u4reader/redaer#4

Number of records: 50,868

data loaded with perl using CAM::PDF modules; dynamically reads all PDF’s available on Unit 4 website, parses, cleans and loads the data.

 

 

Who is this John Bambenek guy?

I had a great chat with the new Board member, John Bambenek, on Friday. My interaction with Mr. Bambenek on Friday reinforced my previous interactions with him, and I had two take-aways; 1) he is very open to talking, just drop him a note and work out a good time, 2) he is passionate about bringing a deeper level of transparency and financial accountability to the school board.

As an alumni of the University’s Computer Science department, I was somewhat compelled to start our conversation on the topic of computers, a topic that came up frequently. John teaches a 400-level course at the University dealing with operating system and network security. If I recall correctly, their latest task is to reverse-engineer a computer virus. I asked about special projects, and he mentioned one I was fascinated with, a quadracopter carrying a raspberry pi to accomplish specific tasks. Computers bled into other aspects of our dialog as well.

For instance, Bambenek has a tie into Adam Andrzejewski’s Open the Books project (I believe he said he either sat on one of the boards or consulted for them). For those not familiar with Open the Books, I encourage you check out the website and/or go read Jim Dey’s Editorial on it (11/02/2013). The basic idea is to track where and how all the money flows, or as their motto says “Every Dime. Online. In Real Time.” For me personally, I find this to be a fundamental part of modern democracy, giving normal taxpayers the tools and authority to see what their tax dollars are accomplishing. I believe it has the potential to bring the voice back to the voter, so that we can ask intelligent questions, but more importantly, give critical feedback to our elected officials so that they have concrete guidance on how to perform their duties. “Transparency” is something that becomes a bit of a buzzword when folks are campaigning, but it makes me wonder how many people really “get it.” I believe Mr. Andrzejewski gets it – and if Bambenek is following in the same path, I am happy with that. To bring the conversation to Unit 4 in particular, there are several things going on. First, we recognize and acknowledge that Unit 4 has gotten better about transparency. For instance, they have been posting check registers online for quite a while now, and even better, Read the rest of this entry »