candlestick pattern recognition

Had posted in mr. excel forum -- https://www.mrexcel.com/board/…ion.1159249/#post-5624414

Respected,

Day Open High Low Close

D1 O1 H1 L1 C1

D2 O2 H2 L2 C2

Wants to get the logic- for excel formula for decoding OpenHighLowClose for candlestick patterns--

The details are --(1) (a)BUllish or (b)bearish

(2)(a)Inside bar (a)Above Bar (b) Belowbar(c)Normal Bar(d)Above normal Bar(body is above >50 of previous days body)(e)Below Normal Bar

(3)(a)Upper wick/shadow is longer than lower wick(b)Lower Wick/shadow is longer than Upperwick(c)Same size but same as body , so no spinning top like thing(d)Upperwick, Lowerwick and body all are of Same Size(~98%)(e)Spinning top pattern.

the structure I need is like--(1) if c(lose>O(open), then bullish else bearish , then go to second stage(2)If c2(today)>c1(yesterday) & o2<o1, inside bar, if c2<c1& o2>o1, its outside bar, if o2<o1& c2>50% of previous days c1-o1, then up Inside bar, ..... like that there are about 30+combination WHILE excel support only 7 levels of IF.--- so importnt to exactly know the logic as what to evaluate first-second-third.......

I am putting emphasis on logic as it has happened many times in other functions---- that a particular condition does not trigger as the preceding conditions negates that condition,(logical error), so I want to first clear the exact thought process----means steps of it in going through various conditions ---before constructing formula for that.

PLEASE HELP

-------I want logic as has to make the formulas to suit at various place.

Zohar Batterywala

Respected Carlm,

My main aim is to find the movement. for the I am able to find the trend of other things(like volume, Average price, etc. ) but here as the combinations are more, has to take care so that each condition gets evaluated AND important is that nothing remains unevaluated.

In condition there are

2 at first stage

4 at second stage

4 at last stage.

,so ~32 combinations do occur but it might happen that

from 1 stage , checking might goes in one side, &

so condition 3,4 of 2nd stag

& 3,4 of 3 stage might not come in checking.

though this might be applicable and make impact on analysis

and so rather than needing to find various candlestick/chart patterns, I have to find just how the last candle performed in relation to previous candle.

The problem with the issue is that (as I have mentioned earlier also) there are about 30+combination and a particular condition might not trigger as the preceding conditions negates that condition,(logical error) . This will now be evident in that calculation but in next calculation by which time that analysis is of no use---so has to understand the logic first as to which way I should make the code so that all the conditions are properly evaluated.

Respected,

Has tried to create formula but had many exceptions which I was not able to coordinate-so asking your help PLEASE PLEASE

-----------------------------------------------------------

Last exception is kept to just check the execution.it will be removed in final

---

=IF(AND(OR(B83<B80,E83<E80),ABS(E83-B83)<ABS(E80-B80)),IF(E83>B83,

IF(B83>=AVERAGE(B83,E83),IF((C83-B83)>=1.5*(E83-D83),"11IBuTW","12IBuLW"), (to see if the lower/upper is prominent)

"13IBuL"), whether the body is in upper/lower half of previous candle

"14IBBr"), whethere the body is bullish(close>open) or bearish(close<open)

"15NIB║")

&"║2ndcase"&

IF(AND(OR(B83>B80,E83>E80),ABS(E83-B83)>ABS(E80-B80)),IF(E83>B83,IF(E83>=AVERAGE(B83+E83),IF((C83-B83)>=1.5*(E83-D83),"21OBuTW","22OBuLW"),"23OBuL"),"24OBBr"),"25NOB")

&"║3rd case"&

IF(OR(AND(E83>E80,OR(B83>E80,B83>B80))),IF(E83>B83,IF(E83>=AVERAGE(B83+E83),IF((C83-B83)>=1.5*(E83-D83),"31BuUTW","32NBuUW"),"33NBuU"),"34NBuUBr"),"35NNBU")

&"║4th case"&

IF(OR(AND(E83>E80,OR(B83>E80,B83>B80))),IF(E83>B83,IF(E83>=AVERAGE(B83+E83),IF((C83-B83)>=1.5*(E83-D83),"41NBuLTW","42NBuLW"),"43NBuL"),"44NBrL"),"45NNBL")

---

Last exception(all 5 in 2nd place)is kept to just check the execution.it will be removed in final

Only one of 20 conditions fulfill and so only one of that should be displayed

d1 o(pen)1 h(igh)1 l(ow)1 c(lose)1

d2 o2 h2 l2 c2

--

case i "inside bar"

if(and(abs(c2-o2)<abs(c1-o1),not(c2>c1),not(o2<o1)

if(c2<o2),"bullish inside bar" ELSE "bearish inside bar"

if((c2-c1)>(o2-o1),(c2-c1)>(o2-o1)"lower insider bullish bar"

if((c2-c1)<(o2-o1),(c2-c1)<(o2-o1)"upper insider bullish bar"

if(h2-c2)>2x(c2-l2)"long upper wick"

if(h2-c2)<2x(c2-l2)"long lower wick"

case 2 "outsider bar"

if(and(abs(c2-o2)>abs(c1-o1),not(c2<c1),not(o2>o1)

if(c2>c1),"bullish outside bar" ELSE "bearish outside bar"

if(and((c2-c1)>(o2-o1)),c2>c1,o2>o1),"upper outside bullish bar"

if((c2-c1)<(o2-o1),c2<c1,o2<o1)"lower insider bullish bar"

if(h2-c2)>2x(c2-l2)"long upper wick"

if(h2-c2)<2x(c2-l2)"long lower wick"

case 3 "upper normal bar"

if(and(c2>c1,o2>o1,not(o2<o1))

if(c2<o2),"upper normal bullish bar",ELSE "upper normal bearish bar"

if(h2-c2)>2x(c2-l2)"long upper wick"

if(h2-c2)<2x(c2-l2)"long lower wick"

case 4 "lower normal bar"

if(and(c2<c1,o2<o1,not(o2>o1))

if(c2<o2),"lower normal bullish bar" ELSE "lower normal bearish bar"

if(h2-c2)>2x(c2-l2)"long upper wick"

if(h2-c2)<2x(c2-l2)"long lower wick"

Zohar Batterywala02-11-2020-TO-22-01-2021NTPCEQN.xlsx

Respected ,

Made the formula more clear.

formula in B76,B79,B82,B85 are main formulas

The formulas in line above that

(means B75.c75,d75.e75 And so on is just done to ease calculation process at this time .it is not needed in final version)02-11-2020-TO-22-01-2021NTPCEQN.xlsx

Zohar Batterywala02-11-2020-TO-22-01-2021NTPCEQN.xlsx