portfolio
portfolio copied to clipboard
portfolio-performance
New IBKR importer wrokng conversation
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).
@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...
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.
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.
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.
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.
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.
Hello All,
my small contribution to the discussion. I can confirm that with a cash account type and base currency (EUR) and Trading account in USD, there are (at least) two types of currency conversions, that can take place.
Forex currency conversion can be placed as a order to convert x amount into USD (min 2 days for settlement) Also, if activated, IB will convert EUR to USD during order placement, provided insufficient USD but sufficient EUR in the accounts (Immediate exchange). Both types are recorded within the <Trades> section.
Whilst the Forex trade will have an IB commission, the auto exchanges do not show an IB commission. (Each auto conversion can create more than one transaction, including ones with very small values) The base currency amount will be shown as a negative value. The "fxRateToBase" is used as the conversion factor. The converted currency will be shown as a positive value.
kind regards, Maria
The "fxRateToBase" is used as the conversion factor.
This I think is incorrect. The conversion factor applied is tradePrice, so quantity * tradePrice = tradeMoney. This is especially true for currency pairs without the base currency because in this case fxRateToBase wouldn't make sense to be applied. To me it also looks like fxRateToBase is something like an end-of-day exchange rate because it's always the same for an entire day whereas the trade price flucuates between currency trades within the same day.
The rest I can confirm but it doesn't really matter for the implementation of this feature as long as all fields are read correcly. The only issue is that some of the really small currency transactions get rounded-off and/or you get 0€ conversions but there is probably no way around this unless you want to support sub-cent accuracy in pp everywhere which I assume is a very big implementation effort.
Sure, @seg-on Can you share the file and the exact version?
Hi @itshukychen , which version do you mean? PP is 0.77.1.
Do you think it's possible to fix it? I tried doing it manually, but it was annoying.
A supposed fix for this issue is https://github.com/portfolio-performance/portfolio/pull/4792 , but as can be seen, it fails tests now.
@buchen: There're more issues. For example, the code attaches fees to a transfer transaction: https://github.com/portfolio-performance/portfolio/blob/master/name.abuchen.portfolio/src/name/abuchen/portfolio/datatransfer/ibflex/IBFlexStatementExtractor.java#L448 . And IMHO, it does that absolutely right (as in: doing that at all, not the way it does it). It was also a long-standing request on the forum. What's not right is that PP's transfer edit dialog doesn't allow to edit fee(s). Back to the code, while the fact that it attaches fee to a transaction is right, the way it does it is not (wasn't tested), because it doesn't calculate net vs gross values properly wrt to fees/taxes, so that leads to errors on import.
@itshukychen: Following the best practice of "only single change must be done per patch, unrelated changes should not be mixed", I left "exachangeRate" typo in the code as is, IMHO it should be fixed as a separate patch.
And IMHO, it does that absolutely right (as in: doing that at all, not the way it does it)
I'm not familiar with the code but what happens if the account's base is in EUR and you do a e.g. CAD.USD conversion? In this case ibCommissionCurrency==EUR. Looks like the 2nd branch here shouldn't execute in this case but I might miss something: https://github.com/portfolio-performance/portfolio/blob/49255153a66d01f860484b593854cf1bdbed77d9/name.abuchen.portfolio/src/name/abuchen/portfolio/datatransfer/ibflex/IBFlexStatementExtractor.java#L449 ?
@pfalcon writes:
What's not right is that PP's transfer edit dialog doesn't allow to edit fee(s).
Fair. But I think this is not the commit to introduce it. Need to change the UX, but also all aggregations of fees, all filters that calculate before fees and taxes, etc.
@jbfuehrer writes:
CAD.USD conversion? In this case ibCommissionCurrency==EUR
I fear three way currencies are not supported by PP. And fees in the transfers also not (see comment above).
What we could do is create an additional "free transactions" in EUR (always of the conversion is USD and CAD, but for now also for regular transfers). Anybody has an opinion?
The alternative: have the exchange rate "eat up" the fees, e.g. when "selling" EUR, you get deducted the EUR price + the commission and get the regular USD price. That results in a different exchange rate.
(Adapting the test cases at the moment).
Can someone comment how to create the transaction for this:
<Trade
currency="USD"
fxRateToBase="0.93199"
assetCategory="CASH"
symbol="EUR.USD"
quantity="-3634"
tradePrice="1.0719"
tradeMoney="-3895.2846"
ibCommission="-1.8726"
ibCommissionCurrency="EUR"
❓ Are 3634 EUR deducted from the EUR cash account? Or 3634 + 1.8726?
~Because both do not seem to add up to the 3895.2846 USD The exchange rate is 'tradePrice' not 'xRateToBase'~
BTW, I have fixed the tests (reversed the currencies) and pushed the change by @pfalcon and mine into the the branch pr_4782_ib_cash_transfers. The tests are still failing, because I did not change the behavior of the commission.
I need to understand two things (see the two comments above):
- First, how to handle the commission in the light that PP does understand (yet) fees on a transfer?
- Second, if the commission is included in the "quantity" or "on top"? Basically, in the sample above, are 3634 or 3635.8726 deducted from the EUR cash account?
I can continue with the change once I get some info on this.
I fear three way currencies are not supported by PP. And fees in the transfers also not (see comment above).
I'd say this is fine for now. It's already very handy if this works for base currency transactions which should anyway cover most user's cases. I'd only suggest to ignore non-base conversions for now (so as it was before the update) than book it io the wrong account. The correct thing to do in these cases is probably to book a standalone fee to the EUR account with the same date and maybe relate the two with some of IB's IDs. You could even argue that it should generally always be like this because otherwise you get two different code/UI paths for what in practice really is the same thing. But there are probably pros and cons for going either way.
I'm not sure if this is what you meant in one of your examples because I might miss some lingo. Like what is a "regular transfer"? You mean a deposit?
Can someone comment how to create the transaction for this:
I'd say tradeMoney = quantity * tradePrice and the total amount deducted is tradeMoney + ibCommission. You can double-check that for stock transactions, this sum always ends up in NetCash but for currency transactions it's for some reason always 0. Idk why IBKR does it like that.
@jbfuehrer :
I'm not familiar with the code but what happens if the account's base is in EUR and you do a e.g. CAD.USD conversion? In this case ibCommissionCurrency==EUR
If that's actually possible, then apparently, the fee should be (always) recorded as a separate transaction. That's essentially how people would have done it manually before. And that would also save on pretty mind-boggling reconciliation of net vs gross values (like @buchen goes thru now). That of course means making larger modifications to @itshukychen's code, but at least we'll be sure it covers all the cases.
@buchen
<Trade
currency="USD"
fxRateToBase="0.93199"
assetCategory="CASH"
symbol="EUR.USD"
quantity="-3634"
tradePrice="1.0719"
tradeMoney="-3895.2846"
ibCommission="-1.8726"
ibCommissionCurrency="EUR"
Hopefully, saying the same as @jbfuehrer: 3634EUR was exchanged into 3895.2846USD with the rate of 1.0719. Additionally, 1.8726EUR was taken as fee. As I mentioned above, as long as you already looking into this up to modifying tests, it may be worth to record fee as a separate transaction. (Would definitely save/push far down the line need to add UI to manage fees on transfers ;-) ).
@jbfuehrer I don't understand why the fee should failing the test testIBFlexStatementFile14 and testIBFlexStatementFile10, these has no negative proceeds and shouldn't have been affected by this change at all, I think this might suggest a bug in the change, or that I'm missing something.
The only relevant test case here is testIBFlexStatementFile18 but it doesn't event assert anything on the 3rd and 4th cash transactions which are the only negative cash transaction in the file, so again, this change shouldn't fail the test unless there's a bug in the change.
I'll try to take a look at the code and see if I understand what's wrong.
Looking at @seg-on 's file, I do agree with @pfalcon PR's that it will solve the issue.
@jbfuehrer @pfalcon Ok the issue is the check is reversed, the code checks quantity, which is negative by nature on normal transaction (non opposite), while the proceed is the positive on normal cases.
Either reverse the check on the code or check of the negativity of "proceeds". That should resolve all tests, we would want to add a new test to check for the negative cash transaction.
I don't mind helping with the code here, just not sure through which branch/PR/repo I should do it.
Let me know what you think.
I have now adapted the tests (because the direction of the transfer is now reserved) and also extract the fees as a separate transaction.
See e7637c3728ae751775fe5781c0fee180a5c60d69
I hope to publish a new version soon.
Thanks @pfalcon @jbfuehrer @seg-on @itshukychen for the good conversation in this issue.