Plugin

Realised P&L by year

Sums a run's closed trades per calendar year and instrument: gross gains, losses, commission, swap and net, as CSV and Markdown, in EUR when ECB rates are cached.

ExporterNo networkDesktop onlyv1.0.0

Get early access Plugin API documentation

About

Not tax advice: a backtest is hypothetical, and this exporter only adds up its trades. Ask a tax adviser how real trading results are to be declared.

A trade counts in the year it was closed, on the clock of the time zone you set, so a trade closed at 23:30 UTC on 31 December counts in the next year in Berlin. Per year and instrument the report lists the number of trades (won and lost), the gross gains (trades that made money before costs), the gross losses, the commission paid, the swap (a credit is positive) and the net result: net = gross gains + gross losses - commission + swap, which is the sum of the trades' own net figures.

The amounts are in the result's currency, which is the instrument's quote currency (USD for EURUSD); the engine converts nothing. With the ECB reference rates plugin (ecb_fx_reference) switched on and its data fetched, the report adds every amount in EUR: each trade at the euro reference rate known on the day it closed, read from the feed ecb_eur plus the currency (ecb_eurusd holds USD per 1 EUR). Without that feed, or when it does not cover every closing day, the report stays in the result's currency and says why in its first lines.

Configuration

Field	Type	Default	Description
`convertToEur`Add EUR with ECB rates	boolean	`true`	Add every amount in EUR with the ECB reference rates, when their feed is on this machine. Off keeps the report in the result's currency.
`fileName`File name	string	`"realised_pnl"`	The start of both file names; the instrument and the run's seed are added, the files end in .csv and .md.
`timeZone`Time zone for the year boundary	string	`"Europe/Berlin"`	The time zone whose midnight separates the years, as an IANA name like Europe/Berlin or UTC.

Use it in a strategy

Not called from a strategy: after a run, choose it in the result's export menu. Exports are part of the plans that include CSV export. For EUR, switch on ecb_fx_reference and run once with "Fetch external data (news and plugin feeds)" ticked, so its feed is on this machine.

What it provides

`realised_pnl_<instrument>_<seed>.csv`File	One row per year and instrument. Two lines starting with # come first (the disclaimer and the currency basis); pandas reads it with comment="#". The _eur columns are empty when nothing was converted.
`realised_pnl_<instrument>_<seed>.md`File	The same as a Markdown report: the disclaimer, the currency basis, a table per year and a table per year and instrument.

Limits

Not tax advice and not a tax report. It does not know your country's rules, loss offsetting, allowances or the tax categories of CFDs, futures or forex.
The trades are those of a hypothetical backtest. A trade the engine closed at the end of the data counts in the year the data ended.
EUR only through the ECB reference rates, one a day: a trade uses the last rate published before the day it closed, which on a Monday is Friday's.

Preview

text# Realised P&L by year

**Not tax advice. These are the trades of a hypothetical backtest, not real transactions; ask a tax adviser how real trading results are to be declared.**

Run: EURUSD H1, 2023-01-02 to 2024-12-31, seed 42. Years by the closing day in Europe/Berlin.

Converted to EUR trade by trade at the ECB euro reference rate known on the day each trade closed (feed ecb_eurusd, USD per 1 EUR).

## Per year (USD)

| Year | Trades | Gross gains | Gross losses | Commission | Swap | Net |
| --- | ---: | ---: | ---: | ---: | ---: | ---: |
| 2023 | 2 | 420.00 | -250.00 | 14.00 | -3.10 | 152.90 |
| 2024 | 2 | 180.00 | -310.00 | 14.00 | -0.80 | -144.80 |

## Per year (EUR)

| Year | Trades | Gross gains | Gross losses | Commission | Swap | Net |
| --- | ---: | ---: | ---: | ---: | ---: | ---: |
| 2023 | 2 | 388.89 | -231.48 | 12.96 | -2.87 | 141.57 |
| 2024 | 2 | 166.67 | -287.04 | 12.96 | -0.74 | -134.07 |

## Per year and instrument (USD)

| Year | Instrument | Trades | Won | Lost | Gross gains | Gross losses | Commission | Swap | Net |
| --- | --- | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: |
| 2023 | EURUSD | 2 | 1 | 1 | 420.00 | -250.00 | 14.00 | -3.10 | 152.90 |
| 2024 | EURUSD | 2 | 1 | 1 | 180.00 | -310.00 | 14.00 | -0.80 | -144.80 |

## Per year and instrument (EUR)

| Year | Instrument | Trades | Won | Lost | Gross gains | Gross losses | Commission | Swap | Net |
| --- | --- | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: |
| 2023 | EURUSD | 2 | 1 | 1 | 388.89 | -231.48 | 12.96 | -2.87 | 141.57 |
| 2024 | EURUSD | 2 | 1 | 1 | 166.67 | -287.04 | 12.96 | -0.74 | -134.07 |

Gross gains add up the trades that made money before costs, gross losses the ones that did not. Commission is what was paid; swap is signed, a credit is positive. Net = gross gains + gross losses - commission + swap. A trade closed by the end of the data counts in the year the data ended.

A made-up sample that shows the shape of the output. Not vendor data and not a result.

The code

main.py, the file the app installs.

python"""Realised P&L by year: gains, losses and costs per calendar year and instrument.

A trade counts in the year it was closed, on your own clock (a trade closed at
23:30 UTC on 31 December is already next year in Berlin). Per year and
instrument the report sums the gross gains (trades that made money before
costs), the gross losses, the commission paid, the swap (signed: a credit is
positive) and the net result, and counts the trades:

    net = gross gains + gross losses - commission + swap

which is the sum of the trades' own net figures, as the engine computes them.

The money is in the result's currency, the instrument's quote currency (the
engine converts nothing). With the ECB reference rates plugin switched on, the
report adds every figure in euros, each trade converted at the reference rate
known on the day it was closed: the feed `ecb_eur<ccy>` (lower case, USD per
1 EUR for `ecb_eurusd`) holds each rate under the day after the ECB published
it. When the feed is missing, or does not reach every trade, the report stays
in the result's currency and says why: a total half in euros and half in
dollars is worse than none.

This is arithmetic on a hypothetical backtest. It is not tax advice and not a
tax report, and the output says so.
"""

import csv
import io
import re

from orionquant_engine.plugins.kit import dates, feeds

_API_REF = {"api": None}

DISCLAIMER = ("Not tax advice. These are the trades of a hypothetical backtest, not real transactions; "
              "ask a tax adviser how real trading results are to be declared.")

COLUMNS = [
    "year", "instrument", "trades", "winning_trades", "losing_trades",
    "gross_gains", "gross_losses", "commission", "swap", "net", "currency",
    "gross_gains_eur", "gross_losses_eur", "commission_eur", "swap_eur", "net_eur",
]

_MONEY = ("gross_gains", "gross_losses", "commission", "swap", "net")


def _setting(key, default):
    api = _API_REF["api"]
    return api.get_config(key, default) if api else default


def _empty():
    return {k: 0.0 for k in _MONEY} | {"trades": 0, "winning_trades": 0, "losing_trades": 0}


def _add(bucket, trade, factor=1.0):
    """Add one trade to a bucket of sums, its money multiplied by `factor`."""
    gross = float(trade.get("grossPnl") or 0.0)
    if gross > 0:
        bucket["gross_gains"] += gross * factor
    else:
        bucket["gross_losses"] += gross * factor
    bucket["commission"] += float(trade.get("commission") or 0.0) * factor
    bucket["swap"] += float(trade.get("swap") or 0.0) * factor
    bucket["net"] += float(trade.get("netPnl") or 0.0) * factor


def eur_rates(trades, currency):
    """(rate per trade or None, note). A rate is units of `currency` per 1 EUR.

    None for the list means the report stays in the result's currency; the
    note says why, or which basis the conversion used.
    """
    if not currency:
        return None, "The result does not state its currency, so nothing was converted to EUR."
    if currency == "EUR":
        return [1.0] * len(trades), "The result is in EUR already."
    if not re.fullmatch(r"[A-Z]{3}", currency):
        return None, "The result's currency {} is not an ISO code the ECB publishes.".format(currency)
    name = "ecb_eur" + currency.lower()
    if not feeds.exists(name):
        return None, ("No ECB reference rate feed {} on this machine, so the amounts stay in {}. Switch on the "
                      "ECB reference rates plugin (ecb_fx_reference) and fetch its data once to add EUR."
                      ).format(name, currency)
    feed = feeds.read_feed(name)
    rates, missing = [], []
    for trade in trades:
        day = dates.to_utc(trade["exitTs"]).date().isoformat()
        value = feed.get(day)
        ok = isinstance(value, (int, float)) and not isinstance(value, bool) and value > 0
        rates.append(float(value) if ok else None)
        if not ok:
            missing.append(day)
    if missing:
        return None, ("The feed {} has no rate for {} of {} closing days (the first is {}), so the amounts stay "
                      "in {}. Fetch the ECB data again to cover the whole run.").format(
                          name, len(missing), len(trades), min(missing), currency)
    return rates, ("Converted to EUR trade by trade at the ECB euro reference rate known on the day each trade "
                   "closed (feed {}, {} per 1 EUR).").format(name, currency)


def tables(result, *, tz="UTC", convert=True):
    """(rows, note, converted): one row per year and instrument, sorted.

    Money stays unrounded here; it is rounded to cents only when written, so a
    year's total is the sum of the trades and not of rounded parts.
    """
    trades = list(result.get("trades") or [])
    currency = str(result.get("resultCurrency") or "").upper()
    dates.zone(tz)  # a wrong zone name fails here, with a message that says so
    if convert:
        rates, note = eur_rates(trades, currency)
    else:
        rates, note = None, "Conversion to EUR is switched off in the settings."

    sums, sums_eur = {}, {}
    for i, trade in enumerate(trades):
        year = dates.local_date(trade["exitTs"], tz).year
        key = (year, str(trade.get("symbol") or (result.get("config") or {}).get("instrument") or ""))
        bucket = sums.setdefault(key, _empty())
        _add(bucket, trade)
        bucket["trades"] += 1
        net = float(trade.get("netPnl") or 0.0)
        if net > 0:
            bucket["winning_trades"] += 1
        elif net < 0:
            bucket["losing_trades"] += 1
        if rates is not None:
            _add(sums_eur.setdefault(key, _empty()), trade, 1.0 / rates[i])

    rows = []
    for key in sorted(sums):
        b = sums[key]
        row = {"year": key[0], "instrument": key[1], "trades": b["trades"],
               "winning_trades": b["winning_trades"], "losing_trades": b["losing_trades"],
               "currency": currency}
        for m in _MONEY:
            row[m] = b[m]
            row[m + "_eur"] = sums_eur[key][m] if rates is not None else None
        rows.append(row)
    return rows, note, rates is not None


def csv_text(rows, note):
    """The CSV: two comment lines (the disclaimer and the basis), then the table."""
    buf = io.StringIO()
    buf.write("# " + DISCLAIMER + "\n")
    buf.write("# " + note + "\n")
    writer = csv.writer(buf, lineterminator="\n")
    writer.writerow(COLUMNS)
    for row in rows:
        writer.writerow([_cents(row[c]) if isinstance(row[c], float) or row[c] is None else row[c]
                         for c in COLUMNS])
    return buf.getvalue()


def _cents(value):
    """Money as text with two decimals, "" for no value; never "-0.00"."""
    if value is None:
        return ""
    text = "{:.2f}".format(value)
    return "0.00" if text == "-0.00" else text


def _money(value):
    """Money for the Markdown tables, with thousands separators."""
    text = "{:,.2f}".format(value)
    return "0.00" if text == "-0.00" else text


def _year_table(rows, suffix):
    """Markdown lines: one row per year, summed over the instruments."""
    out = ["| Year | Trades | Gross gains | Gross losses | Commission | Swap | Net |",
           "| --- | ---: | ---: | ---: | ---: | ---: | ---: |"]
    years = {}
    for row in rows:
        y = years.setdefault(row["year"], {"trades": 0} | {m: 0.0 for m in _MONEY})
        y["trades"] += row["trades"]
        for m in _MONEY:
            y[m] += row[m + suffix]
    for year in sorted(years):
        y = years[year]
        out.append("| {} | {} | {} | {} | {} | {} | {} |".format(
            year, y["trades"], *(_money(y[m]) for m in _MONEY)))
    if not rows:
        out.append("| none | 0 | | | | | |")
    return out


def _instrument_table(rows, suffix):
    """Markdown lines: one row per year and instrument."""
    out = ["| Year | Instrument | Trades | Won | Lost | Gross gains | Gross losses | Commission | Swap | Net |",
           "| --- | --- | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: |"]
    for row in rows:
        out.append("| {} | {} | {} | {} | {} | {} | {} | {} | {} | {} |".format(
            row["year"], row["instrument"], row["trades"], row["winning_trades"], row["losing_trades"],
            *(_money(row[m + suffix]) for m in _MONEY)))
    return out


def markdown_text(result, rows, note, converted, tz):
    """The Markdown report: the disclaimer first, then the tables, in the
    result's currency and, when converted, in EUR as well."""
    config = result.get("config") or {}
    currency = str(result.get("resultCurrency") or "") or "the result's currency"
    out = [
        "# Realised P&L by year",
        "",
        "**{}**".format(DISCLAIMER),
        "",
        "Run: {} {}, {} to {}, seed {}. Years by the closing day in {}.".format(
            config.get("instrument", ""), config.get("timeframe", ""), config.get("startDate", ""),
            config.get("endDate", ""), result.get("seed", ""), tz),
        "",
        note,
    ]
    units = [(currency, "")]
    if converted and currency != "EUR":
        units.append(("EUR", "_eur"))
    for unit, suffix in units:
        out += ["", "## Per year ({})".format(unit), ""] + _year_table(rows, suffix)
    for unit, suffix in units:
        out += ["", "## Per year and instrument ({})".format(unit), ""] + _instrument_table(rows, suffix)
    out += [
        "",
        "Gross gains add up the trades that made money before costs, gross losses the ones that did not. "
        "Commission is what was paid; swap is signed, a credit is positive. "
        "Net = gross gains + gross losses - commission + swap. A trade closed by the end of the data counts "
        "in the year the data ended.",
        "",
    ]
    return "\n".join(out)


def _file_part(text):
    """A piece of a file name: letters, digits, dash and underscore only."""
    return re.sub(r"[^A-Za-z0-9_-]+", "_", str(text)).strip("_") or "run"


def export(result):
    tz = str(_setting("timeZone", "Europe/Berlin")).strip() or "UTC"
    rows, note, converted = tables(result, tz=tz, convert=bool(_setting("convertToEur", True)))
    config = result.get("config") or {}
    base = "{}_{}_{}".format(_file_part(_setting("fileName", "realised_pnl")),
                             _file_part(config.get("instrument", "")), _file_part(result.get("seed", 0)))
    # Relative paths land in the export's own folder (or the data cache). Two
    # files, so the function returns nothing and the export hands back the folder.
    with open(base + ".csv", "w", encoding="utf-8", newline="") as fh:
        fh.write(csv_text(rows, note))
    with open(base + ".md", "w", encoding="utf-8", newline="") as fh:
        fh.write(markdown_text(result, rows, note, converted, tz))
    api = _API_REF["api"]
    if api:
        api.log("wrote {0}.csv and {0}.md ({1} rows, {2})".format(base, len(rows), "EUR" if converted else "no EUR"))
    return None


def register(api):
    _API_REF["api"] = api
    api.register_exporter("realised_pnl_by_year", "Realised P&L by year", export)