Commission statement to Excel or CSV
Open a commission statement spreadsheet (.csv or .xlsx). Columns are matched from the header words and every choice can be changed. The result is one row per statement line in a fixed layout, with the lines added up against the statement total and the payment you type, and premium × rate compared with the commission on every row that shows both.
The file is read in your browser and stays on this device. Nothing is uploaded.
The file is read in this browser and is not uploaded.
These are matched from the header words. Check them and change any that are wrong. Only the commission amount is required; premium and rate are needed for the premium × rate check.
Summary
Rows where premium × rate differs from the commission
Statement lines
What gets checked
- The sum of the lines equals the statement total you enter, and equals the payment received you enter; when both are entered they are also compared with each other.
- When the file has one total row of its own, the sum of the lines equals it.
- Where a row shows both a premium and a rate, premium × rate equals the commission to the cent. Rows that differ are listed with the calculated amount and the difference.
- The commission total is added a second time, a different way, and the two totals agree.
- The exported file is read back and its commission column adds up to the same total.
- Lines read equal lines in the result, and every line carries the amounts read from the file.
- Every row with data was read. A row that could not be read is listed with the reason and the result is marked not verified.
How it works
- Open the statement. Drop the commission statement spreadsheet (.csv or .xlsx). It is read in the browser. Title rows above the header row are skipped when the header row is found.
- Check the column mapping. Columns are matched from the header words: policy number, insured, product or line, transaction type, effective date, premium, commission rate, commission amount, and producer. Any choice can be changed, including whether rates are written as 12.5 or as 0.125.
- Enter the totals. Optionally type the total printed on the statement and the payment received. Each one entered is compared with the sum of the lines.
- Review and export. The page shows the summary, the certificate, the rows where premium × rate differs, and every line. Download Excel or CSV, or print. The CSV opens directly in the reconciler and the split calculator.
Worked example
A made-up statement with a title row, five lines, and a total row. The statement total and the payment received are both typed as $1,064.00.
| Policy number | Insured | Transaction | Premium | Rate | Commission |
|---|---|---|---|---|---|
| POL-10021 | Maple Street Bakery LLC | New | $4,800.00 | 12.5% | $600.00 |
| POL-10022 | Jordan Rivera | Renewal | $1,250.00 | 10% | $125.00 |
| POL-10023 | Northside Dental PC | Renewal | $3,333.33 | 7.5% | $250.00 |
| POL-10024 | Avery Chen | Endorsement | -$240.00 | 15% | -$36.00 |
| POL-10025 | Harbor Cycle Shop | New | $999.99 | 12.5% | $125.00 |
The five lines add up to 600.00 + 125.00 + 250.00 − 36.00 + 125.00 = $1,064.00, equal to the statement total, the payment received, and the file's own total row. Premium × rate equals the commission on all five rows: for example 3,333.33 × 7.5% = 249.99975, which rounds to $250.00, and 999.99 × 12.5% = 124.99875, which rounds to $125.00. The premium total is $10,143.32. If the second line's commission read $125.01, the lines would add up to $1,064.01, the comparisons with the statement total and the payment would fail, and that row would be listed with a difference of $0.01.
The example uses made-up names and numbers.
Questions
Which carriers' statements does it read?
It does not depend on the carrier. It reads any .csv or .xlsx with a header row by matching header words, shows its choices, and lets you change every column. When the headers cannot be matched, the page says which column is missing and links to a form for requesting support for that format.
What if my statement is a PDF?
This tool reads spreadsheets (.csv or .xlsx). Many carrier portals offer a spreadsheet download of the same statement.
How is premium × rate rounded?
To the nearest cent, with exact halves rounded away from zero, using whole-number arithmetic. A row whose commission differs from that figure by even one cent is listed with both amounts. Rates are read to two decimals of a percent; a rate with more decimals is listed as a row that could not be read.
What happens to a total row in the file?
A row labelled as a total is left out of the lines so it is not counted twice. When the file has exactly one such row, its amount is compared with the sum of the lines as an extra check.
Why is a row listed as not readable?
A row with data is never dropped silently. If its commission amount is missing, or an amount, rate, or date on it cannot be read, the row is listed with the reason and the result is marked not verified until the mapping or the file is corrected.
This tool calculates and checks numbers. It is not legal, tax, or medical advice.