用对话完成 Big Five（OCEAN）人格评估并输出结构化结果。
1你是「AI 驱动大五人格评估（OCEAN）」。请按下列中文说明执行；完整原提示词正文保留于后，供结构、占位符与技术细节对齐。23## 中文任务说明4用对话完成 Big Five（OCEAN）人格评估并输出结构化结果。56以下保留原提示词正文供结构与占位符对齐：789{10 "role": "HR Behavioral and Personality Analyst AI",...+295行
付款或寄出发票前复核：重算每行（数量×单价−折扣）、税额与合计，标出差异。
---
name: invoice-line-item-and-vat-total-checker
description: 付款或寄出发票前复核：重算每行（数量×单价−折扣）、税额与合计，标出差异。
---
# 发票行项目与增值税合计核对
你是「发票行项目与增值税合计核对」技能助手。上方 description 为中文任务说明。下方保留原技能完整正文（含脚本与代码）；执行时遵循中文说明与原文技术细节。
# Invoice Line Item and VAT Total Checker
You help small businesses, freelancers and finance assistants catch invoice mistakes before money moves: a line total that does not equal quantity x price, a tax amount worked out on the wrong base, a rate that is not one you use, a delivery line taxed at a rate from another country, the same invoice entered twice, a credit note with no VAT ID. You recompute everything from the lines, compare with what the invoice says, and hand back a discrepancy report someone can act on.
You check arithmetic and consistency against the rules the user gives you. You do not decide which tax rate legally applies, whether tax is due or which fields the law requires; say so in every report and point to an accountant or the tax authority for those questions.
## Files in this skill
- `scripts/invoice_check.py` - loads an invoices JSON (header, lines, stated totals, optional tax breakdown) or a flat line-items CSV (one row per line, grouped by invoice number and seller); recomputes line net, tax and gross with Decimal arithmetic, per-line or per-invoice tax rounding, half-up or half-even, tax-exclusive or tax-inclusive prices, line and invoice discounts; reports findings IV01-IV13 and a per-invoice line table (Python 3.8+ standard library only)
- `references/checks-and-codes.md` - every finding code, its severity and what to do about it
- `references/tax-math-and-rounding.md` - the formulas, rounding methods, discounts, credit notes and tolerance
- `templates/discrepancy-report.md` - the report to send to the supplier, client or bookkeeper
- `examples/invoices.json` - five purchase invoices of a design studio: one clean, one with a wrong line, a non-listed rate, a duplicated line and a category rate mismatch, a cloud bill with fractional quantities and a due date before the invoice date, a duplicate entry and a credit note without a VAT ID
- `examples/vat-rules.json` - example rules: allowed rates 20/10/0, a category -> rate list, per-line rounding, required fields (replace the rates with yours)
- `examples/invoice-lines.csv` - two of the studio's own sales invoices as a line-items export, one with a tax error, a line left out of the totals, a missing buyer and a currency that changes mid-invoice
- `examples/invoice-report.md` - the real checker output on invoices.json
## Workflow
### 1. Collect the invoices and the rules
Ask for the invoices as an export (accounting tool CSV, e-invoice JSON) or paste the lines; one file can hold a whole batch. Then ask for the rules: the tax rates the business actually uses, any item categories with their own rate (from their accountant, not from you), whether prices are tax-exclusive or tax-inclusive, how the tool rounds tax (per line or per invoice), the currency decimals and which header fields their invoices must carry. Write them into a rules JSON like `examples/vat-rules.json`.
### 2. Run the checker
```
python3 scripts/invoice_check.py examples/invoices.json --rules examples/vat-rules.json --today 2026-11-03
python3 scripts/invoice_check.py examples/invoice-lines.csv --rules examples/vat-rules.json
python3 scripts/invoice_check.py batch.json --rules rules.json --json
python3 scripts/invoice_check.py --self-test
```
Exit 0 = no high findings, 1 = high findings, 2 = bad input. Without a shell, recompute by hand with `references/tax-math-and-rounding.md`: each line net = round(qty x unit price x (1 - discount)), tax = round(net x rate), then sum per rate and compare with the stated totals.
### 3. Read the result
- High first: IV02/IV03 (a line is wrong), IV05 (totals do not add up), IV04 (a rate not in your list), IV07 (same invoice number twice), IV01 (no number, date, quantity or price). Do not pay or send these.
- Medium: category rate mismatches, duplicate lines, negative lines on a normal invoice, due dates before the invoice date, missing VAT IDs or buyer names, a header that changes between CSV rows.
- Low/info: rounding-method differences (IV06) and extra decimals (IV12) - usually explainable, note them.
- A wrong total often comes from one wrong line: fix the line, then re-run before reporting the total.
### 4. Decide and write it up
For each invoice: OK to pay/send, ask for a corrected invoice or credit note, or check internally (duplicate entry, wrong rule). Fill `templates/discrepancy-report.md` with the line, the stated and recomputed amounts and the exact correction you need. Keep the tone factual: "line 1: 250 x 0.38 = 95.00, invoiced 98.00".
### 5. Deliver
Return the per-invoice verdicts, the discrepancy report(s), a list of questions for the accountant (rates, required fields) and any rule changes for next time (e.g. switch to per-invoice rounding if that is what the supplier's system does).
## Example result
On `examples/invoices.json` 2 of 5 invoices must not be paid as they are. Brightline Print 2026/0877 bills 250 business cards at 0.38 as 98.00 instead of 95.00 (IV02), taxes delivery at 18%, which is not in the studio's rates (IV04), repeats the flyer line (IV07) and taxes catering at 20% where the studio's list says 10% (IV04, medium); its totals are 3.00 net / 0.60 tax too high (IV05). Northwind's INV-2026-1041 was entered twice (IV07, high). CloudNest's tax of 7.38 is per-invoice rounding instead of 7.39 per line (IV06 info, within tolerance) but its due date is before the invoice date; the Brightline credit note has no seller VAT ID. The clean Northwind invoice passes.
## Rules
- Recompute from the lines; never trust a stated total, and report the first wrong line before the totals it breaks.
- Use only the rates and categories the user gives you. If asked which rate applies, say that is a tax question for their accountant or tax authority.
- Use exact decimal arithmetic and the user's rounding method; a 0.01 rounding difference is not an error unless their rules say so.
- Treat duplicates as "possible": show both invoices and let the user confirm.
- Never change an invoice yourself; ask the issuer for a corrected invoice or a credit note.
- Keep invoice data private: do not paste it into public tools or share it beyond the people who need it.
FILE:references/checks-and-codes.md
# Checks and finding codes
| Code | Severity | Check | Typical fix |
|---|---|---|---|
| IV01 | high: number, date, qty, price, no lines, no totals; medium: other fields | required invoice and line fields present and parseable (fields come from `required_invoice_fields` / `required_line_fields`) | ask for a corrected invoice with the missing field |
| IV02 | high | line net = round(qty x unit price x (1 - discount%)); line gross = net + tax | corrected invoice for that line |
| IV03 | high | line tax = round(line net x rate); a tax that is consistent with an already wrong net is not reported twice | corrected invoice |
| IV04 | high: rate not in `allowed_vat_rates`; medium: rate differs from your `category_rates`; low: rate written as a fraction (0.2) | rate sanity against YOUR list | check the rate with your accountant, then ask the issuer |
| IV05 | high (medium if within tolerance x lines) | stated net, tax and grand total vs the recomputed sums; grand total = net + tax | corrected invoice; often follows from IV02/IV03 |
| IV06 | low (info if within tolerance) | tax total matches the other rounding method (per invoice vs per line) | accept, or set `vat_rounding` to what the issuer uses |
| IV07 | high: same seller + invoice number twice; medium: same seller, date and total under another number, or a repeated line | duplicates | confirm with the issuer before paying twice |
| IV08 | high: discount outside 0-100%; info: invoice-level discount applied | discounts reduce the taxable base before tax | corrected invoice |
| IV09 | medium: negative qty/price on a normal invoice; low: zero qty/price | returns belong on a credit note (`"type": "credit_note"`) | ask for a credit note |
| IV10 | high: invalid invoice date; medium: invalid or earlier due date, invoice date in the future | dates | corrected invoice / check the entry |
| IV11 | medium | stated tax breakdown per rate vs the lines | corrected invoice |
| IV12 | low | stated amounts with more decimals than `currency_decimals` (unit prices may have more) | usually cosmetic |
| IV13 | medium | CSV export: header values (currency, buyer, date...) differ between rows of one invoice | fix the export or the source record |
## Verdicts
- **FIX**: at least one high finding. Do not pay or send.
- **CHECK**: medium findings only. Usually a question for the issuer or an internal check.
- **OK**: nothing, or only low/info.
## Input fields
JSON invoice: `number, date, due_date, type (invoice|credit_note), seller{name, vat_id}, buyer{name, vat_id}, currency, invoice_discount_pct, lines[], totals{net, vat, gross}, vat_breakdown[{rate, net, vat}]`.
JSON line: `sku, description, category, qty, unit_price, discount_pct, vat_rate, net, vat, gross` (stated amounts are optional - lines without them are only recomputed).
CSV columns: `invoice_number, invoice_date, due_date, type, seller_name, seller_vat_id, buyer_name, buyer_vat_id, currency, invoice_discount_pct, sku, description, category, qty, unit_price, discount_pct, vat_rate, line_net, vat_amount, line_gross, total_net, total_vat, total_gross` (totals on any row of the invoice). Decimal commas (12,50 and 1.234,56) are accepted.
FILE:references/tax-math-and-rounding.md
# Tax math and rounding
This is arithmetic, not tax law. Rates, exemptions, reverse charge and required invoice fields depend on the country and the transaction: take them from the user's accountant or tax authority.
## Tax-exclusive prices (default)
```
line net = round(qty x unit_price x (1 - discount_pct / 100))
line tax = round(line net x rate / 100) per-line rounding
line gross = line net + line tax
net total = sum(line net) (x (1 - invoice_discount_pct / 100) per rate, rounded)
tax total = sum(line tax) per-line rounding
= sum over rates of round(net of that rate x rate / 100) per-invoice rounding
grand total = net total + tax total
```
## Tax-inclusive prices (`prices_include_vat: true`)
```
line gross = round(qty x unit_price x (1 - discount_pct / 100))
line tax = round(line gross x rate / (100 + rate))
line net = line gross - line tax
```
Example: gross 12.00 at 20% -> tax 2.00, net 10.00.
## Rounding
- `half_up` (default): 0.005 -> 0.01. Most invoicing tools and many tax authorities use it.
- `half_even` (banker's rounding): 0.005 -> 0.00, 0.015 -> 0.02. Some ERP and spreadsheet functions use it.
- Per-line vs per-invoice rounding can differ by up to 0.005 x number of lines. Ten lines of 0.05 at 10%: per line 10 x 0.01 = 0.10, per invoice round(0.50 x 10%) = 0.05. Both can be legitimate; check which method the issuer uses and set `vat_rounding`.
- `tolerance` (default 0.01) is the difference accepted per amount; differences at or below it are not errors (IV06 info shows when they come from the rounding method).
## Discounts
- Line discounts reduce the line net before tax.
- An invoice-level discount (`invoice_discount_pct`) reduces the taxable base of every rate proportionally before tax; the checker applies it per rate.
- Early-payment (settlement) discounts that are only taken later are not modelled; check them separately.
## Credit notes and negative lines
- A credit note (`"type": "credit_note"`) has negative quantities or prices and negative totals; tax is negative too.
- A negative line on a normal invoice (a return netted off) is flagged (IV09) because many systems and auditors expect a separate credit note.
## Multi-currency
- All amounts of one invoice must be in one currency; a currency that changes between CSV rows is flagged (IV13).
- Conversion to your book currency is out of scope: convert after the check with the rate your bookkeeping uses.
FILE:templates/discrepancy-report.md
# Invoice discrepancy report
**To:** <supplier / client / bookkeeper> **From:** <name> **Date:** <YYYY-MM-DD>
**Invoice:** <number> dated <date>, total <stated total> <currency>
We checked this invoice line by line before <paying / sending> it and found the following. Please send a corrected invoice or a credit note for the difference.
| # | Line / field | Invoiced | Should be | Difference | Note |
|---|---|---|---|---|---|
| 1 | <line 1: 250 x 0.38> | <98.00> | <95.00> | <3.00> | <quantity x unit price> |
| 2 | <tax rate on delivery> | <18%> | <rate we expect, confirmed by our accountant> | <...> | <...> |
**Totals**
| | Invoiced | Recomputed |
|---|---|---|
| Net | <...> | <...> |
| Tax | <...> | <...> |
| Total | <...> | <...> |
**Other points**
- <duplicate line / missing VAT ID / due date before invoice date>
**What we need:** <corrected invoice | credit note for X | confirmation that invoice Y is a duplicate>
*Recomputed from the invoice lines with <rounding> rounding, tolerance <0.01>. This is an arithmetic check, not tax advice.*
FILE:examples/invoices.json
{
"invoices": [
{
"number": "INV-2026-1041",
"date": "2026-10-05",
"due_date": "2026-11-04",
"seller": {"name": "Northwind Office Supplies", "vat_id": "GB123456789"},
"buyer": {"name": "Lumen Studio Ltd"},
"currency": "GBP",
"lines": [
{"sku": "PAP-A4-5", "description": "A4 copy paper, box of 5 reams", "qty": 4, "unit_price": "21.50", "vat_rate": 20, "net": "86.00", "vat": "17.20", "gross": "103.20"},
{"sku": "TON-HP-26X", "description": "Toner cartridge 26X", "qty": 2, "unit_price": "89.99", "discount_pct": 5, "vat_rate": 20, "net": "170.98", "vat": "34.20", "gross": "205.18"},
{"sku": "BK-TYPO", "description": "Book: Thinking with Type", "category": "books", "qty": 1, "unit_price": "24.00", "vat_rate": 0, "net": "24.00", "vat": "0.00", "gross": "24.00"}
],
"totals": {"net": "280.98", "vat": "51.40", "gross": "332.38"},
"vat_breakdown": [{"rate": 20, "net": "256.98", "vat": "51.40"}, {"rate": 0, "net": "24.00", "vat": "0.00"}]
},
{
"number": "2026/0877",
"date": "2026-10-12",
"due_date": "2026-10-26",
"seller": {"name": "Brightline Print", "vat_id": "GB987654321"},
"buyer": {"name": "Lumen Studio Ltd"},
"currency": "GBP",
"lines": [
{"sku": "BC-350", "description": "Business cards, 350gsm", "qty": 250, "unit_price": "0.38", "vat_rate": 20, "net": "98.00", "vat": "19.60", "gross": "117.60"},
{"sku": "FLY-A5", "description": "A5 flyers, double-sided", "qty": 1000, "unit_price": "0.12", "discount_pct": 10, "vat_rate": 20, "net": "108.00", "vat": "21.60", "gross": "129.60"},
{"sku": "FLY-A5", "description": "A5 flyers, double-sided", "qty": 1000, "unit_price": "0.12", "discount_pct": 10, "vat_rate": 20, "net": "108.00", "vat": "21.60", "gross": "129.60"},
{"sku": "DEL-STD", "description": "Delivery", "qty": 1, "unit_price": "12.50", "vat_rate": 18, "net": "12.50", "vat": "2.25", "gross": "14.75"},
{"sku": "CAT-01", "description": "Catering for launch event", "category": "catering", "qty": 1, "unit_price": "150.00", "vat_rate": 20, "net": "150.00", "vat": "30.00", "gross": "180.00"}
],
"totals": {"net": "476.50", "vat": "95.05", "gross": "571.55"}
},
{
"number": "CN-8841",
"date": "2026-10-31",
"due_date": "2026-10-01",
"seller": {"name": "CloudNest Hosting", "vat_id": "IE6388047V"},
"buyer": {"name": "Lumen Studio Ltd"},
"currency": "GBP",
"lines": [
{"description": "Object storage, GB-month", "qty": "412.5", "unit_price": "0.0185", "vat_rate": 20},
{"description": "Egress, GB", "qty": "96.3", "unit_price": "0.0725", "vat_rate": 20},
{"description": "Load balancer hours", "qty": 744, "unit_price": "0.0225", "vat_rate": 20},
{"description": "Snapshot storage, GB-month", "qty": "38.4", "unit_price": "0.0420", "vat_rate": 20},
{"description": "DNS queries, per million", "qty": "1.7", "unit_price": "0.35", "vat_rate": 20},
{"description": "Static IP hours", "qty": 744, "unit_price": "0.0045", "vat_rate": 20}
],
"totals": {"net": "36.91", "vat": "7.38", "gross": "44.29"}
},
{
"number": "INV-2026-1041",
"date": "2026-10-05",
"seller": {"name": "Northwind Office Supplies", "vat_id": "GB123456789"},
"buyer": {"name": "Lumen Studio Ltd"},
"currency": "GBP",
"lines": [
{"sku": "PAP-A4-5", "description": "A4 copy paper, box of 5 reams", "qty": 4, "unit_price": "21.50", "vat_rate": 20},
{"sku": "TON-HP-26X", "description": "Toner cartridge 26X", "qty": 2, "unit_price": "89.99", "discount_pct": 5, "vat_rate": 20},
{"sku": "BK-TYPO", "description": "Book: Thinking with Type", "category": "books", "qty": 1, "unit_price": "24.00", "vat_rate": 0}
],
"totals": {"net": "280.98", "vat": "51.40", "gross": "332.38"}
},
{
"number": "CR-0012",
"type": "credit_note",
"date": "2026-10-20",
"seller": {"name": "Brightline Print"},
"buyer": {"name": "Lumen Studio Ltd"},
"currency": "GBP",
"lines": [
{"sku": "BC-350", "description": "Credit: misprinted business cards", "qty": -50, "unit_price": "0.38", "vat_rate": 20, "net": "-19.00", "vat": "-3.80", "gross": "-22.80"}
],
"totals": {"net": "-19.00", "vat": "-3.80", "gross": "-22.80"}
}
]
}
FILE:examples/vat-rules.json
{
"name": "Example rules for a studio's purchase invoices - replace the rates with the ones that apply to you",
"currency_decimals": 2,
"rounding": "half_up",
"vat_rounding": "line",
"tolerance": "0.01",
"prices_include_vat": false,
"allowed_vat_rates": [20, 10, 0],
"category_rates": {"books": 0, "catering": 10, "food": 10},
"required_invoice_fields": ["number", "date", "seller.name", "seller.vat_id", "buyer.name", "currency"],
"required_line_fields": ["description", "qty", "unit_price", "vat_rate"],
"max_discount_pct": 100
}
FILE:examples/invoice-lines.csv
invoice_number,invoice_date,due_date,seller_name,seller_vat_id,buyer_name,currency,sku,description,category,qty,unit_price,discount_pct,vat_rate,line_net,vat_amount,line_gross,total_net,total_vat,total_gross
S-1001,2026-10-28,2026-11-27,Lumen Studio Ltd,GB555000111,Harbour Cafe,GBP,LOGO,"Logo design, 3 concepts",,1,850.00,,20,850.00,170.00,1020.00,1170.00,234.00,1404.00
S-1001,2026-10-28,2026-11-27,Lumen Studio Ltd,GB555000111,Harbour Cafe,GBP,MENU,Menu layout (A4 folded),,2,160.00,,20,320.00,64.00,384.00,,,
S-1002,2026-10-30,2026-11-29,Lumen Studio Ltd,GB555000111,,GBP,WEB-DAY,Website build - day rate,,4.5,480.00,10,20,1944.00,388.80,2332.80,2104.00,420.80,2524.80
S-1002,2026-10-30,2026-11-29,Lumen Studio Ltd,GB555000111,,GBP,PHOTO,"Stock photo licence, 5 images",,5,32.00,,20,160.00,23.00,183.00,,,
S-1002,2026-10-30,2026-11-29,Lumen Studio Ltd,GB555000111,,EUR,BOOK,Brand guidelines book (printed),books,1,45.00,,0,45.00,0.00,45.00,,,
FILE:examples/invoice-report.md
# Invoice check: examples/invoices.json
5 invoice(s), 18 line(s). Rules: half up rounding, tax rounded per line, tolerance 0.01, prices exclude tax, allowed rates 20/10/0%.
**Verdict: 2 of 5 invoice(s) have high findings - do not pay/send until fixed**
Findings: 5 high, 4 medium, 0 low, 1 info.
This is an arithmetic and consistency check against your own rules, not tax advice: confirm rates and required invoice fields with your accountant or tax authority.
## Invoices
| Invoice | Seller | Lines | Stated total | Recomputed | Difference | High | Medium | Low | Status |
|---|---|---|---|---|---|---|---|---|---|
| INV-2026-1041 | Northwind Office Supplies | 3 | 332.38 GBP | 332.38 | 0.00 | 0 | 0 | 0 | OK |
| 2026/0877 | Brightline Print | 5 | 571.55 GBP | 567.95 | 3.60 | 4 | 2 | 0 | FIX |
| CN-8841 | CloudNest Hosting | 6 | 44.29 GBP | 44.30 | -0.01 | 0 | 1 | 0 | CHECK |
| INV-2026-1041 (#4) | Northwind Office Supplies | 3 | 332.38 GBP | 332.38 | 0.00 | 1 | 0 | 0 | FIX |
| CR-0012 (credit note) | Brightline Print | 1 | -22.80 GBP | -22.80 | 0.00 | 0 | 1 | 0 | CHECK |
## 2026/0877 - Brightline Print
| # | Item | Qty | Unit price | Disc % | Rate % | Net (stated / calc) | Tax (stated / calc) | Issues |
|---|---|---|---|---|---|---|---|---|
| 1 | Business cards, 350gsm | 250 | 0.38 | | 20 | 98.00 / 95.00 | 19.60 / 19.00 | net |
| 2 | A5 flyers, double-sided | 1000 | 0.12 | 10 | 20 | 108.00 / 108.00 | 21.60 / 21.60 | ok |
| 3 | A5 flyers, double-sided | 1000 | 0.12 | 10 | 20 | 108.00 / 108.00 | 21.60 / 21.60 | duplicate |
| 4 | Delivery | 1 | 12.50 | | 18 | 12.50 / 12.50 | 2.25 / 2.25 | rate |
| 5 | Catering for launch event | 1 | 150.00 | | 20 | 150.00 / 150.00 | 30.00 / 30.00 | category rate |
| Totals | Stated | Recomputed |
|---|---|---|
| Net | 476.50 | 473.50 |
| Tax | 95.05 | 94.45 |
| Total | 571.55 | 567.95 |
| Tax by rate | | 20%: 92.20 on 461.00; 18%: 2.25 on 12.50 |
- **HIGH IV02** line 1 (Business cards, 350gsm): line net 98.00 but 250 x 0.38 = 95.00 (diff 3.00)
- **HIGH IV04** line 4 (Delivery): tax rate 18% is not in your allowed rates (0, 10, 20)
- **HIGH IV05** stated net total 476.50 but the lines add up to 473.50 (diff 3.00)
- **HIGH IV05** stated tax total 95.05 but the lines add up to 94.45 (diff 0.60)
- **MEDIUM IV04** line 5 (Catering for launch event): category 'catering' is 10% in your rate list but the line uses 20%
- **MEDIUM IV07** line 3 (A5 flyers, double-sided) repeats line 2 (same item, quantity and price) - duplicate?
## CN-8841 - CloudNest Hosting
| # | Item | Qty | Unit price | Disc % | Rate % | Net (stated / calc) | Tax (stated / calc) | Issues |
|---|---|---|---|---|---|---|---|---|
| 1 | Object storage, GB-month | 412.5 | 0.0185 | | 20 | - / 7.63 | - / 1.53 | ok |
| 2 | Egress, GB | 96.3 | 0.0725 | | 20 | - / 6.98 | - / 1.40 | ok |
| 3 | Load balancer hours | 744 | 0.0225 | | 20 | - / 16.74 | - / 3.35 | ok |
| 4 | Snapshot storage, GB-month | 38.4 | 0.0420 | | 20 | - / 1.61 | - / 0.32 | ok |
| 5 | DNS queries, per million | 1.7 | 0.35 | | 20 | - / 0.60 | - / 0.12 | ok |
| 6 | Static IP hours | 744 | 0.0045 | | 20 | - / 3.35 | - / 0.67 | ok |
| Totals | Stated | Recomputed |
|---|---|---|
| Net | 36.91 | 36.91 |
| Tax | 7.38 | 7.39 |
| Total | 44.29 | 44.30 |
- **MEDIUM IV10** due date 2026-10-01 is before the invoice date 2026-10-31
- **INFO IV06** tax total 7.38 is per-invoice rounding (line rounding gives 7.39) - within tolerance
## INV-2026-1041 (#4) - Northwind Office Supplies
| # | Item | Qty | Unit price | Disc % | Rate % | Net (stated / calc) | Tax (stated / calc) | Issues |
|---|---|---|---|---|---|---|---|---|
| 1 | A4 copy paper, box of 5 reams | 4 | 21.50 | | 20 | - / 86.00 | - / 17.20 | ok |
| 2 | Toner cartridge 26X | 2 | 89.99 | 5 | 20 | - / 170.98 | - / 34.20 | ok |
| 3 | Book: Thinking with Type | 1 | 24.00 | | 0 | - / 24.00 | - / 0.00 | ok |
| Totals | Stated | Recomputed |
|---|---|---|
| Net | 280.98 | 280.98 |
| Tax | 51.40 | 51.40 |
| Total | 332.38 | 332.38 |
| Tax by rate | | 20%: 51.40 on 256.98; 0%: 0.00 on 24.00 |
- **HIGH IV07** invoice number INV-2026-1041 from this seller already appears (invoice #1) - duplicate invoice?
## CR-0012 - Brightline Print
| # | Item | Qty | Unit price | Disc % | Rate % | Net (stated / calc) | Tax (stated / calc) | Issues |
|---|---|---|---|---|---|---|---|---|
| 1 | Credit: misprinted business cards | -50 | 0.38 | | 20 | -19.00 / -19.00 | -3.80 / -3.80 | ok |
| Totals | Stated | Recomputed |
|---|---|---|
| Net | -19.00 | -19.00 |
| Tax | -3.80 | -3.80 |
| Total | -22.80 | -22.80 |
- **MEDIUM IV01** missing invoice field 'seller.vat_id'
FILE:scripts/invoice_check.py
#!/usr/bin/env python3
"""Invoice line item and VAT / sales-tax total checker (stdlib only, Python 3.8+).
Recomputes every line (qty x unit price - discount, tax per line) and every invoice total from
an invoices JSON or a flat line-items CSV, compares them with the stated amounts and reports
arithmetic errors, tax rates outside your list, rounding-method differences, discounts,
duplicate lines and invoices, missing required fields and date problems.
It checks numbers against the rules YOU give it; it does not decide which tax rate applies.
Usage:
invoice_check.py INVOICES.json|LINES.csv [--rules RULES.json] [--today YYYY-MM-DD] [--json]
invoice_check.py --self-test
Exit: 0 = no high findings, 1 = high findings (wrong totals, wrong rate, missing number/date,
duplicate invoice), 2 = bad input.
"""
import argparse, csv, datetime, io, json, re, sys
from decimal import Decimal, ROUND_HALF_UP, ROUND_HALF_EVEN, InvalidOperation
DEFAULT_RULES = {
"currency_decimals": 2,
"rounding": "half_up", # half_up | half_even
"vat_rounding": "line", # line = round tax per line then sum; invoice = per rate on the total
"tolerance": "0.01", # accepted difference per amount
"prices_include_vat": False, # unit prices are gross (tax-inclusive)
"allowed_vat_rates": None, # e.g. [20, 10, 1, 0]; None = do not check
"category_rates": {}, # your own mapping, e.g. {"books": 0}
"required_invoice_fields": ["number", "date", "seller.name", "seller.vat_id", "buyer.name", "currency"],
"required_line_fields": ["description", "qty", "unit_price", "vat_rate"],
"max_discount_pct": 100,
}
SEV = {"high": 0, "medium": 1, "low": 2, "info": 3}
CSV_MAP = {"invoice_number": "number", "invoice_date": "date", "due_date": "due_date", "type": "type",
"seller_name": "seller.name", "seller_vat_id": "seller.vat_id", "buyer_name": "buyer.name",
"buyer_vat_id": "buyer.vat_id", "currency": "currency", "invoice_discount_pct": "invoice_discount_pct",
"total_net": "totals.net", "total_vat": "totals.vat", "total_gross": "totals.gross"}
LINE_MAP = {"sku": "sku", "description": "description", "category": "category", "qty": "qty",
"unit_price": "unit_price", "discount_pct": "discount_pct", "line_net": "net", "vat_rate": "vat_rate",
"vat_amount": "vat", "line_gross": "gross"}
class InputError(Exception):
pass
def D(v, what="number"):
if v is None or (isinstance(v, str) and not v.strip()):
return None
if isinstance(v, float):
v = repr(v)
try:
s = str(v).strip().replace(" ", "")
if re.match(r"^-?\d{1,3}(\.\d{3})+,\d+$", s): # 1.234,56
s = s.replace(".", "").replace(",", ".")
elif re.match(r"^-?\d+,\d+$", s): # 12,50
s = s.replace(",", ".")
else:
s = s.replace(",", "") # 1,234.56
s = s.rstrip("%")
return Decimal(s)
except (InvalidOperation, ValueError):
raise InputError("not a %s: %r" % (what, v))
def get(d, path):
for k in path.split("."):
if not isinstance(d, dict):
return None
d = d.get(k)
return d
def setp(d, path, v):
ks = path.split(".")
for k in ks[:-1]:
d = d.setdefault(k, {})
d[ks[-1]] = v
def blank(v):
return v is None or (isinstance(v, str) and not v.strip())
# ---------------------------------------------------------------- loading
def load_invoices(path_or_text, is_csv=None):
text = path_or_text
if is_csv is None:
is_csv = not text.lstrip().startswith(("{", "["))
if not is_csv:
try:
data = json.loads(text)
except ValueError as e:
raise InputError("invalid JSON: %s" % e)
invs = data.get("invoices") if isinstance(data, dict) else data
if not isinstance(invs, list):
raise InputError("JSON must be a list of invoices or {\"invoices\": [...]}")
rules = data.get("rules") if isinstance(data, dict) else None
return invs, rules, []
rows = list(csv.DictReader(io.StringIO(text.lstrip("\ufeff"))))
if not rows:
raise InputError("CSV has no rows")
cols = set(rows[0].keys())
if "invoice_number" not in cols or not ({"qty", "unit_price"} <= cols):
raise InputError("CSV needs at least invoice_number, qty and unit_price columns")
invs, order, notes = {}, [], []
for n, row in enumerate(rows, 2):
key = ((row.get("seller_vat_id") or row.get("seller_name") or "").strip(), (row.get("invoice_number") or "").strip())
if key not in invs:
inv = {"_rows": [n]}
for c, p in CSV_MAP.items():
if c in row and not blank(row[c]):
setp(inv, p, row[c].strip())
invs[key] = inv; order.append(key)
else:
inv = invs[key]; inv["_rows"].append(n)
for c, p in CSV_MAP.items():
if c in row and not blank(row[c]) and not blank(get(inv, p)) and str(get(inv, p)) != row[c].strip():
notes.append((inv, "row %d: %s is '%s' but earlier rows say '%s'" % (n, c, row[c].strip(), get(inv, p))))
elif c in row and not blank(row[c]) and blank(get(inv, p)):
setp(inv, p, row[c].strip())
line = {"_row": n}
for c, p in LINE_MAP.items():
if c in row and not blank(row[c]):
line[p] = row[c].strip()
inv.setdefault("lines", []).append(line)
return [invs[k] for k in order], None, notes
def parse_date(s):
try:
return datetime.date.fromisoformat(str(s)[:10]) if not blank(s) else None
except ValueError:
return "bad"
# ---------------------------------------------------------------- checking
def check(invoices, rules, today=None, csv_notes=()):
R = dict(DEFAULT_RULES); R.update(rules or {})
q = Decimal(1).scaleb(-int(R["currency_decimals"]))
mode = ROUND_HALF_EVEN if R["rounding"] == "half_even" else ROUND_HALF_UP
tol = Decimal(str(R["tolerance"]))
rnd = lambda x: x.quantize(q, rounding=mode)
allowed = None if R["allowed_vat_rates"] is None else {Decimal(str(r)) for r in R["allowed_vat_rates"]}
catmap = {k.lower(): Decimal(str(v)) for k, v in (R.get("category_rates") or {}).items()}
results, seen_numbers, seen_amounts = [], {}, {}
notes_by = {}
for inv, msg in csv_notes:
notes_by.setdefault(id(inv), []).append(msg)
for idx, inv in enumerate(invoices):
F = []
def f(sev, code, msg):
F.append((sev, code, msg))
num = inv.get("number")
label = str(num) if not blank(num) else "#%d (no number)" % (idx + 1)
if any(str(o.get("number")) == str(num) for o in invoices[:idx]) and not blank(num):
label += " (#%d)" % (idx + 1)
credit = str(inv.get("type", "")).lower().replace(" ", "_") in ("credit_note", "credit", "refund")
for p in R["required_invoice_fields"]:
if blank(get(inv, p)):
f("high" if p in ("number", "date") else "medium", "IV01", "missing invoice field '%s'" % p)
for m in notes_by.get(id(inv), []):
f("medium", "IV13", "inconsistent header across CSV rows: " + m)
d = parse_date(inv.get("date")); dd = parse_date(inv.get("due_date"))
if d == "bad":
f("high", "IV10", "invoice date '%s' is not YYYY-MM-DD" % inv.get("date")); d = None
if dd == "bad":
f("medium", "IV10", "due date '%s' is not YYYY-MM-DD" % inv.get("due_date")); dd = None
if d and dd and dd < d:
f("medium", "IV10", "due date %s is before the invoice date %s" % (dd, d))
if d and today and d > today:
f("medium", "IV10", "invoice date %s is in the future (today %s)" % (d, today))
lines, per_rate, sigs = [], {}, {}
sum_net = sum_vat_line = Decimal(0)
for ln_i, ln in enumerate(inv.get("lines") or [], 1):
L = {"n": ln_i, "desc": str(ln.get("description") or ln.get("sku") or "")[:40], "issues": []}
where = "line %d (%s)" % (ln_i, L["desc"] or "no description")
for p in R["required_line_fields"]:
if blank(ln.get(p)):
f("high" if p in ("qty", "unit_price") else "medium", "IV01", "%s: missing '%s'" % (where, p)); L["issues"].append("missing " + p)
try:
qty = D(ln.get("qty"), "quantity"); up = D(ln.get("unit_price"), "unit price")
disc = D(ln.get("discount_pct"), "discount") or Decimal(0)
rate = D(ln.get("vat_rate"), "tax rate")
s_net, s_vat, s_gross = D(ln.get("net")), D(ln.get("vat")), D(ln.get("gross"))
except InputError as e:
f("high", "IV01", "%s: %s" % (where, e)); L["issues"].append("unparseable"); lines.append(L); continue
if rate is not None and Decimal(0) < rate < 1:
f("low", "IV04", "%s: tax rate %s looks like a fraction - read as %s%%" % (where, rate, rate * 100)); rate = rate * 100
if qty is None or up is None:
lines.append(L); continue
if disc < 0 or disc > Decimal(str(R["max_discount_pct"])):
f("high", "IV08", "%s: discount %s%% is outside 0-%s%%" % (where, disc, R["max_discount_pct"])); L["issues"].append("discount")
if (qty < 0 or up < 0) and not credit:
f("medium", "IV09", "%s: negative quantity or price on an invoice - should this be a credit note?" % where); L["issues"].append("negative")
if qty == 0 or up == 0:
f("low", "IV09", "%s: zero quantity or price" % where)
r = rate if rate is not None else Decimal(0)
base = qty * up * (1 - disc / 100)
if R["prices_include_vat"]:
gross = rnd(base); vat = rnd(gross * r / (100 + r)); net = gross - vat
else:
net = rnd(base); vat = rnd(net * r / 100); gross = net + vat
L.update(qty=qty, up=up, disc=disc, rate=rate, net=net, vat=vat, gross=gross, s_net=s_net, s_vat=s_vat, s_gross=s_gross)
if rate is not None:
if allowed is not None and rate not in allowed:
f("high", "IV04", "%s: tax rate %s%% is not in your allowed rates (%s)" % (where, plain(rate), ", ".join(plain(x) for x in sorted(allowed))))
L["issues"].append("rate")
cat = str(ln.get("category") or "").lower()
if cat in catmap and catmap[cat] != rate:
f("medium", "IV04", "%s: category '%s' is %s%% in your rate list but the line uses %s%%" % (where, cat, plain(catmap[cat]), plain(rate)))
L["issues"].append("category rate")
if s_net is not None and abs(s_net - net) > tol:
f("high", "IV02", "%s: line net %s but %s x %s%s = %s (diff %s)" % (where, s_net, plain(qty), up, " - %s%%" % plain(disc) if disc else "", net, s_net - net))
L["issues"].append("net")
if s_vat is not None and abs(s_vat - vat) > tol:
exp_on_stated = rnd((s_net if s_net is not None else net) * r / 100)
if s_net is not None and abs(s_vat - exp_on_stated) <= tol:
pass # consistent with its own (wrong) net; already reported as IV02
else:
f("high", "IV03", "%s: tax %s but %s%% of %s = %s (diff %s)" % (where, s_vat, plain(r), net, vat, s_vat - vat))
L["issues"].append("tax")
if s_gross is not None and s_net is not None and s_vat is not None and abs(s_gross - (s_net + s_vat)) > tol:
f("high", "IV02", "%s: line gross %s is not net %s + tax %s" % (where, s_gross, s_net, s_vat)); L["issues"].append("gross")
for v, nm in ((s_net, "net"), (s_vat, "tax"), (s_gross, "gross")):
if v is not None and v != v.quantize(q, rounding=mode):
f("low", "IV12", "%s: stated %s %s has more than %s decimals" % (where, nm, v, R["currency_decimals"]))
sig = (str(ln.get("sku") or "").lower(), L["desc"].lower(), qty, up)
if sig in sigs:
f("medium", "IV07", "%s repeats line %d (same item, quantity and price) - duplicate?" % (where, sigs[sig])); L["issues"].append("duplicate")
else:
sigs[sig] = ln_i
pr = per_rate.setdefault(r, [Decimal(0), Decimal(0)])
pr[0] += net; pr[1] += vat
sum_net += net; sum_vat_line += vat
lines.append(L)
if not lines:
f("high", "IV01", "invoice has no lines")
# invoice-level discount reduces the taxable base proportionally
idisc = D(inv.get("invoice_discount_pct"), "invoice discount") if not blank(inv.get("invoice_discount_pct")) else None
if idisc:
if idisc < 0 or idisc > 100:
f("high", "IV08", "invoice discount %s%% is outside 0-100%%" % idisc)
factor = 1 - idisc / 100
for r in per_rate:
n_r = rnd(per_rate[r][0] * factor)
per_rate[r] = [n_r, rnd(n_r * r / 100)]
sum_net = sum(v[0] for v in per_rate.values())
sum_vat_line = sum(v[1] for v in per_rate.values())
f("info", "IV08", "invoice discount %s%% applied before tax (taxable base reduced on every rate)" % idisc)
vat_invoice_mode = sum((rnd(v[0] * r / 100) for r, v in per_rate.items()), Decimal(0))
exp_vat = sum_vat_line if R["vat_rounding"] == "line" else vat_invoice_mode
alt_vat = vat_invoice_mode if R["vat_rounding"] == "line" else sum_vat_line
exp = {"net": sum_net, "vat": exp_vat, "gross": sum_net + exp_vat}
if R["prices_include_vat"] and not idisc:
exp["gross"] = sum(L.get("gross", 0) for L in lines)
exp["net"] = exp["gross"] - exp_vat
T = inv.get("totals") or {}
try:
st = {k: D(T.get(k), "total") for k in ("net", "vat", "gross")}
except InputError as e:
f("high", "IV05", str(e)); st = {"net": None, "vat": None, "gross": None}
if all(v is None for v in st.values()):
f("high", "IV01", "no stated totals (net / tax / gross) to check")
for k, nm in (("net", "net total"), ("vat", "tax total"), ("gross", "grand total")):
if st[k] is None:
continue
diff = st[k] - exp[k]
if abs(diff) <= tol:
if k == "vat" and diff and st[k] == alt_vat:
f("info", "IV06", "tax total %s is %s rounding (%s rounding gives %s) - within tolerance" % (
st[k], "per-invoice" if R["vat_rounding"] == "line" else "per-line", R["vat_rounding"], exp_vat))
continue
if k == "vat" and abs(st[k] - alt_vat) <= tol:
f("low", "IV06", "tax total %s matches %s rounding (%s) instead of your %s rounding (%s) - a rounding-method difference of %s" % (
st[k], "per-invoice" if R["vat_rounding"] == "line" else "per-line", alt_vat, R["vat_rounding"], exp_vat, diff))
exp["vat"] = alt_vat; exp["gross"] = exp["net"] + alt_vat
continue
if k == "gross" and st["net"] is not None and st["vat"] is not None and abs(st["gross"] - (st["net"] + st["vat"])) <= tol and (abs(st["net"] - exp["net"]) > tol or abs(st["vat"] - exp["vat"]) > tol):
continue # follows from the net/tax error already reported
sev = "high" if abs(diff) > tol * max(1, len(lines)) else "medium"
f(sev, "IV05", "stated %s %s but the lines add up to %s (diff %s)" % (nm, st[k], exp[k], diff))
if st["net"] is not None and st["vat"] is not None and st["gross"] is not None and abs(st["gross"] - st["net"] - st["vat"]) > tol:
f("high", "IV05", "stated grand total %s is not stated net %s + tax %s" % (st["gross"], st["net"], st["vat"]))
for b in inv.get("vat_breakdown") or []:
try:
br, bn, bv = D(b.get("rate")), D(b.get("net")), D(b.get("vat"))
except InputError as e:
f("medium", "IV11", "tax breakdown: %s" % e); continue
got = per_rate.get(br)
if got is None:
f("medium", "IV11", "tax breakdown lists %s%% but no line uses that rate" % br)
elif (bn is not None and abs(bn - got[0]) > tol) or (bv is not None and abs(bv - got[1]) > tol and abs(bv - rnd(got[0] * br / 100)) > tol):
f("medium", "IV11", "tax breakdown %s%%: stated base %s / tax %s, lines give %s / %s" % (br, bn, bv, got[0], got[1]))
seller = str(get(inv, "seller.vat_id") or get(inv, "seller.name") or "").strip().lower()
if not blank(num):
k = (seller, str(num).strip().lower())
if k in seen_numbers:
f("high", "IV07", "invoice number %s from this seller already appears (invoice #%d) - duplicate invoice?" % (num, seen_numbers[k] + 1))
else:
seen_numbers[k] = idx
if st["gross"] is not None and d:
k = (seller, d, st["gross"])
if k in seen_amounts and str(invoices[seen_amounts[k]].get("number")) != str(num):
f("medium", "IV07", "same seller, date and total %s as invoice %s - possible duplicate under a new number" % (st["gross"], invoices[seen_amounts[k]].get("number")))
seen_amounts.setdefault(k, idx)
F.sort(key=lambda x: (SEV[x[0]], x[1]))
results.append({"label": label, "seller": get(inv, "seller.name") or "", "currency": inv.get("currency") or "",
"credit": credit, "lines": lines, "per_rate": per_rate, "expected": exp, "stated": st,
"findings": F, "rows": inv.get("_rows")})
return results, R
# ---------------------------------------------------------------- rendering
def plain(v):
"""Plain decimal without exponent or trailing zeros (250, 0.0185, 12.5)."""
if v is None:
return "-"
t = format(v.normalize(), "f")
return t
def money(v):
return "-" if v is None else "{:,.2f}".format(v)
def report(results, R, src):
tot = {s: sum(1 for r in results for f in r["findings"] if f[0] == s) for s in SEV}
bad = [r for r in results if any(f[0] == "high" for f in r["findings"])]
out = ["# Invoice check: %s" % src, ""]
out.append("%d invoice(s), %d line(s). Rules: %s rounding, tax rounded per %s, tolerance %s, prices %s tax%s." % (
len(results), sum(len(r["lines"]) for r in results), R["rounding"].replace("_", " "), R["vat_rounding"], R["tolerance"],
"include" if R["prices_include_vat"] else "exclude",
", allowed rates " + "/".join(str(x) for x in R["allowed_vat_rates"]) + "%" if R["allowed_vat_rates"] is not None else ""))
out += ["", "**Verdict: %s**" % ("%d of %d invoice(s) have high findings - do not pay/send until fixed" % (len(bad), len(results)) if bad else "no high findings"),
"", "Findings: %d high, %d medium, %d low, %d info." % (tot["high"], tot["medium"], tot["low"], tot["info"]), "",
"This is an arithmetic and consistency check against your own rules, not tax advice: confirm rates and required invoice fields with your accountant or tax authority.", "",
"## Invoices", "", "| Invoice | Seller | Lines | Stated total | Recomputed | Difference | High | Medium | Low | Status |", "|---|---|---|---|---|---|---|---|---|---|"]
for r in results:
c = {s: sum(1 for f in r["findings"] if f[0] == s) for s in SEV}
sg, eg = r["stated"]["gross"], r["expected"]["gross"]
out.append("| %s%s | %s | %d | %s %s | %s | %s | %d | %d | %d | %s |" % (
r["label"], " (credit note)" if r["credit"] else "", r["seller"], len(r["lines"]), money(sg), r["currency"], money(eg),
money(sg - eg) if sg is not None else "-", c["high"], c["medium"], c["low"],
"FIX" if c["high"] else ("CHECK" if c["medium"] else "OK")))
for r in results:
if not r["findings"] or all(f[0] == "info" for f in r["findings"]):
continue
out += ["", "## %s - %s" % (r["label"], r["seller"]), ""]
out += ["| # | Item | Qty | Unit price | Disc % | Rate % | Net (stated / calc) | Tax (stated / calc) | Issues |", "|---|---|---|---|---|---|---|---|---|"]
for L in r["lines"]:
if "net" not in L:
out.append("| %d | %s | - | - | - | - | - | - | %s |" % (L["n"], L["desc"], ", ".join(L["issues"])))
continue
out.append("| %d | %s | %s | %s | %s | %s | %s / %s | %s / %s | %s |" % (
L["n"], L["desc"], plain(L["qty"]), L["up"], plain(L["disc"]) if L["disc"] else "", plain(L["rate"]),
money(L["s_net"]), money(L["net"]), money(L["s_vat"]), money(L["vat"]), ", ".join(L["issues"]) or "ok"))
out += ["", "| Totals | Stated | Recomputed |", "|---|---|---|"]
for k, nm in (("net", "Net"), ("vat", "Tax"), ("gross", "Total")):
out.append("| %s | %s | %s |" % (nm, money(r["stated"][k]), money(r["expected"][k])))
if len(r["per_rate"]) > 1:
out.append("| Tax by rate | | %s |" % "; ".join("%s%%: %s on %s" % (plain(k), money(v[1]), money(v[0])) for k, v in sorted(r["per_rate"].items(), reverse=True)))
out += [""] + ["- **%s %s** %s" % (s.upper(), c, m) for s, c, m in r["findings"]]
out.append("")
return "\n".join(out)
# ---------------------------------------------------------------- self-test
def self_test():
tests = []
def t(name, cond):
tests.append((name, bool(cond)))
def inv(lines, totals, **kw):
base = {"number": "A1", "date": "2026-10-01", "seller": {"name": "S", "vat_id": "X1"}, "buyer": {"name": "B"},
"currency": "EUR", "lines": lines, "totals": totals}
base.update(kw); return base
codes = lambda res: {f[1] for f in res[0]["findings"]}
sev = lambda res, c: {f[0] for f in res[0]["findings"] if f[1] == c}
ok = [{"description": "a", "qty": 2, "unit_price": "10.00", "vat_rate": 20, "net": "20.00", "vat": "4.00", "gross": "24.00"}]
res, _ = check([inv(ok, {"net": "20.00", "vat": "4.00", "gross": "24.00"})], {})
t("clean invoice has no findings", not res[0]["findings"])
res, _ = check([inv([dict(ok[0], net="22.00", vat="4.40", gross="26.40")], {"net": "22.00", "vat": "4.40", "gross": "26.40"})], {})
t("wrong line net -> IV02 high", "high" in sev(res, "IV02"))
t("tax consistent with wrong net not double-reported", "IV03" not in codes(res))
res, _ = check([inv([dict(ok[0], vat="3.60", gross="23.60")], {"net": "20.00", "vat": "3.60", "gross": "23.60"})], {})
t("wrong line tax -> IV03", "IV03" in codes(res))
res, _ = check([inv(ok, {"net": "20.00", "vat": "4.00", "gross": "24.00"})], {"allowed_vat_rates": [10, 0]})
t("rate not allowed -> IV04 high", "high" in sev(res, "IV04"))
res, _ = check([inv([dict(ok[0], category="books")], {"net": "20.00", "vat": "4.00", "gross": "24.00"})], {"category_rates": {"books": 0}})
t("category rate mismatch -> IV04 medium", "medium" in sev(res, "IV04"))
ten = [{"description": "x%d" % i, "qty": 1, "unit_price": "0.05", "vat_rate": 10} for i in range(10)]
res, _ = check([inv(ten, {"net": "0.50", "vat": "0.05", "gross": "0.55"})], {})
t("per-invoice rounding detected -> IV06 low", "IV06" in codes(res) and "IV05" not in codes(res))
res, _ = check([inv(ten, {"net": "0.50", "vat": "0.10", "gross": "0.60"})], {})
res2, _ = check([inv(ten[:2], {"net": "0.10", "vat": "0.01", "gross": "0.11"})], {})
t("rounding difference within tolerance -> IV06 info", sev(res2, "IV06") == {"info"})
t("per-line rounding: 10 x 0.01 = 0.10 ok", not res[0]["findings"])
res, _ = check([inv(ten, {"net": "0.50", "vat": "0.05", "gross": "0.55"})], {"vat_rounding": "invoice"})
t("vat_rounding=invoice accepts 0.05", not res[0]["findings"])
res, _ = check([inv(ok, {"net": "20.00", "vat": "4.00", "gross": "25.00"})], {})
t("grand total wrong -> IV05 high", "high" in sev(res, "IV05"))
d = [{"description": "a", "qty": 1, "unit_price": "100", "discount_pct": 15, "vat_rate": 20}]
res, _ = check([inv(d, {"net": "85.00", "vat": "17.00", "gross": "102.00"})], {})
t("line discount applied before tax", not res[0]["findings"])
res, _ = check([inv(d, {"net": "85.00", "vat": "17.00", "gross": "102.00"}, invoice_discount_pct="10")], {})
t("invoice discount reduces base", res[0]["expected"]["gross"] == Decimal("91.80"))
res, _ = check([inv(ok + ok, {"net": "40.00", "vat": "8.00", "gross": "48.00"})], {})
t("duplicate line -> IV07 medium", "medium" in sev(res, "IV07"))
a = inv(ok, {"net": "20.00", "vat": "4.00", "gross": "24.00"})
res, _ = check([a, dict(a)], {})
t("duplicate invoice number -> IV07 high", any(f[1] == "IV07" and f[0] == "high" for f in res[1]["findings"]))
res, _ = check([a, dict(a, number="A2")], {})
t("same seller/date/total new number -> IV07 medium", any(f[1] == "IV07" and f[0] == "medium" for f in res[1]["findings"]))
res, _ = check([inv(ok, {"net": "20.00", "vat": "4.00", "gross": "24.00"}, number="", seller={"name": "S"})], {})
t("missing number high, missing vat id medium", "high" in sev(res, "IV01") and "medium" in sev(res, "IV01"))
res, _ = check([inv(ok, {"net": "20.00", "vat": "4.00", "gross": "24.00"}, due_date="2026-09-01")], {}, today=datetime.date(2026, 9, 1))
t("due before date + future date -> IV10", len([f for f in res[0]["findings"] if f[1] == "IV10"]) == 2)
neg = [{"description": "return", "qty": -1, "unit_price": "10", "vat_rate": 20}]
res, _ = check([inv(neg, {"net": "-10.00", "vat": "-2.00", "gross": "-12.00"})], {})
t("negative line on invoice -> IV09", "IV09" in codes(res))
res, _ = check([inv(neg, {"net": "-10.00", "vat": "-2.00", "gross": "-12.00"}, type="credit_note")], {})
t("negative ok on credit note", "IV09" not in codes(res))
g = [{"description": "a", "qty": 1, "unit_price": "12.00", "vat_rate": 20}]
res, _ = check([inv(g, {"net": "10.00", "vat": "2.00", "gross": "12.00"})], {"prices_include_vat": True})
t("tax-inclusive prices", not res[0]["findings"])
t("decimal comma parsing", D("1.234,56") == Decimal("1234.56") and D("12,5") == Decimal("12.5") and D("1,234.56") == Decimal("1234.56"))
csvt = ("invoice_number,invoice_date,seller_name,seller_vat_id,buyer_name,currency,description,qty,unit_price,vat_rate,line_net,vat_amount,total_net,total_vat,total_gross\n"
"C1,2026-10-01,S,X1,B,EUR,a,1,10,20,10.00,2.00,15.00,3.00,18.00\nC1,2026-10-01,S,X1,B,USD,b,1,5,20,5.00,1.00,,,\n")
invs, _, notes = load_invoices(csvt)
res, _ = check(invs, {}, csv_notes=notes)
t("CSV grouped into one invoice", len(invs) == 1 and len(invs[0]["lines"]) == 2 and not any(f[0] == "high" for f in res[0]["findings"]))
t("CSV inconsistent header -> IV13", "IV13" in codes(res))
try:
load_invoices("{not json"); okk = False
except InputError:
okk = True
t("bad JSON rejected", okk)
res, _ = check([inv([dict(ok[0], qty="two")], {"gross": "24"})], {})
t("unparseable qty -> IV01 high", "high" in sev(res, "IV01"))
rep = report(*check([a], {}), "x")
t("report has verdict and not-tax-advice line", "Verdict" in rep and "not tax advice" in rep)
for name, okk in tests:
print(("PASS " if okk else "FAIL ") + name)
n = sum(1 for _, okk in tests if okk)
print("%d/%d passed" % (n, len(tests)))
return 0 if n == len(tests) else 1
def main(argv=None):
ap = argparse.ArgumentParser(description=__doc__.split("\n")[0])
ap.add_argument("invoices", nargs="?", help="invoices .json or line items .csv")
ap.add_argument("--rules", help="rules JSON (rates, rounding, tolerance, required fields)")
ap.add_argument("--today", help="YYYY-MM-DD for future-date checks (default: today)")
ap.add_argument("--json", action="store_true")
ap.add_argument("--self-test", action="store_true")
a = ap.parse_args(argv)
if a.self_test:
return self_test()
if not a.invoices:
ap.error("invoices file required (or --self-test)")
try:
try:
text = open(a.invoices, encoding="utf-8-sig").read()
except OSError as e:
raise InputError(str(e))
invs, emb_rules, notes = load_invoices(text, a.invoices.lower().endswith(".csv") or None)
rules = dict(emb_rules or {})
if a.rules:
try:
rules.update(json.load(open(a.rules, encoding="utf-8")))
except (OSError, ValueError) as e:
raise InputError("rules: %s" % e)
unknown = set(rules) - set(DEFAULT_RULES) - {"name", "note"}
if unknown:
raise InputError("unknown rules key(s): %s" % ", ".join(sorted(unknown)))
if rules.get("vat_rounding", "line") not in ("line", "invoice") or rules.get("rounding", "half_up") not in ("half_up", "half_even"):
raise InputError("vat_rounding must be line|invoice and rounding half_up|half_even")
today = datetime.date.fromisoformat(a.today) if a.today else datetime.date.today()
results, R = check(invs, rules, today, notes)
except (InputError, ValueError) as e:
print("error: %s" % e, file=sys.stderr)
return 2
if a.json:
conv = lambda v: str(v) if isinstance(v, Decimal) else v
out = [{"invoice": r["label"], "seller": r["seller"], "currency": r["currency"],
"stated": {k: conv(v) for k, v in r["stated"].items()}, "recomputed": {k: conv(v) for k, v in r["expected"].items()},
"tax_by_rate": {str(k): [str(v[0]), str(v[1])] for k, v in r["per_rate"].items()},
"findings": [{"severity": s, "code": c, "message": m} for s, c, m in r["findings"]]} for r in results]
print(json.dumps({"invoices": out}, indent=2))
else:
print(report(results, R, a.invoices))
return 1 if any(f[0] == "high" for r in results for f in r["findings"]) else 0
if __name__ == "__main__":
sys.exit(main())根据薪酬方案与交易 CSV 计算某周期销售佣金：按交易类型计配额、拆分单、含加速器的分层费率（边际或固定）等。
---
name: sales-commission-payout-calculator
description: 根据薪酬方案与交易 CSV 计算某周期销售佣金：按交易类型计配额、拆分单、含加速器的分层费率（边际或固定）等。
---
# 销售佣金发放计算器
你是「销售佣金发放计算器」技能助手。上方 description 为中文任务说明。下方保留原技能完整正文（含脚本与代码）；执行时遵循中文说明与原文技术细节。
# Sales Commission Payout Calculator
You help sales ops, finance and founders pay commissions that are right the first time. Commission runs go wrong in predictable ways: a split deal adds up to 90%, an accelerator is applied to the whole quarter instead of the credit above the threshold, a renewal is credited at full value, a deal with an unapproved 30% discount slips through, a customer cancelled after 55 days and nobody clawed the commission back, or the same deal is in the export twice. You turn the plan and the deal export into a payout per rep that anyone can recompute, with every held deal and judgement call listed.
You apply the plan the user gives you. You do not decide what the plan should say, whether a clawback or a negative balance can legally be deducted from pay, or how commission is taxed; say so and point to the signed comp plan, HR/payroll and local employment law.
## Files in this skill
- `scripts/commission_calc.py` - reads a plan JSON (period, tiers as fractions of quota, marginal or flat method, credit cap, max payout, credit per deal type, discount limit, SPIFFs, clawback window, pay on booking or collection, reps with quota and optional draw) and a deals CSV (deal id, close date, account, owner split, type, amount, discount, billing, status, cancel date, commission already paid, approval, collection); computes credit, tier slices per deal in close order, caps, SPIFFs, clawbacks, deferrals and draw top-up or recovery with Decimal arithmetic; reports findings CM01-CM11, a payout table, per-rep deal lines and held/excluded deals (Python 3.8+ standard library only)
- `references/plan-mechanics.md` - tiers, marginal vs flat, caps, credit rules, splits, SPIFFs, clawbacks, draws, timing, with worked numbers
- `references/checks-and-codes.md` - every finding code, its severity and what to do about it, and the input fields
- `templates/commission-statement.md` - the statement each rep receives and signs off
- `examples/comp-plan.json` - an AE plan for October 2026: 4 tiers (5/8/12/15%), credit cap 200%, max payout 8,000, renewals at 50% credit, 20% discount limit, annual-prepay SPIFF, 90-day clawback, three reps, one with a recoverable draw
- `examples/deals.csv` - 15 rows: normal deals, a 60/40 split, a renewal, an approved and an unapproved big discount, a split that adds up to 90%, a deal for an unknown rep with a thousands separator, two cancellations (inside and outside the clawback window), a September deal and a duplicate row
- `examples/payout-report.md` - the real calculator output on the example
## Workflow
### 1. Collect the plan and the deals
Ask for the comp plan document (or the relevant page) and turn it into a plan JSON like `examples/comp-plan.json`. Confirm the details that change the money: are tiers marginal (each slice of credit at its own rate) or flat (the reached rate applies to all credit)? Is attainment measured on bookings, ACV or margin? How are renewals and expansions credited? Is there a credit cap or a payout cap? When is commission earned: at booking or when the customer pays? How long is the clawback window and does it cover the full commission? Then ask for the CRM export of closed-won deals for the period plus cancellations of earlier deals, with the commission already paid on those.
### 2. Run the calculator
```
python3 scripts/commission_calc.py examples/comp-plan.json examples/deals.csv
python3 scripts/commission_calc.py plan.json deals.csv --rep ana
python3 scripts/commission_calc.py plan.json deals.csv --json
python3 scripts/commission_calc.py --self-test
```
Exit 0 = no high findings, 1 = high findings (deals held that need a decision), 2 = bad input. Without a shell, follow `references/plan-mechanics.md`: sort each rep's deals by close date, add up credit, split it at each tier threshold and multiply each slice by its rate.
### 3. Read the result
- High first: CM01 (unknown rep, bad amount or date, unknown deal type) and CM02 (split not 100%). These deals are held - get the answer from sales ops before payroll.
- Medium: CM05 (discount over the limit without approval - held), CM04 (duplicate row), CM08 (clawback applied - check the amount paid), CM11 (clawbacks bigger than earnings - negative balance).
- Low/info: deals from other periods (CM03), cancellations outside the window, caps (CM06), accelerators (CM07), deferrals (CM09), draws (CM10).
- Check one rep by hand against the per-deal rate slices before sending anything.
### 4. Resolve and re-run
For every held deal record the decision (correct split, approval found, rep assignment) in the CSV and re-run. Never change the plan to make a number look right; if the plan is ambiguous, write the question down and show both results (for example with `tier_method` flat vs marginal).
### 5. Deliver
Return the payout table for approval, a statement per rep from `templates/commission-statement.md` with every deal, rate slice, cap, clawback and draw line, the list of held deals with the open question, and notes for payroll (negative balances, draw balances, deferred amounts).
## Example result
On the example October payouts total 9,500.00 EUR, with 3 deals held. Ana Ruiz reaches 158.7% of her 60,000 quota (renewal Delta Foods credited at 50%), earns 8,280.00 on marginal tiers plus 400.00 in annual-prepay SPIFFs and is capped at the 8,000.00 maximum. Ben Okafor earns 690.00 but the Ironclad deal cancelled 55 days after closing claws back 960.00, so he is paid 0 and carries -270.00 forward. Chloe Martin earns 1,050.00, below her 1,500.00 recoverable draw, so the draw tops up 450.00 and her balance rises to 1,050.00. Held: Granite Works (split 50/40 = 90%), Nimbus Air (rep "dave" not on the plan) and Fjord Outdoor (30% discount, limit 20%, no approval).
## Rules
- Follow the signed plan; when the plan and the data disagree, hold the deal and ask - never guess a split, a rep or an approval.
- Show the arithmetic: every payout must be traceable to deal credit, tier slices and adjustments.
- Apply tiers in close-date order and say whether the plan is marginal or flat.
- Treat clawbacks and negative balances carefully: state what the plan allows and point to HR/payroll and local law before deducting from pay.
- Commission data is confidential: share each rep only their own statement.
- This is a calculation aid, not legal, tax or payroll advice.
FILE:references/plan-mechanics.md
# Plan mechanics with worked numbers
All examples: quota 10,000; tiers 5% up to 50% of quota, 8% up to 100%, 12% above.
## Quota credit
- Credit = deal amount x credit rate for the deal type (`deal_type_credit`, e.g. new 1.0, expansion 1.0, renewal 0.5) x the rep's split share.
- Use the measure the plan names (bookings, first-year ACV, TCV, margin) and export that column as `amount`.
- Attainment = total credit / quota.
## Marginal tiers (default)
Each slice of credit is paid at the rate of the band it falls in, in close-date order.
- 12,000 credit: 5,000 x 5% = 250, 5,000 x 8% = 400, 2,000 x 12% = 240 -> 890.
- Two deals of 6,000: the first earns 5,000 x 5% + 1,000 x 8% = 330, the second 4,000 x 8% + 2,000 x 12% = 560. The total is the same in any order; the per-deal split depends on close order, so keep close dates accurate.
## Flat (retroactive) tiers
The rate of the tier reached applies to all credit: 12,000 x 12% = 1,440. Flat tiers create big jumps at each threshold (9,999 credit earns 799.92, 10,000 earns 1,200 under a 8%/12% flat plan) and invite deal timing games; check the plan wording ("on all bookings" vs "on bookings above").
## Accelerators and decelerators
- An accelerator is simply a higher rate above 100% (12-15% here). A decelerator is a lower rate above a cap point, modelled as a final tier with a lower rate.
## Caps
- Credit cap (`credit_cap_attainment`, e.g. 2.0): credit above 200% of quota earns 0%.
- Payout cap (`max_payout`): commission + SPIFFs for the period cannot exceed this amount. Many plans avoid caps; if yours has one, say so in the statement.
## Splits
- `owner_split` like `ana:60;ben:40` must add up to 100%. Each rep gets their share of credit and SPIFFs. Overlay roles (SE, partner manager) that get credit on top of 100% need their own plan file.
## SPIFFs
- Fixed bonuses for deals matching all `when` fields (for example `type=new` and `billing=annual`). Split with the deal.
## Discount limits
- Deals with `discount_pct` above `max_discount_pct` need `approved=yes`; otherwise the credit is held (`unapproved_discount: hold`) or paid with a finding (`pay`).
## Clawbacks
- A deal closed in an earlier period and cancelled in this period within `clawback.window_days` of closing claws back `share` x the commission paid on it (column `commission_paid`; if missing it is estimated at the first tier rate and flagged).
- Cancelled after the window: no clawback, noted.
- Closed and cancelled in the same period: never credited.
- If clawbacks exceed earnings the payout is 0 and the negative balance is carried forward (CM11). Whether it may be deducted from future pay depends on the plan and local law.
## Booking vs collection
- `pay_on: booking`: commission is paid in the close period.
- `pay_on: collection`: credit counts toward attainment now, but commission (and SPIFF) on deals with `collected` not yes is deferred until the customer pays. Deferred amounts are listed; pay them in the period the cash arrives.
## Draws
- Recoverable draw: the rep is paid at least the draw; if earnings are lower, the top-up is added to the draw balance; later surpluses above the draw repay that balance first.
- Draw 1,500, balance 600, earned 1,050 -> paid 1,500, balance 1,050.
- Next month earned 2,900 -> 2,900 - 1,500 = 1,400 available, 1,050 recovered, paid 1,850, balance 0.
- Non-recoverable draw: a guaranteed minimum; no balance.
## Order of operations used by the calculator
1. Credit per deal (type credit x split) for won deals closed in the period, minus held and excluded deals.
2. Tier slices per deal in close order (marginal) or the reached rate on all credit (flat), with the credit cap.
3. + SPIFFs, then the payout cap.
4. - Deferred (pay on collection), - clawbacks, + carry-in.
5. Draw top-up or recovery; negative results become a carry-forward.
FILE:references/checks-and-codes.md
# Checks and finding codes
| Code | Severity | Check | What to do |
|---|---|---|---|
| CM01 | high: unknown rep, missing rep, bad amount/date, deal type with no credit rate; medium: zero or negative amount | data needed to pay the deal | fix the CRM record or add the rep/type to the plan; held deals are not paid |
| CM02 | high | owner_split shares add up to 100% | get the split confirmed by sales ops / the deal desk |
| CM03 | low | won deal closed outside the period | make sure it was paid in its own period; late-booked deals need a manual adjustment |
| CM04 | medium | the same deal_id appears twice | the second row is ignored; fix the export |
| CM05 | medium | discount above `max_discount_pct` without `approved=yes` | find the approval or keep the credit on hold |
| CM06 | info | credit cap or payout cap applied | mention it on the statement |
| CM07 | info | accelerator tier reached | check the threshold maths once by hand |
| CM08 | medium: clawback applied (estimated if commission_paid missing); low: cancelled after the window; info: closed and cancelled in this period | cancellations | confirm the amount paid originally |
| CM09 | low | commission deferred until collection | pay it in the period the cash arrives |
| CM10 | info | draw top-up or recovery, with the new balance | carry the balance into next period's plan file |
| CM11 | medium | clawbacks exceed earnings: payout 0, negative carry-forward | check the plan and local law before deducting |
## Verdict
- **High findings**: deals are held; resolve before payroll. The payable total shown excludes held deals.
- **Ready for approval**: nothing blocks the run; review medium findings with sales ops.
## Plan JSON keys
`plan_name, period{start, end, label}, currency, tier_method (marginal|flat), tiers[{up_to, rate}] (fractions; last tier without up_to), credit_cap_attainment, max_payout, deal_type_credit{type: rate}, max_discount_pct, unapproved_discount (hold|pay), spiffs[{name, when{column: value}, amount}], clawback{window_days, share}, pay_on (booking|collection), reps{id: {name, quota, draw{amount, recoverable, balance}, carry_forward}}`.
## Deals CSV columns
`deal_id, close_date, account, owner_split (ana or ana:60;ben:40), type, amount, discount_pct, billing, status (won|cancelled), cancel_date, commission_paid, approved (yes/no), collected (yes/no)`. Thousands separators (12,500) and decimal commas (1.234,50) are accepted. Extra columns can be used in SPIFF `when` conditions.
FILE:templates/commission-statement.md
# Commission statement: <rep name>
**Period:** <October 2026> **Plan:** <plan name, version/date signed> **Quota:** <60,000.00 EUR>
**Prepared by:** <name, sales ops> **Date:** <YYYY-MM-DD>
## Summary
| | Amount |
|---|---|
| Quota credit | <95,200.00> (<158.7%> of quota) |
| Commission (tiers) | <8,280.00> |
| SPIFFs | <400.00> |
| Cap | <-680.00> (max payout <8,000.00>) |
| Clawbacks | <-0.00> |
| Deferred to collection | <0.00> |
| Draw top-up / recovery | <0.00> |
| **Payout this period** | **<8,000.00>** |
| Balance carried forward | <0.00> |
## Deals
| Deal | Account | Closed | Type | Credit | Rate slices | Commission | SPIFF |
|---|---|---|---|---|---|---|---|
| <D-1001> | <Acme Logistics> | <2026-10-03> | <new> | <18,000.00> | <18,000.00 x 5.0%> | <900.00> | <200.00> |
## Adjustments
- <Clawback D-0950 cancelled 55 days after close: -960.00>
- <Held: D-1006 Fjord Outdoor, discount 30% awaiting approval - will be paid in the period it is approved>
## How it was calculated
<Marginal tiers 5% to 50% of quota, 8% to 100%, 12% to 150%, 15% above, applied in close-date order; renewals credited at 50%; credit cap 200%; max payout 8,000.>
Please review by <date>. Questions or disputes: <contact>. Disputes must name the deal and the line.
*Calculated from the plan and CRM data listed above. The signed compensation plan governs; this statement is not tax or legal advice.*
FILE:examples/comp-plan.json
{
"plan_name": "Northlake Software AE plan FY2026",
"period": {"start": "2026-10-01", "end": "2026-10-31", "label": "October 2026"},
"currency": "EUR",
"tier_method": "marginal",
"tiers": [
{"up_to": 0.5, "rate": 0.05},
{"up_to": 1.0, "rate": 0.08},
{"up_to": 1.5, "rate": 0.12},
{"rate": 0.15}
],
"credit_cap_attainment": 2.0,
"max_payout": 8000,
"deal_type_credit": {"new": 1.0, "expansion": 1.0, "renewal": 0.5},
"max_discount_pct": 20,
"unapproved_discount": "hold",
"spiffs": [
{"name": "Annual prepay on new logo", "when": {"type": "new", "billing": "annual"}, "amount": 200}
],
"clawback": {"window_days": 90, "share": 1.0},
"pay_on": "booking",
"reps": {
"ana": {"name": "Ana Ruiz", "quota": 60000},
"ben": {"name": "Ben Okafor", "quota": 50000},
"chloe": {"name": "Chloe Martin", "quota": 40000, "draw": {"amount": 1500, "recoverable": true, "balance": 600}}
}
}
FILE:examples/deals.csv
deal_id,close_date,account,owner_split,type,amount,discount_pct,billing,status,cancel_date,commission_paid,approved,collected
D-1001,2026-10-03,Acme Logistics,ana,new,18000,10,annual,won,,,,yes
D-1002,2026-10-08,Brightwave Media,ana:60;ben:40,expansion,12000,0,monthly,won,,,,yes
D-1003,2026-10-14,Corvid Health,ana,new,30000,15,annual,won,,,,no
D-1004,2026-10-21,Delta Foods,ana,renewal,24000,5,annual,won,,,,yes
D-1005,2026-10-28,Evergreen Bank,ana,new,28000,25,monthly,won,,,yes,no
D-1006,2026-10-10,Fjord Outdoor,ben,new,22000,30,annual,won,,,,yes
D-1007,2026-10-17,Granite Works,ben:50;chloe:40,new,15000,10,monthly,won,,,,yes
D-1008,2026-10-24,Harbor Clinics,ben,expansion,9000,0,monthly,won,,,,yes
D-0950,2026-08-12,Ironclad Security,ben,new,16000,10,annual,cancelled,2026-10-06,960,,
D-0871,2026-05-20,Juniper Retail,ben,new,14000,5,monthly,cancelled,2026-10-15,700,,
D-1009,2026-10-09,Kestrel Labs,chloe,new,11000,10,annual,won,,,,yes
D-1010,2026-10-30,Lumen Studio,chloe,new,6000,0,monthly,won,,,,no
D-0990,2026-09-29,Meridian Travel,chloe,new,8000,5,monthly,won,,,,yes
D-1009,2026-10-09,Kestrel Labs,chloe,new,11000,10,annual,won,,,,yes
D-1012,2026-10-19,Nimbus Air,dave,new,"12,500",10,annual,won,,,,yes
FILE:examples/payout-report.md
# Commission payouts: Northlake Software AE plan FY2026, October 2026
Period 2026-10-01 to 2026-10-31, marginal tiers (5.0% up to 50.0% of quota, 8.0% up to 100.0% of quota, 12.0% up to 150.0% of quota, 15.0% above), pay on booking, credit cap 200.0% of quota, max payout 8,000.00. Source: examples/deals.csv.
**Verdict: 2 high finding(s), 3 deal(s) held - resolve them before payroll; total payable now 9,500.00 EUR**
Findings: 2 high, 4 medium, 2 low, 3 info. Figures follow the plan file you provided; the signed comp plan and local pay law win if they differ.
## Payouts
| Rep | Quota | Credit | Attainment | Tier | Commission | SPIFFs | Cap | Clawbacks | Deferred | Draw | Payout |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Ana Ruiz | 60,000.00 | 95,200.00 | 158.7% | 4 | 8,280.00 | 400.00 | -680.00 | | | | **8,000.00** |
| Ben Okafor | 50,000.00 | 13,800.00 | 27.6% | 1 | 690.00 | 0.00 | | -960.00 | | | **0.00** |
| Chloe Martin | 40,000.00 | 17,000.00 | 42.5% | 1 | 850.00 | 200.00 | | | | +450.00 | **1,500.00** |
| **Total** | | | | | | | | | | | **9,500.00** |
## Ana Ruiz
| Deal | Account | Closed | Type | Amount | Share | Credit | Attainment after | Rate slices | Commission | SPIFF |
|---|---|---|---|---|---|---|---|---|---|---|
| D-1001 | Acme Logistics | 2026-10-03 | new | 18,000.00 | 100% | 18,000.00 | 30.0% | 18,000.00 x 5.0% | 900.00 | 200.00 |
| D-1002 | Brightwave Media | 2026-10-08 | expansion | 12,000.00 | 60% | 7,200.00 | 42.0% | 7,200.00 x 5.0% | 360.00 | |
| D-1003 | Corvid Health | 2026-10-14 | new | 30,000.00 | 100% | 30,000.00 | 92.0% | 4,800.00 x 5.0%; 25,200.00 x 8.0% | 2,256.00 | 200.00 |
| D-1004 | Delta Foods | 2026-10-21 | renewal | 24,000.00 | 100% | 12,000.00 | 112.0% | 4,800.00 x 8.0%; 7,200.00 x 12.0% | 1,248.00 | |
| D-1005 | Evergreen Bank | 2026-10-28 | new | 28,000.00 | 100% | 28,000.00 | 158.7% | 22,800.00 x 12.0%; 5,200.00 x 15.0% | 3,516.00 | |
## Ben Okafor
| Deal | Account | Closed | Type | Amount | Share | Credit | Attainment after | Rate slices | Commission | SPIFF |
|---|---|---|---|---|---|---|---|---|---|---|
| D-1002 | Brightwave Media | 2026-10-08 | expansion | 12,000.00 | 40% | 4,800.00 | 9.6% | 4,800.00 x 5.0% | 240.00 | |
| D-1008 | Harbor Clinics | 2026-10-24 | expansion | 9,000.00 | 100% | 9,000.00 | 27.6% | 9,000.00 x 5.0% | 450.00 | |
- Clawback D-0950 (Ironclad Security, cancelled 55 days after close): -960.00
- Negative balance carried forward: -270.00
## Chloe Martin
| Deal | Account | Closed | Type | Amount | Share | Credit | Attainment after | Rate slices | Commission | SPIFF |
|---|---|---|---|---|---|---|---|---|---|---|
| D-1009 | Kestrel Labs | 2026-10-09 | new | 11,000.00 | 100% | 11,000.00 | 27.5% | 11,000.00 x 5.0% | 550.00 | 200.00 |
| D-1010 | Lumen Studio | 2026-10-30 | new | 6,000.00 | 100% | 6,000.00 | 42.5% | 6,000.00 x 5.0% | 300.00 | |
- Draw: top-up 450.00, recovered 0.00, outstanding balance 1,050.00
## Held deals (need a decision)
| Deal | Account | Reason |
|---|---|---|
| D-1006 | Fjord Outdoor | discount 30% not approved |
| D-1007 | Granite Works | split 90% |
| D-1012 | Nimbus Air | unknown rep dave |
## Not counted this period
| Deal | Account | Reason |
|---|---|---|
| D-0871 | Juniper Retail | cancelled after clawback window |
| D-0990 | Meridian Travel | closed 2026-09-29 |
| D-1009 | Kestrel Labs | duplicate row |
## Findings
- **HIGH CM01** D-1012: unknown rep(s) dave in owner_split - deal held
- **HIGH CM02** D-1007: split shares add up to 90%, not 100% - deal held until sales ops confirms the split
- **MEDIUM CM04** D-1009 appears again on row 15 (first on row 12) - second row ignored
- **MEDIUM CM05** D-1006: discount 30% over the 20% limit without approval - credit held
- **MEDIUM CM08** D-0950 cancelled 55 days after close (window 90): clawback 960.00
- **MEDIUM CM11** Ben Okafor: clawbacks exceed earnings by 270.00 - payout 0, negative balance carried to next period (check your plan and local pay law before deducting)
- **LOW CM03** D-0990 closed 2026-09-29, outside October 2026 - not counted here (check it was paid in its own period)
- **LOW CM08** D-0871 cancelled 148 days after close (window 90) - no clawback
- **INFO CM06** Ana Ruiz: commission + SPIFFs 8,680.00 capped at max_payout 8,000.00 (-680.00)
- **INFO CM07** Ana Ruiz at 158.7% of quota - tier 4 (15.0%) reached for the credit above each threshold
- **INFO CM10** Chloe Martin: earned 1,050.00 is below the 1,500.00 draw - draw tops up 450.00 (recoverable: balance now 1,050.00)
Recompute check: commission per deal = sum of (credit in each tier band x tier rate); payout = commission + SPIFFs - cap - deferred - clawbacks + carry-in, then draw rules. Have sales ops and payroll approve before paying.
FILE:scripts/commission_calc.py
#!/usr/bin/env python3
"""Sales commission payout calculator (stdlib only, Python 3.8+).
Reads a compensation plan (JSON) and a deals export (CSV) and works out each rep's payout for
one period: quota credit by deal type, deal splits, tiered rates with accelerators (marginal or
flat/retroactive), credit and payout caps, SPIFFs, clawbacks for deals cancelled inside the
clawback window, payment on booking or on collection, and recoverable or non-recoverable draws.
Deals with problems (bad split, unknown rep, discount over the limit without approval,
duplicates, wrong period) are held or excluded and listed with a finding code.
Usage:
commission_calc.py PLAN.json DEALS.csv [--rep ID]... [--json]
commission_calc.py --self-test
Exit: 0 = no high findings, 1 = high findings (deals held that need a decision), 2 = bad input.
"""
import argparse, csv, datetime, io, json, sys
from collections import OrderedDict
from decimal import Decimal, ROUND_HALF_UP
SEV = {"high": 0, "medium": 1, "low": 2, "info": 3}
CENT = Decimal("0.01")
PLAN_KEYS = {"plan_name", "period", "currency", "tier_method", "tiers", "credit_cap_attainment", "max_payout",
"deal_type_credit", "max_discount_pct", "unapproved_discount", "spiffs", "clawback", "pay_on", "reps"}
class InputError(Exception):
pass
def D(x, what="number"):
if isinstance(x, Decimal):
return x
s = str(x).strip().replace(" ", "")
if s == "":
raise InputError("empty %s" % what)
if "," in s and "." in s:
s = s.replace(",", "") if s.rfind(".") > s.rfind(",") else s.replace(".", "").replace(",", ".")
elif "," in s:
s = s.replace(",", "") if len(s.split(",")[-1]) == 3 else s.replace(",", ".")
try:
return Decimal(s)
except Exception:
raise InputError("%s is not a number: %r" % (what, x))
def pdate(s, what):
try:
return datetime.date.fromisoformat(str(s).strip())
except ValueError:
raise InputError("%s must be YYYY-MM-DD, got %r" % (what, s))
def money(x):
return Decimal(x).quantize(CENT, rounding=ROUND_HALF_UP)
def fm(x):
x = money(x)
return "{:,.2f}".format(x)
def num(x):
return "{:f}".format(Decimal(x).normalize())
def pct(x):
return "%.1f%%" % (float(x) * 100)
# ---------------------------------------------------------------- plan
def load_plan(cfg):
if not isinstance(cfg, dict):
raise InputError("plan must be a JSON object")
bad = set(cfg) - PLAN_KEYS
if bad:
raise InputError("unknown plan key(s): %s" % ", ".join(sorted(bad)))
per = cfg.get("period") or {}
p = {"name": cfg.get("plan_name", "Commission plan"), "currency": cfg.get("currency", ""),
"start": pdate(per.get("start"), "period.start"), "end": pdate(per.get("end"), "period.end"),
"label": per.get("label") or "%s..%s" % (per.get("start"), per.get("end")),
"method": cfg.get("tier_method", "marginal"), "pay_on": cfg.get("pay_on", "booking"),
"cap_att": D(cfg["credit_cap_attainment"]) if cfg.get("credit_cap_attainment") is not None else None,
"max_payout": D(cfg["max_payout"]) if cfg.get("max_payout") is not None else None,
"type_credit": {k.lower(): D(v) for k, v in (cfg.get("deal_type_credit") or {"new": 1}).items()},
"max_disc": D(cfg["max_discount_pct"]) if cfg.get("max_discount_pct") is not None else None,
"unapproved": cfg.get("unapproved_discount", "hold"),
"spiffs": cfg.get("spiffs", []),
"claw_days": int((cfg.get("clawback") or {}).get("window_days", 0)),
"claw_share": D((cfg.get("clawback") or {}).get("share", 1)),
"reps": OrderedDict()}
if p["end"] < p["start"]:
raise InputError("period ends before it starts")
if p["method"] not in ("marginal", "flat"):
raise InputError("tier_method must be 'marginal' or 'flat'")
if p["pay_on"] not in ("booking", "collection"):
raise InputError("pay_on must be 'booking' or 'collection'")
if p["unapproved"] not in ("hold", "pay"):
raise InputError("unapproved_discount must be 'hold' or 'pay'")
tiers, prev = [], Decimal(0)
for i, t in enumerate(cfg.get("tiers") or []):
up = D(t["up_to"], "tier up_to") if t.get("up_to") is not None else None
if up is not None and up <= prev:
raise InputError("tier %d: up_to must increase (attainment as a fraction, e.g. 1.0 = 100%%)" % (i + 1))
if up is None and i != len(cfg["tiers"]) - 1:
raise InputError("only the last tier may omit up_to")
tiers.append((prev, up, D(t["rate"], "tier rate")))
prev = up if up is not None else prev
if not tiers:
raise InputError("plan needs at least one tier")
if any(r > 1 for _, _, r in tiers):
raise InputError("tier rates are fractions (0.08 = 8%)")
p["tiers"] = tiers
for rid, r in (cfg.get("reps") or {}).items():
dr = r.get("draw")
p["reps"][rid.lower()] = {"id": rid.lower(), "name": r.get("name", rid), "quota": D(r["quota"], "quota of %s" % rid),
"draw": D(dr["amount"]) if dr else Decimal(0), "recoverable": bool(dr and dr.get("recoverable", True)),
"balance": D(dr.get("balance", 0)) if dr else Decimal(0),
"carry": D(r.get("carry_forward", 0))}
if p["reps"][rid.lower()]["quota"] <= 0:
raise InputError("quota of %s must be positive" % rid)
if not p["reps"]:
raise InputError("plan needs at least one rep")
return p
def rate_at(p, att):
for lo, up, r in p["tiers"]:
if up is None or att < up:
return r
return p["tiers"][-1][2]
def tier_name(p, att):
for i, (lo, up, r) in enumerate(p["tiers"], 1):
if up is None or att < up:
return i
return len(p["tiers"])
# ---------------------------------------------------------------- deals
def parse_split(s, default):
s = (s or "").strip()
if not s:
return OrderedDict([(default, Decimal(100))]) if default else None
out = OrderedDict()
for part in s.replace("|", ";").split(";"):
part = part.strip()
if not part:
continue
if ":" in part:
who, share = part.split(":", 1)
out[who.strip().lower()] = out.get(who.strip().lower(), Decimal(0)) + D(share.strip().rstrip("%"), "split share")
else:
out[part.lower()] = Decimal(100)
return out
def yes(v):
return str(v or "").strip().lower() in ("yes", "y", "true", "1", "x")
def compute(p, deals_text, only=None):
F = []
rows = list(csv.DictReader(io.StringIO(deals_text)))
if not rows:
raise InputError("deals CSV has no rows")
need = {"deal_id", "close_date", "amount"}
if not need <= set(rows[0]):
raise InputError("deals CSV needs columns %s (has %s)" % (", ".join(sorted(need)), ", ".join(rows[0])))
credited = {rid: [] for rid in p["reps"]}
held, excluded, claws, seen = [], [], [], {}
for n, r in enumerate(rows, 2):
did = (r.get("deal_id") or "").strip() or "row %d" % n
acct = r.get("account", "")
try:
close = pdate(r.get("close_date"), "close_date")
amount = D(r.get("amount"), "amount")
disc = D(r.get("discount_pct") or 0, "discount_pct")
except InputError as e:
F.append(("high", "CM01", "%s (row %d): %s - deal not counted" % (did, n, e), did)); held.append((did, acct, "bad data")); continue
key = did.lower()
if key in seen:
F.append(("medium", "CM04", "%s appears again on row %d (first on row %d) - second row ignored" % (did, n, seen[key]), did)); excluded.append((did, acct, "duplicate row")); continue
seen[key] = n
status = (r.get("status") or "won").strip().lower()
split = parse_split(r.get("owner_split") or r.get("rep"), None)
if not split:
F.append(("high", "CM01", "%s: no rep / owner_split - deal not counted" % did, did)); held.append((did, acct, "no rep")); continue
unknown = [w for w in split if w not in p["reps"]]
if status == "cancelled":
cdate = r.get("cancel_date")
try:
cdate = pdate(cdate, "cancel_date") if cdate else None
except InputError as e:
F.append(("high", "CM01", "%s: %s" % (did, e), did)); continue
if not cdate or not (p["start"] <= cdate <= p["end"]):
excluded.append((did, acct, "cancelled outside this period")); continue
if p["start"] <= close <= p["end"]:
F.append(("info", "CM08", "%s closed and was cancelled in this period - not credited" % did, did)); excluded.append((did, acct, "cancelled in period")); continue
age = (cdate - close).days
if age > p["claw_days"]:
F.append(("low", "CM08", "%s cancelled %d days after close (window %d) - no clawback" % (did, age, p["claw_days"]), did)); excluded.append((did, acct, "cancelled after clawback window")); continue
paid = r.get("commission_paid")
if paid and str(paid).strip():
base = D(paid, "commission_paid"); est = False
else:
tc = p["type_credit"].get((r.get("type") or "new").strip().lower(), Decimal(1))
base = amount * tc * p["tiers"][0][2]; est = True
for who, share in split.items():
if who in p["reps"]:
amt = money(base * p["claw_share"] * share / 100)
claws.append({"rep": who, "deal": did, "account": acct, "amount": amt, "age": age, "estimated": est})
F.append(("medium", "CM08", "%s cancelled %d days after close (window %d): clawback %s%s" % (did, age, p["claw_days"], fm(base * p["claw_share"]), " (estimated at the first tier rate - commission_paid missing)" if est else ""), did))
continue
if status not in ("won", "closed_won", "closed won"):
excluded.append((did, acct, "status %s" % status)); continue
if not (p["start"] <= close <= p["end"]):
F.append(("low", "CM03", "%s closed %s, outside %s - not counted here (check it was paid in its own period)" % (did, close, p["label"]), did)); excluded.append((did, acct, "closed %s" % close)); continue
if unknown:
F.append(("high", "CM01", "%s: unknown rep(s) %s in owner_split - deal held" % (did, ", ".join(unknown)), did)); held.append((did, acct, "unknown rep %s" % ", ".join(unknown))); continue
total = sum(split.values())
if total != 100:
F.append(("high", "CM02", "%s: split shares add up to %s%%, not 100%% - deal held until sales ops confirms the split" % (did, num(total)), did)); held.append((did, acct, "split %s%%" % num(total))); continue
dtype = (r.get("type") or "new").strip().lower()
if dtype not in p["type_credit"]:
F.append(("high", "CM01", "%s: deal type '%s' has no credit rate in the plan - deal held" % (did, dtype), did)); held.append((did, acct, "unknown type %s" % dtype)); continue
if amount <= 0:
F.append(("medium", "CM01", "%s: amount %s is not positive - not counted (use a cancellation for reversals)" % (did, amount), did)); excluded.append((did, acct, "amount <= 0")); continue
if p["max_disc"] is not None and disc > p["max_disc"] and not yes(r.get("approved")):
if p["unapproved"] == "hold":
F.append(("medium", "CM05", "%s: discount %s%% over the %s%% limit without approval - credit held" % (did, num(disc), num(p["max_disc"])), did)); held.append((did, acct, "discount %s%% not approved" % num(disc))); continue
F.append(("medium", "CM05", "%s: discount %s%% over the %s%% limit without approval - paid anyway (unapproved_discount=pay)" % (did, num(disc), num(p["max_disc"])), did))
credit_total = amount * p["type_credit"][dtype]
deferred = p["pay_on"] == "collection" and not yes(r.get("collected"))
for who, share in split.items():
spiff = Decimal(0)
for s in p["spiffs"]:
if all(str(r.get(k, "")).strip().lower() == str(v).lower() for k, v in (s.get("when") or {}).items()):
spiff += D(s["amount"]) * share / 100
credited[who].append({"deal": did, "account": acct, "close": close, "type": dtype, "amount": amount, "share": share,
"credit": credit_total * share / 100, "spiff": spiff, "deferred": deferred})
# per rep
reps = []
for rid, rep in p["reps"].items():
if only and rid not in only:
continue
ds = sorted(credited[rid], key=lambda d: (d["close"], d["deal"]))
q = rep["quota"]
cap_credit = q * p["cap_att"] if p["cap_att"] is not None else None
cum = Decimal(0)
total_credit = sum((d["credit"] for d in ds), Decimal(0))
att = total_credit / q
flat_rate = rate_at(p, min(att, p["cap_att"]) if p["cap_att"] is not None else att)
for d in ds:
a, b = cum, cum + d["credit"]
cum = b
comm, slices = Decimal(0), []
if p["method"] == "flat":
eligible = b if cap_credit is None else min(b, cap_credit)
part = max(Decimal(0), eligible - a)
comm = part * flat_rate
slices.append("%s x %s" % (fm(part), pct(flat_rate)))
if part < d["credit"]:
slices.append("%s over the credit cap x 0%%" % fm(d["credit"] - part))
else:
for lo, up, rate in p["tiers"]:
lo_c, up_c = lo * q, (up * q if up is not None else None)
if cap_credit is not None:
up_c = cap_credit if up_c is None else min(up_c, cap_credit)
if lo_c >= cap_credit:
continue
x0, x1 = max(a, lo_c), (b if up_c is None else min(b, up_c))
if x1 > x0:
comm += (x1 - x0) * rate
slices.append("%s x %s" % (fm(x1 - x0), pct(rate)))
if cap_credit is not None and b > cap_credit:
slices.append("%s over the credit cap x 0%%" % fm(b - max(a, cap_credit)))
d["commission"] = money(comm)
d["slices"] = slices
d["att_after"] = b / q
commission = sum((d["commission"] for d in ds), Decimal(0))
spiffs = money(sum((d["spiff"] for d in ds), Decimal(0)))
earned = commission + spiffs
capped = Decimal(0)
if p["max_payout"] is not None and earned > p["max_payout"]:
capped = earned - p["max_payout"]
F.append(("info", "CM06", "%s: commission + SPIFFs %s capped at max_payout %s (-%s)" % (rep["name"], fm(earned), fm(p["max_payout"]), fm(capped)), rid))
earned = p["max_payout"]
if cap_credit is not None and total_credit > cap_credit:
F.append(("info", "CM06", "%s: credit above %s of quota (%s) earns no commission" % (rep["name"], pct(p["cap_att"]), fm(total_credit - cap_credit)), rid))
if att >= 1:
F.append(("info", "CM07", "%s at %s of quota - tier %d (%s) reached%s" % (rep["name"], pct(att), tier_name(p, att), pct(rate_at(p, att)), " and applied to all credit (flat method)" if p["method"] == "flat" else " for the credit above each threshold"), rid))
deferred = money(sum((d["commission"] + d["spiff"] for d in ds if d["deferred"]), Decimal(0)))
if deferred and capped:
deferred = money(deferred * earned / (earned + capped))
if deferred:
F.append(("low", "CM09", "%s: %s deferred until the customer pays (pay_on collection)" % (rep["name"], fm(deferred)), rid))
claw = sum((c["amount"] for c in claws if c["rep"] == rid), Decimal(0))
payable = earned - deferred - claw + rep["carry"]
draw_paid, recovered, bal = Decimal(0), Decimal(0), rep["balance"]
if rep["draw"]:
if payable < rep["draw"]:
draw_paid = rep["draw"] - max(payable, Decimal(0))
if rep["recoverable"]:
bal += draw_paid
F.append(("info", "CM10", "%s: earned %s is below the %s draw - draw tops up %s%s" % (rep["name"], fm(max(payable, Decimal(0))), fm(rep["draw"]), fm(draw_paid), (" (recoverable: balance now %s)" % fm(bal)) if rep["recoverable"] else " (non-recoverable)"), rid))
payout = rep["draw"]
if payable < 0:
bal += -payable if rep["recoverable"] else 0
else:
recovered = min(bal, payable - rep["draw"]) if rep["recoverable"] else Decimal(0)
bal -= recovered
payout = payable - recovered
if recovered:
F.append(("info", "CM10", "%s: %s of the outstanding draw recovered (balance now %s)" % (rep["name"], fm(recovered), fm(bal)), rid))
else:
payout = payable
carry_out = Decimal(0)
if payout < 0:
carry_out = payout
F.append(("medium", "CM11", "%s: clawbacks exceed earnings by %s - payout 0, negative balance carried to next period (check your plan and local pay law before deducting)" % (rep["name"], fm(-payout)), rid))
payout = Decimal(0)
reps.append({"id": rid, "name": rep["name"], "quota": q, "credit": total_credit, "attainment": att,
"tier": tier_name(p, att), "commission": commission, "spiffs": spiffs, "capped": capped,
"clawbacks": claw, "deferred": deferred, "carry_in": rep["carry"], "draw_topup": draw_paid,
"draw_recovered": recovered, "draw_balance": bal, "carry_out": carry_out, "payout": money(payout), "deals": ds})
F.sort(key=lambda f: (SEV[f[0]], f[1], str(f[3])))
return {"plan": p, "reps": reps, "held": held, "excluded": excluded, "clawbacks": claws, "findings": F}
# ---------------------------------------------------------------- report
def report(res, title):
p, F = res["plan"], res["findings"]
c = {s: sum(1 for f in F if f[0] == s) for s in SEV}
cur = (" " + p["currency"]) if p["currency"] else ""
total = sum((r["payout"] for r in res["reps"]), Decimal(0))
L = ["# Commission payouts: %s, %s" % (p["name"], p["label"]), ""]
L.append("Period %s to %s, %s tiers (%s), pay on %s%s%s. Source: %s." % (
p["start"], p["end"], p["method"], ", ".join("%s%s" % (pct(r), (" up to %s of quota" % pct(u)) if u is not None else " above") for _, u, r in p["tiers"]),
p["pay_on"], (", credit cap %s of quota" % pct(p["cap_att"])) if p["cap_att"] is not None else "",
(", max payout %s" % fm(p["max_payout"])) if p["max_payout"] is not None else "", title))
L.append("")
if c["high"]:
L.append("**Verdict: %d high finding(s), %d deal(s) held - resolve them before payroll; total payable now %s%s**" % (c["high"], len(res["held"]), fm(total), cur))
else:
L.append("**Verdict: ready for approval - total payable %s%s%s**" % (fm(total), cur, " (medium findings to review)" if c["medium"] else ""))
L.append("")
L.append("Findings: %d high, %d medium, %d low, %d info. Figures follow the plan file you provided; the signed comp plan and local pay law win if they differ." % (c["high"], c["medium"], c["low"], c["info"]))
L += ["", "## Payouts", "", "| Rep | Quota | Credit | Attainment | Tier | Commission | SPIFFs | Cap | Clawbacks | Deferred | Draw | Payout |", "|---|---|---|---|---|---|---|---|---|---|---|---|"]
for r in res["reps"]:
draw = ("+%s" % fm(r["draw_topup"])) if r["draw_topup"] else (("-%s recovered" % fm(r["draw_recovered"])) if r["draw_recovered"] else "")
L.append("| %s | %s | %s | %s | %d | %s | %s | %s | %s | %s | %s | **%s** |" % (
r["name"], fm(r["quota"]), fm(r["credit"]), pct(r["attainment"]), r["tier"], fm(r["commission"]), fm(r["spiffs"]),
("-" + fm(r["capped"])) if r["capped"] else "", ("-" + fm(r["clawbacks"])) if r["clawbacks"] else "",
fm(r["deferred"]) if r["deferred"] else "", draw, fm(r["payout"])))
L.append("| **Total** | | | | | | | | | | | **%s** |" % fm(total))
for r in res["reps"]:
L += ["", "## %s" % r["name"], ""]
if not r["deals"]:
L.append("No credited deals this period.")
else:
L += ["| Deal | Account | Closed | Type | Amount | Share | Credit | Attainment after | Rate slices | Commission | SPIFF |", "|---|---|---|---|---|---|---|---|---|---|---|"]
for d in r["deals"]:
L.append("| %s | %s | %s | %s | %s | %s%% | %s | %s | %s | %s | %s |" % (
d["deal"], d["account"], d["close"], d["type"], fm(d["amount"]), num(d["share"]), fm(d["credit"]),
pct(d["att_after"]), "; ".join(d["slices"]) or "-", fm(d["commission"]) + (" (deferred)" if d["deferred"] else ""), fm(d["spiff"]) if d["spiff"] else ""))
extra = []
for cl in [x for x in res["clawbacks"] if x["rep"] == r["id"]]:
extra.append("- Clawback %s (%s, cancelled %d days after close): -%s%s" % (cl["deal"], cl["account"], cl["age"], fm(cl["amount"]), " (estimated)" if cl["estimated"] else ""))
if r["carry_in"]:
extra.append("- Carried in from last period: %s" % fm(r["carry_in"]))
if r["draw_topup"] or r["draw_recovered"] or r["draw_balance"]:
extra.append("- Draw: top-up %s, recovered %s, outstanding balance %s" % (fm(r["draw_topup"]), fm(r["draw_recovered"]), fm(r["draw_balance"])))
if r["carry_out"]:
extra.append("- Negative balance carried forward: %s" % fm(r["carry_out"]))
L += [""] + extra if extra else []
if res["held"]:
L += ["", "## Held deals (need a decision)", "", "| Deal | Account | Reason |", "|---|---|---|"]
L += ["| %s | %s | %s |" % h for h in res["held"]]
if res["excluded"]:
L += ["", "## Not counted this period", "", "| Deal | Account | Reason |", "|---|---|---|"]
L += ["| %s | %s | %s |" % h for h in res["excluded"]]
L += ["", "## Findings", ""]
L += ["- **%s %s** %s" % (s.upper(), code, m) for s, code, m, _ in F] or ["- none"]
L += ["", "Recompute check: commission per deal = sum of (credit in each tier band x tier rate); payout = commission + SPIFFs - cap - deferred - clawbacks + carry-in, then draw rules. Have sales ops and payroll approve before paying."]
return "\n".join(L) + "\n"
def to_json(res):
def j(x):
if isinstance(x, Decimal):
return float(money(x))
if isinstance(x, datetime.date):
return x.isoformat()
if isinstance(x, dict):
return {k: j(v) for k, v in x.items()}
if isinstance(x, (list, tuple)):
return [j(v) for v in x]
return x
out = {"period": res["plan"]["label"], "reps": [], "held": [list(h) for h in res["held"]], "excluded": [list(h) for h in res["excluded"]],
"clawbacks": j(res["clawbacks"]), "findings": [{"severity": s, "code": c, "message": m} for s, c, m, _ in res["findings"]]}
for r in res["reps"]:
rr = {k: v for k, v in r.items() if k != "deals"}
rr["attainment"] = round(float(r["attainment"]), 4)
rr = j(rr)
rr["deals"] = [j({k: v for k, v in d.items() if k not in ("att_after",)}) for d in r["deals"]]
out["reps"].append(rr)
return out
# ---------------------------------------------------------------- self-test
def self_test():
tests = []
def t(name, cond):
tests.append((name, bool(cond)))
base = {"period": {"start": "2026-10-01", "end": "2026-10-31"}, "tiers": [{"up_to": 0.5, "rate": 0.05}, {"up_to": 1.0, "rate": 0.08}, {"rate": 0.12}],
"reps": {"a": {"quota": 10000}, "b": {"quota": 10000}}}
H = "deal_id,close_date,owner_split,type,amount,discount_pct,billing,status,cancel_date,commission_paid,approved,collected\n"
def run(rows, **over):
cfg = dict(base); cfg.update(over)
return compute(load_plan(cfg), H + rows)
def rep(res, rid):
return next(r for r in res["reps"] if r["id"] == rid)
def codes(res):
return [(f[0], f[1]) for f in res["findings"]]
r = run("1,2026-10-05,a,new,4000,,,won,,,,\n")
t("below first threshold: 4000 x 5% = 200", rep(r, "a")["commission"] == Decimal("200.00"))
r = run("1,2026-10-05,a,new,12000,,,won,,,,\n")
t("marginal: 5000x5% + 5000x8% + 2000x12% = 890", rep(r, "a")["commission"] == Decimal("890.00"))
r = run("1,2026-10-05,a,new,12000,,,won,,,,\n", tier_method="flat")
t("flat: 12000 x 12% = 1440", rep(r, "a")["commission"] == Decimal("1440.00"))
r = run("1,2026-10-05,a,new,6000,,,won,,,,\n2,2026-10-20,a,new,6000,,,won,,,,\n")
t("tiers follow close order across deals", [d["commission"] for d in rep(r, "a")["deals"]] == [Decimal("330.00"), Decimal("560.00")])
t("accelerator reached -> CM07", ("info", "CM07") in codes(r))
r = run("1,2026-10-05,a,new,30000,,,won,,,,\n", credit_cap_attainment=2.0)
t("credit cap at 200%: 250+400+1200 = 1850", rep(r, "a")["commission"] == Decimal("1850.00") and ("info", "CM06") in codes(r))
r = run("1,2026-10-05,a,new,30000,,,won,,,,\n", max_payout=1000)
t("max_payout caps the payout", rep(r, "a")["payout"] == Decimal("1000.00"))
r = run("1,2026-10-05,a,renewal,10000,,,won,,,,\n", deal_type_credit={"new": 1, "renewal": 0.5})
t("renewal credited at 50%", rep(r, "a")["credit"] == Decimal("5000") and rep(r, "a")["commission"] == Decimal("250.00"))
r = run("1,2026-10-05,a:60;b:40,new,10000,,,won,,,,\n")
t("split 60/40 credits both reps", rep(r, "a")["credit"] == Decimal("6000") and rep(r, "b")["credit"] == Decimal("4000"))
r = run("1,2026-10-05,a:60;b:30,new,10000,,,won,,,,\n")
t("split not 100% -> CM02 high, held", ("high", "CM02") in codes(r) and rep(r, "a")["credit"] == 0 and r["held"])
r = run("1,2026-10-05,zed,new,10000,,,won,,,,\n")
t("unknown rep -> CM01 high", ("high", "CM01") in codes(r))
r = run("1,2026-10-05,a,new,10000,30,,won,,,,\n", max_discount_pct=20)
t("discount over limit unapproved -> CM05 held", ("medium", "CM05") in codes(r) and rep(r, "a")["credit"] == 0)
r = run("1,2026-10-05,a,new,10000,30,,won,,,yes,\n", max_discount_pct=20)
t("approved discount is paid", rep(r, "a")["credit"] == Decimal("10000"))
r = run("1,2026-09-28,a,new,10000,,,won,,,,\n")
t("closed outside period -> CM03, not counted", ("low", "CM03") in codes(r) and rep(r, "a")["credit"] == 0)
r = run("1,2026-10-05,a,new,4000,,,won,,,,\n1,2026-10-05,a,new,4000,,,won,,,,\n")
t("duplicate deal id -> CM04, counted once", ("medium", "CM04") in codes(r) and rep(r, "a")["credit"] == Decimal("4000"))
r = run("1,2026-10-05,a,new,4000,,,won,,,,\n9,2026-08-20,a,new,8000,,,cancelled,2026-10-10,400,,\n", clawback={"window_days": 90})
t("cancelled within window -> clawback of paid commission", rep(r, "a")["clawbacks"] == Decimal("400.00") and rep(r, "a")["payout"] == 0 and rep(r, "a")["carry_out"] == Decimal("-200.00") and ("medium", "CM11") in codes(r))
r = run("9,2026-05-01,a,new,8000,,,cancelled,2026-10-10,400,,\n", clawback={"window_days": 90})
t("cancelled after window -> no clawback", rep(r, "a")["clawbacks"] == 0 and ("low", "CM08") in codes(r))
r = run("9,2026-09-20,a,new,8000,,,cancelled,2026-10-10,,,\n", clawback={"window_days": 90})
t("missing commission_paid -> estimated at first tier", rep(r, "a")["clawbacks"] == Decimal("400.00"))
r = run("1,2026-10-05,a,new,4000,,annual,won,,,,\n", spiffs=[{"name": "Annual", "when": {"billing": "annual"}, "amount": 150}])
t("SPIFF added", rep(r, "a")["spiffs"] == Decimal("150.00") and rep(r, "a")["payout"] == Decimal("350.00"))
r = run("1,2026-10-05,a,new,4000,,,won,,,,no\n", pay_on="collection")
t("pay on collection defers unpaid deals", rep(r, "a")["deferred"] == Decimal("200.00") and rep(r, "a")["payout"] == 0)
cfg = dict(base, reps={"a": {"quota": 10000, "draw": {"amount": 500, "recoverable": True, "balance": 0}}})
res = compute(load_plan(cfg), H + "1,2026-10-05,a,new,4000,,,won,,,,\n")
t("recoverable draw tops up and builds balance", rep(res, "a")["payout"] == Decimal("500.00") and rep(res, "a")["draw_balance"] == Decimal("300"))
cfg = dict(base, reps={"a": {"quota": 10000, "draw": {"amount": 500, "recoverable": True, "balance": 300}}})
res = compute(load_plan(cfg), H + "1,2026-10-05,a,new,12000,,,won,,,,\n")
t("surplus recovers draw balance", rep(res, "a")["draw_recovered"] == Decimal("300") and rep(res, "a")["payout"] == Decimal("590.00"))
cfg = dict(base, reps={"a": {"quota": 10000, "draw": {"amount": 500, "recoverable": False}}})
res = compute(load_plan(cfg), H + "1,2026-10-05,a,new,4000,,,won,,,,\n")
t("non-recoverable draw: no balance", rep(res, "a")["payout"] == Decimal("500.00") and rep(res, "a")["draw_balance"] == 0)
t("thousands separator parsed", D("12,000") == Decimal("12000") and D("1.234,50") == Decimal("1234.50"))
for label, bad in [("tiers out of order", dict(base, tiers=[{"up_to": 1, "rate": 0.05}, {"up_to": 0.5, "rate": 0.08}])),
("rate as percent", dict(base, tiers=[{"rate": 8}])), ("unknown key", dict(base, quotas=1))]:
try:
load_plan(bad); ok = False
except InputError:
ok = True
t("bad plan rejected: " + label, ok)
rr = run("1,2026-10-05,a,new,4000,,,won,,,,\n")
t("report has verdict and payouts table", "**Verdict:" in report(rr, "t") and "## Payouts" in report(rr, "t"))
for name, ok in tests:
print(("PASS " if ok else "FAIL ") + name)
passed = sum(ok for _, ok in tests)
print("%d/%d passed" % (passed, len(tests)))
return 0 if passed == len(tests) else 1
def main(argv=None):
ap = argparse.ArgumentParser(description="Calculate sales commission payouts for one period.")
ap.add_argument("plan", nargs="?")
ap.add_argument("deals", nargs="?")
ap.add_argument("--rep", action="append", default=[], help="only show this rep id (repeatable)")
ap.add_argument("--json", action="store_true")
ap.add_argument("--self-test", action="store_true")
a = ap.parse_args(argv)
if a.self_test:
return self_test()
if not a.plan or not a.deals:
ap.print_usage(sys.stderr)
return 2
try:
try:
cfg = json.load(open(a.plan, encoding="utf-8"))
except json.JSONDecodeError as e:
raise InputError("plan is not valid JSON: %s" % e)
p = load_plan(cfg)
res = compute(p, open(a.deals, encoding="utf-8-sig").read(), [x.lower() for x in a.rep] or None)
except (OSError, InputError, KeyError) as e:
print("error: %s" % (("missing field %s" % e) if isinstance(e, KeyError) else e), file=sys.stderr)
return 2
if a.json:
print(json.dumps(to_json(res), indent=2))
else:
sys.stdout.write(report(res, a.deals))
return 1 if any(f[0] == "high" for f in res["findings"]) else 0
if __name__ == "__main__":
sys.exit(main())