I’ve been submitting reports to the county regarding issues I have identified in reports reviewed by the Warren County Finance Audit Committee (WCFAC). At the end of the December WCFAC meeting, Supervisor Jamieson (the chair of the WCFAC) expressed his interest in the tools that I’ve been using to review the data. After Dr. Jamieson responded to my first email.

In his response, Dr. Jamieson requested my input on a policy, which I needed to consider. It got a bit lengthy and deserved its own email. After evaluating the subject matter, I decided to send it to all supervisors under a separate email and keep this one on topic. If I only sent my thoughts to Dr. Jamieson, it would have been just a disagreement that would be easy to frame, minimize, and personalize – regardless of intention. I shifted the process concern to the governing body.

This email keeps on topic with the original email, while indicating that the response to his question is in a separate email.

Email

Thank you for getting back to me. The holidays are keeping everyone busy. I’ve not heard of Zapier tools. Usually, I build everything up from scratch to gain more control under the hood. I often run into walls when I want to know more information. As a programmer, I’ve often run into problems with the constraints that automated tools impose.

My data intake process is… manually repeatable. It takes a couple of minutes. I’ve got scripts to clean the CSV files, merge them into a single file that can be imported into a spreadsheet, and create a SQL script to import into MySQL/MariaDB, which the website runs on in a traditional LAMP stack. Issue #5 on GitHub addresses the ability to upload a CSV file, but I haven’t worked on it yet. The trouble is that I am not provided with a unique key that identifies the data in the county ledger, so I need to use a composite key (account number + check number) based on multiple fields. However, if the county makes changes based on some of my findings (such as changing the account number), that composite key could change without my knowledge, resulting in a duplicate entry.

If there were ever an API exposed, or a CSV/database file somewhere that I could request over the internet, then yes – the idea would be that I could automate the process. I’ve worked on getting many systems to talk to each other in my career, and on importing data from old systems that never had data transfer in mind.

I’ve made a few reports so far. Most of it is asking myself questions and then looking at the data. The website is to make it easier to adapt to my queries and drill down into the data, while also making it possible to share a link pointing to precisely the data that I see, so that others can understand the same data from their perspective, or the format of data they are most familiar with. I’d like to eventually add the ability to save drill-downs and add notes on different transactions to actually “work” with the data, rather than view it.

I looked at ClearGov, but it’s very uninformative to me. It only provides a high-level view of yearly expenditures. It doesn’t break them down by fire department or similar categories as I understand them, and you can’t download the underlying data used to build the report it’s displaying. With control over how the data is processed, I can drill down into departments and individual utility accounts to compare seasonal data month by month and identify any unexplained spikes. However, since ClearGov prefers pie charts, I’ve added a separate tab on the website that shows data broken down into multi-layered pie charts.

I’ve shared my response to the policy in a separate email to the board.

Best,

Lewis E. Moten III