Finding maximum drop a stock has experienced

Siraj · July 12, 2023, 10:15am

I am trying to find the maximum a stock has dropped from its recent High in last 50 days.

Filter 		=  


D1          =	IIf(C< Ref(HHV(C,50),-1), (100 * (Ref(HHV(C,50),-1) -C) )/C, 0);
D2          =	IIf(REF(C,-1)< Ref(HHV(C,49),-2), (100 * (Ref(HHV(C,49),-2) -REF(C, -1))/REF(C, -1)), 0);
D3          =	IIf(REF(C,-2)< Ref(HHV(C,48),-3), (100 * (Ref(HHV(C,48),-3) -REF(C, -2))/REF(C, -2)), 0);
D4			=	IIf(REF(C,-3)< Ref(HHV(C,47),-4), (100 * (Ref(HHV(C,47),-4) -REF(C, -3))/REF(C, -3)), 0);
D5			=	IIf(REF(C,-4)< Ref(HHV(C,46),-5), (100 * (Ref(HHV(C,46),-5) -REF(C, -4))/REF(C, -4)), 0);
D6			=	IIf(REF(C,-5)< Ref(HHV(C,45),-6), (100 * (Ref(HHV(C,45),-6) -REF(C, -5))/REF(C, -5)), 0);
D7			=	IIf(REF(C,-6)< Ref(HHV(C,44),-7), (100 * (Ref(HHV(C,44),-7) -REF(C, -6))/REF(C, -6)), 0);
D8			=	IIf(REF(C,-7)< Ref(HHV(C,43),-8), (100 * (Ref(HHV(C,43),-8) -REF(C, -7))/REF(C, -7)), 0);
D9			=	IIf(REF(C,-8)< Ref(HHV(C,42),-9), (100 * (Ref(HHV(C,42),-9) -REF(C, -8))/REF(C, -8)), 0);
D10			=	IIf(REF(C,-9)< Ref(HHV(C,41),-10), (100 * (Ref(HHV(C,41),-10) -REF(C, -9))/REF(C, -9)), 0);
D11			=	IIf(REF(C,-10)< Ref(HHV(C,40),-11), (100 * (Ref(HHV(C,40),-11) -REF(C, -10))/REF(C, -10)), 0);
D12			=	IIf(REF(C,-11)< Ref(HHV(C,39),-12), (100 * (Ref(HHV(C,39),-12) -REF(C, -11))/REF(C, -11)), 0);
D13			=	IIf(REF(C,-12)< Ref(HHV(C,38),-13), (100 * (Ref(HHV(C,38),-13) -REF(C, -12))/REF(C, -12)), 0);
D14			=	IIf(REF(C,-13)< Ref(HHV(C,37),-14), (100 * (Ref(HHV(C,37),-14) -REF(C, -13))/REF(C, -13)), 0);
D15			=	IIf(REF(C,-14)< Ref(HHV(C,36),-15), (100 * (Ref(HHV(C,36),-15) -REF(C, -14))/REF(C, -14)), 0);
D16			=	IIf(REF(C,-15)< Ref(HHV(C,35),-16), (100 * (Ref(HHV(C,35),-16) -REF(C, -15))/REF(C, -15)), 0);
D17			=	IIf(REF(C,-16)< Ref(HHV(C,34),-17), (100 * (Ref(HHV(C,34),-17) -REF(C, -16))/REF(C, -16)), 0);
D18			=	IIf(REF(C,-17)< Ref(HHV(C,33),-18), (100 * (Ref(HHV(C,33),-18) -REF(C, -17))/REF(C, -17)), 0);
D19			=	IIf(REF(C,-18)< Ref(HHV(C,32),-19), (100 * (Ref(HHV(C,32),-19) -REF(C, -18))/REF(C, -18)), 0);
D20			=	IIf(REF(C,-19)< Ref(HHV(C,31),-20), (100 * (Ref(HHV(C,31),-20) -REF(C, -19))/REF(C, -19)), 0);
D21			=	IIf(REF(C,-20)< Ref(HHV(C,30),-21), (100 * (Ref(HHV(C,30),-21) -REF(C, -20))/REF(C, -20)), 0);
D22			=	IIf(REF(C,-21)< Ref(HHV(C,29),-22), (100 * (Ref(HHV(C,29),-22) -REF(C, -21))/REF(C, -21)), 0);
D23			=	IIf(REF(C,-22)< Ref(HHV(C,28),-23), (100 * (Ref(HHV(C,28),-23) -REF(C, -22))/REF(C, -22)), 0);
D24			=	IIf(REF(C,-23)< Ref(HHV(C,27),-24), (100 * (Ref(HHV(C,27),-24) -REF(C, -23))/REF(C, -23)), 0);
D25			=	IIf(REF(C,-24)< Ref(HHV(C,26),-25), (100 * (Ref(HHV(C,26),-25) -REF(C, -24))/REF(C, -24)), 0);
D26			=	IIf(REF(C,-25)< Ref(HHV(C,25),-26), (100 * (Ref(HHV(C,25),-26) -REF(C, -25))/REF(C, -25)), 0);
D27			=	IIf(REF(C,-26)< Ref(HHV(C,24),-27), (100 * (Ref(HHV(C,24),-27) -REF(C, -26))/REF(C, -26)), 0);
D28			=	IIf(REF(C,-27)< Ref(HHV(C,23),-28), (100 * (Ref(HHV(C,23),-28) -REF(C, -27))/REF(C, -27)), 0);
D29			=	IIf(REF(C,-28)< Ref(HHV(C,22),-29), (100 * (Ref(HHV(C,22),-29) -REF(C, -28))/REF(C, -28)), 0);
D30			=	IIf(REF(C,-29)< Ref(HHV(C,21),-30), (100 * (Ref(HHV(C,21),-30) -REF(C, -29))/REF(C, -29)), 0);
D31			=	IIf(REF(C,-30)< Ref(HHV(C,20),-31), (100 * (Ref(HHV(C,20),-31) -REF(C, -30))/REF(C, -30)), 0);
D32			=	IIf(REF(C,-31)< Ref(HHV(C,19),-32), (100 * (Ref(HHV(C,19),-32) -REF(C, -31))/REF(C, -31)), 0);
D33			=	IIf(REF(C,-32)< Ref(HHV(C,18),-33), (100 * (Ref(HHV(C,18),-33) -REF(C, -32))/REF(C, -32)), 0);
D34			=	IIf(REF(C,-33)< Ref(HHV(C,17),-34), (100 * (Ref(HHV(C,17),-34) -REF(C, -33))/REF(C, -33)), 0);
D35			=	IIf(REF(C,-34)< Ref(HHV(C,16),-35), (100 * (Ref(HHV(C,16),-35) -REF(C, -34))/REF(C, -34)), 0);
D36			=	IIf(REF(C,-35)< Ref(HHV(C,15),-36), (100 * (Ref(HHV(C,15),-36) -REF(C, -35))/REF(C, -35)), 0);
D37			=	IIf(REF(C,-36)< Ref(HHV(C,14),-37), (100 * (Ref(HHV(C,14),-37) -REF(C, -36))/REF(C, -36)), 0);
D38			=	IIf(REF(C,-37)< Ref(HHV(C,13),-38), (100 * (Ref(HHV(C,13),-38) -REF(C, -37))/REF(C, -37)), 0);
D39			=	IIf(REF(C,-38)< Ref(HHV(C,12),-39), (100 * (Ref(HHV(C,12),-39) -REF(C, -38))/REF(C, -38)), 0);
D40			=	IIf(REF(C,-39)< Ref(HHV(C,11),-40), (100 * (Ref(HHV(C,11),-40) -REF(C, -39))/REF(C, -39)), 0);
D41			=	IIf(REF(C,-40)< Ref(HHV(C,10),-41), (100 * (Ref(HHV(C,10),-41) -REF(C, -40))/REF(C, -40)), 0);
D42			=	IIf(REF(C,-41)< Ref(HHV(C,9),-42), (100 * (Ref(HHV(C,9),-42) -REF(C, -41))/REF(C, -41)), 0);
D43			=	IIf(REF(C,-42)< Ref(HHV(C,8),-43), (100 * (Ref(HHV(C,8),-43) -REF(C, -42))/REF(C, -42)), 0);
D44			=	IIf(REF(C,-43)< Ref(HHV(C,7),-44), (100 * (Ref(HHV(C,7),-44) -REF(C, -43))/REF(C, -43)), 0);
D45			=	IIf(REF(C,-44)< Ref(HHV(C,6),-45), (100 * (Ref(HHV(C,6),-45) -REF(C, -44))/REF(C, -44)), 0);
D46			=	IIf(REF(C,-45)< Ref(HHV(C,5),-46), (100 * (Ref(HHV(C,5),-46) -REF(C, -45))/REF(C, -45)), 0);


M1			=	Max(Max(Max(D1, D2), Max(D3, D4)), Max(Max(D5, D6), Max(D7, D8)));
M2			=	Max(Max(Max(D9, D10), Max(D11, D12)), Max(Max(D13, D14), Max(D15, D16)));
M3			=	Max(Max(Max(D17,D18), Max(D19,D20)), Max(Max(D21,D22), Max(D23,D24)));
M4			=	Max(Max(Max(D25,D26), Max(D27,D28)), Max(Max(D29,D30), Max(D31,D32)));
M5			=	Max(Max(Max(D33,D34), Max(D35,D36)), Max(Max(D37,D38), Max(D39,D40)));
M6			=	Max(Max(Max(D41, D42), Max(D43, D44)), Max(D45, D46));

MF 			=	Max(Max(Max(M1, M2), Max(M3, M4)), Max(M5, M6));

this is pure armature way, but I am sure there should be some easy way to use it more effectively .
Thanks

fxshrat · July 12, 2023, 10:41am

Look at "2. Underwater Equity" AFL of Report Charts folder of Charts window.
There you have it already.

Just modify to use HHV/LLV instead of Highest/Lowest.

// Modified from "2. Underwater Equity" AFL 
// of Report Charts folder 
EQ = C;
MaxEQ = HHV( EQ, 50 );
DD = 100 * ( Eq - MaxEQ ) / MaxEq;
MaxDD = LLV( DD, 50 );

Plot( MaxDD, "MaxDD of 50 bars", colorRed );

Siraj · July 12, 2023, 2:02pm

@fxshrat thanks for replying my post, your code is nice, but I am surprised why the results differs too much.

Buy		=	1;
Sell	=	0;

EQ = C;
MaxEQ = HHV( EQ, 50 );
DD = 100 * ( Eq - MaxEQ ) / MaxEq;
MaxDD = LLV( DD, 50 );

Plot( MaxDD, "MaxDD of 50 bars", colorRed );

D1          =	IIf(C< Ref(HHV(C,50),-1), (100 * (Ref(HHV(C,50),-1) -C) )/C, 0);
D2          =	IIf(REF(C,-1)< Ref(HHV(C,49),-2), (100 * (Ref(HHV(C,49),-2) -REF(C, -1))/REF(C, -1)), 0);
D3          =	IIf(REF(C,-2)< Ref(HHV(C,48),-3), (100 * (Ref(HHV(C,48),-3) -REF(C, -2))/REF(C, -2)), 0);
D4			=	IIf(REF(C,-3)< Ref(HHV(C,47),-4), (100 * (Ref(HHV(C,47),-4) -REF(C, -3))/REF(C, -3)), 0);
D5			=	IIf(REF(C,-4)< Ref(HHV(C,46),-5), (100 * (Ref(HHV(C,46),-5) -REF(C, -4))/REF(C, -4)), 0);
D6			=	IIf(REF(C,-5)< Ref(HHV(C,45),-6), (100 * (Ref(HHV(C,45),-6) -REF(C, -5))/REF(C, -5)), 0);
D7			=	IIf(REF(C,-6)< Ref(HHV(C,44),-7), (100 * (Ref(HHV(C,44),-7) -REF(C, -6))/REF(C, -6)), 0);
D8			=	IIf(REF(C,-7)< Ref(HHV(C,43),-8), (100 * (Ref(HHV(C,43),-8) -REF(C, -7))/REF(C, -7)), 0);
D9			=	IIf(REF(C,-8)< Ref(HHV(C,42),-9), (100 * (Ref(HHV(C,42),-9) -REF(C, -8))/REF(C, -8)), 0);
D10			=	IIf(REF(C,-9)< Ref(HHV(C,41),-10), (100 * (Ref(HHV(C,41),-10) -REF(C, -9))/REF(C, -9)), 0);
D11			=	IIf(REF(C,-10)< Ref(HHV(C,40),-11), (100 * (Ref(HHV(C,40),-11) -REF(C, -10))/REF(C, -10)), 0);
D12			=	IIf(REF(C,-11)< Ref(HHV(C,39),-12), (100 * (Ref(HHV(C,39),-12) -REF(C, -11))/REF(C, -11)), 0);
D13			=	IIf(REF(C,-12)< Ref(HHV(C,38),-13), (100 * (Ref(HHV(C,38),-13) -REF(C, -12))/REF(C, -12)), 0);
D14			=	IIf(REF(C,-13)< Ref(HHV(C,37),-14), (100 * (Ref(HHV(C,37),-14) -REF(C, -13))/REF(C, -13)), 0);
D15			=	IIf(REF(C,-14)< Ref(HHV(C,36),-15), (100 * (Ref(HHV(C,36),-15) -REF(C, -14))/REF(C, -14)), 0);
D16			=	IIf(REF(C,-15)< Ref(HHV(C,35),-16), (100 * (Ref(HHV(C,35),-16) -REF(C, -15))/REF(C, -15)), 0);
D17			=	IIf(REF(C,-16)< Ref(HHV(C,34),-17), (100 * (Ref(HHV(C,34),-17) -REF(C, -16))/REF(C, -16)), 0);
D18			=	IIf(REF(C,-17)< Ref(HHV(C,33),-18), (100 * (Ref(HHV(C,33),-18) -REF(C, -17))/REF(C, -17)), 0);
D19			=	IIf(REF(C,-18)< Ref(HHV(C,32),-19), (100 * (Ref(HHV(C,32),-19) -REF(C, -18))/REF(C, -18)), 0);
D20			=	IIf(REF(C,-19)< Ref(HHV(C,31),-20), (100 * (Ref(HHV(C,31),-20) -REF(C, -19))/REF(C, -19)), 0);
D21			=	IIf(REF(C,-20)< Ref(HHV(C,30),-21), (100 * (Ref(HHV(C,30),-21) -REF(C, -20))/REF(C, -20)), 0);
D22			=	IIf(REF(C,-21)< Ref(HHV(C,29),-22), (100 * (Ref(HHV(C,29),-22) -REF(C, -21))/REF(C, -21)), 0);
D23			=	IIf(REF(C,-22)< Ref(HHV(C,28),-23), (100 * (Ref(HHV(C,28),-23) -REF(C, -22))/REF(C, -22)), 0);
D24			=	IIf(REF(C,-23)< Ref(HHV(C,27),-24), (100 * (Ref(HHV(C,27),-24) -REF(C, -23))/REF(C, -23)), 0);
D25			=	IIf(REF(C,-24)< Ref(HHV(C,26),-25), (100 * (Ref(HHV(C,26),-25) -REF(C, -24))/REF(C, -24)), 0);
D26			=	IIf(REF(C,-25)< Ref(HHV(C,25),-26), (100 * (Ref(HHV(C,25),-26) -REF(C, -25))/REF(C, -25)), 0);
D27			=	IIf(REF(C,-26)< Ref(HHV(C,24),-27), (100 * (Ref(HHV(C,24),-27) -REF(C, -26))/REF(C, -26)), 0);
D28			=	IIf(REF(C,-27)< Ref(HHV(C,23),-28), (100 * (Ref(HHV(C,23),-28) -REF(C, -27))/REF(C, -27)), 0);
D29			=	IIf(REF(C,-28)< Ref(HHV(C,22),-29), (100 * (Ref(HHV(C,22),-29) -REF(C, -28))/REF(C, -28)), 0);
D30			=	IIf(REF(C,-29)< Ref(HHV(C,21),-30), (100 * (Ref(HHV(C,21),-30) -REF(C, -29))/REF(C, -29)), 0);
D31			=	IIf(REF(C,-30)< Ref(HHV(C,20),-31), (100 * (Ref(HHV(C,20),-31) -REF(C, -30))/REF(C, -30)), 0);
D32			=	IIf(REF(C,-31)< Ref(HHV(C,19),-32), (100 * (Ref(HHV(C,19),-32) -REF(C, -31))/REF(C, -31)), 0);
D33			=	IIf(REF(C,-32)< Ref(HHV(C,18),-33), (100 * (Ref(HHV(C,18),-33) -REF(C, -32))/REF(C, -32)), 0);
D34			=	IIf(REF(C,-33)< Ref(HHV(C,17),-34), (100 * (Ref(HHV(C,17),-34) -REF(C, -33))/REF(C, -33)), 0);
D35			=	IIf(REF(C,-34)< Ref(HHV(C,16),-35), (100 * (Ref(HHV(C,16),-35) -REF(C, -34))/REF(C, -34)), 0);
D36			=	IIf(REF(C,-35)< Ref(HHV(C,15),-36), (100 * (Ref(HHV(C,15),-36) -REF(C, -35))/REF(C, -35)), 0);
D37			=	IIf(REF(C,-36)< Ref(HHV(C,14),-37), (100 * (Ref(HHV(C,14),-37) -REF(C, -36))/REF(C, -36)), 0);
D38			=	IIf(REF(C,-37)< Ref(HHV(C,13),-38), (100 * (Ref(HHV(C,13),-38) -REF(C, -37))/REF(C, -37)), 0);
D39			=	IIf(REF(C,-38)< Ref(HHV(C,12),-39), (100 * (Ref(HHV(C,12),-39) -REF(C, -38))/REF(C, -38)), 0);
D40			=	IIf(REF(C,-39)< Ref(HHV(C,11),-40), (100 * (Ref(HHV(C,11),-40) -REF(C, -39))/REF(C, -39)), 0);
D41			=	IIf(REF(C,-40)< Ref(HHV(C,10),-41), (100 * (Ref(HHV(C,10),-41) -REF(C, -40))/REF(C, -40)), 0);
D42			=	IIf(REF(C,-41)< Ref(HHV(C,9),-42), (100 * (Ref(HHV(C,9),-42) -REF(C, -41))/REF(C, -41)), 0);
D43			=	IIf(REF(C,-42)< Ref(HHV(C,8),-43), (100 * (Ref(HHV(C,8),-43) -REF(C, -42))/REF(C, -42)), 0);
D44			=	IIf(REF(C,-43)< Ref(HHV(C,7),-44), (100 * (Ref(HHV(C,7),-44) -REF(C, -43))/REF(C, -43)), 0);
D45			=	IIf(REF(C,-44)< Ref(HHV(C,6),-45), (100 * (Ref(HHV(C,6),-45) -REF(C, -44))/REF(C, -44)), 0);
D46			=	IIf(REF(C,-45)< Ref(HHV(C,5),-46), (100 * (Ref(HHV(C,5),-46) -REF(C, -45))/REF(C, -45)), 0);


M1			=	Max(Max(Max(D1, D2), Max(D3, D4)), Max(Max(D5, D6), Max(D7, D8)));
M2			=	Max(Max(Max(D9, D10), Max(D11, D12)), Max(Max(D13, D14), Max(D15, D16)));
M3			=	Max(Max(Max(D17,D18), Max(D19,D20)), Max(Max(D21,D22), Max(D23,D24)));
M4			=	Max(Max(Max(D25,D26), Max(D27,D28)), Max(Max(D29,D30), Max(D31,D32)));
M5			=	Max(Max(Max(D33,D34), Max(D35,D36)), Max(Max(D37,D38), Max(D39,D40)));
M6			=	Max(Max(Max(D41, D42), Max(D43, D44)), Max(D45, D46));

MF 			=	Max(Max(Max(M1, M2), Max(M3, M4)), Max(M5, M6));

if ( Status( "action" ) == actionExplore )
{    
    Filter 	=	IIf(Buy,1,0);
	AddColumn(MF,"  MF   ", 1.2);
    AddColumn(MaxDD,"  MaxDD   ", 1.2);
}

I have attached screen shot of my exploration and end result from chart.

fxshrat · July 12, 2023, 10:34pm

Because it is different.

This one

and all others should be (C-Ref(HHV(C,50),-1)) / Ref(HHV(C,50),-1) etc.
Values of your picture -> (20898.4 - 22650.8) / 22650.8 = -7.73

So,

mini = 1e7;
bars = 50;
for ( i = 0; i < bars-4; i++ ) {
	hh = Ref(HHV(C,bars-i),-1-i);
	dd = (Ref(C,-i) - hh) / hh;
	mini = Min(mini,dd);
}
mini *= 100;

Plot(mini, "min"+bars, colorOrange );

But that's different from how MaxDD of post #2 is calculated.

Siraj · July 13, 2023, 8:10am

works flawless perfect !!!!
Once again thanking you fxshrat

system · October 21, 2023, 8:10am

This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.