[MACD Analysis part 1]: The simplest way to calculate MACD in Google Sheets

There are many teachings on MACD calculation on the Internet. And this article describes how to use ArrayFormula to calculate the MACD. The following uses the Hang Seng Index as an example,

Equation 1:

Use GOOGLEFINANCE to import historical data, and use QUERY to organize, enter the equation in A1:

=Sort(GOOGLEFINANCE("INDEXHANGSENG:HSI","Close",today()-200,today(),"daily"),1,False)

note:

1. Since recent information is more important, please make good use of Sort and put new data on top.

2. The start date must be set earlier for the EMA start calculation.

Equation 2 and 3:

To calculate EMA, the formula is:

Among them, Smoothing is set to 2, and to calculate EMA12, you only need to enter the equation in C2:

=ArrayFormula(if(A2:A="","",2/(12+1)*B2:B+(1-2/(12+1))*C3:C))

To calculate EMA26, simply enter the equation in D2:

=ArrayFormula(if(A2:A="","",2/(26+1)*B2:B+(1-2/(26+1))*C3:C))

note:

1. The core equation is: 2/(26+1)*B2:B+(1-2/(26+1))*C3, if you use this equation, you need to pull down the equation.

2. Iterative calculation must be turned on.

Equation 4:

Calculate DIF (EMA12-EMA26), enter the following equation in E2:

=ArrayFormula(if(A2:A="","",C2:C-D2:D))

Equation 5:

Calculating DEA is the same as calculating EMA, enter the following equation in F2:

=ArrayFormula(if(A2:A="","",2/(9+1)*E2:E+(1-2/(9+1))*F3:F))

Equation 6:

To calculate MACD, enter the following equation in G2:

=ArrayFormula(if(A2:A="","",2*(E2:E-F2:F)))

At this point, the calculation part is basically completed, and you can make a chart comparable to tradingview by beautifying the chart.

The following is an implementation, welcome to visit and advise:

https://docs.google.com/spreadsheets/d/15UmThTOKkmn5u0iu2Bg07AQxBB39FH6e7zenhTQxWn0/edit#