close

PS Cashbook

Importing a Bank Statement Wizard

Detailed Description

This function is designed to save time in entering and reconciling bank account transactions.

 

Step 1 - export bank statement/transaction file (in CSV format) from bank

Before using the import function, you will need to export the bank statement/transaction file (in CSV format) from your bank for the relevant bank account/s and date ranges, and save it to a convenient location on your computer, USB, or memory card etc.

A CSV is comma separated values file, which allows data to be saved in a table structured format. CSVs looks like any spreadsheet but with a .csv extension. Most bank exports will allow you to select this format when exporting transactions. If you are unsure if you have the correct file, that will come to light as you go through the import process - the information displayed will not make sense if you have the wrong format.

 

Step 2 - import bank statement/transaction file into Cashbook via the Import Bank Statement Wizard

The "wizard" approach steps the user through several simple step for Importing and processing the relevant data.

  • Click on the Transactions > Import Bank Statement Wizard menu option.

 

The Import Bank Statement window below will appear. Work your way through each of the steps on that window:

  • Step 1. Select bank ... : click on the drop-down arrow to select the bank account you will be importing the bank statement/transactions file into.
  • Step 2. Select date period ... : If you want to restrict incoming transactions to a specific date range, enter the dates, otherwise ensure Include all dates is ticked.
  • Step 3. Select the file to import ... : Click on the Browse icon and select your saved bank statement/transaction CSV file to import. (ie. the file you downloaded from your bank and saved to a convenient location.)
  • Step 4. Select the layout to use ... : 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.
  • Note: The layout tells Cashbook what type of information is contained in each of the incoming columns in the selected file.
  • Click on the Next button.

 

  • Step 4 continued. Set/Check import file details ... :
    • 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.
    • Click on the Show Setup button. (top right corner) 

 

  • Step 4 continued. Set/Check import file details ... :
  • 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.
  • Note: 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.

OR:

  • 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.
  • This screen is used to set up information for automatic account allocations. OR simply click on the Process button and process all transaction in the Bank Feed Import window. 

Note:  If this screen is blank, please go Back to Step C (Step 2. Select date period ... ) and ensure the dates selected are correct (or simply tick Include all dates). 

 

 

If the Customer/Supplier column is empty, you can either:

  • 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:
    1. Select a customer or supplier from the drop-down menu by clicking on the down arrow . OR
    2. Type in some text sufficient for you to determine the destination of a payment or source of a receipt. In the situation this description is not on file and is not on your list customers or suppliers. (Note. This can also be a quick way of finding a Customer/Supplier in the drop down list.)
    3. Add a new customer or supplier by clicking on the button.

 

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 (ie. 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 (ie. 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.

 

Enter as much detail for each supplier or customer:

  • 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.

 

Note: Linking customers/suppliers to consistent portions of incoming transaction description will greatly reduce the time taken to process subsequent imports, as each customer/supplier assigned a default reference, account, enterprise, amount, dissection note etc. will eliminate the need to enter any of this information again as each transaction is processed.

 

When you are satisfied with the account allocation or you just want to simply Match and Create transactions as part of the Import Statement process, click on the Process button.

 

This will take you to the Bank Feeds Import screen.

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:

Match tab

  • 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. ie 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.



  • When the above transactions are matched, the Cashbook transaction(s) should be removed from the list of Cashbook transactions below and the matched transaction should look something like:

Create tab

  • 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 preferrable to add a default Reference and Acccount at this stage.)
    3. Add a new Customer and Supplier by clicking on the  button. (Note: It is preferrable to add a default Reference and Acccount 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.

Example 1: Accepting the imported bank transaction description for a Customer/Supplier. You will then need to manually add other details (eg. Reference, Description, Account).


(Note: Setting up a Customer and Supplier name, allocating Defaults such as Reference and Account will speed up this importing process.) 


Example 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).

 

Example 3: Add a new Customer and Supplier by clicking on the  button.

 

Example 4: Select a Customer or Supplier from the drop-down list by clicking on the  button.

  • 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'.

 

Example 5: Automatic selection due to linkages previously set up.

 

‚Äč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 transacion 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.

 

Transfer tab - this is used when an imported bank transaction is a transfer between company bank accounts.

  • 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.

 

The Balance Line

The balance line appears at the bottom of the transaction list, as follows:

  • .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 balace + 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 balace + 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

 

Other Individual Transaction Options

  • 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 crossin 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.

 

Other Group Options

Refer to the following screen shot:

  • 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 datafeed. 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 transation appears.
  • Continue as above.
     

Transfers between your bank accounts:

  • 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?

Was this helpful?

Not helpful ( ) Very helpful