- software -
Membank ".QIF" Feature
USING THE MEMBANK ".QIF" FEATURE
The Problem It Addresses
We live in a wonderful age! Imagine being able to download your bank information anytime you wish into a ".qif" file (a file ending in .qif extension) and then upload that information into a family finances tracking program such as Quicken. What could be wrong with that? Well, at least for the banks that this author does business with and his 2001 version of Quicken, the downloading of checks leaves much to be desired. The banks seem to have a hard time interpreting the written receiver of the checks, and hence calls each check simply "CHECK". And of course the bank has no idea what personal expense account in your household finance program you wish to assign to it. That means that what one does get in their download for each check is the check number, the date, and the amount. Get ready for a lot of typing and a lot of keystrokes.
What Membank Can Do to Help
After setup of the right file for your needs, Membank can be an intermediary. After your .qif file is downloaded from the net and before loading into your home finance program, Membank can do an assist to fill in some of the needed information perhaps much easier than your final finance program. After the .qif file is loaded into Membank one can use the now visible "up/down" keys to look at each check one by one. As the user recognizes a check number from their checkbook, he or she can press one of the 140 gray keys with their mouse and auto load the check name plus the desired expense category into the data with that single mouse press. After reviewing the changes quickly, one then presses the "load" key to load that data into the .qif file. I find that to be much less tedious.
If that is not enough, Membank has a second feature (qif auto entry - from the pull down menu) that can auto-scan your checks and assign a "likely" check name and expense account. In my case, this feature works for half of my checks and is a real time saver - but of course this will depend on the person and their application. How this works is that a certain number of checks that we write are often the same amount for the same function - such as Mortgage, weekly charitable contribution, membership fees, newspaper costs, periodic service organization costs, and perhaps even cable tv, high speed internet, isp service or mobile phone costs. When activated (qif auto entry - the pull down menu top item under QIF Functions), this program checks amounts that you have stored for each item and when it finds a match it automatically assigns that item as the check recipient and the associated expense category. Of course, there will be times when it will be wrong - the times when a gift or other item was just the same amount. However, one needs to go through the checks one by one afterwards anyway, and I find it is much easier to change the few errors than to begin from nothing. And of course when one does find an error, one just presses the right gray button of the 140 choices below and it should correct it. Then one presses "load" when done with that entry with their mouse.
QIF File Variations and Membank Testing
First of all, we do need to state that Membank has only been fully tested with Quicken 2001. It is also not really clear if there is an absolute standard set for .qif files, and it will definitely not work for non-qif files such as the Money .ofc file. However, it is expected that the Membank program should work for all .qif files that I have seen as yet. There are three versions of .qif files that I see that banks make available: Quicken 98, Quicken 99 and Money .qif's. Upon examining those files with a word processor there seems to be little difference. As long as one does not modify the dates when loaded, all versions should in fact work. The three versions that I have seen do in fact load into Membank. The variations seem to be that the Quicken 98 version and the Money version use only two digits for the year, whereas Quicken 99 modified their year data right before the Millennium to a four-digit year. That would seem to be a reasonable variation. The other variation noted was that the Money .qif function allowed for fewer characters for the check title (check made out to whom), seeming to limit it to about 32 characters instead of approximately 40 characters for the Quicken versions. These changes seem to matter little, again, as long as one does not modify the date from a two-year to a four-year date or vice versa. The pull down menu data changer under .qif functions also has a problem with the two character year versions. But again, as long as one does not change the date on the two year versions, or at least does so carefully, there should not be an issue.
In my case, I never modify the date anyway. I prefer using the bank's date - which is the date cashed rather than the date the check was written -since that is the actual exchange of funds.
Membank must be set up for helping with .qif files. And of course it would be best to try it on some samples before devoting a great deal of energy to creating a full-featured file. If this feature works for your application, however, you may find as I did that even though one must create a file, one is doing most of the work once rather than say once a month.
When creating the file one should start with the sample file "start_bank.txt" since that file will give one the right data names. That will name the primary 3 pieces of data: "DATA" (the name the check is made out to), "CATEGORY" (the expense category character for character that your home finance program calls it), and "$" (which is the check amount, which will have a minus sign in front of it such as: "-10.31" for checks, but positive for deposits). Check amounts are never entered into the loaded qif files when one presses the gray buttons - since the amount the bank believes the check was made out for should absolutely take priority. But if you do enter a check amount in your original main text file then that amount can be used for the ".qif auto entry" feature that was reviewed above. One of the more exacting items of making this main control text file is to ensure that the CATEGORY entries (the second largest text boxes at the top) are character for character the same as required and used by your finance program for your selected expense categories. I used the computer clipboard to transfer the category names to the Membank file to ensure exact characters.
Another interesting feature of the .qif Membank usage is had by putting a period in front of the button name for an item, such as using ".ATM" for one of the 140 gray button names. This does two things when one presses those types of gray buttons with their mouse with .qif data loaded and displayed. The first is that it does not disturb the original "DATA" entry - the original recipient that the bank said was receiving the funds. This is a nice feature when one wishes to only add a CATEGORY (expense category) to the bank data and not upset the check title of whom it is made out to. The second feature of this comes into play during the "qif auto entry" feature. At those times the program will scan the DATA check names (check made out to names) from the Bank and if it sees the same letters in the same order anywhere in that title, will attach that expense category to that check during the ".qif auto entry" process. Where does one find this helpful? Well in my case ".ATM" for a button name captures all of my ATM transactions, and ".DEPOSIT" captures a lot of the deposit transactions and both automatically attach the correct expense / deposit category that I desire.
An Overall Bank Download Plan
One can therefore use Membank to do the following after creating two main text files for it: both a www file for it and a banking file:
1. Use Membank and load the www file to go to the banks on the net where download information can be had. Store password names in this file and also passwords in Membank for the banks that require those passwords (mine all do). Send the www addresses to the computer clipboard and paste it into your browser address by clicking your mouse in the browser address window and then using shift and insert. Send the password name and password separately to the clipboard by clicking the password name button to the left of the password name. Paste the name in the right location in the browser. Then mouse click the "pw" label to send the password itself to the clipboard. Then paste the password itself in the right location of the browser. For each bank account, download the .qif file to your favorite location on your drive.
2. Using Membank, load the main control text bank file information - previously setup and stored. This is the ".txt" file of your bank expense categories. Then under file use "load qif" and load the first of the .qif files that you have stored. You need not of course go through every .qif file, but the ones that have to do with checking should be a time saver. After loading the .qif file, the .qif buttons and text areas should now appear on the Membank program. It will show the title of the file as item number "0". This is the time to decide if you wish to try the "qif auto entry" under the QIF Function pull down menu. If yes, run this feature now. Since this feature is an approximation (and could make a mistake in its guessing) it is best to do this first before going through each check item.
3. After this decision, press the "up" button with your mouse to look at the first check. Compare the check number to your checkbook. Press one of the 140 gray buttons as you wish form the program keys below. After the button presses are done, you can then modify the "DATA" (check made out to whom) text or the CATEGORY text by keyboard entry also if you wish. When done, press "load" and the data changes will be loaded. If no change is desired to that check being reviewed, simply press the up/down buttons with your mouse to move to the next check.
4. When done modifying the .qif file, save it again as a .qif file. Note that if you change the name slightly, it will preserve your original .qif unmodified file. If you use the same name in the same file, it will ask for an overwrite (as normal).
5. Load the completed file into your home finance program, Quicken or Money, and decide if this process was easier for you. I personally find it to be much less tedious, and that it makes the monthly bank balancing job almost reasonable. Of course, you will need to decide that also.
I find it best to NOT modify the bank file with the stored data and the .qif file at the same time. I setup the main bank file and store it as a ".txt" file as always. When into the ".qif" file I just do the best that that setup can do, and save the completed ".qif" file. If I need changes to the original bank file data, the ".txt" file, I reload the program later and put in the changes from my notes.
I realize that this feature may take some experimenting and some work to find out if it can work for you. It however, does do wonders for my banking.
Links to related application pages on this Membank software:
(note that the main program information and screen shot is under "Main Membank Page".)