Fund Manager
PORTFOLIO MANAGEMENT SOFTWARE
Contact Us

Problem of transaction seen as external

Questions about updating prices or transactions in Fund Manager

Postby Djobydjoba » Sat Mar 09, 2013 7:51 am

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.
Djobydjoba
 
Posts: 711
Joined: Tue Mar 09, 2010 9:39 am

Postby Mark » Sat Mar 09, 2013 10:52 am

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?
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11252
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby Djobydjoba » Sat Mar 09, 2013 12:10 pm

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.
Djobydjoba
 
Posts: 711
Joined: Tue Mar 09, 2010 9:39 am

Postby Mark » Mon Mar 11, 2013 8:58 am

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.
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11252
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby Mark » Mon Mar 11, 2013 1:06 pm

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.
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11252
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby Djobydjoba » Mon Mar 11, 2013 1:50 pm

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.
Djobydjoba
 
Posts: 711
Joined: Tue Mar 09, 2010 9:39 am

Postby Mark » Mon Mar 11, 2013 2:11 pm

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.
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11252
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby Djobydjoba » Thu Mar 14, 2013 9:54 am

Hi Mark,

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

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


Thanks.
Djobydjoba
 
Posts: 711
Joined: Tue Mar 09, 2010 9:39 am

Postby Mark » Thu Mar 14, 2013 10:18 am

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.
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11252
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby Djobydjoba » Thu Mar 14, 2013 11:35 am

Understood, thanks.
Djobydjoba
 
Posts: 711
Joined: Tue Mar 09, 2010 9:39 am

Postby Djobydjoba » Sun Mar 31, 2013 8:01 am

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.
Last edited by Djobydjoba on Sun Mar 31, 2013 8:26 am, edited 1 time in total.
Djobydjoba
 
Posts: 711
Joined: Tue Mar 09, 2010 9:39 am

Postby Mark » Sun Mar 31, 2013 8:25 am

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.
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11252
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby Djobydjoba » Sun Mar 31, 2013 8:43 am

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.
Djobydjoba
 
Posts: 711
Joined: Tue Mar 09, 2010 9:39 am


Return to Prices and Transactions

Who is online

Users browsing this forum: No registered users and 13 guests

FundManagerSoftware.com | Search | Site Map | About Us | Privacy Policy
Copyright © 1993-2024 Beiley Software, Inc. All rights reserved.
cron