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.

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

  1. Thank you so very much for this on my windows based laptop and it worked. Can not wait to try ot on my mac now! I have one question……..does this have to be done everytime I import a file?

    1. Hi Marissa. Annoyingly you DO have to do it every time you import a file. But as long as you can remember where you store the template file, it’s just a matter of cutting and pasting the INTUIT ID and bankdata into the new file.

  2. UPDATE: The above Intuit ID number is no longer working. Replace with
    INTU.BID 18948
    (working as of October 2013)

    If you are getting a “nothing imported, account up to date” message. You may have to go in and under the chart of accounts listing and edit the account to disable web connect, then change the account id (ACCTID) by at least one digit (I added a couple of zeroes), then re-import and select to associated bank account number.

  3. I created this tool online to do the work outlined in this article for you easy peasy. Its free and I could care less about making money from it, so its totally free.

    Check it out: http://qboconverter.com

    It probably has bugs in it, but for those that it works for, Cheers!

    1. Shaheen.

      I was a little wary of submitting my bank data to an anonymous web site, but I don’t think there’s anything in it that couldn’t be gleaned from any bank statement so I went for it.

      I tried the online converter on Firefox and it did not work with my ofx file, and returned a notice that it only works with qfx files. So I uploaded a qfx file and it said “Your file will be downloaded”, but there was no download.

      A little ominous. Where and by whom will it be downloaded?

      love, Mike

  4. Hey Mike,

    It should ‘download’ instantaneously. I’ve only tested the site using Google Chrome, so I wouldn’t be surprised if the HTML5 File API is a bit different for Google Chrome vs. Firefox. I’ll look into Firefox compatibility, but give it another shot using Google Chrome.

    Also, I quoted the word download, because the file is never uploaded. Its converted on the spot in your browser – that’s what makes it so safe to use. Nothing is transferred over the internet, its all done on your home computer.

    Thanks for testing the site out and being my first beta-tester… haha

    Lastly all the code for the website is open-source: https://github.com/sghiassy/QBO-Convertor

    1. Really cool, man. Great work. Maybe you could add a little text to the page somewhere explaining the above, which would probably help with SEO as well. Something like, “All of the conversion is done within your browser (Google Chrome only at this point) and never leaves your desktop. The open source code is hosted here.” Possibly followed by, “Back off, Intuit. Don’t you have enough money?”

  5. WOW! I am a NOVICE, let me repeat, NOVICE at the transfer files, extension stuff…..but I can search for answers…..and this works. Thank you BOTH for working on this issue of transferring file types to quickbooks. You’ve saved me LOADS of time. Thanks again!!!!!

  6. OMG THANK YOU!! I’ve been trying to figure out how to import from Canadian banks into my US version of quickbooks. (I live here now but still have many active US accounts). I changed the bank ID to the 18948 listed above and changed CURDEF to USD and it works perfect! (For my purposes I don’t use multiple currency mode, I just know which accounts are CDN). You’ve saved me countless hours of manual entry!!

  7. This solution worked perfectly using ID 18948! Thank you! The key for me was putting the 18948 directly beneath as you noted. I didn’t need to change anything else.

  8. My comment posted with the INTU.BID and LANGUAGE field references. Apparently didn’t like the field markers?

    Should be “The key for me was putting the INTU.BID 18948 directly beneath LANGUAGE as you noted.”

  9. fantastic. thanks for your help. I also do my imports so infrequently. this method worked wonders!
    for my south african bank FNB, I used :3000 and 000000001
    After downloading their “Microsoft money” .OFX format.
    works brilliantly!
    thanks again!

  10. oops. the bracketed didn’t come out,
    I used
    INTU.BID =3000
    bankid = 000000001 (wells fargo. not my bank but it worked!)

    Marc.

  11. Thanks for the update, Marc. Honestly I had trouble last time I was trying to do this and have just been inputting manually the last few months.

Comments are closed.