Python匹配不等行买卖订单 计算带剩余库存的加权售卖单价

核心逻辑 #

采用先进先出队列维护待交割的卖出库存，逐笔处理买入订单时优先消耗最早的剩余卖出库存，按实际交割量加权计算对应买入订单的Sell Price。

完整实现代码 #

import pandas as pd
from collections import deque

def calculate_sell_price(buy_df: pd.DataFrame, sell_df: pd.DataFrame) -> pd.DataFrame:
    """
    按FIFO规则匹配买卖订单，为每笔买入订单计算加权平均卖出价
    参数:
        buy_df: 买入订单表，需包含Units列，已按时间升序排序
        sell_df: 卖出订单表，需包含Units、Price列，已按时间升序排序
    返回:
        填充Sell Price列后的买入订单表
    """
    # 初始化卖出库存队列，元素格式为(剩余可售数量, 卖出单价)
    sell_queue = deque()
    for _, sell_row in sell_df.iterrows():
        sell_queue.append((sell_row['Units'], sell_row['Price']))
    
    # 逐笔处理买入订单
    for idx, buy_row in buy_df.iterrows():
        remaining_buy = buy_row['Units']
        total_cost = 0.0
        
        # 消耗卖出库存直到覆盖当前买入量或无可用卖出库存
        while remaining_buy > 1e-9 and sell_queue:  # 加浮点精度判断避免死循环
            avail_qty, sell_price = sell_queue[0]
            deal_qty = min(remaining_buy, avail_qty)
            
            total_cost += deal_qty * sell_price
            remaining_buy -= deal_qty
            remaining_sell = avail_qty - deal_qty
            
            # 更新卖出队列
            if remaining_sell > 1e-9:
                sell_queue[0] = (remaining_sell, sell_price)
            else:
                sell_queue.popleft()
        
        # 计算当前买入订单的卖出价，无足够库存时返回None
        if abs(remaining_buy - buy_row['Units']) < 1e-9:
            sell_price = None
        else:
            sell_price = round(total_cost / (buy_row['Units'] - remaining_buy), 2)
        
        buy_df.at[idx, 'Sell Price'] = sell_price
    
    return buy_df

# 测试用例（对应题目示例）
if __name__ == "__main__":
    # 构造卖出测试数据
    sell_test = pd.DataFrame({
        'Units': [0.3, 0.5, 0.15, 0.05, 2],
        'Total Units': [0.3, 0.8, 0.95, 1.0, 3.0],
        'Price': [110, 109.6, 109.2, 108, 107]
    })
    # 构造买入测试数据，第4笔为1单位
    buy_test = pd.DataFrame({
        'Units': [0.2, 0.3, 0.4, 1, 1.5],
        'Total Units': [0.2, 0.5, 0.9, 1.9, 3.4],
        'Price': [105, 106, 107, 108, 109],
        'Sell Price': [None]*5
    })
    
    result = calculate_sell_price(buy_test, sell_test)
    print(result)
    # 第4笔买入的Sell Price输出为109.58，符合题目示例要求

注意事项 #

• 传入函数前需保证买卖订单均已按发生时间升序排序，若原始数据无时间列可按Total Units列升序排序
• 代码加入了浮点精度判断，避免浮点运算误差导致的死循环或计算错误
• 若卖出总库存不足以覆盖买入需求，未匹配到足够库存的买入订单Sell Price会赋值为None，可根据业务需求调整兜底规则

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