This post has been closed and is not open for comments or answers.

Using ImportQIF for Multiple Accounts

I need to import QIF files in 3 different acounts.  It looks like ImportQIF will help with that process for one account.  Is there any way to use it for 3 different accounts?

Thanks, Bill
    (Note the whole reason for ImportQIF or manually editing of the QIF is to bypass the Quicken restriction of not importing into any account type that is not a cash or liability account).

    Bill,

    Are you saying that your financial institution is putting the data for 3 different accounts into one QIF file?
    Or is this QIF file from some other source like Quicken?

    If the financial institution allows you download them independently then you can use ImportQIF with no problem.  You just install ImportQIF allowing it to register to handle QIF files.  From there you setup a button for each account with its name and account type.  And when you open a QIF file in your Web browser ImportQIF will take over, you simply press the button you have setup for that account and ImportQIF convert it.  With the advanced features on, you can have ImportQIF import that converted QIF right into Quicken.  Then you just repeat the process for each account/QIF file selecting the different account button in ImportQIF.

    The QIF format certainly allows for having multiple accounts, but to do that you have to have the account names and types in the data file for it to work right.  As in the same information that ImportQIF is adding to the top of your QIF file would be to be added for each account in between the correct transaction.  It is actually this account information that is how you know what transactions belong to what accounts.

    If the financial institution is mixing all the accounts then for the file to be proper at all they would have to supply the account name and type information at the right parts in the QIF file, just like ImportQIF will do for a QIF that doesn't have the information in it (ImportQIF would have no way how to divide up the transactions if they were just a long list from 3 accounts).  Note that if this is the case then you have to some how match up the account name with the one in Quicken (as in rename one in Quicken or at financial institution).

    Here look at this export I just did from a test account in Quicken 2011.  I had two accounts "Checking" and "Checking 2", and told it to export all the accounts (there is only one transaction in each account the openning balance).  The first section which is optional is the list of accounts:
    !Option:AutoSwitch
    !Account
    NChecking
    TBank
    ^
    NChecking 2
    TBank
    ^
    !Clear:AutoSwitch
    !Option:AutoSwitch
    !Account                   --------  This is the start of transactions
    NChecking
    TBank
    ^
    !Type:Bank
    D4/23'11
    U0.00
    T0.00
    CX
    POpening Balance
    L[Checking]
    ^
    !Account
    NChecking 2
    TBank
    ^
    !Type:Bank
    D4/23'11
    U0.00
    T0.00
    CX
    POpening Balance
    L[Checking 2]
    ^
      ImportQIF can take data in one of three formats, QIF, CSV, and OFX (MS Money).
      In the case of QIF, ImportQIF puts the account information (for one account) at the top of the QIF information and can automate the importing of that converted QIF file into Quicken.

      When financial institutions say they are downloading to Excel most of the time they really mean CSV (Comma separated values).  If a person has Excel, Excel is gets setup to "handle" QIF files, which is when you open them in your web browser or Windows Explorer it is Excel that opens the CSV file. Excel then allow the person to save the file in CSV format or XLS, which is Excel standard format.  ImportQIF can not read XLS format.  So this one really depends on what the financial institution is really downloading.  With a CSV file you have to tell ImportQIF what columns are what (Payee, Amount, ...).  Once the columns have been identified for a given account, ImportQIF will ignore rows that don't have exactly all that data, so it shouldn't pick up the comment rows and such.  The CSV data gets converted to QIF format, and then is the same as what happens for the QIF flow.

      ImportQIF can convert the OFX file to a QIF file, and then it follows the same flow.

      So the big question is the file that is being downloaded from GE Interest a CSV file or a XLS file?
      If it is CSV you shouldn't need xl2qif.  If it is in XLS you have to go through xl2qif, but you can certainly use ImportQIF to add the account information.

      Please note it is also possible to setup ImportQIF to be registered to handle CSV files instead of Excel.  If you don't use many CSV files (outside of this operation) then it might be easier to have ImportQIF handle CSV files instead of Excel.

      If ImportQIF is handling the QIF format the flow is:
      Have Quicken running.
      Open CSV file in Web Browser,
      ImportQIF starts up, and you select the button of the account it needs to import into.
      ImportQIF converts the CSV to QIF and if enabled imports it into Quicken.

      (With setup this way if you want to open a CSV file in Excel you use File -> Open to open it).

      If you have Excel handling the QIF format the flow is:
      Have Quicken running.
      Save the CSV file to disk.
      Start ImportQIF and use File -> Open to open CSV file.
      Select the button of the account it needs to import into.
      ImportQIF converts the CSV to QIF and if enabled imports it into Quicken.


      There is a one setup for each account that you want to setup to tell it the account name, account type, and for CSV files you select CSV mapping option that lets you tell it the mapping.
        Just to make sure you looking at the right thing for determining the file type here is a picture with Windows Explorer showing the same file stored in both formats:
        http://www.mediafire.com/imageview.php?quickkey=4o7d549awk7m7x1&thumb=6

        It isn't a file handler for Internet Explorer per say, it is a file handler for Windows.  It simply means what is the default program Windows should use when you open a given file type.  You actually have seen this many times.  When someone says that their default web browser is Firefox or IE what is really meant?  It simply means that when Windows has been told to open a web page file (HTML file type files) it will open with Firefox or IE by default.
        If you have a .TXT file, by default, if you open that type file, Windows will run Notepad and give the path to the file to  Notepad to read in.

        Say you are in Windows Explorer and you see a QIF file, and you double click on it or select Open for it, which program will Windows start to read the QIF file?  That depends on which program is registered to handle it.  Also if no program is registered to open a certain file type Windows will ask you which program you want to use to open it.

        To make it easier to tell what program will open what kind of file Windows will change the icon for the file to the same as the program that Windows will run by default when you open that file type.  For instance look at the icons on the Book1.csv and Book2.csv files in the image above, notice they are both the icon of Excel.  Which means if I tell Windows to open either of them Windows will start Excel and give them the file to open.
        Now below I have registered ImportQIF to handle the CSV file and you can see the icon and the file type description has changed.
        http://www.mediafire.com/imageview.php?quickkey=rn9tram01z17gvh&thumb=6

        (Please note Windows doesn't change the icon until you log off and log back in, but switches which program is the default immediately).

        When you download a file using your web browser and you Open/Run that downloaded file.  What your web browser really does is save that file to a temporary folder on your disk, and then tells Windows to open that file, and Windows checks which program is set as the "default program/one to handle this file type" and it runs it and gives the path to the program to read it in.  

        Another example is that Quicken registers itself as the program that should be run when a QFX (Web Connect) file is opened.

        It is possible to change which program handles what file type by default.  In Windows XP this is under the Control Panel -> Folder Options.  In Windows 7 it is in Control Panel -> Default Programs -> Associate a file type or protocol with a program.

        But in general people don't usually change these settings themselves instead they get the program to do it.
        For instance you use IE, and then you download Firefox and run it.  It asks you if you want it to be your default web browser.  What is actually asking you is should it register itself as the program that Windows should run if you just open a web file (.html or .htm file).  Note just like the IE example you might have something like Quicken running and want to open a web page in your web browser, and it will just tell Windows to open it and let the same process happen.

        When you install ImportQIF it will ask you what file types you want it to register for and handle by default.
        You can re-install ImportQIF at any time to change this setting, and if you deselect a file type ImportQIF will return the default program to use to the one that was there before you installed ImportQIF.

        File "types" are also called "extensions" and "handling" is also called "associating" so you can search for more information (But I have given a very good run down on it except the details on setting them manually) with:
        windows set default file types
        OR
        windows set default file associations

        Also if you end up having to use xl2qif because it is a XLS file you should notice that xl2qif has an option to "auto import".  What this actually does is just ask Windows to open the QIF file, and it expects a program to be registered to handle QIF files like MS Money or Quicken or ImportQIF.  Quicken registers itself to handle QIF files, but it doesn't actually work to import the QIF file (a hold over from when it did work).

        So say you install both xl2qif and ImportQIF (and ImportQIF is registered to handle the QIF file) and setup ImportQIF for the account (no CSV mapping data needed since this is QIF to QIF).

        Then the flow is:
        You have Quicken running
        You open the Excel file in your web browser
        Excel starts and you use the xl2qif menu to convert it to a QIF (with the auto import option on) when you save it.
        ImportQIF is started, you select the account button, the account information is added and a Converted.QIF file is created on your desktop.  If the Auto Import option is on in ImportQIF it will then import the QIF file into Quicken.  You can also manual import the Converted.QIF file into Quicken.
          It sounds like ImportQIF is having problems with something about your Quicken.
          You have not missed any steps, ImportQIF automates this by going through the Quicken menus just like you would so there is nothing to setup in Quicken.

          It might be best if you go to http://www.quicknperlwiz.com/ and fill out the contact request there and we can work out what the problem is through email.

          BTW ImportQIF did create a Converted.QIF that you can manually import into Quicken.
          The steps for that are given here:
          http://www.quicknperlwiz.com/importqifloadintoquicken.html

          As matter of fact if you import the QIF file manually you can see the exact steps ImportQIF would automate and might be able to see what isn't happening right.
            Oops!

            OK I know what the problem is now.  My instructions for doing it manually are backwards.  As in you can't be in the Portfolio view.  When you are in that view the QIF import option is removed replaced by the option to import CSV data.

            Switch to the Home tab and you should be able to import.

            Thank you very much for this feedback and let me know if this works for you.

            Thanks.

            P.S. I will look into changing ImportQIF so that if the QIF option is not available it switch views automatically.
              ImportQIF 2.1.3.0 is now available, which if the user is on the portfolio view in an older version of Quicken (Quicken 2010 or below) it will switch to another view since the QIF Import menu command is not available on these versions of Quicken when on the portfolio view.

              There is a 5 seconds lag before switching because another reason the QIF Import menu can be not available is because of a really slow computer, so ImportQIF waits this long to make sure that this is not the case.
              So on Quicken 2010 and below it is best not to be on the portfolio view when asking ImportQIF to do an automatic import of the QIF file.
                For importing (accepting transactions into the register) these are the steps taken by Quicken.
                First understand that Quicken's picking of a category for a transaction is based on the Payee, and it doesn't care what account you are in (memorized payees and renaming rules are global).

                First Quicken checks if you have a renaming rule (Tools -> Renaming Rules) that matches either the payee or the memo.  The purpose of the renaming rule is to get the payee to the name you really want.  For instance you have payees like "Safeway Store1", "Safeway Store2", and you want them all to be "Safeway" a renaming rule can be created that sees the payee starts with "Safeway" and renames it to "Safeway".  The renaming rule can look at the memo to decide if it should rename the payee, but it doesn't change the memo.

                After the renaming rules Quicken then looks for memorized payees (Ctrl+T).  If one of the payees matches then it will use the category from that memorized payee for the transaction.

                If none of the above applies Quicken will sometimes guess at what it thinks the category should be and use that.

                By default Quicken tries to guess at both the renaming rules and the memorized payees.
                The options that affect these are:
                Edit -> Preferences -> Quicken Preferences -> Downloaded transactions (Renaming rule options).
                And Data entry and QuickFill, Under data entry:
                "Complete fields using previous entries"
                "Recall memorized payees ..."

                And under QuickFill and Memorized Payees
                "Automatically memorize new payees"

                Also if you do not lock a Memorized Payee and you change a transaction as you enter it, it will update the memorized payee with the new information.
                • I've got the Renaming Rules working like I want except that every Interest transaction is now tagged with TXFR in the Num column.  I want that colum to be blank.  To try to fix this problem I deleted the memorized Interest transaction and then memorized a new one that looked just like I wanted it to but that didn't help.

                  Any ideas?

                  Thanks, Bill
                I remembered seeing the problem you are describing so I played with it a lot, and the bottom line is there is a bug in Quicken 2010.  This has been fixed in Quicken 2011.

                Basically in Quicken 2010 it sometimes out of the blue will set the Num field to TXFR.  There is no real pattern to which transactions it will do it to, and there is no settings in Quicken that prevent it.  You can turn off all the auto filling in, the use of renaming rules or memorized payees and it still does it.

                There is actually a field in a QIF for Num (A line starting with N) so that the check number can be passed in and such, but as you would expect most of the time it isn't set.  I found if the N field in the QIF was set to something like N/A then Quicken wouldn't override it with the TXFR, but if the N field is missing or is set to blank like:
                N
                PThis is the payeee

                Quicken 2010 will play it tricks and put in TXFR from time to time.

                The problem of course to setting all the "Num" fields to N/A or something else in the QIF just to prevent this is just the simple fact that now you have another "something" that looks ugly and out of place.  There other problem in this case is that your QIF files are actually being produced by xl2qif not ImportQIF.  ImportQIF is just taking on a header, so there would be more work in actually reading the QIF file and translating it.

                The TXFR in Quicken 2010 doesn't actually cause any problems, it is just ugly and incorrect.
                In Quicken 2011 if you put in TXFR then the category has to be an account.
                • Thanks for the explanation.  I'll probably go through and manually delete the TXFRs for now but I may get tired of that and just leave it alone pretty soon!  At least I now know that its not caused by a wrong setting somewhere.

                  Thanks, Bill
                • New feature added. ImportQIF will now convert a XLS (Excel) data file to a QIF file provided you have Excel installed on your machine.
                In Quicken 2011, you can.  When you select "File" -> "File Import" -> "QIF File", you are prompted to select the account into which you would like to import the QIF file.  Hopefully, this feature is supported in your version.
                  QuickenPerlWiz:  I have 3 accounts at GE Interest Plus which are very similar to having  3 bank accounts at the same bank.  However, GE Interest Plus is not a bank that is listed in Quicken.  Transactions in each of these accounts can be downloaded into Excel but that is the only way they are available for transfer into Quicken.  I know that I can use xl2qif to convert the Excel data into a QIF files and from what I have read it might be useful to install ImportQIF and use it to import each QIF file into the appropriate Quicken account.  Each of the corresponding Quicken accounts is a Banking account at this point.

                  I know that I could manually edit each QIF file to add the proper first lines so Quicken would import them into the appropriate accounts but that is more manual work than I would like to put into it if there is a better way.  Also, the data that ends up in Excel seems OK but it has a lot of Excel rows of labeling and comments above the transaction rows and maybe some more below and I may have to manually remove those rows too.

                  So, I just looking for a way to not have to manually enter all the transactions as I have been doing for a number of years.

                  Thanks for yours and Sherlock's help.

                  Bill
                    Thanks for the detailed info.  That is a big help.  The web site provides an Excel file that can be downloaded, so it really is in Excel format.  To retrieve it, I have to click on a button on the web site that initiates the download.

                    I'm not familiar with the concept of file handlers for Internet Explorer.  Can you point me in the right direction to read about this concept?

                    Thanks,  Bill
                      Thanks QuicknPerlWiz, that was a very thorough explanation.  I have been familiar with file-type associations for a long time but I never noticed the use of the word "handler" with that, so that is why I didn't recognize what you were describing earlier.

                      I believe that the transaction file that is downloaded is actually an Excel file because the button on the web page says Excel file and when I save the download to disc it is saved as a .xls file.  I think I'm all set to implement this process so that will be my next step.  I'll come back with more questions if I get stumped.

                      P.S.  I opened the downloaded transactions file from GE Interest Plus with my text editor to see what the internal format actually is and it is not a .CSV file.  It is obviously a binary file with it's own internal format and lots of stuff in addition to the actual data.  Hence, it really is an Excel file.

                      Thanks again.
                      Bill
                        OK, I've made some progress but have encountered a problem.  I installed ImportQIF as the handler for .QIF files and I set it up with the 3 Quicken account names.  I used xl2qif to save a set of transactions in a QIF file and then I double-clicked the file and ImportQIF opened.   I clicked on the appropriate button in ImportQIF and Quicken's Import sub-menu opened up.  None of the items in the sub-menu looked appropriate to me and after waiting a very short time an error message popped up.  The same thing happened when I tried again and did click one of the items in the sub-menu.  The error message:

                        Error  QIF Import dialog did not come up.
                        Make sure Quicken is not minimized.

                        I haven't done anything in the setup to tell Quicken about ImportQIF.  Have I missed a step there?

                        Any idea what I should do differently to make this work?

                        Thanks,  Bill
                          I followed your second URL to see about importing the QIF file manually.  After reading that, I opened Quicken and selected Portfolio view and clicked File > Import and importing a QIF file is not listed!  That sub-menu lists these choices:

                          Web Connect File
                          Import Prices
                          Turbotax File
                          Microsoft Money file

                          I selected the Web Connect item and it was looking for a .QFX file -- no other file extension choices.

                          BTW, I am running Quicken 2010 Deluxe Release R 10

                          I'll be glad to follow your other suggestion of going to the quickenperlwiz.com site and exchanging emails, but I'll wait and see if the problem just described indicates an obvious problem or solution to you.

                          Thanks, Bill
                          Yes, moving off the port display did add QIF file to the sub-menu.  However, most of my accounts, including the bank accounts, were not listed in ImportQIF's drop-down list.  I finally selected one of the accounts that was listed to see what it would do and the records were imported into the account I selected.

                          Thanks, Bill
                          • Yes that is normal, it reflects the fact that Quicken normally wouldn't import to any account other then the ones listed, but the account name in the QIF file overrides this.  I personally set the account to "All Accounts", but it doesn't matter as long as it is set to something.
                          • OK, now I have the whole sequence working!  I'm all set!

                            Thanks for all your help.
                            Bill
                          Now that I have the transaction transfers from the web accounts to Quicken I've got a related question!

                          Is there any way to associate a particular Category with each retrieved transaction?  A high percentage of these transactions are interest payments so it would be nice if each one could be assigned an interest category.

                          Thanks,  Bill
                            WOW!  You are a FAST Perl Wiz!  I'll try that version in the next day or two.

                            Bill