Creating a Trade Journal from Excel Dump

[exodusme]

Hello, apologies if my question isn't clear but hopefully I can explain in a way that makes it possible for someone to help.

I have an excel dump that I receive from a stock trading platform and I would like to use that dump to number the trade. It seems like a simple task but for some reason I can't wrap my head around it. Below is an example of the excel dump and the non-existing column. I am guessing the calculation would be based on the 'qty open'. so theoretically the 2 that is bought at 9:41 would be the 1st trade of the day and it occurred over three transactions (the first buy and then 2 sells that followed). Then that trade would be closed and the 2nd trade would begin. In this case, the second trade was one buy and one sell. How would I create this calculation in powerquery? Thanks

Symbol	Side	Type	qty	Price	Time open	time close	trade#
ESH23	Sell	Limit	1	$           3,974.25	1/11/2023 11:16	1/11/2023 11:22	2
ESH23	Buy	Market	1	$           3,970.25	1/11/2023 11:16	1/11/2023 11:16	2
ESH23	Sell	Limit	1	$           3,972.25	1/11/2023 9:41	1/11/2023 9:57	1
ESH23	Sell	Limit	1	$           3,972.25	1/11/2023 9:41	1/11/2023 9:57	1
ESH23	Buy	Stop	2	$           3,967.25	1/11/2023 9:41	1/11/2023 9:41	1

[nickvanmaele]

Hi, 

I presume that you typed in the trade# yourself just as an example. 

Is it possible that your data would have a case where the second 'buy' transaction would occur before the last sell transaction of a previous trade? For example, if the 'buy' of trade# 2 had occurred at 9:55, how would you want the trades to be numbered?

[exodusme]

Hi nickvanmaele, that is true. you could buy buy and then sell sell and in that case I would want that to be 1 trade. Kind of complicated to me, but I am sure someone out there much smarter has the answer hopefully 🙂