Calculations › Market Discovery

Which currencies moved the most in the last 30 days?

The query

-- table market-wide: biggest currency 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 = 'currency'
  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
MXNUSD=X MXN/USD 0.06 USD -5.88
JPYSEK=X JPY/SEK 0.06 SEK 5.62
NZDUSD=X NZD/USD 0.56 USD -4.85
SEKHKD=X SEK/HKD 0.78 HKD -4.32
USDSEK=X USD/SEK 9.99 SEK 4.29
JPYCAD=X JPY/CAD 0.01 CAD 4.22
JPYEUR=X JPY/EUR 0.01 EUR 4.12
SEKUSD=X SEK/USD 0.10 USD -4.10
CHFUSD=X CHF/USD 1.20 USD -3.27
PLNUSD=X PLN/USD 0.26 USD -3.25
AUDUSD=X AUD/USD 0.69 USD -3.14
NOKUSD=X NOK/USD 0.10 USD -2.71
CADUSD=X CAD/USD 0.70 USD -2.62
DKKUSD=X DKK/USD 0.15 USD -2.49
EURUSD=X EUR/USD 1.13 USD -2.45

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