Last Update: February 6, 2020
Stock technical indicators are calculated by applying certain formula to stock prices and volume data. They are used to alert on the need to study stock price action with greater detail, confirm other technical indicators’ signals or predict future stock prices direction.
This topic is part of Stock Technical Analysis with Excel course. Feel free to take a look at Course Curriculum.
This tutorial has an educational and informational purpose and doesn’t constitute any type of trading or investment advice. All content, including spreadsheet calculations and data, is presented for personal educational use exclusively and with no guarantee of exactness of completeness. Past performance doesn’t guarantee future results. Please read full Disclaimer.
An example of stock technical indicators is moving averages convergence/divergence MACD [1] which consists of centered oscillator that measures a stock price momentum and identifies trends. Twelve days are commonly used for short-term smoothing, twenty-six days for long-term smoothing and nine days for signal.
1. Technical indicator calculation.
1.1. Short-term and long-term smoothing calculation.
Where = current period stock close prices,
= current period close prices
periods exponential moving average,
= initial close prices
periods simple moving average.
1.2. Moving averages convergence/divergence MACD stock technical indicator, signal and histogram calculation.
Where = current period close prices MACD,
= current period close prices MACD signal,
= current period close prices MACD histogram.
2. Excel example.
2.1. Short-term smoothing calculation.
- Data: S&P 500® index replicating ETF (ticker symbol: SPY) daily close prices (2016).
- Short-term smoothing number of periods not fixed and only included for educational purposes.
2.2. Short-term smoothing calculation formulas.
2.3. Long-term smoothing and MACD stock technical indicator calculation.
- Data: S&P 500® index replicating ETF (ticker symbol: SPY) daily close prices (2016).
- Long-term smoothing and MACD technical indicator number of periods not fixed and only included for educational purposes.
2.4. Long-term smoothing and MACD stock technical indicator calculation formulas.
2.5. MACD stock technical indicator signal and histogram calculation.
- Data: S&P 500® index replicating ETF (ticker symbol: SPY) daily close prices (2016).
- MACD technical indicator signal and histogram number of periods not fixed and only included for educational purposes.
2.6. MACD stock technical indicator signal and histogram calculation formulas.
2.7. MACD stock technical indicator chart.
3. References
[1] Gerald Appel. “Technical Analysis: Powerful Tools for Active Investors”. FT Press. 2005.