Hacking Quicken to import OFX transaction exports in foreign currencies

July 24th, 2010 · 26 Comments

So, earlier this year my wife and I relocated to the UK for a period of time due to work reasons. As part of the move, we’d opened up a checking account at HSBC but had been unable to import transactions into Quicken because the bank only allows downloads in QIF, OFX, and CSV formats rather than the “WebConnect” Quicken wants to import.

ASIDE: Intuit are out to screw you, the user of Quicken, by not using the open industry format OFX.

WebConnect is Intuit’s special format that requires Quicken to validate an embedded bank code value before it will let the import succeed. This code value is something that Intuit sells to each bank, at an apparently not cheap price, or else more bank’s would have it. Although, I suspect another very real cost is that of supporting this special, proprietary format – coding, testing, maintenance, etc.

However, we can hack around the proprietary nature of WebConnect as it turns out this format is really OFX with one special (badly formatted) tag added. And in fact, you can download an OFX file, edit it manually, then import it into Quicken. You can even do this for a UK bank export into a USA install of Quicken! Here are the steps I took to do just that:

Visit the bank website and export your transactions in OFX format.
Edit the file in a text editor. I used Notepad++ on a Win7 box.
Search for the string “</SONRS>” in the file. This may be in the middle of a line or on its own line, depending on the bank export. Either way, it should have come immediately after a “</LANGUAGE>” string (or the previous line should end with that.)
Before the “</SONRS>”, insert a new line or phrase that is “<INTU.BID>” followed by any valid bank code. You can find these on your machine in (for Quicken 2010 running on Windows XP) “C:\Documents and Settings\All Users\Application Data\Intuit\Quicken\Inet\Common\Localweb\Banklist\fidir.zip” which contains a file called fidir.txt. Open that and look for a bank code you’d like to use. If you have a different version of Windows or Quicken, the file location will need to be adjusted. Earlier versions of Quicken didn’t zip up the fidir.txt file so you may not have to unzip anything in that case.

Note for those familiar with XML file formats: This is a purposely malformed XML tag with no closing! Do not insert a matching </INTU.BID> at the end!

In my particular case, for using the HSBC US bank account code, my edited file looked like:
. . . </LANGUAGE> <INTU.BID>1270 </SONRS> . . .

Don’t forget to save the edited file. But save it with the file extension “.qfx” instead of “.ofx”!
There is one other catch, if you try importing this into a USD (US dollar) install of Quicken, you’ll get an error like:
Downloaded data does not match the currency of your Quicken account
This is because your exported data includes a tag indicating it is for a foreign currency, in my case for HSBC UK that would be GB Pounds (Sterling). This is specified just a few lines further down in the .qfx file with something like:
<curdef>GBP</curdef>
Simply change that to the standard currency symbol for US Dollars, USD, and your file will import without error. Here’s an example of the line edited:
<curdef>USD</curdef>
Do not worry if the account you’re trying to import into in Quicken is setup for Pounds, Quicken’s check is only to match the default install currency, not the per account currency!

NOTE: Quicken will NOT convert amounts from USD to GBP on import either!
As a last step, import this into Quicken. Navigate the application menu: File–>File Import–>Web Connect File and in the resulting dialog select your edited .qfx file. Quicken will pop-up a dialog to ask you which account to match to, after which it will show a few more to indicate progress as the import is done.

Finally, it may also ask you if you want to turn on “One Step Update” for this account. Do NOT do this as your account does not really come from the bank Quicken thinks it does, so it won’t be able to automatically download new transactions for you. You’ll need to download them manually each time.

26 responses so far ↓

1 Nepa // 2010.07.27 at 10:07 am

To convert CSV and QIF to regular OFX try iCreateOFX Basic from http://icreateofx.com/Convert-CSV-to-OFX
2 matsch // 2010.08.19 at 11:42 pm

It worked just the way you described. I can’t believe how sick I am of Intuit, who would have thought I’d be thinking that MS Money was still available?
3 Robert // 2010.09.04 at 4:36 pm

Thanks! I followed the instructions above and it worked great.
4 Chris // 2010.11.12 at 8:10 pm

The instructions worked perfectly. Now has anyone done a script so that you can update your file automatically.

If only Intuit would listen to their Customers.
5 QuicknPerlWiz // 2011.01.07 at 5:19 am

I see someone else has figured out what I figured out too, but I thought I would point out something the comment about having a program to do this automatically. The QFX format is copyrighted by Intuit. So even though it is basically just OFX with a sprinkle of financial information thrown in, anyone that creates a program to create a QFX file and then goes out and makes it public, risks Intuit coming down on them.
6 QuicknPerlWiz // 2011.01.07 at 5:27 am

P.S. The whole purpose making QFX over using OFX which Intuit, Microsoft and the banking industry created is so that Intuit could charge for the support it needed to provide the financial institutions and of course a money stream, after all Intuit is a business not a charity. People might hate the fact that Intuit created this cost that the financial institutions are now trying to pass on to customers and such, but the fact that Microsoft gave up on MS Money and OFX (which it couldn’t charge for) might be an indication of what was the better approach to running a business providing “personal finance” software.

Now Intuit dropping out of the UK market altogether is a whole another subject, but I guess they dropped out for the same reason Microsoft dropped out, the cost was greater then the gain for some reason.
7 Jaco // 2011.01.12 at 1:45 pm

Hail the writer of this tip! It is thanks to people like you that the world will become a better place to live in! Ok, and Wikipedia too but thats besides the point. Im just thankful Im not the only person living outside of the US, companies like Intuit would have succeeded in letting me feel left out in the dark.
8 dave // 2011.04.01 at 2:35 am

I had been looking for such a solution for a long time I live in UK and have always used quicken from 1995, when they decided to pull out of UK I was well disapointed have had to use old versions until now I bought a USA copy of 2010 last year as the quicken web site intimated that it used ofx format which of course was a lie , they are anally retentive at quicken, with just a small software tweak it could work worldwide but no they want their pound of flesh from the banks for their stupid QFX file download, Thankyou so much I am now a very happy guy even though it has to be modded every time very quick and easy to do thanks once again
9 Sergiy // 2011.04.05 at 8:18 pm

Here is a little tool that does the hack for you: OFX2QFX

Don’t forget to set the “Force currency” checkbox.

It will even help you select the INTU.BID, insert it in the file and update the currency for all accounts on the file.

And it supports command line mode for automatic conversion.

$.Note: I developed this tool.
10 mak // 2011.07.06 at 6:03 pm

I followed the steps but quicken say it cannot verify the financial institution and will try again later. error [OL-220-B]
11 dave // 2011.07.08 at 4:39 am

hey mak there is something you haven’t done , it does work you need the currency to be USD not GBP
12 JAW // 2011.07.14 at 5:23 pm

it seems that I am the only quicken2009 user in Poland, it is obvious that there is no bank linked to quicken in Poland, I used to convert ofx files into qfx manualy, i.e. by adding 3000, converting PLZ into USD, addind bank.id. 00000001. Unfortunately, this method has stoped working. Does anybody know why?
13 davmp // 2011.07.14 at 8:19 pm

@JAW, have you tried a different bank id by any chance? Quicken probably has someway to stop a given bank id from working if that banks stops paying them the licensing fee for ‘qfx’ format.
14 dave // 2011.07.16 at 3:02 am

I would refer you to sergiy’s input up the page it works a treat and for not a lot opf cash
15 JAW // 2011.07.16 at 3:52 pm

dave, I tried sergiy’s trial version without positive results, I will come back to this problem later cause am leaving for holidays, thanks for your and davmp comments. JAW
16 JAW // 2011.07.27 at 4:02 pm

guys, I tried to sort out my problem, however without any positive result, now I look for a helpful eye that could look at my example *.qfx file in order to check what is wrong with it despite of adding required elements. i am quicken2009 premiere user, thanks in advance. jaw
17 dave // 2011.07.30 at 12:38 am

Hi JAW can you copy and paste the first part of the OFX download file then the error can possibly be spotted it really can only be the bank identifier or the currency setting , the other thing is are you saving the converted file as .QFX?
18 JAW // 2011.08.02 at 5:23 pm

Hi Dave, see the latest qfx file requested part below, following my practice, I’ve inserted 3000 line, changed he currencyDEF from PLN into USD, changed bank ID into 00000001. I found such instruction on the net. Should you prefer communication in this respect by e-mail, let me know your e-mail address. I appreciate your help! BR JAW

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

0 INFO
20110802135522 ENG 3000 0
0 INFO
USD 00000001 1111327966 CHECKING
19 JAW // 2011.08.02 at 5:30 pm

upps, the file does not show up correctly, I changed symbol ”
?SIGNONMSGSRSV1>
?SONRS>
?STATUS>
?CODE>0
?SEVERITY>INFO
?/STATUS>
?DTSERVER>20110802135522
?LANGUAGE>ENG
?INTU.BID>3000
?/SONRS>
?/SIGNONMSGSRSV1>
?BANKMSGSRSV1>
?STMTTRNRS>
?TRNUID>0
?STATUS>
?CODE>0
?SEVERITY>INFO
?/STATUS>
?STMTRS>
?CURDEF>USD
?BANKACCTFROM>
?BANKID>00000001
?ACCTID>
?ACCTTYPE>CHECKING
?/BANKACCTFROM>
?BANKTRANLIST>
?DTSTART>20110801
?DTEND>20110802210000
?STMTTRN>
?TRNTYPE>DEBIT
?DTPOSTED>20110802120000
?TRNAMT>-9750.76
?FITID>201108020001
?NAME>Zlecenia różne
?/STMTTRN>
?STMTTRN>
?TRNTYPE>DEBIT
?DTPOSTED>20110801120000
?TRNAMT>-114.98
?FITID>201108010001
?NAME>Smyk Poznan
?/STMTTRN>
?STMTTRN>
?TRNTYPE>DEBIT
?DTPOSTED>20110801120000
?TRNAMT>-3000.00
?FITID>201108010002
?NAME>A4360419/PEKAO SA PUSZCZY
?/STMTTRN>
?STMTTRN>
?TRNTYPE>DEBIT
?DTPOSTED>20110801120000
?TRNAMT>-3000.00
?FITID>201108010003
?NAME>A4360419/PEKAO SA PUSZCZY
?/STMTTRN>
?STMTTRN>
?TRNTYPE>DEBIT
?DTPOSTED>20110801120000
?TRNAMT>-3000.00
?FITID>201108010004
?NAME>A4360419/PEKAO SA PUSZCZY
?/STMTTRN>
?/BANKTRANLIST>
?LEDGERBAL>
?BALAMT>38551.60
?DTASOF>20110802135522
?/LEDGERBAL>
?/STMTRS>
?/STMTTRNRS>
?/BANKMSGSRSV1>
?/OFX>
20 JAW // 2011.08.02 at 5:36 pm

dear readers sorry for the above mess, I replaced symbol ”
?SIGNONMSGSRSV1>
?SONRS>
?STATUS>
?CODE>0
?SEVERITY>INFO
?/STATUS>
?DTSERVER>20110802135522
?LANGUAGE>ENG
?INTU.BID>3000
?/SONRS>
?/SIGNONMSGSRSV1>
?BANKMSGSRSV1>
?STMTTRNRS>
?TRNUID>0
?STATUS>
?CODE>0
?SEVERITY>INFO
?/STATUS>
?STMTRS>
?CURDEF>USD
?BANKACCTFROM>
?BANKID>00000001
?ACCTID>
?ACCTTYPE>CHECKING
?/BANKACCTFROM>
?BANKTRANLIST>
21 JAW // 2011.08.02 at 5:38 pm

Dave, once again sorry for the above. I cannot display the content of qfx file correctly. May I send you this by e-mail directly. thanks. JAW
22 dave // 2011.08.02 at 5:58 pm

Hi JAW The only thing I can see from your download file is it is missing a the other side of the USD, I would also try 01270 as the bank ID . I do know that one works
regards
Dvae
23 luca // 2011.12.22 at 1:27 pm

Hello, i can´t convert this file to QFX

0
INFO

20111216011606
ENG
3000

0

0
INFO

USD

00000001
720
SAVINGS

Activity
20111215120000
11263712.02
2221345

Activity
20111215120000
-10555812.03
2222175

Trade Date Activity
20111215120000
2483073.83
3233544

3190973.82
20111216011654

3190973.82
20111216011654
24 Frank // 2012.01.06 at 10:51 am

Here is what my insert looks like “PNC01270″ but it still rejects it with ” Quicken is unable to verify the financial institution information for this download. Please try again later. Did I miss something?
25 MM // 2012.01.26 at 5:25 pm

This is what I did to fix the “Quicken is unable to verify the financial institution information for this download. Please try again later.” –
Change the value for to “000000000″ (that’s 9 zeros) in the ofx file, to be saved as qfx ( is found about 10 lines down from where you inserted the bank code.) After making this change, the Quicken import web connect file process immediately opened a window allowing linkage to the correct existing account.
26 MM // 2012.01.26 at 5:30 pm

Edit: Missing from my post above are the data field names because the “less than” and “greater than” symbols were stripped — insert the 9 zeros after BANKID which is about 10 lines down from INTU.BID.