盈透证券API持仓和打印订单python程序

盈透证券打印自己的持仓，未成交订单和已成交订单的python程序

import pandas as pd
from ib_insync import *
import datetime
import os
from pytz import timezone
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter  # 导入 get_column_letter

# 辅助函数：生成期权完整名称
def get_contract_symbol(contract):
    if isinstance(contract, Option):
        symbol = contract.symbol
        expiry = contract.lastTradeDateOrContractMonth.replace('-', '')
        strike = str(contract.strike)
        right = contract.right[0]  # 'C' 或 'P'
        return f"{symbol} {expiry} {strike} {right}"
    else:
        return contract.symbol

# 连接到 IBKR
def connect_to_ibkr():
    ib = IB()
    ib.connect('127.0.0.1', 7496, clientId=0)
    print(f"已连接，clientId: 0")
    return ib

# 查询已成交订单（Fills）
def get_fills(ib):
    fills = ib.fills()
    return fills

# 处理Fill数据，合并同一订单，并转换为北京时间
def process_fills(fills):
    beijing_tz = timezone('Asia/Shanghai')
    orders = {}
    
    for fill in fills:
        order_key = fill.execution.permId if fill.execution.permId else fill.execution.orderId
        beijing_time = fill.time.astimezone(beijing_tz).strftime('%Y-%m-%d %H:%M:%S')
        
        if order_key not in orders:
            orders[order_key] = {
                'Symbol': get_contract_symbol(fill.contract),
                'Time': beijing_time,
                'Side': fill.execution.side,
                'Total Cost': 0.0,
                'Cumulative Qty': 0.0,
                'Avg Price': 0.0
            }
        
        order = orders[order_key]
        shares = float(fill.execution.shares)
        price = float(fill.execution.price)
        
        order['Total Cost'] += shares * price
        order['Cumulative Qty'] = float(fill.execution.cumQty)
        
        if beijing_time < order['Time']:
            order['Time'] = beijing_time
    
    for order in orders.values():
        if order['Cumulative Qty'] > 0:
            order['Avg Price'] = order['Total Cost'] / order['Cumulative Qty']
        del order['Total Cost']
    
    return list(orders.values())

# 主函数
def main():
    today = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
    output_file = f"C:/stock/python/ibkr_combined_{today}.xlsx"

    os.makedirs(os.path.dirname(output_file), exist_ok=True)

    ib = connect_to_ibkr()
    
    try:
        account_summary = ib.accountSummary()
        account_id = account_summary[0].account if account_summary else '未知'
        print(f"当前账户: {account_id}")

        # 1. 查询并处理已成交订单（Fills）
        fills = get_fills(ib)
        fills_data = process_fills(fills) if fills else []
        fills_df = pd.DataFrame(fills_data)
        if not fills_df.empty:
            fills_df = fills_df.sort_values(by=['Symbol', 'Time'], ascending=[True, False])
        print(f"已合并成交订单数量: {len(fills_data)} (原始成交记录数: {len(fills)})")

        # 2. 查询持仓并筛选价值超过 1000 USD 的股票，排除 USD.HKD
        positions = ib.portfolio()
        filtered_positions = []
        print(f"持仓数量: {len(positions)}")

        for pos in positions:
            contract = pos.contract
            local_symbol = contract.localSymbol if hasattr(contract, 'localSymbol') else f"{contract.symbol}.{contract.currency}"

            if local_symbol == 'USD.HKD':
                print(f"跳过 USD.HKD 持仓，不请求数据")
                continue

            if isinstance(contract, Forex):
                contract.exchange = 'IDEALPRO'
            elif not contract.exchange:
                contract.exchange = 'SMART'
            elif contract.primaryExchange == 'ISLAND':
                contract.exchange = 'NASDAQ'

            market_value = pos.marketValue
            if market_value > 1000:
                try:
                    ticker = ib.reqMktData(contract, '', True, False)
                    ib.sleep(1)
                    last_price = ticker.last if not util.isNan(ticker.last) else pos.marketPrice
                    filtered_positions.append({
                        'Symbol': get_contract_symbol(contract),
                        'Position': pos.position,
                        'Last Price': last_price,
                        'Market Value': market_value
                    })
                except Exception as e:
                    print(f"请求 {get_contract_symbol(contract)} 数据时出错: {e}")
                    continue

        positions_df = pd.DataFrame(filtered_positions)

        # 3. 查询所有交易数据（仅提取未成交订单）
        all_trades = ib.trades()
        ib.reqAllOpenOrders()
        ib.sleep(5)

        pending_from_trades = []
        print(f"所有交易数量: {len(all_trades)}")
        
        for trade in all_trades:
            order = trade.order
            order_id = order.orderId
            if order_id < 0:
                contract = trade.contract
                pending_from_trades.append({
                    'Symbol': get_contract_symbol(contract),
                    'Action': order.action,
                    'Quantity': order.totalQuantity,
                    'Lmt Price': order.lmtPrice if hasattr(order, 'lmtPrice') else None,
                })

        pendingtrades_df = pd.DataFrame(pending_from_trades)
        if not pendingtrades_df.empty:
            pendingtrades_df = pendingtrades_df.sort_values(by='Symbol')
        print(f"未成交订单数量（从 trades 提取，负 Order ID）: {len(pending_from_trades)}")

        # 写入 Excel 文件并设置工作表颜色
        with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
            positions_df.to_excel(writer, sheet_name='Positions Over 1000 USD', index=False)
            pendingtrades_df.to_excel(writer, sheet_name='Pending Orders', index=False)
            fills_df.to_excel(writer, sheet_name='Executed Fills', index=False)

            # 获取工作簿对象
            workbook = writer.book

            # 设置 'Positions Over 1000 USD' 为金色
            positions_sheet = workbook['Positions Over 1000 USD']
            positions_sheet.sheet_properties.tabColor = 'B8860B'  # 金色标签
            max_col = get_column_letter(positions_sheet.max_column)  # 获取最大列字母
            for row in positions_sheet[f'A1:{max_col}{positions_sheet.max_row}']:
                for cell in row:
                    cell.font = Font(color='B8860B')  # 金色文字
            print("已将 'Positions Over 1000 USD' 设置为金色")

            # 设置 'Pending Orders' 为红色
            pending_sheet = workbook['Pending Orders']
            pending_sheet.sheet_properties.tabColor = 'FF0000'  # 红色标签
            max_col = get_column_letter(pending_sheet.max_column)  # 获取最大列字母
            for row in pending_sheet[f'A1:{max_col}{pending_sheet.max_row}']:
                for cell in row:
                    cell.font = Font(color='FF0000')  # 红色文字
            print("已将 'Pending Orders' 设置为红色")

            # 设置 'Executed Fills' 为蓝色
            fills_sheet = workbook['Executed Fills']
            fills_sheet.sheet_properties.tabColor = '0000FF'  # 蓝色标签
            max_col = get_column_letter(fills_sheet.max_column)  # 获取最大列字母
            for row in fills_sheet[f'A1:{max_col}{fills_sheet.max_row}']:
                for cell in row:
                    cell.font = Font(color='0000FF')  # 蓝色文字
            print("已将 'Executed Fills' 设置为蓝色")

        print(f"数据已保存至 {output_file}")

    except Exception as e:
        print(f"发生错误: {e}")

    finally:
        ib.disconnect()

if __name__ == "__main__":
    main()