Calculate Bollinger Band Width Through Elasticsearch
Bollinger Band Width (BBW) was invented by U.S. stock market analyst John Bollinger in 2010 to measure the relative distance (width) between the upper band and the lower band of the Bollinger Band (BB)。The value decreases as the width becomes narrower and increases as the width relaxes. Because BB is based on the standard deviation, the fluctuation range of BBW can reflect the volatility. BB is composed of three different lines, including a simple moving average (SMA), the upper band (BBU) and the lower band (BBL). It can be used for daily stock trading decisions and is a common technical indicator in financial market.
Before calculating BBW, BB must be obtained first. The step-by-step instructions are as follows.
BBU = SMA(tp,window) + n * SD(tp,window)
BBL = SMA(tp,window) - n * SD(tp,window)
BBW = (BBU-BBL)/SMA(tp,window)In the above formula, the variables denote as:
tp: the typical price, where tp = (high + low + close)/3.
window: the sliding window involved in the moving average (usually 20 or 26)
SMA: simple moving average
SD: standard deviation
n : a number (usually 2, which means 2 times of the standard deviation)
BBU: BB’s upper band
BBL: BB’s lower band
BBW: Bollinger Band widthIn general, the BB is often used in stock market. In this article, we try to use it for the commission-free exchange traded funds (ETF) and focus on Elasticsearch as a tool for analysis. The following example randomly picks “Fidelity Blue Chip Value ETF”. Its ticker symbol is FBCV. The data is selected from the time range between January 1, 2021 and April 30, 2021 provided by IEX, Investors Exchange. Suppose there is an Elasticsearch index, which contains seven fields, the ticker symbol of the ETF, trading date, the volume and the prices including high, low, open and close. The following data mappings are used.
"mappings": {
"dynamic": "false",
"properties": {
"symbol": { "type": "keyword" },
"date": { "type": "date", "format": "yyyy-MM-dd" },
"high": { "type": "float" },
"low": { "type": "float" },
"close": { "type": "float" },
"open": { "type": "float" },
"volume": { "type": "long" }
}
}The following illustrates the step-by-step operations, and the corresponding REST API request body is shown:
- Collect all relevant documents through the search operations
Use a “bool” query with a “must” clause to collect documents with the symbol FBCV and the trading date between February 1, 2021 and April 30, 2021. Due to the computation of the moving average, one month’s data has been added (from January 01, 2020, to January 31, 2020).
{
"query":{
"bool":{
"must": [
{"range":{"date":{"gte":"2021-01-01", "lte":"2021-04-30"}}},
{"term": {"symbol":"FBCV"}}
]
}
},2. Calculate the daily typical value of the fund
Use a “date_histogram” aggregation, named BBI, with the parameter “field” as “date” and the parameter “interval” as “1d” to extract the prices of the fund each day. Then followed by a “scripted_metric” aggregation, named TP, to calculate the typical price.
"aggs" : {
"BBI" : {
"date_histogram" : {
"field" : "date",
"interval" : "1d",
"format" : "yyyy-MM-dd"
},
"aggs": {
"TP" : {
"scripted_metric":{
"init_script": "state.totals=[]",
"map_script": "state.totals.add((doc.high.value+doc.low.value+doc.close.value)/3)",
"combine_script": "double total=0; for (t in state.totals) {total += t} return total",
"reduce_script": "return states[0]"
}
},3. Extract the date of the bucket
Because one month's data has been added and the subsequent operation need to calculate the average of the BBW, a filtering action by the field “date” is required later. The “min” aggregation, named “DateStr”, is to obtain the date of the bucket. In Elasticsearch server, the date is stored in Epoch time. The time unit is milliseconds, and the time zone is UTC.
"DateStr": {
"min": { "field": "date"}
},4. Select the buckets with more than 1 document
When a "moving_fn" aggregation is used to calculate moving average, the parameter "min_doc_count" of the "date_histogram" aggregation is restricted to 0 in newer Elasticsearch version. In order to filter out the empty buckets (non-trading days), a "bucket_selector" aggregation is used to select buckets with its document count greater than 0.
"STP": {
"bucket_selector": {
"buckets_path": {"count":"_count"},
"script": "params.count > 0"
}
},5. Calculate the daily simple moving average
Use a “moving_fn” aggregation, named SMA, with the parameter window as 20 and the parameter “buckets_path” as TP to calculate the 20-day simple moving average of the typical value. SMA is calculated by using the unweighted average function (MovingFunctions.unweightedAvg).
"SMA": {
"moving_fn" : {"script":"MovingFunctions.unweightedAvg(values)", "window":20, "buckets_path":"TP.value"}
},6. Calculate the daily standard deviation of the SMA
Use a “moving_fn” aggregation, named “SD, with the parameter window as 20 and the parameter “buckets_path” as TP to calculate the daily standard deviation of SMA. SD is calculated by using the standard deviation function (MovingFunctions.stdDev).
"SD": {
"moving_fn": {"script":"MovingFunctions.stdDev(values, MovingFunctions.unweightedAvg(values))", "window":20, "buckets_path":"TP.value"}
},7. Calculate the upper and the lower band of Bollinger Band
Use two “bucket_script” aggregations, named BBU and BBL, with the parameter “buckets_path” to specify the results from SMA aggregation and SD aggregation. Then, the upper band and lower band of the Bollinger Band are calculated from SMA with plus and minus the value of 2 standard deviations.
"BBU": {
"bucket_script": {
"buckets_path": {
"SMA": "SMA",
"SD": "SD"
},
"script": "params.SMA + 2 * params.SD"
}
},
"BBL": {
"bucket_script": {
"buckets_path": {
"SMA": "SMA",
"SD": "SD"
},
"script": "params.SMA - 2 * params.SD"
}
},8. Calculate the Bollinger Band width
Use a “bucket_script” aggregation, named BBW, with the parameter “buckets_path” to specify the resulting values of BBU aggregation, BBL aggregation, and SMA aggregation to calculate the BBW.
"BBW": {
"bucket_script": {
"buckets_path": {
"BBU": "BBU",
"BBL": "BBL",
"SMA": "SMA"
},
"script": "(params.BBU - params.BBL) / params.SMA"
}
},9. Plot the BB and the BBW
After collecting the results, the BB (BBU, BBL and SMA) can be plotted as shown as the following figure.
The BBW can be plotted as shown below.
Under certain circumstances, BBW can be used to identify trading signals, such as the Bollinger Band Squeeze to identify whether the fund has entered a consolidation state. However, this article will not involve and discuss it. In the beginning of this article, it was mentioned that BBW can be used as an indicator of volatility. Here, we try to adopt the area covered by BBW to indicate the degree of volatility for the fund during the period. Comparing the area can be substituted by comparing the average BBW. The larger the BBW of the fund, the greater the volatility during the period.
To calculate the average BBW, the documents from the extra month must be filtered out. The “bucket_selector” aggregation, named SBBW, is applied with the parameter “buckets_path” as “DateStr” to select the correct buckets specified in the “script” statement. The selection criterion is those buckets having the date on or after February 1, 2021 (the epoch time 1612137600000 is specified in milliseconds).
"SBBW": {
"bucket_selector": {
"buckets_path": {"DateStr":"DateStr"},
"script": "params.DateStr >= 1612137600000L"
}
}
}
},After selecting the appropriate buckets, a “avg_bucket” aggregation, named avg_BBW, with the parameter “buckets_path” to specify “BBI>BBW” （the resulting value of the sub-aggregation BBW from the BBI aggregation）is performed to calculate the average BBW. The resulting value is about 0.05934. The following shows the relevant code.
"avg_BBW": {
"avg_bucket": {
"buckets_path": "BBI>BBW"
}
}
}Suppose the average values of BBW for related funds during the same period are collected and those documents are indexed. Then “percentile_ranks” aggregation is applied to calculate the ranking of the value among the relevant funds. The mapping of the index can be designed as follows, which includes four fields, “symbol”, “avg_bbw”, “management”, and “start_end_date”. The following shows the relevant code.
"mappings": {
"dynamic": "false",
"properties”: {
"symbol": { "type": "keyword" },
"management": { "type": "keyword" },
"avg_bbw": { "type": "float" },
"start_end_date ": { "type ": "keyword"}
}
}The following is the REST API request body for the percentage ranking of the average BBW of FBCV and the percentile distribution of the average BBW for 28 commission-free stock ETFs managed by Fidelity Investments between February 1, 2021 and April 30, 2021.
{
"query":{
"bool":{
"must": [
{"term": {"management":"Fidelity Investments, Inc."}},
{"term": { "start_end_date" : "2021-02-01_2021-04-30" }}
]
}
},
"aggs" : {
"avg_BBW_dist" : {
"percentiles" : {
"field" : "avg_bbw"
}
},
"Rank" : {
"percentile_ranks" : {
"field":"avg_bbw", "values":[0.05934]
}
}
},
"size":0
}The percentage ranking of FBCV is about 42%. When comparing with the 28 commission-free stock ETFs managed by the same company, this fund has mild volatility within the specified period. The percentile distribution of the average BBW of 28 Fidelity Commission-free ETFs between 2021–02–01 and 2021–04–30.is as follows:
For a set of related funds, it is a very interesting trial to use the percentage of the average BBW as an indicator for the relative volatility within a time period. Regarding to the implementation used in this article for Elasticsearch, it shows seamlessly and is easy to understand. Interested readers can further refer to the open-source project on GitHub: Calculate_Bollinger_Band_Width_Through_Elasticsearch.
Remarks:
- This article is also published in my LinkedIn.
- Thanks to IEX (Investors Exchange) providing ETF data and also GitHub providing open-source project storage.
- This article is based on a technical thought and does not constitute any investment advice. Readers must take their own responsibilities when using it.
- There may still have errors in the article, and I urge readers to correct me.
- Those readers feel interests can refer to the book authored by the writer for the basic skills of Elasticsearch. “Advanced Elasticsearch 7.0”, August 2019, Packt, ISBN: 9781789957754. This book is recommended by BookAuthority as one of the 4 Best New Elasticsearch Books To Read In 2021.
