How do I Import my Bank Statement into the Bank Reconciliation?

From Catalyst
Jump to: navigation, search

Summary

This article explains how to set up the bank reconciliation to load an output file from your banking system to aid reconciliations.

This process assumes you have some working knowledge of the use of the Data Import module within Platinum. If you require assistance please Contact your Platinum dealer or our support team on 0116 230 1500 or by using our website support contact page.
This process does not import transactions into your system nor does it post journals into your Nominal Ledger or receipts/payments into your Sales or Purchase Ledgers.

More Information

Enabling this option may speed up the process of reconciling your bank account within Platinum. It will aim to match transactions from your bank file to those posted within Platinum based on a combination of the fields for Date, Amount, Comment and Account.

This article assumes you are familiar with the Bank reconciliation option within Platinum.

Configuration

You should ensure you have a sample csv file from your bank so you can identify the columns containing the "Date", "Debits", "Credits" and "Comment".

The import template should be created following the steps below. It then needs to be allocated to your bank Nominal Centre to enable the feature.

Import Template

  • Go to " Data Import".
  • At "Template Code", enter a three letter code for the new import template e.g. 'REC'.
  • Press <Enter> and enter a suitable description for the new template, e.g. 'Bank Statement Import'.
  • At "Destination", enter 'EXRD'. A description of 'External File Reader' should appear.
  • At "Source File", enter the location where you will save your bank file from your online banking system e.g. 'C:\Bank\HSBC.CSV'. The length of the path is limited so ensure the location is easily accessible.
You must download and save the file into this location each time. You must also ensure the filename remains unchanged e.g. 'HSBC.CSV' so you will need to overwrite the file each time you download it.
  • Press <Enter>.
  • At 'Line Type 1', press <F5>.

Row 1

  • Row 1 communicates to the " Bank Reconciliation" whether the transaction is a Debit or a Credit.
    • Under "Variable", enter '01' then press <Enter>.
    • Under "Expression", you should enter the expression to inform the system where the 'D' - Debit and 'C' - Credit indicator can be found. If your file does not contain such an indicator but instead has your debits and credits in individual columns you will need to enter a formula to define which column contains each value.
ST{2(,)} - where the second column of your file contains the Debit/Credit indicator. Note the use of ST as the column in the file is a text (string) result.

Or

IF NU{4(,)}=0 THEN "C" ELSE "D" - where the fourth column of your file contains the Debit (Paid out) amount. Note the use of NU as the column in the file is a numeric result.
Remember that the credits and debits are shown from the bank's point of view - i.e. credits are receipts into your bank, and debits are payments out of the bank.

Row 2

  • Row 2 communicates to the " Bank Reconciliation" the date on which the transaction took place.
    • Under "Variable", enter '03'.
    • Under "Options", enter the format in which the date in your source file is expressed. For example 'DD/MM/CCYY'.
    • Under "Expression", you should enter the expression to inform the system where the date can be found.
ST{1(,)} - where the first column of your file contains the date in the format you have specified.

Row 3

  • Row 3 communicates to the " Bank Reconciliation" the amount of the transaction.
    • Under "Variable", enter '02'.
      • Under "Expression", you should enter the expression to inform the system where the amount can be found.
IF NU{4(,)}=0 THEN NU{5(,)} ELSE NU{4(,)}*-1. Where the file contains Debits in column 4 and Credits in Column 5. If the Debit is not shown in the file as a negative value the formula converts it to a negative value to ensure the values are read correctly.
Remember that the credits and debits are shown from the bank's point of view - i.e. credits are receipts into your bank, and debits are payments out of the bank.

Row 4

  • Row 4 communicates to the " Bank Reconciliation" a description or comment for the transaction.
    • Under "Variable", enter '01'.
    • Under "Expression", you should enter the expression to inform the system where the description or comment can be found.
ST{3(,)} - where the third column of your file contains the Comment.
  • Press <Esc> to return to the field 'Line Type 1'.
  • Press <Esc> again and [Accept] the creation of your template.

Link the Import Template to the Nominal Centre

Next you will need to link the import template you have just created to the Nominal Centre for your Bank Account. To do this:

  • Go to " Nominal Ledger Maintenance Options Centre Maintenance Add or Edit Nominal Centres".
  • Search for the centre that you want to be able to reconcile.
  • Press <F5> or click [Edit] to edit the centre.
  • At "Template", enter the code of the import template you have created.
  • Press <Esc> and [Accept] the changes.

Reconciling

You are now ready to reconcile your centre with the aid of an imported bank statement.

  • Follow this article for how to reconcile your bank.
  • Enter the "Reconcile to date" and "Statement balance" in the usual way and press [Search] to list the transactions.
  • You will have two lists of transactions. On the right are the transactions in Platinum; on the left are the transactions that have been imported from the bank for reconciling.
  • Platinum will initially match any transactions that it believes are linked; these will appear as reconciled.
  • Your job is then to match up the remaining transactions, and to check the matched ones.
    • Select a transanction and double click, press <F5> or click [Select] to select an item for matching. Do the same on both sides of the screen.
You may select multiple items to match e.g. where several transactions posted in Platinum equate to one transaction your bank statement.
    • Click [Link] to link those transactions together. This will mark the transactions as reconciled.
    • You can unlink transactions by using the [Unlink] button, this will un-reconcile both the imported and Platinum transactions that had previously been linked together.
    • Repeat until you have matched all relevant items.
You do not have to match all items but you cannot select items posted in Platinum that do not appear on the bank statement.
  • Once you have finished, check the "Difference" is 0.00 and press <Esc>.
  • Click [Detailed] to print a detailed summary of both reconciled and unreconciled items.
  • Click [Accept] to confirm the reconciliation. For other options please refer to this article.

See also


Feedback
Thank you for using our Knowledge Base, we value your feedback. Did you find this article useful? 'Yes' or 'No'
Keywords AND Misspellings
bank, account, reconcile, balance, rec, acc, month end, month, end, bank reconciliation, reconciliation, bank recon, bank reconcilliation, statement, import
Tags

RP11