How to Import Bank Transactions into Quickbooks

One of the most frustrating things I have experienced with doing Mr. A’s bookkeeping is the company which holds his credit card does not include options to download transaction in Quickbooks format. It is the same with his checking accounts through the credit union. You can download to Money or Quicken or Excel format, but not Quickbooks format.

This means I have enter each and every transaction manually.  I have known about a program that could be used for downloading the transactions created by Big Red Consulting for over a year now.  The program is called QIF to IIF Converter for Quickbooks.  The company allows you to download the program and use it for a trial period, then the program locks and you have to pay for it.  I was apprehensive about spending the money for this program, because I wasn’t sure I would use it often enough to make it worth the cost, which was $89. I also had a hard time figuring out how to use it.  I emailed the company and asked for some additional instructions, which they provided, but it was taking so much time to figure it out that I just lost interest.

However, over the weekend I decided to try again, as I have gotten behind a couple of weeks in logging Mr. A’s credit card transactions into Quickbooks. I was not looking forward to entering nearly one hundred transactions one by one by one… so I went to Big Red Consulting’s website. I couldn’t remember the name of the program I’d used… but the IIF Transaction Creator looked like it, so I downloaded it and started reading the documentation.  I was able to figure it out pretty quickly this time. The program allows you to import 15 transactions at a time, up to 100 transactions and had imported almost all the transactions when I got an error message that I couldn’t figure out and with this mistake used up my trial period. I decided to just go for it, and ordered the program. It cost $99, but I think it will be worth it with the time I will save from not having to enter transactions individually. Then I started looking through my old emails and this was when I found the program I was trying to use originally was something else.

At any rate, both the credit card company and the credit union allow for Excel downloads (.xls and .csv) so this program will work nicely for both accounts.

This is one of those things where you have to weigh the cost of the product against the man hours it is going to save. Even though Mr. A’s business is struggling, I felt it was worth it to take $99 and buy this program to save me time and hours of data entry. This is partly why I get behind because I do not enjoy having to manually enter this information! One of the reasons I originally hesitated was because Mr. A was thinking about changing to Wells Fargo and then the program would not be needed. But that has not happened yet, so I decided to take the chance and buy it.

I am glad that I did. I was able to learn the format for the spreadsheet fairly quickly, and the error was occurring because I forgot to list the account name in the spreadsheet, so the data was importing into a new account it created within Quickbooks.

The website shows screenshots of the program, and the instructions are thorough and easy to follow.

This is basically how it works – but this is only a brief description, so do be sure to read the documentation that comes with the program – AND this includes the setting up process from the beginning. Once you get the program in place and your spreadsheet template set up it will take literally minutes and about five steps:

  1. Make a backup of your Quickbooks data before you start anything.
  2. First you have to set up the program to work with Quickbooks.  The program works as an add-in to Excel.
  3. Once you’ve installed the program, open Excel.
  4. Make sure Quickbooks is open for the business account you are working with. Go to the add-ins menu and from the IIF Transaction Creator drop-down menu choose to “Integrate your Quickbooks Lists”. Doing this allows Excel to know all your lists which will populates Excel with pull-down menus in each column.  For example, if you right-click on the column named Account you will see the option to insert the appropriate account.
  5. Go again to the add-ins menu and from the IIF Transaction Creator drop-down menu choose “Examples…” this will help you to create a template to use for importing your downloaded data. Note that this part of the tool only pulls data from the last five days, so you can do one of two things.  1) Give new dates to some of the data already in Quickbooks with today’s date (to assure you are within the five days time span) or 2) enter data with recent dates.  I gave new dates to one of every type of entry that we have to give myself a good example sheet.  For example, I included some charges to various accounts, as well as some deposits, interest charges and also an item that had been returned to the store.
  6. Download your data from your bank in Excel-friendly format, either .xls or .csv (comma separated values).
  7. Cut and paste your data into your template. Modify the data into the columns where they belong. For example, my downloaded data from the bank comes with only five columns, but my template sheet contains thirteen columns.  Column E in the downloaded data is “Amount” but Column K is the “Amount” column in my template spreadsheet.
  8. Be sure you made that backup I told you to do in the first step.  I say this because I have lost my data accidentally and it is a nightmare, and any time I do anything new I save the data in case I need to roll back to the saved version.  You’re going to be importing data into Quickbooks and if you happen to import it into the wrong areas you can easily go back and start over.
  9. After you get the data in, go to the add-ins menu and choose Create IIF from worksheet.
  10. The IIF Transaction Creator will tell you where it is going to create the IIF file.  Copy the address.
  11. Go to Quickbooks, go to File > Utilities > Import > IIF Files.  Simply paste in the address you just copied.  Click Open.
  12. Your data is imported just like that.

The next time you need to import information you will use your template, you won’t have to recreate it, so it will much faster.

I am looking forward to the time this is going to save me.  If I can enter the data in five minutes, instead an hour, just think of the hours I will save myself each month. This reminds me of a post I made called When Being Poor Warps Your Thinking wherein I describe how my employer used to make raffle tickets using a sewing machine.

Let me know if you decide to try this program. I highly recommend it! Have you bought any time-saving programs or equipment lately?  Did it work out like you thought it would? Share with me in the comments.


OUT OF DEBT AGAIN is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to AMAZON.COM. OUT OF DEBT AGAIN is an affiliate for several companies and may be compensated through advertising and marketing channels. This post may contain affiliate links.

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge