How to treat empyt cell value in AFL

AFL Programming
#1

Hi,

I am using VWAP related AFL. When I plot the lines, I find that due to an empty value date, the line has become totally skewed.
Is there any way through coding that I can ignore these empty value dates?

My code is:

_SECTION_BEGIN("VWAP");

Bars_so_far_today = 1 + BarsSince( Day() != Ref(Day(), -1));
StartBar = ValueWhen(TimeNum() == 091500, BarIndex());
TodayVolume = Sum(V,Bars_so_far_today);
IIf (BarIndex() >= StartBar, VWAP = Sum (C * V, Bars_so_far_today ) / TodayVolume,0);
Plot (VWAP,"VWAP",colorOrange, styleThick);
Plot(EMA(vwap,50), "MVWAP", colorblue, styleThick);
_SECTION_END();

image

#2

It looks as though your zero result is being cause by the incorrect use of the IIF statement. Fixing that will likely solve it. But one way to get around zero values in an indicator is to use the previous value when zero (or Null).

_SECTION_BEGIN("VWAP");
Bars_so_far_today = 1 + BarsSince( Day() != Ref(Day(), -1));
StartBar = ValueWhen(TimeNum() == 091500, BarIndex());
TodayVolume = Sum(V,Bars_so_far_today);

VWAP = Sum(C * V, Bars_so_far_today ) / TodayVolume; // Redundant IIF() removed

VWAP = IIf(Nz(VWAP) == 0, Ref(VWAP, -1), VWAP); // If current value is "missing" use previous value

Plot (VWAP,"VWAP",colorOrange, styleThick);
Plot(EMA(vwap,50), "MVWAP", colorblue, styleThick);
_SECTION_END();
3 Likes
#3

Worked like a charm. Thanks a lot for your guidance.

1 Like
#4

Subsequent to this, I am facing a strange problem with VWAP and MVWAP plotting.

I find that in 5 minute chart, some consecutive VWAP values are zero. Based on McAllan’s code, I tried to code in such a way that if the previous values are zero, code will omit the zero value and take up the last non-zero value. This will take care of upto 4 zero values

But in some cases, I am finding that VWAP is getting picked up as zero but in other cases, its OK.

Also once the MVWAP value is getting zero, it is staying as zero value throughput. Not able to avoid this by coding. If someone help coding this properly, that will be of great help. I am not able to figure out what can be the correct expression to take care off this type of scenarios.

Code:
SetChartOptions(0,chartShowArrows|chartShowDates);
_N(Title = StrFormat("{{NAME}} - {{INTERVAL}} {{DATE}} Open %g, Hi %g, Lo %g, Close %g (%.1f%%) {{VALUES}}", O, H, L, C, SelectedValue( ROC( C, 1 ) ) ));
Plot( C, "Close", ParamColor("Color", colorDefault ), styleNoTitle | ParamStyle("Style") | GetPriceStyle() );

_SECTION_BEGIN("Volume At Price");
/*
f=14;
VWAP=Sum(V*C,f)/Sum(V,f);

*/
Bars_so_far_today = 1 + BarsSince( Day() != Ref(Day(), -1));
StartBar = ValueWhen(TimeNum() == 091500, BarIndex());
TodayVolume = Sum(V,Bars_so_far_today);

VWAP = Sum(C * V, Bars_so_far_today ) / TodayVolume; // Redundant IIF() removed

VWAP = IIf(Nz(VWAP) == 0, IIf(Nz(Ref(VWAP, -1))==0, IIf(Nz(Ref(VWAP, -2))==0, IIf(Nz(Ref(VWAP,-3))==0,Ref(VWAP,-4),Ref(VWAP,-3)),Ref(VWAP,-2)),Ref(VWAP,-1)), VWAP);

Plot (VWAP,"VWAP",colorOrange, styleThick);
Plot(EMA(vwap,50), "MVWAP", colorblue, styleThick);
_SECTION_END();

image

#5

If you have multiple bars with a zero or Null value, one option is to use ValueWhen() to look back to find the last non-zero value and use that for the bar which has no value.

VWAP = IIf(Nz(VWAP) == 0, ValueWhen(Nz(VWAP) > 0, VWAP), VWAP);

With that said, you should probably also spend some time trying to understand why you are getting Null or zero values. As if there's an underlying problem that can be fixed, that would negate the use of this workaround.

1 Like
#6

Thank you very much for solution.