Vetting carriers with a spreadsheet — what it catches and what it misses

Most small brokerages run a script into Excel rather than paying for a tool. It works, up to a point. Here is precisely where that point is.

Plenty of small brokerages do not pay for carrier vetting. They run a scheduled script that pulls FMCSA data into a spreadsheet, and it does the job for a fraction of the cost.

That is a reasonable engineering decision. This is not an argument against it — it is a description of the specific places the approach breaks, so you can patch them.

What a spreadsheet gets right

Authority status. Straightforward field, straightforward answer
Identity. Legal name, address, fleet size, phone
Recency of registration. Useful signal on its own
Scale. A script checks 400 carriers as easily as four, which is more than most manual

processes manage

If your sheet covers those, it is already ahead of a broker checking SAFER by hand.

Where it breaks

1. Authority status is not an insurance check

The single biggest gap. Authority and insurance are separate FMCSA files, not updated together. A carrier can have no coverage on file while the authority field reads Active.

Sampled across a 500-carrier list, 6.6% showed Active authority alongside an insurance exception — roughly one row in fifteen, green in the sheet, uninsured in fact.

The script is not broken. It reads the field it was told to read. The field does not mean what it appears to mean.

2. The active filing table lists cancelled policies

If your sheet pulls "insurance on file" and stops, it will report coverage that has been cancelled. Cancellations live in a separate record keyed by policy number and have to be joined before the answer is meaningful.

This is the fix with the largest effect for the least work.

3. Form codes are not interchangeable

BMC-91X, BMC-91, BMC-82, BMC-35 — satisfy the liability requirement
BMC-34 — cargo, at $5,000. Does not
BMC-84, BMC-85 — broker bonds at $75,000. Not carrier insurance at all

A sheet keyed on "has an insurance filing" counts all of these as coverage. Full form-code table.

4. Excess is counted as primary

Liability filings carry a class: P primary, E excess. An excess layer with no primary underneath satisfies nothing, but it carries the larger dollar figure — so a naive MAX() over coverage amounts ranks the broken record above the compliant one.

5. The 2026 system change may have quietly broken your pull

FMCSA moved to a new registration system in May 2026 and froze the legacy data tables on 2026-05-14. Anything built against the old endpoints either stopped updating or now serves a snapshot that never changes.

A silently stale scraper is indistinguishable from a working one. The question is not "does it run" but "when did we last confirm it returns fresh data" — check the maximum date in your own table against today.

6. Nobody is watching the sheet

The deepest limitation is not technical. A spreadsheet answers when someone opens it. Carriers lose coverage on Tuesdays when nobody is looking, and the gap between a lapse and a suspension order runs a median of 17 days — a window that opens and closes without a single query being run.

How to patch it

In rough order of value per hour:

1. Join cancellations to active filings by policy number. Fixes the largest error 2. Filter by form code. Count only BMC-91X / 91 / 82 / 35 as liability 3. Check the class. Exclude class E when testing for primary coverage 4. Compare the amount to your requirement, not the $750,000 federal floor 5. Assert freshness. Fail loudly if your latest record is older than a couple of days — a silent zero looks exactly like a quiet week 6. Run it on a schedule and diff it. The value is in what changed, not the snapshot

Points 1 through 4 are a morning's work and remove most of the false negatives.

The honest comparison

A well-patched spreadsheet gets you most of the way. What it does not give you is someone watching every morning and telling you what changed — which is the part that catches the 17-day window, and the part that is tedious to build and maintain yourself.

Whether that is worth paying for depends on what a single uninsured load costs you. For most brokerages that number is large enough that the question answers itself, but it is a real calculation and not everyone lands in the same place.