Bruce 

what will be a custom pcf for Intraday  0.618 retracement from the opening price

thanks

I really don't understand the question since you are specificing only a single value and then asking for a retracement. So I am going to talk through my guess.

Note that even though you specified intarday, the formulas themselves are daily as there is no way to determine the open, high, or low of the trading day so far in an intraday formula.

Retracements require specifying the high and low of a range and where price is relative to that range depending on if the high or low happened first.

So let's assume the open represents the high or the low of the range with the actual high or low of the trading day representing the other end of the range.

If the current price is above the open, then low of the range would be the open, the high of the range would be the high and the retrecament would be how far price has come back toward the open since the high.

(H - C) / (H - O)

If the current price is below the open, then the high of the range would be the open, the low of the range would be the low and the retracement would be how far price has come back toward the open since the low.

(C - L) / (O - L)

So, the retrecament formula would be the following if we assume price has fully retraced and is 1 when C = O.

IIF(C > O, (H - C) / (H - O), IIF(C < O, (C - L) / (O - L), 1))

So if we want this to be greater than or equal to 0.618:

IIF(C > O, (H - C) / (H - O), IIF(C < O, (C - L) / (O - L), 1)) >= .618

If you want to exclude cases where C = O, then change the 1 in the formula to 0 or 1 / 0 instead.

If this isn't what you want, we would need additional clarification as to the actual intent of the question.

Hmm Okay Bruce 

Lets take an example for what i m trying to achieve example of "ULTA " on 30 min chart so 9.30 to 10.00 am bar it made range of high  low and open close so the next bar 10.00 am to 10.30 closed just above 88.6 ratio when we draw retracement from low to high of the first bar  

and that is what i want in output value where the second bar retracement in terms of ratio was 61.8 %  or 62, 63 ,64 of the first bar and i can sort that on any intraday time frame using data of the first bar may it is 15 min bar or 30 min bar or hourly bar 

hope it helps 

thanks 

Can you give me the specific numbers you used to calculate that 88.6% ratio for ULTA for the first and second bars of the trading day?

Also, from my previous response, "Note that even though you specified intarday, the formulas themselves are daily as there is no way to determine the open, high, or low of the trading day so far in an intraday formula."

So keep in mind that if we write something like this, you would only be able to use it during a specific bar of the trading day (say while the second or third bar is still forming).

Bruce the 88.6% came from the low to high retracement of the first 30 min bar so the first bar had print of low and high and on that bar i drew retracement which comes on to second bar close at just above 88.6% and low of that second bar was below 88.6% 

So in theory what i was trying to trade is using low high range of first bar(if i m bullish) where i draw retracement from low to high of first bar and draw high to low range of the first bar (if i m bearish) regrdless of the time frame i want spike at 61.8% if the second third fourth fifth any bar  low touches 61.8 % of the first opening bar and every time through out intrady if the 61.8 % is touched i need spike  and it would be nice to get output value as fib ratio at what exact value current bar touched the opening range 

cheers

I asked because the math didn't actually return that result when I plugged in any combination of numbers which made any sense to me for the first two 30 minute bars of ULTA.

We can write the formula for individual bars during the trading day.

So during the second bar of the trading day.

L <= .618 * L1 + .382 * H1 AND .618 * L1 + .382 * H1 <= H

And during the third bar of the trading day.

L <= .618 * L2 + .382 * H2 AND .618 * L2 + .382 * H2 <= H

And during the fourth bar of the trading day.

L <= .618 * L3 + .382 * H3 AND .618 * L3 + .382 * H3 <= H

But as pointed out in my two previous responses, there isn't a way to automate this in a generalized fashion which would not involve writing a different formula for each bar of the trading day.

Thank you Bruce but can we make a slight tweak here with strick rule " if current bar price close - high or low  is NOT within 0.618 or 0.382 range of that first opening bar range then don;t show the spike "" 

is it possible ??

price close - high or low 

its just close , high , low   

I have no idea what that is supposed to mean.

Okay same example  ULTA  "  30 min price bar if i draw fib retracement regardless of high to low or low to high only on that first bar of 9.30 to 10.00 am then the remainder following bars forming at 11 am , 12 pm  2 pm if they don;t have high , low , close in between 38.2 to 61.8 ratio of that first bar then don;t show the spike at all 

so ULTA" on 30 min chart had 11.00 am and 11.30 am bar completely out of that 38.2 and 61.8 ratio opening range of first bar so i don't want the SPIKE THERE 

12.00 pm to current bar they are in range so spike should be there 

hope it helps 

thanks

During the second bar of the trading day.

(.618 * L1 + .382 * H1 <= C AND C <= .382 * L1 + .618 * H1) OR (.618 * L1 + .382 * H1 <= H AND H <= .382 * L1 + .618 * H1) OR (.618 * L1 + .382 * H1 <= L AND L <= .382 * L1 + .618 * H1)

During the third bar of the trading day.

(.618 * L2 + .382 * H2 <= C AND C <= .382 * L2 + .618 * H2) OR (.618 * L2 + .382 * H2 <= H AND H <= .382 * L2 + .618 * H2) OR (.618 * L2 + .382 * H2 <= L AND L <= .382 * L2 + .618 * H2)

During the fourth bar of the trading day.

(.618 * L3 + .382 * H3 <= C AND C <= .382 * L3 + .618 * H3) OR (.618 * L3 + .382 * H3 <= H AND H <= .382 * L3 + .618 * H3) OR (.618 * L3 + .382 * H3 <= L AND L <= .382 * L3 + .618 * H3)

Thanks Bruce thats exactly what i want just one more last question 

During the second bar of the trading day.

(.618 * L1 + .382 * H1 <= C AND C <= .382 * L1 + .618 * H1) OR (.618 * L1 + .382 * H1 <= H AND H <= .382 * L1 + .618 * H1) OR (.618 * L1 + .382 * H1 <= L AND L <= .382 * L1 + .618 * H1)

what is the formula for SHOWING SPIKES IF THE LOW HIGH OR CLOSE ARE OUTSIDE 0.382 OR 0.618 RANGE OF THE FIRST BAR

Example " TIF" on 195 min chart when drawing retracement low to high or high to low the current second bar is outside the range of 0.382 and 0.618 of first bar so that's where i need spike 

Thanks a lot again 

If you need the entire bar to be outside the range.

During the second bar of the trading day.

.382 * L1 + .618 * H1 < L OR H < .618 * L1 + .382 * H1

During the third bar of the trading day.

.382 * L2 + .618 * H2 < L OR H < .618 * L2 + .382 * H2

During the fourth bar of the trading day.

.382 * L3 + .618 * H3 < L OR H < .618 * L3 + .382 * H3

If you really need the low, high, or close to be outside of the range. This is the literal interpretation of the request, but the above seems like a better interpretion to me.

During the second bar of the trading day.

NOT(.618 * L1 + .382 * H1 <= H AND H <= .382 * L1 + .618 * H1) OR NOT(.618 * L1 + .382 * H1 <= L AND L <= .382 * L1 + .618 * H1)

During the third bar of the trading day.

NOT(.618 * L2 + .382 * H2 <= H AND H <= .382 * L2 + .618 * H2) OR NOT(.618 * L2 + .382 * H2 <= L AND L <= .382 * L2 + .618 * H2)

During the fourth bar of the trading day.

NOT(.618 * L3 + .382 * H3 <= H AND H <= .382 * L3 + .618 * H3) OR NOT(.618 * L3 + .382 * H3 <= L AND L <= .382 * L3 + .618 * H3)

During the second bar of the trading day.

NOT(.618 * L1 + .382 * H1 <= H AND H <= .382 * L1 + .618 * H1) OR NOT(.618 * L1 + .382 * H1 <= L AND L <= .382 * L1 + .618 * H1)

Bruce this formula above does not give any spikes is there something missing  ?? example " GOOG" on 195min chart  there should be a spike i don't see that happening 

pls check 

Yeah, it does give a spike. The problem is that the OR means it pretty much always gives a spike in a 195 minute time frame so you aren't seeing anywhere with the value not at 100% (where it drops down to 0%).

Okay thanks a lot Bruce I will do some backtesting and play around with all of them 

Cheers

You're welcome.