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. Here is my email sent a few hours later.

Email

Supervisor Richard Jamieson,

I appreciated hearing that you were interested in the tools I’ve been using to review data. I’m happy to walk through anything you’d like to see. If you’d like to meet, please feel free to choose a time on my Calendly link: https://calendly.com/lewismoten

If you’d prefer an overview of the tools, methods, and background behind my work, the rest of this email should cover most of what you were planning to ask. I’ve also included a screenshot of the current web page with a few filters applied, but you are free to try it out for yourself:

https://wcf.regaldragondanceparty.com

Most of my work started in Google Sheets, especially for quick charting or comparisons.

Fuel Report 

I initially tried parsing the PDFs, but each month had a different structure, and some couldn’t be parsed reliably. Eventually, I submitted my first FOIA request to the county.

Transactions from Dec 30, 2024 – Sep 30, 2025 (FOIA 25-849)

https://docs.google.com/spreadsheets/d/1GHEbtV8AqKzzUnyH6U-_i_r3dPpl3L3iD1oTvTmgmMQ/edit?usp=sharing 

This spreadsheet uses array formulas, queries, VLOOKUPs, pivot tables, and charts. VINs were decoded using NHTSA’s API: https://vpic.nhtsa.dot.gov/api/

General Ledger

Checks from Jan 4, 2024, to Oct 30, 2025 (FOIA 25-899)

https://docs.google.com/spreadsheets/d/1JS9MnGGilX8H0OLOTEN2XeFGnA_tNhr3f6jP66EGxAw/edit?usp=sharing

My initial analysis used a combined CSV dataset. I removed the subtotal rows and kept only rows that begin with a seven-digit purchase order. After hitting spreadsheet limits, I moved to a more capable toolset.

Open-Source Ledger Tools

I created an open-source project to automate data conversion and build SQL scripts for deeper analysis:

https://github.com/lewismoten/wcfac-general-ledger

A list of features that I would like to add is here:

https://github.com/lewismoten/wcfac-general-ledger/blob/master/src/LedgerPage/todo.md

The conversion script is here:

https://github.com/lewismoten/wcfac-general-ledger/blob/master/utils/convertApRegister.js

The script creates a clean LEDGER table and splits several fields.

• Invoice numbers often contain embedded metadata, including month/year strings.
• Account numbers are separated into their logical components (R/E, OL1, OL2, Fund, Department, Account).

The web interface uses a classic LAMP stack (Linux, Apache, MariaDB, PHP) and a React/TypeScript front end. It’s lightweight enough to run on a $15 Raspberry Pi Zero.

Reporting Features I‘ve Built

Fiscal Year Monthly Chart

• Uses Recharts to compare monthly and year-over-year trends.
• Supports grouping by year, department, account, vendor, invoice number, etc.
• Paging added to handle large datasets.

Totals Chart

• Column chart for comparing fiscal-year totals.

Advanced Filters

• Auto-complete dropdowns with multi-select.
• URL query-string synchronization for shareable filtered views.
• Consolidation of multiple vendor numbers under a single entity (e.g., 14 Mansfield listings).

Filtered Data Table

• Virtualized table showing 100 rows at a time.
• Median-based color gradients to highlight outliers.
• Full visibility of original CSV fields.

Drill-Down Report

• Hierarchical display: Department → Account → Vendor with yearly totals.

Layered Pie Chart

• Built this week; displays hierarchical levels visually.

The web page is still in a working layout rather than a finished design, but all functionality is active.