Question:

Is it possible to import bank statements into IQs Cashbook. 

Answer:

Yes

Overview:

Importing a bank statement file in a comma-separated (CSV) or Excel (XLS) format creates bank statement lines and reconciles them with new spend/cheque/payment or receive/deposit money transactions in IQ Retail. 

Configure Bank Import Definition:

We first need to configure a bank import file definition. 

To perform this action. 

Click on Utilities -> Setup -> Module Parameters.

Click on the Ledger tab.

Select the Bank Import Definitions and click on the Setup button. 

To create a new bank import definition click on the Add button. 

To amend or update an existing bank import click on the Define Bank Import button. 

 In this scenario we are going to add a new bank import import definition. 

Type in the Import Name in the right hand side pane (Edit Data). 

Type in the Import Description in the right hand side pane, this is usually the bank's name. 

Select the file format from the bank. 

It can be in a Comma Separated Value (CSV) or Text file (TXT).

It can be in Excel format (XLS) or in a XML format (this format is not commonly used).

Click on Save Edits button to save your changes. 

Make sure that you select the Bank Import definition on the left hand side pane (Bank Imports Maintenance) and click on the Define Bank Import button. 

On the Define Data Import form, make sure that you select the Source File by clicking on the [...]. 

Select the bank import file, in this scenario it would in a XLS format that we downloaded from Standard Bank.

 Once the file is selected click on the Define Import button. 

Click on the radio button Excel file (*.xls).

Click on the Next button. 

You do not need to fill in anything on the next screen. 

File assumptions:

In the example file below from Standard Bank, you will note that the First row starts on Row 3 on the file. 

The row with the field names or column headers will be row 1. 

In the form below make sure that you fill in the First Row column as 3 this is where the detailed data is located. 

In the Row with field names make sure it is filled in as 1, this sit where the headers on the file are populated. 

In the Date Order field make sure that you select custom. 

Make sure that you type in the format as dd/mm/yyyy.

Once typed in click on the Next button.

On the Data Preview screen click on the Next button. 

On the File Preview screen; you need to map the columns accordingly, by clicking on the headers and selecting the appropriate file type.

On column A, select the TDate field, this would be the date on the statement. 

On column B, select the Reference field, this would be the deposit or payment reference on the statement. 

On column C, select the Deposit field, this would be the deposit or funds received on the statement. 

On column D, select the Cheque field, this would be the payments to your suppliers on the statement.

On the Mappings form you can define additional mappings, for example you can map the Reference and Description fields to use the same column as on the file. 

On the example below we have mapped column B for the Reference and Description fields. 

Click on the Next button to proceed.

You will see the Data preview page.

On this screen just make sure that all the information is correct. 

Click on the Next button to proceed. 

This will take you to the Summary page. 

To proceed click on the Execute button. 

Recording below:

Click on the Accept button to proceed.

Configure Bank account:

Click on the Ledger > Account Maintenance. 

Navigate to the Bank Account. 

Make sure that you select the Import File Format that you have just configured.

In this scenario it would be STD. 

Once selected click on the Accept button. 

Import Bank Statement:

To import the Bank Statement, click on Ledger > Cashbook Postings

Select the Bank Account at the top. 

Click on the Bank Import button.