ED.OFFER.HEATMAP
OMIE day-ahead 24h × 12m heatmap of the bid price (or volume) a single tech offered over one year. Companion to ED.OFFER.STRATEGY (4-tier) and ED.OFFER.CURVE (8-bin histogram) — same dataset, different lens (intraday × seasonal).
Signature
ED.OFFER.HEATMAP([tech], [year], [zone], [metric], [headers])Description
Returns a **24×12 heatmap matrix** (hora Madrid × mes Madrid) of the OMIE day-ahead bid prices (or volumes) that a single technology offered over one calendar year. Cell (h, m) is the chosen metric aggregate of all bids with side='V' AND status='O' whose ts_utc AT TIME ZONE 'Europe/Madrid' falls in (hour h, month m) of year for tech × zone.
**Metrics (one per call)**:
- p50_price (default) — median of bid prices in the cell (€/MWh). The price-strategy headline.
- avg_price — mean of bid prices (€/MWh). Sensitive to outliers; p50 is preferred for behavioral analysis.
- mwh_offered — Σ(qty_mw × granularity_min / 60) over the cell (MWh volume offered).
- pct_negative — 100 × (mwh offered at price < 0) / mwh_offered (cannibalisation share).
**Killer insight** (solar Dec 2025 ES, p50_price): h9-12 ≈ **-10 €/MWh** (deep floor — solar gaming the negative tail when irradiance saturates), h19-21 > **50 €/MWh** (intraday scarcity bidding as the sun sets). A single Excel matrix tells both stories; the trio ED.OMIE.BIDS + ED.OFFER.STRATEGY + ED.OFFER.CURVE cannot — they collapse the hour-of-day signature.
**Why a separate function**: STRATEGY/CURVE are *per-bucket-totals*. HEATMAP is *per-(hour, month)*. The intraday axis is what unlocks behavioral insight (which hour does solar floor; which hour does gas safety-bid) and the monthly axis captures seasonality (the floor deepens summer-to-winter, scarcity peaks shift). Pre-aggregated read sub-ms — designed for sheets that pivot tech/year/metric live.
**Tech enum — single sub-tech ONLY** (16 accepted values):
- *Renewables*: solar (default), wind, vre_unspecified, renewable_other, hydro, bombeo, bess, hybrid.
- *Thermal*: nuclear, coal, thermal_gas, thermal_fueloil, biomass, cogen.
- *Other*: autoprod.
- *Special*: interconnect — MIEU/MIP/MIE only; offered side is typically empty in OMIE data so most cells return NULL.
Rejected with 400 (with explanatory hint): all, vre, thermal, placeholder, unknown, representation, venta_generica. Mixing techs per (hour, month) averages opposing strategies and erases the heatmap — this is by design.
Parameters
| Name | Type | Default | Description |
|---|---|---|---|
| tech | string | "solar" | Sub-tech only (e.g. "solar", "wind", "thermal_gas", "nuclear", "hydro"). Aggregates "all"/"vre"/"thermal" are NOT accepted. |
| year | integer 2018..currentYear | last fully-loaded year | Single calendar year. Omit for the last year with data in omie_offer_heatmap_year_tech. |
| zone | "es" | "pt" | "iberian" | "es" | Geographic filter. es → pais IN ('ES','MI'), pt → pais IN ('PT','MI'), iberian → pais IN ('ES','PT','MI'). |
| metric | "p50_price" | "avg_price" | "mwh_offered" | "pct_negative" | "p50_price" | Cell aggregate. p50_price (median bid €/MWh — recommended), avg_price (mean), mwh_offered (Σ volume), pct_negative (% of mwh offered at price<0). |
| headers | 0 | 1 | 0 | Set 1 for a leading header row ["hora","ene",…,"dic"]. |
Returns
Matrix 24×13 — col 0 = hora 0..23 Madrid, cols 1..12 = ene..dic of the chosen metric. 25×13 with headers=1. Empty cells (e.g. tech didn't bid in that (hour, month) — interconnects, rare seasonal techs) return NULL.
Examples
=ED.OFFER.HEATMAP()— Solar p50_price last fully-loaded year ES (all defaults)=ED.OFFER.HEATMAP("solar", 2025, "es", "pct_negative", 1)— Solar % MWh offered at <0 in 2025 ES with headers — the cannibalisation heatmap=ED.OFFER.HEATMAP("wind", 2024, "iberian", "mwh_offered", 1)— Wind volume offered Iberian 2024 — captures the diurnal/seasonal supply curve=ED.OFFER.HEATMAP("nuclear", 2025, "es", "avg_price", 1)— Nuclear mean bid price 2025 ES — the safety-bid floor by hour-of-day=ED.OFFER.HEATMAP("thermal_gas", 2025, "es")— Gas median bid price 2025 ES — peak hours light up vs midday solar valleyNotes
- **Pre-aggregation**: data is read from
omie_offer_heatmap_year_tech(PK(year, zone, tech, hora_madrid, mes_madrid)— ~104k rows total over 8 years × 15 techs × 3 zones × 24 hours × 12 months). Sub-ms read latency. Refresh is **monthly** post-OMIE ingest, NOT daily — OMIE publishes monthly with ~90d lag so the in-flight year only changes ~once/month. - **No
aggparameter**: the matrix is 24h × 12m by construction (one full year). For multi-year analysis call the function once per year and stack the matrices in Excel. - **No
start/end**: filter is by single calendar year. The history cliff is **January 2018** (first month of public OMIE bid-curve data). - **
tech=all/vre/thermalrejected with 400**: mixing techs per (hour, month) would average opposing strategies (solar's midday floor + gas's midday peak) and erase the insight. Pick a single sub-tech. - **DST**: hours are *civil Madrid* (Europe/Madrid). The duplicate 02:XX hour on fall-DST day is collapsed by the underlying
extract(hour from ts_utc AT TIME ZONE 'Europe/Madrid'); spring-DST simply has fewer slots in hour 02. - **Publication lag**: OMIE publishes with ~90-day confidentiality lag. Queries for
year > last_available_yearreturn 404 withlast_available_yearin the body. Years currently in-progress may be incomplete until the next monthly ingest+refresh. - **Metric semantics**:
p50_price(median) is preferred overavg_pricefor behavioral analysis — bidders concentrate volume at canonical price points (0, -10, 180), and the mean is dragged by tail bids.pct_negativeis the cleanest cannibalisation indicator (mwh<0 / mwh_total); cells withmwh_offered = 0return NULL (not 0%). - **Special case
tech=interconnect** (= MIEU/MIP/MIE): offered side is typically empty in OMIE data so most cells return NULL. Provided for symmetry withED.OMIE.BIDS/ED.OFFER.STRATEGY/ED.OFFER.CURVE— informative as a sanity check, not as a strategy claim.