Bank statement editing

Building Bank Import by ERPLY Books is a paid service and its minimum price is 99 eur + VAT.

If you want to build bank import please contact us at support@erplybooks.com

In ERPLY Books, you can build your own bank import. This is necessary because every bank statement has a different structure and with this configuration you can set up the rules for every bank statement file determining the data queue. If the bank statement is configured, then you can use that in the bank import to make connecting payments and incomes with invoices in ERPLY Books easier.

You can find this configuration when you go to “Settings -> Configuration” and find “Build your own bank import”.

When you click on “Manage”, the following window will open:

In this window you have to write the following information:

Name: write here the bank name the statement of which you are configuring. It will later be visible in the bank import tab when you select which bank file you are uploading

Data in Header?: Depending on the bank, the data can start on the first row, but not always. In this cell you have to select YES or NO.

Encoding: if you don’t know this, then write UTF-8 in this cell. You can see the file encoding in the Notepad++ programme.

Date Format: at the moment, the date is in the JAVA format. The format is dd/MM/yyyy, for example 03/01/2022.

File Separator: the possible options are “,”, “;”, “|”.

Alignment: here you have to write the alignment of the columns in the bank statement file

In the alignment cell, the information must be written in exactly the same order as it is in the bank statement file.

For example, if the CSV file information in the columns is in this order:

“Acc name”, “Account number”, “Bank name”, “Currency”, “Country”, “BIC”, “IBAN”, “Account status”, “Account type”, “Bank reference”, “Additional narrative”, “Customer reference”, “TRN type”, “Value date (dd/mm/yyyy)”, “Credit amount”, “Debit amount”, “Balance”, “Time”, “Post date”

You have to write this in the alignment cell like this:

The reason why there is “debitSumdivide-1%” in the order is because in ERPLY Books the bank import cannot have a negative value. All amounts must have a positive value and also they must be differentiated if the values are debit or credit. Based on this information, Books will show the value with a minus or plus sign.

So “debitSumdivide-1%” was important because in this sample bank statement file the credit amounts had negative values and to transform them into positive values, you need to divide them with -1.

Minimal information needed about the payment are the total amount and date. If these two are shown, the payment can be prepared and the user can determine where the payment will go if it is needed. In Books you can also set up bank statements with more information.

ERPLY Books supports the following information from statement columns:

amount- payment amount
Fee- for example the bank service fee

For example, the full payment amount is 100€ but the commission is 1€ so two payments are made: 100€ and -1€. If the fee is separated, a separate row is made for this expense. There can be many bank fees and a new row is made for every fee.

debitSum- debit amount
creditSum- credit amount
name- the other transaction party’s name where the payment is going or who paid to the company. If there are many names, then they are added with a hyphen.
invoiceNumber- the file import allows using any type of income import so the invoice number can be also added in a column
remitterId- if the registry or identification code is added
description- transaction’s description, you can add multiple descriptions and every new description is added to the previous description
debit- this is needed to distinguish income and costs because it is not standard in the banks. Transactions are described very differently in bank statements – a lot of banks signify transactions with a minus or plus sign, some banks write all transactions with a plus sign but differentiate them as debit or credit. It is possible that banks write transactions with a plus or minus and also show if it is a debit or credit transaction. So the possible “debit” values depend on how they are described. You can set up exceptions too, such as debit=D or debit=d. If you write just “debit”, then Books will search for a value named “D” or “d”.
date- transaction date
check – it is popular to use checks in the USA so it is possible that the check number column is shown in the US bank statement format. A check is an order written by a depositor instructing the bank to pay a specific amount to a recipient from the depositor’s bank account.
id- this is the bank’s archiving ID, every transaction has a unique ID in the bank and based on this we can recognise if the transaction is complete or not. If the archiving ID is missing, it is impossible to ascertain if the transaction is already done (payment or income) or not. The identification number helps to avoid double income and payments. In case of a CSV file, it is very important not to open the file before importing it to ERPLY Books because opening a CSV file in a spreadsheet programme changes all archiving ID-s into the same one and because of that the accounting softwarecannot distinguish if the payment or income is new or if it has been imported before.
currency- if the currency is written in the bank statement, you can add it – we support the standard ISO-3 format currency (Euro, USD), there cannot be currency signs (€, $)in the currency column because Books does not support that. If the currency is not shown in the file, then it is added in the currency that is stated in the configuration settings (Settings -> Configuration)
ref – reference number

Specific cases of building bank imports

There are a wide variety of rules that can be set up while building bank imports:

You can build a bank import in the situation where the bank statement is not in a CSV format but in an XML format -> you can tell the system to fill the name grid not from <NAME> but from </NAME>.
You can build a bank import in the situation where the bank statement is not in a CSV or an XML format, but instead in fixed lengths. This means that the name is 11th characters to 38th characters.
You can build a bank import in such a way that each row would check which column to take which value from.

The rule looks something like this:

date|other|other|other|currency|amount|other|other|other|id|other|name|other|other|other|other|description|other|conditions=6>0,4=Express Checkout Payment,date,other,other,other,currency,amount,other,other,other,id,other,other,other,other,other,other,descAndName,other;6<0,4=Express Checkout Payment,date,other,other,other,currency,amount,other,other,other,id,other,name,other,other,other,other,description,other;4=Account Hold for Open Authorization,date,other,other,description,currency,amount,other,other,other,id,other,other,other,other,other,other,name,other;4=Cancellation of Hold for Dispute Resolution,date,other,other,description,currency,amount,other,other,other,id,other,name,other,other,other,other,other,description;4=Chargeback,17=BLANK,date,other,other,description,currency,amount,other,other,other,id,other,name,other,other,other,other,other,other;4=Chargeback,17=NOTBLANK,date,other,other,description,currency,amount,other,other,other,id,other,other,other,other,other,other,name,other;4=Dispute Fee,date,other,other,description,currency,amount,other,other,other,id,other,name,other,other,other,other,other,description;4=General Currency Conversion,date,other,other,description,currency,amount,other,other,other,id,other,name,other,other,other,other,other,description;4=Hold on Balance for Dispute Investigation,date,other,other,description,currency,amount,other,other,other,id,other,name,other,other,other,other,other,other;4=Payment Refund,date,other,other,description,currency,amount,other,other,other,id,other,name,other,other,other,other,other,description;4=Payment Reversal,date,other,other,description,currency,amount,other,other,other,id,other,name,other,other,other,other,other,description;4=Website Payment,17=BLANK,date,other,other,description,currency,amount,other,other,other,id,other,name,other,other,other,other,other,other;4=Website Payment,17=NOTBLANK,date,other,other,description,currency,amount,other,other,other,id,other,name,other,other,other,other,descAndName,other

If the rule has “conditions”, then everything before that is the default bank statement format. Everything that follows is an exception.

All preceding elements are separated by “|”, all following ones by “;” and “,”.

This option is built on the logic that it first carries out the checks to which a row must conform and then determines the order of how the data is taken. That is: 6>0,4=Express Checkout Payment,date,other,other,other,currency,amount,other,other,other,id,other,other,other,other,other,other,descAndName,other;

6>0 -> this means that the value in the sixth column must be a number and greater than 0.

4=Express Checkout Payment -> this means that the value in the fourth column must equal “Express Checkout Payment”.

From there on it’s a queue. So the “date” is located in the first column.

Additional options:

BLANK – cell content must be empty.
NOTBLANK – the content of the cell cannot be blank.

Bank statement editing

Specific cases of building bank imports

ABOUT US

SUPPORT