Convert GBP/GBX when importing from Interactive Brokers

English Import/export
1

Zunächst einmal vielen Dank für diese erstaunliche Open-Source-Software. Lassen Sie mich auf Englisch fortfahren (meine Entschuldigung).

I’m trying to solve the same issue in this great software!! I setup my Konto and Depot in GBP (I’m from NL but based in UK) and added the securities trading on London stock exchange/LSE all in GBX in PP.

Flex Query gives me the data in GBP back and therefore this throws an error in PP that the security currency in PP and the imported currency don’t match.

Looking at the PP IBKR importer source code here it seems it uses these fields from the flexquery XML for the import that are relevant:

// amount (mainly used for the calculations)
currency
fxRateToBase
netCash
quantity

// fees (these can be still in GBP)
ibCommission
ibCommissionCurrency

I tried to adjust the fxRateToBase to 0.01 from 1 and multiple the netCash * 100 and then import the XML into PP. Unfortunately this didn’t work. it imported the right GBX/GBP rate (so the quote in PP was correct) but then the Net Transaction Value was off.

Solution
In the end I updated all GBP currencies with the following script in Terminal / MacOS:

~ xml edit -P -O -u '//Trade[@exchange = "LSEETF"]/@currency' -v 'GBX' my_ibkr_export.xml > ibkr_with_gbx.xml (replace my_ibkr_export.xml with your own FlexQuery file name).

This updates the currency for all trades in the XML where the exchange is LSEETF (in my case). Still, this comes with issues as after importing you look at the details of the transaction in PP and it says “Converted sub-total is incorrect” and the quote is not correct. This doesn’t however seems to impact the overall numbers (see below) in the “All transaction interface”.

Update: the quote not being correct in GBX impact the graphs apparently so still manual editing needed.

Portfolio_Performance
Portfolio_Performance1178×694 118 KB

Long term solution?
The issue seems to be on IBKR side as they report in all their interfaces GBX also as GBP as far as I can see… Not sure if this is a bug or feature but a possible solution could be to overwrite the currency to GBX in the IB FlexStatement Importer, set the right fxRateToBase and convert/multiply netCash to GBX (* 100).

1 Like
2

I have the exact same problem. I did not find a solution on the forum.

Br,

Cedric

3

Hey guys!

I made a very simple program to transform the IBRK reports GBP to GBX.
you can find the code here: GitHub - angelsanchez/ibkr-gbp-to-gbx-transformer: Convert GBP to GBX in InteractiveBroker reports

hope it helps! :grinning:

4

Hello, I am using the flex query export from IB and importing into my USD base currency PP account. I have CHF, Euro, SEK, USD and GBP. The Flex Query contains GBP as I can read out of the XML that is generated out of IB. BUT the Yahoo Data is generating GBX in 3 out of 4 cases. I have 4 GBP instruments, all bought on the LSE in GBX to start off with. What do I need to do to get Yahoo (or another quote source) to correctly enter in GBP rather than GBX? Very frustrating as it is skewing my entire Portfolio. Thanks for the help.

5

I’m not familiar with Interactive Brokers’ exports, but…

I’m just writing a guide on how to handle imports for GBX denominated securities. The main reference manual does contain a section now on importing with explanation of handling different currencies. It is possible to work it out from there but I must admit I took many twists and turns (even after getting it working).

It can be done in the CSV import however (assuming you don’t mind manipulating and adding some info. to the CSV). It does not need editing of the XML.

I’m nearly done on the guide although I still need to navigate getting that into PP’s manual format and contributing it for review via GitHub.

In the meantime, the short version which may help:

  1. Create your securities first before importing any transactions (otherwise your GBX securities will be created as GBP when you first import a transaction).
  2. Specify the currency of the securities as GBX where applicable
  3. Create the Portfolio Transactions import data (i.e. your buys and sells) with at a minimum:
    Date, Type, Value, Shares, Currency Gross Amount, Exchange Rate and one of Ticker Symbol, ISIN or WKN. And optionally Taxes and Fees.

I use Yahoo! Finance so I just have the YF Ticker Symbol.

Currency Gross Amount is the pricing currency of the security (e.g. GBP, USD, EUR, or GBX)

Exchange Rate is blank except for the GBX lines where it needs to be 100.

Import using type “Portfolio Transactions”

The following thread may also help (it helped me):

6

Hi, how do I apply this? Thanks!

7

From the website manual

Screenshot_20240402_215601_Chrome Beta
Screenshot_20240402_215601_Chrome Beta1079×534 82 KB

8

Hi, I tried to create the new security as GBX before importing using the Flex Report from IB, but I get a currency mismatch. My base account is USD and IB seems to document the security (DGE.L in this case) as GBP. But YF reports in GBX. So at this time I am now further than I was. Sorry!

9

Unfortunately that does not help me any further

10

@Stellablue

Why not? :nerd_face:

You have to go into detail to best illustrate your problem with screenshots. :smiley:

Cheers, Laura

11

Hey Laura,
because I am too dumb to get the java script out of github for me to use. Not sure how to start with that. Sorry.

12

Hey @Stellablue,

That’s exactly how I feel. I am unable to start anything other than an EXE file. :roll_eyes:

I assume that you have to copy the link into your Java console. But that’s just a guess.

Cheers, Laura

13

Hi, this is how you do it

  1. Ensure Java JDK (Java SE Development Kit) is installed
  2. Download the Code from Github as a Zip file (click code-> Download Zip)
  3. Extract the zip file to a folder
  4. Open Command Prompt, Powershell or Terminal
  5. Change directory to the extracted folder with CD command. e.g CD “C:\ibkr-transformer”
  6. Run the compile command javac Main.java
  7. Run the conversion java Main “C:\myibkrreport.xml”