On the surface, importing bank transactions into QuickBooks Online seems easy. For many users, it’s as simple as signing into your bank or credit card accounts through QuickBooks Online. But sometimes limitations and challenges can arise when importing transactions into QuickBooks Online.
To help you get the most from your business accounting software, this article will familiarize you with the QuickBooks Online Banking feature, and provide in-depth instructions for using it to import your bank transactions.
QuickBooks Online Banking (bank feed) feature
Bank transactions are imported into QuickBooks Online using the Banking (or bank feed) feature. This feature serves two distinct purposes: reconciliation and facilitating data entry.
The Banking feature provides a way to reconcile your business’s bookkeeping to your bank’s records, almost in real-time. There is often a 24-hour lag in updates, depending on how your bank communicates with QuickBooks Online. Even with this lag, using the Banking feature to reconcile daily — or every few days — is still quicker than the traditional reconciliation method where you wait for your bank or credit card statement to arrive in the mail. This quicker reconciliation gives you an opportunity to detect certain problems — like an unexpected overdraft or potential fraud — much sooner than waiting until the end of the month when your bank or credit card statements arrive.
Keep in mind, even though the Banking feature gives you the ability to compare your bookkeeping with your bank’s records daily, this quick reconciliation does not replace the need to do the traditional monthly reconciliation of your accounts against the statements prepared by your financial institutions. Even if your bank balance and QuickBooks amounts match when you are done accepting or matching transactions, it’s still important to conduct a complete reconciliation of your accounts each month.
Many QuickBooks Online users rely solely on the Banking feature to do their bookkeeping. This can result in significant errors in your books.
Using the Banking feature to import bank transactions into QuickBooks Online is a way to expedite your bookkeeping by eliminating some data entry, especially for recurring automatic transactions. However, keep in mind this is a reconciliation feature meant to compare your books with your financial institution’s records.
Certain transactions — like checks you’ve written, payments you’ve received from customers and asset purchases — should always be entered into your accounting records when they occur and then “matched” to the transaction in the bank feed when the transaction clears the bank. This will prevent mistakes in your bookkeeping and — especially in the case of checks that haven’t cleared the bank yet — will help you keep tabs on your true available cash balance.
This doesn’t mean you have to go back to manual data entry for every transaction, though. There are a plethora of tools — like Receipt Bank and Bill.com — that connect to and push these “lagging” transactions into QuickBooks Online for you.
The takeaway from this section: Use the Banking feature to help you with some data entry, but resist the temptation to rely solely on imported bank transactions to do your bookkeeping.
How to import bank transactions into QuickBooks Online
Now let’s get down to the business of importing your bank transactions into QuickBooks Online. There are two ways to do this:
Connect directly to your financial institution
Import from Excel
Following is a step-by-step guide for each method.
Connect to your financial institution
Connecting your bank and credit card accounts in QuickBooks Online is the quickest and most reliable way to import transactions into the Banking feature. It’s also secure; your bank login information is encrypted and protected, meaning no one can access your bank accounts, even if they have access to your QuickBooks Online subscription.
1. From the Dashboard, click on Banking.
2. Click the Add Account button.
3. Enter your financial institution’s name or the URL where you sign into your account. When you start typing the name of your financial institution, a list of options will appear in a drop-down box and you can select the correct option from it.
Alternatively, you can click on the logo of one of the popular banks shown on this screen.
4. Enter the required information and click Continue.
5. From here, you will get a list of accounts to connect. The current balance in each account will be shown (it’s been redacted this on the screenshot for privacy). Select the accounts you want to use to import transactions into QuickBooks Online. If you have your personal accounts and your business accounts at the same financial institution, make sure to only connect your business accounts. This will keep your business and personal finances separate, which is crucial to good accounting.
As you select each account, you will be prompted to enter the account type from a drop-down menu. Map each account you are importing transactions from to the corresponding bank account you’ve currently set up in your chart of accounts. You can also add the bank account on the fly by clicking on “add new” at the top of the drop-down list.
Finally, select the start date you want to use to pull transactions. Note that your bank might restrict how far back you can import transactions.
Once you have selected the account(s) you want to connect, click Connect.
6. The bank transaction import will begin. This can be virtually instantaneous, or it can take several minutes, depending on how many transactions there are to import. Once completed, you will be returned to the Banking screen, where you can start matching or adding the transactions that have been imported.
7. As you’re adding or matching your transactions, you can create rules in order to expedite this process moving forward. Rules are especially helpful for recurring automatic transactions you might not have receipts for, like bank fees and subscriptions. Click into a transaction — in this case, we chose the “12/27/19 Account Maintenance” transaction — and then click “Create rule from this transaction.”
Now you can set the conditions you want to use to apply the rule. We recommend using the “Auto-categorize and manually review” option so you still see the transaction in your bank feed, but it’s automatically categorized based on the rule. This helps prevent misclassification errors and duplicate entries. When you are done setting the conditions, click Save to apply the rule and return to the Banking screen.
8. After the initial connection to your financial institution, your bank transactions will usually be imported automatically every day. Occasionally, though, you might have to update the connection.
Start by clicking on the Update button. Give the connections a few minutes to refresh and see if new transactions are brought in automatically.
Sometimes, the bank feed will need to be reconnected. This typically happens when you change your login information with your financial institution. If this happens, click the pencil icon next to the account you need to reconnect.
Then select “Edit sign-in info.”
Rarely, you might have to disconnect the account from Banking altogether and then reconnect it. Before you proceed with the next step, match or accept any transactions currently waiting for you in the bank feed. Otherwise, they will be deleted when you disconnect the account.
Click on “Edit account info.”
Then check the box next to “Disconnect this account on save” and click Save and Close.
Now follow steps one through seven above to reconnect the account.
Import transactions into QuickBooks from Excel
Sometimes, you might have to manually import bank transactions into QuickBooks Online. This can happen when the start date for your import is earlier than the date from which QuickBooks Online can pull transactions or — more rarely — if your financial institution does not interface with QuickBooks Online.
1. Log into your bank or credit card account directly and find the option to download or export transactions. You must export transactions in CSV, QFX, QuickBooks Online or OFX format. If your bank doesn’t export to one of these formats, look for an option to export to Excel. You can easily convert an Excel file to CSV format and use that for your import.
Every financial institution is different, and sometimes it’s not easy to find the option to export your transactions. You might have to hunt around a bit – or contact your financial institution’s customer service department – to find the export option.
Many banks limit how far back you can export transactions. If you need to export transactions from further back than your bank allows, see if you can get PDFs of your statements from the months you still need to import into QuickBooks Online. If you can’t export all the transactions you need from your bank but you can get PDFs of the statements for those months, MoneyThumb has a product that will convert data from PDFs into a file you can import into QuickBooks Online.
2. If you had to export your bank transactions into Excel instead of CSV, QFX, QuickBooks Online or OFX, you’ll need to save the file as a CSV file. To do that, open the file you downloaded from your bank, then:
Click on File.
Click on Save As.
Choose the location where you want to save the file. You can choose from the file list that appears, or you can click on Browse.
Change the File Name (if desired) and then change “Save as type” to CSV (Comma delimited).
3. Now you’re ready to import your transactions into QuickBooks Online. Go to the Banking screen as in Step 1 to connect to your financial institution, but this time you’ll click the down arrow next to the Update button and choose “File upload.”
4. Click on the Browse button, find the file you want to upload, then click Next.
5. Choose the account where you want to upload the transactions, then click Next. If the bank or credit card account where you are uploading the transactions doesn’t already exist in your chart of accounts, you can add it by clicking “Add new” at the top of the drop-down list.
6. Now you’ll need to map the columns in your CSV to QuickBooks. Take care here; incorrect mapping will import your transactions incorrectly.
You have an option to indicate that the first row in the CSV file is a header row (a). This will usually be the case but double-check your CSV to make sure there isn’t data that needs to be imported in Row 1. You will also need to indicate whether your credits and debits are in one column or two columns (b). If your debits and credits are in separate columns, you will need to map those columns separately (c).
When you are done mapping, click Next.
7. The next screen will give you a preview of the transactions you are about to import. The QuickBooks Online default is to select all transactions, and this is what we recommend unless you are certain there are duplicates in your CSV file. Make sure the data looks correct by comparing the data on this screen to the CSV you are using for the import, then click Next.
8. Now, you’ll see a pop-up box that tells you how many transactions will be imported. Click Yes if you are ready to complete the import. If not, click No. If you click No, you will have to start the import process again from Step 3 when you’re ready to proceed.
9. Once the import is complete, you’ll get a screen prompting you to accept your transactions. Click “Let’s go!” to be taken to the Banking screen to start matching or adding your transactions.
Considerations when importing transactions into QuickBooks Online
It’s easy to import transactions into QuickBooks Online, but there are a few things you need to be aware of before you proceed.
Overwriting existing banking transactions
If transactions exist in the Banking screen, you must accept those before importing new transactions using the manual (from Excel) import feature. Otherwise, the existing transactions will be overwritten in the import, and you will have to do another manual import to replace them so you can accept or match them.
Whenever you import bank transactions into QuickBooks Online, there is a chance you could inadvertently duplicate some transactions. If you discover you have imported duplicate transactions, you can easily exclude the duplicates from your bank feed.
Select the duplicate transactions, then click the down arrow next to the “Batch actions” button.
Now, click on “Exclude selected.”
The selected transactions will be removed from your Banking screen. If you later discover they were not duplicate transactions, you can restore them by clicking on the Excluded tab, selecting the transactions you want to restore, and then using the “Batch actions” button to Undo the exclusion.
Opening balance equity
When you import your transactions by connecting to your financial institution, QuickBooks Online will bring in the balance on the account as of the first date of your import by making an entry to an account called Opening Balance Equity. If you don’t have any data in QuickBooks Online for the bank or credit card prior to the import, then this entry will have no impact on your reconciliations. In fact, it will help make your first reconciliation easier.
However, if you have been manually entering or importing your transactions and then you later connect to your financial institution, the Opening Balance Equity entry will impact your next bank reconciliation.
If you have difficulty with your first reconciliation after connecting to your financial institution, go to your chart of accounts and search for the Opening Balance Equity account. Click on “View register” to see the transactions in the account.
Find the opening balance entry for the account you are attempting to reconcile, then “unreconcile” that transaction by clicking on the “R” until that field is blank.
Now, return to your reconciliation and see if you are able to reconcile. If you can now reconcile the account, you can delete the Opening Balance Equity entry for the account. If you still have trouble reconciling, though, reach out to your bookkeeper or accountant for help.
A version of this article was first published on Fundera, a subsidiary of NerdWallet