Bank matching and the transaction import

The transaction import functionality is a useful tool to ensure everything on your bank statement exists in Brightpearl. This article explains how to import a CSV bank statement and match the entries against transactions in your Brightpearl account.

You can also use the imported data to create new bank transactions.

Marking a transaction as matched:

  • Flags the related transactions to show they are matched
  • Locks the related transactions (if "Lock matched bank transactions" under Settings > Company > Accounting: Options is set to "Yes")

Warning: Bank matching should be done in conjunction with bank reconciliation! It's not a substitute for reconciling, as it will not catch errors with transactions that exist in Brightpearl but not on your bank statement.

Importing bank transactions

It is not currently possible to connect a bank account directly to Brightpearl for bank feeds, but the bank transaction import can be used to manually import a statement which has been downloaded onto your computer.

Each bank tends to provide a different layout and format. Brightpearl will only accept files in CSV format with the following columns, so you will need to manipulate it before importing:

bank

  • The date must be in the format of YYYY-MM-DD
  • All reference information must be contained in a single cell. Brightpearl will be able to break out individual details such as contact name and invoice reference
  • Deposits and receipts into your account are entered as positive values...
  • ... and withdrawals or payments out of your account must be entered as negative values, all in the same column
  • Save your file as a Comma Separated Value (CSV) file

Duplicated lines on the statement

If the import file contains multiple lines which are the same date, reference and amount then they will be excluded on import.

If they are true duplicates that need to be imported, editing the reference to make them unique will allow them to be imported.

How to import a bank statement

  1. Format your file with the following columns:

    Date: As YYYY-MM-DD

    Reference: As text

    Amount: As a positive or negative number to represent money in or out of the account

  2. Save your file as a CSV.
  3. Go to Accounting > Import Transactions and select the relevant bank account.
  4. Click the Import transactions link.
  5. Click Browse and select your CSV file.
  6. Click Upload

The statement lines will then be displayed on the left of the screen ready to be matched.

Matching imported bank transactions

Imported bank transactions are matched to new or existing transactions in Brightpearl. There are three methods of handling an imported transaction:

  • Match it to an existing bank transaction
    • This might be a sales receipt (SR), purchase payment (PP), bank transfer (BT), bank receipt (BR) or bank payment (BP)
      mceclip0.png
  • Allocate it to an existing unpaid invoice and create the transaction at the same time
    • This might be an unpaid sales invoice (SI) or purchase invoice (PI), or an unpaid sales credit (SC) or purchase credit (PC)
    • Allocating a transaction to an invoice will create a sales receipt (SR) or purchase payment (PP) journal
      mceclip1.png
  • Create a new bank transaction
    • This enters a bank transfer (BT), bank receipt (BR) or bank payment (BP)
      mceclip2.png

When an imported transaction is selected, any existing invoices and payments of the same value are displayed as potential matches.

A relevance score is provided to sort transactions and find the best fit based on date and the reference details.

If the correct transaction is not found in Brightpearl, then a new bank transaction can be entered.

bank match allocate create

The following types of transaction can be matched:

Type Description What matching a bank transaction does
SI Sales invoices Creates a SR journal and marks it as bank matched.
Marks the invoice as paid.
SR Sales receipts Marks the SR journal as bank matched.
PI Purchase invoices Creates a PP journal and marks it as bank matched.
Marks the invoice as paid.
PP Purchase payments Marks the PP journal as bank matched
BT Bank transfers Marks an existing BT journal as bank matched.
Or, creates a new BT journal and marks it as bank matched.
BR Bank receipts Marks an existing BR journal as bank matched.
Or, creates a new BR journal and marks it as bank matched.
BP Bank payments Marks an existing BP journal as bank matched.
Or, creates a new BP journal and marks it as bank matched.

Note: JJ type journals will not show within the bank matching routine.

Matching payments and receipts

If a payment (in or out) has cleared your bank account and a matching transaction exists in Brightpearl, a Match button will be displayed.

The accounting entries have already been created, so matching them together only flags the existing transaction and locks it from future editing.

mceclip0.png

Matching invoices and credits

If a payment (in or out) has cleared your bank account and a matching invoice (sales or purchase) exists, an Allocate Receipt or Payment button will be given.

The accounting entries for the payment have not yet been created (the invoice is currently unpaid), so by clicking the Allocate Receipt or Payment  button you can generate the payment in Brightpearl against the invoice and match the items in one go. This makes receipting lots of payments directly into your account really quick!

mceclip2.png

No matches

If no match is found it could be one of three things:

  • The transaction amount is different

    Investigate the differences. Process the payment outside of the matching process, making the relevant adjustments for any differences. Note that the matching functionality expects a one-to-one match - for example, a payment of $100 is expected to match to a payment of $100, not two payments of $50.

  • There is no related transaction in Brightpearl

    Enter a bank transfer, bank payment or bank receipt using the Bank transaction tab.

  • The payment is for multiple invoices

    Search for the contact or invoice references using the Invoice allocation tab.

mceclip3.png

Enter a bank transaction

If there is no match for an imported transaction, you may need to enter a bank transaction. This could be a bank transfer between two Brightpearl accounts, a bank payment recording money being paid out, or a bank receipt showing income being paid in.

mceclip4.png

Clicking on a withdrawal will let you enter a transfer out or a bank payment, and clicking on a receipt will let you enter a transfer in or a bank receipt.

The fields are:

Setting Description
Create new Select the kind of transaction being created.
Account code Pick the account (nominal) code to post the transaction to.
Using tax code

Pick the tax code to be used for the transaction.

(Only for bank payments and receipts - transfers are always non-taxable.) 

Details/your reference Enter a reference to be used in the 'Details' box on the journal. This will come from the details imported with the file by default.
Channel, project, lead source

Select a channel, project or lead source to assign to any income statement codes being posted to.

(Only for bank payments and receipts.)

Net, tax, total Finalize the amounts to be posted.

Match multiple invoices

Where a single payment in or out of the account is the balance of multiple invoices, select the Invoice allocation tab and search for and match each relevant transaction.

You can continue searching and matching until they are all selected. When there is zero left to allocate, click the Match selected button.

Note: It is only possible to match multiple transactions of the same type - so only sales invoices, only purchase invoices, only sales credits and only purchase credits. If a transaction is made up of an invoice and a credit, it will have to be entered outside of the bank matching process.

mceclip3.png

How to match multiple invoices to a single imported payment

  1. Within the bank matching routine, select the imported bank transaction on the left-hand side.
  2. Select the Invoice allocation tab, displayed below any results shown on the right-hand side.
  3. Search for invoices by customer, supplier, amount or invoice reference.

    bank match multiple invoices

  4. Select the relevant invoice(s).
  5. If needed, search again to find more invoices - the previous selections will be retained.
  6. Once all invoices have been selected, the balance left to allocate must equal zero.
  7. Click Pay these invoices to match and create the journal for the transaction.

Un-matching bank transactions

Matched imported bank transactions can be un-matched from Brightpearl transactions. Once un-matched, the important bank transaction can be deleted or matched to something else, and the un-matched Brightpearl accounting journal can be edited (as long as it is not bank or VAT reconciled).

Making a correction during the matching process can be done by filtering the list of imported transactions to those already matched.

When searching for a transaction matched some time ago, or un-matching multiple transactions the bank activity report provides additional filtering options and a fast un-matching method.

How to un-match an imported transaction within the matching process

  1. Go to Accounting > Import transactions and select the bank account.
  2. Use the filters to display already matched imported transactions.
  3. Find the transaction and click Un-match.

    bank match unmatch

How to un-match an imported transaction from the Bank Activity report

  1. Go to Accounting > List bank accounts.
  2. Select Activity Report for the relevant bank account.
  3. Use the Filters to help you find the transactions.
  4. In the Matched column you will see a green check mark indicating the item is matched. Click on the green check mark to un-match the item. A cross indicates the transaction is no longer matched.

    bank activity unmatch

FAQs

Why isn't the transaction being found?

Only transactions of the exact amount posted to the selected bank account will be listed as matches. Ensure that the journal values are exactly the same as on the bank statement.

The bank matching routine won't include any transactions created with a journal type of JJ.

Can I allocate multiple payments to a single invoice using the matching functionality?

This is not currently possible - you will need to create the payments in Brightpearl outside of the matching functionality, then match your imported transaction to those payments instead of the invoice.

Have more questions? Submit a request