Bank Transfer Reconciliation: installation and configuration

Installation

Install the module from Modules > Module Manager > Upload a module by sending the ZIP file, or copy the dfbankreconcile folder into the /modules/ directory of your shop and click Install.

During installation the module creates four tables (imports, transfers, order allocations, reminders), adds the Bank reconciliation tab under the Orders menu and registers the order page hook. The bank details used in reminders are prefilled from the native bank wire payment module when it is configured.

Nothing changes in the shop until you import a statement: no payment is recorded and no reminder is sent, as reminders are disabled by default.

The four screens of the module

• Transfers awaited: orders awaiting a transfer, with their age, amount due, reminders sent and next reminder date. At the top, four indicators: amount outstanding, transfers to review, transfers matched and reminders sent over 30 days.
• Reconciliation: imported transfers, filtered by tab (To review, Matched, Partial, Ignored, All), with the order proposals and actions.
• Import a statement: file upload and import history.
• Reminders: summary of the settings, cron task URL and log of reminders and cancellations.

Configuration

Open the configuration with the Settings button of the module toolbar or from the Module Manager.

Matching and validation

• Order statuses awaiting a transfer: orders in these statuses are compared with the imported transfers. By default, the “Awaiting bank wire payment” status. Hold Ctrl to select several.
• Status after full payment: “Payment accepted” by default.
• Status after a partial payment: keep “Keep the current status” or choose a dedicated status you created. In all cases the payment is recorded on the order.
• Validate reliable matches automatically and Minimum score for automatic validation: see the next section.
• Amount tolerance: difference accepted to treat an amount as exact, 0.01 by default.
• Accepted shortfall for bank fees: a transfer lower than the amount due by at most this value settles the order. Useful for international transfers where the customer’s bank withholds fees. 0 by default, which disables the rule.
• Search orders placed in the last: 120 days by default. Older orders are not proposed.
• Send the status email to the customer: sends the email linked to the “Payment accepted” status when the order becomes paid.

CSV import

OFX and CAMT.053 files need no setting. For CSV, leave the column fields empty: the module finds the header row within the first 40 lines and recognises the usual column names in 8 languages. If your export is not recognised, set the Column separator, the Date format and the column numbers, starting at 1:

• Date column.
• Amount column for a signed amount, or Credit column and Debit column if the bank separates them.
• Label columns: one or more columns separated by commas, for example 3,4. They are joined to form the label that is analysed.

The score and automatic validation

Each transfer is compared with the orders awaiting payment placed no later than the transfer date. Points add up:

• Order reference found in the label: 60 points. The search ignores spaces and punctuation, so a reference split in two by the bank is still found.
• Order number found after CMD, CDE, Commande, Order, Bestellung, Pedido, Ordine, Zamówienie or Encomenda: 45 points.
• Exact amount: 35 points. Amount minus bank fees, within the tolerance you set: 30 points.
• Customer name found (last name, company or billing address name, at least 4 characters): 15 points.
• Only order with this amount, without reference or number: 10 points.
• Several orders in one transfer: when the label quotes several references whose total matches the amount, a grouped proposal scoring 95 is added.

Examples: reference and exact amount score 95. Order number, exact amount and name score 95. Exact amount and name without reference score 50, or 60 if it is the only order with this amount.

A transfer is validated automatically during the import if automatic validation is on, the best proposal settles the order, its score reaches the threshold (90 by default) and it leads the second one by at least 15 points. Other transfers with a proposal of at least 40 points become Proposal, the rest No match.

Importing a statement

1. Download your account statement from your online bank as CSV, OFX, QFX or CAMT.053 XML.
2. Open the Import a statement screen, choose the file (20 MB maximum) and click Import and match.
3. The result message gives the number of incoming transfers read, the lines already imported, the orders marked as paid automatically and the transfers to review. You then land on the Reconciliation screen.

Only credits are kept. Each line is identified by its date, amount and label: a line already imported is counted as a duplicate and skipped. You can therefore import a statement of the current month every week without creating duplicates. For CAMT.053 files, a batch of several transfers is split into individual transfers with the name of each payer.

Reviewing proposals

The To review tab lists pending transfers sorted by score. For each transfer, the Orders column shows up to 5 proposals with the reference, the clickable order number, the customer and company, the amount due, any difference, the score and the reasons. A Partial payment or Overpayment badge flags an amount different from the amount due.

• Validate: records the payment on the proposed order. A confirmation is asked if the amount differs from the amount due.
• Bulk validation: tick transfers, or Select all proposals, then click Validate the first proposal of the selected transfers.
• Assign: type an order reference or number, or several separated by a space or a comma for a grouped transfer.
• Ignore: for a transfer that does not concern an order, for example a supplier refund. It stays visible in the Ignored tab and can be restored, which runs its matching again.

The Match again button recalculates the proposals of all pending transfers, for example after new orders are placed. The search box filters transfers by label, payer, order reference or exact amount, for example 1234.56.

What is recorded in PrestaShop

Validation adds a native payment to the order, visible in the Payments tab, on the invoice and in accounting exports. The payment method is the order’s, the transaction ID is the bank reference from the statement (FITID in OFX, account servicer reference in CAMT) or otherwise DFBR- followed by the transfer number, and the date is the operation date. If an invoice already exists, the payment is linked to it.

If the amount settles the order, tolerances included, the order moves to the status after full payment. The status change reuses the recorded payment and does not create a second one. Otherwise the payment is partial: the order moves to the status after a partial payment if one is set, the balance due stays displayed and a later transfer completes the payment.

Orders split between several carriers share one reference. The module handles them as a single order: the amount due is the total of the orders, the payment is recorded on the first one, as PrestaShop does, and all of them change status.

Block on the order page

The detail page of an order awaiting payment, or that received a transfer, shows a Bank transfer block: amount still due, transfers received with their date, amount and label, reminders sent, and a Send a payment reminder button.

Reminders for transfers not received

Settings

• Send payment reminders: disabled by default.
• Reminder delays (days after the order): up to 5 delays separated by commas, 3,7,14 by default. The last one sends the final reminder email. Two reminders for the same order are at least 2 days apart.
• Do not remind orders older than: 30 days by default, so that customers of old orders are not contacted on the day you switch reminders on.
• Send a copy of reminders to: optional blind copy address.
• Bank details shown in reminders: account holder, IBAN, BIC and bank address.
• Cancel unpaid orders after: 0 by default, which disables cancellation. A partially paid order is never cancelled. Cancelling restocks the products.

No reminder is sent for an order that a transfer awaiting review proposes with a score of at least 40: the money has probably arrived. The task result gives the number of orders skipped for this reason.

Cron task

The Reminders screen and the configuration show the task URL, protected by a token. Schedule a daily call from your hosting:

0 9 * * * curl -s "https://www.your-shop.com/module/dfbankreconcile/cron?token=YOUR_TOKEN" > /dev/null

The response is JSON with the number of reminders sent, orders cancelled, orders skipped and errors. The Run now button runs the same task from the back office. The Generate a new token button in the configuration invalidates the previous URL.

The emails

The module ships two templates, bankwire_reminder and bankwire_reminder_final, in HTML and text, in 8 languages. The email is sent in the order language, in English if that language is not provided. It contains the amount due, the order total, your bank details and a request to quote the reference in the transfer. The final reminder states that the order may be cancelled. Variables available to customise the templates in mails/: {firstname}, {lastname}, {order_name}, {order_date}, {amount_due}, {total_paid}, {bank_details}, {bank_details_html}.

You can also remind a customer at any time with the Remind button of the Transfers awaited screen or from the order page. Every email is logged in the reminder log.

Troubleshooting

“The file could not be read”

The CSV header row was not recognised. Open the file in a spreadsheet, note the numbers of the date, amount (or credit and debit) and label columns, and enter them in the CSV import section of the configuration.

“No transaction was found in this file”

The file was read but contains no line with a usable date and amount. Check the date format (day/month/year or month/day/year) and that the statement is not empty.

An obvious transfer has no proposal

Check that the order is in one of the watched statuses, was placed before the transfer date, within the search period, and uses the same currency as the statement. After fixing a status, click Match again.

“Order no longer awaits payment”

The order changed status or was paid since the import, for example by another transfer. Run the matching again to refresh the proposals.

Reminders are not sent

Check that reminders are enabled, that the cron URL is called (the Run now button lets you test), that the orders are younger than the maximum age set and that the shop can send emails, from Advanced Parameters > E-mail.

Compatibility

• PrestaShop 8.0 to 9.x, the same ZIP covers both branches.
• Multistore: imports and transfers belong to the active shop.
• Formats: CSV, TXT, OFX and QFX 1.x and 2.x, CAMT.053. The MT940 format is not supported.
• ModuleAdminController architecture, no Composer dependency.
• Interface and emails in English, French, Spanish, German, Italian, Dutch, Polish and Portuguese.