This work instruction is part of the guide Setting up your insurance solution.
This documentation describes step by step how to configure the bank statement functionality within the Novulo Application. It is based on revision 569 of M3439 - Importing Novulo Bank Statements. The required knowledge level for this documentation is:
- Registration of relations, including registration of bank account(s) for a relation
- Regex
- Import definitions
- Knowledge of a general ledger (ledger accounts, journals, fiscal years and periods)
1. Purpose of the Bank Statement Functionality
The bank statement functionality enables the organization to automatically import bank transactions, perform matching of imported bank transactions against debtors or creditors, purchase or sales invoices, collection and payment orders, or general ledger accounts. The fully matched bank statement can then be processed, generating a bank journal entry.
The following topics will be described in subsequent sections:
- Data model related to bank statements
- Registration of the bank account for which a bank statement will be imported
- Configuration of import definitions for importing a bank statement file (CAMT.053)
- Configuration for posting a bank statement into the general ledger of the respective organization
- The different types of matching performed after importing a bank statement to minimize manual work
2. Bank Statement Data Model
The data model (excluding configuration tables) for bank statements consists of the registration of the bank statement and the resulting financial posting in the bank journal.
The bank statement contains, on one hand, the bank transactions, which together form the total amount of movements (closing balance minus opening balance). Each bank transaction can contain one or more transaction lines to allocate the transaction amount to an invoice, general ledger account, collection or payment order, or relation.
The result of processing a bank statement is a financial posting (1 journal entry with 2 or more journal entry lines) in the general ledger of the owner of the bank account linked to the bank statement.
3. Regex Transformations and Import Definitions for CAMT.053 Import
What is CAMT.053?
CAMT.053 is an XML standard (ISO 20022) for electronically receiving bank statements. It contains detailed information on debits and credits across one or multiple bank accounts.
To populate the configuration tables with the necessary data, dataset ID 566 can be distributed to the target application. This dataset contains two configuration tables: regex transformations (regextransforms) and import definitions (importdefinitions).
The regex transformation is used to extract, among other things, invoice numbers and payment references from the transaction descriptions provided by counterparties when transferring funds, before importing the transformed file using the import definitions.
Regex transformations & import definitions
After distribution of the dataset, under All Apps – Application Management – Settings – Regex Transformations, the regex transformation shown below will be available.
By opening the record “CAMT.053,” the regex replacements are displayed, which transform the original bank statement file into the file that will be imported via the two import definitions linked to the transformation. The regex replacements can be adjusted to match the syntax of invoice numbers used in the application.
In the standard configuration, it is assumed that the invoice numbering sequence for sales invoices follows the syntax “V000000.”
4. Linking a Bank Account to the Organization
Naturally, registering a bank account is required in order to import a bank statement for that account. Through the Bank Accounts overview under My Organization (relation), a new bank account can be added.
5. Configuration for Posting into the Bank Journal
For financial postings, in addition to the standard setup of a general ledger, the following configuration is needed:
5.1 Configure General Ledger Account
Via All Apps – Settings – General Ledger Administrations – General Ledger – Ledger Accounts Overview, a ledger account can be created specifically for recording the movements of the bank account (per bank statement, one posting line is generated if all transactions fall into the same accounting period).
Key fields:
- Type: Balance
- Number: Desired number, often between 1100 and 1199 (cash and cash equivalents according to RGS – Dutch Reference Chart of Accounts). It is recommended to create a separate account per bank account.
- Description: e.g., ING212, ABN124
5.2 Create Bank Journal
Via All Apps – Settings – General Ledger Administrations – General Ledger – Journals Overview, a bank journal can be created.
Key fields:
- Type: Bank
- Code: e.g., BANK, ING01, RABO
- Description: desired description of the bank account/journal
- Ledger Account: ledger account for the bank account. The balance of this account reflects the current balance of the bank account. By linking this, the functionality knows that the financial posting of the bank statement must be booked in this bank journal.
6. Automatic Matching
After importing the bank statement, the matching process is automatically executed to link bank transactions against purchase or sales invoices, collection and payment orders, general ledger accounts, or based on bank templates.
The matching order is as follows:
| Sequence | Matching Type | Method |
|---|---|---|
| 10 | Collection or payment order | Based on PaymentInfoID, links to the bank transaction |
| 20 | Direct debit reversal | Based on EndToEndID, links to the original direct debit transaction and associated sales invoice(s) |
| 30 | Payment reference | Matches expected transaction if the bank account can be reconciled at transaction level |
| 40 | Invoice | Matches invoice based on the oldest open invoice of the relation if the invoice number is found in the transaction description |
| 50 | Invoice | Matches invoice using invoice number extracted via regex transformation |
| 60 | General ledger account | Matches using a bank template linking the correct ledger account |
| 70 | Invoice | If the bank account matches, links the oldest invoice of the relation with the exact same amount as the transaction |
7. Bank Templates
Bank templates allow automatic recognition and processing of recurring transactions based on the counterparty’s bank account and/or the description of the transaction.
Examples of templates:
| Criteria | Action | Ledger Account | Description |
|---|---|---|---|
| Description contains “ABN” and Account is NL01ABNA12312131 | Book as bank charges | 4300 | Automatic bank charges |