Nearest above and nearest below price

There are 4 indicators (lets say a, b, c and d) and the price. I need to find the nearest above price and the nearest below price. I was planning to first sort the all the 5 items using an array but i think afl does not allow user defined array dimensions but only barcount based ones. Any idea how i can do this?

First thought -

  1. Create a matrix
  2. Insert in a matrix the end result of a loop of a,b,c,d minus the price ( you can add an iff ( above > 0 …) iff( below < 0) statement or create two separate matrices for two cases ).
  3. Sort the records
  4. Pick the the lucky winner
4 Likes

@waverider I am not certain what you were searching for, but with a series of nested immediate IF functions and greater than or less than inequality checks you can find the "closest" above or below. Not as elegant as @teyano suggestion, and I don't have your secret indicators but to Explore this possible solution I just used standard deviations.

// Closest Above

price = Close;
IndicatorA = StDev( C, 20 ) + Close;
IndicatorB = 2 * StDev( C, 20 ) + Close;
IndicatorC = -StDev( C, 20 ) + Close;
IndicatorD = -2 * StDev( C, 20 ) + Close;

ClosestAbove = IIf( IndicatorA > price AND
                    ( IndicatorA < IndicatorB OR IndicatorB < price ) AND
                    ( IndicatorA < IndicatorC OR IndicatorC < price ) AND
                    ( IndicatorA < IndicatorD OR IndicatorD < price ),
                    IndicatorA, IIf( IndicatorB > price AND
                                     ( IndicatorB < IndicatorA OR IndicatorA < price ) AND
                                     ( IndicatorB < IndicatorC OR IndicatorC < price ) AND
                                     ( IndicatorB < IndicatorD OR IndicatorD < price ),
                                     IndicatorB, IIf( IndicatorC > price AND
                                             ( IndicatorC < IndicatorA OR IndicatorA < price ) AND
                                             ( IndicatorC < IndicatorB OR IndicatorB < price ) AND
                                             ( IndicatorC < IndicatorD OR IndicatorD < price ),
                                             IndicatorC,
                                             IndicatorD ) ) );

Filter = 1;
AddColumn( price, "Price" );
AddColumn( ClosestAbove, "ClosestAbove" );
AddColumn( IndicatorA, "IndicatorA" );
AddColumn( IndicatorB, "IndicatorB" );
AddColumn( IndicatorC, "IndicatorC" );
AddColumn( IndicatorD, "IndicatorD" );

image

You can easily use the same logic to create a version for "Closest Below" and you could probably do the same calculations with a loop in half as many lines.

3 Likes

@portfoliobuilder has already provided a solution, but for what it's worth, here's an alternative using Min. Two ways to skin the same cat it would appear. I think he wins with less lines of code though... :wink:

If you're wondering why we couldn't just use one big nested Min function, it's because zeros or Nulls will give an undesired result when using Min, so we first have to isolate which indicators are above.

It should be easy enough to switch for closest below using Max instead.


MA1 = MA(C, 5);
MA2 = MA(C, 20);
MA3 = MA(C, 100);
MA4 = MA(C, 200);

MA1Diff = MA1 - C;
MA2Diff = MA2 - C;
MA3Diff = MA3 - C;
MA4Diff = MA4 - C;

AboveCount = ((MA1Diff >= 0) * 1) + 
			((MA2Diff >= 0) * 2) + 
			((MA3Diff >= 0) * 4) + 
			((MA4Diff >= 0) * 8);
			
LowestDiffAbove = 
			IIf(AboveCount == 1, MA1Diff, 
			IIf(AboveCount == 2, MA2Diff, 
			IIf(AboveCount == 3, Min(MA1Diff, MA2Diff), 
			IIf(AboveCount == 4, MA3Diff, 
			IIf(AboveCount == 5, Min(MA1Diff, MA3Diff), 
			IIf(AboveCount == 6, Min(MA2Diff, MA3Diff), 
			IIf(AboveCount == 7, Min(MA1Diff, Min(MA2Diff, MA3Diff)), 
			IIf(AboveCount == 8, MA4Diff, 
			IIf(AboveCount == 9, Min(MA1Diff, MA4Diff), 
			IIf(AboveCount == 10, Min(MA2Diff, MA4Diff), 
			IIf(AboveCount == 11, Min(MA1Diff, Min(MA2Diff, MA4Diff)), 
			IIf(AboveCount == 12, Min(MA3Diff, MA4Diff), 
			IIf(AboveCount == 13, Min(MA1Diff, Min(MA3Diff, MA4Diff)), 
			IIf(AboveCount == 14, Min(MA2Diff, Min(MA3Diff, MA4Diff)), 
			IIf(AboveCount == 15, Min(MA1Diff, Min(MA2Diff, Min(MA3Diff, MA4Diff))), 
			Null))))))))))))))); 


ClosestAbove = 	IIf(MA1Diff == LowestDiffAbove, 0, 					
				IIf(MA2Diff == LowestDiffAbove, 1, 					
				IIf(MA3Diff == LowestDiffAbove, 2, 					
				IIf(MA4Diff == LowestDiffAbove, 3, Null))));				
				
Filter = 1;
AddColumn(C, "Close");
AddColumn(MA1, "MA1");				
AddColumn(MA2, "MA2");				
AddColumn(MA3, "MA3");				
AddColumn(MA4, "MA4");	
AddColumn(LowestDiffAbove, "LowestDiffAbove");
AddColumn(MA1Diff, "MA1Diff");				
AddColumn(MA2Diff, "MA2Diff");				
AddColumn(MA3Diff, "MA3Diff");				
AddColumn(MA4Diff, "MA4Diff");	
AddColumn(AboveCount, "AboveCount", 1);			
AddMultiTextColumn(ClosestAbove, "MA1\nMA2\nMA3\nMA4", "Closest Above");

image

3 Likes

Why not use

I1 = ..your indicator
I2 = ..your indicator
I3 = ..your indicator
I4 = ..your indicator

Nearest = 1e9;
for( n = 1; n  <= 4; n++ ) 
{
  i = VarGet( "i"+ n);
  Diff = i - C;
  Above = IIF( Diff > 0, Diff, 1e9 );
  Nearest = Min( Nearest, Above );
}

The code remains the same regardless of number of indicators you want to use and does not require any nesting. To calculate nearest below you can replace Above with

Below = IIF( Diff < 0, -Diff, 1e9 );
12 Likes

Brilliant @Tomasz, and so obvious when you see it that way. Thanks :clap:

1 Like

@Tomasz and @HelixTrader always help teach me something with their posts! But with @Tomasz 's simple and elegant code the output may not be quite what the OP was looking for as he finds the "Diff" or the difference between the closest indicator value and the price (Close in our examples). But it does not tell us the actual indicator value (what I was outputting) or the name of the winning indicator , which Alan's code outputs.

image

If I may be so presumptuous as to make a slight modification to your code Tomasz, in search of both the indicator value and it's identification, this seems to work.

// plug in the indicator you choose, these are just examples
I1 = StDev( C, 20 ) + Close;
I2 = 2 * StDev( C, 20 ) + Close;
I3 = -StDev( C, 20 ) + Close;
I4 = -2 * StDev( C, 20 ) + Close;

Nearest = 1e9;

for( n = 1; n  <= 4; n++ )
{
    i = VarGet( "i" + n );
    Diff = i - C;
    Above = IIF( Diff > 0, i, 1e9 );
    Nearest = Min( Nearest, Above );
}

ClosestAbove = 	IIf( Nearest == I1, 0,
                     IIf( Nearest == I2, 1,
                          IIf( Nearest == I3, 2,
                               IIf( Nearest == I4, 3, Null ) ) ) );
Filter = 1;
AddColumn( Close, "Close" );
AddColumn( Nearest, "Nearest Value" );
AddColumn( I1, "Indicator 1" );
AddColumn( I2, "Indicator 2" );
AddColumn( I3, "Indicator 3" );
AddColumn( I4, "Indicator 4" );
AddMultiTextColumn( ClosestAbove, "Indicator 1\nIndicator 2\nIndicator 3\nIndicator 4", "Closest Above" );

Outputs this type of result,
image

This assumes that at least one indicator will be above the price, if not then the output may not look as desired, here I use Alan's four moving averages as the indicators,

image

2 Likes

Tomasz is simply brilliant.
He made it look so simple.
Hats off.

Gloria Filamino.

1 Like

Tomasz code is a poem!. This is how i use it in my code

I1 = ..your indicator;
I2 = ..your indicator;
I3 = ..your indicator;
I4 = ..your indicator;

NearestAboveDiff = 1e9;
NearestBelowDiff= 1e9;
for( n = 1; n  <= 4; n++ ) 
{
  i = VarGet( "I"+ n);
  Diff = i - C;
  Above = IIF( Diff > 0, Diff, 1e9);
  Below = IIF( Diff < 0, -Diff, 1e9);
  NearestAboveDiff = Min( NearestAboveDiff, Above );
  NearestBelowDiff = Min( NearestBelowDiff, Below );  
}
NearestAboveInd=NearestAboveDiff+C;
NearestBelowInd=C-NearestBelowDiff;
_TRACE("NearestAboveInd="+NearestAboveInd+" NearestBelowInd="+NearestBelowInd);

VarGet() comes in handy here. Thanks to teyano, portfoliobuilder, HelixTrader

4 Likes

@Tomasz Even though the details of the problem were not of specific interest to me, I learn so much from looking at the posts of possible solutions and how the thread evolves to the correct solution. Thanks to all who contribute to make this such a great forum!

3 Likes

sir,
i tried this with the set of horizontal lines but not getting the accurate result
i need to find the nearest abv 1 and 2
and nearest blw 1 and 2 considering daily open price
my code below`

``
Price_Movement1 = Param( "1st Price Movement %",0.5, 0.1, 1000, 0.1 ) / 100;
Intrevald_Open = TimeFrameGetPrice( "O", Intervald_Select, 0 );
lv = lowestSince(New_Intrevald, l);
i0 = lv;
i1 = lv + (lvPrice_Movement1);
i2 = lv + (lv
(Price_Movement12));
i3 = lv + (lv
(Price_Movement13));
i4 = lv + (lv
(Price_Movement14));
i5 =lv + (lv
(Price_Movement15));
i6 = lv + (lv
(Price_Movement16));
i7 = lv + (lv
(Price_Movement17));
i8 =lv + (lv
(Price_Movement18));
i9 = lv + (lv
(Price_Movement19));
i10 = lv + (lv
(Price_Movement110));
i11 = lv + (lv
(Price_Movement1*11));`

plz guide me sir
thank you