Formula to count days if a specific condition is met

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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
A user defined function (udf) is much simpler than a formula, for me.
Put this code into a standard module of your vba project:
VBA Code:
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
Excel 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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

Book3.xlsx
ABCD
1TxSymbolDateCount
2BuyCGX10/10/2020 
3BuyVET10/15/2020 
4BuyCNK10/16/2020 
5BuyCNQ10/16/2020 
6BuyAC10/29/2020 
7BuyCGX10/31/2020 
8BuyCNQ10/26/2020 
9BuySUN11/5/2020 
10SellCGX11/25/202025
11BuyCNQ11/2/2020 
12SellCNK11/16/202031
13SellAC11/18/202020
14SellCGX11/29/2020 
15BuyAAL11/24/2020 
16SellVET11/26/202042
17SellCNQ11/29/202027
18BuyCGX12/10/202011
19BuyAC12/21/202033
Sheet850
Cell Formulas
RangeFormula
D2:D19D2=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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
RangeFormula
D2D2=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

Board Regular
Joined
Dec 1, 2011
Messages
205

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Can you show samples with desired results?
 

Matador_24

Board Regular
Joined
Dec 1, 2011
Messages
205

ADVERTISEMENT

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:

NameTxSymbolDateCount
Person 1BuyCGX
10/10/2020​
Blank
Person 1BuyVET
10/15/2020​
Blank
Person 2BuyCNK
10/16/2020​
Blank
Person 2BuyCNQ
10/16/2020​
Blank
Person 3BuyAC
10/29/2020​
Blank
Person 3BuyCGX
10/31/2020​
Blank
Person 1BuyCNQ
10/26/2020​
Blank
Person 2BuySUN
11/5/2020​
Blank
Person 2SellCGX
11/25/2020​
Blank
Person 3BuyCNQ
11/2/2020​
Blank
Person 2SellCNK
11/16/2020​
31​
Person 2SellAC
11/18/2020​
Blank
Person 1SellCGX
11/29/2020​
Blank
Person 1BuyAAL
11/24/2020​
Blank
Person 1SellVET
11/26/2020​
42​
Person 2SellCNQ
11/29/2020​
Blank
Person 3BuyCGX
12/10/2020​
Blank
Person 2BuyAC
12/21/2020​
33​
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
I think you have incorrect Result/Count in your sample data:

Book3.xlsx
ABCDEF
1NameTxSymbolDateCountYou posted
2Person 1BuyCGX10/10/2020 Blank
3Person 1BuyVET10/15/2020 Blank
4Person 2BuyCNK10/16/2020 Blank
5Person 2BuyCNQ10/16/2020 Blank
6Person 3BuyAC10/29/2020 Blank
7Person 3BuyCGX10/31/2020 Blank
8Person 1BuyCNQ10/26/2020 Blank
9Person 2BuySUN11/5/2020 Blank
10Person 2SellCGX11/25/2020 Blank
11Person 3BuyCNQ11/2/2020 Blank
12Person 2SellCNK11/16/20203131
13Person 2SellAC11/18/2020 Blank
14Person 1SellCGX11/29/202050Blank
15Person 1BuyAAL11/24/2020 Blank
16Person 1SellVET11/26/20204242
17Person 2SellCNQ11/29/202044Blank
18Person 3BuyCGX12/10/2020 Blank
19Person 2BuyAC12/21/20203333
Sheet850
Cell Formulas
RangeFormula
E2:E19E2=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)),"")
 
Solution

Matador_24

Board Regular
Joined
Dec 1, 2011
Messages
205
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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top