How to Import Bank Transactions Into QuickBooks: Excel & More

Cody Cromwell
Written by
Last update:

The Importance of Connecting Your Bank Account to QuickBooks

After you set up your QuickBooks company file, you’ll want to set up your bank account information. You can import bank transactions from your bank account records into the Account List area of your QuickBooks company file. If you’ve separated your personal and business accounts, you can also import your business transactions from your personal accounts into the Account List as well.

Before you go any further, you’ll need to make sure that your bank is set up to allow online banking connection with your QuickBooks company file. If your bank isn’t set up to allow this, you’ll need to set up an Online Banking Connectivity Account on your bank’s website to allow this.

Now… the QuickBooks Process

Launch QuickBooks … with your QuickBooks company file open, click on … Add/Edit a bank account and select your bank.

On the next screen, it will give you an opportunity to set up your bank account … which you’ll need to do to proceed.

You’ll also need to provide some information about your bank account open which the company file needs to connect to your bank account.

How to Import Banking Transactions Automatically in QuickBooks Online

Banking information is one of the most important yet tedious data to enter in QuickBooks. If you’re not comfortable with the task it can be a privacy leak and an open invitation to identity theft.

Now QuickBooks can import banking transactions automatically. It’s a feature you’ll be able to use if you have QuickBooks online. This would mean no manual entry. Rather, all the required data will be pulled by QuickBooks online straight from your banking providers!

That’s the good news! If you’re not happy with this feature, check out the managing bank transactions section in QuickBooks. See if the manual line item method is possible for you.

Step 1: Locate Your Bank

Scanning your bank statements might seem like a laborious task, but you’d be amazed at how easy it is to do with the help of a few free download tools and programs. Find out how to find your bank statements in three easy steps.

To get started, you’ll need to set up a secure folder to hold your bank statements. For that, visit Microsoft’s website. Microsoft’s free File Explorer will help you create a secure folder from any Windows computer and you can sync it up with your accounts. The instructions can be found in this Microsoft article.

Now, you need to find a scanner that supports scanning into PDFs. Check out these scanners on Amazon.

Here are the tools you’ll need to scan your statements:

I generally prefer the High Quality setting with this scanner.

Scanning Paper into a PDF

Scanning your paper statements in PDF is the easiest and safest way to archive your bank statements. You can keep them all in one file and easily find it whenever you need them. Paste them into your secure folder and you’re all set.

However, this method has one major disadvantage – you won’t be able to search and sort your bank statements in Excel.

Step 2: Log Into Your Bank Account

Online.

Once you’ve setup your bank‒s online banking, login into your bank account online. Be sure to log in with your bank‒s secure online banking URL, found on your bank‒s website under "Accessing Your Account Information".

It may take you several attempts to login before you finally get in, so be patient. Once you’ve logged in, check that your actual transaction data mirrors the source information on your spreadsheet file. If you’re importing excel transactions, check that the titles of the transactions match the source of the data. For example, check that client‒s account names are what they’re listed as in QuickBooks, and that the account numbers from the transactions match what you’ve entered.

Once you’re satisfied that the two match, proceed to step three.

Step 3: Link Bank Accounts to Chart of Accounts

Step 4: Adjust Dates for Actual Transactions to reflect individual accounts

Step 5: Source transactions from all your bank or credit cards accounts over the month and include them in your P&L

Step 6: Create a P&L (profit & loss)

Explanation of the Chart of Accounts.

Step 1: Open QuickBooks

Step 2: Click on the QuickBooks Menu icon

Step 3: Choose Add/Edit

Step 4: Click on Chart of Accounts: Press the line to the left: C/O HEADING

Step 5: Press Add: Select –Other–

Step 6: Click on –Account Name–

Step 7: Press Add: –Assets–

Step 8: Press –Other–

Step 9: Press: –Deposits–

Step 10: Press –Account Number–

Step 11: Press –Assets–

Step 12: Repeat Steps 8-11 for –Liabilities– and –Owner–

Step 4: Selecting the Right Date Range

Once you’ve done this, go back to the dialog you opened and select the 'Selected Date' column. After doing so, you will need to make the appropriate corrections. The important thing to understand here is that with bank transactions, dates are recorded in the following format: DD/MM/YY. You need to ensure that the 'Selected Date' column matches the corresponding DD/MM/YY format. If it does not, make the appropriate changes. Usually, you will need to change 'DD' to 'D' and 'MM' to 'M', as these are the first two columns that are available. But be cautious that the last two digits must not be obscured by adjusting these two columns. As the example below, you can see that we have to change the first two columns to the date format '01/12', and then apply a number format to the third column to ensure that the decimal point is visible.

Now that you have finished, the remaining steps are very similar. Make the appropriate changes and click 'Create Transaction'.

You can use this same method to import bank transactions for Payroll, Employees, Vendors, etc.

Step 5: Review Downloaded Transactions in the Banking Center

Once you’ve confirmed that the downloaded transactions were successful, you’ll want to double-check that the downloaded transactions have been properly imported into your system. Below we will go through the steps used to confirm and review downloaded transactions in the Banking Center.

Before we jump into Step 5, it’s important to note that QuickBooks does not allow you to stop or re-process transactions after they’ve been imported. This is to prevent errors and potential mistakes in the import process.

However, errors and mistakes can, and most likely will, occur during this process, so be sure to double-check at the end.

Step 5: Review Downloaded Transactions in the Banking Center

When you sign in to your Banking Center, you can access transactions marked as downloaded by the bank and those that you’ve previously downloaded.

Simply click on the Action menu and select Look at the History. This will launch a window containing all the transactions that match your criteria, much like a list of records.

The Transaction Type column contains multiple options for selecting the type of transactions to review. In this recipe, we’ll select Downloaded Internal transactions. Click the Import icon to the far right, and the Transaction Import window will open.

When to Manually Import Transactions Into QuickBooks Online Using a CSV File

The better idea is to use Internet banking to transfer funds from your bank account to your business checking account so that you can enter the transactions more quickly and easily.

If you need to import transactions while you’re on the road, then you can use a CSV file to export the transactions from your Internet banking. You’ll then import those transactions into QuickBooks using the Import/Export function.

To export the transactions from the Internet banking, you’ll need to go to the –Transactions” list, and select the appropriate bank account. On the right-hand side of the page, you’ll find a drop down list. Select the transactions that you want to export from your Internet banking, and save the CSV file to your computer.

Next, you’ll need to input the QuickBooks Company Setup information. To do that, you’ll need to go to the –Accounting” tab in the bottom left-hand corner of the QuickBooks window. Next, click the top right-hand icon to make the drop-down list. Then select the –No QuickPayment Account” tab.

How to Manually Import Transactions Into QuickBooks Online With a CSV or Similar File

If you’re importing bank transactions (sometimes referred to as bank statements) into QuickBooks Online via the Web Connect plug-in, even if you navigate to the correct bank account register and/or bank account list, the QuickBooks Online import wizard will fail to locate the statements.

The problem here is that there are some 3rd party services that automatically download transactions from your online bank. Since the QuickBooks Online import wizard is scripted to search only for data within QuickBooks Online, it fails to recognize the downloaded transactions.

Using a 3rd party service for online bank management is not unusual. Many people set up online banking for mortgage payments, for example, and simply download the statements to a spreadsheet on a monthly basis.

However, if run a manual import, you’ll have to create the transaction item manually and then navigate to the correct QuickBooks register to record the transactions.

If you want to quickly import transactions into QuickBooks Online, below are some options for automating the process.

Step 1: Download a Transfer File from Your Bank

Your bank may not provide any information on how to perform a ACH transfer. If that’s the case, you should contact your bank and ask for a transfer file. You’ll need to print off the information and it’ll have to be emailed to you while it’s still in a PDF format.

If your bank provides the information and a transfer file, you can get that file by using a few different methods. Be sure to keep good records of where you sent the files to and with whom you exchanged emails with.

You can manually download the transfer file to your computer. If you’re using Excel for Mac or Excel for Windows, open the Transfers workbook and click on the download button next to the desired transfer file.

It’s important that you download the latest transfer file. If the file has been downloaded previously, it’ll have an older version number and you’ll need to download the latest information.

Your bank may provide the information in PDF format. If that’s the case, here’s a step by step process to import your information into QuickBooks.

Step 1: Open a blank Excel spreadsheet and select the data you want to import.

Step 2: Select the Transfer File to Upload in QuickBooks

Select File > New > Sales Receipt > Enter an Item Number and click the Ok buttonEnter 10, for example. Then in the Amount cell, enter 10. Press Tab twice to move to the next field and in the Issue Date cell enter 02/10/15. Then press Tab again twice to move to the next field and on 02/10/15, enter 2, which is the designated number for a digitized bank transaction. If you need to enter a bigger number, you’ll need to save the file in a different format using the Save Copy As option. If this option is greyed out, you’ll have to use an alternative spreadsheet.

After pressing the Export button, the transaction will then be available in QuickBooks. You’ll notice that it is a 26 digit transaction. The first 4 digits are your company identification number that will always stay the same. The next 24 are the original batch number assigned by your bank. The rest are the transaction number assigned by your bank.

The last check digit is assigned by QuickBooks, and it is the 13th digit. This check digit is used during the transaction verification process.

Step 3: Select a QuickBooks Account

Step 4: Map Columns to QuickBooks Fields (CSV and TXT Files Only)

There will always be some Excel cells that don’t correspond with QuickBooks fields. Next, you will need to map the columns to the corresponding QuickBooks fields. Then, you can import the CSV and TXT files.

Step 5: Select Transactions for Import (CSV and TXT files only)

The last step in the import process is to select the files with financial transactions to import. If your financial transactions data is stored in a text file or a comma-separated value (CSV) file, select Just Text Files under the Quicken Options heading.

If your financial transactions data is stored in a text file that’s saved with newline characters, select Text Files with Newline as the Convert Format .

If your financial transactions data is stored in a .QFX, .QIF, or .QBO file, select Text Files with Newline as the Convert Format.

If your financial transactions data is stored in other file types, such as image, PDF, or XLS files, select Other Financial Files under Convert Format .

Click Continue to proceed with the import process.

If you’re prompted to import just one file at a time, click Yes.

Then select the type of financial transaction list you’re importing. For this example, we’ll be importing a text file.

Click the Next button to select the file with financial transactions to import.

Step 6: Return to the Banking Center

Once you have all the connections in the banking center created, you’re ready to use each account’s transactions. It’s easier to make transactions with a balance in the center than to try andbalance the bank accounts after the transactions are made in the banking center.

When you make a transaction, Quickbooks gives you a balance that needs to be balanced, but due to rounding, the available balance and the balance shown in the center may be slightly different. This is another reason to enter the balance in the center before you make transactions. Of course if you have a large number of transactions, it’s more efficient to balance the bank account than to enter the same transactions multiple times in the banking center.

Quickbooks can also get confused about what’s a debit and what’s a credit. When you select debit, it’s a mistake Quickbooks makes about which transactions it thinks are debits. Therefore, you’ll get balances that say the account has a negative balance even though there are a few transactions that actually went to credit the account.

Wrap Up

As every entrepreneur knows, there are a lot of accounting and bookkeeping requirements involved in running a business. Running an online business is even more involved than running a brick-and-mortar business, requiring a lot of extra effort to keep up with all the documentation and record-keeping. What many people don’t realize is how to make the process easier, streamlining the documentation and storing it all in one place. Fortunately, there are some great online tools available to help you with all these tasks, and this article by Biz2Credit will take you through some of them.

This resource is packed with useful information and will leave you feeling organized and confident about your bookkeeping abilities.