OFX import: margin interest & other misc transactions

[hub]

Mark,

FM seems to ignore OFX transaction type "DEBIT". Interactive Brokers use it for margin interests and account mininum fees, foreign taxes etc.

However, when I download from AMTD, margin interest are imported. So they must encode their OFX differently. What can I do to make FM recognize IB's version? I don't mind using a text editor to replace some strings. I don't know how to intercept AMTD's OFX statement to peek inside.

If you can make FM recognize these in say ver 10.x, that would be great. I pasted two examples below.

Thanks,
Bob

Examples attached:
<INVBANKTRAN>
<STMTTRN>
<TRNTYPE>DEBIT</TRNTYPE>
<DTPOSTED>20090603202000.000[-4:EST]</DTPOSTED>
<TRNAMT>-100</TRNAMT>
<FITID>20090603.U360458.e.HKD.1291387879</FITID>
<MEMO>HKD DEBIT INT ADJ FOR MAY-2009</MEMO>
<CURRENCY>
<CURRATE>1.00</CURRATE>
<CURSYM>HKD</CURSYM>
</CURRENCY>
</STMTTRN>
<SUBACCTFUND>CASH</SUBACCTFUND>
</INVBANKTRAN>

<INVBANKTRAN>
<STMTTRN>
<TRNTYPE>DEBIT</TRNTYPE>
<DTPOSTED>20090806202000.000[-4:EST]</DTPOSTED>
<TRNAMT>-100</TRNAMT>
<FITID>20090806.U360458.e.HKD.1358054895</FITID>
<MEMO>JUL BALANCE OF MONTHLY MINIMUM FEES</MEMO>
<CURRENCY>
<CURRATE>1.00</CURRATE>
<CURSYM>HKD</CURSYM>
</CURRENCY>
</STMTTRN>
<SUBACCTFUND>CASH</SUBACCTFUND>
</INVBANKTRAN>

[Mark]

Hi Bob,

These are "investment Banking" transactions. You'll notice an option on the OFX/QFX import dialog to "Import "Investment Banking Transactions" Into Default Cash Account(s)". If you turn on this option, and you have a default cash account assigned for the sub-portfolio, then these will be imported there. You can check your default cash account assignment under the "Properties..." of the sub-portfolio.

Just FYI, you can see the OFX file when you retrieve transactions. It is left in your Fund Manager log folder. It is available in the original reply format, and also a couple easier to read formats. See the "Technical Details" section at the bottom of this page for details:

http://www.fundmanagersoftware.com/tintrtv.html

[hub]

Thanks.

It seems that I need to create the cash investment before the OFX import. If I just create a new sub-portfolio, then import OFX with the "investment banking" box checked, as well as the "cash investment" box, no margin debit tranx etc. ever gets imported or shows up in the preview window. However, if I create the default cash investment manually first, then things work. Can you make it so that default cash investment does not have to be created manually before OFX import? Maybe it is already fixed in beta v10.

Now I have a different problems.
This is more of a feature request. FM doesn't seem to recognize the currency tag in OFX, so all HKD stocks are still created as USD investments. I am using the following portfolio tree

-Single Interactive Brokers account
|--USD subaccount
|--HKD subaccount

It would be nice if there is a flag where you can automagically parse through the OFX, and just pluck out the transactions with a currency type matching that of the subaccount and create new investments with the right currency type. (IB generates all multi-currency transactions in the same OFX file.) IB's OFX file does have the <CURRENCY> tag under <POSSTOCK>.

[Mark]

Hi Bob,

Yes, that investment banking transaction into a new sub-portfolio with no default cash account issue has been fixed in v10.

FM doesn't read the currency tags in an OFX file currently. This is something fairly easy to add and a good thing to do, so we'll plan on implementing this for v10. In the OFX file, the CURRENCY aggregate is optional, but if it exists, and has a CURSYM field, we'll use that to compare against your defined currency symbols under "Options / Currency Settings...". If there is a match, then we'll create that new investment using the matched currency.

[hub]

Thanks, Mark!

IB has <CURSYM> for at least the following tag types

<BUYSTOCK>
<SELLSTOCK>
<INVBANKTRAN>
<POSSTOCK>

After years of paying for Quicken upgrades, I am sold on FM. I am looking forward to the release of production version of v10.

If you ever need an source of OFX files generated by IB, just email me.

Bob

[Mark]

Hi Bob,

Great, welcome to Fund Manager. Thanks for all your help/feedback.

[horton]

FYI I had a similar problem with imports from IB using 9.x and then 10.x. I created a default cash account manually and that resolved the issue. I tried Quicken too and the files imported without any manual intervention but the code on each transaction (these were the initial deposits) was "MiscInc", which seems odd.

[StephenL]

I have picked up this thread because I too am looking to import transactions tagged <INVBANKTRAN>/<STMTTRN> such as FX trades, withholding tax on dividends, funding of the account in the Interactive Brokers ofx file I want to import. I have now organised my IB portfolio with currency sub-portfolios (so JPY, GBP, USD, EUR) and default cash investments for each currency (JPY cash in the JPY sub-portfolio etc) as discussed in this thread, and have checked under the 'Advanced Import Options' that the 'Import Investment Banking Transactions' box is ticked. For some reason I have 2 issues when I 'Preview Importing Transactions':

1. There are none of the <STMTTRN> transactions, despite my belief that I have followed the steps outlined in this thread. I have imported JPY transactions and the cash transactions for <INVTRAN> items (buys/sells/gross dividends) are correctly reflected. When I rerun, looking for the [Investment Banking Transactions] differences (taxes on dividends, FX transactions) to appear as 'JPY Cash' items in the 'Preview', they are not present.

2. From what was said on this thread, I think I should be able to have JPY investments and transaction entries in the ofx file automatically select the JPY sub-portfolio. However that is not the case, in order to have the correct cash default account (say JPY) present I need to actually look to import all transactions into the JPY sub-portfolio, then deselect anything not JPY. Also, the currency can be seen by reviewing the transaction detail from the 'Preview' screen, but is not on that screen itself. I am unclear on the set-up required for this data flow to work as I hoped.

I would be grateful for guidance in respect of both of these issues.

[Mark]

Hi StephenL,

When you import non-investment banking transactions (buy, sell, distribution, etc), FM looks for any investment underneath the sub-portfolio being retrieved for a matching CUSIP, and puts the transaction in that investment. It then finds what the default cash account is for that investment, and updates it if appropriate. This works well if you have sub-portfolios by currency, and want to update different cash accounts for each currency.

Investment banking transactions do not specify a CUSIP, so are always applied to the default cash account of the sub-portfolio being retrieved. This means if you have sub-portfolios by currency beneath this top level account sub-portfolio, those default cash accounts don't matter for the purposes of investment banking transactions. So, check that you have a default cash account assigned at the account level sub-portfolio, that you are retrieving. For example:

Code: Select all

- Master
  - My Holdings
    - Broker ABC
      - JPY Holdings
      - USD Holdings

In the above example you would enable transaction retrieve at the "Broker ABC" level, and that is where you have the account number defined, and that sub-portfolio's default cash account would be updated with Investment Banking transactions.

[StephenL]

Thank you for your reply Mark. I have done more work, but still have problems.
My setup is an Interactive Broker portfolio in FM under ‘Master’ with currency sub-portfolios under that with default currency cash investments in CHF, GBP, EUR, JPY, USD. EUR is my base currency, and I have set up the CHF->EUR etc investments and these are shown at ‘Master’ level.

When I use the OFX file downloaded from IB directly, I have the problems described in my earlier post. So I see no Investment Banking Transactions, and the Securities transactions do not filter down to the correct Sub-portfolios, even though the investments are all already set up in the correct sub-portfolios.

To try and see if I could progress, I decomposed the original OFX file. I retained ‘common’ sections, and allocated Transactions (e.g. framed by <BUYSTOCK>, </BUYSTOCK>) and Positions by Currency. The resultant OFX files by currency could still be read by Fund Manager, and now, when I import these to their respective Currency sub-portfolios, I get following results:
- Security transactions seem generally OK (they were before of course).
- The one exception was in relation to a Corporate Action: the tendered securities were offered for import in each portfolio bar CHF (my fault I think as not analysed by Ccy by me), and the distribution in USD made as part of the corporate action was not picked up (treated as TRANSFER in OFX, with a value per share).
- CHF has and had only INVBANKTRAN – for some reason I saw nothing at all directly in this sub-portfolio
- Foreign Exchange trades were picked up in the ‘secondary’ currency only, e.g. EURUSD in USD only. Presumably this may mean that in FM you need each side presented as its own cash movement. Also there is an issue with the data provided by IB, in that the commission payable in EUR seems to impact the (say) USD amount I see in the OFX file, i.e. slight errors would result.
- In general, the INVBANKTRAN items do now appear for import (barring CHF as above). But they seem to appear with the values they would have had as EUR although imported as (say) USD. So a USD 4.50 withholding tax is imported as USD 4.05. This does not impact the Funding Transactions or general fees which were all in EUR (default currency) or CHF (could not see anything), but all other entries I saw, so FX trades and withholding in particular.
- Having manually adjusted imported data and added anything missing I can at least reconcile my cash position for the period I did this for.

I’m not wild about it, but if I must I can continue improving my means of manipulating the OFX files I receive to finagle what is imported into FM by currency. But I suspect my problem is setup in some way, and possibly I’m not asking for the right data from IB. Naturally my preference is to use the file as is from IB and hopefully find a way to correctly set up in FM to use it as best it can be used – given that the FX data is in part inadequate as noted. I am completely stuck with this approach, and hope that you will be able to help, and that my recent experiments provide a clue for you.

[Mark]

Hi StephenL,

I see no Investment Banking Transactions

So, is your hierarchy like this:

Code: Select all

- Master
  - IB
    - CHF
    - EUR
    - USD...

When you import, are you choosing the option to import into portfolio "with matching account number as OFX account"? You should. Also, make sure you have the account number specified at your "IB" level, and not anywhere else. Make sure you have a default cash account assigned at the IB level. This should cause all your Investment Banking transactions to go into the IB default cash account.

the Securities transactions do not filter down to the correct Sub-portfolios, even though the investments are all already set up in the correct sub-portfolios

Verify that you have the correct CUSIP assigned to your investments, and that it matches the CUSIP in the OFX file. If you have the matching CUSIP assigned (and don't have more than 1 investment with that CUSIP), the incoming transactions will go into those existing investments, rather than trying to create new investments.

[StephenL]

Thank you for your quick response Mark.

The hierarchy is OK, but I will now try getting rid of the account numbers below the IB level, as you recommend.

The Cusips should all be OK, because I picked up the securities from IB in the first place and left the Cusip/ISIN field exactly as was. However I have added an additional ISIN field (replacing Custom 1) for data export purposes - would that make any difference on import?

Once I have tested and hopefully find the hierarchy properly works, I may still want to explore with you what happens to the investment banking transactions out of base currency.

[StephenL]

Mark,

This worked. The Preview is confusing - all the destinations are shown at IB level, though once I had gathered my courage and completed the import the transactions did flow down as required.

So thank you very much and I'll review more properly tomorrow.

Stephen

[StephenL]

I now have an overall process I am comfortable with (though I am still exploring pricing and reporting) given that there are issues with the data in the original file from IB that I cannot currently resolve. It does still involve my decomposing the original OFX file by currency, and amending FX trade errors in the original data when it gets to FM, but that is no problem. But I would still like to improve the process in two respects:

- I currently recode INVBANKTRAN of TRNTYPE DEBIT/CREDIT transactions to being FEE in the OFX file I present to Fund Manager, where this is a charge or refund of a charge by IB, as I want to treat this as affecting performance. However I realise now that anything read as FEE is treated as a charge, so at present I find and delete both entries in Fund Manager, obviously not optimal from my perspective.

- I ensure FX trades are present in the OFX files of both currencies, treating them both as payments in/out (i.e. TRNTYPE DEBIT/CREDIT) not, as I would prefer, as affecting overall performance.

I believe that I should be able to use the 'Distribution Label' functionality to do what I want, either by redefining Distributions I will never need, e.g. ST/MT/LT Cap Gain, or by using the two User Defined Distributions - probably both work. However I have not found anything specific on my issue in the documentation so it would be really helpful if you could outline:
- How you interpret TRNTYPE in the OFX files presented to Fund Manager to determine the Distribution. If I define "FX trade" as a Long Label and "FX" as a short label, can I use one or the other as a TRNTYPE in my OFX file and it will be understood by Fund Manager? Are there reserved names in the OFX file I should not use, such as FEE?
- Which of the Distribution types you have are in effect sacrosanct, or have the same restriction as 'Account Fee', i.e. it is assumed a charge or credit without reference to the sign preceding the transaction amount.

Many thanks for your help.

[Mark]

Hi StephenL,

Sorry, but you can't control where transactions go based on the distribution label. They are hard coded to go into the original named distribution, based on the OFX transaction type. For example, a dividend will go into the original named dividend distribution type, even if you were to rename it to display as something different. There are many different OFX transaction types, and we don't have them documented, but generally they behave as you would probably expect. You can either test it, knowing the name of the distribution doesn't matter, or if you have a specific transaction question, I can investigate and answer for you.