This article encompass 4 concepts:

  • Downloading Bank Transactions in Excel (or CSV) instead of “QuickBooks Web Connect”
  • Editing or cleaning up the transactions a little bit in excel by doing some mild excel work
  • Using a $69 tool called CSV2IIF  to import the excel file to QuickBooks
  • Using The Reclassify Transaction tool in QuickBooks Accountant 2011 or newer

First you must make sure you create an Income Account called “Income” and an Expense account called “Expenses”, basically all bank credits/deposits will fall right in Income here and all the bank debts/checks right in Expenses.

ScreenHunter_03 Apr. 18 01.19

Second, Open the CSV or Excel File Downloaded from the Bank

ScreenHunter_04 Apr. 18 01.20

Then, Insert a Column to duplicate the Description columns and name this one Memo.  This is to make sure QuickBooks retaines in the memo the identical description found on the bank statement originally, regardless of what vendor you end up using

 

Now, it is very important to do so minor “clean-up” in the excel file, The first thing to do is limit the data on the Description/Payee field to 31 characters because QuickBooks maximum name character count is that.  You can use the =LEFT() formula for this:ScreenHunter_06 Apr. 18 01.22

 

After that, sort the spreadsheet by the Description or Payee:

ScreenHunter_07 Apr. 18 01.23

Now you need to manually scroll and scan for slighly different vendor names that really need to be replaced with a single vendor name.  In this example, I replaced everything that said ” Check XXX…” to “Need Check Image”, so QuickBooks will create a single vendor for this, which makes it easy to create a filtered report… These are going to be fixed later on manually.

ScreenHunter_08 Apr. 18 01.24

Costco, has different description based on the location, but I will override and just replace with “Costco”, this is very common for gas stations, large chain stores, and fast food restaurantes

ScreenHunter_09 Apr. 18 01.25

 

Because of the 31 character limit, I need to be careful on different payees that would otherwise download exactly the same, because the differentiation is after the first 31 characters.  Here I have 2 Chase credit cards, and I manually replaced the name to contain an indication of which card this is.

ScreenHunter_10 Apr. 18 01.29 ScreenHunter_11 Apr. 18 01.30

Sometimes, you will have patterns where the actual description you want starts at a consistent character count, in this example, the vendor’s name started at the 51st , character, so I used the =MID() formula to automatically get the description from the 51st character and beyond:

ScreenHunter_13 Apr. 18 01.38

When finished with the clean-up, save the CSV, and Launch the CSV2IIF Utility

ScreenHunter_15 Apr. 18 01.44

 

Save the IIF, and go into your QuickBooks File to Import the data:

ScreenHunter_16 Apr. 18 01.45

You will see the new bank Balance

ScreenHunter_17 Apr. 18 01.46

Which should match the excel sum of transactions:

ScreenHunter_14 Apr. 18 01.43

Which means, INSTANT BANK RECONCILATION:

ScreenHunter_18 Apr. 18 01.46

And the Financial Statements will look very basic..

ScreenHunter_19 Apr. 18 01.47 ScreenHunter_20 Apr. 18 01.47

So the next step is to use the Reclassify Transactions tool only found on the ACCOUNTANT version of QuickBooks

 

ScreenHunter_21 Apr. 18 01.48

 

So, we start reclassifying to the desired expense categories based on the vendor name..

ScreenHunter_22 Apr. 18 01.49

By Cleaning up the excel previously, should make it easier to use the SELECT ALL feature when filtering a specific vendor.

ScreenHunter_23 Apr. 18 01.49

 

Remember what I had said about the Checks?  Unfortunately, Unless you tool the time to write the payee names on the Checks portion of the spreadsheet, they would have to go into a temporary account and get re-classified later.   The Reclassify Transactions tool charges the Expense Account and the Class, but not the vendor name.

ScreenHunter_24 Apr. 18 01.51

 

 

Some Other Examples:

ScreenHunter_25 Apr. 18 01.52

Until the catch-all “Expenses” Account is no longer being used, and all transactions are classified where they should

ScreenHunter_26 Apr. 18 02.05

And your financial statements start to take share really fast!

 

ScreenHunter_27 Apr. 18 02.05 ScreenHunter_28 Apr. 18 02.06

 

 

 

 

One of the things I like most about this technique (other that how fast write-up becomes) is that the MEMO retains the original bank description, so makes it easier to revise later on.

ScreenHunter_29 Apr. 18 02.06

On all types of transactions:

ScreenHunter_30 Apr. 18 02.07

 

 

Conclusion, if you are an accountant with a lot of “data entry” work, you can change the way you do the work substantially with this technique.  Now the Online Banking (Bank Feeds) feature is designed to make this whole convoluted operation much easier to work… But guarantee you it will not be faster in most cases, specially if you are an avid Excel user that can clean up the spreadsheets fast.  Also, I have a few clients that Download the bank spreadsheet THEMSELVES and use it as their internal accounting then give me the spreadsheet with notes and all.. so I can write-up in a zilch.

Best part, data entry based user-error is virtually minimized, and not having to reconcile the banks the traditional way speeds up the process with same accuracy.

You can check out the tool here, if you want to buy it

 

Hector Garcia, CPA
Advanced Certified QuickBooks ProAdvisor
hector@garciacpa.com

Share →

Leave a Reply

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



For More Information

Call us to 954-633-2718 or email: hector@quickbooks-training.net