Bank Reconciliation > Data Import - New Specification
- Kat Grinter
- Kye Hush
The bank reconciliation data import specification needs to be setup before you can import your bank file. This specification needs to match your bank statement export exactly.
ON THIS PAGE YOU WILL FIND: |
|---|
CREATING A NEW SPECIFICATION
Before creating the new setup, download your bank statement in the standard format
In Workbook: finance & administration module > month-end sub-menu> bank reconciliation
Go to the import file tab > and click the ‘import’ button
The data import box will open
Click on the settings tab > the create import specification button
Complete the following fields:
No | Field | Description |
|---|---|---|
1 | Specification | This is the name of the new bank file. E.g. National Bank |
2 | File type | Select the file type of your bank statement. E.g. CSV or Excel |
3 | Exclude lines | This tells Workbook which lines to ignore at the top of your bank statement, e.g. your header lines. If there are 5 header lines, enter 5 |
4 | Field separator | If you’re importing a CSV file, enter a comma here. If it’s an excel file, the field will lock so nothing is required |
MATCHING THE FIELDS TO THE BANK STATEMENT
Once you’ve set up the above fields > go back to the execute tab > and select your newly created specification from the dropdown
Now you’re ready to set up the fields to match your bank statement
When entering the fields some of them will require formatting. See notes below
Fields available:
Field name | Description | Required | Formatting |
|---|---|---|---|
Transaction date | Some bank statements have 2 dates. Choose the date that is most relevant to you | YES | Date formatting needs to match your statement |
Cheque number | If your statement gives you the cheque number, you can enter it here | NO | NO |
Debit amount | If your statement has separate columns for + & -, use this field and the credit amount field | YES - if not using total amount | Number formatting needs to match your statement |
Credit amount | If your statement has separate columns for + & -, use this field and the debit amount field | YES - if not using total amount | Number formatting needs to match your statement |
Reference | The commentary on the payment | YES | NO |
Transaction type | Some statements state the transaction/payment type | NO | NO |
Balance amount | The balance of your bank account at each transaction | NO | Number formatting needs to match your statement |
Account number | The from/to account number | NO | NO |
Currency | The currency the transaction was from/to | NO | NO |
Transit number | The from/to transit number | NO | NO |
Institution number | The from/to institution number | NO | NO |
Routing number | The from/to routing number | NO | NO |
Transaction number | Optional if you have this available on the statement | NO | NO |
Amount | If you have just one column for the total that’s +/- | YES - if not using debit and credit amount | Number formatting needs to match your statement |
Field 1 needs to match column A in your bank statement, Field 2 matches column B etc.
Either use the debit and credit fields OR the amount field, depending on your bank statement setup
FORMATTING THE FIELDS
If a field needs a format selected it will come up with a yellow triangle
Click on formatting > and enter a value from the dropdown box to match your bank statement export E.g. dates: dd/mm/yyyy or numbers: -1,234.56/1,234.56
Now that you’ve setup the specification, you can import your bank file and check that the information matches correctly
EXAMPLE
If you’re using the credit and debit amount fields, check that WorkBook is recognising it correctly as they can be interpreted differently. If it’s the wrong way round, you can either swap the debit and credit fields on the import specification or use the ReverseNumericValue script below.
ADDITIONAL FORMATTING OPTIONS - SCRIPTS
Scripts are used when additional formatting is required on an import. You can use them to delete extra lines at the bottom of the import or calculate 2 fields.
In the bank reconciliation data import go to the settings tab > select the add new script parameter button
Complete the fields in the add data import script pop up (see script examples below) > click ok
Standard script usage:
DeleteEmptyLines
Lines can be deleted with this. The import can stop on its own at the last line, but if a file contains empty lines among the data then that can cause errors for some fields such as dates.
Some lines that look empty can also simply be blank in excel, and these may also be wrongly imported. This can cause an import to be overly long and encounter issues as well.
This script checks if a line is empty in certain fields and deletes it if so. It only allows checking within the first 16 fields.
Function: Checks specific fields (2 minimum) to see if they are empty and deletes the line.
Sequence: Not important.
Script type: Before default.
Field number: Not used.
Parameter: Field;Field;... So for example if you want to delete lines where field 3, 5 and 6 are empty you would write this: 3;5;6
ReverseNumericValue
Used to convert positive amounts to negative, or negative amounts to positive.
Function: Reverses the numeric value in a field..
Sequence: Not important.
Script type: Before default.
Field number: Not used.
Parameter: @field to be converted. So @5 to reverse the value in field 5.
Related articles
© Tangram 2022. All rights reserved.