TLDR: Scroll to the bottom for the sed script I wrote to accomplish this task.
A few years ago, my bank (formerly Chemical Bank, and then bought out by TCF Bank) institued a system upgrade to its online system. One of the results of this “upgrade” was that all of transactions associated with any bank initiated bill payments became stripped of any unique details rendering them unidentifiable when downloaded into Quickbooks via the traditional QBO file. Specifically, transactions that previously imported with a payee name that matched one of my rules within my Quickbooks file, now all simply had a payee of “CHECK PAID”. Consequently, none of my import rules worked anymore and each bill payment transaction had to be manually entered. Upon discovering this problem and subsequently learning that TCF bank had absolutely no desire to correct the problem, I came up with the following solution.
NOTE: I originally tested this with the Destkop version of Quickbooks 2017. I’ve since upgraded to Quickbooks 2020 and it works fine there as well. I’ve never tested it with Quickbooks Online, but I don’t see why it wouldn’t work there either.
I noticed that the bill payment module utilized by my bank’s website offered a way to download an IIF file of the bill payments which did keep vendor names associated with each transaction.
If unfamiliar, IIF files are used to import (or export) transactions directly into or out of your Quickbooks file. However, unlike a QBO file, you don’t get to review the imported transactions. When the IIF file is imported, it simply imports EVERYTHING and just creates new vendors, accounts, categories, etc. as needed. For this reason, use of IIF files can be problematic if done incorrectly. (I’ll describe how prevent disaster via IIF import later.)
UPDATE: It turns out that Quickbooks 2020 does provide a little review process when importing IIF files vs previous versions. However, I didn’t find it all that robust and most of the concerns that I’ll highlight in this post about importing IIF files are still basically true.
The following is an example of an IIF file which I could download from my bank:
As you can see, unlike the QBO files available after the upgrade, the payee names are still visible in the NAME column of the IIF file.
In an IIF file, a transaction’s Quickbooks vendor name is assigned on the TRNS line in the NAME column and it’s expense category is assigned on the SPL line in the ACCNT column.
I could have imported this file into Quickbooks just as it was and it would have preserved the payee names. However, if I did so without editing it first, it would result in the creation of a new checking account within Quickbooks named “BUSINESS CHECKING” and a new expense category named “None” to which all of the imported transactions would be assigned. This was clearly not desireable. Fortunately, IIF files are really just tab delimited text files that can be easily manipulated.
Specifically, the manipulation I needed was for the bank account name (set as “BUSINESS CHECKING” in the example IIF file above) to exactly match that of the checking acount I was already using in Quickbooks (in my case “Chemical Bank”). I also needed/wanted the IIF file to specify the expense accounts to which I wanted these transactions assigned. (In essense, I wanted this modified IIF file to do what my QB rules had been doing to these transactions prior to the TCF upgrade). In other words, this:
Additionally, TCF Bank’s upgrade resulted in numerous syntax errors within the IIF file they provide. The following modifications to the IIF download were also needed in order to prevent errors when importing the file:
- Remove quotes from around all values
- Remove erroneous “N” value from CLEAR parameter in SPL line
- Remove erroneous “N” value from QNTY parameter in SPL line
- Correct “!ENDTRANS” typo in line 3 so that it reads: !ENDTRNS
These syntax errors (if left uncorrected) wouldn’t prohibit the data from being imported, but did yielded errors when running the “Data Verification” tool within Quickbooks. Correcting these types of errors from within Quickbooks after import is an outright PITA, so fixing them prior to import is critical.
I also wanted to make a few other elective changes, such as to:
- Remove unnecessary memo lines
- Remove “E-Pay” from check number column for payments sent electronicly
The final product looked like this:
Once I determined the necessary modifications needed for my IIF file, I set out to write a script that would make all of these changes in a single command. I acheived this objective using the sed command. SED is typically used on Linux, but being that Quickbooks is a Windows program, it seemed more practical to use the version of sed that works with Windows. This is the SED script I wrote to acheive the task:
The script can be edited to suit one’s own needs should they find themselves in a similar situation. Once it’s edited to your liking, execute it from your Windows command prompt like this:
sed.exe -r -f sed_script.txt bank_download.iif > bank_download_modified.iif… where sed_script.txt is the script above, bank_download.iif is the file you downloaded from the bank and bank_download_modified.iif is whatever name you wish to give to the modified IIF file.
CAUTION: Because of the risks of screwing up your QB file via an IIF import (as mentioned earlier), you’ll want to back up your QB file before importing any kind of IIF file. Once you have a good backup of your QB file, you can safely import an IIF file into QB. If problems arise, it’s easy to restore the QB backup and try again.
Before importing your first IIF, you’ll want to make sure that you don’t already have errors in your QB file!!!
- Check the “Other Names List” found under Lists -> Other Names List. It’s not you can’t have items on this list. However, if you do, you’ll want to know they’re there before your first IIF import. I prefer to keep this list empty so that there’s no confusion.
- Check for errors in the QB data. From the menu bar: File -> Utilities -> Import -> Verify Data. If this generates any errors, you’ll want to correct those before importing an IIF. If you do have errors, you could choose to leave them uncorrected. However, it will become difficult to determine if you’ve created new errors with your IIF import when errors already exist in your QB file.
If everything is configured correctly within the SED script (and you have a good backup of your QB file), it’s time to import the modified IIF file. You’ll find the import option from the menu bar: File -> Utilities -> Import -> IIF Files… After you’ve imported the IIF file, you’ll want to make sure that things imported as desired.
- Make sure that the import didn’t create a new checking account or new expense account named “None”. If either of these occur, something isn’t propertly configured in the sed script.
- Check the “Other Names List” again. There shouldn’t be anything new here after the import. If you see something new after import it means that either:
- One of the payees in your IIF doesn’t yet exist in QB. (From within Other Names List, you can easily convert the payee to a Vendor. Subseqent imports should no longer list the payee here after converting it to a vendor.), or
- The payee name in the IIF doesn’t exactly match the Vendor name in QB. There’s a section in the SED script that you can use to modify payee names so that they’ll match the vendor names in QB.
- Check for errors again via the “Verify Data” utility. There should be no new errors after the import (and ideally none at all.)
If you get any errors, you’ll want to restore the QB backup you made prior to importing the IIF, edit the sed script, import the IIF again, and re-check for errors. Once the transactions have been imported via IIF, they should auto-match to the previously unidentifiable transactions that came through with your QBO file.
And that’s basically it. I realize this sounds like a hassle and a half. However, in my opinion, running my script once a month when I download transactions is far less hassle than switching banks or worse, manually entering/editing all of these transactions.
Feel free to post questions or comments.