Open Position — Physical Net Long/Short

Report ID: RPT-02 Module: commodity_risk_reporting (Global Reporting → Positions) ERP: Trad'On (Tryton 6.x) Type: Tabular list report (no charts) Status: Specification for development

1. Goal of the Report

The Open Position report answers one question for the risk manager and the trader:

For each product, have I bought more than I have sold, or the reverse — and by how much?

It computes, per product, the net physical position as the algebraic difference between open purchase quantities and open sale quantities:

Net Position = Σ Open Purchase Qty − Σ Open Sale Qty

Net > 0 → LONG — more bought than sold. The desk is exposed to a falling market (holds/owes goods it must sell).
Net < 0 → SHORT — more sold than bought. The desk is exposed to a rising market (owes goods it must source).
Net = 0 → FLAT — purchases and sales balance; no directional physical exposure.

This is the physical position only. It deliberately excludes derivatives — the hedge overlay is handled by RPT-03 (Net Position / Consolidated Risk), which consumes this report's output as its physical leg. Keeping the physical position isolated here lets the trader see the raw physical book before any hedge is applied.

Primary users: physical traders (per book), risk manager (portfolio view). Valuation basis: quantity only — this report is not a P&L report. Values (price, MTM) are out of scope; see RPT-06 (Unrealized P&L).

2. Scope and Definitions

Term	Definition
Open quantity	Contract line quantity not yet delivered/executed, i.e. nominal contract quantity minus quantity already shipped and closed. A fully executed line contributes 0.
Purchase leg	Contract lines where the company is the buyer (`P-xxxx` contracts). Add to net.
Sale leg	Contract lines where the company is the seller (`S-xxxx` contracts). Subtract from net.
Product	The tradeable product (e.g. `[FI-H2SO4] SULPHURIC ACID`). Grouping key.
Commodity	The parent commodity family (Metals, Coffee, Cocoa, Cotton, Grains, Iron Ore). Used for filtering and sub-totalling.
Quantity UoM	Unit in which the net is expressed: MT for most commodities, lots for those quoted in lots (e.g. Arabica/KC). Driven by the product's `risk_uom`, never mixed within a row.
Pricing status	Whether the open quantity is price-fixed, unfixed, or partly fixed. Informational here (full detail in RPT-04).

Position sign convention: purchases positive, sales negative. A long book shows a positive net; a short book shows a negative net.

3. Source Data

The report reads contract lines joined to their linked shipments. One source row per contract line, then aggregated by product.

Primary source model: commodity.contract.line (or the Trad'On equivalent carrying targeted quantity, direction, product, and pricing state). The executed/shipped quantity is not stored on the line — it is derived by summing the linked shipment quantities.

Fields required from source:

contract.type → buy / sell (leg direction)
product → grouping key
product.commodity → family + risk_uom
targeted_quantity → nominal/target contract quantity on the line
finished (boolean/state) → if the line is finished, open quantity is zeroed (see below)
linked shipment quantities → summed per line to obtain shipped quantity
pricing_state → fixed / unfixed / partly_fixed
shipment_period → for the period filter
strategy → for the strategy filter
counterparty

Open quantity derivation.

The line's execution progress is defined as:

quantity_executed = targeted_quantity − Σ(linked shipment quantities)

Here quantity_executed is the quantity still remaining to execute (target minus what has already shipped) — i.e. it is the open quantity. Therefore, subject to the finished-line rule below:

open_qty = targeted_quantity − Σ(linked shipment quantities)   if line NOT finished
open_qty = 0                                                   if line IS finished

Finished-line zeroing (balance zeroing). A contract line can be commercially closed before it is quantitatively complete. When the line is marked finished — the option holder has declared the balance final under the tolerance framework — any remaining unshipped quantity ceases to be open exposure, even if targeted_quantity − Σ(shipments) > 0. A finished line therefore contributes 0 to the position regardless of the arithmetic. This guard takes precedence over the shipment sum: check the finished flag first, and only compute the residual for lines still open.

Because there is no stored quantity_executed column, the report computes Σ(shipment quantities) directly from the shipment table (a LEFT JOIN + SUM, so lines with no shipment yet count their full target as open). Lines are excluded from the open position when either: the line is finished, or open_qty <= 0 (fully executed, or over-delivered within tolerance — clamp to 0).

4. Column Specification

The report has one row per product (default grouping). Columns:

#	Column name	Type	How to create it
1	Product	Char	Display name of the product (`product.rec_name`). Grouping key of the row.
2	Commodity	Char	`product.commodity.name`. If the UoM is lots, append the quotation ref (e.g. "Coffee (lots)"). Used for sub-totals.
3	Pricing	Selection badge	Aggregated pricing state of the open quantity for this product: `Fixed` if all open lines fixed; `Unfixed` if none fixed; `Partly fix` if mixed. Rendered as a status pill.
4	Shipment Pd	Char	Earliest–latest shipment period spanning the product's open lines (e.g. "Q3 2026"). If a single period, show it; if several, show the range.
5	Strategy	Char	Strategy/book code the product's open lines belong to (e.g. `ACID-EU`). If lines span strategies, show "Multiple" and expose detail on drill-down.
6	Bought (MT)	Numeric, right-aligned	`Σ open_qty` over all purchase lines of this product, in `risk_uom`. 3 decimals for MT; integer for lots.
7	Sold (MT)	Numeric, right-aligned	`Σ open_qty` over all sale lines of this product, in `risk_uom`.
8	Net (MT)	Numeric, right-aligned	`Bought − Sold`. Colored: green if > 0, red if < 0, neutral if 0. This is the core figure.
9	Direction	Selection badge	Derived from Net: `LONG` (Net > 0, green), `SHORT` (Net < 0, red), `Flat` (Net = 0, neutral). Rendered as a pill.

Column header note: columns 6–8 carry the unit label of the row's UoM. Because different rows may be in MT or lots, the header reads "Bought (MT / lots)" and each cell shows its own unit; totals are only summed within a UoM (see §5).

Optional drill-down columns (hidden by default, shown on row expand — one sub-row per contract line): Contract ref, B/S, Counterparty, Open Qty, Pricing period, Shipment date.

5. Aggregation & Grouping

Default grouping: by Product (one row each).
Optional grouping: by Commodity (sub-totals per family), then Product. Tryton's list grouping handles this natively.
Totals row (footer): the "Net" column footer sums net positions only across rows sharing the same UoM. MT rows and lot rows are totalled separately and shown as two figures (e.g. −7,880.000 MT eq and +120 lots). Never sum MT and lots into one number.
MT-equivalent option: if the business wants one grand total, provide a risk_uom → MT conversion factor on the product (e.g. 1 KC lot = 37,500 lb ≈ 17.01 MT) and expose a "MT eq" toggle. Off by default to avoid misleading single totals.

6. Filter Criteria

All filters are on the report's search bar (Tryton domain filters). Defaults in bold.

Filter	Values	Purpose
Valuation Date	date (default today)	Snapshot date; open quantity computed as of this date.
Commodity	All / Metals / Coffee / Cocoa / Cotton / Grains / Iron Ore	Restrict to a commodity family.
Product	All / specific product	Single-product focus.
Pricing Status	All / Fixed / Unfixed / Partly fixed	Isolate unpriced (price-at-risk) quantity.
Shipment Period	All / Q3 2026 / Q4 2026 / Q1 2027 …	Time-bucket the position.
Strategy	All / strategy codes	Per-book view.
Counterparty	All / specific	Concentration/limit checks.
Direction (optional)	All / Long / Short / Flat	Show only exposed products.

Active filters appear as removable chips under the filter bar (Valuation date and Weight unit shown by default).

7. KPI Header Cards

Five cards summarising the filtered result:

Card	Value	Computation
Net Long	Σ of positive nets (MT eq)	Sum of Net across products where Net > 0.
Net Short	Σ of negative nets (MT eq)	Sum of Net across products where Net < 0.
Flat / Balanced	count	Number of products with Net = 0.
Unfixed Qty	MT	Σ open_qty where pricing_state = unfixed (price risk open).
Open Contracts	count	Distinct open contract lines (buy + sell) in scope.

8. Tryton Implementation

8.1 Recommended pattern

Build on the shared abstract base commodity.risk.report (proposed for the whole suite), which provides: valuation date, risk_uom resolution, and UoM/FX conversion helpers. Open Position needs only the quantity logic — no market pricing.

8.2 Model

A read-only, non-stored ModelSQL backed by a SQL view (table_query) is the right shape: it aggregates contract lines on the fly, so it is always live and stores nothing.

# commodity_risk_reporting/open_position.py
from decimal import Decimal
from trytond.model import ModelSQL, ModelView, fields
from trytond.pool import Pool
from trytond.transaction import Transaction
from sql import Literal
from sql.aggregate import Sum
from sql.conditionals import Case


class OpenPosition(ModelSQL, ModelView):
    "Open Position — Physical Net Long/Short"
    __name__ = 'commodity.risk.open_position'

    product = fields.Many2One('product.product', 'Product', readonly=True)
    commodity = fields.Many2One(
        'commodity.commodity', 'Commodity', readonly=True)
    pricing = fields.Selection([
        ('fixed', 'Fixed'),
        ('unfixed', 'Unfixed'),
        ('partly', 'Partly fix'),
        ], 'Pricing', readonly=True)
    shipment_period = fields.Char('Shipment Pd', readonly=True)
    strategy = fields.Many2One('commodity.strategy', 'Strategy', readonly=True)
    bought = fields.Numeric('Bought', digits=(16, 3), readonly=True)
    sold = fields.Numeric('Sold', digits=(16, 3), readonly=True)
    net = fields.Numeric('Net', digits=(16, 3), readonly=True)
    direction = fields.Selection([
        ('long', 'LONG'),
        ('short', 'SHORT'),
        ('flat', 'Flat'),
        ], 'Direction', readonly=True)
    risk_uom = fields.Many2One('product.uom', 'Unit', readonly=True)

    @classmethod
    def table_query(cls):
        pool = Pool()
        Line = pool.get('commodity.contract.line')
        Contract = pool.get('commodity.contract')
        Shipment = pool.get('commodity.shipment')  # linked shipments
        line = Line.__table__()
        contract = Contract.__table__()
        shipment = Shipment.__table__()

        # Step 1: shipped quantity per line = Σ of linked shipment quantities.
        # Grouped subquery so we get one shipped total per contract line.
        shipped = shipment.select(
            shipment.contract_line.as_('line'),
            Sum(shipment.quantity).as_('shipped_qty'),
            group_by=[shipment.contract_line],
        )

        # Step 2: open_qty = targeted_quantity − shipped_qty.
        # LEFT JOIN so lines with no shipment yet keep their full target open.
        # Coalesce the (possibly NULL) shipped total to 0.
        shipped_qty = Coalesce(shipped.shipped_qty, Literal(0))
        residual = line.targeted_quantity - shipped_qty

        # Finished-line zeroing: a line marked finished contributes 0 open qty,
        # regardless of the residual (balance declared final by the option
        # holder). This guard takes precedence over the shipment arithmetic.
        # Combined with per-line over-delivery clamp (residual > 0).
        open_qty = Case(
            (line.finished, Literal(0)),
            (residual > Literal(0), residual),
            else_=Literal(0),
        )

        is_buy = contract.type == 'purchase'
        bought = Sum(Case((is_buy, open_qty), else_=Literal(0)))
        sold = Sum(Case((~is_buy, open_qty), else_=Literal(0)))

        query = line.join(
            contract, condition=line.contract == contract.id
        ).join(
            shipped, 'LEFT',
            condition=shipped.line == line.id
        ).select(
            # a synthetic id per product group is required by Tryton
            line.product.as_('id'),
            line.product.as_('product'),
            bought.as_('bought'),
            sold.as_('sold'),
            (bought - sold).as_('net'),
            where=contract.state.in_(['confirmed', 'processing']),
            group_by=[line.product],
            # keep only products with a genuine open position;
            # per-line clamping of over-delivery is handled in step 2 (see note)
            having=(bought - sold) != Literal(0),
        )
        return query

Per-line guards folded into open_qty. The Case above applies both rules before aggregation: a finished line yields 0 (balance zeroed), and a non-finished line yields its residual only when positive (over-delivery within tolerance clamps to 0). Doing this per line is essential — otherwise one finished or over-shipped line silently offsets a genuine open position elsewhere in the same product.

Imports. Add from sql.functions import Coalesce (or from sql.conditionals import Coalesce depending on the python-sql version).

Other columns. pricing, shipment_period, strategy, commodity, direction, and risk_uom are resolved either in SQL (extra joins with MIN/MAX/CASE aggregates) or as fields.Function computed in Python after the aggregate. direction is a pure function of net — long if net > 0, short if net < 0, else flat — best as a fields.Function with a searcher so the Direction filter works.

8.3 View (list / tree)

<!-- open_position_list.xml -->
<tree>
    <field name="product"/>
    <field name="commodity"/>
    <field name="pricing"/>
    <field name="shipment_period"/>
    <field name="strategy"/>
    <field name="bought"/>
    <field name="sold"/>
    <field name="net"/>
    <field name="direction"/>
</tree>

Add colour via tree cell attributes / a states expression so Net is green when positive and red when negative, and render pricing and direction as badges.

Register under Global Reporting → Positions → Open Position, mirroring the existing Physical Position screen. Provide the search filters of §6 as <field> search elements and a saved default filter on valuation_date = today.

9. Edge Cases & Business Rules

Over-delivery within tolerance. If a line's shipments exceed its target (Σ shipment qty > targeted_quantity, so open_qty < 0), clamp that line to 0 — it is closed, not a negative open position. Clamp per line before aggregating (see the CASE note in §8.2), otherwise one over-shipped line silently offsets a genuine open position elsewhere in the same product.
Finished lines (balance zeroing). A line marked finished contributes 0 open quantity even if targeted_quantity − Σ(shipments) > 0. The option holder has declared the balance final, so the residual is no longer exposure. This guard is checked first, ahead of the shipment arithmetic (see §8.2).
Unconfirmed / draft contracts. Excluded (state in ('confirmed', 'processing') only). Draft quotes are not risk.
Mixed UoM within a product. Should never happen — a product has exactly one risk_uom. Guard: if missing, flag the row rather than defaulting silently.
Lots vs MT totals. Column footers must not add across UoM (see §5). This is the single most common reporting error for multi-commodity desks.
Multi-strategy products. Show "Multiple" in the Strategy column; the per-line breakdown is available on drill-down.
Matched vs total quantity. This report uses open (undelivered) quantity, consistent with the physical exposure definition. It does not use matched quantity — that distinction (flagged in tracker items TO-004/TO-023/TO-031) concerns P&L, not position, and does not affect this report.

10. Acceptance Criteria

[ ] One row per product with open contract lines; fully executed products absent.
[ ] Net = Bought − Sold holds for every row, in the product's risk_uom.
[ ] Direction badge matches the sign of Net (LONG/SHORT/Flat).
[ ] Footer totals separate MT and lots; no cross-UoM summation.
[ ] All eight filters (§6) apply correctly, Valuation Date defaults to today.
[ ] KPI cards recompute against the filtered set.
[ ] Draft/unconfirmed contracts excluded; over-delivered lines clamped to 0.
[ ] Finished lines contribute 0 open quantity even with a positive residual (balance zeroing).
[ ] Report is read-only and stores no records (live table_query).

Spec version 1.0 — feeds RPT-03 (Net Position / Consolidated Risk) as its physical leg.