[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#

[MACD分析一] :Google Sheets 6條方程計算出任意時間範圍之MACD

網上關於MACD計算的教學並不少.而這篇主講述如何利用ArrayFormula 用到極致把 MACD 化致最簡潔的版本. 以下用恆生指數做例子

方程一:

用GOOGLEFINANCE導入歷史數據,並用QUERY 整理,在A1輸入方程:

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

注意:

1. 由於近期資訊更為重要,所以請善用Sort, 把新資料置頂.

2. 開始日期必須設早一點以用於EMA起始計算.

方程二和三:

計算EMA,公式為:

其中Smoothing 設為2, 而計算EMA12只需在C2輸入方程:

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

計算EMA26只需在D2輸入方程:

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

注意:

1.核心方程為: 2/(12+1)*B2+(1-2/(12+1))*C3, 如使用這方程需要往下拉方程.

2.反覆計算必須開啟.

方程四:
計算DIF (EMA12-EMA26), 在E2中輸入方程:

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

方程五:

計算DEA, 和計算EMA 的方法一樣, 在F2輸入方程:

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

方程六:

計算MACD, 在G2輸入方程:

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

到這裡計算部分基本完成, 把圖表美化一下即可做出媲美tradingview的圖表,

以下為實作, 歡迎參觀和指教:

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