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>
. . .
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:
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:
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.