Matador_24
Board Regular
- Joined
- Dec 1, 2011
- Messages
- 205
Hello and thanks in advance,
I would like to have a formula or a macro if a formula is not feasible to count the number of days between the moment a position (BUY or SELL) is reversed. Thus between a BUY and SELL or a SELL and a BUY (the 'waiting time' between reversing a position. That means that the transaction (Tx) in the same direction is fine. Thus, if you BUY and then BUY, the count would start from the last time it was bought. If you SELL and then SELL again, when you BUY afterwards, then the count would be between the last SELL and the new BUY. This for each independent symbol.
For example, the table below, when you buy something, it is fine, it can just show blank, however, when you sell it, then to count the number of days that the share was held since the last time it was bought. For example below,
I bought CGX on 10/10/2020 and also on 10/31/2020. BUY and then BUY is fine because it is the same direction, it does not add up the numbers of days. If I SELL CGX on 11/25/2020, then the count should be 25 days (holding period) because it should take the date of the last different direction Tx, BUY on 10/31/2020 and not 10/10/2020. If CGX was SELL again on 11/20/2020 then it is blank because it is the same direction of the last trade (a SELL), the position is not reversed. Then if the same CGX was bought again on 12/20/2020 the count is 11 (the waiting period before doing a transaction in the opposite direction, therefore reversing the position)
I made manually the table below to show the reasoning. and the column COUNT would be the desirable result.
I do not find the best way to do it, if a formula or a macro. In either case, I have doing some tests and without success.
Thanks in advance,
Luis
Tx Symbol Date Count
Buy CGX 10/10/2020 Blank
Buy VET 10/15/2020 Blank
Buy CNK 10/16/2020 Blank
Buy CNQ 10/16/2020 Blank
Buy AC 10/29/2020 Blank
Buy CGX 10/31/2020 Blank
Buy CNQ 10/26/2020 Blank
Buy SUN 11/5/2020 Blank
Sell CGX 11/25/2020 25
Buy CNQ 11/2/2020 Blank
Sell CNK 11/16/2020 31
Sell AC 11/18/2020 20
Sell CGX 11/29/2020 blank
Buy AAL 11/24/2020 Blank
Sell VET 11/26/2020 42
Sell CNQ 11/29/2020 27
Buy CGX 12/10/2020 11
Buy AC 12/21/2020 33
I would like to have a formula or a macro if a formula is not feasible to count the number of days between the moment a position (BUY or SELL) is reversed. Thus between a BUY and SELL or a SELL and a BUY (the 'waiting time' between reversing a position. That means that the transaction (Tx) in the same direction is fine. Thus, if you BUY and then BUY, the count would start from the last time it was bought. If you SELL and then SELL again, when you BUY afterwards, then the count would be between the last SELL and the new BUY. This for each independent symbol.
For example, the table below, when you buy something, it is fine, it can just show blank, however, when you sell it, then to count the number of days that the share was held since the last time it was bought. For example below,
I bought CGX on 10/10/2020 and also on 10/31/2020. BUY and then BUY is fine because it is the same direction, it does not add up the numbers of days. If I SELL CGX on 11/25/2020, then the count should be 25 days (holding period) because it should take the date of the last different direction Tx, BUY on 10/31/2020 and not 10/10/2020. If CGX was SELL again on 11/20/2020 then it is blank because it is the same direction of the last trade (a SELL), the position is not reversed. Then if the same CGX was bought again on 12/20/2020 the count is 11 (the waiting period before doing a transaction in the opposite direction, therefore reversing the position)
I made manually the table below to show the reasoning. and the column COUNT would be the desirable result.
I do not find the best way to do it, if a formula or a macro. In either case, I have doing some tests and without success.
Thanks in advance,
Luis
Tx Symbol Date Count
Buy CGX 10/10/2020 Blank
Buy VET 10/15/2020 Blank
Buy CNK 10/16/2020 Blank
Buy CNQ 10/16/2020 Blank
Buy AC 10/29/2020 Blank
Buy CGX 10/31/2020 Blank
Buy CNQ 10/26/2020 Blank
Buy SUN 11/5/2020 Blank
Sell CGX 11/25/2020 25
Buy CNQ 11/2/2020 Blank
Sell CNK 11/16/2020 31
Sell AC 11/18/2020 20
Sell CGX 11/29/2020 blank
Buy AAL 11/24/2020 Blank
Sell VET 11/26/2020 42
Sell CNQ 11/29/2020 27
Buy CGX 12/10/2020 11
Buy AC 12/21/2020 33