Formula to count days if a specific condition is met

[Matador_24]

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

 

[Anthony47]

A user defined function (udf) is much simpler than a formula, for me.
Put this code into a standard module of your vba project:

Function CkDelay(ByRef myRan As Range) As Variant
Dim myArr, I As Long, cCal As Date, iMax As Long
Dim cTick As String, cOp As String, mySplit, crRep As String
'
crRep = String(myRan.Rows.Count, ",")
mySplit = Split(crRep, ",", , vbTextCompare)
myArr = myRan
For I = UBound(myArr) To 2 Step -1
    If myArr(I, 1) <> "" Then
        If I > iMax Then iMax = I
        cOp = myArr(I, 1)
        cTick = myArr(I, 2)
        cCal = myArr(I, 3)
        For J = I - 1 To 1 Step -1
            If myArr(J, 1) = cOp And myArr(J, 2) = cTick Then Exit For
            If myArr(J, 1) <> cOp And myArr(J, 2) = cTick Then
                mySplit(I - 1) = cCal - myArr(J, 3)
                Exit For
            End If
        Next J
    End If
Next I
ReDim Preserve mySplit(0 To iMax - 1)
CkDelay = Application.WorksheetFunction.Transpose(mySplit)
End Function

Then return to your worksheet; let's suppose your data spans from A2 to C20; enter in D2 the formula

=CkDelay(A2:C20)

This will return an array with the delays you are looking for.

If your Excel version supports Dynamic arrays then you enter the formula as usual in D2 and the results will be extended up to row 20.

Otherwise you have to enter the formula as an "array formula":
-select D2:D20
-input the formula in the formula bar
-confirm the formula by using the keys Contr+Shift+Enter (not only Enter)

The formula indeed accept an input range longer than the one currently populated, ready for adding new lines; so for example you may use =CkDelay(A2:C100), but the return array will span only to the last used row. You can easily exploit this feature if Dynamic array is supported by your Excel; otherwise you will have the error #N/A in the rows not populated.

Try...

 

[jtakw]

Hi,

Here's a formula that seems to do what you want:

Book3.xlsx
	A	B	C	D
1	Tx	Symbol	Date	Count
2	Buy	CGX	10/10/2020
3	Buy	VET	10/15/2020
4	Buy	CNK	10/16/2020
5	Buy	CNQ	10/16/2020
6	Buy	AC	10/29/2020
7	Buy	CGX	10/31/2020
8	Buy	CNQ	10/26/2020
9	Buy	SUN	11/5/2020
10	Sell	CGX	11/25/2020	25
11	Buy	CNQ	11/2/2020
12	Sell	CNK	11/16/2020	31
13	Sell	AC	11/18/2020	20
14	Sell	CGX	11/29/2020
15	Buy	AAL	11/24/2020
16	Sell	VET	11/26/2020	42
17	Sell	CNQ	11/29/2020	27
18	Buy	CGX	12/10/2020	11
19	Buy	AC	12/21/2020	33
Sheet850

Cell Formulas
Range	Formula
D2:D19	D2	=IFERROR(IF(LOOKUP(2,1/(B$1:B1=B2),A$1:A1)=A2,"",C2-LOOKUP(9^9,1/(B$1:B1=B2),C$1:C1)),"")

 

[jtakw]

Just realized, while testing the formula posted above, I forgot to change the 9^9 to 2, formula works either way, just a tad shorter with the 2:

Book3.xlsx
	D
2
Sheet850

Cell Formulas
Range	Formula
D2	D2	=IFERROR(IF(LOOKUP(2,1/(B$1:B1=B2),A$1:A1)=A2,"",C2-LOOKUP(2,1/(B$1:B1=B2),C$1:C1)),"")

 

[Matador_24]

Thanks a lot, it works!

How can I add another little complication? for instance an additional column E with the name of the trader.
Thus, the formula would count only the BUY/SELL for the same person. The formula would do the same that you accomplished but only counting for each person,

Thanks,

Luis

 

[jtakw]

Can you show samples with desired results?

 

[Matador_24]

Can you show samples with desired results?

Yes, Thanks. See below same table but adding Person names: Person 1, Person 2, Person 3.
The extra step would be that the count counts only when the same Person is buying and selling (or viceversa) the share. If it is other person, then it does not take it into consideration:

Name	Tx	Symbol	Date	Count
Person 1	Buy	CGX	10/10/2020​	Blank
Person 1	Buy	VET	10/15/2020​	Blank
Person 2	Buy	CNK	10/16/2020​	Blank
Person 2	Buy	CNQ	10/16/2020​	Blank
Person 3	Buy	AC	10/29/2020​	Blank
Person 3	Buy	CGX	10/31/2020​	Blank
Person 1	Buy	CNQ	10/26/2020​	Blank
Person 2	Buy	SUN	11/5/2020​	Blank
Person 2	Sell	CGX	11/25/2020​	Blank
Person 3	Buy	CNQ	11/2/2020​	Blank
Person 2	Sell	CNK	11/16/2020​	31​
Person 2	Sell	AC	11/18/2020​	Blank
Person 1	Sell	CGX	11/29/2020​	Blank
Person 1	Buy	AAL	11/24/2020​	Blank
Person 1	Sell	VET	11/26/2020​	42​
Person 2	Sell	CNQ	11/29/2020​	Blank
Person 3	Buy	CGX	12/10/2020​	Blank
Person 2	Buy	AC	12/21/2020​	33​

 

[jtakw]

I think you have incorrect Result/Count in your sample data:

Book3.xlsx
	A	B	C	D	E	F
1	Name	Tx	Symbol	Date	Count	You posted
2	Person 1	Buy	CGX	10/10/2020		Blank
3	Person 1	Buy	VET	10/15/2020		Blank
4	Person 2	Buy	CNK	10/16/2020		Blank
5	Person 2	Buy	CNQ	10/16/2020		Blank
6	Person 3	Buy	AC	10/29/2020		Blank
7	Person 3	Buy	CGX	10/31/2020		Blank
8	Person 1	Buy	CNQ	10/26/2020		Blank
9	Person 2	Buy	SUN	11/5/2020		Blank
10	Person 2	Sell	CGX	11/25/2020		Blank
11	Person 3	Buy	CNQ	11/2/2020		Blank
12	Person 2	Sell	CNK	11/16/2020	31	31
13	Person 2	Sell	AC	11/18/2020		Blank
14	Person 1	Sell	CGX	11/29/2020	50	Blank
15	Person 1	Buy	AAL	11/24/2020		Blank
16	Person 1	Sell	VET	11/26/2020	42	42
17	Person 2	Sell	CNQ	11/29/2020	44	Blank
18	Person 3	Buy	CGX	12/10/2020		Blank
19	Person 2	Buy	AC	12/21/2020	33	33
Sheet850

Cell Formulas
Range	Formula
E2:E19	E2	=IFERROR(IF(LOOKUP(2,1/((C$1:C1=C2)*(A$1:A1=A2)),B$1:B1)=B2,"",D2-LOOKUP(2,1/((C$1:C1=C2)*(A$1:A1=A2)),D$1:D1)),"")

 

[Matador_24]

Hello,

You are completely right! thanks a lot! it works!

That is a too advance formula to me, I do not even understand it. How can I learn what you did? it seems vectors? or how do you call them?

Thanks,

Luis

 

[jtakw]

You're welcome, thanks for the feedback.