Importing a Bank Statement Wizard

Click on the Browse icon and select your saved bank statement/transaction CSV file to import. (i.e. the file you downloaded from your bank and saved to a convenient location.)

If this is your first time using the Import Bank Statement option, the default layout  <Create New Layout>  will be used, otherwise select one from the drop-down menu. 

Step 4 Continued. Set/Check import file details:

1. If you had selected the <Create New Layout> layout option, the information in the selected bank statement/transaction file will appear. You then need to tell Cashbook what is represented in each column.
   
2. Click on the Show Setup button. (top right corner) 
   

Step 4 Continued. Set/Check import file details:

1. This screen allows the order (column no.) of each data field in the file to be specified. In the Bank Statement Import Setup window match the import fields with column numbers.
   

Note: In the Bank Statement Import Setup window the imported bank statement file can be view at the bottom of the window in the File Preview section. At the top of each column there is a column number. e.g. 1-Date, 2-Debit/Credit, 3, Match these column numbers to the Column No. in the Field to import section above.   

For this example: 

The dates of the transactions are located in the first column - tick Date box and set Column No. to 1. (Click on the up or down arrow to select column number.)

The transaction amounts are located in the second column - tick Transaction Amounts box, click on the Combined debit/credit fields radio dial, set Column No. to 2, with Debit amounts are 'Negative amounts'.

For amounts, you will need to indicate if debits and credits are in the same column (i.e. Combined debit/credit fields) or in separate columns for debits and credits (i.e. Separate fields).

If you tick Combined debit/credit fields, you must selected how Debit amounts are identified. (i.e. Negative amounts or Positive amounts)

the references of the transactions are located in the fourth column - tick Reference (Primary) box, set Column No. to 4. 

the descriptions of the transactions are located in the fifth column - tick Description box, set Column No. to 5.

Note: If there are rows at the top or the bottom of the data that do not represent valid transactions, tick the Header rows are present and/or Footer rows are present and select how many rows should be ignored.

You can specify a default reference, for any transactions that do not have a specific reference.

• Click on the  Next  button
• Click  Yes  to the Confirm message: 'Do you want to save the current layout? If you answer No any changes you have made will be lost.'
• Enter a name for your new layout in the text box
• Click  OK  to save

• Click on the  Save Layout  button and give the layout a name. This layout can then be selected next time you import data for this selected bank. You should then be able to ignore Step 4
• Enter the name of you layout:
• Click on the  OK  button to save
• Click on the  Next  button to continue
  

Step 5. Finalise the imported bank statement:

• This screen displays the transactions from the imported bank statement/transaction file again with correct column headings and can be used  to set up information for automatic account allocations 
  
  

• 
  
  
• Click on the Process button and process all transactions in the Bank Feed Import window. 

1.           See further on how to match and link transactions for quicker and more efficient processing.

Option 2  

• Leave it blank , and assign the customer/supplier as the transaction is processed OR
• Enter a Customer/Supplier  by clicking on the transaction to highlight. In the Customer/Supplier column, either:
  
• Select a customer or supplier from the drop-down menu by clicking on the down arrow 
  

 Add a Link between bank statement descriptions and a particular supplier/customer:

• This means for example, if you add a link 'CBA', any bank statement record whose description contains the text CBA (case sensitive) will automatically link to this supplier/customer (i.e. Commonwealth Bank of Australia) during the next bank statement import.
• Click on the  Link  button. (This will take you to the  Bank Statement Link  tab/section in the  Edit Customer/Supplier  window for that particular customer or supplier.)
• Click on the  Add Link  button.
• The bank statement import description for this transaction will be displayed in this window.
• Select the portion of that text you think will always be part of this recurring transaction. For this example we have a Commonwealth Bank of Australia dividend deposit. CBA (i.e. the ASX share code) is unique to this transaction.
  

• Type in CBA next to 'Match Text'.
• Click on the  OK  button to add text link
  

• Click on the  Basic Details  tab
• Enter in a  Reference
• Select an  Account  from the drop-down list. (Single Dissection Line) 
• Select an  Enterprise  from the drop-down list, if applicable
• Enter an  Amount  if it is a regular payment such as a D/DEBIT, otherwise leave blank
• Enter a  Dissection Note  if desired
• Click on the  OK  button to save
  

Step 2. Process the displayed transactions

• Your screen should now display:
  
  • a list of all unprocessed imported transactions dated up to the  Reconcile To  date. The date will be calculated from your last reconciliation for the selected bank, based on the reconciliation frequency set for the bank. You can set that date to whatever you wish, as long as it is after the most recent reconciliation. This type of transaction will be recognized by the presence of a left-hand and a right-hand column.
    
    • The left-hand column shows the imported information
    • The right-hand column shows Cashbook's best attempt to match to an existing transaction, create a new one or determine that the transaction is a bank transfer. These matches/transfers/creations will only take affect when you click on the  Record  button.
  • all existing unreconciled Cashbook transactions. These only show in the right-hand column.
• Note that each imported transaction will either show a green tick   (the transaction passes all new transaction checks) or a red cross   (transaction checks not passed).
• You can make any changes you require and then close the window, if you need to resume the processing at a later time. Just make sure you answer "yes" to the prompt about saving the data when you do close the window.  Remember - no new cashbook transactions are created, or existing ones modified (via a match) until you click on the Record button .

 There are 3 tabs to help you process a transaction correctly:

• If you suspect that an incoming transaction should match to an existing transaction, click on the  Match  tab and then click on the  Find Matches  button. You can alter the  No. of days grace to show matches  to broaden the date range, if need be. The last-used 'days grace' will be used for future matches until that number of days is changed. 
• Click on the up arrow   to increase the number of days.
• Click on the down arrow   to reduce the number of days.
• An example for the need to increase the 'days grace':
  
  • A cheque was written on the 05/03/2014 and entered into Cashbook. The cheque was not presented until 16/03/2014. i.e. 16 days difference. In order to accommodate this date difference, the No. of days grace to show matches can be changed by clicking on the up arrow to increase the date range. Similarly, click on the down arrow to decrease.
• Transactions are "matched" via  Amount  and  Date . It is common for the date to be different by a few days (eg. cheques to clear and overnight processing for EFTPOS transactions), so the software allows (by default) for a 5 days grace when matching via amount.
• To match the imported transaction to one or more Cashbook transactions, tick the  Matched  box for each relevant transaction. When the total of the ticked transactions matches the amount of the incoming transaction, the  Unallocated  total will show as '$0.00' . At this point the  OK  button will be enabled.
• Click on the  OK  button to save the match. Click on  Unmatch  button if you want to remove matches for an incoming transaction. Click on the  Cancel  button if you want to exit the matching process without saving any changes.
• A matched transaction will be marked with a green tick   indicating it is cleared for reconciliation, with data from the existing Cashbook transaction displayed on the Match tab, if a match has only been made to a single transaction.
• Cashbook will automatically attempt to match an imported bank transaction (up to the  Reconcile To  date) with an existing unreconciled Cashbook transactions in the current company and bank.

• This enables you to create a new transaction using the information available from each incoming transaction, plus whatever other information you may need to complete the transaction.
• There are 5 ways to nominate a  Customer/Supplier: (see Examples 1-5 below)
  
  1. Accepting the imported bank transaction description for a Customer/Supplier. You will then need to manually add other details (eg. Reference, Description, Account).
  2. Entering your own Customer/Supplier description  (this will not add this to the Customer or Supplier list).  You will then need to manually add other details (eg. Reference, Description, Account). If you do wish to add this to the Customer/Supplier list, click on the  button. ( Note: It is preferable to add a default Reference and Account at this stage.)
  3. Add a new Customer and Supplier  by clicking on the  button. ( Note: It is preferable to add a default Reference and Account at this stage.)
  4. Select a Customer or Supplier from the drop-down list by clicking on the   button.
  5. Automatic selection due to linkages previously set up - see next point.

• If you have selected an existing customer/supplier, the   button will become visible. This enables you to link bank statement transaction descriptions to the selected customer/supplier. Each link must be unique and unambiguous to prevent conflict. For example, if you add a link "ZYX Insurance", any bank statement transaction whose description contains the text "ZYX Insurance" ( not  case-sensitive) will automatically link to this customer/supplier during the next bank import. An example of how to set up a link between a bank description and a customer/supplier is shown below.

• An example of setting up a link between a bank description and a customer/supplier.  The following window (or similar) will appear after you click on the  Link  button in the transaction import. In this example, 'QBE' has been set up as a link for the QBE Insurance Group Ltd. After this link has been assigned, and transaction coming in with the text 'QBE'  anywhere  in the description will automatically be assigned to QBE Insurance Group Ltd as the supplier/customer, and will use the default reference and account assigned to that supplier/customer. If you had another customer/supplier which also had 'QBE' in the description, you would need to qualify 'QBE' further e.g. extend to 'QBE INSURANCE', until it becomes unique. Note that you can't have one supplier/customer linked via 'QBE' and another linked by 'QBE INSURANCE', as both would be matched via just 'QBE'.
  

OTHER INFORMATION:

• Reference  e.g. BPAY, EFT, DEPOSIT, D/CREDIT, D/DEBIT, FEE.
• Description  - This is only relevant if you have (or will create) 1 dissection line for the transaction. It is a note pertaining to the transaction eg. Invoice Number, what you have purchase or sold, and will be saved with the dissection of the resulting transaction.
• Account -  This is only relevant if you have (or will create) 1 dissection line for the transaction.    If you select a customer/supplier with a default account, this will be used to prefill the Account. Alternatively, if you have set up PS Data Feed with a default expense/receipt account, this may be used to prefill the account. Otherwise select an item from your Chart of Accounts. Depending on your account selection preference (via the  Setup > Settings  menu option, using the  Transactions  tab), you can select an account either by code or description.
• More Details  - click on this blue link to see what the transaction would look like so far if you had entered it directly via the cashbook transaction listing. This is handy in the following circumstances:
  
  • You are getting a red cross against the transaction and you can't see why. In this instance, click on  More Details  and then click on the  OK  button. You will then see what errors are generated.
  • You want to link the transaction to existing invoices.
  • You want to dissect the transaction further than the default method allows.
  • Any other situation where you need the full power of the normal Add Transaction window.

1. If Cashbook can find another unprocessed imported transaction for the same amount on the same day in a different bank, in the opposite direction (e.g. if this transaction is a payment, the other transaction must be a receipt), it will automatically allocate the current transaction as a transfer. If the current transaction is a payment, the transfer will be marked as a Transfer To the other bank, otherwise a Transfer From the other bank.
   

• If not automatically assigned as a transfer, click on the drop-down list of banks and select the bank you wish to transfer money to or from.

• Closing Statement Balance - this figure is calculated as:
  
  • If there are previous reconciliations for the selected bank, the most recent reconciled balance PLUS new imported receipts LESS new imported payments, OR
  • If there are no previous reconciliations for the selected bank, the calculated closing balance at the start of the year (opening balance + brought-forward payments - brought-forward receipts) PLUS new imported receipts LESS new imported payments
• Error: Bank Balance Mismatch - this error will appear if the imported closing balance (as passed on by the bank) does not equal the displayed Closing Statement Balance. Click on this error to see the two figures.
• Reconciled Balance -
  
  • if there are previous reconciliations for the selected bank, the most recent reconciled balance PLUS new imported receipts that have a green tick beside them LESS new imported payments that have a green tick beside them, OR
  • if there are no previous reconciliations for the selected bank, the calculated closing balance at the start of the year (opening balance + brought-forward payments - brought-forward receipts) PLUS new imported receipts that have a green tick beside them LESS new imported payments that have a green tick beside them.
• Unreconciled items  - the sum of outstanding cashbook payments LESS the sum of outstanding cashbook receipts.
• Cashbook Balance  - the Reconciled Balance LESS Unreconciled items

• For outstanding cashbook transactions that are displayed (easily identified as having no information in the left-hand column), you can click on the  More Details  link to view/edit the transaction.
• For each imported transaction, you can click on the  Reset  link (underneath the green tick or red cross). This will cancel any changes you have made to the pending cashbook transaction, and reload the imported data for that transaction.
  
• For each transaction that shows a red cross, you can delete the transaction by clicking on the white cross   in the top left-hand corner. However, we do recommend caution with using this. The most likely reason that you would want to delete a transaction is because it is already in Cashbook and has been reconciled.
  

• The  Close  button. If there are any un-processed transactions, you will be asked if you want to save any changes you have made. You should always answer "Yes" unless:
  
  • You have not made any changes (in which case saving the data would not hurt anyway), OR
  • You have made changes but you do want to save them.
• The  Refresh  button. This will reload all unprocess imported transactions. You may wish to do this if you have altered some existing cashbook transactions via the  More Details  link, which may have altered which incoming transactions are matched to existing ones. Remember to save any changes (if you want to) you have made, when prompted.
• The  Please select a bank  option. You can use this option to switch between banks. Remember to save any changes (if you want to) you have made, when prompted.
• The  Reconcile To  date. When a bank is selected, either manually or automatically, a reconciliation date will be calculated based on the last reconciliation for the bank and the frequency of reconciliation set for that bank. You can change the date if desired, but not prior to the beginning of the year or the most recent reconciliation. If transactions are displayed at the time of changing the date, they will be reloaded.
• The  Print B/S  button. This will print a bank statement, with a running balance based on the imported transactions.
• The  Banks Summary  button. This will give you a summary of transactions available for import for each bank with an assigned data feed. Refer to usage in Step 1 above.
• The  Record  button. This will only be visible if the Closing Statement Balance = the Reconciled Balance and the Reconcile To date is not after the end of the financial year. Click on this button if you want to process all the imported transactions with a green tick. Once you click on this button, you will be presented with a screen similar to the following:

• If you want to view the reconciliation after saving all the transactions, ensure that  Print reconciliation after saving  is ticked.
• Click on the  Cancel  button if you need to change the reconciliation date.
• Click on the  Save  button to continue. You have now finalised the import of data for the current bank.

• When you are satisfied with the account allocation, click the  Process  button.
• Click  Yes  to the Confirm message: ' This routine will take you through all transaction not ticked as "Ignore" and allow you to match, ignore or add each one. Do you want to proceed?,
• Each transaction will appear via the 'Add Transactions' window, in succession.
• If all details are correct and you have added a note if required, click  OK  to add that transaction to Cashbook and continue to the next one.
• Click  Yes  to the Confirm message: 'Do you want to link "Telstra Corporation Limited" to "Telstra Corporation Limited" for future bank statement imports?' ( Note: This message will not appear for those transactions that have not been linked to a listed Customer or Supplier ).

Matching Transactions:

• If an incoming bank statement transaction appears to match a transaction already entered into Cashbook, that transaction will automatically appear:

• Click  Match With Selected Transaction  if this is a match.
• Click  Yes  to Confirm message: 'Reconcile selected transaction recording an amount of 800.00 for "John Brown"?'.
• This will take you to the next transaction.
• If not a match, click  Don't Match  and add the transaction the normal way.

Find Matches:

• If you believe there is a matching cashbook transaction for a bank statement transaction but it does not automatically appear, click on the  Find Matches  button located at the top right hand corner of the  Add Transactions  window.
• In the  Existing transactions matching incoming record number  window, go to the  No. of days grace to show matches  box and click on the up arrow to increase the number of days, until the matching transaction appears.
• Continue as above.
  
  

• For transaction that are transfer between your bank accounts, select  Transfer to  in the  Add Transaction  window, then in the  To Bank  box, selected the correct bank from the drop down menu.
• Click  OK  to save.
  

Step H.

• When all transaction have been process :
• Click  Yes  to Confirm message: 'Would you like to reconcile the imported statement?'
• This will take you to the reconciliation screen for that bank account.
• Reconcile as normal.
• Click here for link to:  How do I reconcile a bank account?