Michael Brenndoerfer | Data & AI, Private Equity, Technology

Michael Brenndoerfer

Data Handling and Visualization › Loading Financial Data

Michael BrenndoerferNovember 7, 20256 min read

Quantitative FinanceAll chapters

Part 1 of 15Mathematical Foundations

Chapter 9Data Handling and Visualization › Loading Financial Data

Reading section 2 of 9 · Loading Financial Data · 6 minSection 2 of 9 · 6 min

Section 2 of 9 · 6 min read

Data Handling and Visualization › Loading Financial DataOne section · Sections in this chapter2 / 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 data comes in many formats: CSV files from data vendors, Excel spreadsheets from analysts, SQL databases at institutions, and API responses from market data providers. Pandas supplies readers for many tabular formats and can organize results returned by database drivers, vendor SDKs, or custom API clients. Some readers accept authentication options, but vendor-specific authentication and connectors can still require external clients. Once the records are available, pandas provides a consistent table interface for analysis.

Reading CSV FilesLink Copied

CSV is a common introductory and interchange format for historical price data. It is human-readable and broadly supported by tabular-data tools, although dialects, encodings, schemas, and alternative formats vary. Let's load a sample dataset to illustrate basic loading patterns:

In[3]:

Code

import numpy as np
import pandas as pd

# Create sample price data (simulating what you'd load from a CSV)
np.random.seed(42)
dates = pd.date_range(
    "2020-01-01", periods=252, freq="B"
)  # Weekdays, not an exchange calendar
prices = 100 * np.cumprod(1 + np.random.randn(252) * 0.02)
open_prices = prices * (1 + np.random.randn(252) * 0.005)
high_spread = np.abs(np.random.randn(252)) * 0.01
low_spread = np.abs(np.random.randn(252)) * 0.01

df = pd.DataFrame(
    {
        "date": dates,
        "open": open_prices,
        "high": np.maximum(open_prices, prices) * (1 + high_spread),
        "low": np.minimum(open_prices, prices) * (1 - low_spread),
        "close": prices,
        "volume": np.random.randint(1_000_000, 10_000_000, 252),
    }
)

Use pd.read_csv() to load data. Three parameters matter for financial data:

parse_dates: Attempts to parse specified columns as datetimes. Under pandas 3's default string inference, unparsable values or incompatible mixed time zones can leave the column with str dtype instead of a datetime dtype, so validate the result.
index_col: Sets a column as the DataFrame index (typically the date)
dtype: Specifies column data types to prevent incorrect type inference, for example forcing numeric columns to float64.

In[4]:

Code

from pathlib import Path
from tempfile import TemporaryDirectory

# Create and remove only a directory owned by this example.
# A same-named file in the working directory is never touched.
with TemporaryDirectory(prefix="qf-ch09-prices-") as sample_dir:
    sample_csv = Path(sample_dir) / "sample_prices.csv"
    df.to_csv(sample_csv, index=False)

    # Load it back, parsing dates and setting the index
    loaded_df = pd.read_csv(sample_csv, parse_dates=["date"], index_col="date")

The CSV exists only inside the temporary directory. Leaving the with block removes that directory; the loaded DataFrame remains available for subsequent cells.

Out[5]:

Console

                  open        high         low       close   volume
date                                                               
2020-01-01  102.065047  102.729126  100.495210  100.993428  1100734
2020-01-02  101.234072  101.727209  100.527996  100.714153  9546310
2020-01-03  101.243759  102.623134  100.374726  102.018781  3851274
2020-01-06  104.871805  106.034615  104.137377  105.126334  2225811
2020-01-07  105.296830  105.347922  104.031707  104.634020  4081091

Index type: DatetimeIndex
Data shape: (252, 5)

This displays the first five rows of synthetic OHLC (open, high, low, close) data and volume. The generator explicitly keeps each high at or above both open and close, and each low at or below both. The index is a DatetimeIndex, as the type output confirms. The shape shows 252 synthetic weekday observations and 5 data columns. Pandas frequency "B" excludes weekends but is not an exchange calendar and does not remove exchange-specific holidays. The DatetimeIndex enables date-range slicing and frequency-based resampling that plain text dates do not provide directly.

Handling Multiple SecuritiesLink Copied

An analysis can involve multiple securities. A portfolio manager might track dozens or hundreds of positions, while a factor model might require returns for thousands of stocks. Organize this data in two ways: wide format with one column per security, or long format with a single column for security identifier. Each format has advantages for different analyses.

In[6]:

Code

# Create multi-security data in wide format
np.random.seed(42)
dates = pd.date_range("2020-01-01", periods=252, freq="B")

n = len(dates)

# Simulate correlated, heavy-tailed log returns with a stress period.
# The ticker names are illustrative labels, not observed market data.
stress_mask = (dates >= "2020-02-20") & (dates <= "2020-04-15")

# Common market factor: higher volatility and negative drift during stress
market_vol = np.where(stress_mask, 0.03, 0.012)
market_drift = np.where(stress_mask, -0.0015, 0.0003)
market_factor = market_drift + market_vol * np.random.standard_t(df=5, size=n)

# Asset-specific exposures and idiosyncratic risk
betas = {"AAPL": 1.2, "GOOGL": 1.0, "MSFT": 1.1}
idio_vol = {
    "AAPL": np.where(stress_mask, 0.020, 0.012),
    "GOOGL": np.where(stress_mask, 0.018, 0.011),
    "MSFT": np.where(stress_mask, 0.021, 0.013),
}
asset_drift = {"AAPL": 0.0004, "GOOGL": 0.0002, "MSFT": 0.00035}

log_returns_sim = pd.DataFrame(
    {
        ticker: (
            asset_drift[ticker]
            + betas[ticker] * market_factor
            + idio_vol[ticker] * np.random.standard_t(df=7, size=n)
        )
        for ticker in ["AAPL", "GOOGL", "MSFT"]
    },
    index=dates,
)

start_prices = pd.Series({"AAPL": 100.0, "GOOGL": 150.0, "MSFT": 80.0})
multi_prices = start_prices * np.exp(log_returns_sim.cumsum())

The variables market_vol and idio_vol are shock scales, not standard deviations. A Student-t variable with $\nu>2$ degrees of freedom has variance $\nu/(\nu-2)$ , so the factor's standard deviation is its scale times $\sqrt{5/3}$ and each idiosyncratic shock's is its scale times $\sqrt{7/5}$ .

The finite seeded prices and their descriptive statistics are used throughout this chapter. The ideal untruncated Student-t log-shock model has no finite positive exponential moments: its polynomially decaying tails cannot offset exponential growth. Transforming these log shocks to simple returns with $\exp(r)-1$ therefore does not provide finite population means, variances, or higher absolute moments. Do not treat the sample summaries below as calibration of those population moments.

Out[7]:

Console

Wide format (one column per security):
                  AAPL       GOOGL       MSFT
2020-01-01  101.264291  149.781536  80.022172
2020-01-02   98.706922  147.383256  77.148026
2020-01-03  100.009852  150.899989  78.238160
2020-01-06  101.610677  152.126586  79.573452
2020-01-07  103.564120  152.944752  80.247694

Three columns appear (AAPL, GOOGL, MSFT), each with price data for one security. Each row is a date, enabling easy cross-security price comparison. This format aligns values for cross-security calculations in the same row. Computing a correlation matrix or portfolio returns becomes straightforward.

Long format works better for filtering and grouping operations: especially when applying the same analysis across multiple securities or including additional attributes like sector or fundamentals.

In[8]:

Code

# Convert to long format
long_format = (
    multi_prices.reset_index()
    .melt(id_vars="index", var_name="ticker", value_name="price")
    .rename(columns={"index": "date"})
)

Out[9]:

Console

Long format (stacked):
        date ticker       price
0 2020-01-01   AAPL  101.264291
1 2020-01-02   AAPL   98.706922
2 2020-01-03   AAPL  100.009852
3 2020-01-06   AAPL  101.610677
4 2020-01-07   AAPL  103.564120
5 2020-01-08   AAPL  102.658508
6 2020-01-09   AAPL  104.309018
7 2020-01-10   AAPL  102.718245
8 2020-01-13   AAPL  102.366038
9 2020-01-14   AAPL  101.691553

Each price observation is a separate row with columns for date, ticker, and price. By default, melt() emits the complete AAPL value block first, so the first rows are consecutive dates for AAPL rather than multiple tickers on one date. Sort by date and ticker if that row order is needed. Long format enables filtering and grouping, though cross-security calculations generally require pivoting to wide format.