Automation · Reconciliation

Reconciling the city council's report against the agency's own records

A command-line tool that sets the council's PDF of municipal tax instalments against the agency's internal spreadsheet, and returns a results workbook with every discrepancy flagged by colour.

The problem

Curitiba city council periodically issues a PDF listing the property registrations whose tax instalments have been sent to the collection agreement, with a due date. The registration number is what identifies a property in the municipal register.

The agency keeps its own records, exported as a spreadsheet, holding the internal property reference, the matching municipal registration, who is responsible for payment, whether that amount should be charged on to the tenant, the total, the number of instalments and the applicable share.

Cross-checking the two is obligatory and nobody wants to do it. There are hundreds of rows per period, the registration numbers run to fourteen punctuated digits, and the consequences of missing something are concrete: an instalment the council charges that is absent from the records ends up with nobody to pay it, and a property where the agency is the responsible party but which is not flagged to charge the tenant is money going out and not coming back.

How it is used

There is no interface. You create a folder next to the executable, drop in the council's PDF and the records spreadsheet, and run it. The tool processes every folder that does not already hold a results file, which lets you queue up several periods and collect them all at once.

The output is a new spreadsheet, alongside the source files, with five sheets: an aggregated view by registration number, the record-by-record detail with the totals summed on each side, the two versions restricted to records that appear in the PDF, and one sheet for everything that exists in the PDF with no counterpart in the records.

Reading by colour

The people checking this work in Excel, not in an application. So the output speaks Excel: the row colour says what to look at, and the observations column says why. No colour ever stands alone, each one always has its text beside it.

Yellow rowSomething is wrong with this record. The explanation sits in the observations field.

Red rowThe record in the agency's spreadsheet has no counterpart in the council's PDF.

Blue cellThe owner is named as the party responsible for payment.

Orange cellThe agency is responsible, but the record is not flagged to charge the tenant.

Green rowOn the detail sheet, the summed totals agree.

White rowNo observations. This is the normal state and most of the rows.

The decision that mattered

The temptation was to build an application with a user interface. It would have taken weeks, and nobody would have opened it more than once a quarter, which is how often this work comes round.

What I did instead was respect the workflow that already existed. The team receives files, puts them in a folder, and gets another file back, in the format they already know how to work in. The tool was ready in two days and needs no training at all.

The honest trade-off is that a tool with no interface ages badly if the council changes its PDF format, because nobody is looking at it between uses. That is an acceptable trade for a quarterly process, and would not be for a daily one.