Price Types & Commodity Pricing

This guide explains how commodities are priced in energy trading deals and how to analyze pricing exposure in the EWE Numera platform.

Overview

Energy trading deals can be priced in different ways: - Fixed pricing: Price is set at deal execution - Floating pricing: Price linked to market indices - Hybrid pricing: Combination of fixed and floating components

Understanding pricing types is critical for: - Risk management (exposure to price movements) - P&L analysis (how deals will perform) - Hedging strategies (offsetting price risk)

---

Price Types

Fixed Price

A fixed price deal has a predetermined price that doesn't change with market movements.

Characteristics: - Price set at deal execution - No exposure to market index movements - Full certainty on settlement amount - Often used for hedging or long-term supply

Example: Sell 100 MWh of gas at €25/MWh for January delivery.

Query to find fixed price deals:

SELECT
    d.deal_id,
    d.portfolio,
    dp.commodity,
    dp.price_type,
    dp.price
FROM dim_deal_attribute d
JOIN dim_deal_physical dp
    ON d.dim_deal_attribute_id = dp.dim_deal_attribute_id
WHERE dp.price_type = 'Fixed'
  AND d.tran_status IN ('Validated', 'Matured')
ORDER BY d.portfolio, dp.commodity

Floating Price

A floating price deal is linked to a market index. The final settlement price is determined by the index value at a future date.

Characteristics: - Price determined by reference index - Exposure to market price movements - May include spread or percentage adjustment - Common for short-term and speculative trades

Example: Buy gas at TTF Month-Ahead + €0.50/MWh for January delivery.

Common Indices: - TTF (Title Transfer Facility): Dutch gas hub, European gas benchmark - NBP (National Balancing Point): UK gas hub - EEX (European Energy Exchange): Power exchange - EPEX Spot: European power spot market - ICE: Intercontinental Exchange futures

Query to find floating price deals:

SELECT
    d.deal_id,
    d.portfolio,
    dp.commodity,
    dp.price_type,
    dp.proj_index,
    dp.index_percent,
    dp.index_spread
FROM dim_deal_attribute d
JOIN dim_deal_physical dp
    ON d.dim_deal_attribute_id = dp.dim_deal_attribute_id
WHERE dp.price_type = 'Floating'
  AND d.tran_status IN ('Validated', 'Matured')
ORDER BY d.portfolio, dp.proj_index

Hybrid Pricing

Some deals combine fixed and floating elements.

Example: Gas priced at 50% of TTF + €15/MWh

---

Pricing Formula Components

When analyzing deal pricing, several components come into play:

Component	Description	Example
Base Price	Fixed price component	€20/MWh
Index	Reference market price	TTF Month-Ahead
Index Percent	Percentage of index to use	100% (full index exposure)
Index Spread	Fixed adjustment to index	+€0.50/MWh
Reference Source	Data source for index	ICE, EEX, etc.

Formula Examples:

1. Pure Fixed: Settlement Price = Base Price
2. Pure Floating: Settlement Price = Index Price + Spread
3. Percentage Index: Settlement Price = (Index Price × Percentage) + Spread
4. Hybrid: Settlement Price = Base Price + (Index Price × Percentage)

---

Analyzing Deal Pricing Details

Get Pricing Formula for a Deal

The fct_deal_commodity_pricing table contains detailed pricing information for each deal side.

SELECT
    deal_id,
    side_id,
    pricing_formula,
    price AS base_price,
    index_percent,
    index_spread,
    proj_index_name,
    ref_source,
    start_date,
    end_date
FROM fct_deal_commodity_pricing
WHERE deal_id = 12345  -- Replace with your deal ID
ORDER BY side_id, start_date

Key Fields: - pricing_formula: Text description of pricing method - price: Base/fixed price component - index_percent: Percentage of index (100 = 100% exposure) - index_spread: Additive spread to index - proj_index_name: Name of the projection index - ref_source: Market data source - start_date, end_date: Date range for this pricing

Index-Linked Pricing Details

For deals with index-linked pricing, you can see both the volume exposure and the associated index:

SELECT
    f.delivery_date_utc,
    d.portfolio,
    dp.proj_index,
    dp.index_percent,
    dp.index_spread,
    SUM(f.volume_mwh) AS total_volume,
    COUNT(DISTINCT d.deal_id) AS deal_count
FROM fct_volume f
JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
JOIN dim_deal_physical dp
    ON d.dim_deal_attribute_id = dp.dim_deal_attribute_id
WHERE dp.price_type = 'Floating'
  AND f.delivery_date >= CURRENT_DATE
  AND f.delivery_date < CURRENT_DATE + 30
GROUP BY f.delivery_date_utc, d.portfolio, dp.proj_index, dp.index_percent, dp.index_spread
ORDER BY f.delivery_date_utc, d.portfolio

---

Fixed vs Floating Exposure Analysis

Portfolio Exposure by Price Type

Understanding your portfolio's exposure to fixed vs floating pricing is critical for risk management.

SELECT
    d.portfolio,
    dp.price_type,
    dp.commodity,
    SUM(CASE WHEN d.buy_sell = 'Buy' THEN f.volume_mwh ELSE 0 END) AS buy_volume,
    SUM(CASE WHEN d.buy_sell = 'Sell' THEN f.volume_mwh ELSE 0 END) AS sell_volume,
    SUM(CASE WHEN d.buy_sell = 'Buy' THEN f.volume_mwh
             ELSE -f.volume_mwh END) AS net_volume
FROM fct_volume_daily f
JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
JOIN dim_deal_physical dp
    ON d.dim_deal_attribute_id = dp.dim_deal_attribute_id
WHERE f.business_date_local >= CURRENT_DATE
  AND f.business_date_local < CURRENT_DATE + 30
  AND d.tran_status IN ('Validated', 'Matured')
GROUP BY d.portfolio, dp.price_type, dp.commodity
ORDER BY d.portfolio, dp.price_type, dp.commodity

Exposure to Specific Indices

Identify concentration risk to particular market indices:

-- Exposure to TTF
SELECT
    d.portfolio,
    d.buy_sell,
    dp.proj_index,
    SUM(f.volume_mwh) AS total_volume,
    COUNT(DISTINCT d.deal_id) AS deal_count
FROM fct_volume_daily f
JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
JOIN dim_deal_physical dp
    ON d.dim_deal_attribute_id = dp.dim_deal_attribute_id
WHERE dp.proj_index LIKE '%TTF%'
  AND f.business_date_local >= CURRENT_DATE
  AND d.tran_status IN ('Validated', 'Matured')
GROUP BY d.portfolio, d.buy_sell, dp.proj_index
ORDER BY d.portfolio, total_volume DESC

Net Fixed vs Floating Position

Calculate your net position split between fixed and floating pricing:

SELECT
    d.portfolio,
    dp.commodity,
    SUM(CASE
        WHEN dp.price_type = 'Fixed' AND d.buy_sell = 'Buy'
        THEN f.volume_mwh
        WHEN dp.price_type = 'Fixed' AND d.buy_sell = 'Sell'
        THEN -f.volume_mwh
        ELSE 0
    END) AS net_fixed_volume,
    SUM(CASE
        WHEN dp.price_type = 'Floating' AND d.buy_sell = 'Buy'
        THEN f.volume_mwh
        WHEN dp.price_type = 'Floating' AND d.buy_sell = 'Sell'
        THEN -f.volume_mwh
        ELSE 0
    END) AS net_floating_volume
FROM fct_volume_daily f
JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
JOIN dim_deal_physical dp
    ON d.dim_deal_attribute_id = dp.dim_deal_attribute_id
WHERE f.business_date_local >= DATE_TRUNC('month', CURRENT_DATE)
  AND f.business_date_local < DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE))
  AND d.tran_status IN ('Validated', 'Matured')
GROUP BY d.portfolio, dp.commodity
ORDER BY d.portfolio, dp.commodity

---

Power Index Pricing (PWR Index)

EWE has specific models for power trades linked to spot indices (instrument type 'PWR Index').

15-Minute Index-Linked Volumes

For index-linked power trades, you can analyze volumes alongside the actual index prices:

SELECT
    delivery_date_utc,
    business_case,
    power_index_name,
    SUM(volume_buy_mw) AS total_buy_mw,
    SUM(volume_sell_mw) AS total_sell_mw,
    AVG(power_index_price_eur_per_mwh) AS avg_index_price,
    SUM(volume_buy_mw * power_index_price_eur_per_mwh) / NULLIF(SUM(volume_buy_mw), 0) AS buy_vwap,
    SUM(volume_sell_mw * power_index_price_eur_per_mwh) / NULLIF(SUM(volume_sell_mw), 0) AS sell_vwap
FROM fct_ewe_volume_pwr_floating_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY delivery_date_utc, business_case, power_index_name
ORDER BY business_case

Index Price Exposure by Hour

SELECT
    EXTRACT(HOUR FROM begin_cet) AS hour_of_day,
    power_index_name,
    SUM(volume_buy_mw) AS buy_mw,
    SUM(volume_sell_mw) AS sell_mw,
    SUM(volume_buy_mw) - SUM(volume_sell_mw) AS net_position_mw,
    AVG(power_index_price_eur_per_mwh) AS avg_index_price
FROM fct_ewe_volume_pwr_floating_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY EXTRACT(HOUR FROM begin_cet), power_index_name
ORDER BY hour_of_day, power_index_name

See Power Volume Models for more details on these tables.

---

Common Market Indices

Gas Indices

Index	Full Name	Hub Location	Typical Use
TTF	Title Transfer Facility	Netherlands	European gas benchmark
NBP	National Balancing Point	United Kingdom	UK gas benchmark
GASPOOL	Gaspool	Germany	German gas hub
NCG	NetConnect Germany	Germany	German gas hub
PEG	Point d'Échange de Gaz	France	French gas hub

Power Indices

Index	Full Name	Region	Granularity
EEX	European Energy Exchange	Germany/Europe	Day-ahead, futures
EPEX Spot	European Power Exchange Spot	Multiple EU countries	15-minute, hourly
Phelix	Physical Electricity Index	Germany	Day-ahead baseload/peakload

---

Price Data Sources

Reference Sources

Common market data providers referenced in ref_source: - ICE (Intercontinental Exchange): Futures and OTC markets - EEX: European power and gas - EPEX: European spot power - Platts: Energy price assessments - Argus: Independent energy price assessments

Historical Price Data

(Note: Historical price data models may be added in future releases)

For now, index prices for floating power trades are available in: - fct_ewe_volume_pwr_floating_by_business_case - fct_ewe_volume_pwr_floating_by_business_case_deal

---

Risk Management Use Cases

Hedge Effectiveness

Compare fixed and floating positions to assess natural hedges:

-- Portfolio hedged if fixed buy ≈ floating sell (or vice versa)
SELECT
    d.portfolio,
    dp.commodity,
    SUM(CASE
        WHEN dp.price_type = 'Fixed' AND d.buy_sell = 'Buy'
        THEN f.volume_mwh ELSE 0
    END) AS fixed_buy,
    SUM(CASE
        WHEN dp.price_type = 'Floating' AND d.buy_sell = 'Sell'
        THEN f.volume_mwh ELSE 0
    END) AS floating_sell,
    SUM(CASE
        WHEN dp.price_type = 'Fixed' AND d.buy_sell = 'Sell'
        THEN f.volume_mwh ELSE 0
    END) AS fixed_sell,
    SUM(CASE
        WHEN dp.price_type = 'Floating' AND d.buy_sell = 'Buy'
        THEN f.volume_mwh ELSE 0
    END) AS floating_buy
FROM fct_volume_daily f
JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
JOIN dim_deal_physical dp ON d.dim_deal_attribute_id = dp.dim_deal_attribute_id
WHERE f.business_date_local >= CURRENT_DATE
  AND d.tran_status IN ('Validated', 'Matured')
GROUP BY d.portfolio, dp.commodity

Index Concentration Risk

Identify over-concentration to a single index:

SELECT
    dp.proj_index,
    dp.commodity,
    SUM(ABS(CASE WHEN d.buy_sell = 'Buy' THEN f.volume_mwh
                 ELSE -f.volume_mwh END)) AS gross_volume,
    ABS(SUM(CASE WHEN d.buy_sell = 'Buy' THEN f.volume_mwh
                 ELSE -f.volume_mwh END)) AS net_volume
FROM fct_volume_daily f
JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
JOIN dim_deal_physical dp ON d.dim_deal_attribute_id = dp.dim_deal_attribute_id
WHERE dp.price_type = 'Floating'
  AND f.business_date_local >= CURRENT_DATE
  AND d.tran_status IN ('Validated', 'Matured')
GROUP BY dp.proj_index, dp.commodity
ORDER BY gross_volume DESC

---

Best Practices

1. Know your exposure: Regularly analyze fixed vs floating splits
2. Monitor index concentration: Avoid over-reliance on single indices
3. Track formula changes: Pricing formulas may change over time (check date ranges)
4. Understand settlement timing: Know when indices are published and settled
5. Use delta reports: See fct_delta for index sensitivity

---

Related Documentation

• Business Glossary - Index - Index definitions
• Dimensional Models - Core data models
• Power Volume Models - Index-linked power volumes
• Query Patterns - More SQL examples
• Risk Analysis - Delta and VaR queries