Import OFX file to Intuit Quickbooks QBO – No Cost Method – AKA Free

Members First Federal Credit Union of Florida, features online banking options, including the download of account transaction listings for import into the users accounting software. Like many banks, Members First Federal Credit Union of Florida does not offer the transactions.qbo file required for use in Quickbooks Web Connect. The options are:

  1. CSV (Comma Separated Values) which is a universal format useful for moving between simple data programs like Excel
  2. OFX (Open Financial Exchange) which is a specific format for financial records
  3. QFX is Intuits variation on OFX, specifically for use with their Quicken program.

qbooksThe reason for this is that Intuit requires financial institutions to pay a fee for use of the QIF format. There is a method to easily convert the OFX (potentially QFX as well) into a QBO file.

As of this posting (late 2012) all you need to do is open the OFX file in a text editor like Notepad, or in OSX, I recommend Text Wrangler freeware and the beginning of the file will look something like this:


OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE

<OFX>
    <SIGNONMSGSRSV1>
        <SONRS>
            <STATUS>
                <CODE>0
                <SEVERITY>INFO
            </STATUS>
            <DTSERVER>20121213072123[-6:CST]
            <LANGUAGE>ENG
        </SONRS>
    </SIGNONMSGSRSV1>

What needs to be inserted for is the <INTU.BID>, or Intuit Bank ID line and it will need to contain a number associated with a valid Intuit Financial Institution. As of right now the following will work, if inserted just below the <LANGUAGE> line:  <INTU.BID>17673

This is the 1stadvantagebankonline.com number. You will not be using 1st Advantage Bank, but Intuit will think you are.

There is a program available for Mac OSX called Bank2QIF that converts the OFX file to a QIF file. It costs $49, but the demo is free and allows you to convert three transactions, which will output a QBO file from which you can cut and paste segments into your OFX file to make sure your OFX file header is acceptable to Quickbooks.

Once you have made the changes, save your file with the QBO extension instead of OFX and from Quickbooks FILE: IMPORT: from Web Connect.

Once you have imported the file Quickbooks will request that you chose a bank account to import to. Select the account and import, at which point Quickbooks will remember the account number located in your file:


   <BANKACCTFROM>
        <BANKID>000000001
        <ACCTID>000000000022
        <ACCTTYPE>CHECKING
    </BANKACCTFROM>

At this point you will need to click the ADD TO REGISTER button for each transaction in order for it to appear in your account.

If you have any trouble here, you may need to follow the following procedure:

1. Go to your chart of accounts, select the account you’re having issues with and click on “Edit Account”.2. Now click “Online Settings”, at the bottom select the dropdown “Downloaded transactions” and change it to “Not enabled”.

3. Open the .QBO file, open this in text editor. (using only the dates that you need, don’t go back further or you’ll have to manually match transactions)

4. Scroll down till you see “<ACCTID> ” and you should see your account number / CC number after that.

5. Now delete the last digit of your account number.

6. Now import your file and select the account you want it associated with.

The next time you import your data, you’ll first have to go back through steps 1 & 2 above.

But this time DON’T delete one of the digits on the account number with text editor.

You’ll have to again select the account the data belongs with and then you should be back on track.

You’ll have to again select the account the data belongs with and then you should be back on track.

It’s taken many hours of trial and tribulation to finally get this procedure working. At some point last year for $30 or $40 the SimplyConvert IIF Converter 1.5 had been working, but I usually end up going months between importing and getting caught up on accounting and at this point I can’t even remember where I was getting the QIF files required for SimplyConvert to work. I think QIF stands for Quicken Interchange Format (confirmed) and maybe there was a conversion process from QFX to QIF.

Thanks to ProperSoft for insight as well as Edseek and TimTheToolMan.

 

Note: This method isn’t working for me at the moment, possibly this link can provide a solution.