Pix Reconciliation

What makes this one different

A reconciliation file that only lists payments is a ledger you already have. The bank produces one; copying it into a spreadsheet adds nothing except the satisfaction of having typed it.

What the bank does not have is the mapping between the name on a transfer and the client it belongs to. That knowledge lives entirely in your head, it is rebuilt from scratch every time somebody else has to do the reconciliation, and it is the single thing in this whole process that improves with age. The file below has a place to put it.

Download it

A CSV with six example rows, the payer-name lookup as a second block, and the working rules in comments. Opens in any spreadsheet or text editor.

Preview — pix-reconciliation-sheet.csv
Date,Time,Value,Payer name shown,Payer doc (partial),Description,Matched to,Confidence,Status,Note
2026-08-03,09:14,495.00,MARIA S OLIVEIRA,***.412.***-**,,INV-2026-118,HIGH,MATCHED,Value exact + 1 day after reminder
2026-08-04,17:52,1200.00,J R MARTINS ME,**.771.***/0001-**,ref julho,INV-2026-115,HIGH,MATCHED,Doc matches client record
2026-08-06,11:03,300.00,CARLOS EDUARDO LIMA,***.908.***-**,,INV-2026-121 (partial),MEDIUM,PARTIAL,450 invoice - 150 outstanding
2026-08-07,08:41,860.00,ANA P S RIBEIRO,***.233.***-**,,INV-2026-119 + INV-2026-120,MEDIUM,MATCHED,Two invoices summed exactly
2026-08-10,20:12,495.00,LUCIANA FERREIRA,***.556.***-**,,,LOW,UNRESOLVED,Asked 11/08 - no reply yet
2026-08-11,14:30,180.00,PEDRO H C SANTOS,***.019.***-**,nf,,LOW,UNRESOLVED,No open invoice at this value

# ===========================================================================
# PAYER NAME -> CLIENT
#
# The second sheet. This is the part that pays for itself.
#
# Payer name shown,Client,First seen,Note

Download the template

pix-reconciliation-sheet.csv · csv · 3.2 kB

  1. 1.One row per receipt, never per invoice. A transfer covering two invoices stays one row with both references, because the file has to reconcile against the bank total and splitting a row breaks that.
  2. 2.Fill in the confidence column honestly. In six weeks you will not remember which allocations were solid, and a forgotten MEDIUM looks exactly like a HIGH — which is how a wrong allocation becomes a wrongly chased client.
  3. 3.Start the payer-name lookup today, even with two rows. It is the only part of this that compounds, and everyone who reconciles by memory is rebuilding it monthly at full cost.

The four statuses, and why there are only four

Every additional status is one nobody maintains.

StatusMeansWhat you do nextCommon mistake
MATCHEDAllocated to one or more invoicesNothingMarking a guess as matched
PARTIALAllocated, balance still openKeep the invoice openClosing the invoice because money arrived
UNRESOLVEDHeld, allocated to nobodyAsk, then waitGuessing to make the row go away
REFUNDEDSent backReconcile the outgoing transaction tooForgetting the refund is a second row

Source: The template on this page. The mistake column is what actually goes wrong with files of this shape.

The monthly check, in three steps

Ten minutes. The first step is the one that catches real errors.

  • Sum the value column against the credits on your statementThey must be equal. A discrepancy means a receipt never made it into the file, and that is a much worse problem than an unresolved row — it is a payment you do not know about at all.
  • Count unresolved rows and compare with last monthA stable count is normal. A growing one is a signal about how you are being paid rather than about the file, and no amount of diligence in the spreadsheet will fix it.
  • Review every unresolved row older than thirty daysThree outcomes only: ask again, write it off, or refund it. Leaving them to accumulate is how a file becomes something nobody opens.
  • Add any new payer names you resolved this month to the lookupFive minutes. This is the step that makes next month faster and it is the one that gets skipped because it has no deadline.
  • Check partial rows against their invoicesA partial payment with a forgotten balance is the most common way a small business quietly loses money it was owed and never noticed.

Questions

Why CSV rather than a spreadsheet with formulas?
It opens everywhere, survives being emailed to an accountant, and has nothing to break when someone inserts a row. Add formulas after opening it if you want them; the file deliberately ships with none.
Should I import the bank export directly?
Yes, and then add the columns this file has that the export does not. The point is not retyping the bank's data — it is having somewhere to record the judgement you made about each row.
What if my bank exports OFX rather than CSV?
Most spreadsheet tools import OFX, and most banks offer both formats even when only one is obvious in the interface. Compare the two exports once — they frequently differ in whether the description field survives.
Does this replace the tool this site is selling?
For a lot of readers, yes, and that is a reasonable outcome. Below about fifty receipts a month a disciplined file and the payer-name lookup do the job. The tool starts mattering when the file stops being maintained.

What the manual version costs

Receipts, minutes and your own rate, in a calculator that runs in your browser.

Open the time calculator