Concepts

1. Zones

A zone is a market bidding area. Pass it via the zone argument of any function; default is "ES".

Commodity prices (Brent, TTF, EUA, metals) are global and ignore the zone argument.

2. Timezone (tz)

Three modes for interpreting date/hour arguments and labelling range output:

• 0 /"madrid" (default)— DST-aware Madrid civil time. Spring DST day has 23 hours, fall DST day has 25. Matches what users see on a Spanish wall clock.
• 1 /"cet"— Fixed UTC+1, never shifts. Always 24 hours/day → 8760 hourly rows per year (8784 leap). Use this for consistent time-series modelling.
• 2 /"utc"— UTC. Useful when comparing across European zones or feeding into UTC-based models.

3. Aggregation codes

Range functions take an agg argument that controls how raw rows roll up.

Capture, generation-per-tech, and demand metrics use weighted aggregation where it makes sense (e.g. capture rolls up asΣ(price × MW) / Σ(MW), not a flat mean).

4. Capture: realized vs PBF

ED.CAPTURE andED.CAPTURE.RANGE support two computation methods via the method argument.

On the current day before realized data lands, the API silently falls back from realized to PBF for ES so that=ED.CAPTURE(1, TODAY())doesn't return #N/A.

5. How DST is handled

Storage uses both a Madrid-civil string (ts) and an unambiguous UTC instant (ts_utc). The primary key is on ts_utc, so fall-DST 02:XX has two distinct rows (CEST and CET) — neither is dropped.

• Spring DST (last Sunday of March): wall clock jumps from 02:00 CET to 03:00 CEST. The 02:XX hour does not exist; our parsers correctly skip it. Madrid range queries return 23 hourly rows; CET-fixed return 24.
• Fall DST (last Sunday of October): wall clock jumps back from 03:00 CEST to 02:00 CET. The 02:XX hour happens twice; both are stored with distinct UTC instants. Madrid hourly queries average them by default; CET-fixed sees them as two separate UTC hours.
• For repeatable historical analysis we recommend tz="cet" ortz="utc" — they always give you exactly 8760 (or 8784 leap) hourly rows per year.

6. Stock vs flow — why capacity aggregates differently

Generation and price are flows: they accumulate over time. Summing 24 hourly values gives you the daily total in MWh. Installed capacity is a stock: at any instant there are X MW connected to the grid. You can't sum 12 monthly snapshots to get an "annual capacity" — that would be meaningless.

For ED.CAPACITY.RANGE, the agg argument collapses monthly snapshots into longer buckets, and mode picks how:

Sub-monthly aggregations (agg=0/1/2) are rejected for capacity — the source data is monthly snapshots. Use ED.CAPACITY for the latest scalar.

7. System cost — ESIOS indicator map

Each system_cost_* metric is sourced from a specific ESIOS indicator. Most are published as monthly EUR/MWh-of-demand values (~2 month lag from REE); two were silently deprecated by REE and we fall back to their hourly equivalents, aggregated monthly via agg=3.

Sum of all 13 active components (RDL 10/2022 ones are 0 post-Feb-2023) ≈ the official ESIOS final-price report ±1 EUR/MWh. For the residential bill view (PVPC), use the parallel pvpc_* family instead.

8. Government renewable PPAs — RRE vs REER

Spain has two distinct government-backed PPA mechanismsrunning in parallel and the acronyms collide in everyday conversation. Read this once and you'll never confuse them again.

What we expose for RRE (RD 413/2014)

Source: C2 ZIP file comppfre (downloaded monthly from ESIOS archive 8). Three numbers per technology bucket:

• renewable_settled_price_<tech> — effective €/MWh actually earned by the technology (mixes RRE plants + market plants)
• renewable_settled_rre_adj_<tech> — RRE clawback / make-whole per MWh. Negative = RRE plants pay back to the system. Most active 2021-2022 (-2 to -5 €/MWh); near-zero from 2023
• renewable_settled_energy_<tech> — total settled MWh for that month (all regimes combined)

Tech keys: eolica · fotovoltaica · hidraulica · termosolar · termica_renov · termica_no_renov. Non-renewable thermal is included as a market baseline (those plants don't participate in RRE). Full breakdown (gross revenue, market price, individual adjustments) lives in the renewable_settlement_monthly table.

What we expose for REER (RD 960/2020)

Per-project auction results — every Unidad de Adjudicación awarded across the four REER auctions (Jan 2021, Oct 2021, Oct 2022, Nov 2022). Scraped from the BOE resolution HTML.

• reer_auction_awards table — 219 projects, 6,345 MW total. Columns: company, CIF, technology, subgrupo, UA code, strike €/MWh, awarded MW, BOE ref
• =ED.REER.AWARDS() Excel function with filters by auction / tech / company
• reer_component / _cor / _lib — ESIOS 2402/2403/2404, the QH make-whole price. All zeros so far (no auction year has triggered the floor yet)

Hourly settled generation per renewable bucket (since 2022)

Source: C2 ZIP files grpresol (solar), grpresfh (hydro+biomass),grpreolr (wind+cogen). Settlement-grade measured generation, distinct from real-time gen_*.

• renewable_settled_hourly_solar (FV + termosolar)
• renewable_settled_hourly_hydro_bio (hidráulica + biomasa/biogás)
• renewable_settled_hourly_wind_cogen (eólica + cogeneración/residuos)

What is NOT exposed

• Per-project monthly liquidation (which RRE plant earns what each month) — CNMC publishes only aggregate by technology. Per-installation data is in ERIDE behind a digital-certificate login
• Operational status of REER projects — MITECO publishes adjudicación and preasignación in BOE, but the "state of operación" transition is only in ERIDE
• Mapping award → operational UP — REER awards are SPV-level, the operational UP often has a different name. Fuzzy-matching covers ~10% of awards (mostly large utilities like Engie, Iberdrola)

9. T.Real vs Medida — real-time vs settled generation

ESIOS publishes most generation indicators in two parallel series. Both describe the same physical quantity but at different stages of the settlement pipeline:

Most of our gen_* metrics ride on the T.Real series — they update within minutes of generation. Thegen_total_measuredmetric is the exception: it's the official Medida total (ESIOS id 10043), summed from per-province measurements. The two typically differ by 1-2% because T.Real is preliminary; the gap closes after settlement.

Practical rule: use the virtual gen_total aggregate for live analysis and forecasts; use gen_total_measured for figures that go into reports.

10. How the data pages are cached

Every /data/* page server-renders on each request, but the underlying data fetches go through Vercel's Data Cache with a 30-minute TTL and a shared"data" tag. Identical queries from N visitors within the same window collapse to a single backend hit (~1 DB query instead of N × 29 queries on the System Cost page).

On-demand invalidation: when daily-ingest.tsfinishes its nightly run, it POSTs to /api/revalidatewhich calls revalidateTag("data", "max"). Every cached entry is dropped immediately and the next visitor sees freshly-ingested numbers — no waiting for the 30-min TTL to expire. The "max" profile keeps serving the previous cached value while regeneration runs in the background, avoiding a thundering herd at peak traffic.

The Excel add-in has its own client-side cache (1000 entries, 30 min TTL) so repeated formulas in the same workbook session deduplicate before they ever touch the API. See the changelog for the cache size + TTL bump history.