Posts by zohar

  • function to find out format

    Respected,

    Though value() formula and isnumber() shows it was correct. The record was not recognised.

    Is there any formula to find format of a cell(like dd-mmm-yy hh:mm Example: 8-dec-2021 11:20)

    Zohar Batterywala

  • candlestick pattern recognition

    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

  • candlestick pattern recognition

    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.

  • 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

  • range

    sir, the total excel file is of 1.3 GB and I have tried using conditional formatting previously but it has slowed even the normal pageup/down thing tremendously. I am looking for logic of this , then I have to convert it to workable formulas in 14 files(each having 8 instances)

    HOPE

    Zohar Batterywala

  • range

    Respected Sir ,

    PRACTICAL: The issue is that when the levels in the upper reservoir is too much it is released in the lower reservoir , now normallly it is happening that this release is sometimes more than the capacity the lower reservoir can hold(and the structure is endangered).


    The solution I am trying: to monitor level of the reservoir so that difference(range between the opening and closing) is spotted.

    when the extra level is released from upper reservoir, a step-by-step movement has to be done so that immense flow does not come to lower reservoir which it can handle. So am trying to monitor (daily levels are updated every 4 hours , so 6 in a day). And similar step-by step can be released further.

    And the SECOND case is that if the increaseing levels is spotted in above feeder, lower feeder levels decreased pre-emptively to make way for the incoming future flow.


    OB-Overflow build up is spotted only after 3rd reading as it is normal that levels increase or decrease in normal flow. but 3rd consequence of it do trigger the situation. OB#, OB$, OB5, OB6 do mention that and when the OVERFLOW BUILDUP is released to lower reservoirs, it is flagger by OR

    ORA is flagging of abnormal levels seen(in lower reservoirs) if only one or two reading is too much fluctuating (due to OR from upper reservoir)


    Zohar

  • range

    Respected,

    I am trying to formulate a way so that increasing/decreasing level in a reservoir can be known before hand so controlled flow can be assures.

    Zohar Batterywalarange.xlsx

  • alert levels

    Respected Sir Glenn,

    Yes sir it was mistake by me

    Originally I had included the formula that was in D in the main formula in E

    The issue is solved at this time.

    Will request your help if I will need it.

    The issue with me is cant find a correct answer if has done a mistake once as that type of thought do continue when reviewing the problem. In that case others perspective make a big difference for me.

    THANK YOU SIR

    Zohar Batterywala

  • alert levels

    Respected Sir,

    I had tried to use conditional formatting before but the plain file without any formatting is itself of 1.1 GB and takes 15-20 minutes for opening and 7-8 minutes to save and (conditional) formatting makes it more heavy.

    I am using This file just to calculate data (as 90% of formulas needs past data of atleast 15 days).

    Then this data is moved to an value only file (which is just becoming 200 MB).

    Zohar Batterywala

  • alert levels

    Respected Sir Roy,

    This are the alerts

    Range is just for indication -it not to be included in formula

    High(A) Low(B) Last(C) Range alert/Mention(In E column)

    (1)8404 8390.15 8390.15 16.78 WLN▼ Last is vry low near low.Her▲also comes in range but last is low--needed to alert that level is too much low

    Row 3 in file

    (2)8410.35 8395.5 8410.35 16.82 WLN▲ Last is very high near high Here ▼ also comes in range but last is high--needed to alert that level is too much high

    Row 9 in file

    When Last reading is same as high or low Alert WLN▼(LOW) or WLN▲(HIGH) OR in 0.005%(ad not mentioned in file but I find this recently--Sorry for that)

    WLN=Warning Last Near

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

    Normal Case

    High Low Last Range Mention(In E column)

    (3)8415 8385 8398.35 16.8 LN▼ here more near is low

    Row 5 in file

    (4)8415.35 8384.5 8400.35 16.8 LN▲ here more near is HIGH

    Row 7 in file

    (4)8441 8373.15 8400.35 16.8 OR none is in range-OUT OF RANGE

    Row 11 in file


    Originally in the sheet which I had to keep this formula here DATA is sergegared but will manage adjusting that in formula. This is the functionality scenario.

    THANK YOU

    Zohar Batterywala