Fix IBKR import and value calculation of securities listed in other currencies

[RadoslavGatev]

I was facing an issue that prevented me from importing a security listed in other exchanges (hence in different currencies). I guess the issue occurs when you hold the security from various exchanges.

My setup:

• EUR security account
• USD security account

The security is originally listed in USD but also on German exchanges in EUR. At first, you can have the originally listed security in USD but at some point, you may decide to buy the one listed in EUR. Then the issue occurs because the EUR listed security resolves to the same security listed in USD (I guess because it has the same isin).

Therefore, the gross amount of the portfolio transaction must be set. Because of the order of operations in the code and the absence of currency code on the transation, gross value was not set. I spotted also an error in the calculation of value because there is an assumption that the value of the investment is in the currency of the security.

Caution: I am not an expert in Portfolio Performance.

[RadoslavGatev]

Related #2139

[buchen]

Hi @RadoslavGatev,

thanks for taking the time to build this contribution.

The internal model which PP expects makes the following assumption: the currency of a transaction either matches the currency of the security or there is a gross value unit (including the exchange rate) that matches the currency of the security. If you have the security configured with the currency USD, then either the transaction must be in USD or there must be a gross value unit including the exchange rate from the account currency to USD.

I guess because it has the same isin

Correct. The importer typically work this way: they try to identify/extract the ISIN. If the ISIN is found, the existing security is used. If it is not found, a new security object is created with the information in the statement.

I am a little confused because I would have expected that an ISIN also denotes the currency.

From your description, I assume you run into the following problem:

• Initially the security is imported with USD as currency (because it was traded on a USD exchange)
• Then you do additional trades for the same security in EUR using an account in EUR
• The imported transactions only contain EUR (there is no exchange rate) and then PP complains

There are two ways to address this: change the way PP works (big) or use the exchange rate of the day to create the appropriate gross value (smaller).

For the second option, it would require to inject the ExchangeRateProviderFactory in the constructor of the IBFlexStatementExtractor and then use it if the exchange rate cannot be extracted from the statement itself.

Let me ask about your PR (IB flex was contributed, I am no expert). The test cases fail because of this block:

String currency = asCurrencyUnit(element.getAttribute("currency"));
transaction.getPortfolioTransaction().setCurrencyCode(currency);

Up until now, the code was looking at the AccountInformation in the XML and extracting the currency there as the currency for the transaction (see the code in the setAmount method). With your change, there is now a different transaction currency which prevents importing taxes and fees. Do you know how Interactive Brokers is handling this? In what situation can the currency of the account differ from the currency of the transaction element?

Andreas.

[RadoslavGatev]

Hi @buchen,

Thanks for the provided information!

What you described is exactly the issue I run into. It has to do with the absence of gross value. But it seems the way you proposed (using the ExchangeRateProviderFactory) is better. My current PR supplies the gross value in EUR while the security is in USD and that causes other problems. That's why I had to change the logic in SecurityPosition to respect the currency of the transactions.

I'll have to change my approach a bit and I'll see if unit tests still complain. To be honest, I haven't executed them locally.

Up until now, the code was looking at the AccountInformation in the XML and extracting the currency there as the currency for the transaction (see the code in the setAmount method). With your change, there is now a different transaction currency which prevents importing taxes and fees. Do you know how Interactive Brokers is handling this? In what situation can the currency of the account differ from the currency of the transaction element?

The Flex statements are kind of messy, in my opinion. The concept of the base currency in Interactive Brokers is just for the sake of reporting. The same thing is applied to their XML reports - the AccountInformation element carries the base currency, and its child elements (Trades and others) can be of any currency, therefore they provide the fxRateToBase attribute. If a particular transaction is in the base currency, the rate is set to 1.

It's somewhat difficult to import those into PP. I track 2 pairs of cash and securities accounts: EUR and USD. Before I start importing, I open the XML, comment out the elements that are not in the base currency, and import to the respective account (that matches my base currency) in PP. Then, I change the account currency to whatever currencies I have traded in, in my case it's USD, and then uncomment the USD ones while commenting out the EUR (which were imported in the previous step).