基于kdb/q的FIFO已实现盈亏与跨日未实现盈亏计算问题

问题说明 #

基于FIFO逻辑实现了单日已实现盈亏计算，现需将该逻辑延伸至跨交易日场景，同时按每日收盘价对投资组合盯市，跟踪每日末的未实现盈亏（已知跨日交易数据及每日收盘价）。

测试数据 #

t:([]id:1+til 6;date:(3#2024.01.02),3#2024.01.03; side:`b`s`b`s`s`b;qty:6 5 4 3 8 7;px:10 11 10 12 9 8f; closePx:13 13 13 14 14 14 );

单日FIFO已实现盈亏计算逻辑 #

// 筛选单日交易数据
t: select from t where date = 2024.01.02;
// 计算每笔交易的持仓变动量
t:update pos:sums delta from update delta:qty*(1;-1)side=`s from t;
// FIFO匹配核心函数
f:{signum[x]*x,{@[(-). z;x;:;abs[y]-sum z 1]}[y;x y]{(x;deltas y&sums x)}[abs where[signum[x]<>signum x y]#x;abs x y]};
// 计算每笔交易的FIFO匹配量
t:update fifo:deltas[id!delta;f\[id!delta;id]] from t;
// 计算每笔交易的已实现盈亏
t: update realised_pnl:sum each(id!px)*/:fifo from t;

单日未实现盈亏计算逻辑 #

// 统计每笔交易的累计匹配量
t: update matched:sum each fifo@'/:id from t;
// 计算每笔交易的剩余持仓量
t: update outstanding: delta+matched from t;

// 汇总当日末有效持仓，计算加权平均成本与未实现盈亏
eod: select px: outstanding wavg px, qty: sum outstanding, first closePx by date, side from t where outstanding>0;
eod: update unrealisedPnL: ?[side in `b; qty*(closePx-px);-1*qty*(closePx-px)] from eod;

跨交易日逻辑延续方案 #

核心思路是将前一日末持仓转换为当日的虚拟初始交易，并入当日真实交易后复用单日FIFO计算逻辑，具体实现代码如下：

// 初始化：提取所有交易日并初始化结果表
dates:asc distinct t.date;
eod:();
realised_daily:();

// 处理第一日
t_day1: select from t where date = dates[0];
// 复用单日FIFO计算逻辑
t_day1:update pos:sums delta from update delta:qty*(1;-1)side=`s from t_day1;
f:{signum[x]*x,{@[(-). z;x;:;abs[y]-sum z 1]}[y;x y]{(x;deltas y&sums x)}[abs where[signum[x]<>signum x y]#x;abs x y]};
t_day1:update fifo:deltas[id!delta;f\[id!delta;id]] from t_day1;
t_day1: update realised_pnl:sum each(id!px)*/:fifo from t_day1;
// 计算当日末持仓
t_day1: update matched:sum each fifo@'/:id from t_day1;
t_day1: update outstanding: delta+matched from t_day1;
eod_day1: select px: outstanding wavg px, qty: sum outstanding, first closePx by date, side from t_day1 where outstanding>0;
eod_day1: update unrealisedPnL: ?[side in `b; qty*(closePx-px);-1*qty*(closePx-px)] from eod_day1;
// 保存第一日结果
eod,:eod_day1;
realised_daily,:select date:dates[0], total_realised:sum realised_pnl from t_day1;

// 循环处理后续交易日
do[1+til count dates;
    current_date:dates[x];
    // 提取前一日末持仓数据
    prev_eod:select from eod where date=dates[x-1];
    // 生成虚拟初始交易（模拟前一日持仓带入当日）
    virtual_trades:([]id:(max t.id)+1+til count prev_eod; date:count prev_eod#current_date; side:prev_eod.side; qty:prev_eod.qty; px:prev_eod.px; closePx:prev_eod.closePx);
    // 合并当日真实交易与虚拟初始交易
    t_current:select from t where date=current_date;
    t_current,:virtual_trades;
    // 按交易ID排序（确保虚拟交易在当日真实交易前）
    t_current:asc[`id]t_current;
    // 复用单日FIFO计算逻辑
    t_current:update pos:sums delta from update delta:qty*(1;-1)side=`s from t_current;
    t_current:update fifo:deltas[id!delta;f\[id!delta;id]] from t_current;
    t_current: update realised_pnl:sum each(id!px)*/:fifo from t_current;
    // 计算当日末持仓
    t_current: update matched:sum each fifo@'/:id from t_current;
    t_current: update outstanding: delta+matched from t_current;
    eod_current: select px: outstanding wavg px, qty: sum outstanding, first closePx by date, side from t_current where outstanding>0;
    eod_current: update unrealisedPnL: ?[side in `b; qty*(closePx-px);-1*qty*(closePx-px)] from eod_current;
    // 保存当日结果
    eod,:eod_current;
    realised_daily,:select date:current_date, total_realised:sum realised_pnl from t_current;
];

// 查看最终结果
realised_daily;  // 每日已实现盈亏汇总
eod;             // 每日末持仓未实现盈亏汇总

其他未实现盈亏跟踪方法建议 #

• 持仓台账法：维护实时更新的持仓明细台账，记录每笔未平仓持仓的成本价、数量，每日收盘价更新时直接计算单明细未实现盈亏后汇总。该方法直观，适合需要明细追溯的场景，无需每日重新执行FIFO匹配。
• 逐日盯市法：每日计算当日持仓公允价值（收盘价×持仓量）与持仓总成本（加权平均成本×持仓量）的差值，作为当日未实现盈亏变动，累计得到总未实现盈亏。该方法无需回溯历史交易，仅依赖每日末持仓数据，计算效率更高。

内容的提问来源于stack exchange，提问作者Dido12