2020-2Q: Processing Bank Statements

A new feature is added to enhance the functionality of the payment processing tools. An operator now has the possibility to import different format of bank statements. As statements for each bank can be different, it is now possible to setup the format for the different payment files to be imported. Intelligence has been added which will allow payment matches to now be made, based on InvoicingID or Invoice Number or Unique Name.

The following is needed to make use of this functionality:

1. Bank statements in csv format

2. InvoicingID: Unique ID to identify each customer/subscriber. Must be at least 8 characters long

3. Invoice Numbers: Unique ID to identify each invoice. Must be at least 8 characters long

Azotel team can assist with changes to increase the digits under point 2 and 3 mentioned above.

Steps to set up the bank processing tool:

1. Create the different file formats under menu 'Settings' > 'Import File Formats'

Import File Format

Fig 1a - Choose 'Settings' from menu > Import File Format

Sample Bank Format

Fig 1b - Create a file format for each bank template

Bank files are available in several formats each with different columns. To import data from these columns, variables need to be defined. Each column in the bank file should be replaced with a variable that best fits the column, columns with no data to import should be skipped.

List of variables:

  • amount - payment amount

  • date - payment date in YYYY-MM-DD format

  • date_MM/DD/YYYY - payment date in MM/DD/YYYY format

  • date_DD/MM/YYYY - payment date in DD/MM/YYYY format

  • date_DD-MM-YY - payment date in DD-MM-YYYY format

  • InvoicingID - if found in the file directly

  • CustomerID - if found in the file directly

  • customer_match_v1 - if InvoicingID of the customer can be found by matching the particular column. At the moment it will search the field for customer InvoicingID. Note: customer InvoicingID must be longer than 7 digits to avoid false positives.

  • skip - column has no value for processing and should be skipped

2. To upload a bank statement proceed to menu 'Invoices' > 'Receive Payment'

Import Bank Statement

Fig 2. Bulk upload transactions from statement

a) choose the appropriate Payment Type

b) select the correct bank template under Import Format

c) choose the bank file for upload

d) Upload the file

3. Transactions that could not be processed can be downloaded for further action:

Unprocessed Lines

Fig 3. Download Unprocessed Lines

4. The transactions that were matched against a customer account will appear on the list. If the statement provides dates,

these will be displayed as well as the narrative.

Proceed to select the 'Generate' button

List of Transactions for Processing

Fig 4. Matched Transaction List

5. Once submitted the function will create prepayments (where there is no outstanding balance) and a set lodgements using the reference (one for each unique date).

Payment Transaction

Fig 5. Processed Transactions

Matching Algorithm:

  • First scan for Invoice Number of outstanding invoices (Note: Invoice Number must be at least 8 characters long)

  • If above fails than scan for customer InvoicingID (Note: Invoice Number must be at least 8 characters long)

  • If above fails scan for names of customers with outstanding balances.


An example of different bank statement formats:

Example Different Bank Templates

Fig 6. Different Bank Formats

Published Date: June 4, 2020

Change History Log:

[enter revision date]

Contact Azotel Support:

Need more help? Save time by creating a maintenance ticket to Azotel through your instance.