Hacking Quicken to import OFX transaction exports in foreign currencies

July 24th, 2010 by davmp · 48 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:

  1. Visit the bank website and export your transactions in OFX format.
  2. Edit the file in a text editor. I used Notepad++ on a Win7 box.
  3. 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.)
  4. 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”!

  5. 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!

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

More on this topic (What's this?) Read more on Banking, Exports at Wikinvest

Tags: Personal Finance → 48 Comments

Covered calls: Getting out early

November 11th, 2009 by davmp · 2 Comments

One of the things I’ve recently started to do with my covered call trades is to try and exit the position early by trading a little bit of the maximum profit for a reduced risk of losing money.  Between Sept and Oct expiration, I attempted that by setting a limit credit order for each of my positions.  But this month, I’m trying a limit BTC order for just the call option itself.

Trying to fit all the pieces together in regards to covered calls

Trying to fit all the pieces together in regards to covered calls

Why the change?  Well, let’s back up a bit first and explain what I tried last month.  A limit credit order is one of the few ways TradeKing will let me completely liquidate a covered call position using a single order, where that order matches up with something I want to do.  What I’d actually like to do is to place two orders, one to capture a target gain and liquidate, and the other to accept a loss and also liquidate.  The idea underlying both of those trades is to manage risk by limiting losses, a skill that is critical for any trader, through minimizing unnecessary exposure to the market.  In my case, where I can’t watch the market throughout the day due to having a “real” job, the best I think I can do is to enter two orders to represent the two endpoints of the spectrum of possibilities I find acceptable.

However, it turns out TradeKing won’t let me do a OCO (one-cancels-other) order against a covered call as they claim it is too complex to implement in their platform.  So instead, last month I found myself forced to pick one side of the spectrum to cover with an order, and since things looked mostly positive throughout the month, that was a limit credit order.  Which means, my position would liquidate if I could capture at least X dollars in the process.  Last month I set X to be about 100% of the max potential profit for each position, and the trades never got filled — not really a surprise looking back on it.  Note that for the other side (limiting total losses), I’d just have to manually monitor things on a periodic basis and adjust as needed.

Now, in November, where things are looking down across all three positions I’m in, I really want to place orders to cover the other side of the spectrum.   Unfortunately, you can’t use a limit credit order here because you only want the order to be placed should the potential credit drop below a threshold, not should it go above a threshold (it already is above the threshold I’d set!)  Instead, I could try a contingent credit order.  However, at TradeKing the contingency may only be based on one part of the position — either the underlying or the option but not both.  To me, that makes it too easy to trigger at the wrong time, thus causing me to get out too early or to take more losses than I wanted.   Plus, there’s another potential problem too.

That additional problem is that an early dip in the prices of the underlying could trigger a full liquidation when there are multiple weeks left before exercise.  IMO, a better solution would be to BTC (buy-to-close) the option position at minimal cost, say 5 cents, leaving me with a long position in the underlying and a little bit of captured profit on the call option.  Still having the underlying gives me the opportunity to re-evaluate it, where I may find out that the dip isn’t sustained, and I can then resell a new call for even more income in the same month.  On the other hand, my re-evaluation may indicate that I really should sell and I’m back at a full liquidation.   I’m thinking this extra step to full liquidation is well worth the trouble it takes to handle two separate orders.   Especially since it’s trivially easy to enter a BTC on the option as soon as the position is established.

Keep in mind that for the option price to drop to such a low trading value, it means the underlying has decreased in price, the time premium has decayed, or volatility has dropped.  In all but one of those situations, it’s really a good sign that I could buy back the option for $0.05 as it gives the possibility of higher than my targeted maximum profit.   And even when it’s a bad sign, like I said above, I may not think it’s as bad a sign as the market thought, and if the underlying comes out of its dip, I may also find myself with greater profits than originally expected.   So, this type of closeout can easily result in “letting the bulls run.”

Currently, all three of my covered call positions have an open order to BTC the call options for a limit of $0.05.  I’ve seen a few last trade numbers go by that were exactly $0.05, but unfortunately mine weren’t the ones being filled.  We’ll have to wait and see how this works out over the next two weeks.

Tags: Options → 2 Comments

Belated: Covered call with MBI

November 10th, 2009 by davmp · 1 Comment

And yet another catch up on trades completed but not yet blogged about.  A position using MBIA Inc (MBI) as the underlying is the last of my covered calls done within the GI portfolio for November expiration.  Sadly, it’s been too long since I initiated the position for me to remember exactly why I went ahead with this trade.  Which is really another way to say I may be doing too much experimentation and not enough of sticking to a plan.

I should have given this to myself two weeks ago in exchange for my thoughts at the time

I should have given this to myself two weeks ago in exchange for my thoughts at the time

All I can say at this point is that I either need to write about my trades sooner, or else I need to adhere better to a known set of strategies for picking positions each month.  My best guess at what I was thinking is that MBI may have been treading its bottom for long enough, and with growth starting to pick up in the overall economy, MBI might finally start to pick up again.  MBI currently seems to have one of the lowest P/S (price-to-sales) ratios of its peer group, which includes ABK, AGO, WRB, and MKL.  But what I appear to have missed at the time I made this trade was that MBI also has an absurdly high total debt-to-equity ratio of 392.  More than 3 times higher than any of its peers!

For November expiration, this looks to be the second time (of only three positions!) that I really tried to force something, and that’s not a good thing to look back on only two weeks later.  I know that I was constrained by the amount of money left over to establish this third position, so perhaps I just simply decided to take on a little extra risk with this smaller portion of my portfolio size?   But that’s really no excuse for forcing things, is it?

The best thing I can say about MBI at this point is that it has a high historic volatility (currently a 30-day of 87% according to TradeKing) which should lead to higher time premiums should I end up selling additional calls against it.   Anyway, without further ado, here are the details of the position I took and the possible outcomes.  As usual, I’m including my actual commissions incurred at TradeKing and thus some numbers go out to fractions of a cent.

Critical dates
2009.10.21: Initial position: BTO 100 MBI @ $5.2465
2009.10.21: Initial call option: STO 1 MBI KY (Nov09 $6) @ $0.290253

Summary, if not called
Days position held: 30
Capital investment: $1,573.95
Income received: $87.08
Percent return: 5.53%
Annualized yield: 92.54%

Summary if called at expiration
Days position held: 30
Capital investment: $1,573.95
Net profit: $308.18
Percent return: 19.58%
Annualized yield: 780.72%

As of the date of writing this post (Monday, Nov 9th but it won’t be posted until tomorrow) MBI is trading in the pre-market with a $4.51 mid-point between the bid and ask.  That’s currently under my cost basis of $4.96/share but I do still have two weeks until expiration.  There’s alot of time for further price movement, both good or bad.

More on this topic (What's this?) Read more on MBIA at Wikinvest

Tags: Trades → 1 Comment

Belated: Another dance with AFL

November 9th, 2009 by davmp · Comments Off

Continuing my catch up on recent trades, I did another covered call against AFLAC (AFL) for November expiration.  This time, I stepped back from using a screening tool and simply looked for an opportunity where the underlying position was something I’d feel comfortable holding for a couple months if I got caught holding it past expiration.

Things go up, then they go down, and hopefully up again.

Things go up, then they go down, and hopefully up again.

Don’t get me wrong, I still have other constraints to consider. Like the size of my portfolio, and portion of available funds within that portfolio, which certainly limits the stocks in which I can take a 100 share position in.  While most investing guides indicate that you shouldn’t put a large percentage of your portfolio in a single investment, I’m pretty much doing the exact opposite.  That’s because (a) this portfolio is a small percentage of my overall investments, and (b) I’d rather take a large position in something I’m comfortable in than many small positions in something I’m not.   So, when looking around for opportunities after October expiration, I was looking for something that required up to, but no more than, $6,000 in capital to establish.

I again started out by looking through the Dividend Achiever’s list, trying to find something that would both be highly likely pay a dividend prior to Nov expiration, have a good time premium, be a business I believed in after doing my background research, and not require more than my $6,000 in capital.   Fortunately, AFL looked like it had an ex-dividend date coming up, had good premiums on calls, and still seemed to be doing well as a business (Good P/E ratio, P/S ratio, and I still like AFL enough to have long-term long positions in it.)  Plus, I have previously done a covered call against AFL in my GI portfolio.  And it would easily fit within my capital requirements.

Here’s the summary of the position I took and possible outcomes.  As usual, my actual commissions at TradeKing are factored in, and thus some numbers go out to fractions of a cent.

Critical dates
2009.10.21: Initial position: BTO 100 AFL @ $45.5195
2009.10.21: Initial call option: STO 1 AJO KY (Nov09 $47) @ $1.0539
2009.11.16: Dividend predicted: DIV 100 @ $0.28

Summary, if not called
Days position held: 30
Capital investment: $4,551.95
Income received: $133.39
Percent return: 2.93%
Annualized yield: 42.11%

Summary, if called 1 day prior to ex-dividend
Days position held: 25
Capital investment: $4551.95
Net profit: $248.49
Percent return: 5.46%
Annualized yield: 117.28%

Summary if called at expiration
Days position held: 30
Capital investment: $4551.95
Net profit: $276.49
Percent return: 6.07%
Annualized yield: 104.92%

I should note that I purposefully went a little bit more bullish on this position than I had been going in more recent covered call positions.  Which means, I picked a higher strike than I otherwise would have.  That hasn’t worked so well for me as of the time of this writing, as instead of going up, AFL has gone down to $42.19 as of this past Friday’s close.  That’s well below my cost basis of $44.47.  But this is certainly a position I’d just turn around and sell new calls for the next month if this month’s call expires worthless.

Tags: Trades Comments Off

Belated: New EK calls sold

November 8th, 2009 by davmp · Comments Off

I’ve been *real* busy the last couple weeks and am finally getting a chance to catch my breath and post a few things to my blog.  Since I’m trying to use my blog as a record of my attempts to grow my “Geographic Independent”-qualified income, and I’m still selling covered calls in my quest to learn about options trading, that means I should have written about my recent trades already.  Better late than never I guess.

I don't even think Kodak makes this anymore

I don't even think Kodak makes this anymore

If you’ve been reading my blog, you’ll know that October expiration left me holding 300 shares of Eastman Kodak (EK) at an unrealized loss.  I was trying to decide whether to sell new calls or simply liquidate, taking my losses, and move on to something else.   Well, I ended up selling new calls, but that’s primarily because I couldn’t identify any better opportunities.   Unfortunately, that’s likely more because I didn’t spend enough time researching than anything else.  Which, in itself, should qualify as a lesson learned: don’t put money into things because you’re “settling” due to lack of time to find something better.

So what happened?  Well, I actually completed other trades, which I haven’t written about yet, first.  So my actions on EK were delayed by about 3 to 4 trading days after October expiration.  At that point, EK had already fallen quite a bit and premiums for Nov $5 strikes were rather low.  Hoping (hey, there’s my error!) that things would bounce back a bit, I ended up placing a $0.35 limit order to STO three calls (I had 300 shares and one call is 100 shares).  Unfortunately, after another 3 to 4 trading days, the bounce had never come and in fact EK had fallen even lower.  I tried a $0.25 limit order for another 3 to 4 days with the same result – no takers.

At this point, EK was trading for well under $4.00 and the last trades on the $5 strikes were $0.05.  So I ended up trying to decide on selling either Dec $5 strikes, or Nov $4 strikes.  If I did the latter, and EK was over $4 at Nov expiration, I’d actually take a realized loss on the whole EK two-month position, but at least I’d be out of my position and have hopefully learned something along the way.  If I did the former, I’d be locked into EK for a total of two months while having only collected around $100 for the new calls.  And worse IMO, only if EK rose to $5 would I be forced to exit.  And I’d still have an overall (but unrealized) loss if EK was trading below about $4.30 at Dec expiration.

After spending a bit of time researching EK itself, and not finding anything in particular that gave me great hope of a $5 trading price by December, and also considering how earnings season was shaping up, I decided to go with the Nov $4 strikes with a $0.25 limit order.   Here’s the summary of the actual trade done.  Like usual, I’m including my actual commissions which explains why some numbers go out to fractions of a cent.

EK & EK JA: Critical dates
2009.10.17: Initial position: Holding 300 EK @ $4.62
2009.10.28: Sold call option: STO 3 EK KQ (Nov09 $4.00) @ $0.22697

Summary, if NOT called (static return):
Days position held: 24
Capital investment: $1386.00
Income received: $68.09
Percent return: 4.91%
Annualized yield: 107.38%

Summary, if CALLED at expiration:
Days position held: 24
Capital investment: $1386.00
Net profit if called: -$122.86
Percent return if called: -8.86%
Annualized yield if called: -75.63%

As I write this, EK has jumped up over the last couple days and closed on Friday at $4.23.  I’m not planning on taking any action on this position between now and expiration, unless EK drops and I can buy my calls back at $0.05.   I expect EK to range between the low $4’s and mid $3’s during that time.

More on this topic (What's this?)
Kodak Moment - Not for 4,500 People Losing Their Jobs
A Kodak Moment - Maybe Not
Read more on Eastman Kodak Company at Wikinvest

Tags: Trades Comments Off

 Subscribe in a reader