An RFP Consolidator That Flags What It Cannot Read, Shown on Synthetic Sample Data

This page walks through one build from start to finish. Everything on it comes from synthetic sample data: the supplier names, contacts, rates and dates in the test files are fictional, made up to test the tool. It is not a client project, and it has not been run on anyone's real files. For which jobs suit this kind of automation in the first place, see AI consulting and automation for Niagara small business.

What the tool does

Give it a folder of spreadsheets that answer the same request for proposal. Every supplier answers the same questions, but each file is laid out a little differently: labels reworded, answers moved to a second sheet, cells merged. The tool reads them all and writes one workbook. A summary sheet puts every supplier side by side, and each supplier gets its own tab listing every field with its value, its status, and the cell it came from.

Which label counts as which field lives in one configuration file, not in the code. A label has to match one of a field's listed names exactly, with no fuzzy matching. A different spreadsheet template needs a new configuration file, not new code.

The rule: two checks, or the field is flagged

A value is written only when two things are both true. Its cell maps to a field through the configuration file, and the value passes the checks for that kind of field: a date has to fall inside the expected window, an amount has to be a plain number, and the currency has to be stated and match the one expected. If either check fails, the field is flagged, and the flag quotes what was actually in the cell.

After each run, a self-check traces every written value back to its source cell and reads it again. If it finds a value it cannot trace, the run fails, and that workbook is not to be used.

Ten sample files, built to cause trouble

The first test set was ten synthetic supplier files. Two were clean, and one of those used reworded labels the configuration file already lists. The other eight each carried a specific problem. This is what was in them, and what the tool did with each one:

• A blank cell where a contact email or a fee belonged: flagged as empty. Nothing was filled in.
• A row missing from the file entirely: flagged as not found.
• Words typed into number fields, like TBD or call, and a range typed where one number belonged: flagged as not a plain amount, with the raw text quoted.
• One rate typed once and merged down across three rows: flagged, because a merged cell cannot be tied to just one field.
• A label merged across two columns, with its value in the next cell: read, because there is only one way to read it.
• A label reworded to wording the configuration file does not list: flagged as not found, and the unmatched label is listed in the report instead of being guessed at.
• Some answers moved to a second sheet: read from the second sheet.
• A second sheet restating a date with a different value: flagged as a conflict, with both cells named.
• Dates outside the expected window: flagged, with the allowed range shown.
• A currency given only as local: the currency was flagged, and every amount in that file was held back.
• A tax rate typed as a bare decimal, with no percent sign: flagged as ambiguous, since it could be read two ways.
• A date written with slashes: flagged until someone confirms which part is the month.

Graded on files it had never seen

A tool can look good on the files it was built around. The real test was a second set of ten synthetic files, prepared separately, with an answer key that was held back and not opened during the run. The tool ran on those files, and its output was then checked against the key.

Of the graded fields, 182 were correct, 13 were correctly flagged, and 3 were flagged when they could have been read. That is 98.48% correct or correctly flagged, against a pass bar of 95%, with zero invented values, zero misread and zero missed.

All three of the cautious flags came from the one file that never stated its currency. The tool held those amounts back rather than assume a currency, which is the rule working as intended.

What this demo does not show

A demo on sample data proves the rule, not the result on real work. These limits are part of the record:

• It has not been run on a client's real files. A real template would need its own configuration file, and layouts the tool does not understand yet would be flagged, not guessed.
• It reads .xlsx spreadsheets. PDF, older .xls and .csv files, and hidden sheets are not covered yet.
• The summary shows only values traced to a source cell. It does not calculate totals.
• It makes no claim about how much faster any job gets. That has not been measured on real work.

The same rule, everywhere

Every value traces to its source, and anything unreadable is flagged for a person. That standard runs through all the automation work described on the AI consulting and automation page, and through this website too: before a page here ships, it has to pass the no-fabricate gate, which fails the build on any statistic without a cited source.

If someone in your business re-keys spreadsheets by hand, describe the job in a sentence or two, by phone at 289-402-8169 or by email at hello@livingwebsites.ca. You will get a straight answer on whether it is a good fit.