Use two source files and an optional allocation file
Export the relevant entries from your own records, then map their columns to these headers. This first release supports this documented format; it does not claim to understand every bank export. A receipt ID is your unique ledger reference; keep the actual provider or bank transfer reference in the original statement. Required text cannot be blank; missing optional amounts default to zero. IDs must be unique ignoring letter case.
invoices.csv
invoice_id,invoice_date,customer,currency,gross,service_descriptionreceipts.csv
receipt_id,receipt_date,currency,received,fees,held,invoice_id,payerallocations.csv
receipt_id,invoice_id,received,fees,heldInvoice columns required: invoice_id, invoice_date, customer, currency, gross. service_description is optional. Receipt columns required: receipt_id, receipt_date, currency, received. fees, held, invoice_id and payer are optional. All allocation columns except fees and held are required. Dates use YYYY-MM-DD. Use plain non-negative amounts such as 1000.25, with no currency symbols, grouping separators or scientific notation. If a receipt is a refund or negative adjustment, this release cannot import it: retain that statement separately and ask the desk how to represent it.
The invoice_id on a receipt is a single explicit reference hint. For multiple invoices, leave that hint blank and supply allocations.csv or add allocations manually. Quoted fields, commas inside quotes, UTF-8 BOM and standard newlines are supported. Identifiers that resemble spreadsheet formulas are neutralised in exported CSVs; original in-memory records are not executed.
Decisions you can inspect
- Validate the complete import before replacing a workspace. Reject duplicate IDs, invalid dates, excess precision and unsupported allocations.
- Offer a candidate only when a receipt has an exact invoice ID and the currencies match. You explicitly apply the references; equal amounts alone do not trigger matching.
- Allocate received, proposed fees and held components separately. A receipt cannot be over-allocated in any component. Multiple receipts can link to one invoice and one receipt can split across invoices.
- Calculate gross minus received minus proposed fees minus held for each invoice. Negative differences are flagged. Review each row even if positive and negative differences cancel in a currency total.
- Keep currencies separate. A three-letter code is a grouping label, not confirmation that the payment is valid or the code is supported by your bank.
- Turn unallocated receipts, missing links, unexplained balances, reserves and user-marked evidence gaps into review actions. None is a compliance verdict.
Money is calculated in integer hundredths to avoid ordinary decimal rounding errors. This means the tool currently supports amounts with at most two decimals; it is not appropriate for currencies or records that require three or more decimals. There is no exchange-rate feed and no fee estimation.
Four complete worked cases
Fees, a partial payment and an unidentified receipt
EX101 is explained by USD 920 received and an USD 80 proposed fee. EX102 still has USD 1,200 unexplained. P3 has USD 150 with no invoice reference; the tool leaves it unallocated rather than guessing from a name or amount.
| Invoice | Currency | Gross | Received | Fees | Held | Unexplained |
|---|---|---|---|---|---|---|
| EX101 | USD | 1000.00 | 920.00 | 80.00 | 0.00 | 0.00 |
| EX102 | USD | 2000.00 | 800.00 | 0.00 | 0.00 | 1200.00 |
Downloads: invoices.csv · receipts.csv · allocations.csv · expected-results.csv · README.txt
One batch payout covers two invoices
The fictional provider schedule explicitly assigns USD 540 received plus USD 60 fee to EX201, and USD 360 received plus USD 40 fee to EX202. These allocations are supplied in allocations.csv. The tool does not assume that every provider distributes fees proportionally.
| Invoice | Currency | Gross | Received | Fees | Held | Unexplained |
|---|---|---|---|---|---|---|
| EX201 | USD | 600.00 | 540.00 | 60.00 | 0.00 | 0.00 |
| EX202 | USD | 400.00 | 360.00 | 40.00 | 0.00 | 0.00 |
Downloads: invoices.csv · receipts.csv · allocations.csv · expected-results.csv · README.txt
USD invoice, INR bank credit
EX301 is USD 1,000. P301 is INR 83,000. Even with an explicit invoice reference, these currencies cannot be directly allocated. Obtain the provider conversion statement and source-currency allocation. Neither an exchange rate nor a fully paid status is invented.
| Invoice | Currency | Gross | Received | Fees | Held | Unexplained |
|---|---|---|---|---|---|---|
| EX301 | USD | 1000.00 | 0.00 | 0.00 | 0.00 | 1000.00 |
Downloads: invoices.csv · receipts.csv · allocations.csv · expected-results.csv · README.txt
A rolling reserve is explained but still unreceived
USD 1,200 gross less USD 1,000 received, USD 90 proposed fees and USD 110 held leaves zero unexplained. The separate unreceived-after-fees figure remains USD 110. When released later, revise the held amount and record the new receipt; retaining both the old hold and released cash would double count.
| Invoice | Currency | Gross | Received | Fees | Held | Unexplained |
|---|---|---|---|---|---|---|
| EX401 | USD | 1200.00 | 1000.00 | 90.00 | 110.00 | 0.00 |
Downloads: invoices.csv · receipts.csv · allocations.csv · expected-results.csv · README.txt
Limitations, evidence and reproducible checks
These are working allocations, not a bank’s assessment of deductions, third-party payments, timeliness, declaration acceptance or EDPMS status. A payer-name difference is a reason to collect relationship evidence, not an automatic classification. A matched gross value does not mean a reserve has been received or a fee approved.
Amounts are bounded to 1 billion per source row; a maximum of 200 invoices, 500 receipts and 1,000 allocations keeps the report manageable. Inputs work in page memory. There is no automatic saving, document upload, live bank integration or cross-device sync. You may export a JSON backup and restore it later. Edited letters are downloaded separately. On a shared device, clear the workspace and remove any downloaded private files when finished.
For the batch case, the two received allocations add to 900 and the two fees add to 100. For the reserve case, unexplained is zero but unreceived after fees is 110. For the conversion case, no allocation is offered. These are useful checks after you import the sample files. If your results differ, contact hello@braininnx.com with the case name and non-sensitive steps.
The calculation and validation checks in the downloadable project source cover these examples, partial allocations, over-allocation, duplicate IDs, decimal precision, mismatched currencies, malformed CSV, formula protection, offsetting row errors and backup validation. A passing calculation test does not validate a legal interpretation. Check the source register for the separate regulatory guidance.