PDF, Excel or CSV? Working with carrier commission statements
A practical guide for small and midsized P&C insurance agencies.
Every carrier sends a commission statement. Almost no two look alike. Some arrive as a PDF, some as an Excel file, some as a CSV, and some give you a choice. Before you can reconcile anything, you have to get the lines into a form you can work with, and each format has its own ways of going wrong.
What you need from every statement
Whatever the format, you are looking for the same few things on each line:
- Policy number
- Insured name
- Effective date
- Premium
- The commission amount actually paid
The amount actually paid is the starting point. Everything else helps you match that payment to the right policy in your book.
CSV: the cleanest, with a few traps
A CSV is plain text with one line per record, so it is usually the easiest to work with. The problems tend to show up when someone opens it in a spreadsheet.
- Leading zeros disappear. A policy number such as 00123456 can turn into 123456 when a spreadsheet treats it as a number.
- Long numbers get rewritten. Spreadsheets can convert a long number into scientific notation, which cannot be turned back into the original.
- Negatives look different. A chargeback may appear with a minus sign, in parentheses, or in a separate column.
- Extra rows. A header block, a totals row, or subtotals by producer can sit in the middle of the data.
Excel: familiar, but formatted for people
An Excel statement is often built to be read, not processed. That can make it harder than a CSV.
- Merged cells and multi-line headers throw off anything that expects one header row.
- Totals and subtotals sit between lines, so adding up a column counts them twice.
- Numbers stored as text, and the reverse, so a column won't add up or sort the way it should.
- Several sheets in one file, with the detail on a different tab than the summary.
- Hidden columns or rows that still hold data.
PDF: the most common, and the hardest
A PDF is a picture of a page. Nothing tells a computer where one column ends and the next begins.
- Lines break across pages, and headers and totals repeat on every page.
- Wrapped text. A long insured name or policy number can spill onto a second line.
- Copy and paste scrambles columns. Pasting a PDF into a spreadsheet often merges or misaligns the columns, which makes errors easy to miss.
- Text-based vs. scanned. If you can select and highlight the words, the PDF contains text. If you can't, it's an image of a page, which is harder to read reliably.
A quick test: try selecting a line of text in the PDF. If it highlights, you can work with the text. If it doesn't, you are looking at a scan.
Which format should you use if you have a choice?
Some carriers offer more than one format. When yours does, choose the CSV or Excel file over the PDF, because the data is already in rows and columns. Whichever you choose, check that the statement total matches what the carrier says it paid. If the lines don't add up to the total, something was dropped or counted twice.
Retyping a PDF by hand, or copying it into a spreadsheet, is where most errors creep in. The more times a number is moved by hand, the more chances it has to change.
The same statement, a different layout next month
Even with one carrier, a layout can change. A column moves, a header is renamed, a new section appears. A process that depended on one exact layout stops working without warning, and you may not notice until a line goes missing. Keep an eye on each carrier's statement for changes, and recheck your totals when something looks different.
How EZ Commissions handles the format problem
EZ Commissions accepts CSV, Excel and PDF statements. A scanned or image-only PDF has no text to read, so it may need additional processing by our team before it can be imported. There is no template to fill in, and you don't have to rebuild every carrier's statement into a special format before uploading. It identifies the policy number, insured, effective date, premium and commission on each line, then checks each line against your book and flags the ones that need attention with the reason. If a carrier's layout is new, our team maps it for you.
Explore the workflow at your own pace with a private, 48-hour demo using sample data. No appointment or live demonstration needed.