Introduction to the Bank Import Plugin

Using this Bank Import Plug-in, you may import a valid bank statement file (downloaded from your online banking) saved on your system into a Bank Account (Cash-book).

This Bank Import Plugin will save you time, since you do not need to physically enter the transactions manually in the Payments Journal and Receipts Journal.

This plug-in uses Bank references to automatically allocate or link payments and deposits, receipts, other debits. credits, etc. to the correct accounts. 

It further supports the bank accounts of your debtors (customers / clients) to use the automatic receipts for debit orders, etc. 

It further supports the bank accounts of your creditors (suppliers) to use the automatic payments to manage the payments to suppliers, etc.

You may download bank statements for more than one bank account; credit card statements; etc.; provided that:

  • You are registered for online banking at your bank or financial institution.
  • Have created separate Bank, Credit Card accounts, etc. which is linked to its own Payments and Receipt Journals).

Once you have imported your bank statement (downloaded and saved on your system), the transactions on your bank statement will be imported (by a few mouse clicks) into the Payments and Receipts batch of your bank account as follows:

  • Debit transactions (payments, bank charges, interest payed, debit orders, electronic payments, etc.).
  • Credit transactions (receipts, interest received, electronic payments (receipts), debit orders, etc.).

Once this is done; you need to do the following simple process:

  • Select the Account code and Tax account; if applicable to a payments transaction.
  • Select the Account code and Tax account; if applicable to a receipts transaction.

You may also use the Context menu to locate select and search for a specific transaction, debtor (customer / client), creditor (supplier / vendor), or a general ledger account.

If any debtor (customer / client), creditor (supplier / vendor), or a general ledger account is set as an open item account, you may link specific debit transactions to specific credit transactions (and vice versa); e.g.:

  • Payment transactions – link to specific credit transactions (e.g. Purchase documents, Purchase Journal, etc.).
  • Receipt transactions – link to specific debit transactions (e.g. Invoices, Sales Journal, etc.).

This will display the Outstanding amount for each transaction, etc.

You may also select and link a part of a payment / receipt and / or one payment / receipt to more than one outstanding transactions.

You may also link an account to a specific debtor (customer / client), creditor (supplier / vendor) or a general ledger account to allow the correct automatic linking (allocation (selection of accounts)) during the next bank statement import.

Managing Bank Statements


Download and Save your Bank Statement

Before you can use this Bank Import Plugin, you need to download your Bank Statement from your financial institution and save it in a valid file format.

When downloading a bank statement; from your bank or financial institution; you may need to:

  • Sequence to list your transactions, e.g. Date, Amount or Description.
  • Select the correct start and end dates, for your requirements.

You may download bank statements for more than one bank account; credit card statements; etc.; provided that:

  • You are registered for online banking at your bank or financial institution.
  • Have a created separate Bank, Credit card accounts, etc. which is linked to its own Payments and Receipt journals).

If you have created and use the Petty Cash account as a Bank Account, you may still enter the transactions manually, if required.

Managing Bank Accounts and Account Bank References

When Bank statements are imported, it will automatically import all transactions into the Batch entry screen (combined Payments and Receipts journal). You need to allocate the transactions to the correct accounts, as the bank statement does not know which accounts in TurboCASH is matched to items on the bank statement.

TurboCASH allows you to manage these references to specific accounts. When importing a bank statement, and the account links are correct, it will automatically allocate the bank statement transactions to the correct accounts.

When importing statements and these are not linked, e.g. new receipts, payments, etc., these will then be set to Empty in the Accounts column of the Bank accounts Batch entry screen.

To automate the process for allocating (selecting accounts) to an imported bank statement, please take not of the following settings:

General ledger accounts

  • Bank References – This needs to be correctly entered on the Bank Links tab. If this is correctly entered (edited), it will match the references as imported from bank statements.
  • Tax (VAT / GST / Sales Tax) - If you are registered as a VAT / GST / Sales Tax Vendor, and VAT is applicable to the specific account, you need to link that account to the correct Input or Output Tax (VAT / GST / Sales Tax) account.

Debtor accounts

  • Bank references - This needs to be correctly entered on the Bank accounts tab. If this is correctly entered (edited), it will match the references as imported from bank statements.
  • Bank accounts – The Banking details for the Debtor (customer / client) account needs to be correctly maintained on the Accounting information tab of the Debtor account. This may be used to manage debit orders, refund debtors, etc. via the automated payments system. BIC (Bank Identifier Code) and IBAN (International Bank Account Number) accounts are also supported.

Creditor accounts

  • Bank references - This needs to be correctly entered on the Bank accounts tab. If this is correctly entered (edited), it will match the references as imported from bank statements.
  • Bank accounts – The Banking details of the creditor (supplier / vendor) account needs to be correctly maintained on the Accounting information tab. This may be used to pay creditors (suppliers) via the automated payments system. BIC (Bank Identifier Code) and IBAN (International Bank Account Number) accounts are also supported.

Different ways of keeping track of your bank transactions

You can keep track of your bank bookings in different ways, in this manual we will discuss the 2 most common ones.

  1. Statement method, where you make your bookings per statement from the bank.
    • Benefits :
      • You have a good check of the balances: the final amounts on the statement.
    • Cons :
      • The bank balances are not "up to date", you always fall behind "today" depending on the shear frequency.
      • It is possible that there are 2 statements with the same date
      • In the download, you will always download all bookings from that date
      • These "extra" bookings must be deleted either before importing, or immediately after the import in the screen for assigning the reference
  1. Date method, in which you record the bookings by date.
    • Benefits :
      • The bank balances are "up to date", you are one day behind today.
      • No corrections on downloads, with bookings of the same date appearing on 2 statements.
    • Cons :
      • None as far as known.


Enter cash / bank details according to the "statement method"

You prepare statement no. 1 to verify the data.

You now have 2 options for entering the transactions:

  1. You can download the statement and process it electronically via electronic banking.
  2. You can enter the statement manually.


License 

This plugin is shipped with the osFinancials5 Business class subscription. Registered versions of osFinancials5 Business class subscription can import and process unlimited statements for each of your Bank accounts. 

Once the osFinancials5 Business class subscription is purchased, an e-mail with the registration (activation) codes and instructions will be forwarded to you.

---------------------------

Handyman Hardware Store - osFinancials 5.1.0.16 

---------------------------

This demo plugin allows 0 records to be entered. If you need more, please buy the product at https://www.osfinancials.org/en/webshop

---------------------------

OK   

---------------------------

Configuring the Set of Books for the Bank Import plugin


Activating the Bank Import Plugin

To enable this feature, you first need to deselect (remove the tick) on the Default bank reconciliation setting.

This will change the “Bank account recon options screen” and combine the Payments and Receipts Batch entry screens into one screen.

To activate the Bank Import plugin:

  1. Go to the Setup ribbon and select Setup → System parameters
  2. Deselect (remove the tick) on the “Default bank reconciliation” to activate this plugin.



  1. Click OK. This will save your setting.


Settings for the bank account batch

Firstly, you will need to check and confirm the settings of the Bank entry batch. 

  1. Go to the Default ribbon and select Cash/Bank entry . The “Bank account recon options” screen is displayed:


  1. Select the “Bank account” into which you need to import your bank statement. For the purposes of this tutorial, select "ABC – Bank". 
  2. Click Open. The "Batch entry" screen, is displayed, as follows:


  1. Click F10: Setup . The "Options for this batch” - “Standard” tab is displayed: 


  1. Check the following items:
    • The contra account is the Account code (number) of your bank account in your ledger.
    • The check mark "No tax” should be 'on'. If you are not registered as a VAT (GST/Sales tax vendor you may leave this option unticked. 
  1. Then click on the "Advanced" tab and accept the settings as below. 


  1. Click on OK. if the settings are selected correctly. This will save your options and close the “Options for this batch” screen. 


Working with the bank import plugin

Now all settings are checked/set correctly, transactions can be imported with the bank import plug-in.

Import Bank statements 

On the Default ribbon and select Cash/Bank entry . The “Bank account recon options” screen is displayed:


Select the “Bank account” into which you need to import your bank statement. 

For the purposes of this tutorial, select “ABC – Bank”. 

Click Open. The "Batch entry" screen, is displayed, as follows:



Click on the "Import bank statement" . The “Import bank statement” screen is displayed:



Select a file format. You may select a Country to list the available file formats supported for a specific country. See - Supported bank import file formats.

For the purposes of this tutorial, select the “INT-OFC / OFX standaard 2” file format. 



Then click on "Import".

 


Select the “Show details” option. If the “Show details” is not selected (not ticked), the “Check” screen will not be launched.

The “Open” screen is displayed. Select the correct bank statement saved on your system. 

With the installation of TurboCASH5.2 (365) an example bank statement “2020-03-Handyman-ABC-Bank.ofc” is included in the " ...\bin\Tutorials\EN-TUTORIALS\BANK-STATEMENTS " folder. 


Click on Open. The transactions included in the bank statement file will be imported. 

If the “Show details” option is not selected (not ticked) on the "Import" tab of the "Import bank statement" screen, the “Check” screen will not be launched.


Check here if the transactions are correct. If a transaction is incorrect, this can be removed with the "Delete line" button. If a line is yellow, there is a change this transaction has already been imported into a previous import.

Check this transaction carefully!


Explanation of the "Check" screen:

Delete line

This removes a selected line. For example, if it has been imported before.

Split

With this button, an amount can be split, for example, if one amount has been paid, but there are two VAT rates or one payment is made for more than one items, which need to be allocated to different accounts. 

Merge

With this button amounts can be merged, for example if two payments are actually one invoice or need to be allocated to one account.

Statement reference

The reference is entered here, so that the payment can easily be retrieved later. Entering the statement reference it is not necessary, but you may need to enter for your specific requirements.

Apply

With the apply button, the reference is adjusted.


If all statements are correct, click on Close. The screen below is displayed. In this screen, the imported bank statement(s), are listed.



The details displayed on the “Import Bank Statement” screen is as follows:

  1. Transaction no. – This will be the transaction number generated and would consist basically of the date yyyy/mm/dd format; followed a time stamp and a reference number. This may differ from your operating system's regional and date settings.
  2. Total – This is the net value Credit (Deposits / Receipts) minus the Debit (Payments) amounts. If the Credit (Deposits / Receipts) is more than the Debit (Payments) this will display a positive amount. Should the Debit (Payments be more than the Credit (Deposits / Receipts) this amount should be displayed as a negative (“ -”) amount.
  3. From – The start date as selected while downloading (exporting) the bank statement from your bank or financial institution.
  4. Until – The end date until when transactions would be included in the downloaded (exported bank statement).
  5. Posted – The default value is “0”. This means that the transactions have not yet been posted (updated to the ledger). Once you have allocated (selected) Accounts and / or Tax accounts (where applicable) a value of “1” as posted (processed) will be displayed.

The colour of the font for this line will change from black to blue if transactions have been processed and / or posted.

If the bank reconciliation is done, the font colour will change to green.


Click Close again to actually import the transactions in the bank statement into the batch. This closes the “Import bank statement” screen. The bank statement transactions is imported into the “Batch entry” screen for the selected bank account:


Change the name of the Batch entry

Change the name of the batch (alias) (i.e. “ABC-Payments”) of the Batch. In this example, "ABC-Bankst.5" is entered in the “Change alias” field. This will identify the specific Bank statement for which bank is processed in the specific batch.  


Allocating transactions to ledgers / debtors / creditors 

In the next step each transaction must be specified with an account (debtor / creditor / ledger) which belongs to the transaction. 
This account can be allocated in the account column. There are different ways to link a transaction to an account.

  1. The account is looked up by account name. 
  2. The account is looked up by looking up the corresponding document.
  3. The account is looked up by looking for a debtor or creditor account.
  4. The account is entered automatically, because it is linked to the (bank) account number (i.e. the debtor is entered because the debtor himself and his / her bank account are linked).



Account looked up by account name

The details on a bank statement will be available in the batch entry screen for the bank account. These details will be displayed, for each selected transaction, in the following options of the batch entry screen:

  1. Underneath the icons the descriptions as entered or processed by the bank.
  2. Description column. 

These descriptions or details provide an important guide to identify and select the correct applicable account. 


To allocate an account to a transaction by name: 

  1. Select a transaction. 
  2. Click on the Account column and enter a few characters of the descriptions. In this example, “ba” is entered. All the accounts containing “ba” is listed. 
  3. Select “Bank charges”.    


The account looked up by searching for the corresponding document

This way of allocating account to the transaction is only possible if there is a document (invoice / quote / credit note / purchase / purchase return / order).

If the transaction belongs to a journal entry, this option cannot be used.


To allocate an account to a transaction by document number: 

  1. Select a transaction. In this example, “IN000003” is selected. The details from the imported bank statement displays 2 key details. You can now choose to select either “Document lookup”, “Document lookup with amount” or “Lookup debtor”.  
  2. Right-click on the transaction line and select “Document lookup” or “Lookup document with amount” on the context menu. The document entry listing all invoices will be listed.  



  1. Select “IN000003” for “Mr. AB Green”.      
  2. Double-click to transfer the selected “Document” to the “Batch entry” screen. 


In the "Lookup document" or “Lookup document with amount": 

  • With the first option, the “Document entry” screen will be opened and the correct document can be searched for.
  • With the second option, “Lookup document with amount", a document with a corresponding amount will be searched for.

You may use the filter options, to filter for a specific document. If the document you are searching for is still unposted, it will not be listed if “Posted” is selected. You may then select “All or “Unposted” options to list unposted documents. 


With the double-click, the “Open item selection” screen will automatically be launched (if the Debtor account is set as an “Open item” account) . In the example of “Mr. AB Green”, the transaction can be directly linked (matched) to the specific invoice.


The payment details and the invoice, (i.e. invoice “IN000003”) is displayed. Since the 2,000.00 payment is more than the payment, you can select to link other outstanding invoices, e.g. “INV001” (Batch entry invoice) for the balance of the payment received. 

The account looked up by searching for the Debtors / Creditors account

If a debtor or creditor name is in the details of the batch entry screen, you may select from the context menu to lookup a debtor or creditor account. 

To allocate an account to a transaction by creditor account: 

  1. Select a transaction. In this example, “PAYMENT ABC-OFFICE SUPPLIES” is selected. 
  2. Right-click on the transaction line and select “Lookup creditor” on the context menu. The “Creditor accounts” listing all creditors (suppliers) will be listed.  


  1. Select “ABC-Office supplies”.      
  2. Double-click to transfer the selected creditor account to the batch entry screen. 


With the double-click, the “Open item selection” screen will automatically be opened (if the Creditor account is set as an “Open item” account. In the example of “ABC-Office Supplies”, the payment transaction can be linked (matched) to the batch transactions or documents.



Select “PU002” (batch purchase) as the purchase transaction, for which this payment was made.  

Press the Enter key and the linked transaction will updated in the “Batch entry” screen.

Account automatically selected as it is linked to the bank account number

It is also possible to link an account number to debtors/creditor/ledger account.
With this, transactions in the bank import are directly linked to the correct account. This linking can be done in several ways:

  1. If the number of the bank account of a debtor or creditor is known, this can be entered into the details of the relevant debtor/ledger/creditor.
    The transactions with the same bank account number entered for the debtor / creditor will be linked to the correct account in the "Account" column.
  2. It is also possible to link a bank account from a transaction. Stand on a transaction. The account (debtor, creditor or ledger) must be entered here for the first time.
    Then click the right mouse button and choose "Link bank account". With the next transaction from this account, the account will be filled in automatically.

List the transactions in the batch


Post (Update) the transactions to the ledger

If all the transactions are correctly allocated to the correct accounts, the imported bank transactions can be posted (updated) to the ledger. 

Click on the F6: Post icon. 



Since a transaction is posted from this bank account to another bank account (e.g. Petty cash) is posted, the following confirmation message, is displayed:

"You are putting cash from a bank/cash directly to a bank/cash account! Do you wish to continue?
Line : 20112901 PETTYCASH        REPLENISHMENT 12345678902 810000 ABC - Bank to 820000 Petty cash"

Click OK on the confirmation message. The “Batch entry” screen will automatically be closed. 

The "Bank account recon options" screen is displayed: 



All the imported and processed bank statement transactions, which are posted (updated) to the ledger, will be listed. 

Bank import Check screen options 

Before you close the Bank Import Statement – Check screen, you may Delete a line, merge or split transactions.

Splitting transactions

If there any transactions on the “Import bank statement – Check” screen, you may split the transaction into two (2) or more entries.

You may need to do this, if a payment or receipt needs to be allocated to different accounts.

To do this on the “Import bank statement – Check” screen:

  1. Click on the transaction to split.
  2. Click in the Split button. You may also right-click and select the “Split” option on the context menu. The “Split” amount screen will be displayed.
  3. Enter the amount for the amount to split. It will insert another transaction with the same details as the selected transaction with the remaining amount.
  4. Click OK. Repeat the process, if necessary.

If this is not done at this stage (on the “Import bank statement – Check” screen), you may add it later on the Batch entry screen for the Bank account.

To do this on the “Batch entry” screen:

  1. Select the transaction and click on the ^N:Insert icon. In the blank row, enter the transaction details and the new amount.
  2. On the selected transaction to split, over type it with required amount. Repeat as necessary.


Merge transactions

If there are a few transactions on the “Import bank statement – Check” screen, you may merge (consolidate) it into one (1) entry.

To do this on the “Import bank statement – Check” screen:

  1. Press the Ctrl key and while holding your mouse down, click on the transactions to merge (consolidate).
  2. Click in the Merge button. You may also right-click and select the “Merge” option on the context menu.


To do this on the Batch entry screen:

  1. Select the transaction to be merged and over type the amount with the total of the transactions to be merged.
  2. Select the transaction to be deleted and click on the ^D:Delete icon. Repeat, as necessary.


Batch entry – Context menu options 

This context menu will allow you to select more advanced functions to assist you with the allocation process.

If there are any transactions, which are displayed as “Empty” in the “Account” column, you need to allocate them to the correct account using the “Account lookup”. In addition to the“Account lookup” (select an account from the list), you may use the context menu (right-click on a selected line) to allocate or link the transactions.

When you right-click on a row (line) in the batch, the following context menu will be displayed:

  1. ^D:Delete – This will remove a selected transaction or transactions from the Batch entry screen. This is the same as the ^D:Delete icon on other “Batch entry” screens.
  2. Update bank account – This will update the references as in the “Reknr” column (“Import bank statement - Check” screen) to the following:
    1. Bank links tab of Ledger Accounts (Setup → Accounts (Setup ribbon)).
    2. Bank account no. on the “Bank accounts” tab of the debtor (customer / client) accounts (Debtors (Default ribbon)).
    3. Bank account no. on the “Bank accounts” tab of the creditor (supplier / vendor) accounts (Creditors (Default ribbon)). 
  3. Lookup document – Find locate a specific document.
  4. Lookup document with amount - Find locate a specific document by amount.
  5. Lookup debtor - Find locate a specific debtor (customer / client) account.
  6. Lookup creditor - Find locate a specific creditor (supplier / vendor) account.
  7. Link bank account from import to accounts -
  8. Link all records with current account bank accounts
  9. Show refs for account - View, add, edit, delete to manage references for accounts.
  10. Select from account open item list - This will display a list of the transactions on the “Open item selection” screen for the selected account. On this screen, you may select a transaction from a list to link the transaction in the “Batch entry” screen.
  11. Open item – Launch the “Open item” screen, for the selected account.
  12. Search by amount
  13. Find with central search
  14. Match ideal bank @*@


Supported bank import file formats

If your bank or financial institution's available file formats, is not currently supported (cannot be imported in this Bank Import Plugin), please send us one downloaded file, and we will implement support for your bank or financial institution's bank statement format.

Country specific formats

 

Country

File

Comment 1

Remarks

Belgium

BE 

Fortis bank

Comma separated

 

BE

ING

Comma separated

 

BE

KCB

 

 

BE

KCB

Comma separated

 

BE

Paypal

 

 

BE

Recordbank

Comma separated

 

France

FR 

INT-ofx general format

OFX [Open Financial eXchange file]


Netherlands

NL 

ABN-AMRO

Tab delimited

 

NL

Afterpay

 

 

NL

Fortis bank

Tab delimited

 

NL

Frieslandbank

Comma separated

 

NL

Icepay

 

 

NL

iDeal

Comma separated

Download from Dashboard

NL

iDeal

Comma separated

Email from dashboard (rapport)

NL

ING

Comma separated

 

NL

Mastercard Creditcard

TSV

 

NL

MCG

 

New

NL

MT940

General format


NL

MT940

General format

On booking date

NL

MT940 Rabobank

 

 

NL

MT940 SNS bank

 

 

NL

MT940 Triodos bank

 

 

NL

Multi Safe pay

MT940 (General format)

 

NL

Paypal

 

 

NL

Postbank

Comma separated (CSV)

 

NL

Postbank

Comma separated (CSV)

New

NL

Rabobank

Comma separated (CSV)

Fixed length

NL

Rabobank

Comma separated (CSV)

ideal payment id

Spain

SP 

SP-MT940 Triodosbank



United Kingdom

UK 

UK-HSBC UK Transaction File

 

 

United Sates of America

USA 

Bank of America – Quicken QIF 4 digit 

QIF [Quicken Interchange Format]

 

USA

Bank of America – Comma separated

Comma separated (CSV)



International (universal) formats

In addition to a various file formats for the Belgium, Netherlands, France, Spain, United Kingdom and United States of America environments, the following file formats are currently supported for the international banking environments:

Country

File

Comment 1

Comment 2

Remarks

INT

INT-MT940 (algemeen formaat (SEPA proof))


 

 


INT-PayPal


 

 


INT-OFC/OFX standaard

OFC [Open Financial Connectivity file] / 

OFX [Open Financial eXchange file]

 

 


INT-Simple comma separated column Date dd/mm/yyyy, description, Amount . is thousandseperator

Simple comma seperated

Comma separated (CSV)

 


INT-OFX bankexport

OFX [Open Financial eXchange]




INT-QIF format (mm-dd-yyyy)

QIF [Quicken Interchange Format]




INT-OFC / OFX standaard 2

OFC [Open Financial Connectivity file] / 

OFX [Open Financial eXchange file]


 


INT 053 CAMT file format