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, swap the debit and credit fields on the import specification.
ADDITIONAL FORMATTING OPTIONS - SCRIPTS
Scripts are used when additional formatting is required on an import. You can use them to delete an extra line at the bottom of the import, calculate fields, combine fields etc.