Unable to render embedded object: File (Teach_Centre_Navy.png) not found.

Skip to end of banner
Go to start of banner

Bank Reconciliation > Data Import - New Specification

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

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

image-20240207-234027.png
  • The data import box will open

  • Click on the settings tab > the create import specification button

image-20240207-234348.png
  • Complete the following fields:

image-20240207-234928.png

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

image-20240207-235720.png
  • 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

image-20240208-004308.png
  • 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

image-20240208-003026.png

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.


  • No labels