Buy-side commissions not taken into account

[mirono]

Great work! Not really an issue, but rather some questions:

• Why subtract commission on the sale but not on the buy?
• In case of short trades, the CSV include a section at the end with short borrowing fees - I assume they are not included in the calculation from what I could see in the code.

[almog]

Thanks for the questions, they point to some intricacies I'm not sure I can answer with confidence, but here is what I think I know:
Assuming we use this format of the statement, the "Display closing trades only" is checked, which results in a CSV structure that shows the one-to-many Trade to ClosedLot structure in adjacent rows. However, by applying this option, we filter out some rows from the report, namely the buy-side trade rows, which would have included the fees on that trade. That is why it is only subtracted from the sale.
While it might be a reasonable explanation, your questions does point to a bug (or a missing feature) — not all fees are included in output 1325 form which right now only processes the "Trades" section.

Possibly a better solution than the current one would be to join each lot with its matching entry in the commissions part of the CSV. I think that (symbol, date+time, quantity) could be used as a composite key joining the two tables.

I think it might also solve the borrowing fees assuming the borrowing fees are included in the fees section, can you confirm that?

[mirono]

In "my" report, the borrowing fees are grouped by month, .i.e.:
Interest,Header,Currency,Date,Description,Amount
Interest,Data,USD,2022-02-03,USD Borrow Fees for Jan-2022,-155.14
Interest,Data,USD,2022-03-03,USD Borrow Fees for Feb-2022,-18.68
...
Interest,Data,USD,2022-10-05,USD Borrow Fees for Sep-2022,-7.72
Interest,Data,USD,2022-11-03,USD Borrow Fees for Oct-2022,-21.53
Interest,Data,USD,2022-12-05,USD Borrow Fees for Nov-2022,-24.95
Interest,Data,Total,,,-544.76

But maybe there are settings in the report generation page that can cause them to be added to each closed lot.

The fees section also include exchange fees that you can deduct as expenses:
Fees,Data,Other Fees,USD,2022-07-05,m90:NYSE Level I for Jul 2022,-1.5
Fees,Data,Other Fees,USD,2022-08-03,m90:AMEX Level I for Aug 2022,-1.5
Fees,Data,Other Fees,USD,2022-08-03,m90:CME Level II Non-Professional for Jul 2022,11
Fees,Data,Other Fees,USD,2022-08-03,m90:CME Level II Non-Professional for Aug 2022,-11
Fees,Data,Other Fees,USD,2022-08-03,m90:NASDAQ Level I for Aug 2022,-1.5
Fees,Data,Other Fees,USD,2022-08-03,m90:NYSE Level I for Aug 2022,-1.5
Fees,Data,Other Fees,USD,2022-09-05,m90:AMEX Level I for Sep 2022,-1.5
Fees,Data,Other Fees,USD,2022-09-05,m90:CME Level II Non-Professional for Aug 2022,11
Fees,Data,Other Fees,USD,2022-09-05,m******90:CME Level II Non-Professional for Sep 2022,-11
.....

In my case they are mostly rebated by IB, not all of them.

[almog]

Regarding transaction fees ("Commision Details" section), both Broker Charges (execution + clearing) and Third-Party Charges (Execution, Clearing and Transaction Fees) are all summed together into the Commission column (that's what I noticed on a small example) and using the composite key I believe they can be matched to both the Trade and ClosedeLot.

As the the borrowing fees, I'm not sure what's the correct way to present them, I'll have to check if the Date matches the date at which the borrowed asset was bought back, if so, I guess it can be added to the closing transaction*. If not, it might needs to go into a different row in the 1325, but I'm not sure how that should look like if it does not have both buy-side and sell side? I'm really not sure about that.

Out of these two issues I'll prioritize changing how the commissions are calculated, taking the entire commissions into account (rather than just the closing trade commissions which is the current implementation).

(*) I didn't mention it explicitly earlier, but the composite key that I suggested (symbol, date time, quantity) is not guaranteed to be unique if you're a high frequency trader. It is even less likely to be unique in the borrowing section since it only lists date rather than time, which lead me to suspect that it might not be linked directly to a transaction, but if the borrowing fee changes daily, that might not matter after all.

[almog]

After I tried to join the Trade schema with the Commission schema based on the (symbol, date, quantity) currency I found out two facets of the same thing which will add some complexity:

1. While Trade rows have date and time, ClosedLot rows only have date.
2. The reason for (1) is the source of the complexity, and that is - if until now I thought that Trade and ClosedLot could be joined following a 1-to-many relationship, now I realized there's an additional (nested) 1-to-many relationship that needs to be joined: I noticed that some ClosedLot were not joined with a line from the "Commission Details" pandas dataframe, and the reason for that, other than the date/datetime mismatch, was that a specific lot of say 400 stocks, was listed on the commissions table (dataframe) as two rows of 372 and 28 stocks.
   How is it a nested 1-to-many relationship? If you uncheck the "Display Closing Trades Only?" on your custom statement, you'll find a Trade line for each of the buy-side trades (additional individual rows of 372 and 28 stocks before the Trade-ClosedLot section).

These lines, unlike the ClosedLot which may combine multiple buy-side trades with the same price, also have date and time.
Joining the buy-side Trade rows with the Commission is easy using simple join on Pandas' multiindex.

However, the entity that should be presented in the 1325 form is each of the (instrumented) ClosedLot, which means that each ClosedLot should be matched to the buying trade, hopefully this can be done based on (symbol, date). If we want to be accurate regarding varying commission rates across different orders (of the same symbol within the same day), we'll have to also update the remaining qty after matching each ClosedLot to its buy-side Trade row (assuming it's already already joined with the commission table), but that might be an overkill and we can assume that commissions should not vary wildly within the same day.

[JacobPyC]

C:\Users\jacob\Downloads\Files\IBKR-to-1325-form-main\IBKR-to-1325-form-main>python src
Traceback (most recent call last):
File "C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.8_3.8.2800.0_x64__qbz5n2kfra8p0\lib\runpy.py", line 194, in _run_module_as_main
return _run_code(code, main_globals, None,
File "C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.8_3.8.2800.0_x64__qbz5n2kfra8p0\lib\runpy.py", line 87, in run_code
exec(code, run_globals)
File "C:\Users\jacob\Downloads\Files\IBKR-to-1325-form-main\IBKR-to-1325-form-main\src_main.py", line 15, in
def get_input_paths() -> tuple[str, str]:
TypeError: 'type' object is not subscriptable
יש לך מושג מה הבעיה? חחח אין לי כוח לדבג זה אמור לעבוד לא?

[almog]

@JacobPyC You're using an older version of Python which does not support type hinting generics in standard collections (e.g. tuple[str, str]).

Besides that, while I'd gladly try to assist you if you encounter further issues and despite the title ("Questions"), this isn't a catch-all issue for support (I'll change the title to reflect the nature of the issue I discussed with with @mirono here). If any further issue arise which you cannot find solution to, please open a new issue.

[mirono]

However, the entity that should be presented in the 1325 form is each of the (instrumented) ClosedLot, which means that each ClosedLot should be matched to the buying trade, hopefully this can be done based on (symbol, date). If we want to be accurate regarding varying commission rates across different orders (of the same symbol within the same day), we'll have to also update the remaining qty after matching each ClosedLot to its buy-side Trade row (assuming it's already already joined with the commission table), but that might be an overkill and we can assume that commissions should not vary wildly within the same day.

I agree that same day stock commissions probably refer to the same closed lot. If overkill I would just add them to the nearest symbol, date closed lot.