Perform an automatic Bank Reconciliation

Updated Jan 8th, 2021

Who is this guide for?

Accounts Users and Accounts Supervisors

Using an online banking transaction download, it is possible to automatically reconcile your bank.

Ensure your CSV file is valid

The layout of the CSV file is described below:

CSV RowName  Description
ASort CodeAs Supervisor > Financial Setup > Banks > Sort code saved against the bank you’re reconciling
BAccount NumberAs Supervisor > Financial Setup > Banks > Account Number saved against the bank you’re reconciling (no dashes but must be stored in here with a leading 0 – so 8 characters preceded by a 0)
CAccount AliasAs Supervisor > Financial Setup > Banks > Account Description saved against the bank you are reconciling – must be identical.
DAccount Short NameAs Supervisor > Financial Setup > Banks > Account ID saved against the bank you’re reconciling
ECurrencyCurrency of the bank
FAccount TypeNot required
GBICNot required
HBank NameNot required
IBranch NameNot required
JDatePosting Date of transaction
KNarrative #1Posting Detail of transaction
LNarrative #2Not required
MNarrative #3Not required
NNarrative #4Not required
ONarrative #5Not required
PTypePosting Reference of transaction
QDebitIf posting is for less than 0, minus value, else blank
RCreditIf posting is for more than 0, value, else blank

You can download this file as a template:

Set up a new bank for auto reconciliation

To successfully implement the Auto Reconciliation, first you need to create or amend the bank within Supervisor with all the correct details, ensuring that the sort code and account number are set correctly.

Step 1


Navigate to Supervisor > Financial Setup > Banks > Add Bank.

Step 2


Fill in as much information in as you can for the bank. You must, at minimum, supply:

  • Account Code (in line with your other banks)
  • Account Description
  • Bank Account Number – should be 9 characters so prefix with a 0 if necessary – e.g. 10129833 becomes 010129833
  • Bank Sort Code
  • Bank Type (Office or Client)

Edit an existing bank for auto reconciliation

Step 1


Navigate to Supervisor > Financial Setup > Banks.

Step 2


Right click on the bank you want to amend and click Edit.

Step 3


Now add:

  • Bank Account Number – should be 9 characters so prefix with a 0 if necessary – e.g. 10129833 becomes 010129833
  • Bank Sort Code

Save your changes.

Auto reconcile your bank

Step 1


Navigate to Banks & Journals, select the bank you wish to auto reconcile from the Bank/Journal box at the top, and click Refresh.

Step 2


Select Auto Reconciliation from the left hand menu.

Step 3


Click Choose File and select the CSV file which contains your bank statement. Double click it, or highlight it and click Open to select it.

Step 4


Click the Upload button, and then the Verify button.

Step 5


The transactions from your bank statement will now be displayed on the Auto Reconciliation screen. Select the most appropriate method of reconciliation from the list of options.

  • Show Unticked Statement Items – This lists all transactions from the CSV file which have not yet been ticked. (The first time the file is loaded, Osprey will display all entries in the file, and subsequent to performing any of the options below, will show anything remaining unticked).
  • Find Exact Matches – Only locates transactions in Osprey where the reference, date and amount are identical in the CSV file. Should you wish to un-tick any of the items matched selecting Confirm Changes will reflect the change in the projected balance.
  • Find First Batch – This finds batches of postings (one batch at a time) that have the same posting reference and date, and ticks them ready for instruction. Should you wish to un-tick any of the items matched selecting Confirm Changes will reflect the change in the projected balance.
  • Match on Amount Only – Results are shown where matches are found by amount only.
  • Show Cleared Statement Items – This shows all ticked items on the bank.

Select Tick Now to Reconcile the items ticked.