Mathew K Analytics

Lesson 13 · Real-World Data Analytics

RSI, MACD, Bollinger Bands & Moving Averages in Python (Pandas) | Data Analytics #13

Calculate the most popular technical indicators from scratch with pandas on real Apple price history: moving averages, golden and death crosses, RSI, MACD…

What you'll learn

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

📓 Full notebook

Download .ipynb

Data Analytics 100, Video 13: Technical Indicators on Real Price Series#

  • Video thirteen of the hundred-video real-world data analytics series.
  • Computing real moving averages, real RSI, and real MACD directly from the real Apple price history already cleaned in this domain.
  • Let's get into it.

This lesson is for learners who have cleaned a price series and now want to turn it into the indicators traders actually watch. The business question: can simple rules built from past prices, such as moving-average crossovers, tell you anything useful, and would following one have beaten simply holding the stock?

You reuse aapl_clean.csv, the cleaned Apple file saved two lessons ago, with 506 trading days from February 2015 to February 2017. The only column you need is AAPL.Close, the daily closing price; every indicator is built from it.

In this lesson you will:

  • Compute simple and exponential moving averages with rolling() and ewm()
  • Build RSI (Relative Strength Index), MACD (Moving Average Convergence Divergence) and Bollinger Bands step by step
  • Detect crossover signals with shift(1)
  • Backtest a moving-average strategy against buy-and-hold without look-ahead bias

Prerequisites: you should have the cleaned Apple CSV from the earlier finance lessons and be comfortable with pct_change(), cumprod() and basic matplotlib line charts.

Part 1: Real Indicators, Zero New Data#

Technical indicators need no extra data: they are all calculations on the closing price you already have. The code reads aapl_clean.csv with parse_dates=['Date'] so dates come back as real datetimes, then stores the AAPL.Close column in a short variable, close, to keep later formulas readable. The output confirms 506 trading days, the same count as when the file was saved. Checking the row count on reload is a quick way to be sure you are working with the right file.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
aapl = pd.read_csv('aapl_clean.csv', parse_dates=['Date'])
close = aapl['AAPL.Close']
close.shape[0]
506

Part 2: Real Simple Moving Averages#

Daily prices are noisy, and a moving average smooths them so the underlying trend is easier to see. A simple moving average (SMA) is the plain average of the last N closes. The code uses close.rolling(20).mean() and close.rolling(50).mean(), which slide a 20-day and 50-day window along the series. The first 49 rows of SMA50 are NaN because there are not yet 50 prices, so .dropna() makes the first shown row index 49, 2015-04-28, where the close was 130.56, SMA20 127.19 and SMA50 127.16. A longer window is smoother but reacts more slowly.

aapl['SMA20'] = close.rolling(20).mean()
aapl['SMA50'] = close.rolling(50).mean()
aapl[['Date', 'AAPL.Close', 'SMA20', 'SMA50']].dropna().head(3).round(2)
Date AAPL.Close SMA20 SMA50
49 2015-04-28 130.56 127.19 127.16
50 2015-04-29 128.64 127.40 127.18
51 2015-04-30 125.15 127.44 127.11

Part 3: Real Golden Cross and Death Cross Signals#

Traders watch for moments when the short-term average crosses the long-term one. A golden cross is when SMA20 moves above SMA50, often read as a sign of a new uptrend; a death cross is the reverse. The code checks two things at once: today SMA20 is above SMA50, and yesterday, using .shift(1), it was not. Combining them with & flags only the exact crossing day rather than every day of the trend. The output shows 5 golden crosses and 5 death crosses over the two years, so the trend flipped direction fairly often.

aapl['GoldenCross'] = (aapl['SMA20'] > aapl['SMA50']) & (aapl['SMA20'].shift(1) <= aapl['SMA50'].shift(1))
aapl['DeathCross'] = (aapl['SMA20'] < aapl['SMA50']) & (aapl['SMA20'].shift(1) >= aapl['SMA50'].shift(1))
aapl['GoldenCross'].sum(), aapl['DeathCross'].sum()
(np.int64(5), np.int64(5))

Part 4: Real Exponential Moving Average#

A simple average gives equal weight to every day in the window, so it can lag behind sharp moves. An exponential moving average (EMA) gives more weight to recent prices. The code uses close.ewm(span=20, adjust=False).mean(); span=20 sets the decay to roughly match a 20-day window and adjust=False uses the standard recursive formula traders use. The last rows show that on 2017-02-16 the EMA20 was 128.84 against an SMA20 of 127.64. With prices rising, the EMA sits higher because it reacts faster to the latest gains.

aapl['EMA20'] = close.ewm(span=20, adjust=False).mean()
aapl[['Date', 'SMA20', 'EMA20']].dropna().tail(3).round(2)
Date SMA20 EMA20
503 2017-02-14 126.08 127.38
504 2017-02-15 126.86 128.16
505 2017-02-16 127.64 128.84

Part 5: Visualizing Real Price with Moving Averages#

Seeing the averages on top of the price shows what the crossover signals actually look like. The code draws the close in a thin black line with the SMA20 in orange and SMA50 in blue, adds a legend, and saves the chart to moving_averages.png, so nothing displays inline. When you open it, look for where the orange line crosses the blue one: those are the golden and death crosses you counted. Also notice how the blue line trails behind turning points, which is the lag that longer averages bring.

plt.figure(figsize=(11, 6))
plt.plot(aapl['Date'], aapl['AAPL.Close'], label='Real Close Price', color='black', linewidth=1)
plt.plot(aapl['Date'], aapl['SMA20'], label='Real SMA 20', color='orange')
plt.plot(aapl['Date'], aapl['SMA50'], label='Real SMA 50', color='blue')
plt.xlabel('Real Date')
plt.ylabel('Real Price (USD)')
plt.title('Real Apple Price with Moving Averages')
plt.legend()
plt.tight_layout()
plt.savefig('moving_averages.png', dpi=120)
plt.close()

Part 6: Real RSI, the Relative Strength Index#

RSI, the Relative Strength Index, compares the size of recent gains with recent losses to judge whether a stock has risen or fallen too far, too fast. The first step splits daily price changes into gains and losses. close.diff() gives today minus yesterday; .clip(lower=0) keeps only the positive moves, and -delta.clip(upper=0) keeps the negative moves and flips them positive. rolling(14).mean() then averages each over 14 days, the standard RSI period. This step has no output; the averages are used in the formula next.

delta = close.diff()
gain = delta.clip(lower=0)
loss = -delta.clip(upper=0)
avg_gain = gain.rolling(14).mean()
avg_loss = loss.rolling(14).mean()

Part 7: Real RSI Formula#

The RSI formula turns the gain and loss averages into a score between 0 and 100. rs is average gain divided by average loss, and 100 - (100 / (1 + rs)) rescales it, so mostly gains push RSI towards 100 and mostly losses towards 0. This version uses simple 14-day averages; many charting tools use a smoothed variant, so values may differ slightly. The summary covers 492 days, since the first 14 have no value, with a mean of 52.4 and a range from 8.2 to 93.5. A median of 50.7 suggests a fairly balanced period overall.

rs = avg_gain / avg_loss
aapl['RSI'] = 100 - (100 / (1 + rs))
aapl['RSI'].dropna().describe().round(1)
count    492.0
mean      52.4
std       18.9
min        8.2
25%       38.4
50%       50.7
75%       67.7
max       93.5
Name: RSI, dtype: float64

Part 8: Real Overbought and Oversold Flags#

The usual rule of thumb is that RSI above 70 means overbought, a possible pullback ahead, and below 30 means oversold, a possible bounce. The code builds a True or False mask for each condition and .sum() counts the days. The output shows 99 overbought days and 62 oversold days out of 492 with an RSI value. These are not buy or sell instructions on their own: in a strong trend RSI can stay above 70 for weeks.

n_overbought = (aapl['RSI'] > 70).sum()
n_oversold = (aapl['RSI'] < 30).sum()
n_overbought, n_oversold
(np.int64(99), np.int64(62))

Part 9: Visualizing Real RSI#

A chart shows how RSI moves between the two thresholds over time. The code plots RSI in purple and adds dashed horizontal lines at 70 in red and 30 in green with axhline, then saves the figure to rsi_chart.png rather than displaying it. When you open it, look for clusters of days above 70 or below 30 and compare those dates with the price chart to see whether the price really did turn soon afterwards.

plt.figure(figsize=(11, 4))
plt.plot(aapl['Date'], aapl['RSI'], color='purple')
plt.axhline(70, color='red', linestyle='--', label='Real overbought (70)')
plt.axhline(30, color='green', linestyle='--', label='Real oversold (30)')
plt.xlabel('Real Date')
plt.ylabel('Real RSI')
plt.title('Real Relative Strength Index, Apple')
plt.legend()
plt.tight_layout()
plt.savefig('rsi_chart.png', dpi=120)
plt.close()

Part 10: Real MACD#

MACD, short for Moving Average Convergence Divergence, measures momentum by comparing a fast and a slow exponential average. The code computes a 12-day and a 26-day EMA with ewm(span=..., adjust=False) and subtracts them. A positive MACD means the short-term average is above the long-term one, so momentum is upward. The last three values are 4.187, 4.314 and 4.352, positive and rising, which fits Apple's climb in early 2017. The number is in dollars, so it is not directly comparable across stocks with very different prices.

ema12 = close.ewm(span=12, adjust=False).mean()
ema26 = close.ewm(span=26, adjust=False).mean()
aapl['MACD'] = ema12 - ema26
aapl['MACD'].dropna().tail(3).round(3)
503    4.187
504    4.314
505    4.352
Name: MACD, dtype: float64

Part 11: Real MACD Signal Line#

On its own, MACD is still noisy, so traders add a signal line: a 9-day EMA of the MACD itself. The code applies ewm(span=9, adjust=False).mean() to the MACD column. The output shows that on 2017-02-16 MACD was 4.352 and the signal line 3.798. MACD sitting above its signal line means momentum is still strengthening. Because both are EMAs, values at the very start of the series are less reliable while the averages settle.

aapl['MACD_Signal'] = aapl['MACD'].ewm(span=9, adjust=False).mean()
aapl[['Date', 'MACD', 'MACD_Signal']].dropna().tail(3).round(3)
Date MACD MACD_Signal
503 2017-02-14 4.187 3.496
504 2017-02-15 4.314 3.660
505 2017-02-16 4.352 3.798

Part 12: Real MACD Crossover Signals#

Just as with the moving averages, the trading signal is the moment of crossing. A bullish crossover is when MACD moves above its signal line; a bearish one is when it drops below. The code uses the same pattern as before, comparing today's position with yesterday's via .shift(1). The output shows 17 bullish and 16 bearish crossovers, roughly three times as many as the SMA crosses. MACD reacts faster, which means earlier signals but also more false alarms.

aapl['MACD_Bullish'] = (aapl['MACD'] > aapl['MACD_Signal']) & (aapl['MACD'].shift(1) <= aapl['MACD_Signal'].shift(1))
aapl['MACD_Bearish'] = (aapl['MACD'] < aapl['MACD_Signal']) & (aapl['MACD'].shift(1) >= aapl['MACD_Signal'].shift(1))
aapl['MACD_Bullish'].sum(), aapl['MACD_Bearish'].sum()
(np.int64(17), np.int64(16))

Part 13: Visualizing Real MACD#

Plotting MACD and its signal together makes crossovers and momentum shifts easy to spot. The code draws MACD in navy, the signal line in orange and a dashed zero line with axhline(0), then saves to macd_chart.png. There is no inline output. When you open it, look for points where the two lines cross and where MACD moves from below zero to above, which marks the fast average overtaking the slow one.

plt.figure(figsize=(11, 5))
plt.plot(aapl['Date'], aapl['MACD'], label='Real MACD', color='navy')
plt.plot(aapl['Date'], aapl['MACD_Signal'], label='Real Signal Line', color='orange')
plt.axhline(0, color='gray', linestyle='--')
plt.xlabel('Real Date')
plt.ylabel('Real MACD Value')
plt.title('Real MACD, Apple')
plt.legend()
plt.tight_layout()
plt.savefig('macd_chart.png', dpi=120)
plt.close()

Part 14: Real Bollinger Bands#

Bollinger Bands show how far price is stretching from its recent average, adjusted for current volatility. The middle band is the SMA20; the upper and lower bands sit two rolling standard deviations above and below it, using close.rolling(20).std(). When volatility rises the bands widen, and when it falls they narrow. The first full row, 2015-03-16, shows a lower band of 122.17, SMA20 of 127.71 and upper band of 133.25. A close outside the bands is unusual and is often read as a stretched move.

rolling_std20 = close.rolling(20).std()
aapl['BB_Upper'] = aapl['SMA20'] + 2 * rolling_std20
aapl['BB_Lower'] = aapl['SMA20'] - 2 * rolling_std20
aapl[['Date', 'BB_Lower', 'SMA20', 'BB_Upper']].dropna().head(3).round(2)
Date BB_Lower SMA20 BB_Upper
19 2015-03-16 122.17 127.71 133.25
20 2015-03-17 122.13 127.67 133.22
21 2015-03-18 122.12 127.66 133.20

Part 15: Visualizing Real Bollinger Bands#

The bands are much easier to read as a shaded area. The code plots the close and SMA20, then uses plt.fill_between to shade the region between the lower and upper bands, with alpha=0.4 making it semi-transparent so the lines stay visible. The chart is saved to bollinger_bands.png. When you open it, look for periods where the shaded band pinches narrow, a calm market, and where the price pokes outside it.

plt.figure(figsize=(11, 6))
plt.plot(aapl['Date'], aapl['AAPL.Close'], color='black', label='Real Close Price', linewidth=1)
plt.plot(aapl['Date'], aapl['SMA20'], color='blue', label='Real 20-Day SMA')
plt.fill_between(aapl['Date'], aapl['BB_Lower'], aapl['BB_Upper'], color='lightblue', alpha=0.4, label='Real Bollinger Band')
plt.xlabel('Real Date')
plt.ylabel('Real Price (USD)')
plt.title('Real Bollinger Bands, Apple')
plt.legend()
plt.tight_layout()
plt.savefig('bollinger_bands.png', dpi=120)
plt.close()

Part 16: Real Simple Crossover Strategy Backtest#

An indicator only proves its worth when turned into a rule and tested. The rule here is simple: hold the stock when SMA20 is above SMA50, otherwise hold cash. np.where sets Position to 1 or 0, and pct_change() gives daily returns. The crucial detail is Position.shift(1): today's return is earned using yesterday's signal, because you could only act on the signal after the close. The output shows the strategy fully invested in the final three days, earning 1.30%, 0.36% and -0.12%. Skipping the shift is look-ahead bias and makes a strategy look far better than it could ever be.

aapl['Position'] = np.where(aapl['SMA20'] > aapl['SMA50'], 1, 0)
aapl['DailyReturn'] = aapl['AAPL.Close'].pct_change()
aapl['StrategyReturn'] = aapl['Position'].shift(1) * aapl['DailyReturn']
aapl[['Date', 'Position', 'StrategyReturn']].dropna().tail(3).round(4)
Date Position StrategyReturn
503 2017-02-14 1 0.0130
504 2017-02-15 1 0.0036
505 2017-02-16 1 -0.0012

Part 17: Real Strategy vs Real Buy-and-Hold#

The real test is whether the strategy beat simply buying and holding. The code fills the first NaN returns with 0, adds 1 and compounds with .cumprod() for both series. The output shows 1 dollar grew to 1.064 with the crossover strategy and 1.059 with buy-and-hold. The strategy finished slightly ahead, but the gap is tiny and ignores trading costs and taxes, which would likely wipe it out. Small edges like this are rarely meaningful on one stock over two years.

strategy_cumulative = (1 + aapl['StrategyReturn'].fillna(0)).cumprod()
buyhold_cumulative = (1 + aapl['DailyReturn'].fillna(0)).cumprod()
round(strategy_cumulative.iloc[-1], 3), round(buyhold_cumulative.iloc[-1], 3)
(np.float64(1.064), np.float64(1.059))

Part 18: Visualizing Real Strategy vs Real Buy-and-Hold#

End values hide how each approach got there. The code plots both cumulative series on the same axes and saves the chart to strategy_vs_buyhold.png. There is no inline output. When you open it, look for flat stretches in the strategy line, which are periods spent in cash, and check whether they protected you during falls in the buy-and-hold line or caused you to miss rallies.

plt.figure(figsize=(11, 6))
plt.plot(aapl['Date'], strategy_cumulative, label='Real SMA Crossover Strategy')
plt.plot(aapl['Date'], buyhold_cumulative, label='Real Buy and Hold')
plt.xlabel('Real Date')
plt.ylabel('Real Growth of $1 Invested')
plt.title('Real SMA Crossover Strategy vs Real Buy-and-Hold')
plt.legend()
plt.tight_layout()
plt.savefig('strategy_vs_buyhold.png', dpi=120)
plt.close()

Part 19: Real Time Spent in the Market#

Time in the market matters: a strategy that is out half the time takes less risk but may miss gains. Because Position is 1 or 0, its .mean() is the share of days invested. The output shows 47.0%. Note that the early rows before SMA50 exists count as 0, since a comparison with NaN is False. So the strategy matched buy-and-hold's return while being exposed to the market for under half the days.

pct_time_invested = aapl['Position'].mean()
round(pct_time_invested * 100, 1)
np.float64(47.0)

Part 20: Real Sanity Check on RSI Bounds#

By construction RSI must lie between 0 and 100, so any value outside that range would reveal a formula error. The code uses .between(0, 100), which is inclusive by default, and .all() to confirm every non-missing value passes. The output is True. Simple bound checks like this are worth adding whenever an indicator has a known valid range.

aapl['RSI'].dropna().between(0, 100).all()
np.True_

Part 21: Saving the Real Indicators Table#

Saving the indicators means later lessons or dashboards can use them without recalculating. The code lists the columns to keep, rounds to three decimals and writes them with index=False, then reloads the file and compares row counts. The output is True. Notice that the early rows contain blanks for indicators that need a warm-up period; those NaNs are saved as empty cells, which is expected.

indicator_cols = ['Date', 'AAPL.Close', 'SMA20', 'SMA50', 'EMA20', 'RSI', 'MACD', 'MACD_Signal', 'BB_Upper', 'BB_Lower']
aapl[indicator_cols].round(3).to_csv('aapl_technical_indicators.csv', index=False)
reloaded = pd.read_csv('aapl_technical_indicators.csv')
reloaded.shape[0] == aapl.shape[0]
True

Part 22: Real Recap Print#

A summary line records what was built and the headline behaviour of the strategy. The f-string uses len(aapl) for the row count and the rounded share of days invested. The output reads: indicators built on 506 trading days, with the SMA crossover strategy invested 47% of the time. This is the sentence to lead with if you are explaining the work to someone who has not seen the charts.

print(f'Built moving averages, RSI, MACD, and Bollinger Bands on {len(aapl)} real trading days; the SMA crossover strategy was invested {round(pct_time_invested*100)}% of the time.')
Built moving averages, RSI, MACD, and Bollinger Bands on 506 real trading days; the SMA crossover strategy was invested 47% of the time.

Wrap-Up: What You Learned#

  • Moving averages, RSI, MACD, and Bollinger Bands are all genuinely just arithmetic layered on top of a real closing price series.
  • A golden cross and a death cross are real trend-change signals defined purely by where two real moving averages sit relative to each other.
  • RSI measures the real balance of recent gains against real losses, scaled onto a real familiar zero-to-hundred range.
  • MACD tracks the real gap between a real fast and real slow exponential average, with its own real signal line for crossovers.
  • Turning a real indicator into an actual real trading rule and backtesting it honestly is what separates a real chart pattern from real evidence.
  • Next video: real volatility analysis and rolling risk metrics, digging deeper into how real risk itself changes over time.

Practice on your own

  • Change the crossover rule to SMA50 versus a 100-day SMA, count the new golden and death crosses, and compare the backtest result with the 1.064 from this lesson.
  • Build a strategy that holds Apple only when MACD is above its signal line, using .shift(1) as before, and compare its final value and time invested with buy-and-hold.
  • Count how many days the close finished above BB_Upper or below BB_Lower, and check the average next-day return after each.

The next lesson moves on to volatility analysis and rolling risk metrics, looking at how risk itself changes over time.

🧠 Test yourself

Pick an answer to check it. Your best score is saved in this browser.

  1. What does this code print?

    import pandas as pd
    s = pd.Series([1, 2, 3, 4, 5])
    print(s.rolling(3).mean().tolist())
    
  2. In the lesson, a golden cross is flagged when...

  3. What does an RSI above 70 usually suggest in the lesson's interpretation?

  4. How is MACD built in the lesson?

  5. The backtest uses aapl['Position'].shift(1) * aapl['DailyReturn']. Why shift the position by one day?

💻 Practice exercises

Try each one in the notebook or in your own editor before opening the solution.

  1. Given closes [10, 11, 12, 11, 10, 11, 13] compute a 3-day simple moving average and print it next to the close in a DataFrame.

    💡 Hint

    Use Series.rolling(3).mean().

    ✅ Show solution
    import pandas as pd
    
    close = pd.Series([10, 11, 12, 11, 10, 11, 13], name='Close')
    df = pd.DataFrame({'Close': close, 'SMA3': close.rolling(3).mean()})
    print(df.round(2))
    
  2. Create a 150-day synthetic price random walk (seed 5, start 100, normal steps with std 1). Compute SMA10 and SMA30, then count golden crosses (SMA10 crossing above SMA30) and death crosses (crossing below).

    💡 Hint

    Compare today's relation with yesterday's using shift(1).

    ✅ Show solution
    import numpy as np
    import pandas as pd
    
    rng = np.random.default_rng(5)
    close = pd.Series(100 + rng.normal(0, 1, 150).cumsum())
    sma10 = close.rolling(10).mean()
    sma30 = close.rolling(30).mean()
    
    golden = (sma10 > sma30) & (sma10.shift(1) <= sma30.shift(1))
    death = (sma10 < sma30) & (sma10.shift(1) >= sma30.shift(1))
    print('Golden crosses:', int(golden.sum()))
    print('Death crosses:', int(death.sum()))
    
  3. On a 250-day synthetic random walk (seed 11, start 100, normal steps std 1), compute a 14-day RSI using rolling means of gains and losses, then print how many days RSI is above 70 and below 30. Also confirm RSI always stays between 0 and 100.

    💡 Hint

    delta = close.diff(); gains = delta.clip(lower=0); losses = -delta.clip(upper=0).

    ✅ Show solution
    import numpy as np
    import pandas as pd
    
    rng = np.random.default_rng(11)
    close = pd.Series(100 + rng.normal(0, 1, 250).cumsum())
    
    delta = close.diff()
    gain = delta.clip(lower=0)
    loss = -delta.clip(upper=0)
    avg_gain = gain.rolling(14).mean()
    avg_loss = loss.rolling(14).mean()
    rs = avg_gain / avg_loss
    rsi = 100 - 100 / (1 + rs)
    
    print('Overbought days:', int((rsi > 70).sum()))
    print('Oversold days:', int((rsi < 30).sum()))
    print('Within 0-100:', rsi.dropna().between(0, 100).all())
    

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.