Reconciliation ·
Insurance Commission CSV Cleanup: Policy Numbers, Dates, and Negative Amounts
By SplitVerde editorial team · Insurance agency operations
A matching engine can be correct and still give the wrong answer if the imported carrier data is wrong.
Key principle
Preserve carrier evidence, investigate policy-level transactions, and keep projected, reported, approved, and deposited amounts distinct.
Preserve original statements before cleaning
Save the original carrier file, reporting period, row count, and signed total before transforming anything. Cleaning should create a separate working representation rather than changing the only copy. Preserve enough information to trace every matched transaction to the source. A source file is evidence, while a cleaned file is an interpretation of that evidence.
Before acting on a discrepancy, preserve the original carrier statement and verify that the transaction data was imported accurately. Compare the row count and signed total with the source. Check unusual policy identifiers, date formats, and negative adjustments. A CSV that opens successfully can still lose leading zeros; a text-based PDF can still extract columns incorrectly. Keep the source file and the working version so the next reviewer can reconstruct the calculation. If the input is incomplete, label the review incomplete rather than manufacturing a match or shortage.
Protect policy numbers and leading zeros
Policy 00123456 can become 123456 when spreadsheet software treats it as a number. Import identifiers as text and retain the original beside any normalized matching key. Test carrier-specific rules on prefixes, suffixes, and long identifiers before using them broadly. Do not strip all punctuation blindly because unrelated policies might then collide.
An expected commission should have a documented basis: policy activity, eligible premium where relevant, transaction type, carrier schedule, and timing conditions. Do not treat every policy effective date as a payment due date. Carrier processing cycles and premium collection may affect eligibility. Record the schedule version and distinguish projected commission, statement-reported commission, and cash deposited. These numbers answer different questions. If the schedule is unclear, mark the expectation uncertain and seek confirmation rather than creating a false late-payment alert.
Normalize dates without guessing
A value such as 04/05/2026 is ambiguous without knowing the carrier's date convention. Determine the source format before conversion. Keep effective, transaction, statement, and deposit dates separate. Flag ambiguous values rather than assigning an invented date. Record the rule used for normalization so the next import follows the same convention.
Monthly totals can conceal policy-level mistakes. Suppose two policies are expected to pay $200 each, but the carrier reports $250 for one and $150 for the other. Both totals equal $400 while each policy has a $50 difference. Match using carrier, policy reference, transaction type, statement period, and signed amount. A single policy may have several events over its life, so do not force all transactions into one net row. Review ambiguous matches instead of guessing based only on similar names or amounts.
| Policy | Expected | Carrier reports | Variance |
|---|---|---|---|
| A | $200 | $250 | +$50 |
| B | $200 | $150 | −$50 |
| Total | $400 | $400 | Two exceptions |
Fictional figures. Matching totals do not establish policy-level accuracy.
Keep signed amounts intact
Negative amounts may be reversals, corrections, or chargebacks. Parentheses and debit-credit columns require consistent conversion rules. If positive amounts total $1,000 and negative adjustments total $150, the signed net is $850. Check that total against the source statement. Never discard a negative transaction merely because it makes a policy total look unusual.
Every unresolved exception needs an owner, a source reference, and a follow-up date. Separate confirmed underpayments from timing questions, unexpected positives, chargebacks, and duplicate candidates. When contacting the carrier, provide the policy reference, statement period, expected amount, and rule used for the calculation. Save the response. A representative's promise to fix something next month is not a verified recovery. Leave the case open until a later statement or documented explanation supports closing it.
Validate spreadsheet and PDF imports
Workbooks can include multiple sheets and summary rows. Text-based PDFs may extract amounts into the wrong columns. Compare row counts, signed totals, and sample policy references with the original. Do not assume scanned-image OCR or direct carrier integrations are available. If an import looks wrong, stop and correct the mapping before investigating the carrier.
Producer compensation is a separate layer. The agency's carrier commission is not automatically an individual producer's entitlement. New-business and renewal rates, shared accounts, and agreement effective dates can change the calculation. A negative carrier transaction does not automatically authorize a producer deduction; review the agreement and applicable law. Keep calculated, approved, and actually paid amounts separate. An exported payout report is not proof that money was transferred. This separation prevents reconciliation errors from becoming compensation disputes.
Create a carrier-specific import checklist
Document preferred format, policy-number rules, date convention, sign handling, and known summary rows for each carrier. Retest when exports change. A sudden rise in unmatched rows may be a formatting problem rather than a new wave of unpaid commissions. Preserve the mapping notes alongside a verified sample file.
At month-end, archive source statements, validation checks, matched and unmatched rows, unresolved cases, and any producer approvals. Compare carrier remittances with bank deposits separately from policy-level accuracy. Link later corrections to the earlier issue instead of rewriting historical statements. Look for recurring problems by carrier; repeated discrepancies may indicate misunderstood schedules or import mapping changes. A repeatable review process should make uncertainty visible and actionable rather than hiding it behind a green dashboard.
Frequently asked questions
Can matching statement totals hide an error?
Yes. Offsetting policy-level differences can hide behind matching totals. Review individual transactions.
Is an expected commission already cash received?
No. Expected amounts, carrier-reported amounts, and bank deposits are separate checkpoints.
Can a carrier chargeback automatically reduce producer pay?
Not necessarily. Review the producer agreement and applicable law before changing compensation.
Related reading
See our reconciliation overview, monthly checklist, and producer payout guide.