Calculations › Market Breakdown

Show the average year-to-date % change by exchange

The query

-- barchart market-wide: average YTD % change by exchange (stocks)
WITH latest AS (
  SELECT DISTINCT ON (tp.ticketnameid) tp.ticketnameid, tp.price AS price_now
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'stock'
  ORDER BY tp.ticketnameid, tp.datetime DESC
),
year_start AS (
  SELECT DISTINCT ON (tp.ticketnameid) tp.ticketnameid, tp.price AS price_start
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'stock'
    AND tp.datetime >= EXTRACT(EPOCH FROM DATE_TRUNC('year', NOW()))::bigint
  ORDER BY tp.ticketnameid, tp.datetime ASC
)
SELECT sm.exchange, AVG((l.price_now - y.price_start) / NULLIF(y.price_start,0) * 100) AS avg_ytd_pct_change, COUNT(*) AS n
FROM latest l
JOIN year_start y ON y.ticketnameid = l.ticketnameid
JOIN symbolmetadata sm ON sm.ticketnameid = l.ticketnameid
WHERE sm.exchange IS NOT NULL AND sm.exchange != '' AND y.price_start >= 0.5
GROUP BY sm.exchange
HAVING COUNT(*) >= 3
ORDER BY avg_ytd_pct_change DESC;

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

ExchangeAvg YTD % changeCount
NMS 44.18 32
OSL 25.68 17
TOR 14.93 20
STO 2.47 348
CPH 1.14 3
HEL 0.38 3
NYQ -1.94 34
GER -2.80 9
NCM -23.27 4

Live output, first 9 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