Forum Discussion
Creating a Trade Journal from Excel Dump
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 |
2 Replies
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?
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 🙂
