How do I use my spreadsheets to reconcile the bank?
What you keep in a sheet outside the ERP —the payments of an aid programme, what you pay your collaborators— is loaded as reconciliation hints. A hint says “this money is this”: when you reconcile, the ERP shows it next to the bank line it matches and, if it knows which customer it belongs to, proposes their bills. It creates no collections and no journal entries.
What do I need before I start?
The sheet in Excel or CSV, with one row per collection or payment. At least: a reference (the voucher, settlement or order number) and the amount. It helps a lot if it has the date, the tax ID or the code of the customer or supplier, and the invoice number.
How do I load a sheet?
- Go to Treasury and click Reconciliation hints.
- Click Load a sheet. Data import opens with the type Hints to reconcile the bank already chosen.
- Upload the sheet and pair its columns with the fields:
- Origen: a name for the sheet, such as “Kit Digital” or “Collaborators”. It is usually the same for every row.
- Referencia and Importe, which are required.
- Sentido: write “pago” if the sheet has payments as positive amounts. If the amount already has the minus sign, it is not needed.
- Fecha, Contraparte, Nif, Iban, Factura, CodigoTercero, Concepto and Grupo, if you have them.
- Save the pairing as a template: next time you load that sheet, it is already done.
- Import. If an invoice or a party is not found, the row is loaded anyway and the warning says so.
Loading the same sheet again updates what was already there: nothing is duplicated.
How do I bring in the online store's collections?
The bank's card terminal credits everything collected by card or Bizum in a day as a single line, and only the store has the breakdown. For past months, bring it in as hints:
- In Reconciliation hints, click Bring in from the store.
- Choose the store and the period (From and To).
- Click Bring in the collections. It goes to the background job queue: follow it in System → Background jobs.
Each payment comes in as a hint with the order number and is grouped by the card terminal session in which it was collected. When you reconcile, the bank credit matches the sum of its session's orders, even if the bank credits it with the fee deducted. It creates no orders, invoices or collections: for day-to-day work, the store's collections follow their own path.
Bringing in the same period again updates what was already there.
What is the Grupo field for?
For payments the bank makes together. If you pay three settlements with a single transfer, give them the same Grupo: the bank line matches the sum of the three.
Where do I see the hints when reconciling?
In Bank reconciliation, when you open a pending line, the Reconciliation hints block shows the ones that match it and why: the reference appears in the bank description, the amount is the same, the date is close… If the best one knows which customer or invoice it is, their pending bills already come in the Composition against due dates. Review and confirm as usual.
The hints also appear in the file exported from Monthly reconciliation to prepare a reconciliation proposal.
What does the Reconciliation hints screen tell me?
Each hint with its invoice and party (if found) and the Bank line it matches best. If it shows “Not seen in the bank”, the sheet says there was a collection or payment the statement does not show: a payment that never arrived, a wrongly recorded date or a statement still to be loaded.
To correct a sheet, choose its source, click Delete this source and load it again. Bank lines are not touched.
If something goes wrong
| What you see | What has happened | What to do |
|---|---|---|
| “Invoice … not found: the hint is loaded without it.” | The number matches no invoice, or the sheet writes it differently | Check the number; without an invoice the hint still helps to match |
| “There are … invoices numbered …” | That number exists in several series | Write the number with its series, as on the invoice |
| The hint does not show on the line you expected | Neither the reference appears in the description nor the amount matches | Check the row's amount and date, or use Grupo if the bank paid several together |
| “A hint with a zero amount matches nothing.” | The row has a zero amount | Correct the sheet |
See also: Reconcile the bank month by month · Reconcile the bank transactions
More about Banks
- How do I load the bank statement from a file?
- How do I reconcile the bank transactions?
- How do I connect my bank to the ERP?
- How do I balance a POS or payment gateway settlement?
- How do I set up my company’s access to Enable Banking?
- Connecting the bank and reconciling the statement
- How do I reconcile the bank month by month?
- How do I review my store’s POS remittances?
- How do I review the transactions that come in from the bank?