Loading Transaction History into QuickBooks

You’ve decided to download transaction history from your bank and credit card companies.  How are you going to get that data into QuickBooks?  And how will you ensure each transaction ends up posted to the right account?

In this post, I want to offer several suggestions to help you decide how to load your data.  There are plenty of options, and some will work better in your situation than others.

Start with an example.  Say your bank gives you the option of downloading checking account transactions, and you’d like to post the last 18 months.  The download comes in QBO format for the past 90 days, or CSV format for the past 18 months.  You can include the date, amount, and a description field with a mixture of data in it that varies with the type of transaction.

First, note that options differ for importing history into Desktop and Online versions of QuickBooks.  We’ll consider these cases separately.

QuickBooks Desktop

1)  Accountant’s Edition.  If you have access to QuickBooks Premiere Accountant Edition, you can download bank data into Excel and import it directly into QuickBooks.  This is a great option and I highly recommend it as your first choice.

What you can import:

• Types of transactions: Checks, Deposits, Credit Card Charges, invoices, and Bills
• Fields: You can upload most of the fields you’d ever need to populate.  For example, with checks this includes account, class, payee, memo, customer:job, billable status, and check number.

There are certain restrictions:

• Editions: Let’s say someone with QuickBooks Pro asks you to help load history using your Accountant’s Edition. Both of your QuickBooks Editions must be in the same version (year).

For example, you can’t begin with data in 2018 QuickBooks Pro, process the history using 2019 Accountant’s Edition, and send the file back to 2018 QuickBooks Pro.  You’d need to have both Editions in either 2018 or 2019 versions.

How to learn more:

• You can learn more about using this feature here.

2)  Third Party Utilities.  If you can’t use the Accountant Edition approach, another option is to buy third party software that facilitates batch data entry.

Vendors include:

• CloudBusiness
• Transaction Pro
• zedsystems

3)  iif Files.  A third option is to convert your data into iif transaction format and import it into QuickBooks.

What you can import:

• Types of transactions: Most transactions you’d ever wish to import.
• Fields: Most of the fields you’d ever wish to import for any given transaction.

There are certain restrictions:

• The use of iif files requires a certain degree of experience and patience. You’d be wise to read up on how to do it, and to ensure you’ve properly backed up your data file before proceeding.

How to learn more:

• Explore Intuit’s iif utility toolkit here.
• Intuit changed their tools for importing iif files starting in 2019. Access information from Intuit here.
• Reviews of the 2019 changes have been mixed.  For one review click here.

I have a free utility program that converts Excel data into a general ledger formatted iif transaction.  Contact me for more information.  Instructions appear on the first tab of the Excel workbook.  Plan to use this tool at your own risk as Flexible Financials will not be responsible for its use outside of a Flexible Financials consulting engagement.

QuickBooks Online

QBO will accept transactions from:

1)  A direct connection between QBO and your bank

2)  Files in QBO format downloaded from your bank and uploaded into QBO.

3)  Files in CSV format downloaded from your bank and uploaded into QBO.

Note that QBO does not accept iif files.  Use of iif files is limited to QuickBooks Desktop.

Whatever your data source, all the transactions you upload to QBO flow through the same bank import module you’d use to process your daily bank downloads.  This means that QBO will only accepts the following fields:

• Date
• Description*, and
• Amount

* QBO normally copies bank detail information into the memo fields of downloaded transactions, and this can vary somewhat from the information on your bank statement.  For simplicity, I’m using the term Description to include whatever information of this type appears in your feed.

Why not accounts or classes?  The bank import module assumes you’ll assign accounts and classes in one of three ways:

• By matching each transaction to one you’ve already entered (which, of course, doesn’t work if you’re importing a history file as your original data feed)
• By classifying transactions one at a time in the banking module, or
• By processing transactions using QBO rules.

QBO’s rules are extremely flexible, and it will be your first choice for assigning accounts and classes to historical data.  There are plenty of great resources online showing you how to set up rules, so I won’t cover this information again in this post.

What I will address, however, is the situation where you want to assign accounts or classes in Excel before uploading your data.  The question then arises, how can you get QBO to accept these fields? There are two choices:

• Employ a workaround solution (described below), or
• Buy a third-party utility that bypasses the bank import module.

Let’s look at these two options.

The Workaround

Let’s say your data downloads from the bank in CSV format.  When you open it in Excel, it looks like this.

I’ve had clients sort this info by payee, then copy accounts into Col D as shown below.

From here, you can add a column F to hold a modified description incorporating the account information, as follows:

You populate column E using Excel’s CONCATENATE command.  The formula for cell E2 is:

E2 = CONCATENATE(“ACCT“,” “,LEFT(D2,4),” “,B2).

When it comes time to upload the description, you’d use column E for the description instead of column B.  Then you’d write a QBO rule for that account.  For example, you would create a rule looking for the text “ACCT 6240” and classify those expenses to account 6240.  You’ll need one rule for each account.

Why would you do this in Excel instead of simply making the account assignment within QBO in the first place? There simply are times when Excel handles this better:

• One case is where you have a lot of transactions to classify and automatically assigning accounts requires several logical decisions. For example, you might have a situation where you need to consider three factors in sequence.  You could add four columns to your download:  Columns F, G, H, and I.  Col F is a Y/N flag set based on the card used.  Col G is a Y/N flag based on the state where a purchase occurred.  Col H is a yet another flag based on the date, since the company reorganized halfway through the year and department assignments changed.  Finally, Col I is an IF/THEN/ELSE formula based on Cols F-H.  By bringing this answer back into Col D, you’d be able to mass upload accounts in a way that would be more difficult to code using QBO rules.
• Another case is where you’re dividing the work of assigning codes among several people and it’s just faster to copy/paste the answers into a single file that you then upload.

Third Party Utilities

There are several companies offering utilities to send transactions from bank downloads or Excel into QBO.  ProperSoft is one company, Zachary Systems is another.  I have not used these tools so I can’t vouch for their effectiveness.  You can search online for reviews and pricing.