http://www.baystateconsulting.com

How to import bank statements into QuickBooks

New in Transaction Pro Importer 5.0!  You can now can import bank statements from excel, csv, text, Quicken (.qif) or Microsoft Money (.ofx) file into QuickBooks.  To make this process even easier Transaction Pro Importer includes a powerful feature called payee alias matching.  Most times financial institutions use different names that those in your QuickBooks company file nor do they provide the expense or income accounts that you want to use in QuickBooks.  The Payee Alias feature is available on the following import types:  bank statements, bills, checks and credit card charges.

In this post we will provide an overview of how to import bank statements into QuickBooks using Transaction Pro Importer.  On the first screen of Transaction Pro you will select the browse button to select your import file.  I have downloaded a  Quicken file (qif) from a bank as an example and have selected the browse button to select the file.   We will also select an import type of bank statement.  Then select the next button to advance to the next screen.

On the file contents screen you will see the data in your import file in a grid format.  Select the next button.

The next screen in Transaction Pro Importer is the mapping screen.  The required QuickBooks fields are indicated in red.  Please note the following below:

  1. We are not selecting an import file column for the the QuickBooks Fields, Account, as this Quicken file does not include the account and we will create the accounts during the Payee Alias matching.   You can easily select the import file column that you want to have matched to the QuickBooks Fields, by putting your cursor in the middle column, Import File Columns.
  2. As we want these bank transactions to post directly to the QuickBooks account, Checking, we typed this value in under the Static Value/Formula column next to the QuickBooks field, Bank Account.

Select the next button to advance to the next screen.

You will get an error that some required fields are missing.   This is because the Quicken file did not have the QuickBooks accounts.  We will select the accounts during the Payee Alias step which is next.

On the next screen select the Aliases button and this is where we will match the name in the Quicken file to the names in QuickBooks and assign accounts.

Now we will walk though how to set up an Alias for the first line in this import file.  We will need to include certain key characters contained in the the Payee Column in the bottom half of the screen.

The first step is to select certain key characters from the payee column at the bottom of the Alias screen.  In this case we are going to use “Bank of Amer.”  Once you have determined the Keywords the next step is to select a name.  In this example the vendor, Bank of America, does not exist in this QuickBooks company file so I am going to use Transaction Pro to create it.  First I will select <Add new> from the Payee drop down box.  In the Add New Payee box I am going to select Vendor and then type in the name of the vendor that I want to create and then select OK.  Transaction Pro will create this new vendor in the QuickBooks company file.

Then the next step is to select an account to be associated with the keywords box.  In this example I am going to choose an already existing account by using the drop down under the Account field and I am going to select Bad Debt and then select the Add/Update button.  You also have the option to create a new account if you want we just have elected not to show that step here.

After selecting the Add/Update button you will see that the bottom part of the screen that has the grid titled, “Alias Definitions Applied to Import File,” now has the name and account columns completed.  Review this data for accuracy and if everything looks correct then select the Apply button.  This mapping will be saved your next import.

This next screen shows how the data will be mapped to your QuickBooks fields.  The QuickBooks fields are listed at the top of the grid and the data follows in rows immediately below.  Review this data to make sure that data is mapped correctly.  Select the next button.

On the last screen of Transaction Pro you select how any new items that do not exist in your QuickBooks company file will be set up by making selections from the drop down menus.  Please note this last screen needs to be completed even if you are not mapping data to the items item field.  Select the finish button to start the import.

The Payee Alias function has more features.  We will publish another article on how to use these features in another blog post.

You can read more about Transaction Pro Importer for QuickBooks and also download a free demo to try this out here.

Comments

  1. Does the importer have the ability to see if there are duplicate transactions already in QB before importing so there is no double entry of transactions?

    • For most transaction types we have an option to check for duplicate transactions. However, this is not available on a bank statement import. If you want to check for duplicates I would do two imports one for checks and one for deposits and then that way you can utilize the option to check for duplicates.

  2. I really like this feature you added last year. Would love to have a similar one for credit card statements that would post credit card charges and credit card credits. Thanks!

  3. websolprov says:

    This feature is poorly thought out as it gives no option to deal with blank payees, also where you are already imported the account as part of the file but the payee is blank it gives you NO option to add a row without having first specified the account number. It is indeed possible to enter transactions into QB without a payee so why aren’t you doing the same? There is a LOT of improvement needed to TPI nowadays.

  4. websolprov says:

    I wouldn’t bother with the Bank Statement import, it really is so inflexible: insists that Payees are less than 31 characters even though QB allows larger, also insists that ALL names imported are payees and sticks two fingers up if theyre not. Doesn’t allow you to deal with multicurrency banks, I am totally disillusioned with this now!

  5. websolprov says:

    Also when using payee aliasing, doesn’t allow you to just add exceptions, i.e. limited few that need aliasing, it expects you to add the whole damn payee list!

  6. websolprov says:

    And it also appears the same is try of nominal codes (particularly where they are subaccounts and hence combined using colon join), I can import my larger length nominals perfectly well when importing journals, but when used for bank statements, the TPI rejects them. This is unacceptable!

  7. Can you use this outside US also? Or only for US banks?

  8. Chris Ragain says:

    This is a great tool that can be paired with our software Ledgersync (www.ledgersync.com). Ledgersync allows accountants to download their clients bank transactions, check images, and bank statements directly to a .csv file. Then you can use TPI to move them into QuickBooks. Simple…

Any feedback? Please let us know!

Over ten million transactions have been imported into QuickBooks Online.  Sign up for a free trial here.
Save $50 on a Transaction Pro Importer and Exporter bundle here.
http://www.baystateconsulting.com