NJT Bank Assistant

(Interacts with the VT Software bookkeeping packages supplied by VT Software Limited)

Product Summary

The 'NJT Bank Assistant' is a Microsoft Excel workbook that provides a structured approach to achieve a bank reconciliation, driven by the bank statement. Internet banking is required to download your bank statement, which is then imported into the workbook. This product is designed as a companion application for use with the 'VT Transaction+' and 'VT Cash Book' bookkeeping packages supplied by VT Software Limited.

The workbook contains a customised application developed in Microsoft Visual Basic for Applications (VBA). The software application runs automatically when the workbook is opened and interacts with the user by displaying a series of 'User' Forms (Windows). The worksheets are not accessed in the traditional way and are therefore hidden from view.


Main Features Include:
  • Import bank statements downloaded in 'Excel/CSV' format. Currently supports Barclays, Co-Op, Lloyds, NatWest, RBS and Sandander bank internet accounts. Additional banks can be supported on a request basis.

  • Supports multiple bank accounts including non-UK Currency accounts. For example 'Euro' bank accounts.

  • Match VT bank account transactions against the bank statement at the click of a button. Displays unmatched transactions in an Exceptions list. Highlights 'Close matches' (Called an 'Assumed match'). Also highlights Customer & Supplier transactions that are awaiting invoice allocation.

  • Supports the processing of Bank Deposit Lists using pre-designed Excel templates including UK and Currency receipts. This process also supports VAT amounts and sales invoice allocations. A single VT Bank Receipt transaction (REC) is created in the VT application containing an analysis entry for each of the deposited items. Translation differences and VAT Output transactions are calculated and created automatically.

  • Supports the creation & amendment of individual bank transactions from the bank statement including Currency amounts, VAT amounts and sales/purchase invoice allocations. The analysis account is selected via a drop down list. A Bank Receipt (REC) transaction or Bank Payment (PAY) transaction is created automatically in VT.

  • Perform a VT bank reconciliation from within the Bank Assistant.

  • Archive 'Reconciled' bank statement transactions. This process can be reversed using the 'Recall' routine.

  • Backup and restore all Bank Assistant data. Can be used, for example, to restore data following a new product release.


Minimum System Requirements

The minimum requirements for use of this product are Microsoft Windows Vista, Microsoft Excel 2007 (service pack 3) and a reliable internet connection. However, we recommended using devices with the latest versions of the Microsoft Windows operating system and Microsoft Excel.

The combined VT Software bookkeeping package needs to be installed before using the Bank Assistant.

Additionally, macros need to be enabled within the Microsfoft Excel configuration.


Organising Your Files

Files used and created by this product are organised using a pre-set folder structure. Sub folders are created automatically within the folder where the workbook resides. It is recommended that the Bank Assistant workbook is stored in a specific folder. For example, "C:\My Bank Assistant".

New Statements Folder: After a new bank statement has been downloaded, move the file to the sub folder "New Statements". The 'Import Statement' routine will inspect this folder.

Processed Statements Folder: After a new bank statement has been imported, the file will be moved automatically to the sub folder "Processed Statements".

New Deposit Lists Folder: After a new deposit list has been populated and the money deposited into the bank, move the workbook to the sub folder "New Deposit Lists". The 'Deposit Lists' routine will inspect this folder

Processed Deposits Lists Folder: After the deposit list has been processed, the workbook will be moved automatically to the sub folder "Processed Deposit Lists".

Data Backup Folder: Each backup created by the 'Backup Data Tables' routine will be stored in the sub folder "Backup".

1. Initial Set-up

1.1 VT Configuration

Note: The software licence is validated using the VAT Registration Number (for VAT registered companies) or the VT default Bank Account Number (for Non-VAT registered companies).

  • For VAT Registered companies only, populate your VAT Registration Number (via the Company properties).

  • For both VAT Registered and Non-VAT registered companies, populate your Bank Account Number within your default Bank Account (via the Bank Account properties). This is used to validate the download file during the Bank Statement import.

  • If you wish to use the Bank Assistant with a different VT Bank Account then also populate the relevant Bank Account Number for that Bank Account.


1.2 Bank Assistant Configuration
  • Within the User Configuration, check the Selected VT Bank Account. This is initially set to your default Bank Account. Note: You can create multiple Bank Assistant workbooks linked to different VT Bank Accounts.

  • Within the User Configuration, check the Accounting Period dates. On first use, these are populated with the current VT accounting period where available. This period can be changed independently of VT to facilitate transitional periods at year end. The 'Refresh' routine ignores reconciled transactions dated before the Period Start Date.

  • Within the User Configuration, populate the Import Statement File Format by selecting an item from the dropdown list. This is required to identify the correct download file format during the Bank Statement import.



2. Configuration Integrity Check


BA Integrity Check Image

A configuration integrity check is undertaken automatically at the start of each of the main routines. Where failures are encountered the results are displayed in a form. Integrity check failures may prevent the execution of these routines. The integrity check form is always displayed at the start of the 'User Configuration' & 'Licence' routines and after the completion of the 'Restore Data Tables' routine. Certain failures will require resolution in VT, whereas other failures can be recified using the Bank Assistant 'User Configuration'.




3. User Configuration

The User Configuration enables customisation of the Bank Assistant to suit specific business requirements. Certain settings are required, whereas other settings are optional. A configuration integrity check is undertaken and the results displayed in a form. This highlights potential problems before the configuration form is displayed.


3.1 User Configuration (VT Bank Account and Required Settings Tab)
  • Selected Bank Account: This is the VT bank account to be used with the Bank Assistant. On first use of the Bank Assistant, this is set to your default Bank Account. (This default account is also known as the Defined Bank Account).
    Note: You can create multiple Bank Assistant workbooks linked to different VT Bank Accounts.
    Note: This setting is used to validate the download file during the Bank Statement import.

  • Account Period Start and End Dates: On first use of the Bank Assistant, this is set to your default Bank Account. (This default account is also known as the Defined Bank Account).
    Note: You can create multiple Bank Assistant workbooks linked to different VT Bank Accounts.
    Note: This setting is used to validate the download file during the Bank Statement import.

  • Import Statement File Format: Select the format from the dropdown list. This item is used during the 'Import Statement' routine to identify the correct format of the Bank download file.

  • Reduced and Standard VAT rates: The VAT rates are applicable only for VAT registered companies. On first use of the Bank Assistant, these rates are populated with the default values, 5% and 20% respectively.
    Note: These rates are used by the 'Create/Amend Transaction' & 'Deposit Lists' routines to calculate the actual VAT amount when the 'Reduced' or 'Standard' VAT option is selected.


BA Config Image 1

3.2 User Configuration (Optional Routines Tab)
  • Allow Update: This option allows or denies the updating of transactions to VT.

  • Allow Delete: This option is applicable only where 'Allow Update' is Yes and allows or denies the deletion of VT transactions.

  • Allow Text Amend: This option is applicable only where 'Allow Update' is Yes and allows or denies the customisation of VT transaction Text.
    Note: The 'Create/Amend Transaction' routine normally generates a preformatted text.

  • Allow Deposits: This option is applicable only where Account Codes are in Use and allows or denies the updating of Bank Deposit Lists from the Bank Assistant to VT.


BA Config Image 2

3.3 User Configuration (Optional Settings)
  • Colour Intensity: This option adjusts the colour intensitiy of Buttons & Backgrounds (Higher value is brighter). Select a value between 10 and 100 percent. The Default is 80%.

  • Refresh Days Threshold: This option Adjusts the days theshold for matching bank statement and VT transactions during 'Refresh'. A smaller value may result in more exceptions. A Larger value may result in more incorrect matches. Select a value between 0 and 30 days. The default is 14 days.

  • Exceptions List: This option is used during the 'Refresh' routine to identify the scope of exceptions listed. Select 'Yes' to Include all exceptions. Select 'No' to ignore future transactions not yet present on the imported bank statement. The default is 'Yes'.

  • Transaction Text Default: This option is used during the 'Create/Amend Transaction' routine. Select 'Statement' to use the Bank Statement Description. Select 'Account' to use the Analysis Account Name.


BA Config Image 3

3.4 User Configuration (Custom Account Lists)
  • Reserved Accounts: Accounts in this optional list are prohibited from use as analysis accounts within the 'Create/Amend Transaction' and 'Deposits Lists' routines.

  • Valid Account Code Ranges: This optional range is applicable only where account codes are in use and only becomes active when populated. Allows only valid Account Code ranges to be visible within the 'Create/Amend Transaction' and valid within the 'Deposit Lists' routines. Intended for use with numeric codes and where the VT 'Chart of Accounts' is structured using strict Account Code ranges.


BA Config Image 4

3.5 User Configuration (Custom VAT Account Lists)
  • Zero Rated Account Codes: The VAT rates are applicable only for VAT registered companies. This optional list is used during the processing of 'Deposit Lists' where entries are analysed to 'Profit and Loss' accounts and the VAT column is left blank. Entries analysed to accounts that are included in this list will be rated as Zero VAT. All other 'Profit & Loss' analysis accounts will be rated as Standard VAT unless the account is normally outside the scope of VAT. Please Note: This list only becomes active when it contains at least 1 account. If this list is empty all 'Profit & Loss' entries are validated to ensure a valid VAT Rate or Amount exists. Intended for use when processing sales receipts to the 'Income' Ledger and where the 'Income' Ledger is structured using specific VAT Rated accounts. This enables an automated approach to the entry of VAT Rates.


BA Config Image 5

The Dashboard Form

The Dashboard is the main controlling form and is displayed when the workbook is opened. Subsequent forms are displayed using the buttons on the Dashboard. For example: Import bank statement, Create/amend transaction, Bank reconciliation, User configuration, Administration routines.

Certain buttons may be disabled depending on the status of a bank transaction. Also the button description may change in particular circumstances.


BA Dashboard Image

1. 'End to End' Outline Process

1.1 Import Bank Statement:
  • Download latest bank statement and move to the 'New Statements' Folder.

  • Import bank statement via 'Import Statement' button.

1.2 Process Bank Statements & Receipts:
  • It is assumed that any customer invoices (SIN) and supplier invoices (PIN) have already been created in VT.

  • Create bank receipt (REC) or bank payment (PAY) transactions using 'Create Transaction' button within the Bank Assistant or using the VT application.

  • Note: receipt and payment transactions for multiple accounts must be created using the VT application. Transactions for single/multiple invoices for the same account can be processed using the Bank Assistant.

1.3 Refresh the Bank Statement:
  • Refresh the Bank statement using the 'Refresh' button.

  • Resolve any exceptions and unallocated invoices & refresh again.

1.4 Bank Reconciliation:
  • Run the bank reconciliation using the 'Bank Reconciliation' button.

  • Note: the bank statement will be refreshed automatically.

  • Enter the statement name and select the desired closing date. Then click on the 'Refresh' button within the bank reconciliation form.

  • Click on the 'Accept' button to complete the bank reconciliation.

  • Note: the bank statement will be refreshed automatically.

1. Import Bank Statement

The 'Import Statement' routine appends new bank statement transactions to the end of the table from a downloaded bank statement file.

During the import process, the download file format and bank account is validated against the user configuration settings. Any duplicate records found in the download file are ignored. The bank balance of the first eligible transaction in the download file is also validated against the last bank statement transaction to ensure the transactions are consecutive.


BA Import Image
1.1 Download Bank Statement
  1. Download your latest bank statement in 'Excel/CSV' format then move the file to the 'New Statements' Folder. Note: Co-Op bank statements are downloaded in TXT format.

1.2 Import Bank Statement
  1. Click on the 'Import Statement' button within the Dashboard.
  2. Click on the 'New' Tab (If not already displayed).

  3. Select the downloaded file (Click on the file if not already highlighted).

  4. Click on the 'Process' button.

Note: The total number of transactions added will be displayed. The file will then be moved the 'Processed Statements' folder.




2. Refresh the Bank Statement

To refresh the bank statement transactions, click on the 'Refresh' button from the Dashboard. The Refresh routine inspects all transactions for the selected VT bank account defined in the User Configuration. Reconciled transactions dated before the Accounts Period Start are ignored.

Optional 'User Entry' details can be populated for each bank statement transaction to facilitate a more precise match. These details include Analysis Account Name and Account Code (Enter the last analysis account where multiple entries exist). Note: "Deposit List" is included at the top of the Analysis Account Name list.


2.1 The Process Explained

Two attempts are made to match the transactions to the bank statement.

  1. The first attempt uses a strict approach by checking the corresponding dates, values and oprional User Entry details where populated.

  2. The second attempt uses a similar approach to the first except it attempts to match transactions where the dates are different. The extent of the allowed difference (in days) is controlled by a user configuration setting called the 'Refresh Days Threshold'. The default is 14 days. Transactions that are matched by this criteria are identified by setting the status to 'Assumed match'. The dates for these transactions can be matched later using the 'Synchronise Transaction' button.

  3. Transactions that fail to match are added the Exceptions table. Examples of exceptions are:

    1. Duplicated transactions or incorrect transaction value.
    2. Transactions entered in VT in advance of the bank statement import.

    3. Different transaction dates (above the 'Refresh Days Threshold' setting).

    4. Where the user entry details have been populated but differ from the last analysis account within the VT transaction. Normally these can be resolved by amending or removing the user entry details and performing another Refresh.

    5. Transactions dated before the accounts period start & not reconciled.



3. Process a Transaction

VT bank transactions can be created, amended or deleted for the currently highlighted bank statement transaction. The button caption in the Bank Statement table will display 'Create' or 'Amend' Transaction, denoted by the status of the transaction. Reconciled transactions cannot be amended or deleted.

Note: before attempting to create a VT transaction, it is advisable to resolve any exceptions. This will remove the risk of creating duplicate transactions.

Note: Be careful when amending transactions that are analysed to multiple accounts. Accepting an amendment will overwrite the existing transaction and will be analysed to the account selected by the dropdown list. I.e. the other analysis accounts will be removed from the transaction. Normally, these should be created and amended using VT.


BA Process Transaction Image
3.1 Create a VT Bank Transaction

This applies where the bank statement transaction's status is "Outstanding". I.e. no matching VT transaction exists.

  1. Highlight the desired transaction and click on the 'Create Transaction' button.
  2. Populate the data items described below.

  3. Click on the 'Accept' button to create the transaction or the 'Cancel' button to abandon.

Note: A new bank receipt/payment transaction is created in VT (including any relevant invoice allocations) and the bank statement table is updated.


3.2 Amend a VT Bank Transaction

This applies where the bank statement transaction's status is not "Outstanding".

  1. Highlight the desired transaction and click on the 'Amend Transaction' button.
  2. Amend the data items described below.

  3. Click on the 'Accept' button to create the transaction or the 'Cancel' button to abandon.

Note: The existing VT bank receipt/payment transaction (including any relevant invoice allocations) and the bank statement table is updated.


3.3 Delete a VT Bank Transaction

This applies where the bank statement transaction's status is not "Outstanding".

  1. Highlight the desired transaction and click on the 'Amend Transaction' button.
  2. Click on the 'Transaction Delete' button.

  3. Click 'Yes' to delete the transaction or 'No" to abandon.


3.4 'Ledger Name' Data Item

Select the Ledger Name from the dropdown list. The Ledger may be pre-populated by entering the User Entry Details in the Dashboard.
Note: When a different Ledger is selected, the Account dropdown list is automatically refreshed.
For information, the order that the Ledgers are displayed in the dropdown list can be changed within the VT application. Within VT, click 'Setup', then click 'Ledgers', Highlight the ledger and re-position using the Up and Down buttons.


3.5 'Account Name' Data Item

Select the Account Name from the dropdown list. The Account may be pre-populated by entering the User Details in the Dashboard.
Note: Where Account Codes are in use, a search option is available to find the Account by Code.


3.6 VAT Rate or Actual Amount

This applies to Profit and Loss Ledgers only. Select the VAT Rate from the dropdown list. Choose from "No Scope, Zero, Reduced, Standard" or enter the actual VAT amount.


3.7 Invoice Allocation

This applies to Customer and Supplier Ledgers only. Under normal circumstances, the sales invoices (SIN) or purchase invoices (PIN) would have been created prior to processing the bank transaction.


3.8 VT Bank Transaction Text

The VT transaction text is created automatically in one of two formats. This format can be set using the User Configuration Parameter 'Transaction Text Default Source'. The options are 'Statement' or 'Account'.
1. Statement Format: (Default) "TTT - Bank Statement Description" where TTT = Bank Statement Type.
2. Account Format: "TTT - Account Name" where TTT = Bank Statement Type.
Text can be entered manually by first changing the 'Allow Text Amend' configuration setting to "Yes". Afterwich the text can be changed for each transaction by clicking on the Check Box.


User Entry Details (Entered via the Dashboard)

User entry details are populated from the Dashboard for 3 main reasons.
1. To provide a more precise match during the 'Refresh' routine.
2. To optionally populate the Analysis Account before creating a VT bank transaction.
3. To designate the transaction as a "Deposit List".


3.9 'Analysis Name' Data Item

Select the Analysis Name from the dropdown list then click the 'Save' button. When Saved, the Bank Statement transaction and Account Code will be updated.
Note: The 'Deposit List' item is always listed first.
Note: The order that the Names are grouped in the dropdown list can be changed within the VT.
1. Within VT, click 'Setup', then click 'Ledgers', Highlight the ledger and re-position using the Up and Down buttons.


3.10 'Account Code' Data Item

Enter the Account Code then click the 'Save' button. When Saved, the Bank Statement transaction and Analysis Account Name will be updated.



4. Process Deposit Lists

Deposit Lists are workbooks that contain a batch of customer receipts paid into your bank account. Separate workbook templates are provided for Base (normally GBP) and Currency batches respectively. Note: Each batch should contain only 1 type of currency.

For all Deposit List workbooks, you enter each receipt on a separate line. The workbook is pre-designed with entry lines from 1 to 50. For each entry, enter the Gross Receipt Amount, Analysis Account Code , VAT Rate/Amount, up to 3 Sales Invoice numbers and any Entry Notes. Please Note: The VAT Rate/Amount is relevant only to Profit & Loss Account Codes. The Sales Invoice Numbers are relevant only to Customer Account Codes.

For the Currency workbooks, you enter the currency amount for each receipt entry and the total amount banked in GBP. Note: During processing of these Deposit Lists, the translation differences amount is calculated automatically using the 'Rate to Base Currency' obtained from the Currency Item in the VT application.

During the processing of all Deposit Lists, each receipt entry is validated, including the Account Code, the eligibility of the Account Ledger, the eligibility of the VAT Rate/Amount and the Sales Invoice numbers (up to 3 numbers are allowed).


BA Process Deposit List Image
4.1 Prepare the Deposit List
  1. Create a new Deposit List workbook, either using one of the templates provided or using your previously populated workbook. Increment the Batch Number by 1. Populate the Deposit Date. For Currency Lists only, populate the Currency Item (E.G. 'Euro') and the Deposit amount in Base currency (Normally GBP). However, you may not always know the actual GBP amount deposited at this time, in which case, set it to zero. This amount can be populated later when transaction appears on the bank statement.

  2. Populate each receipt entry on a separate line.

  3. Finally, move the workbook to the 'New Deposit Lists' Folder.


4.2 Process the Deposit List
  1. Click on the 'Deposit Lists' button from the Dashboard.

  2. Click on the 'New' Tab (If not already displayed).

  3. Select the file (Click on the file if not already highlighted).

  4. Click on the 'Process' button.
    Note: The batch statistics will be displayed.

  5. Click on the 'OK' button to continue or 'Cancel' button to abandon.
    Note: The workbook will then be moved the 'Processed Deposit Lists' folder.
    Note: A single VT Bank Receipt transaction (REC) is created in VT containing an analysis entry for each of the deposited items. Translation differences and VAT transactions are calculated and created automatically. A summary of the Entry Notes and VAT amounts are also created automatically in the VT Transaction Notes.

  6. Then, refresh the bank statement transactions, click on the 'Refresh' button from the Dashboard.
    Note: Sometimes the Deposit Date entered in the workbook is different to the date on the bank statement. Where this occurs, follow the 'Synchronise' process.



5. Synchonise an 'Assumed Match' Transaction

Synchonising is a process that quickly matches a bank statement transaction that has a status of 'Assumed match' following a refresh. For example, a receipt transaction is entered in VT dated '11 April 2016' and the date on the bank statement is '12 April 2016. The bank statement is then refreshed and the bank statement transaction status is set to 'Assumed match'.

  1. Highlight the desired transaction and click on the 'Synchronise Transaction' button within the 'Bank Statement' tab within the Dashboard.
    Note: The Transaction details will be displayed.

  2. Click on the 'Yes' button to continue or the 'No' button to cancel.
    Note: The transaction date and the status of the transaction will be changed. Additionally, the User Entry details will be populated where previously blank.



6. Bank Reconciliation

This routine reconciles VT bank transactions up to a chosen Closing Date. The Status of all transactions up to this date must be either 'Ready to Reconcile' or 'Awaiting Allocation'. Transactions previously reconciled will be ignored.


BA Bank Reconciliation Image
  1. Click on the 'Bank Reconciliation' button from the Dashboard.

  2. Click on the 'Yes' button to continue or the 'No' button to cancel.
    Note: the bank statement will be refreshed.
    Note: The total number of eligible transactions and the LAST eligible transaction date are displayed as defaults.

  3. Enter the Statement Name and select the desired closing date. Then click on the 'Refresh' button within the bank reconciliation form.

  4. Click on the 'Accept' button to process the bank reconciliation.
    Note: the bank statement will be refreshed again.

Archive, Recall and Purge Transactions

Bank statement transactions that have been reconciled can be moved to the 'Archive' table. The main reason for this 'Archive' process is to segregate transactions for different accounting periods. This process is normally undertaken at the accounting period year-end, however, transactions may be archived in advance. For example, following a bank reconciliation.

Bank statement transactions that have been archived can be moved back the the bank statements table. This process is called 'Recall'.

Archived Bank statement transactions for previous accounting periods can be permanently deleted from the Bank Assistant. This optional process is called 'Purge'. The purpose of this process is to help maintain an optimum sized workbook, sometimes referred to as 'Housekeeping'.



1. Archive Bank Statement Transactions


BA Archive Transactions Image
  1. Click on the 'Archive' button within the Dashboard.
    Note: The total number of eligible transactions and the LAST eligible transaction date are displayed as defaults.

  2. Enter a different date if required and click on the 'Refresh' buttion.

  3. Click on the 'Accept' button.
    Note: The total number of 'Archived' transactions processed is then displayed.




2. Recall Bank Statement Transactions


BA Recall Transactions Image
  1. Click on the 'Recall Transactions' button within the 'Archive' tab of the Dashboard.
    Note: The total number of eligible transactions and the FIRST eligible transaction date are displayed as defaults.

  2. Enter a different date if required and click on the 'Refresh' buttion.

  3. Click on the 'Accept' button.
    Note: The total number of 'Recalled' transactions processed is then displayed.




3. Purge Archived Transactions


BA Purge Transactions Image
  1. Click on the 'Purge Transactions' button within the 'Archive' tab of the Dashboard.
    Note: The total number of eligible transactions and the LAST eligible transaction date are displayed as defaults.

  2. Enter a different date if required and click on the 'Refresh' buttion.

  3. Click on the 'Accept' button.
    Note: The total number of 'Purged' transactions processed is then displayed.

Product Options

There are 2 product options available. The unlicensed '60-day Trial' product option is free of charge and can be downloaded from this website. The 'Company' product option is subject to an annual licence fee. The '60-day Trial' workbook can be upgraded to a licensed 'Company' product by activating a Licence Key. See section below on 'How to Upgrade to a Licensed Product'.

Product Option Licence Period Price Description Delivery Method
60-Day Trial N/A Free Allows use of product for 60 days free of charge. This product can be downloaded from this site
Company 1 Year £48.00 Allows use of the product for 1 year subject to the software licence. The product is authorised for use on any computer within the licenced company. The Licence Key is dispatched via an email following receipt of a purchase order.


How to Upgrade to a Licensed Product

  1. Email your Purchase Order to 'support@NeilsonJamesTech.co.uk' specify the Licence Type (i.e. 'Purchase Bank Assistant licence' or 'Renew Bank Assistant licence') and include your VAT Registration No, Company Name, Address, Telephone and Contact Name. If you are not VAT Registered then please provide the Default/Main Bank Account number configured in VT (This is known as the 'Defined Bank Account').
  2. You will then receive an email containing your new Licence Key and a Purchase Invoice.
  3. Download and open the '60-day Trial' product. Link to your VT Company file when requested and complete the User Configuration.
  4. Then click the 'Purchase Licence' button on the Dashboard.
  5. Then click the 'Licence' button on 'Configuration Integrity Check' form.
  6. Finally, enter your Company Name, (VAT Registration Number or Bank Account No), enter the Licence Key and click 'Activate'.