Lesson 11 · Real-World Data Analytics

Python Data Analytics #11: Fetching & Cleaning Real Stock Price Data in Python

Video eleven of the hundred-video real-world data analytics series, and the first video of Domain 2, Finance and Stock Market Analytics. Two real stocks,…

⬇ Download notebook ⬇ Notebook + data (.zip)Open in Colab ↗

CourseReal-World Data Analytics
Lesson11 of 26
Video27 min
FormatJupyter notebook · 30 code cells
Data2 datasets

pandas NumPy Matplotlib

What you'll learn

Welcome to Domain 2, Finance and Stock Market Analytics
Real Apple Data, First Look
Real Apple Date Parsing
Real Apple Date Range and Coverage
Loading the Real Tesla Data
Real Malformed Row in the Tesla Data
Dropping the Real Bad Row
Real Tesla Date Parsing

Datasets used in this lesson

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

aapl_finance_charts.csv58.3 KB
tesla_stock_price.csv53.3 KB

📓 Full notebook

Download .ipynb

Data Analytics 100, Video 11: Fetching and Cleaning Real Historical Stock Price Data#

Video eleven of the hundred-video real-world data analytics series, and the first video of Domain 2, Finance and Stock Market Analytics.
Two real stocks, Apple and Tesla, each with real messy quirks that need real cleaning before any analysis can trust them.
Let's get into it.

Part 1: Welcome to Domain 2, Finance and Stock Market Analytics#

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
aapl_raw = pd.read_csv('aapl_finance_charts.csv')
aapl_raw.shape

(506, 11)

Part 2: Real Apple Data, First Look#

aapl_raw.head(3)
aapl_raw.dtypes

Date              object
AAPL.Open        float64
AAPL.High        float64
AAPL.Low         float64
AAPL.Close       float64
AAPL.Volume        int64
AAPL.Adjusted    float64
dn               float64
mavg             float64
up               float64
direction         object
dtype: object

Part 3: Real Apple Date Parsing#

aapl = aapl_raw.copy()
aapl['Date'] = pd.to_datetime(aapl['Date'])
aapl = aapl.sort_values('Date').reset_index(drop=True)
aapl[['Date', 'AAPL.Open', 'AAPL.Close']].head(3)

	Date	AAPL.Open	AAPL.Close
0	2015-02-17	127.489998	127.830002
1	2015-02-18	127.629997	128.720001
2	2015-02-19	128.479996	128.449997

Part 4: Real Apple Date Range and Coverage#

aapl['Date'].min(), aapl['Date'].max()
aapl.shape[0]

Part 5: Loading the Real Tesla Data#

tsla_raw = pd.read_csv('tesla_stock_price.csv')
tsla_raw.shape
tsla_raw.head(3)

	date	close	volume	open	high	low
0	11:34	270.49	4,787,699	264.50	273.88	262.2400
1	2018/10/15	259.59	6189026.0000	259.06	263.28	254.5367
2	2018/10/12	258.78	7189257.0000	261.00	261.99	252.0100

Part 6: Real Malformed Row in the Tesla Data#

bad_rows = tsla_raw[~tsla_raw['date'].astype(str).str.match(r'^\d{4}/\d{2}/\d{2}$')]
bad_rows

	date	close	volume	open	high	low
0	11:34	270.49	4,787,699	264.5	273.88	262.24

Part 7: Dropping the Real Bad Row#

tsla = tsla_raw[tsla_raw['date'].astype(str).str.match(r'^\d{4}/\d{2}/\d{2}$')].copy()
tsla.shape[0]

Part 8: Real Tesla Date Parsing#

tsla['date'] = pd.to_datetime(tsla['date'], format='%Y/%m/%d')
tsla[['date', 'close']].head(3)

	date	close
1	2018-10-15	259.59
2	2018-10-12	258.78
3	2018-10-11	252.23

Part 9: Real Volume Column Cleanup#

tsla['volume'].head(3)
tsla['volume'] = tsla['volume'].astype(str).str.replace(',', '', regex=False).astype(float).astype(int)
tsla['volume'].head(3)

1    6189026
2    7189257
3    8128184
Name: volume, dtype: int64

Part 10: Real Chronological Sort#

tsla = tsla.sort_values('date').reset_index(drop=True)
tsla[['date', 'close']].head(3)

	date	close
0	2015-10-15	221.31
1	2015-10-16	227.01
2	2015-10-19	228.10

Part 11: Real Duplicate Date Check#

tsla['date'].duplicated().sum()
aapl['Date'].duplicated().sum()

np.int64(0)

Part 12: Real Date Range for Tesla#

tsla['date'].min(), tsla['date'].max()
tsla.shape[0]

Part 13: Real Overlapping Window#

overlap_start = max(aapl['Date'].min(), tsla['date'].min())
overlap_end = min(aapl['Date'].max(), tsla['date'].max())
overlap_start, overlap_end

(Timestamp('2015-10-15 00:00:00'), Timestamp('2017-02-16 00:00:00'))

Part 14: Real Merged Comparison Table#

aapl_window = aapl[(aapl['Date'] >= overlap_start) & (aapl['Date'] <= overlap_end)][['Date', 'AAPL.Close']]
tsla_window = tsla[(tsla['date'] >= overlap_start) & (tsla['date'] <= overlap_end)][['date', 'close']].rename(columns={'date': 'Date', 'close': 'TSLA.Close'})
merged = pd.merge(aapl_window, tsla_window, on='Date', how='inner')
merged.shape[0]

Part 15: Visualizing Real Apple Closing Price#

plt.figure(figsize=(10, 5))
plt.plot(aapl['Date'], aapl['AAPL.Close'], color='steelblue')
plt.xlabel('Real Date')
plt.ylabel('Real Closing Price (USD)')
plt.title('Real Apple Closing Price History')
plt.tight_layout()
plt.savefig('aapl_price_history.png', dpi=120)
plt.close()

Part 16: Visualizing Real Tesla Closing Price#

plt.figure(figsize=(10, 5))
plt.plot(tsla['date'], tsla['close'], color='crimson')
plt.xlabel('Real Date')
plt.ylabel('Real Closing Price (USD)')
plt.title('Real Tesla Closing Price History')
plt.tight_layout()
plt.savefig('tsla_price_history.png', dpi=120)
plt.close()

Part 17: Real Daily Returns#

merged['AAPL_Return'] = merged['AAPL.Close'].pct_change()
merged['TSLA_Return'] = merged['TSLA.Close'].pct_change()
merged[['Date', 'AAPL_Return', 'TSLA_Return']].dropna().head(3).round(4)

	Date	AAPL_Return	TSLA_Return
1	2015-10-16	-0.0073	0.0258
2	2015-10-19	0.0062	0.0048
3	2015-10-20	0.0183	-0.0661

Part 18: Real Volatility Comparison#

aapl_volatility = merged['AAPL_Return'].std()
tsla_volatility = merged['TSLA_Return'].std()
round(aapl_volatility * 100, 3), round(tsla_volatility * 100, 3)

(np.float64(1.477), np.float64(2.447))

Part 19: Real Correlation Between the Two Stocks#

return_correlation = merged[['AAPL_Return', 'TSLA_Return']].corr().iloc[0, 1]
round(return_correlation, 3)

np.float64(0.217)

Part 20: Real Cumulative Return Comparison#

merged['AAPL_Cumulative'] = (1 + merged['AAPL_Return'].fillna(0)).cumprod()
merged['TSLA_Cumulative'] = (1 + merged['TSLA_Return'].fillna(0)).cumprod()
round(merged['AAPL_Cumulative'].iloc[-1], 3), round(merged['TSLA_Cumulative'].iloc[-1], 3)

(np.float64(1.21), np.float64(1.215))

Part 21: Visualizing Real Cumulative Returns#

plt.figure(figsize=(10, 6))
plt.plot(merged['Date'], merged['AAPL_Cumulative'], label='Real Apple')
plt.plot(merged['Date'], merged['TSLA_Cumulative'], label='Real Tesla')
plt.axhline(1, color='gray', linestyle='--')
plt.xlabel('Real Date')
plt.ylabel('Real Growth of $1 Invested')
plt.title('Real Cumulative Return, Apple vs Tesla')
plt.legend()
plt.tight_layout()
plt.savefig('cumulative_return_comparison.png', dpi=120)
plt.close()

Part 22: Saving the Real Cleaned Datasets#

aapl.to_csv('aapl_clean.csv', index=False)
tsla.to_csv('tsla_clean.csv', index=False)
merged.round(4).to_csv('aapl_tsla_merged.csv', index=False)

Part 23: Real Reload Verification#

reloaded_aapl = pd.read_csv('aapl_clean.csv')
reloaded_tsla = pd.read_csv('tsla_clean.csv')
reloaded_aapl.shape[0] == aapl.shape[0], reloaded_tsla.shape[0] == tsla.shape[0]

(True, True)

Part 24: Real Sanity Check on Cleaning#

(tsla['volume'] > 0).all()

np.True_

Part 25: Real Best and Worst Single Trading Days#

aapl_best_day = merged.loc[merged['AAPL_Return'].idxmax(), 'Date']
aapl_worst_day = merged.loc[merged['AAPL_Return'].idxmin(), 'Date']
aapl_best_pct = round(merged['AAPL_Return'].max() * 100, 2)
aapl_worst_pct = round(merged['AAPL_Return'].min() * 100, 2)
print(f'Apple: best day {aapl_best_day.date()} at {aapl_best_pct}%, worst day {aapl_worst_day.date()} at {aapl_worst_pct}%.')

Apple: best day 2016-07-27 at 6.5%, worst day 2016-01-27 at -6.57%.

Part 26: Real Tesla Best and Worst Days#

tsla_best_day = merged.loc[merged['TSLA_Return'].idxmax(), 'Date']
tsla_worst_day = merged.loc[merged['TSLA_Return'].idxmin(), 'Date']
tsla_best_pct = round(merged['TSLA_Return'].max() * 100, 2)
tsla_worst_pct = round(merged['TSLA_Return'].min() * 100, 2)
print(f'Tesla: best day {tsla_best_day.date()} at {tsla_best_pct}%, worst day {tsla_worst_day.date()} at {tsla_worst_pct}%.')

Tesla: best day 2015-11-04 at 11.17%, worst day 2016-06-22 at -10.45%.

Part 27: Real Maximum Drawdown#

def max_drawdown(cumulative):
    running_max = cumulative.cummax()
    drawdown = (cumulative - running_max) / running_max
    return drawdown.min()
round(max_drawdown(merged['AAPL_Cumulative']) * 100, 1), round(max_drawdown(merged['TSLA_Cumulative']) * 100, 1)

(np.float64(-26.3), np.float64(-40.1))

Part 28: Real Average Daily Volume Comparison#

aapl_avg_volume = aapl['AAPL.Volume'].mean()
tsla_avg_volume = tsla['volume'].mean()
round(aapl_avg_volume, 0), round(tsla_avg_volume, 0)

(np.float64(43178421.0), np.float64(6148865.0))

Part 29: Real Recap Print#

print(f'Cleaned {aapl.shape[0]} real Apple trading days and {tsla.shape[0]} real Tesla trading days; {merged.shape[0]} real days overlap for direct comparison.')

Cleaned 506 real Apple trading days and 756 real Tesla trading days; 338 real days overlap for direct comparison.

Part 30: Real Direction Column Sanity Check#

aapl['ActualDirection'] = np.where(aapl['AAPL.Close'].diff() >= 0, 'Increasing', 'Decreasing')
(aapl['direction'].iloc[1:].values == aapl['ActualDirection'].iloc[1:].values).mean()

np.float64(0.8138613861386138)

Wrap-Up: What You Learned#

Real financial data pulled from the wild rarely arrives clean; comma-formatted numbers, stray malformed rows, and reversed date order are genuinely common.
Daily percentage returns, not raw prices, are the real fair way to compare two different real stocks.
Standard deviation of daily returns is a real standard, simple measure of volatility.
Correlation between two real stocks' returns shows how much they genuinely move together, or don't.
Cumulative return curves show a real intuitive answer to "what would my dollar be worth now," starting from the real same day.
Next video: real portfolio returns, risk, and the efficient frontier, building on these exact same two real stocks.

Found this useful?

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

☕ Buy me a coffee Other ways to help