Michael Brenndoerfer | Data & AI, Private Equity, Technology

Michael Brenndoerfer

Data Handling and Visualization › Time Series Fundamentals

Michael BrenndoerferNovember 7, 202512 min read

Quantitative FinanceAll chapters

Part 1 of 15Mathematical Foundations

Chapter 9Data Handling and Visualization › Time Series Fundamentals

Reading section 3 of 9 · Time Series Fundamentals · 12 minSection 3 of 9 · 12 min

Section 3 of 9 · 12 min read

Data Handling and Visualization › Time Series FundamentalsOne section · Sections in this chapter3 / 9 ↓

Sections in this chapter9

Reading tools Definitions & inline links

Choose your expertise level to adjust how many terms are explained. Beginners see more tooltips, experts see fewer to maintain reading flow. Hover over underlined terms for instant definitions.

Article links

Make inline references clickable

Financial analysis revolves around time series: sequences of observations indexed by time. Time series have an inherent ordering, whereas cross-sectional observations are indexed by entities or units; neither structure guarantees independence. Prices can depend on prior values, and return distributions can exhibit serial dependence or volatility clustering. Pandas provides specialized tools for temporal data that simplify time-based selection, alignment, and aggregation.

The DatetimeIndexLink Copied

DatetimeIndex

A pandas index type for timestamps enabling time-based slicing, resampling, and alignment.

DatetimeIndex: When your DataFrame has a DatetimeIndex, you gain access to time-based selection that understands calendar concepts like months, quarters, and years. This enables simple string-based slicing instead of constructing a boolean mask.

In[10]:

Code

# Select data for a specific month using .loc for partial string indexing
jan_2020 = multi_prices.loc["2020-01"]

# Select a date range
q1_2020 = multi_prices.loc["2020-01":"2020-03"]

# Select a specific date
specific_day = multi_prices.loc["2020-06-15"]

Out[11]:

Console

January 2020 data points: 23
Q1 2020 data points: 65

Prices on 2020-06-15:
AAPL     137.188506
GOOGL    162.577217
MSFT      78.243302
Name: 2020-06-15 00:00:00, dtype: float64

For this exact generated index, January contains 23 observations and Q1 contains 65. These are pandas "B" weekdays, not exchange-calendar trading sessions. The specific-day selection returns a Series with one price value for each simulated security. Partial string indexing expresses the requested calendar range without a separate boolean mask.

Resampling Time SeriesLink Copied

Financial data often needs to be converted between frequencies. Daily prices might need weekly or monthly conversion for longer-term analysis, risk reporting, or comparison with lower-frequency economic data. Alternatively, you might aggregate intraday tick data into daily bars for overnight processing. The resample() method handles this transformation elegantly, treating time series frequency conversion as a specialized form of grouping.

In[12]:

Code

# Resample daily prices to monthly using last observation
monthly_prices = multi_prices.resample("ME").last()

# For OHLC data, you'd use different aggregations
# open: first, high: max, low: min, close: last, volume: sum

Out[13]:

Console

Monthly prices (end of month):
                  AAPL       GOOGL       MSFT
2020-01-31   98.680295  154.108855  73.578792
2020-02-29   98.157671  143.712118  69.690895
2020-03-31   87.685277  131.849042  54.452322
2020-04-30   76.466313  105.191337  53.847502
2020-05-31  120.107901  149.071665  71.474786

The result has 12 rows labeled at calendar month end. Each value is the last available observation in its monthly bin; the label itself can fall on a weekend or holiday. Downsampling is useful when the analysis needs lower-frequency endpoints or aggregates. Upsampling requires an explicit missing-value policy, and inserted values are not new market observations. Frequency codes include "D" for calendar day, "W" for weekly, "ME" for month end, and "QE" for quarter end. The aggregation function matters: last selects an endpoint observation, while mean computes a period average. For OHLC data, use first for open, max for high, min for low, last for close, and sum for volume when those operations match the source's bar semantics.

Out[14]:

Visualization

Line chart comparing simulated daily prices with monthly endpoint observations for an AAPL-labelled series. — Simulated daily AAPL-labelled prices and the last available observation in each calendar-month bin. The 12 orange markers reduce the number of displayed observations but do not average or mathematically smooth the daily path.

Rolling Window CalculationsLink Copied

Many financial metrics rely on rolling windows, including moving averages, rolling volatility, and rolling correlations. These calculations capture how a statistic evolves over time and provide insight into changing market conditions that a single summary statistic would obscure. A rolling window calculation computes a statistic over a fixed-size moving window of data. For a window of size $n$ at time $t$ , we compute the statistic using observations from $t-n+1$ to $t$ .

Imagine sliding a frame across the data. At each position, you calculate a statistic using observations inside the frame, record it, then shift the frame forward by one observation. This generates a time series showing how the statistic evolves. For moving averages and many other smooth rolling estimators, window size creates a common tradeoff: smaller windows usually react sooner to recent changes but vary more from sample to sample, while larger windows usually produce smoother estimates but react more slowly. This pattern is a heuristic, not a guarantee for every rolling statistic or dataset.

Out[15]:

Visualization

Diagram showing how a rolling window slides across time series data. — Rolling window concept: a 5-observation window slides across price data. At t=9, indices 5 through 9 contain 104, 106, 108, 107, and 109, whose mean is 106.8. Sliding the window forward produces one statistic per eligible endpoint.

Formally, a rolling statistic $S_t$ with window size $n$ is computed as:

S_t = f(X_{t-n+1}, X_{t-n+2}, \ldots, X_t)

where:

$S_t$ : statistic value at time $t$
$f$ : the statistical function (examples include mean, standard deviation, maximum, minimum, or any aggregate function)
$X_i$ : the observation at time $i$
$n$ : the window size (number of observations to include)
$t$ : current time index

At each time point $t$ : you gather the most recent $n$ observations, apply your chosen function $f$ , and record the result. As $t$ advances, the window slides forward by dropping the oldest observation and incorporating the newest one. This creates a dynamic view of your statistic that reveals patterns invisible in static summaries.

Pandas makes these calculations straightforward:

In[16]:

Code

# Calculate 20-day and 50-day moving averages for AAPL
aapl = multi_prices["AAPL"].to_frame()
aapl["MA20"] = aapl["AAPL"].rolling(window=20).mean()
aapl["MA50"] = aapl["AAPL"].rolling(window=50).mean()

# Rolling 20-day volatility of returns
returns = multi_prices["AAPL"].pct_change()
rolling_vol = returns.rolling(window=20).std() * np.sqrt(252)  # Annualized

The annualization formula converts daily volatility to annual volatility. Volatility is often annualized to support comparison across assets and horizons. Other risk metrics use application-specific horizons, so state the horizon and scaling assumptions explicitly. To understand why the square root appears, consider that volatility scales with the square root of time under the assumption of independent returns (the "square root of time" rule). This relationship, known as the "square root of time" rule, shows how risk scales across investment horizons.

\sigma_{\text{annual}} = \sigma_{\text{daily}} \times \sqrt{T}

where:

$\sigma_{\text{annual}}$ : square-root-of-time annualized volatility estimate
$\sigma_{\text{daily}}$ : daily volatility, which is the standard deviation of daily returns
$T$ : annualization factor in observations per year; 252 is a common convention for daily US-market data, not the exact count in every calendar year

Let $A_T = \sum_{t=1}^{T} R_t$ denote the additive aggregate of $T$ daily returns. If the daily returns are independent and identically distributed with variance $\sigma_d^2$ (or, more generally, have zero pairwise covariances and the same variance), then the variance of this sum is:

\begin{aligned} \text{Var}(A_T) &= \sum_{t=1}^{T} \text{Var}(R_t) && \text{(zero cross-covariances)} \\ &= T\sigma_d^2 && \text{(sum of } T \text{ identical variances)} \end{aligned}

where:

$\text{Var}(A_T)$ : variance of the additive return aggregate over $T$ days
$R_t$ : return at time $t$
$\sigma_d^2$ : variance of daily returns
$T$ : number of days

For the simple returns used in this chapter, the exact compounded $T$ -day return is $\prod_{t=1}^{T}(1+R_t)-1$ , not $A_T$ . Multiplying daily simple-return volatility by $\sqrt{T}$ is therefore a square-root-of-time convention or small-return approximation, not the exact standard deviation of compounded simple returns in general. The variance relation is exact for additive returns such as log returns when the stated covariance conditions hold.

Taking the square root to obtain standard deviation gives:

\sigma_T = \sqrt{T}\sigma_d

where:

$\sigma_T$ : standard deviation of the additive $T$ -day return aggregate under the stated covariance conditions
$\sigma_d$ : daily volatility
$T$ : number of days

A daily volatility of 1% does not imply annual volatility of 252% but rather approximately 16%, since $\sqrt{252} \approx 15.9$ . This square-root-of-time scaling is used in risk management and option pricing.

Out[17]:

Console

Prices with moving averages:
                 AAPL        MA20        MA50
2020-03-10  93.269925  103.915180  103.268932
2020-03-11  90.534324  103.177259  103.054333
2020-03-12  93.769109  102.561292  102.955577
2020-03-13  90.276702  101.720663  102.760914
2020-03-16  87.935324  100.611557  102.487407

Current 20-day annualized volatility: 32.86%

AAPL-labelled simulated prices appear alongside their 20- and 50-observation moving averages. The shorter average responds more quickly because it uses fewer past observations. The current volatility value is the latest 20-observation sample standard deviation of daily simple returns multiplied by $\sqrt{252}$ ; it is a square-root-of-time annualized estimate, not the standard deviation of observed annual returns. For a fully observed input, the first $n-1$ rolling values are NaN when the window requires $n$ observations. Here, pct_change() contributes an additional leading NaN, so the first 20 rolling-volatility values are NaN. Use min_periods to permit earlier results with smaller effective samples.

Handling Missing DataLink Copied

Financial time series can contain missing values. Markets close for holidays, securities halt trading, and data feeds occasionally fail. Depending on the function and its parameters, missing values can propagate, be skipped, or change the effective sample. The treatment therefore affects the meaning of downstream results.

In[18]:

Code

# Create data with some missing values
prices_with_gaps = multi_prices.copy()
prices_with_gaps.iloc[10:15, 0] = np.nan  # Remove some AAPL prices
prices_with_gaps.iloc[20:22, 1] = np.nan  # Remove some GOOGL prices

Out[19]:

Console

Missing values per column:
AAPL     5
GOOGL    2
MSFT     0
dtype: int64

AAPL has 5 missing values and GOOGL has 2, while MSFT has none. These gaps represent periods when we artificially removed data to demonstrate handling techniques. In real data, missing values might indicate trading halts, data feed failures, or corporate actions. Pandas provides several strategies for handling missing data: each strategy has different assumptions and use cases.

In[20]:

Code

# Forward fill: use last known value
ffill_prices = prices_with_gaps.ffill()

# Backward fill: use next known value
bfill_prices = prices_with_gaps.bfill()

# Interpolation: equal row-position steps between known values
interp_prices = prices_with_gaps.interpolate(method="linear")

# Drop rows with any missing values
clean_prices = prices_with_gaps.dropna()

Out[21]:

Console

Original rows: 252
After dropna: 245

Missing after forward fill: 0

Dropping rows with any missing value reduces this dataset from 252 to 245 rows. Forward filling removes the gaps by carrying the last observed value forward, which assumes a stale price until a new observation arrives. That can create artificial zero returns and suppress measured volatility. Whether it is appropriate depends on the asset, trading calendar, halt status, valuation policy, and analysis. Backward filling directly uses a future observation and can introduce look-ahead bias in a backtest. Interpolation across an interior gap also uses the later endpoint: it is an offline reconstruction, not a price available at the missing timestamp. Here method="linear" spaces values by row position, ignoring elapsed calendar time; weekend gaps mean its trace need not be straight on a calendar-time axis.

Out[22]:

Visualization

Line chart comparing forward fill, backward fill, and linear interpolation methods for handling missing data. — Three strategies for handling missing data in simulated AAPL-labelled prices. Forward fill carries the last value forward; backward fill projects the next value backward. Linear interpolation uses equal row-position steps, so its trace need not be straight against calendar time. Both backward fill and interior interpolation use a future endpoint. The shaded region highlights the gap period; each method changes the returns around the gap.