OFX import: margin interest & other misc transactions

[StephenL]

Thank you Mark. OK, I think if I understand correctly that probably means that:
- I cannot use a User Defined Distribution because that is undefined as a mapping
- I cannot use Dividend/various Capital Gains/Amortisation/Accretion/Return of Capital (I think) without a Cusip (or ISIN) unless I add a dummy identifier in the Fund Manager system to the Default Cash instruments (which may be my chosen option)
- I could probably use Interest Income, Foreign Tax but would not want to, and Other, but this may not be defined as a mapping

Is this basically correct?

[StephenL]

Mark,

Please read and let me know if my thinking is out in the below. Also, if you are aware of someone in your community who has already solved for FX trades being seen as part of the overall portfolio performance, please set me straight.

I've thought about this a bit more. I think it is too complicated and inherently risky to rebrand and recode FX transactions as trades in a dummy security, because the relationship between that dummy security and the default cash account is problematic, whether the dummy security is new or is in fact part of the setup of default cash. I also think that my longer term solution does not require that.

So I believe I will need to stay within INVBANKTRN, which simplifies the analysis.

I'm assuming you use OFX Spec 2.2 and having looked at this and tested to see what happens when I pass tags and values to Fund Manager, I think that in essence if I have a Debit it needs to be tagged FEE or SRVCHG if I want to see this treated as impacting performance, both of which are read by FM as reinvested Account Fees, and if a Credit it needs to be tagged INT, which is read as reinvested Interest Received. (Other tags seem to default to Buy/Sell.)

I assume I can then manually reset the Distribution Type in Fund Manager as desired, including to relabelled or user defined types.

I see this as a temporary fix, but one that I will probably use for a year. I have used the Generic Transaction Import for another broker, and from your documentation of it, if I ultimately format to that I believe that I will have the flexibility I need. My plan is therefore to do this, but there are certainly issues I need to resolve first in respect both of my overall understanding and in respect of data issues outside your world.

Kind regards
Stephen

[Mark]

Hi Stephen,

Yes, you can do this. It is sounding complicated. You can rename the distribution types to anything you want, without affecting how the import works. Do you think you could re-state the underlying problem you're having with importing FX transactions? Do I understand the problem is that all your FX trades are recorded as investment banking transactions, thus you have no easy way to put them into separate default cash accounts, by currency?

[StephenL]

Mark,

Thank you very much - I think I can manage for the interim based on your feedback.

The OFX (and the QFX file is the same plus worse in other respects) have following issues re FX trades:
- The data are incorrect, in effect through mistreatment of commission being translated for this OFX file in the wrong currency. This can only be resolved by correcting manually after the numbers are in FM
- The trades are shown as Investment Banking Transactions only - actually I can live with this. Many years ago I worked for Morgan Stanley and their treatment of currencies as securities you trade caused a lot of headaches.
- The trades are 'one sided' i.e. a trade selling Euro for USD is shown only in USD, so I have to generate another transaction for the other currency to insert into the OFX file.

Because IB does not support reporting for German taxation purposes I had previously reverse-engineered the various transactions and then did the appropriate taxlot processing using German rules starting from a download of the FX rates they use and a csv version of the IB statement, which is not of course a database, so not a very stable starting point. When I move to Switzerland the most complex processing requirements go away and I can focus on just making the process of reading the transactions/balances and performing reconciliations much stronger, then presenting the results to your Generic Transaction Import. But I won't do that properly until after I am completely done with German tax, so I need an interim solution for a year at minimum.

One of my reasons for buying Fund Manager was to rid myself of a lot of legacy stuff I have put together in Access over the years, and though (unless I ditch IB) I will not quite be able to do that completely, I am very confident this will work well for me.

Kind regards
Stephen