Problem of transaction seen as external

[Djobydjoba]

Hi Mark,

I've just seen the benefit of cash account for some of my sub-portfolios, and so I have to record again past transactions to record them in the cash account. Big work, but the copy and past function in the Data Register is a good time saver.

There is a small problem however. In the Investment Transactions report, with the filter "Include Only Portfolio External Transactions" on, a few transactions are seen external but they aren't. They are transactions in foreign currencies (my default currency is euro).

Here's a setup and the steps to explain the problem :

- Cash account (€)
- Investment A ($).

I sell 1000€ of Investment A. I record a redemption transaction, with value=1000*EUR/USD (I make a copy and paste from the calculator here, with maximum digits), number of shares, and let the price be auto-fill.

So, in the cash account, the corresponding purchase transaction is recorded. The value in € is correct, the same that the converted value for the redemption (of course). At this moment, the transaction doesn't appear in the Investment Transactions report as external. All is OK.

The problem is when I open the transaction in the cash account. Just an open and a close, and the transaction becomes external. I think it's due to rounding operation that is done at the opening, due to the conversion to default currency. As a side effect, the price for the share for the transaction in the cash account can be sometimes 0,9999 instead of 1. Rounding effect here.

Perhaps this recording approach is not optimal, but I don't see any other.

How can I deal with this problem ?

Thanks for your suggestions. I can provide you an example .MM4 file if you wish.

D.

[Mark]

Hi Djobydjoba,

It does sound like a rounding issue. When you open that cash transaction, what are the numbers for value, shares, and price? I assume you are pressing OK on this dialog, and not Cancel or Close. After you press OK, if you edit the transaction again, what are the same numbers for value, shares, and price?

[Djobydjoba]

Hi Mark,

I agree that's a rounding issue. I've dug into it.

The transaction has to be recorded with value in the currency of the investment (USD), and the transaction in the broker statement appears in €, with no information regarding the exchange rate used.

Here, it was a purchase for 2292,60 €

So my approach is :

A]
1) to read the exchange rate price in the exchange rate investment at the date of the transaction : 1,3017

2) calculate the value in $ : 2292,60 * 1,3017 = 2984,27742

3) Record the purchase transaction :
Delete the price filled
Value : 2984,27742
Shares : 150
Price (auto calculate) : 19,895183
OK

In the cash account, the redemption appears like this (I've copied and pasted the transaction here) :
Date Price Shares Total Fee Type Memo
21/03/12 1,000000 2.292,600000 2.292,60 0,00 Purchase Sold 150 ProShares UltraShort Euro

No problem here. Transaction is internal in the Investment Transactions report.

B]
If I reopen the transaction in the investment in $. The value is then rounded automatically to 2.984,28. I think I have observed that the price is rounded too (to 19,8952). OK or Cancel, the rounded values are recorded. I'm OK with that, it's cleaner. But the redemption now appears in the cash account like this :

Date Price Shares Total Fee Type Memo
21/03/12 1,000000 2.292,601982 2.292,60 0,00 Purchase Sold 150 ProShares UltraShort Euro

For a price of 1, shares not equal to value. Mmmmh... Not terrible, or what ??

My temptation here is to enter the share field to round it, to have value = share price. More cleaner ! But it's a bad move I presume, because then the link is lost between the transaction and it is considered external.

My questions are :

- Are my steps to record a transaction in foreign currency are OK ?
- Is the rounding consequence in the cash account (share price <> value) by design ? Potential bad side effects ?
- Could an improvement be made, eventually, for a link more robust between cash account and investments, regarding this rounding effects. Here's it's a question more than a request.

Thanks.

[Mark]

Hi Djobydjoba,

Thanks for the details. Let me spend some time trying to enter this, and reproduce the problem. I'll post back once I have more details.

[Mark]

Hi Djobydjoba,

I entered your example into FM to test it here. I see the same issue. The source of the problem is that the exchange rate being used (1,3017) doesn't have enough resolution to allow the exact 0.01 value in both currencies. As you observed, when you edit a transaction, FM displays the value rounded to the nearest 0.01, as that is normally the minimum resolution of an amount in any currency. When you recorded the transaction initially, you had values from the equation:

2292,60 * 1,3017 = 2984,27742

However, when you edit the investment in $, and round the value to 2.984,28, this reflects back on the value subtracted from the default cash account:

2.292,601982 * 1,3017 = 2.984,28

So, assuming that you want both $ 2.984,28 and 2292,60 € you need to use an exchange rate with more resolution. If you record the transaction in the $ investment, and specify an exchange rate of 1,30170112536 (which is 2.984,28 / 2292,60) for that transaction, you will get both $ and € values as desired.

I did notice a problem that we need to address though, and that is that if you enter a transaction specific exchange rate with this much resolution, and then edit that same transaction again, it will only display 6 significant digits, and if you press OK, it will record it that way. Internally it is capable of recording much more, so we will see about preventing that exchange rate figure from defaulting to rounding at 6 digits.

To answer your questions, your approach is fine, but I would suggest determining/using the exact exchange rate appropriate for this transaction, such that both currencies can be displayed accurately at the 0.01 resolution. Do this as I described above.

The reason in your example the shares <> value is due to rounding when being displayed. Since the price was 1.0, they are actually the same, but the shares are incorrect, so you should use the exact exchange rate so you don't get an accumulation of small share balance errors.

Yes, it is possible to have a more definitive link between cash and investment transaction.

[Djobydjoba]

Hi Mark,

Thanks for your clear answer.

So, the steps necessary to record the transaction (and more generally all the transactions in foreign currency) would be :

1) find the exchange rate for the day of the transaction in the EUR/USD investment : 1,3017. Four digits.
2) calculate the value in $ : 1,3017 * 2292,60 = 2984,27742
3) Round this value to two digits : 2984,28
4) Find the exchange rate with a greater resolution : 2.984,28 / 2292,60 = 1,30170112536
5) Record the transaction.

I wonder if some of these tasks/calculations could be done by FM... It's note a very funny work when multiple transactions have to be recorded. More generally, I think the process could be simpler for the user, but have no idea yet how this could be... And manipulate 10 digits numbers doesn't really please me at first sight..

I'm surprised too that nobody has reported the "issue" yet, as record transactions in foreign currency seems a basic task.

[Mark]

Hi Djobydjoba,

Yes, your described method would work here. I suspect not everyone tracking investments in multiple currencies has run into this issue, or if they have they may not have noticed/cared whether or not transactions are designated as portfolio external or not. If a set of transactions is designated as portfolio external it doesn't change any performance/valuation figures.

Also, often times, when buying investments in a non-standard currency I believe that money is taken out of a cash account in that same currency, instead of immediately converted to their default currency as you've described.

[Djobydjoba]

Hi Mark,

As you suggest, I will record higher resolution for the exchange rate, so I will wait for the change you've mentioned :

we will see about preventing that exchange rate figure from defaulting to rounding at 6 digits.

Thanks.

[Mark]

Hi Djobydjoba,

You can record the exchange rate now with the higher resolution. You don't have to wait. It is just that if you "edit" the transaction, it will be initially displayed with resolution rounded to 6 decimal places. If you were to OK that edited version without changing it back to the higher resolution, it will record with the displayed lower resolution.

[Djobydjoba]

Understood, thanks.

[Djobydjoba]

Hi Mark,

I've just noticed another situation with external / internal identification issue. This time the transactions are in default currency.

- cash account in €
- Investment A in €.

The transactions are :
1) Buy 8000€ of cash account the 27/01/10. (value 8000; shares 8000; price 1)
2) The same day, Buy 8000€ of Investment A.

Investment A is a mutual fund. The number of shares purchased : 16,3890 (information by my broker, I know it's a rounded number).

So I recorded : value 8000, share 16,3890 and price 4.881,322838 (auto-calculated).

In the Investments Transactions report, transaction 1) should be external and 2) internal. But it's not the case. In fact it's the opposite. I don't understand why. In fact is seems because the dates are identical, because If I change the day for transaction 2) with the day after the issue is solved. Could you explain this ?

Thanks.

Edit : Forgot to mention : the transaction 1) is a new deposit on the account.

[Mark]

Hi Djobydjoba,

You should have 3 transactions in this example:

1) Buy 8000 cash
2) Buy 8000 investment
3) Sell 8000 cash

#3 is from the default cash account entry for the #2 transaction. When FM determines internal/external it is just matching up equal/opposite transactions and counting those as internal. Everything remaining is external. In this case, it sounds like #1 and #3 are matched up and cancelled, so #2 is your external transaction.

There is no hard link between a transaction and a corresponding default cash account entry. It is only matched by date/value/type. I agree it would be better for FM to look for matches outside the default cash account first, and then look within the default cash account. We'll see about improving this.

[Djobydjoba]

Hi Mark,

Thanks, I understand now. It's strange because other transactions are OK with the same principle : deposit in the (virtual) cash account first, then purchase of an investment for the same amount the same day. So there is a random thing here..

Perharps I see why in fact.. the cash account is named "Cash Account", and in the given example the investment name is "Carmignac Patrimoine", so alphabetically before "Cash Account". It was not the case with other investments I think.

Thanks for an improvement on this. I appreciate when internal/external flows are OK, as figures in some reports speak now (as total contributions in Portolio Performance report).

Best regards.