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 1215 1161.30 2169.90 5.32
NG=F Natural Gas Oct 26 3.01 2.52 7.46 9.88
PL=F Platinum Oct 26 1722.80 1504 2852.40 16.23
SI=F Silver Dec 26 60.78 46 115.08 21.40
GC=F Gold Dec 26 4189.20 3839.70 5318.40 23.64
KC=F Coffee Dec 26 289.90 244.40 422.70 25.52
CT=F Cotton Mar 26 79.33 61.06 91.70 59.63
CL=F Crude Oil Oct 26 90.36 55.27 112.95 60.84
CC=F Cocoa Dec 26 5338 2798 6675 65.51
BZ=F Brent Crude Oil Last Day Financial Futures 97.92 58.92 118.35 65.62
ZW=F Chicago SRW Wheat Futures,Dec-2 675.25 496.75 767 66.05
ZC=F Corn Futures,Dec-2026 501.25 402 543 70.39
ZS=F Soybean Futures,Nov-2026 1294 1006.50 1328 89.42
HG=F Copper Dec 26 6.64 4.83 6.80 91.81
SB=F Sugar #11 Oct 26 18.62 13.31 18.73 97.97

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