← All work

Case study 02

Market Sentiment Pipeline

A scheduled pipeline that reads the day's news on five large-cap stocks, turns it into a sentiment score, makes a call on the next session, and then grades that call in public once the next close comes in. It started as an Airflow DAG on my laptop. This is the version that keeps running when the laptop is closed.

See it running

The dashboard updates every weekday after the US close. It shows each ticker's latest call and confidence, the headlines the model read and its one-line reason, every past call marked right or wrong, and a log of recent pipeline runs, including how many rows each one actually changed.

Open the live dashboard ↗

What it does

It tracks five tickers: AAPL, GOOGL, MSFT, NVDA and TSLA. At 21:30 UTC every weekday, after the close, a Railway cron job starts, runs python -m pipeline.run, and exits:

  • Extract. Two years of daily bars and the latest headlines per ticker from Yahoo Finance through yfinance, plus the article text wherever the publisher allows it to be fetched.
  • Score. One Claude call per ticker turns that session's headlines into a score from −1 (bearish) to +1 (bullish) and a one-sentence reason.
  • Predict. A Random Forest per ticker, retrained on every run using only sessions whose outcome is already known, calls the next session BUY, SELL or HOLD. It uses RSI, price against the 50-day average, Bollinger width, the day's range, and that session's sentiment. A move beyond ±0.5% counts as up or down.
  • Grade. A SQL view joins every call to the following session's close and marks it right or wrong, using the same ±0.5% rule the model trained on.

Version one, and what rebuilding it turned up

The first version ran from February to March 2026: an Airflow DAG in Docker on my laptop, loading into BigQuery's free tier. It worked, in the sense that the tasks went green. When I rebuilt it to run somewhere other than my laptop, I found three problems that no task had ever failed on:

  • Every load appended. A retry or a manual rerun added the same day's prices, news and sentiment a second time. The predictions table was the only exception. The free tier doesn't allow DELETE, so I deduplicated it by reading the whole table back and rewriting it on every run.
  • The sentiment never reached the model. Price dates were stored as timestamp strings (2026-03-20 00:00:00-04:00) and sentiment dates as plain dates (2026-03-20). The join compared them as text, so a daily price row never matched a score. For six weeks the model was "using sentiment" and its sentiment feature was 0 every day. Nothing errored.
  • Nothing checked the predictions. They were written down and never compared to what happened next.

It also only ran when my laptop was open. Everything below is version two.

The constraint everything else follows from

A job that runs every day will get retried, rerun by hand, and backfilled. If any of those can add a row twice, every number downstream is quietly wrong and nothing tells you. So every write had to be safe to repeat before anything else was worth building.

Architecture

Market sentiment pipeline architecture Yahoo Finance prices and headlines are pulled by a weekday Railway cron job, upserted into Supabase Postgres on natural keys, scored for sentiment by Claude, and turned into a next-session call by a per-ticker Random Forest. A SQL view grades each call against the next close, and a Vercel dashboard reads the results through a cached, read-only endpoint. Yahoo Finance Daily prices OHLCV, 2 years Headlines + article text Railway cron — weekdays 21:30 UTC · runs, then exits Extract yfinance + scrape Upsert only changed rows Score Claude, cached Predict Random Forest keyed on (symbol, date) rerun: 0 rows Supabase Postgres — read-only to the web (row-level security) stock_prices · stock_news market_sentiment predictions · pipeline_runs view prediction_outcomes: every call joined to the next close Vercel dashboard calls · grades · runs local development: the same steps as an Airflow DAG (docker compose)
Watching one daily run…
One job, one database, one page. The dashboard reads the same tables the job writes, through a cached endpoint that can only SELECT. Re-run it: the row count doesn't move, and that's the whole argument of the next section.

Why the upsert matters — try it

This is the bug version one actually had, and the fix is the least glamorous part of the project. Both tables below receive the same day's data. One appends, which is what version one did. The other upserts on (symbol, date). Press Run the same day again, which is exactly what a retry, a backfill or a manual rerun does, and watch them stop agreeing.

Same input, twice

INSERT — append

what version one did

    UPSERT on (symbol, date)

    what it does now

      append rows: 0 upsert rows: 0 phantom rows: 0

      The real version goes one step further: the update only fires when a value actually changed (… DO UPDATE … WHERE old IS DISTINCT FROM new). That turns the row count into evidence. Each run records how many rows it changed. A normal weekday adds the five new price rows, the day's new headlines, five scores and five calls. A rerun of a day that already loaded records exactly 0. You can see that in the run history on the dashboard. The job also re-pulls two years of prices every time, so a late correction or split adjustment from the source overwrites the old value instead of sitting next to it.

      Decisions and what they cost

      DecisionWhyWhat it costs
      Upsert on a natural key, and only when something changed Retries, reruns and backfills become safe, and the row count becomes a real signal instead of a number that only goes up. Every table needs an honest key, which forces you to know the grain of your data. Re-pulling two years of prices means comparing a few thousand rows to store five new ones.
      A cron job in production, not a hosted Airflow The work takes a couple of minutes a day. A Railway cron service starts, runs and exits, so it's billed for those minutes. An Airflow scheduler and web server would sit idle all day and would likely cost more than the Hobby plan's included usage. Production loses Airflow's per-task retries and task graph. A ticker that fails is recorded on the run and shown on the dashboard, but nothing retries it until the next day. The Airflow DAG still exists for local runs and calls the same functions.
      An LLM for sentiment instead of a classical classifier Financial headlines are full of negation and hedging. A bag-of-words model reads "beats lowered expectations" as good news. An external dependency in the middle of the run, and scores that aren't guaranteed to be reproducible. Scores are cached per ticker per session, so a rerun never calls the API again. The dependency is real: the API key type this project started on stopped being accepted partway through, which is why the scorer sits behind one function with a second provider an environment variable away.
      Random Forest over a neural model A small tabular dataset, and I wanted feature importances I could actually read and argue with. Almost certainly leaves accuracy on the table compared with a gradient-boosted or sequence model.
      Grade every call in SQL, next to a baseline Grading is a view over the same tables, so it can't drift from the data. Every hit rate is shown next to what you'd score by always calling the most common outcome, which is the number the model has to beat. It grades direction against a threshold and nothing else. It says nothing about returns, costs or position sizing.
      Supabase with read-only row-level security, behind a cached endpoint Free Postgres, and the dashboard's key can only read the tables it shows. Scraped article text has no read policy, so it never leaves the database. Free projects pause after a week without activity, so the daily job is also what keeps the database awake. The page is cached, so it can be a few minutes behind the data.
      Where it stands, honestly
      • It runs every weekday on Railway and the dashboard is live. The live record only goes back to when this version started running. The older calls on the dashboard come from a walk-forward backtest: the same code, retrained one session at a time on only what was known then, and labelled as backtest everywhere they appear.
      • This is not a trading strategy, and I don't present it as one. It's a data pipeline that happens to produce a signal. There are no transaction costs, no slippage and no position sizing, so I make no claim about profitability. I'm also not quoting a hit rate here, because it changes daily. The dashboard shows the current one next to its baseline.
      • The sentiment feature is mostly zeros in training. There's no archive of historical news to backfill it from, so until months of live runs build up, the model is effectively technical-only. The backtest had no sentiment at all.
      • Yahoo Finance has no SLA, and yfinance can get rate-limited from cloud IPs. The job retries with backoff and records failures per ticker. There's no second data source.
      • There's no alerting. A failed run shows up on the dashboard and in Railway's logs, and nothing notifies me.
      • The tests are an offline suite against a local Postgres that fakes Yahoo and the scoring API and checks that a rerun writes 0 rows and that a price correction changes exactly one row per ticker. They run by hand, not in CI.

      What I'd tell you in an interview

      Ask me about the date join. For six weeks the model was "using sentiment" and never received a single score. Every task was green, because a join that matches nothing isn't an error. That's why this version stores dates as dates, logs what every run actually changed, and grades every call in public. A pipeline that can't show you when it's wrong isn't finished.


      Stack

      • Python
      • pandas
      • scikit-learn
      • Claude API
      • PostgreSQL
      • Supabase
      • Railway
      • Vercel
      • Apache Airflow
      • Docker
      Open the dashboard ↗ ← Previous Next case study →