Error uploading IBKR flex query data

[cpt23]

When I try uploading the IBKR flex query data for the past tax year, the upload initially succeeds but then returns an error message "File failed to upload". I tried to trace it in the source code but the message seems to originate in some binary libraries. I had many similar problems with your earlier Python code, mostly relating to input parsing issues: I was able to fix those but I don't quite understand the output the Python code is generating (some of the result seems wrong, especially for CGT) and wanted to try your newer code.

[alexpung]

Hello, I think likely it is a datetime parsing problem. Can you make sure the datetime is parsible? e.g. "01-May-21 12:34:56" The code to parse is here If you can give me a minimum reproducible sample with your private data redacted, I can take a look.

https://github.com/alexpung/UK-Investment-tax-calculator/blob/master/UnitTest/System%20Test/Interactive%20brokers/TaxExample.xml

[cpt23]

Thanks for your quick reply! I checked the date format and it is fine. I also created a minimal sample that reproduces the error - can I email you the file? Perhaps you could also share screenshots of the exact IBKR settings for your flex query - there are many, many (sub)options.

[alexpung]

I would prefer if you can post it here (with your info removed of cause).

[cpt23]

I managed to get it to read the data after tweaking my flex queries, thanks again for your help. This looks extremely useful and I very much appreciate your efforts. Do you have a "sponsorship" option or some other way I could express my gratitude? If you don't mind me asking a few more questions: 1.) In the output , I assume that "2022" means the tax year 2022/2023 (i.e. the exact period 6th April 2022 until 5th April 2023) rather than the 2022 calendar year, correct? 2.) Is the "Trades Calculation" in the format expected by HMRC for a CGT tax return? Would HMRC require other data such as the Section104 report or the IBKR data itself? 3.) I ran my IBKR data through your system a few days ago and then again now and the results are slightly different, presumably due to changes you made to the code a few days ago. Is the code, to the best of your knowledge, correct and stable enough for use in a tax return at this time or are you actively working on some bug fixes or improvements?

[cpt23]

One small bug I noticed: your "Trades Calculation" outputs "acquition" rather than "acquisition".

[alexpung]

One small bug I noticed: your "Trades Calculation" outputs "acquition" rather than "acquisition".

Nice catch. English is my second language and the spelling mistake is all over my codebase. Blame Microsoft for not spotting that. ;p

[alexpung]

I am creating a Github sponsor if you would like to support this project monetarily. Besides that, you can help by

1. Checking if the output is correct.
2. Making a feature request.
3. Promoting this project if you know anyone could use this project.
4. Or if you can code and want some other statement parsed, make a new parser with a pull request.

Regarding your questions:

1. This is correct. The tax period is governed by the UKTaxYear class.
2. AFAIK there is no formal format of the calculation details. For brevity some of the details are omitted in the text output but it should be sufficient to show your (the program's) calculation method.
3. To my knowledge the calculation is correct. I have made changes in the algorithm regarding handling multiple shorting and bed and breakfast as the tax law have made it clear that earlier trades are matched first in these categories. Please check your calculation and if you could create a bug report if I do it wrong then it would be very helpful.

The only quirk I am aware of is that "Same day" refers to UK time and it is possible that you trade around midnight in UK time and those trades may not be in the same day although it is in the same market session in some oversea exchanges, and AFAIK IBUK provide local exchange time in the xml file. AFAIK there is no major stock exchange that cross the midnight boundary and this problem applies more in future contract trading where the contract trade 24 hours a day.

Most of the algorithm is here

[alexpung]

Calculation should be correct but text output may have problem, please check the output.

[cpt23]

Thanks for your replies. I can't see the sponsorship option yet. I am running comparisons between your system's output and that obtained by http://www.cgtcalculator.com/calculator.aspx The results for the stocks I've looked at manually seem very similar but I think the CGT calculator explains the reasoning more clearly. I will give you an obfuscated example with output from both systems

[cpt23]

Here is the output from the CGT online calculator for a particular disposal. Note how some of the shares were matched using the "Bed and breakfast" rule and others to the section 104 pool:

X. SELL: 100 ABC on 23/05/2022 at £57.19826 gives LOSS of £38.00 Matches with: BUY: 06/07/2022 40 ABC shares of 40 bought at £54.38516 BUY: 19/07/2022 20 ABC shares of 20 bought at £54.07294 BUY: SECTION 104 HOLDING. 40 ABC shares of 300 bought at average price of £62.42651 CALCULATION: Loss = £38.00 = ( 100 * 57.19826 - 0.96 )

• ( 40 * 54.38516 + 0.83863 + 0.00 )
• ( 20 * 54.07294 + 0.83343 + 0.00 )
• ( 300 62.42651 ) ( 40 / 300 )

[cpt23]

The output from your system is below. The matching was done in the same way, which is very reassuring, although the numbers are slightly different - maybe the CGT calculator uses less precision than your system. Your system's output is more detailed and I like it very much overall, but I find it is actually less clear how the gain/loss was derived. I think at the end it should output a calculation to show how the "Total gain (loss)" was derived. In this example, that line could be "Resulting overall gain for this disposal: £111.30 + £61.48 -£209.51 = -£36.73"

Here is the output from your system for this same disposal (I did not fix the "acquitions" spelling error, comments included starting with ">"):

Disposal XX: Sold 100 units of ABC on 23-May-2022 for £5,718.87. Total gain (loss): -£36.73 All units of the disposals are matched with acquitions Trade details: Sold 100 unit(s) of ABC on 23-May-2022 12:15 for $7,020.00 = £5,719.83 Fx rate = 0.81479 with total expense £0.96, Net proceed: £5,718.87 Expenses: Commission: $1.17 = £0.96 Fx rate = 0.81479
Trade matching: Bed and breakfast: 40 units of the acquition trade against £2,287.55 units of the disposal trade. Acquition cost is £2,176.24 Matched trade: Bought 40 unit(s) of ABC on 06-Jul-2022 10:29 for $2,594.00 = £2,175.41 Fx rate = 0.83863 with total expense £0.84, Total cost: £2,176.24 Expenses: Commission: $1.00 = £0.84 Fx rate = 0.83863
Gain for this match is £2,287.55 - £2,176.24 = £111.30

Your system outputs three empty lines here which I think is too much: it makes it difficult to understand that the two "Bed and > breakfast" matchings are for the same disposal

Bed and breakfast: 20 units of the acquition trade against £1,143.77 units of the disposal trade. Acquition cost is £1,082.29 Matched trade: Bought 20 unit(s) of ABC on 19-Jul-2022 10:05 for $1,297.60 = £1,081.46 Fx rate = 0.83343 with total expense £0.83, Total cost: £1,082.29 Expenses: Commission: $1.00 = £0.83 Fx rate = 0.83343
Gain for this match is £1,143.77 - £1,082.29 = £61.48

Again there is too much whitespace (newlines) here which makes it less clear

At time of disposal, section 104 contains 300 units with value £18,727.95 Section 104: Matched 40 units of the acquition trade against £2,287.55 units of the disposal trade. Acquition cost is £2,497.06 Gain for this match is £2,287.55 - £2,497.06 = -£209.51

Here in the output it would be much clearer to show the final calculation for the gain/loss, i.e.

Resulting overall gain for this disposal: £111.30 + £61.48 -£209.51 = -£36.73

[alexpung]

I have made a fix on this output and some others Bed and breakfast: 40 units of the acquition trade against £2,287.55 units of the disposal trade

[alexpung]

The total gain is simply the sum of these 3 lines
Gain for this match is £2,287.55 - £2,176.24 = £111.30 Gain for this match is £1,143.77 - £1,082.29 = £61.48 Gain for this match is £2,287.55 - £2,497.06 = -£209.51

£111.30 + £61.48 + (-£209.51) = -£36.73

Yes the formatting could be better. Thanks for the feedback.

[cpt23]

Yes, I know how the total gain is calculated in this case but the output could easily include this for better clarity, either like in your comment, or like in the CGT calculator output, or as I suggested: Resulting overall gain for this disposal: £111.30 + £61.48 -£209.51 = -£36.73

[alexpung]

I have made some changes to the output format. You can try that out.

[cpt23]

Thanks! Some minor feedback:

1.) In a line such as "Bed and breakfast: 150 units of the acquisition trade against 150 units of the disposal trade. acquisition cost is" the start of the new sentence should be capitalised, i.e. "Acquisition cost" rather than "acquisition cost".

2.) The summary explanation that you have implemented for each complex disposal as I suggested, i.e. lines like "Resulting overall gain for this disposal: X + Y = Z" are great but there should be an extra new line (empty line) after that before the next block (which starts with "****")

[alexpung]

Thanks for the generous donation, you are the first person to donate to my open source project!

Also thanks for the feedback and your suggestions are implemented and pushed to GitHub. The change should be live shortly.

[cpt23]

Excellent, thank you for all your help!