Bollinger Bands in a spreadsheet: population against sample deviation, worked on 5 closes

[Fairman]

Bollinger Bands in a spreadsheet: population against sample deviation, worked on 5 closes

Bollinger Bands use the standard deviation of the last N closes, and there are two versions: population (divide by N) and sample (divide by N minus 1). Chart indicators normally use the population one. A spreadsheet's STDEV function is the sample version, and STDEV.P is the population one.

Five closes, in pips above 1.0800: 50, 54, 52, 58, 56. The mean is 54. Deviations: -4, 0, -2, +4, +2. Squares: 16, 0, 4, 16, 4, which sum to 40.

Population: 40 / 5 = 8, so the standard deviation is 2.83 pips. Sample: 40 / 4 = 10, so 3.16 pips. At 2 standard deviations the bands sit 5.66 pips either side of the mean in the first case and 6.32 in the second, an 11.8% difference.

At the usual N of 20 the gap shrinks to the square root of 20/19, which is 1.026, so 2.6%. A band 8.0 pips from the mean becomes 8.2. Small, but it explains why a hand built sheet may not match the chart by a few percent.

[FXS HongKong Scalper]

For short periods like 20, the difference between sample and population is around 2.5%, which can matter when comparing band touches between a spreadsheet and the chart. Using STDEV.P in spreadsheets avoids confusion.

[Fairman]

In rule terms, the difference changes how often price tags the band. If returns were normal, the chance of a close outside 2 standard deviations is 4.55%. Using sample standard deviation widens the band to 2.052 in population units, and the chance falls to 4.02%, about 12% fewer touches. Real price changes have fatter tails than normal, so the exact figures will differ, but the direction holds: a wider band gives fewer signals. If you tuned a strategy on a spreadsheet using STDEV and then coded it with a population indicator, the signals you get live are a little more frequent than the ones you tested.

[Fairman]

In MT5, iBands returns a handle and the three buffers are 0 for the middle line, 1 for the upper band and 2 for the lower. Two derived values are useful. Percent b is (close - lower) / (upper - lower): with a lower band of 1.0842, an upper of 1.0858 and a close of 1.0856, that is 14 / 16 = 0.875, near the top. Bandwidth is (upper - lower) / middle, which is 16 pips on 1.0850, or 0.147%. Keeping bandwidth as a percentage makes it comparable between pairs, where a count of pips is not. Print both for a few bars and check them against the chart before building rules on them.

[Fairman]

Squeeze rules need a reference. Saying the bandwidth is below 0.10% is a number that holds for one pair and one timeframe at one time of year. A relative measure is steadier: rank the current bandwidth among the last 100 bars and call it a squeeze when it falls in the lowest 10%. In a spreadsheet that is PERCENTRANK of the current value against the previous 100. The rank adapts as volatility drifts, so a quiet August and a busy March both produce about ten squeezes per hundred bars. Whether that squeeze precedes anything useful is a separate question, and the test is to count what the next 20 bars do after each one.

[Fairman]

In Pine, ta.stdev has an argument called biased which is true by default, and that gives the population version, so ta.bb matches the usual Bollinger definition. If you write your own bands from ta.sma and ta.stdev, the default already matches. The place where mismatches creep in is when someone calculates the deviation from a different series, for example high-low midpoints, or uses a different length for the mean and the deviation. A quick check: put the built in Bollinger Bands on the chart, put your own on top, and look at the data window values on one bar. They should agree to the last digit; if not, something differs in the inputs, not the maths.