New IBKR importer wrokng conversation

[seg-on]

Hi!
I tried to import the whole history from IBKR through Flex Report, but now it needs a EUR account, USD account and EUR account for transfers.
All currency conversions from EUR to USD were imported as transfers from a USD account to a EUR account.
This makes double expenditures in the USD account.
For example, I bought a stock in USD for 456 USD, and a cash transfer of 456 USD was made from USD account to the EUR account. Therefore, the USD account are in debt by -912 (-456-456).

[buchen]

Thanks. Sounds like it is the wrong direction...

[Nirus2000]

@itshukychen

Do you have time to have a look?

[itshukychen]

Sure, @seg-on Can you share the file and the exact version?

[buchen]

@seg-on, as @itshukychen said, we need an example.

I have to honestly admit I do not understand the IB flex format well enough.

Looking at testIBFlexStatementFile10.xml as an example, it contains to trades (transactions in PP speak): a purchase and a cash transfer.

In the test case, we check that there is

• a purchase in EUR with a gross amount in USD
• a transfer from USD to EUR

That would mean: USD was converted to EUR to purchase the instrument in EUR (but traded in USD).

But I am not sure if that is the right way to interpret it.
Looking at the XML, the trade is fully in USD (even though it belongs to an account in EUR).

Is it maybe a purchase in USD and the transfer is a SELL of EUR to create that USD?
Then it would be exactly the other way around.

<Trade acctAlias="Cash" assetCategory="STK" buySell="BUY"
       cost="140.83125725" currency="USD" ...
<Trade acctAlias="Cash" assetCategory="CASH" buySell="SELL"
       cost="0" currency="USD"
       symbol="EUR.USD"
       fxRateToBase="0.94302"
       tradePrice="1.0596"
       tradeMoney="-140.9268"
       quantity="-133"
       ...

But:

• It is unclear to me, where the difference is gone (between 140.83 USD purchase and 140.9268 USD transfer.
• The order of the dates would also not fit. The purchase is before the conversion. But I would expect it the other way around: first convert the money to USD, the use that money to purchase.

I am very confused...

[dylan09]

I am very confused...

That's understandable if you didn't have an IB account.
Cash and stock transactions aren't related to another if it is a margin account. If so, you could buy stocks in USD (or other currency) on margin. Meaning you are borrowing money from IB even if there is enough money in other currencies. There will be no automatic exchange. That maybe also the reason for the difference between the purchase and the FX.
So the two transactions have no relations and must be also separate transactions in PP.

The confusion how the purchase is shown in the XML maybe related to the base currency used in the IB account. If you have a "home currency" different from USD, you could set a base currency for your account. Every transaction no matter which currency is used will also be converted to your base currency.
So the purchase is done in USD. But the XML (and every other report) will show also the amount converted to EUR.

[jbfuehrer]

Is it maybe a purchase in USD and the transfer is a SELL of EUR to create that USD?
Then it would be exactly the other way around.

Exactly. You can either check the buySell indicator or the sign of the quantity to determine the direction of the trade. This also holds for other currency pairs of course. USD -> EUR would be listed as a buy (of the pair's first currency) with a positive quantity.

[jbfuehrer]

The confusion how the purchase is shown in the XML maybe related to the base currency used in the IB account. If you have a "home currency" different from USD, you could set a base currency for your account. Every transaction no matter which currency is used will also be converted to your base currency. So the purchase is done in USD. But the XML (and every other report) will show also the amount converted to EUR.

I don't think anything at least within stock and currency transactions in flex queries is listed in your account's base currency. Only commissions might be paid in your base currency but then it's also outlined accordingly with ibCommissionCurrency="EUR". The only other thing that has a relation to the account base currency is the field fxRateToBase which I think is internally used by IB to convert to the base currency in various reports and account statements you can generate.

But:

It is unclear to me, where the difference is gone (between 140.83 USD purchase and 140.9268 USD transfer.
The order of the dates would also not fit. The purchase is before the conversion. But I would expect it the other way around: first convert the money to USD, the use that money to purchase.

I am very confused...

I think @dylan09 mostly covered it already but it's like you have multiple currency accounts within your IB account and stock purchases are generally not tied to currency conversions. It is actually a forex trade where you buy or sell a currency for another. Depending on how the test data was generated or retrieved, there might have been a positive USD balance in the account that the stock purchase was made from before the EUR.USD conversion was executed. Or it's of course also possible to make a currency conversion and then only buy stocks for a fraction of the converted amount after, leaving the rest in your USD account.

[seg-on]

Thank you for your commitment.
First, I would like to clarify that I am trying to import the entire history, as I have been keeping everything under one cash account and using currency conversion directly on transactions. This is how the IBKR flex report importer originally worked. As I understand it, the new importer will now keep purchases in each currency separately so that it is consistent, so I wanted to import the entire history (from 2021).
The first thing that struck me was that two combo boxes for accounts had been added to the importer. I don't really understand why.

When I ran the imports, I looked at what and how it imported, and that's when I noticed the problem mentioned above.

Here is the XML file
https://www.swisstransfer.com/d/e20d7906-0851-4d83-8025-354cdcbe8f7e

Maybe I'm making a mistake somewhere, or misunderstanding something.

[dylan09]

OK. That's new. Have to be careful when importing next month with the new version.
Until now I have used different cash accounts for every currency (USD, AUD, CAD, GBP, ...) Reflecting all balances in my IBKR account.