ED.OFFER.STRATEGY
OMIE day-ahead offer strategy by tech — splits offered volume into 4 price tiers (negative, zero, positive real, safety-bid). Companion to ED.OMIE.BIDS focused on bidding behavior rather than volumes.
Signature
ED.OFFER.STRATEGY([tech], [start], [end], [agg], [zone], [headers])Description
Returns the OMIE day-ahead offer strategy of each technology by splitting the offered volume (side='V', status='O') across 4 price tiers per (bucket, tech):
- mwh_neg — volume offered at **< 0 €/MWh** (plant is willing to pay to clear; must-run, hedge, gaming against the price floor).
- mwh_zero — volume offered at **exactly 0 €/MWh** (exact price-takers: "clear me at whatever").
- mwh_pos_real — volume offered at **0 < p < 180 €/MWh** (competitive real bids).
- mwh_safety — volume offered at **≥ 180 €/MWh** (safety bids near the cap; the plant is not really willing to clear at this price).
Plus the two headline percentages:
- pct_neg — 100 × mwh_neg / total_offered.
- pct_safety — 100 × mwh_safety / total_offered.
And n_slots (count of distinct hourly slots with any offered volume in the bucket × tech).
**Killer insight** (Dec 2025 ES, default range): solar **88% negative** (cannibalised price-taker), gas/nuclear **30-50% safety** (rarely willing to clear above 180€/MWh). Nuclear paradox: ~46% negative (avoiding shutdowns) + ~10% safety (reserve capacity).
**Why this function exists**: ED.OMIE.BIDS returns volumes + clearing rates but doesn't explain *how* each tech bids. ED.OFFER.STRATEGY is the bidding-behavior companion: which tier dominates each tech's offer book over time.
**Tech enum (18 accepted values — identical to ED.OMIE.BIDS)**:
- *Renewables*: solar, wind, vre (= solar+wind+renewable_other+vre_unspecified), hydro, bombeo, bess, hybrid.
- *Thermal*: nuclear, coal, thermal_gas, thermal_fueloil, thermal (alias for thermal_gas + thermal_fueloil), biomass, cogen.
- *Other*: autoprod.
- *Aggregator*: all (default) — every classified real tech, ex interconnects + non-generation labels.
- *Special*: interconnect — INVERTED filter; returns only MIEU/MIP/MIE. Note: MIEU/MIP/MIE have **no offered side at all** (0 status='O' rows in the whole history), so this endpoint returns **no rows** for them and the function answers No data for this range. — not a row of zeros, and not empty percentages.
Rejected with 400 (with explanatory hints): placeholder, unknown, representation, venta_generica, and any other unrecognised string.
**Expansion semantics**: vre/all/thermal cause the output to spill ONE row per sub-tech. Single-tech values keep the literal you asked for as the row label.
Parameters
| Name | Type | Default | Description |
|---|---|---|---|
| tech | string | "all" | Tech filter. Default "all" (= every classified real tech). Same 18-value enum as ED.OMIE.BIDS. |
| start | date | — | YYYY-MM-DD. Omit for end − 1 year. |
| end | date | — | YYYY-MM-DD. Omit for the first day after the last available month (OMIE publishes monthly with ~90-day lag). |
| agg | 1..7 | 6 | 1=hourly, 2=daily, 3=monthly, 4=quarterly, 5=semiannual, 6=annual (default), 7=total. String aliases H/D/M/Q/S/Y/T also accepted. |
| zone | "es" | "pt" | "iberian" | "es" | Geographic filter. es → pais IN ('ES','MI') (default), pt → pais IN ('PT','MI'), iberian → pais IN ('ES','PT','MI') (includes the MIBEL pais='MI' bucket). |
| headers | 0 | 1 | 0 | Set 1 for a header row. |
Returns
Spill matrix — 9 columns: [bucket, tech, mwh_neg, mwh_zero, mwh_pos_real, mwh_safety, pct_neg, pct_safety, n_slots]. One row per (bucket, tech) pair. For agg=7 (total) bucket is the literal string "TOTAL". pct_neg/pct_safety come back **empty** for a bucket with no offered volume to divide by — the contract, not a case seen in the wild: every offered row on record carries qty_mw ≥ 0.1 (0 of 153,250,473), so a bucket that exists has a positive total. A window with no row at all answers No data for this range. in a single cell, header row included — that is what tech=interconnect returns, always.
Examples
=ED.OFFER.STRATEGY()— Last 12 months offer strategy all techs ES, yearly (all defaults)=ED.OFFER.STRATEGY("solar", "2025-12-01", "2025-12-31", 3, "es")— Monthly solar Dec 2025 ES — pct_neg ≈ 88% (the headline cannibalised-solar insight)=ED.OFFER.STRATEGY("thermal_gas", "2025-01-01", "2025-12-31", 6, "es")— Annual thermal_gas 2025 ES — pct_safety typically 30-50%=ED.OFFER.STRATEGY("vre", "2025-01-01", "2025-12-31", 3, "iberian")— Monthly VRE 2025 Iberian (4 sub-techs spilling separately)=ED.OFFER.STRATEGY("nuclear", "2025-01-01", "2025-12-31", 6, "es", 1)— Annual nuclear 2025 ES with header row (paradox: high pct_neg + non-trivial pct_safety)=ED.OFFER.STRATEGY("all", "2024-01-01", "2026-01-01", 6, "es")— Annual all-tech 2024-2025 ES — yearly breakdown for board-deck chartsNotes
- **Tier boundaries are fixed** at
0and180 €/MWh. The< 0and= 0split mirrors the conceptual difference between "willing to pay" (gaming the floor) and "price-taker" (exact zero). The180cap is the canonical safety-bid floor observed in MIBEL CCGT/nuclear behaviour (price-cap is 4,000 €/MWh but ≥180 captures the safety-bid intent). Re-bucket in Excel post-fetch if you need different thresholds. - **Granularity asymmetry (Schema A/B vs C)**: pre-March-2025 OMIE files are 60-min granularity (
granularity_min=60); post-March-2025 are mixed 60-min + 15-min (granularity_min=15for the 15-min product). Theqty_mw * granularity_min / 60.0normalisation is applied uniformly — no double-counting — but the share of slots will appear higher post-March-2025 (4× more 15-min slots per hour). - **History cliff**: OMIE bid-curve dataset starts in **January 2018**. Queries with
start < 2018-01-01will return rows starting at the dataset boundary; bucket aggregates will be partial for the boundary bucket. - **Publication lag**: OMIE publishes monthly bid-curve data with ~90-day confidentiality lag. Queries for too-recent dates return 404 with the last-available month in the body (
last_available_mes). - **Special case
tech=interconnect**: filter is INVERTED — keeps only MIEU/MIP/MIE. This function reads the **offered** side (status='O') and MIEU/MIP/MIE have none: 0 rows in the whole history, in every zone and every year. The endpoint therefore returns **0 rows** and the function answersNo data for this range.— it never spills a row of zeros, and never empty percentages. Accepted for symmetry withED.OMIE.BIDS, where the interconnect bucket does carry cleared volume. - **Expansion semantics**:
vre/all/thermalcause the output to spill ONE row per sub-tech. Single-tech literals keep that literal as the row label. - **Bucket format**:
YYYY-MMfor monthly,YYYY-MM-DDfor daily,YYYY-MM-DD HH:MMfor hourly,YYYY-Qnfor quarterly,YYYY-Hnfor semiannual,YYYYfor annual, literal'TOTAL'foragg=7. - Empty cells: a value the API reports as null spills as an empty cell, never as 0, because the source published no number for it. A 0 in the matrix is measured data, not a gap. =N(cell) reads a blank back as 0 if a formula needs it.