Bank Reconciliation with QBO File Import

Modified on Wed, Feb 25 at 9:30 AM

Note: This feature requires Connected 12.0 or greater.

The Connected Bank Reconciliation can be made faster and more efficient by importing a QBO file for transaction matching. Accountants and/or bookkeepers can quickly and accurately match imported bank transactions to the corresponding Connected transaction.

What Does Importing a QBO File Do?

It saves time by helping to match transactions from a bank statement automatically with ones entered in Connected
Helps to ensure accuracy when reviewing bank transactions
Easily identifies transactions not yet entered. For example, bank charges.
Isolates transactions are "matched" or "unmatched" so the focus is narrowed to unreconciled items.
Creates a great audit trail with the ability to attach the actual Bank Statement PDF file and/or the QBO file used. Files are easily attached within the Connected Notes. Reference: Attaching Files within Notes

What Doesn't Importing a QBO File Do?

It will not create new transactions, like bank fees or customer payments.
It doesn't happen automatically, a user initiated file selection, downloaded from the bank is required.

TABLE OF CONTENTS

Getting Started – Adjust Payment Receipt Groupings
Using a .QBO File to Match Bank Transactions
Tips and tricks

Please Note: This article assumes that Bank Reconciliation is enabled and being actively used in your Connected company data file.

Getting Started – Adjust Payment Receipt Groupings

On a Bank Statement, checks and cash deposits are normally grouped together along with incoming credit card transactions, however ACH/EFT deposit transactions will often appear as separate entries.

Users can control what Receipt Types are grouped in Connected, to align with how transactions appear on a Bank Statement or qbo file export.

There are two areas of setup in Connected that need to be verified and/or changed prior to importing bank transactions to ensure the most accurate and easy matching process possible.

Step 1 – Turn Off “Group Receipts” Setting for Bank Reconciliation

First, to allow for the Bank Reconciliation window to display user specified Receipt Type groupings, the following setting needs to be unchecked:

Go to File -> Company Setup -> G/L Defaults
Verify that the Group Receipts on Bank Reconciliation check box is unchecked

Step 2 - Set Up Custom Grouping for Individual Receipt Types

This step allows users to set up specific groupings of Receipt Types that align with how their bank statement presents them.

Go to A/R -> Setup -> Payment Methods.
Select the payment type to be grouped
Select Modify at the top of the window

NOTE: When modifying the Payment Methods, ensure that you answer “No” to the following prompt

Select the “Group on Bank Reconciliation” checkbox.

Save the changes
All transactions with the same method and the same date will be totaled together on the bank reconciliation window.

Using a .QBO File to Match Bank Transactions

Downloading a .QBO File

Most banks have a .QBO export format of transactions available for users. This format can be imported into Connected to assist in matching against existing transactions. It usually is an option in a dropdown menu on a bank website when logged in.

Download a .QBO formatted file from your bank website for the account being reconciled.
Save this file to an accessible location.

How to Start Using a .QBO File

Open the Bank Reconciliation window in Connected
Select a bank code and period to load the transactions into the window.
From the Actions menu, select “Match QBO Transactions file” and navigate and open the downloaded QBO formatted file.

The QBO file is read by Connected and an “auto-match” process occurs.
The bank reconciliation window alters to display the QBO-specific controls and an OK message informs the user of the matching results.

How Does Auto-Match work?

Auto-Match is defined as:

Exact - Matches on Check Number and amount OR exact matches on date and amount (but no other transactions having the same date or amount)

Likely - Matches must have dates within +/- three days and exact amount (but with no other transactions having the same date and amount)

Matching is always performed with the reconciliation transactions in their default sort order: date, source, payment method, description, debit amount, credit amount (all ascending values) .

Any transaction that has an exact match will be indicated in the Match column. Tabs across the top of that window section will list, All, Matched and Unmatched transactions.

If a transaction is selected from either the top or bottom window, an Exact Match will be indicated in both windows, highlighted in the top window and an indicator on the left side of the screen in the bottom window. The matching information will appear under the description of the transactions in the lower window.

A Likely Match will be indicated as shown below:

How to Adjust an Incorrect Match

If this match is not correct, it can be unmatched by clicking on the trash can icon in the lower right part of the Description box.

Connected will then provide the following prompt:

Answer Yes to remove the match and No to cancel.

NOTE: Once a match (of any kind) has been removed it will not be matched again until the Bank reconciliation window is closed and the QBO file is reloaded.

How to Use the Match All Possible Toggle

If the Match All Possible checkbox is selected, a message will appear indicating how many transactions were “possible” matches. A possible match is the first match found with an exact amount, dates are not compared and other transactions with the same amount are ignored. They will be indicated with the word “possible” in the bank reconciliation window.

Manual Matching

Some matches need to be made manually. This can be accomplished by selecting a transaction in the upper window and dragging it on top of a transaction in the lower window.

The manually matched transactions will appear as “Drop Match”.

NOTE: If there is already a possible match for a transaction, a manual match cannot be made until it is removed.

How to Save Cleared Matched Transactions

Using the matches from the QBO file, it is possible to mark as cleared all transaction that are matched. If the bank reconciliation is saved and not all transactions that have been matched have been marked as cleared the following message will appear.

Using Notes to Attach a Bank Statement or .qbo File

You can use the Notes feature in Connected to attach documents such as a PDF of a bank statement right to the Bank Reconciliation window.

Read How Here -=> Using and Linking (Attaching) Files to Notes

Tips and tricks

Do the Drop matches last. If you do a drop match and then you need to exit out of the bank reconciliation window to add another entry, any drop matches previously made will be undone and will need to be redone the next time the bank reconciliation is loaded.
If there are 2 transactions that are recorded in Connected that represents a combined amount from the bank statement (example: a receipt that covers 2 customer codes in Connected), you can match the bank transaction to 1 of the amount. Connected will provide a warning that the amounts do not match, and then you can match the remaining difference to the other transaction.
Voided payments and receipts from the bank feed will not be matched.
Transaction amounts that repeat may cause improper matching especially if the dates are also the same. Review the detailed section of the bank reconciliation (where you clear the item) and ensure that the exact match is actually exact. For example you may have the same amount received from different customers on the same day. Connected may “match” them incorrectly, different customer, same amount and call it an “exact match”.
Try to record the transactions the same way they appear on the bank statement, especially the amounts. While it is more work to add in multiple transactions for various bank charge amounts, because they appear that way on the bank statement it will make the matching process more efficient.
Descriptions are not as important as recording the same value on the right date as the QBO download.
A monitor in portrait mode really helps if there are a lot of entries through the bank account.