Bank statement editing
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