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.
Second, Open the CSV or Excel File Downloaded from the Bank
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:
After that, sort the spreadsheet by the Description or Payee:
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.
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
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.
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:
When finished with the clean-up, save the CSV, and Launch the CSV2IIF Utility
Save the IIF, and go into your QuickBooks File to Import the data:
You will see the new bank Balance
Which should match the excel sum of transactions:
Which means, INSTANT BANK RECONCILATION:
And the Financial Statements will look very basic..
So the next step is to use the Reclassify Transactions tool only found on the ACCOUNTANT version of QuickBooks
So, we start reclassifying to the desired expense categories based on the vendor name..
By Cleaning up the excel previously, should make it easier to use the SELECT ALL feature when filtering a specific vendor.
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.
Some Other Examples:
Until the catch-all “Expenses” Account is no longer being used, and all transactions are classified where they should
And your financial statements start to take share really fast!
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.
On all types of transactions:
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
