incorrect cash calculation

[ivanf]

Hi,

I'm importing historic transactions for a new portfolio I've setup.
I'm importing qfx files from IB

The first file shows an initial cash deposit of $500 AUD, and because my IB account is in base USD, the exchange rate is 0.69547, so the balance should be $347.735 USD

However, FM shows the initial balance to be $241 USD and I think it's applying the exchange rate of 0.69547 to the amount of $347 not the $500

If I import the same file into quicken, it show the initial balance of $347 USD correctly.

So I think there might be a problem interpreting the qfx file correctly ?

regards
Ivan

[Mark]

Hi Ivan,

Yes, Fund Manager is expecting all transactions in the currency of that investment. So, if your cash is in USD, it is assuming cash deposits are reported in USD.

Can you email me your .QFX file that shows this problem?

[ivanf]

[Hi Mark,

yes I can email you the file, but maybe this is a stupid question, but what is your email address ? Or is there another way to get the file to you ?

regards
Ivan

[Mark]

Hi Ivan,

I've just sent you an email, so you can reply to that with the attachment.

[Mark]

Hi Ivan,

Thanks for sending the file. I believe Fund Manager is handling this correct, per the OFX spec. Granted, the end result is not what really happened, but the way I read the spec it is being imported properly. Specifically, if you look at the key sections of the transaction in your OFX file, it is showing:

Code: Select all

<STMTTRN>
   <TRNTYPE>CREDIT</TRNTYPE>
   <TRNAMT>347.735</TRNAMT>
   <CURRENCY>
      <CURRATE>0.69547</CURRATE>
      <CURSYM>AUD</CURSYM>
   </CURRENCY>
</STMTTRN>

The OFX spec states that if there is a CURRENCY aggregate then the transaction is reported in the specified currency (AUD in this case), and has not yet been converted to the "home currency" as they call it. Since this transaction has a CURRENCY aggregate, the amount is in the specified AUD, and to convert to the home currency you must multiple by the specified rate (0.69547). If you're interested, you can go to www.ofx.net, and dowload the spec. The relevant section is 5.2, and is only about 1 page long.

I'm sure you don't care to learn the details of the OFX spec, but the bottom line is that I believe Fund Manager is importing this properly, and if the 347 number was meant to be in USD, then the OFX file is formatted incorrectly. If you would like to have this investigated further I'd be happy to talk to someone at IB about it. You might call or email them, and ask them for clarification on why the OFX file is formatted the way it is, apparently in contradiction to the spec. You can point them to this discussion, and mention they can contact us directly to resolve it.

Also, as I'm sure you're aware, you can just modify this recorded transaction in Fund Manager to be how it actually happened.

[Mark]

Hi Ivan,

One other thought... I believe on IB you can download either a Quicken formatted .QFX file, or a MS Money formatted .OFX file. If you try the .OFX file, does that work properly?

[ivanf]

Hi Mark,

yes if I import the files as MS money .ofx then it works ok

And as I posted originally, if I import the the original qfx file into Quicken, it reports it ok as well. So I understand what you say about being an IB problem, but if that were the case, I'm curious as to why Quicken imports it ok. (unless Quicken is aware of the IB format being incorrect and compensates for it...?)

I will send you the ofx file if you like to compare

regards
Ivan

[Mark]

Hi Ivan,

Interesting, thanks for the information. It would be good to compare this transaction between QFX and OFX. QFX is Quicken's version of OFX. Apparently, they treat this case differently than the standard OFX spec...

[Mark]

Hi Ivan,

Thanks for sending the .OFX file. That same transaction is shown as would be expected from the OFX spec:

Code: Select all

<STMTTRN>
   <TRNTYPE>CREDIT</TRNTYPE>
   <TRNAMT>500</TRNAMT>
   <CURRENCY>
      <CURRATE>0.69547</CURRATE>
      <CURSYM>AUD</CURSYM>
   </CURRENCY>
</STMTTRN>


I don't have a QFX spec. Fund Manager treats OFX and QFX files the same. Apparently there must be a difference here. I'll have to search and see if I can find any documentation on the QFX spec. For now, given that you have a choice, I'd stick with the OFX file.

[Beagle47]

I'll try the same. I didn't know what the problem was but had largely given up on balancing cash in FM. I have an AUD cash account (base currency) with most transactions being initially in USD.

Thanks for the explanation Mark - and Ivan for raising the issue.

Regards .......... Alan

[Beagle47]

Well, that 'fix' didn't work as well as expected. In one portfolio I buy US stocks in USD through Interactive Brokers (IB). My base currency is AUD.

Prior to seeing this thread I was downloading Quicken files from IB and importing them into FM. All was fine except for the currency conversion issues referred to in this thread. I was living with that by not using FM to monitor cash in the account.

Last week my PC fried - literally smoke coming out - and lost my HDD so working on reconstituting my setup. I took the opportunity to rebuild my US stocks database, this time using Money downloads.

This seemed to work well in that the cash transactions seem fine. The downside is that in creating the 'new' investments (US stocks) they show a currency of AUD - whereas they are of course in USD. I can change the properties on each one to USD and all seems ok then.

I assume this is a problem with the MS Money file that IB creates. Is there a quick way (as a workaround) to bulk change the currency property on all the investments in a portfolio as this seems to be an ongoing problem?

Regards ........... Alan

[Mark]

Hi Alan,

When importing transactions from an OFX/QFX file, each transaction can have a 3 character currency specified in the file (<CURSYM>). When Fund Manager comes across a transaction for a non-existent investment, it uses the following algorithm to assign the currency to the new investment:

1) If the 3 character currency in the OFX file exactly match one of your currencies specified under "Options / Currency Settings..." then that is the currency that will be used.

2) If #1 did not find a match, Fund Manager looks through all open investments from all accounts, and if it finds other investments with the same symbol, it uses the currency assigned to those other investments with the same symbol.

3) If #2 did not find a match, Fund Manager looks at all the other investments in the same sub-portfolio. If they are all the same currency, then this currency is used.

4) If #3 did not find a match, Fund Manager uses your default currency.

So, double check your currency settings, under "Options / Currency Settings...", and make sure you have a currency that matches the 3 currency symbol reported in your OFX file.

Sorry, there is no option to bulk change currency properties on all investments in a sub-portfolio.

[Beagle47]

Thanks Mark. That fits the scenario and is now fixed. My error - I imported the data before I set up the currency options and exchange rate 'investment'.

Regards ......... Alan

[Beagle47]

Thanks Mark. That fits the scenario and is now fixed. My error - I imported the data before I set up the currency options and exchange rate 'investment'.

Regards ......... Alan

[Beagle47]

Thanks Mark. That fits the scenario and is now fixed. My error - I imported the data before I set up the currency options and exchange rate 'investment'.

Regards ......... Alan