Calculations › Market Discovery

Where does each material sit in its own 52-week range?

The query

-- table market-wide: where each material sits in its own 52-week range
WITH window_prices AS (
  SELECT tp.ticketnameid, tp.price, tp.datetime
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'material'
    AND tp.datetime >= EXTRACT(EPOCH FROM NOW())::bigint - 365*86400
),
bounds AS (
  SELECT ticketnameid, MIN(price) AS low_52w, MAX(price) AS high_52w
  FROM window_prices GROUP BY ticketnameid
),
latest AS (
  SELECT DISTINCT ON (ticketnameid) ticketnameid, price AS price_now
  FROM window_prices ORDER BY ticketnameid, datetime DESC
)
SELECT tn.ticket AS symbol, COALESCE(NULLIF(sm.name, tn.ticket), tn.ticketname, tn.ticket) AS name,
       l.price_now, b.low_52w, b.high_52w,
       (l.price_now - b.low_52w) / NULLIF(b.high_52w - b.low_52w, 0) * 100 AS pct_of_52w_range
FROM latest l
JOIN bounds b ON b.ticketnameid = l.ticketnameid
JOIN ticketname tn ON tn.id = l.ticketnameid
LEFT JOIN symbolmetadata sm ON sm.ticketnameid = l.ticketnameid
ORDER BY pct_of_52w_range ASC NULLS LAST

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

SymbolNamePriceLow 52wHigh 52w% of 52w range
PA=F Palladium Dec 26 1188 1161.30 2169.90 2.65
NG=F Natural Gas Oct 26 2.95 2.52 7.46 8.59
PL=F Platinum Oct 26 1732.40 1504 2852.40 16.94
SI=F Silver Dec 26 61.36 46.56 115.08 21.60
GC=F Gold Dec 26 4205.60 3880.80 5318.40 22.59
KC=F Coffee Dec 26 289.25 244.40 422.70 25.15
CT=F Cotton Mar 26 77.26 61.06 91.70 52.87
CL=F Crude Oil Oct 26 93.02 55.27 112.95 65.45
ZW=F Chicago SRW Wheat Futures,Dec-2 681.50 496.75 767 68.36
CC=F Cocoa Dec 26 5448 2798 6650 68.80
ZC=F Corn Futures,Dec-2026 500.75 402 543 70.04
BZ=F Brent Crude Oil Last Day Financial Futures 102.56 58.92 118.35 73.43
ZS=F Soybean Futures,Nov-2026 1280.75 1006.50 1328 85.30
HG=F Copper Dec 26 6.58 4.85 6.80 88.41
SB=F Sugar #11 Oct 26 18.96 13.31 18.96 100

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