Construct MACD Histogram with Elasticsearch

Moving Average Convergence Divergence (MACD) is a trading indicator used for technical analysis of stock invented by Gerald Appel in 1979. MACD involves two moving average values, a short period and a long period. For common practice, the short period is 12 and the long period is 26, and the moving average function uses exponential weighting (EWMA). Basically, MACD is the distance between the short-period and long-period exponentially weighted moving average of the daily stock price. If the distance tends to decrease, it means convergence, and vice versa, to divergence. When MACD is zero, the trend is in a transitional state between convergence and divergence. The calculation equation is as follows:

If MACD is positive and away from zero, the upward momentum of stock prices is increasing. If it is negative and away from zero, the downward momentum is increasing. The table below describes the meaning of the combination of MACD value and trend.

In this article, we try to apply MACD for commission-free exchange-traded funds (ETF) and focus on Elasticsearch as a tool for analysis. The following example randomly picks “Fidelity International Multifactor ETF”. Its ticker symbol is FDEV. The data is selected from the time range between 2021–01–05 and 2021–05–31 provided by IEX, Investors Exchange.

When MACD value rises from below zero and crosses zero, the market is considered bullish. On the other hand, when the MACD value drops from above zero and crosses zero, the market is considered bearish. In the above chart, we can see that the intersection of MACD and the Y-axis (y=0) lags when the price trend changes. Therefore, traders often criticize this defect. In 1986, Thomas Aspray introduced MACD Histogram/Momentum to solve this problem, and it is widely used now. The MACD histogram can provide early signals when comparing to MACD. However, it was later discovered that some of the so-called early signals may be invalid and need to be confirmed by other indicators. Basically, MACD Histogram is the distance between MACD and its signal line. The signal line is the EWMA of MACD with a period of 9. The formula is as follows:

When MACD_HISTOGRAM is zero (two lines cross), it means that the trend is about to reverse. The figure below shows the daily MACD and the corresponding signal line for the symbol FDEV from 2021–01–15 and 2021–05–31. Observing the timing when MACD and Y-axis (y=0) intersects, you will find a corresponding intersection ahead between MACD and the signal line.

Since the line chart of MACD and its signal line are not easy to observe, a bar chart can be used to depict MACD_HISTOGRAM to intuitively reflect the market trend. The figure below shows the daily MACD_HISTOGRAM for the symbol FDEV from 2021–01–15 to 2021–05–31. The bar is a positive value when MACD is higher than the signal line and vice versa. The height of the bar is the difference between the MACD and the signal line. In the figure, bars in aqua blue color represents positive and showing an upward trend. Bars in blue color represent positive but show a downward trend. Bars in orange color represent negative but show a growth. Finally, bars in red color represents negative and show negative growth.

Suppose there is an Elasticsearch index populated with data, and its data mapping used is the same as described in the previous paper (Calculate Bollinger Band Width Through Elasticsearch). The following steps explain how to use Elasticsearch to construct the MACD histogram and demonstrate the REST API request body code.

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 2021–01–15 and 2021–5–31. Due to the computation of 26-trading days moving average, additional data is adjusted for 1.5 months (from Dec-1-2021 to Jan-14-2021).

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

Calculate the daily typical value of the fund

Use a “date_histogram” aggregation, named MACD_Histogram, 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_Histogram": {
            "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 the Elasticsearch server, the date field 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

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.

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

Calculate MACD Signal

Use a “moving_fn” aggregation, named Signal, with the parameter window as 9 and the parameter “buckets_path” to specify the results from MACD to calculate the 9-trading day EWMA of the MACD. EWMA is calculated by using the function MovingFunctions.ewma with the parameter alpha as 2/(9+1).

        "Signal": {
            "moving_fn": {"script": "MovingFunctions.ewma(values, 2/(9+1))", "window": 9, "buckets_path": "MACD"}
        },

Calculate MACD Histogram

Use a “bucket_script” aggregation, named MACD_Histogram, with the parameter “buckets_path” to specify MACD and Signal aggregation results. Then, the MACD Histogram indicator is calculated according to the equation.

        "MACD_Histogram": {
            "bucket_script": {
                "buckets_path": {
                    "MACD": "MACD",
                    "Signal": "Signal"
                },
                "script": "params.MACD - params.Signal"
            }
        },

Identify the type of the MACD Histogram value

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

        "MACD_HistogramDiff": {
            "derivative": {
                "buckets_path": "MACD_Histogram" 
             }
        },

b) Use a “bucket_script” aggregation, named MACD_histogramType, with the parameter “buckets_path” to specify the results from MACD_Histogram and MACD_HistogramDiff aggregation to classify the type of the MACD_Histogram value.

➤ Type 1 if MACD_HistogramDiff is a decrement and MACD_Histogram < 0
➤ Type 2 if MACD_HistogramDiff is an increment and MACD_Histogram < 0
➤ Type 3 if MACD_HistogramDiff is an increment and MACD_Histogram > 0
➤ Type 4 if MACD_HistogramDiff is a decrement and MACD_Histogram > 0
➤ Type 0 for other cases

        "MACD_HistogramType": {
            "bucket_script": {
                "buckets_path": {
                    "MACD_Histogram": "MACD_Histogram",
                    "MACD_HistogramDiff": "MACD_HistogramDiff"
                },
                "script": "(params.MACD_Histogram > 0) ? (params.MACD_HistogramDiff > 0 ? 3 : 4) : ((params.MACD_Histogram < 0) ? (params.MACD_HistogramDiff > 0 ? 2 : 1): 0)"
            }
        },

Filter out the additional documents for output

Use a “bucket_selector” aggregation, named SMACD_Histogram, 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 2021–01–15 (the epoch time 1610668800000 is specified in milliseconds).

        "MACD_HistogramType": {
            "bucket_script": {
                "buckets_path": {
                    "MACD_Histogram": "MACD_Histogram",
                    "MACD_HistogramDiff": "MACD_HistogramDiff"
                },
                "script": "(params.MACD_Histogram > 0) ? (params.MACD_HistogramDiff > 0 ? 3 : 4) : ((params.MACD_Histogram < 0) ? (params.MACD_HistogramDiff > 0 ? 2 : 1): 0)"
           }
       },

After collecting results, we can draw the figures as shown before.

The Elasticsearch example used in this article shows seamless integration and easy to understand. Readers can further refer to the open-source project on GitHub (Construct_MACD_Histogram_With_Elasticsearch)

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 who 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.