Skip to content

How to Compute P&L and Net Position

Analytical recipes for the 15m views. All examples use the generic numera-core views; substitute the EWE wrapper name to use the wrapper layer (the column shape is the same — see Migration mapping).

Every recipe assumes a bounded delivery_date_utc filter for partition pruning.

Net position over time

Net = buy − sell. Useful for visualising intraday exposure.

select
    begin_cet,
    sum(volume_buy_mw) - sum(volume_sell_mw) as net_mw
from fct_volume_pwr_15m
where delivery_date_utc = current_date
group by 1
order by 1

For a single grid / business case, add to the WHERE clause and the GROUP BY.

Daily summary by business case

select
    delivery_date_utc,
    business_case,
    sum(volume_buy_mw)                       as total_buy_mw,
    sum(volume_sell_mw)                      as total_sell_mw,
    sum(volume_buy_mw) - sum(volume_sell_mw) as net_position_mw
from fct_volume_pwr_15m
where delivery_date_utc between dateadd('day', -7, current_date) and current_date
group by 1, 2
order by 1 desc, 2

P&L approximation (VWAP-based)

Treats each period's volume × VWAP as the cash flow for that slot. Multiply by 0.25 to convert MW × EUR/MWh to EUR/h (each period is 15 min = 0.25 h).

select
    delivery_date_utc,
    business_case,
    grid,
    sum(volume_sell_mw * sell_vwap_price_eur_per_mwh * 0.25)  as sell_revenue_eur,
    sum(volume_buy_mw  * buy_vwap_price_eur_per_mwh  * 0.25)  as buy_cost_eur,
    sum(volume_sell_mw * sell_vwap_price_eur_per_mwh * 0.25)
      - sum(volume_buy_mw * buy_vwap_price_eur_per_mwh * 0.25) as gross_pnl_eur
from fct_volume_pwr_15m
where delivery_date_utc between dateadd('day', -7, current_date) and current_date
group by 1, 2, 3
order by 1 desc, 2, 3

This is a snapshot, not an accounting P&L

VWAP-based P&L approximates the realised value at the average trade price for each period. It does not reconcile against settlement statements or include MTM components for unsettled positions. Use the dedicated P&L facts (fct_pnl, fct_pnl_15m) for accounting-grade P&L.

Daily VWAP roll-up (correctly weighted)

Naive AVG(buy_vwap_price_eur_per_mwh) over a day double-counts low-volume slots. Re-derive from underlying volumes:

select
    delivery_date_utc,
    business_case,
    grid,
    sum(volume_buy_mw * buy_vwap_price_eur_per_mwh)
        / nullif(sum(volume_buy_mw), 0)  as daily_buy_vwap,
    sum(volume_sell_mw * sell_vwap_price_eur_per_mwh)
        / nullif(sum(volume_sell_mw), 0) as daily_sell_vwap
from fct_volume_pwr_15m
where delivery_date_utc between dateadd('day', -7, current_date) and current_date
group by 1, 2, 3

On the generic views, volume_x_price is volume_mwh × price in EUR. If you use it as the numerator, divide by SUM(volume_buy_mw) / 4 — not by SUM(volume_buy_mw) — because volume_buy_mw = volume_mwh × 4. The form above (weighted by volume_buy_mw) is simpler and avoids the unit trap.

Variance vs index price

Joins fixed and floating views on the same (date, period, business_case, grid) cell. Compares VWAP against the prevailing index price.

select
    fp.delivery_date_utc,
    fp.begin_utc,
    fp.business_case,
    fp.grid,
    fp.volume_buy_mw,
    fp.buy_vwap_price_eur_per_mwh,
    fl.power_index_price_eur_per_mwh,
    fp.buy_vwap_price_eur_per_mwh - fl.power_index_price_eur_per_mwh as price_variance
from fct_volume_pwr_15m fp
left join fct_volume_pwr_floating_15m fl
    on fp.delivery_date_utc = fl.delivery_date_utc
   and fp.begin_utc         = fl.begin_utc
   and fp.business_case     = fl.business_case
   and fp.grid              = fl.grid
where fp.delivery_date_utc between dateadd('day', -7, current_date) and current_date
  and fp.volume_buy_mw > 0
order by 1, 2

The floating view is at BU + index grain; you'll need to aggregate or pick a specific index if more than one applies to your business case.

Top 20 deals by absolute volume

select
    deal_id,
    business_case,
    grid,
    sum(volume_buy_mw + volume_sell_mw) as total_volume_mw,
    avg(buy_vwap_price_eur_per_mwh)     as avg_buy_price,
    avg(sell_vwap_price_eur_per_mwh)    as avg_sell_price
from fct_volume_pwr_15m_deal
where delivery_date_utc = current_date
group by 1, 2, 3
order by total_volume_mw desc
limit 20

Hourly summary

If your downstream tooling can't handle 96 rows per day, roll up to hourly:

select
    delivery_date_utc,
    date_trunc('hour', begin_cet) as hour_cet,
    business_case,
    grid,
    sum(volume_buy_mw)             as buy_mw,
    sum(volume_sell_mw)            as sell_mw,
    sum(volume_buy_mw * buy_vwap_price_eur_per_mwh)
        / nullif(sum(volume_buy_mw), 0)  as buy_vwap
from fct_volume_pwr_15m
where delivery_date_utc = current_date
group by 1, 2, 3, 4
order by 1, 2, 3, 4

Aggregating in CET local time can pull DST-shifted slots into a "wrong" hour for spring-forward / fall-back days. See Time zones and DST for the safe rule.