Quickbooks Online Banking: Setting Up, Deleting Matched Transactions and Manually Uploading Web Connect or Excel Files
Using online banking can be real time saver for businesses with numerous bank transactions. It is primarily a tool to help you reconcile your bank accounts by matching the transactions that have already been entered in QuickBooks. It helps to avoid the painstaking process of checking each transaction against your bank statement and the maddening frustration when you something goes wrong and you have to redo it.
Setting Up Online Banking:
QuickBooks makes it quite easy to set up your online banking. You need to go to Banking/Online Banking/Set up Account for Online Services and follow the instructions. It is important to make sure that your bank supports export into the correct QuickBooks (OFX) format and that you have all the information so that this process is automated. If you prefer to import your bank transactions manually, please see below.
Manual Download of Files:
Many banks allow you to export your transactions into a web connect file which you can then download manually into QuickBooks as follows:
If the account that you are downloading the data for is already set up for online banking it is very important to disable this. You can do this by going to the chart of accounts, edit account and disabling the online banking function. Once this is done follow the steps below.
Go to File/Utilities/Import
Select Web Connect File
Select the account that you want matched.
Go to online banking center to see “Items Received from Financial Institution”
Go to “review statement” to match transactions
Manual Download of Excel Files:
Some banks, like HSBC Canada, do not have web connect file functionality. However, it is still possible to convert excel files to the appropriate OFX format using software (or if you have programming skills, without software). I use QIF to OFX creator from Big Red Consulting to do this. You will have to pay approximately $59 to use the software, which is worth it if you have alot of transactions to reconcile. The instructions are fairly straightforward and really only require that you include the date and amount of the transaction in the excel file.
Delete Matching of Online Bank Account Transactions:
There are occasions when you are either learning how to use the online banking function or your trigger happy download finger performs the action twice or for other reasons, you want to unmatch the transactions. Unmatching each one individually is time consuming (and irksome) and defeats the purpose of using this time saving functionality in the first place. As I was searching for a solution to this problem I came across this incredibly easy fix:
After you have incorrectly matched transactions in the online banking module and decide that you want to start over:
Go to the Banking/Reconcile
Select the bank/credit card account that you want to match. Enter reconciliation details as normal.
Select “continue” to go to the details
In the details page, next to mark all, unmark all etc. you will notice a “matched” button (only if you have used online banking to download and match your transactions). Select this and it will bring up all the matched transactions.
Click on unmark all
Voila – all your online transactions are no longer matched.
You can then proceed to make the necessary corrections, go back to online banking and rematch the transactions.
I highly recommend the use of online banking (automated or manual) for anyone who has more than a few banking transactions each month. It greatly facilitates the reconciliation, reduces frustration and frees up your time to provide more value in more important areas.
