Using spreadsheet to produce stocks technical indicators - MACD (Moving Average Convergence / Divergence)

Stock Exchange and Market Analysis

Technical Analysis (4)

Continuing the previous investment financial notes - stock technical analysis (3), today let us continue to produce the mathematical formula of MACD (Moving Average Convergence / Divergence) using the same template (template) into the Excel spreadsheet:

MACD:
DI = ( 2 * P _c + P _h + P _l ) / 4
DI (Demand Index): weighted price
P _c , P _h , P _l: the closing price, the highest price and the lowest price respectively
nEMA _t = nEMA _t-1 + α ₁ * (DI – nEMA _t-1 )
α ₁ = 2 / ( 1 + n )
n : moving average days, n usually takes 12 days and 26 days as the basis for MACD calculation
nEMA _t : n-day exponential smoothing moving average on day t.
DIF _t = 12EMA _t – 26EMA _t
  DIF _t : the difference between the fast (12 days) and slow (26 days) exponential smooth moving averages
DEM _t = DEM _t-1 +α ₂ * (DIF _t – DEM _t-1 )
α ₂ = 2 / ( 1 + n )
At this time , n in α ₂ takes the average moving day of 9 days, and DEM _t is the MACD indicator.
OSC _t = DIF _t - DEM _t
OSC _t : MACD bar (MACD histogram)
If you want to know more about the usage of these technical indicators, please refer to the explanation on Ref.1~2. For the direct analysis of MACD line graph, please refer to the advanced technical line diagram made by Ref.3.
Finally, in order to facilitate the reader to use the MACD indicator, the author produced an analysis sample in Excel format for readers' reference:
Template4.xlsx
This sample provides data for the period of 2003/1/22-2013/12/05 to calculate the DIF, MACD and OSC values. The previous moving averages, the BIAS, the RSI, the William indicator and the Stochastic KD line will also be included. If you find any mistakes, please do not hesitate to correct me, so that the information compiled can be more accurate and useful. Thank you!
Next time we will start to use the technical indicators provided to analyze this sample stock, and provide you with reference. In addition, the author's various indicators for stock analysis (moving average, RSI, BIAS, Stochastic KD line, William indicator, MACD and Bollinger band, etc.) produced the EXCEL spreadsheet for readers' reference, and further produced candlestick charts and other technical indicators on the same chart. Interested readers can refer to Ref.4. Stay tuned .
Reference (Ref):
1. Wikipedia: MACD
2. MoneyDJ Wealth Management Network: MACD
3. Investment and financial notes - stock technical analysis ( 9): How to produce Excel charts and show a stock K- line chart, moving average chart,volume, RSI, deviation rate, MACD, KD line and Bollinger channel diagram
4. Investment Financial Notes - Stock Technical Analysis