Oil Price Tracker: Pairing Daily Prices with Market-Moving Events

Author

Robert Frenken

Published

May 19, 2026

Keywords

exploratory data analysis, data visualization, data wrangling, data integration, time series analysis, interactive visualization

Oil market notebook

Oil Price Tracker

Brush the price history with Altair-native linked selections, then inspect market-moving events against a long-running benchmark that now reaches back to 1946.

Objective

This post builds a simple oil-price tracker that joins benchmark prices with a curated event table. The chart shows price history as a line and marks important events on top of it. The top panel gives context over the full history; the bottom panel starts in the mid-1970s and can be zoomed with the built-in Altair brush.

The implementation uses:

Price data: FRED CSV downloads for EIA crude spot series.
Events data: A CSV with date, description, countries, tags, and source.
Join logic: Match each event to the nearest available oil-price observation.
Visualization: Altair/Vega-Lite with hover and click selections.

Methodology

Price-data source

FRED publishes daily EIA crude-oil spot series as downloadable CSV files. Two practical series are:

WTI Cushing, Oklahoma spot price: DCOILWTICO
Brent Europe spot price: DCOILBRENTEU

The CSV endpoint pattern is:

https://fred.stlouisfed.org/graph/fredgraph.csv?id=<SERIES_ID>

This example defaults to FRED’s spliced WTI series, which extends the monthly history back to 1946. If you want a modern-only daily series, you can switch to Brent or daily WTI.

How the event set is chosen

The event list is curated, not exhaustive. I include an event when it has a plausible path to oil prices and enough documentation to explain the move. In practice that means:

a direct supply, demand, or geopolitics channel to crude prices
a recognizable market reaction or a credible expectation of one
a source I can point to, usually Reuters, EIA, or another primary market/history source
a date I can pin down to the announcement or shock day

That keeps the chart focused on episodes that actually matter for price formation instead of every news item that happened to mention oil.

Event-data structure

The event schema is:

field	type	meaning
`date`	date	Event timestamp, usually announcement date or shock date
`description`	string	Plain-English event summary
`countries`	string	Country or countries most directly involved
`tags`	string	Semicolon-separated event tags for filtering
`source`	string	Optional URL or source note

In this post, the table lives in events.csv so the workflow stays reproducible. The tags field is kept as metadata for later filtering or expansion.

Setup

Code

import html as html_lib
import json
import pandas as pd
import altair as alt
from IPython.display import HTML, display

alt.data_transformers.disable_max_rows()

DataTransformerRegistry.enable('default')

Setup notes

The post only needs:

pip install pandas altair ipython

The interactive shell below uses a small custom JavaScript helper for the popup and window/tag controls. The brush itself is native Altair.

Load benchmark prices

Code

BENCHMARKS = {
    "WTI monthly (spliced)": {
        "series_id": "WTISPLC",
        "price_label": "Spliced WTI spot price",
    },
    "Brent daily": {
        "series_id": "DCOILBRENTEU",
        "price_label": "Brent crude spot price",
    },
    "WTI daily": {
        "series_id": "DCOILWTICO",
        "price_label": "WTI Cushing crude spot price",
    },
}

BENCHMARK = "WTI monthly (spliced)"  # Switch to a daily series if you only want the modern period.
SERIES_ID = BENCHMARKS[BENCHMARK]["series_id"]
PRICE_LABEL = BENCHMARKS[BENCHMARK]["price_label"]

url = f"https://fred.stlouisfed.org/graph/fredgraph.csv?id={SERIES_ID}"
prices = pd.read_csv(url)

prices = prices.rename(columns={"observation_date": "date", SERIES_ID: "price_usd_per_barrel"})
prices["date"] = pd.to_datetime(prices["date"])
prices["price_usd_per_barrel"] = pd.to_numeric(prices["price_usd_per_barrel"], errors="coerce")
prices = prices.dropna(subset=["price_usd_per_barrel"]).sort_values("date")

prices.tail()

	date	price_usd_per_barrel
959	2025-12-01	57.97
960	2026-01-01	60.04
961	2026-02-01	64.51
962	2026-03-01	91.38
963	2026-04-01	100.32

Create an event table

The event table lives in oil_price_tracker_events.csv.

Code

events = pd.read_csv("oil_price_tracker_events.csv", parse_dates=["date"])
events["tags"] = events["tags"].fillna("")

Join events to oil prices

Oil-price series skip weekends and holidays, so the join attaches the nearest available oil price to each event. If the nearest point is the next trading day, the event uses that instead of the previous one.

Code

prices_for_join = (
    prices[["date", "price_usd_per_barrel"]]
    .rename(columns={"date": "price_date"})
    .sort_values("price_date")
)
events_for_join = (
    events.rename(columns={"date": "event_date"})
    .sort_values("event_date")
)

previous_price = pd.merge_asof(
    events_for_join,
    prices_for_join,
    left_on="event_date",
    right_on="price_date",
    direction="backward",
)
next_price = pd.merge_asof(
    events_for_join,
    prices_for_join,
    left_on="event_date",
    right_on="price_date",
    direction="forward",
)

previous_gap = (events_for_join["event_date"] - previous_price["price_date"]).abs()
next_gap = (next_price["price_date"] - events_for_join["event_date"]).abs()

use_next = next_price["price_date"].notna() & (
    previous_price["price_date"].isna() | (next_gap < previous_gap)
)

events_joined = previous_price.copy()
events_joined.loc[use_next, ["price_date", "price_usd_per_barrel"]] = next_price.loc[
    use_next, ["price_date", "price_usd_per_barrel"]
].to_numpy()
events_joined["days_from_price_date"] = (
    events_joined["event_date"] - events_joined["price_date"]
).dt.days.abs()
events_joined["event"] = True
events_joined["date"] = events_joined["price_date"]
events_joined = events_joined.drop(columns=["price_date"])

Build the chart

The interactive chart now lives in a standalone widget so Quarto only embeds it instead of executing the chart build inline.

Notes on front-end lifecycle

The iframe keeps the chart lifecycle isolated from the Quarto page, which avoids notebook execution issues and keeps the sidebar and TOC intact. The widget itself still handles hover, brush, filtering, and popup behavior.

Reusable event CSV template

date,description,countries,tags,source
2022-02-24,"Russia launched its full-scale invasion of Ukraine; oil prices jumped on supply-risk concerns.","Russia / Ukraine / Global","Russia-Ukraine;war;sanctions",https://www.reuters.com/business/energy/oil-rises-us-says-russian-attack-ukraine-may-occur-soon-2022-02-24/
2023-04-02,"OPEC+ producers announced surprise voluntary output cuts of about 1.16 million barrels per day.","Saudi Arabia / OPEC+","OPEC+;supply",https://www.reuters.com/business/energy/sarabia-other-opec-producers-announce-voluntary-oil-output-cuts-2023-04-02/
2026-02-28,"U.S. and Israeli strikes on Iran escalated the conflict and tightened oil supply expectations.","United States / Iran / Israel / Middle East","US-Iran;Middle East;war;Hormuz",https://www.brecorder.com/news/40415137/oil-prices-rise-as-hormuz-stays-shut-ahead-of-trump-deadline-strikes-on-iran-intensify
2026-04-16,"U.S. officials signaled the military was ready to strike Iran's energy infrastructure if ordered.","United States / Iran","US-Iran;Middle East;war;Hormuz",https://www.investing.com/news/commodities-news/us-forces-ready-to-restart-combat-if-iran-doesnt-agree-a-deal-says-hegseth-4618091
2026-04-20,"Oil jumped as ceasefire hopes faded and violence around the Strait of Hormuz flared again.","United States / Iran / Middle East","US-Iran;Middle East;war;Hormuz",https://ca.investing.com/news/commodities-news/oil-prices-rebound-7-as-strait-of-hormuz-is-closed-again-4572835

--- title: "Oil Price Tracker: Pairing Daily Prices with Market-Moving Events" author: "Robert Frenken" date: today keywords: - exploratory data analysis - data visualization - data wrangling - data integration - time series analysis - interactive visualization format: html: toc: true code-fold: show code-tools: true execute: warning: false message: false jupyter: python3 --- <div class="oil-hero"> <div class="oil-hero-kicker">Oil market notebook</div> <h2>Oil Price Tracker</h2> <p>Brush the price history with Altair-native linked selections, then inspect market-moving events against a long-running benchmark that now reaches back to 1946.</p> </div> ## Objective This post builds a simple oil-price tracker that joins benchmark prices with a curated event table. The chart shows price history as a line and marks important events on top of it. The top panel gives context over the full history; the bottom panel starts in the mid-1970s and can be zoomed with the built-in Altair brush. The implementation uses: - **Price data:** FRED CSV downloads for EIA crude spot series. - **Events data:** A CSV with `date`, `description`, `countries`, `tags`, and `source`. - **Join logic:** Match each event to the nearest available oil-price observation. - **Visualization:** Altair/Vega-Lite with hover and click selections. ## Methodology ### Price-data source FRED publishes daily EIA crude-oil spot series as downloadable CSV files. Two practical series are: - WTI Cushing, Oklahoma spot price: `DCOILWTICO` - Brent Europe spot price: `DCOILBRENTEU` The CSV endpoint pattern is: ```text https://fred.stlouisfed.org/graph/fredgraph.csv?id=<SERIES_ID> ``` This example defaults to FRED's spliced WTI series, which extends the monthly history back to 1946. If you want a modern-only daily series, you can switch to Brent or daily WTI. ### How the event set is chosen The event list is curated, not exhaustive. I include an event when it has a plausible path to oil prices and enough documentation to explain the move. In practice that means: - a direct supply, demand, or geopolitics channel to crude prices - a recognizable market reaction or a credible expectation of one - a source I can point to, usually Reuters, EIA, or another primary market/history source - a date I can pin down to the announcement or shock day That keeps the chart focused on episodes that actually matter for price formation instead of every news item that happened to mention oil. ### Event-data structure The event schema is: | field | type | meaning | |---|---:|---| | `date` | date | Event timestamp, usually announcement date or shock date | | `description` | string | Plain-English event summary | | `countries` | string | Country or countries most directly involved | | `tags` | string | Semicolon-separated event tags for filtering | | `source` | string | Optional URL or source note | In this post, the table lives in `events.csv` so the workflow stays reproducible. The `tags` field is kept as metadata for later filtering or expansion. ## Setup ```{python} #| label: imports import html as html_lib import json import pandas as pd import altair as alt from IPython.display import HTML, display alt.data_transformers.disable_max_rows() ``` ## Setup notes The post only needs: ```bash pip install pandas altair ipython ``` The interactive shell below uses a small custom JavaScript helper for the popup and window/tag controls. The brush itself is native Altair. ## Load benchmark prices ```{python} #| label: load-prices BENCHMARKS = { "WTI monthly (spliced)": { "series_id": "WTISPLC", "price_label": "Spliced WTI spot price", }, "Brent daily": { "series_id": "DCOILBRENTEU", "price_label": "Brent crude spot price", }, "WTI daily": { "series_id": "DCOILWTICO", "price_label": "WTI Cushing crude spot price", }, } BENCHMARK = "WTI monthly (spliced)" # Switch to a daily series if you only want the modern period. SERIES_ID = BENCHMARKS[BENCHMARK]["series_id"] PRICE_LABEL = BENCHMARKS[BENCHMARK]["price_label"] url = f"https://fred.stlouisfed.org/graph/fredgraph.csv?id={SERIES_ID}" prices = pd.read_csv(url) prices = prices.rename(columns={"observation_date": "date", SERIES_ID: "price_usd_per_barrel"}) prices["date"] = pd.to_datetime(prices["date"]) prices["price_usd_per_barrel"] = pd.to_numeric(prices["price_usd_per_barrel"], errors="coerce") prices = prices.dropna(subset=["price_usd_per_barrel"]).sort_values("date") prices.tail() ``` ## Create an event table The event table lives in `oil_price_tracker_events.csv`. ```{python} #| label: load-events events = pd.read_csv("oil_price_tracker_events.csv", parse_dates=["date"]) events["tags"] = events["tags"].fillna("") ``` ## Join events to oil prices Oil-price series skip weekends and holidays, so the join attaches the nearest available oil price to each event. If the nearest point is the next trading day, the event uses that instead of the previous one. ```{python} #| label: join-events prices_for_join = ( prices[["date", "price_usd_per_barrel"]] .rename(columns={"date": "price_date"}) .sort_values("price_date") ) events_for_join = ( events.rename(columns={"date": "event_date"}) .sort_values("event_date") ) previous_price = pd.merge_asof( events_for_join, prices_for_join, left_on="event_date", right_on="price_date", direction="backward", ) next_price = pd.merge_asof( events_for_join, prices_for_join, left_on="event_date", right_on="price_date", direction="forward", ) previous_gap = (events_for_join["event_date"] - previous_price["price_date"]).abs() next_gap = (next_price["price_date"] - events_for_join["event_date"]).abs() use_next = next_price["price_date"].notna() & ( previous_price["price_date"].isna() | (next_gap < previous_gap) ) events_joined = previous_price.copy() events_joined.loc[use_next, ["price_date", "price_usd_per_barrel"]] = next_price.loc[ use_next, ["price_date", "price_usd_per_barrel"] ].to_numpy() events_joined["days_from_price_date"] = ( events_joined["event_date"] - events_joined["price_date"] ).dt.days.abs() events_joined["event"] = True events_joined["date"] = events_joined["price_date"] events_joined = events_joined.drop(columns=["price_date"]) ``` ## Build the chart The interactive chart now lives in a standalone widget so Quarto only embeds it instead of executing the chart build inline. <iframe src="../_static/oil_price_tracker_widget.html" title="Oil Price Tracker widget" style="width: 100%; min-height: 1120px; border: 0; overflow: hidden;" loading="eager" ></iframe> ## Notes on front-end lifecycle The iframe keeps the chart lifecycle isolated from the Quarto page, which avoids notebook execution issues and keeps the sidebar and TOC intact. The widget itself still handles hover, brush, filtering, and popup behavior. ## Reusable event CSV template ```csv date,description,countries,tags,source 2022-02-24,"Russia launched its full-scale invasion of Ukraine; oil prices jumped on supply-risk concerns.","Russia / Ukraine / Global","Russia-Ukraine;war;sanctions",https://www.reuters.com/business/energy/oil-rises-us-says-russian-attack-ukraine-may-occur-soon-2022-02-24/ 2023-04-02,"OPEC+ producers announced surprise voluntary output cuts of about 1.16 million barrels per day.","Saudi Arabia / OPEC+","OPEC+;supply",https://www.reuters.com/business/energy/sarabia-other-opec-producers-announce-voluntary-oil-output-cuts-2023-04-02/ 2026-02-28,"U.S. and Israeli strikes on Iran escalated the conflict and tightened oil supply expectations.","United States / Iran / Israel / Middle East","US-Iran;Middle East;war;Hormuz",https://www.brecorder.com/news/40415137/oil-prices-rise-as-hormuz-stays-shut-ahead-of-trump-deadline-strikes-on-iran-intensify 2026-04-16,"U.S. officials signaled the military was ready to strike Iran's energy infrastructure if ordered.","United States / Iran","US-Iran;Middle East;war;Hormuz",https://www.investing.com/news/commodities-news/us-forces-ready-to-restart-combat-if-iran-doesnt-agree-a-deal-says-hegseth-4618091 2026-04-20,"Oil jumped as ceasefire hopes faded and violence around the Strait of Hormuz flared again.","United States / Iran / Middle East","US-Iran;Middle East;war;Hormuz",https://ca.investing.com/news/commodities-news/oil-prices-rebound-7-as-strait-of-hormuz-is-closed-again-4572835 ```