search

dickwolff / Export-To-Ghostfolio

Convert transaction history export from your favorite broker to a format that can be imported in Ghostfolio.
https://hub.docker.com/r/dickwolff/export-to-ghostfolio
Apache License 2.0
45 stars 8 forks source link

Support for Interactive Brokers (IBKR) #38

Closed swissbuechi closed 6 months ago

swissbuechi commented 6 months ago

It would be awesome if you could support IBKR. I've created a custom flex query for the trades and dividends + taxes. I'll create a small explanation on how to create the query, if you decide to implement this broker.

Trades

Trades.csv:

"Buy/Sell","TradeDate","ISIN","Quantity","TradePrice","TradeMoney","CurrencyPrimary","IBCommission","IBCommissionCurrency"
"BUY","20230522","CH0111762537","7","282.7","1978.9","CHF","-5","CHF"
"BUY","20230522","US9220427424","95","93.78","8909.1","USD","-1","USD"
"BUY","20230609","US9220427424","105","95.42","10019.1","USD","-1","USD"
"BUY","20230609","US9220427424","11","95.46","1050.06","USD","-1","USD"
"BUY","20230803","US9220427424","1","97.84","97.84","USD","-0.979","USD"
"BUY","20231228","US9220427424","2","103.18","206.36","USD","-1","USD"
"BUY","20240126","US9220427424","97","103.61","10050.17","USD","-1","USD"
"BUY","20240129","US9220427424","12","103.66","1243.92","USD","-1","USD"
"SELL","20230522","","-8000","1.1173","-8938.4","USD","-1.79924","CHF"
"SELL","20230609","","-10012","1.10725","-11085.787","USD","-1.79802","CHF"
"SELL","20240126","","-10020","1.1537","-11560.074","USD","-1.73416","CHF"

Dividends and Taxes

Dividends_Taxes.csv

"Type","SettleDate","ISIN","Description","Amount","CurrencyPrimary"
"Withholding Tax","20230913","CH0111762537","SMMCHA(80486947) CASH DIVIDEND CHF 5.99 PER SHARE - CH TAX","-14.68","CHF"
"Withholding Tax","20230623","US9220427424","VT(US9220427424) CASH DIVIDEND USD 0.6504 PER SHARE - US TAX","-20.58","USD"
"Withholding Tax","20230921","US9220427424","VT(US9220427424) CASH DIVIDEND USD 0.4055 PER SHARE - US TAX","-12.9","USD"
"Withholding Tax","20231221","US9220427424","VT(US9220427424) CASH DIVIDEND USD 0.8008 PER SHARE - US TAX","-25.47","USD"
"Dividends","20230913","CH0111762537","SMMCHA(CH0111762537) CASH DIVIDEND CHF 0.69 PER SHARE (Return of Capital)","4.83","CHF"
"Dividends","20230913","CH0111762537","SMMCHA(80486947) CASH DIVIDEND CHF 5.99 PER SHARE (Ordinary Dividend)","41.93","CHF"
"Dividends","20230623","US9220427424","VT(US9220427424) CASH DIVIDEND USD 0.6504 PER SHARE (Ordinary Dividend)","137.23","USD"
"Dividends","20230921","US9220427424","VT(US9220427424) CASH DIVIDEND USD 0.4055 PER SHARE (Ordinary Dividend)","85.97","USD"
"Dividends","20231221","US9220427424","VT(US9220427424) CASH DIVIDEND USD 0.8008 PER SHARE (Ordinary Dividend)","169.77","USD"
dickwolff commented 6 months ago

Thanks for creating this issue. I appreciate the detailed description!

Are trades and dividends always exported seperately (so not both in the same file?)

swissbuechi commented 6 months ago

You could combine them to the same file, but it would just copy the content of the second file below the first one. With new headers, etc...

dickwolff commented 6 months ago

Gotcha!

I will work something out later this week and get back to you so you can do some testing!

swissbuechi commented 6 months ago

You're awesome!

cheesestickstomatojuice commented 6 months ago

This might also help figuring stuff out, this program syncs ghostfolio with IBKR maybe u can take a look and see how they do it? Appreciate your work so far!

https://github.com/agusalex/ghostfolio-sync

Also ghostfolio seems to have an example here what a format from IBKR should look like so ghostfolio can support it.

https://github.com/ghostfolio/ghostfolio/blob/main/test/import/ok-vti-buy-on-ibkr.csv

dickwolff commented 6 months ago

I have created an initial version the IBKR converter. You can clone it for local running here and run with npm run start ibkr, or pull this Docker image (0.7.0-beta).

One thing I found was that the SELL orders you provided as an example don't have an ISIN. So they can't be matched to a security. Is this an error with your export, or don't they ever have an ISIN? 

swissbuechi commented 6 months ago

Great, thank you!

One thing I found was that the SELL orders you provided as an example don't have an ISIN

There are no SELL orders in my report, I've never sold something... The SELL orders you see without ISIN are just currency exchanges from CHF to USD.

Real SELL orders should look the same and also have an ISIN I think...

swissbuechi commented 6 months ago

This might also help figuring stuff out, this program syncs ghostfolio with IBKR maybe u can take a look and see how they do it? Appreciate your work so far!

https://github.com/agusalex/ghostfolio-sync

Also ghostfolio seems to have an example here what a format from IBKR should look like so ghostfolio can support it.

https://github.com/ghostfolio/ghostfolio/blob/main/test/import/ok-vti-buy-on-ibkr.csv

Wow, it seems like Ghostfolio has native support for importing IBKR BUY/SELL Orders. Works for me, I guess...

And Dividends can be auto-imported... So the only thing missing is the Withholding Tax.

dickwolff commented 6 months ago

@swissbuechi

Importing Buyworks fine.

Great!

Importing Dividends works also fine. But, do you really want to add the description to the record? I have done this for all converters as far as I recall, so I also did it here. This way you have the original reference (if present).

Importing Withholding Tax did not work. Is this intended?

There was one dividend in your example that is being processed without fees (0). image

This is not working for you?

The SELL orders you see without ISIN are just currency exchanges from CHF to USD.

Ah, that would be an interpretation error on my side. Thanks for clarifying!

Wow, it seems like Ghostfolio has native support for importing IBKR BUY/SELL Orders.

I did not know this. Great that Thomas has this support built in.

swissbuechi commented 6 months ago

There was one dividend in your example that is being processed without fees (0).

I was talking about the Withholding Tax line:

"Withholding Tax","20230913","CH0111762537","SMMCHA(80486947) CASH DIVIDEND CHF 5.99 PER SHARE - CH TAX","-14.68","CHF"

Shouldn't this be imported as Fee?

dickwolff commented 6 months ago

@swissbuechi I'm sorry, withholding tax is included in the tool! The tool matches the dividend tax lines to the correct dividend lines.

image

image

As you see with the line above, the fee value contains the withholding tax value, and the other part is the normal dividend values.

swissbuechi commented 6 months ago

@swissbuechi I'm sorry, withholding tax is included in the tool! The tool matches the dividend tax lines to the correct dividend lines.

Alright I see, thank you.

dickwolff commented 6 months ago

If there aren't any other issues, could you provide me with export instructions like these so I can add those to the readme? Much appreciated!

swissbuechi commented 6 months ago

Interactive Brokers

  1. Open Interactive Brokers Account Management

  2. Click Reporting in the sidebar

  3. Click on the Flex Queries tab in the Reporting section.

  4. From the Flex Queries section, Click the plus (+) icon on the right side to create a new Flex Query.

  5. Create a new Flex Query for Trades and another one for Dividends image

    Trades

    1. Select Trades
    2. Select Properties:
      • Buy/Sell, TradeDate, ISIN, Quantity, TradePrice, TradeMoney, CurrencyPrimary, IBCommission, IBCommissionCurrency

    Dividends

    1. Select Cash Transactions
    2. Select Properties:
      • Type, SettleDate, ISIN, Description, Amount, CurrencyPrimary

  6. Select Format = CSV

  7. Save Query

  8. Run Query and select time frame

dickwolff commented 6 months ago

Thanks a lot! I will add the instructions to the readme later this week, probably friday.

dickwolff commented 6 months ago

I've added the readme and merged the PR! Thanks for your help!