When MACD couples with BB in Elasticsearch, …

Moving Average Convergence Divergence (MACD) is a trend and momentum-based indicator, while Bollinger Bands (BB) is a volatility-based indicator. When MACD couples with BB, some professionals call it MACD BB, and others call it BB MACD. In this article, the name MACD BB is used. The combination of two technical analysis indicators inherits the capabilities of the two indicators and provides insight into market trends. From my intensive web search, there is nowhere to say who invented this indicator. If anyone knows, please share the source. However, many trading platforms and forums provide this indicator as an advanced feature. Readers are advised to read my two previous articles to quickly have a basic understanding of these two indicators and their implementation using Elasticsearch.
According to the equation described in the article “Construct MACD Histogram with Elasticsearch”, MACD involves a short-term and a long-term exponential weighting moving average (EWMA). Common practices for these two terms are 12 and 26.

In the article “Calculate Bollinger Band Width Through Elasticsearch”, BB is based on the simple moving average (SMA) and standard deviation (SD) of the daily prices to construct the upper band (BBU) and the lower band (BBL). The midline of BB is the SMA. From the terminology of MACD BB, it uses MACD instead of price. The computation of BBL and BBU is explained as follows, where the sliding window (window) is 20 or 26, and the standard deviation (n) is 1 or 2 in common practice.

Basically, MACD, BBU and BBL will be plotted on a chart and users can observe the intersections of MACD and the two bands. When the MACD breaks through BBU, it shows a strong uptrend signal. Similarly, when the MACD breaks through BBL, it shows a strong downward signal. It is much easier to use diagram to describe the meaning. In this article, we try to apply MACD and BB to commission-free exchange-traded funds (ETFs) and focus on Elasticsearch as analysis tool. The following example randomly selects “Fidelity International Multifactor ETF”. Its ticker symbol is FDEV. The data is selected from the time range between Feb. 1, 2021, and May 31, 2021 provided by IEX, Investors Exchange. The most used parameters of MACD are 12 for short-term and 26 for long-term. According to many internet articles, when calculating BB, the period of SMA is 10 and the standard deviation of BB is 1.
In the figure below, MACD and its BBL, BBU and SMA are plotted. If the MACD value is above the BBU and is an increment while comparing to the value in the timestamp ahead, it is an aqua-blue dot. If the MACD value is above the BBU and is a decrement, it is a blue dot. If the MACD value is below the BBL and is a decrement, it is a red dot. If the MACD value is below the BBL and is an increment, it is an orange dot. For other cases, it is a gray dot. Readers can easily observe that the red/orange lines are below BBL and the blue/aqua-blue lines are above BBU. In addition, when the MACD value rises from below zero and crosses zero (consider a bullish signal generated from MACD), there is a corresponding aqua-blue dot followed closely in most cases. In the same way, when the MACD value drops from above zero and crosses zero (consider a bearish signal generated by MACD), a corresponding red dot will follow. The slope of the line indicates the momentum of the trend.

However, when we try to explain the point when the MACD value breaks through from BBU or BBL in combination with typical values, it does not seem to match the upward or downward trend of the price, as shown in the figure below. Potential signs of increased volatility and possible future trading opportunities are not easy to capture and sometimes the direction is reversed.

Although most trading platforms provide MACD BB indicator and give the same comment, “It is not suitable for novice traders”, its Elasticsearch implementation shows seamless integration and easy to understand. Suppose there is an Elasticsearch index populated with data, and its data mapping used is the same as described in the previous paper. The following steps demonstrate the code of the REST API request body.

Collect all relevant documents through the search operation

Use a “bool” query with a “must” clause to collect documents with the symbol FDEV and the date between Feb. 1, 2021 and May 31, 2021. Due to the computation of 26-trading days moving average, additional data is adjusted for 1.5 month (from Dec. 15, 2021, to Feb. 1, 2021)

{
"query": {
"bool": {
"must": [
{"range": {"date": {"gte": "2020-12-15", "lte": "2021-05-31"}}},
{"term": {"symbol": "FDEV"}}
]
}
},

Calculate the daily typical value of the fund

Use a “date_histogram” aggregation, named MACD, 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, which is equal to the average price of the highest, the lowest, and the closing price.

 "aggs": {
"MACD_BB": {
"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]"
}
},

Extract the date of the bucket

Because of the additional data, subsequent operations need to filter out the out-of-range portion later. A “min” aggregation named “DateStr” is to get 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"}
},

Select the buckets with more than 1 document

In order to filter out the empty buckets (non-trading days), a “bucket_selector” aggregation, named STP, is used to select buckets with its document count greater than 0.

 "STP": {
"bucket_selector": {
"buckets_path": {"count":"_count"},
"script": "params.count > 0"
}
},

Calculate daily 12-trading day and 26-trading day EWMA of the typical value

Use a “moving_fn” aggregation, named EWMA12, with the parameter window as 12 and the parameter “buckets_path” as TP.value to calculate the 12-trading day EWMA of the typical value. EWMA is calculated by using the function MovingFunctions.ewma with the parameter alpha as 2/(window+1). The EWMA26 aggregation can be done in the same way.

 "EWMA12": {
"moving_fn": {"script": "MovingFunctions.ewma(values, 2/(12+1))", "window": 12, "buckets_path": "TP.value"}
},
"EWMA26": {
"moving_fn" : {"script": "MovingFunctions.ewma(values, 2/(26+1))", "window": 26, "buckets_path": "TP.value"}
},

Calculate MACD

Use a “bucket_script” aggregation, named macd, with the parameter “buckets_path” to specify the results from EWMA12 and EWMA26. Then the MACD indicator is calculated according to the equation in the script.

 "MACD": {
"bucket_script": {
"buckets_path": {
"EWMA12": "EWMA12",
"EWMA26": "EWMA26"
},
"script": "params.EWMA12 - params.EWMA26"
}
},

Calculate the daily 10-day simple moving average of the typical value

Use a “moving_fn” aggregation, named SMA10, with the parameter window as 10 and the parameter “buckets_path” as MACD to calculate the 10-day SMA of MACD value. SMA is calculated by using the unweighted average function (MovingFunctions.unweightedAvg).

 "SMA10": {
"moving_fn" :{"script": "MovingFunctions.unweightedAvg(values)", "window":10, "buckets_path":"MACD"}
},

Calculate the daily 10-day standard deviation of the typical value

Use a “moving_fn” aggregation, named SD10, with the parameter window as 10 and the parameter “buckets_path” as MACD to calculate the 10-day MACD standard deviation. SD is calculated by using the standard deviation function (MovingFunctions.stdDev).

 "SD10": {
"moving_fn": {"script":"MovingFunctions.stdDev(values, MovingFunctions.unweightedAvg(values))", "window":10, "buckets_path":"MACD"}
},

Calculate MACD BB

Use two “bucket_script” aggregations, named BBU10 and BBL10, with the parameter “buckets_path” to specify the results from SMA10 aggregation and SD10 aggregation. Then, the BBL10 and BBU10 are calculated from SMA10 with plus or minus the value of SD10.

 "BBU10": {
"bucket_script": {
"buckets_path": {
"SMA": "SMA10",
"SD": "SD10"
},
"script": "params.SMA + params.SD"
}
},
"BBL10": {
"bucket_script": {
"buckets_path": {
"SMA": "SMA10",
"SD": "SD10"
},
"script": "params.SMA - params.SD"
}
},

Identify the type of the MACD value

a) Use a “derivative” aggregation named, MACD_Diff, with the parameter “buckets_path” to specify the value of MACD to determine whether it is an increment or decrement from the MACD at the timestamp ahead.

 "MACD_Diff": {
"derivative": {
"buckets_path": "MACD" 
}
},

b) Use a “bucket_script” aggregation, named MACDType, with the parameter “buckets_path” to specify the results from BBL10, BBU10, macd and MACD_Diff aggregation to classify the type of the MACD value.

➤ Type 1 if MACD_Diff is a decrement and macd value < BBL
➤ Type 2 if MACD_Diff is an increment and macd value < BBL
➤ Type 3 if MACD_Diff is an increment and macd value > BBU
➤ Type 4 if MACD_Diff is a decrement and macd value > BBU
➤ Type 0 for other cases

 "MACDType": {
"bucket_script": {
"buckets_path": {
"BBL": "BBL10",
"BBU": "BBU10",
"MACD": "MACD",
"MACD_Diff": "MACD_Diff"
},
"script": "(params.MACD > params.BBU) ? (params.MACD_Diff > 0 ? 3:4) : (params.MACD < params.BBL) ? (params.MACD_Diff > 0 ? 2:1):0"
}
},

Filter out the additional documents for output

Use a “bucket_selector” aggregation, named SMACD_BB, 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 Feb. 1, 2021 (the epoch time 1612137600000 in milliseconds).

 "SMACD_BB": {
"bucket_selector": {
"buckets_path": {"DateStr":"DateStr"},
"script": "params.DateStr >= 1612137600000L"
}
}
} 
}
},
"size": 0
}

After collecting results, we can draw the figures as shown before. The dot color for Type 3 is aqua-blue, type 4 is blue, type 1 is red, type 2 is orange, and the others are gray.

Readers can further refer to the open-source project on GitHub (MACD_BB)

Remarks:

I. Thanks to IEX (Investors Exchange) providing ETF data and also GitHub providing open-source project storage.

II. This article is based on a technical thought and does not constitute any investment advice. Readers must take their own responsibilities when using it.

III. There may still have errors in the article, and I urge readers to correct me.

IV. Those readers feel interests can refer to the book authored by the writer for all basic skills of Elasticsearch. “Advanced Elasticsearch 7.0”, August 2019, Packt, ISBN: 9781789957754.

Source: https://wtwong316.medium.com/when-macd-couples-with-bb-in-elasticsearch-3cca987c0678?source=rss——-8—————–cryptocurrency