pandas: price data as a table

Price data is a table: one row per bar, with columns for the open, high, low, close and volume. Lists, from the previous lesson, are awkward for this. You would need five parallel lists and a sixth for the dates, and keeping them aligned would be your problem. The pandas library solves this. It stores data as a DataFrame: a table with named columns and a labelled index, where the index for price data is the date. Data ordered by time like this is called a time series, and pandas has tools built specifically for it.

This lesson builds a year of synthetic daily bars, then shows the operations you will use constantly: selecting columns, selecting dates, filtering by condition, and converting daily bars into weekly ones.

Generating synthetic price data

All data in this course is generated rather than downloaded. That keeps every example reproducible, free of the data errors covered in Module 02, and clearly separate from any real market. The function below creates OHLCV bars from a random walk.

import numpy as np
import pandas as pd
 
 
def make_prices(n_days=260, seed=42):
    """Synthetic daily OHLCV bars from a random walk. Not real market data."""
    rng = np.random.default_rng(seed)
    dates = pd.bdate_range("2024-01-01", periods=n_days, name="date")
    close = 100 * np.exp(np.cumsum(rng.normal(0.0003, 0.012, n_days)))
    prev_close = np.concatenate([[100.0], close[:-1]])
    open_ = prev_close * (1 + rng.normal(0, 0.002, n_days))
    high = np.maximum(open_, close) * (1 + np.abs(rng.normal(0, 0.004, n_days)))
    low = np.minimum(open_, close) * (1 - np.abs(rng.normal(0, 0.004, n_days)))
    volume = rng.integers(800_000, 1_500_000, n_days)
    return pd.DataFrame(
        {"open": open_, "high": high, "low": low, "close": close, "volume": volume},
        index=dates,
    )
 
 
prices = make_prices()
print(prices.head().round(2))
print(prices.shape)
print(prices.index.min().date(), "to", prices.index.max().date())

              open    high    low   close   volume
date                                              
2024-01-01   99.95  100.79  99.93  100.40  1029686
2024-01-02  100.34  100.70  99.15   99.18  1025550
2024-01-03   99.23  100.64  98.51  100.11  1399629
2024-01-04  100.00  101.35  99.64  101.27  1181183
2024-01-05  101.37  101.94  98.96   98.96  1293259
(260, 5)
2024-01-01 to 2024-12-27

You do not need to memorise the generator, but it is worth understanding once, because it shows what the data does and does not contain.

• rng = np.random.default_rng(seed) creates a random number generator. The seed fixes its sequence, so every run, on every computer, produces the same numbers.
• pd.bdate_range(...) creates 260 business days, Monday to Friday, starting on 1 January 2024. That is roughly one trading year.
• rng.normal(0.0003, 0.012, n_days) draws 260 daily returns from a normal distribution with a small average and a spread of about 1.2% per day. np.cumsum adds them up day by day, and 100 * np.exp(...) turns that running total into a price path starting near 100. You will see why this works in the next lesson.
• Each open sits close to the previous close; close[:-1] means "every close except the last", shifted one day later by putting 100 in front.
• The high is a little above the larger of the open and close, and the low a little below the smaller, so every bar is internally consistent.
• Volume is a random whole number in a fixed range.

Finally, pd.DataFrame({...}, index=dates) assembles the table. The curly braces map each column name to its values, and index=dates labels each row with its date.

In the output, .head() shows the first five rows, and .round(2) rounds for display without changing the data. .shape gives the size as (rows, columns). The index has its own methods: .min() and .max() give the first and last dates.

Selecting data

Three kinds of selection cover most needs. Each example rebuilds the data first so that it runs on its own; in a notebook, you would run make_prices once in an earlier cell.

import numpy as np
import pandas as pd
 
 
def make_prices(n_days=260, seed=42):
    """Synthetic daily OHLCV bars from a random walk. Not real market data."""
    rng = np.random.default_rng(seed)
    dates = pd.bdate_range("2024-01-01", periods=n_days, name="date")
    close = 100 * np.exp(np.cumsum(rng.normal(0.0003, 0.012, n_days)))
    prev_close = np.concatenate([[100.0], close[:-1]])
    open_ = prev_close * (1 + rng.normal(0, 0.002, n_days))
    high = np.maximum(open_, close) * (1 + np.abs(rng.normal(0, 0.004, n_days)))
    low = np.minimum(open_, close) * (1 - np.abs(rng.normal(0, 0.004, n_days)))
    volume = rng.integers(800_000, 1_500_000, n_days)
    return pd.DataFrame(
        {"open": open_, "high": high, "low": low, "close": close, "volume": volume},
        index=dates,
    )
 
 
prices = make_prices()
 
close = prices["close"]                       # one column: a Series
print(close.tail(3).round(2))
 
week = prices.loc["2024-03-04":"2024-03-08", ["open", "close"]]   # rows by date label
print(week.round(2))
 
up_days = prices[prices["close"] > prices["open"]]   # rows where a condition is True
print(f"Up days: {len(up_days)} of {len(prices)}")

date
2024-12-25    94.85
2024-12-26    94.73
2024-12-27    93.20
Freq: B, Name: close, dtype: float64
              open   close
date                      
2024-03-04  104.23  104.75
2024-03-05  104.68  105.89
2024-03-06  106.16  106.20
2024-03-07  106.33  107.10
2024-03-08  107.47  107.22
Up days: 121 of 260

A column. prices["close"] returns a single column as a Series: a one-dimensional version of a DataFrame, with the same date index. .tail(3) shows the last three values. The footer describes it: Freq: B means business-day frequency, and dtype: float64 means the values are floats.

Rows by date. .loc[...] selects rows by their index labels. With dates as the index, you can write them as strings. The slice "2024-03-04":"2024-03-08" includes both ends, which is different from list slicing. The second part, a list of column names, keeps just those columns.

Rows by condition. prices["close"] > prices["open"] compares the two columns row by row and produces a Series of True and False. Putting that inside prices[...] keeps only the rows where it is True. Here, 121 of the 260 bars closed above their open.

Resampling to a higher timeframe

Resampling combines bars into a longer timeframe. The key point is that each column needs its own rule. A weekly bar opens at the first daily open, reaches the highest daily high and the lowest daily low, closes at the last daily close, and trades the total volume.

import numpy as np
import pandas as pd
 
 
def make_prices(n_days=260, seed=42):
    """Synthetic daily OHLCV bars from a random walk. Not real market data."""
    rng = np.random.default_rng(seed)
    dates = pd.bdate_range("2024-01-01", periods=n_days, name="date")
    close = 100 * np.exp(np.cumsum(rng.normal(0.0003, 0.012, n_days)))
    prev_close = np.concatenate([[100.0], close[:-1]])
    open_ = prev_close * (1 + rng.normal(0, 0.002, n_days))
    high = np.maximum(open_, close) * (1 + np.abs(rng.normal(0, 0.004, n_days)))
    low = np.minimum(open_, close) * (1 - np.abs(rng.normal(0, 0.004, n_days)))
    volume = rng.integers(800_000, 1_500_000, n_days)
    return pd.DataFrame(
        {"open": open_, "high": high, "low": low, "close": close, "volume": volume},
        index=dates,
    )
 
 
prices = make_prices()
 
weekly = prices.resample("W-FRI").agg(
    {"open": "first", "high": "max", "low": "min", "close": "last", "volume": "sum"}
)
print(weekly.head(3).round(2))
print(f"{len(prices)} daily bars -> {len(weekly)} weekly bars")

             open    high    low   close   volume
date                                             
2024-01-05  99.95  101.94  98.51   98.96  5929307
2024-01-12  99.16   99.34  96.28   96.34  5898319
2024-01-19  96.20  101.03  95.92  100.41  6326310
260 daily bars -> 52 weekly bars

.resample("W-FRI") groups the rows into weeks ending on Friday, and labels each week with that Friday's date. .agg({...}) applies a different rule to each column. Compare the first weekly bar with the daily table earlier in this lesson: its open, 99.95, is Monday's open; its high, 101.94, is Friday's high, the largest of the week; its low, 98.51, is Wednesday's; and its close, 98.96, is Friday's close.

Other rules work the same way. "ME" groups by calendar month, labelled with the month's last day, which you will use in the exercise.

What you have learned

You can now hold a year of price data in one table, select any part of it by column, date or condition, and convert it to a higher timeframe correctly. The next lesson does arithmetic on these columns: returns, moving averages, and a first signal.