Knowledgebase
Trading Software > Help Desk > Knowledgebase

Search help:


Bank Statement Import Functionality

Solution

 

Import bank statement data from the clipboard, from CSV or from Excel
This is an optional add-on feature
 

Access

Main Interface: Accounts-> Reconciliation ...

 

 

Setup

Menu: Configuration -> Set Bank Account Group

Tell the system which General Ledger (GL) Account group contains your bank accounts data. The system presents a list of your GL groups to choose from. 

TOP TIP: General Ledger Groups are managed under the Configuration Menu of the Accounts -> Journal UI

Once a group is chosen, items listed in that group are presented to this UI as bank accounts, and you can start collecting statement data and reconciling statement data against activity in the BMS.

Next you will need to tell the system how you want to receive bank account statement data.

This is achieved by setting an Import Profile for each account in your list. The simplest import profile is the PASTE profile that simply means you can cut and paste your statement data into the BMS, but you may also choose to use an Excel or CSV import option that targets a specific file.

Excel or CSV Import Option

These options require that your bank statement is always presented in the same format every time. IF this an be guaranteed, and you can map your source data to the columns required by the BMS then you're good to go. Call support for help setting up this import option - it's easier that way. Once set up, the system will prompt you to identify a source file, and then import its data using the map defined for the purpose. You can have a different import profile for each account.

Paste Option

Much easier to manage and set up.

Importing data this way requires you to highlight and copy (Ctrl-C) your source data, and then click the [Paste From Clipboard] menu option on the import UI. If you get it wrong, just re-select and paste again. The system still requires a map to tell it which pasted columns should be used fr the relevant columns in the BMS, but even this is easily managed as you go.

The Enhanced Bank Statement Import Functionality notes below is largely targeted at this [PASTE] option.

Import Process

Step1

Menu: Statements -> Collect Statement Data

Opens a UI listing your bank accounts. The [Import Profile] column identifies your chosen import method.

[Import Profile] can be changed by double clicking the relevant cell. You will be shown a list of available import profiles to choose from. You can add your own custom [PASTE] options to the import profile list by using the UI's menus.

IF you're not using a [PASTE] option you can set the START_ROW, START_COLUMN. COLUMN_COUNT values to tell the system where to find relevant data in your Excel or CSV.

Step 2

Check a row in the Collect Statement UI to identify the account you are working with. Click the big green button.

The system will either ...

  • open a new screen into which you PASTE your statement data via the PAST OPTION menu
  • ask you to navigate to an Excel or CSV file to use as a data soure

When presented with a 'raw' copy of your source data you can either begin the import process by using the menu option ...

Data Check -> Show Results

which will present a list of your data as it will appear in the BMS IF you import it for real ...

Or you can edit your import map by using ...

Configuration -> Map Fields

Step 3

Data Check -> Show Results

This step presents a list of your data as it will appear in the BMS IF you import it for real. It is a good idea to review this data carefully because once a statement has been imported, it can be difficult to get it out again.

Step 4

If you are happy with how the pre-import results look, you can post the statement data into the BMS by using the IMPORT menu on the results UI.

 

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

Enhance bank statement import functionality:

Allow users to
> ignore TYPE / EVENT columns
> collect CREDIT / DEBIT values from the same column
> include Currency symbols in thier import
> Omit a balance column, and have the system create one.

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

From Version 2.01.XX.618

Users can now
> ignore TYPE / EVENT columns
Set the column map value to ZERO

> collect CREDIT / DEBIT values from the same column
Set the column map to the same imported data column number

> include Currency symbols in their import
The system will strip non numeric chars from the presented data

> Omit a balance column, and have the system create one.
The system will collect the latest balance from the account being updated, and apply CREDIT / DEBIT values to it during the import process.


 
Was this article helpful? yes / no
Related articles Import Contacts
Simplified Stock Importer
Data Import: Stock Import File - Data List
Data Import: Attributes
Price Lists
Article details
Article ID: 19
Category: Accounts and Book Keeping
Rating (Votes): Article rated 3.1/5.0 (30)

 
« Go back

 
Powered by Help Desk Software HESK, in partnership with SysAid Technologies