Calculations › Market Discovery

Which materials and commodities moved the most in the last 30 days?

The query

-- table market-wide: biggest material moves over 30 days
WITH latest AS (
  SELECT DISTINCT ON (tp.ticketnameid) tp.ticketnameid, tp.price AS price_now, tp.datetime AS ts_now
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'material'
  ORDER BY tp.ticketnameid, tp.datetime DESC
),
earlier AS (
  SELECT DISTINCT ON (tp2.ticketnameid) tp2.ticketnameid, tp2.price AS price_then
  FROM ticketprice tp2 JOIN latest l ON l.ticketnameid = tp2.ticketnameid
  WHERE tp2.datetime <= l.ts_now - 30*86400
  ORDER BY tp2.ticketnameid, tp2.datetime DESC
)
SELECT tn.ticket AS symbol, COALESCE(NULLIF(sm.name, tn.ticket), tn.ticketname, tn.ticket) AS name,
       l.price_now, sm.currency,
       (l.price_now - e.price_then) / NULLIF(e.price_then,0) * 100 AS pct_change
FROM latest l
JOIN earlier e ON e.ticketnameid = l.ticketnameid
JOIN ticketname tn ON tn.id = l.ticketnameid
LEFT JOIN symbolmetadata sm ON sm.ticketnameid = l.ticketnameid
ORDER BY abs((l.price_now - e.price_then) / NULLIF(e.price_then,0)) DESC
LIMIT 25

Runs against Cryptobi's market tables — ticketname (symbols), ticketprice (daily prices and volume) and symbolmetadata (sector, exchange, market cap and other reference fields).

Current output

SymbolNamePriceCurrency% change
CC=F Cocoa Dec 26 5338 USD -19.73
LBS=F Random Length Lumber Futures 344 USD -17.96
KC=F Coffee Dec 26 289.90 USX -15.70
CT=F Cotton Mar 26 79.33 USX -13.49
PA=F Palladium Dec 26 1215 USD -10.85
ZW=F Chicago SRW Wheat Futures,Dec-2 675.25 USX -10.74
SI=F Silver Dec 26 60.78 USD -8.22
BZ=F Brent Crude Oil Last Day Financial Futures 97.92 USD 8.21
GC=F Gold Dec 26 4189.20 USD -5.46
CL=F Crude Oil Oct 26 90.36 USD 5.36
SB=F Sugar #11 Oct 26 18.62 USX 4.55
PL=F Platinum Oct 26 1722.80 USD -3.67
ZC=F Corn Futures,Dec-2026 501.25 USX -2.67
NG=F Natural Gas Oct 26 3.01 USD 2.59
ZS=F Soybean Futures,Nov-2026 1294 USX 1.47

Live output, first 15 rows, refreshed periodically. Model estimates are trend projections from recent prices, not financial advice.

Run this on your own holdings, change it, or write your own in SQL or Python — then put it on a dashboard or have it emailed on a schedule. Free, no credit card.

Sign Up Free

Related calculations