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.
Importing bank transactions/statement
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:
- 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 breakout individual details such as contact name and invoice reference
- Deposits and payments 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 Separate 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
- 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
- Save your file as a CSV.
- Go to Accounting > Import Transactions and select the relevant bank account.
- Click the Import transactions link.
- Click Browse and select your CSV file.
- Click Upload .
The statement lines are displayed on the left of the screen ready to be matched.
Matching imported bank transactions
Imported bank transactions / statements 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)
- Allocate it to an existing invoice and create the transaction at the same time. This might be an unpaid sales invoice (SI) or purchase invoice (PI)
- Create a new bank transaction. This enters a bank transfer (BT), bank receipt (BR) or bank payment (BP)
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 to match with then a new bank transaction can be entered.
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.
JJ type journals will not show within the bank matching routine.
How to match imported statement items
To begin matching, select an imported statement item from the left hand panel. Matching transactions are listed on the right panel.
A matching payment
If a payment (in or out) has cleared your bank account and a matching transaction exists in Brightpearl, a Match button will be given. The accounting entries have already been created so no further action is needed except to match them together so you can see in the journal the item has cleared your account.
A matching invoice
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 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!
If no match is found it could be one of three things:
- The transaction amount is different
Investigate the differences. Process the payment out side of the matching process making the relevant adjustments for any differences.
- There is no relating transaction in Brightpearl
Enter a 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.
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 that it is only possible to multi-match sales invoices and purchase invoices.
How to match multiple invoices to a single imported payment
- Within the bank matching routine, select the imported bank transaction on the left-hand side.
- Select the Invoice Allocation tab below any results shown on the right-hand side.
- Search for invoices by customer, supplier, amount or invoice reference.
- Select the relevant invoice(s).
- If needed, search again to find more invoices - the previous selections will be retained.
- Once all invoices have been selected the balance left to allocate must equal zero.
- 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
- Go to Accounting > Import transactions and select the bank account.
- Use the filters to display already matched imported transactions.
- Find the transaction and click Un-match .
How to un-match an imported transaction from the Bank Activity report
- Go to Accounting > List bank accounts .
- Select Activity Report for the relevant bank account.
- Use the Filters to help you find the transactions.
- 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.
Manually flag a journal as bank matched
If an accounting entry is made to the bank account in Brightpearl for which there won't be a real bank deposit or withdrawal it will never appear on a statement and will never be bank matched or reconciled. These transactions might occur due to opening balances or adjustments made by your accountant or simply error postings which are later then reversed with another journal. It is possible to manually flag these accounting entries as "matched" so that they no longer show up in un-matched transaction results and won't show up in the bank matching routine.
How to hide un-matched journals from bank matching
- Go to Accounting > Activity report and select the relevant bank account.
- Locate the original transaction and click the red cross in the Matched column. The icon will change to a blue exclamation mark to indicate it is "void" and no longer needs to be matched. Voided entries will not show up in the bank matching routine.
FAQS - Bank matching & importing bank transactions
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.