Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

import pyodbc import numpy import pandas as pd import matplotlib.pyplot as plt import…

  1. User

    import pyodbc import numpy import pandas as pd import matplotlib.pyplot as plt import xlwings as xw

    def brinson(portfolioID, beginDate, endDate, analyst=None, industry=None): try:
    # 建立与Excel的互动 wb = xw.Book.caller() sht = wb.sheets['sheet1'] sht2 = wb.sheets['sheet2']

        # 设置连接字符串和数据库连接
        conn_str = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\LIQI\Documents\portfolio.accdb"
        conn = pyodbc.connect(conn_str)
    
        portfolio_benchmark_mapping = {
        '300Active': 'CSI300',
        '300Standard': 'CSI300',
        '500Active': 'CSI500',
        '500Standard': 'CSI500'}
    
        # 获取给定portfolioID的对应benchID
        if portfolioID not in portfolio_benchmark_mapping:
            print(f"未找到投资组合ID的基准: {portfolioID}")
        else:
            benchID = portfolio_benchmark_mapping[portfolioID]
        
        # 查询数据库
        portfolio_query = "SELECT * FROM Mockportfolio WHERE ID = ? AND TRADEDATE >= ? AND tradedate <= ?"
        portfolio_data = pd.read_sql(portfolio_query, conn, params=[portfolioID, beginDate, endDate])
        # 修改 benchmark_query,使用参数化查询
        benchmark_query = "SELECT * FROM Benchmark WHERE BenchID = ? AND TRADEDATE >= ? AND tradedate <= ?"
        benchmark_data = pd.read_sql(benchmark_query, conn, params=[benchID, beginDate, endDate])
    
        portfolio_data['RETURN'] = portfolio_data['RETURN'].astype(float)
        benchmark_data['Weight'] = benchmark_data['Weight'].astype(float)
        benchmark_data['Return'] = benchmark_data['Return'].astype(float)
    
        portfolio_data['CONTRIBUTION'] = portfolio_data['WEIGHT'] * portfolio_data['RETURN']
        benchmark_data['CONTRIBUTION'] = benchmark_data['Weight'] * benchmark_data['Return']
    
        if analyst is not None:
            # 为指定的分析师过滤投资组合数据
            portfolio_data = portfolio_data[portfolio_data['Analyst'] == analyst]
            benchmark_data = benchmark_data[benchmark_data['Analyst'] == analyst]
        
        benchmark_data_grouped = benchmark_data.groupby(['TradeDate', 'Industry I'])[['Weight', 'Return', 'CONTRIBUTION']].sum().reset_index()
        portfolio_data_grouped = portfolio_data.groupby(['TRADEDATE', 'Industry I'])[['WEIGHT', 'RETURN', 'CONTRIBUTION']].sum().reset_index()
        benchmark_data_grouped['Return'] = benchmark_data_grouped['CONTRIBUTION'] / benchmark_data_grouped['Weight']   
        portfolio_data_grouped['RETURN'] = portfolio_data_grouped['CONTRIBUTION'] / portfolio_data_grouped['WEIGHT']
        #portfolio_data_grouped['RETURN'] = portfolio_data_grouped['RETURN'].fillna(0)
        
        benchmark_data_grouped.rename(columns={'TradeDate': 'TRADEDATE','Weight': 'WEIGHT','Return': 'RETURN'}, inplace=True)
        portfolio_df = pd.merge(portfolio_data_grouped, benchmark_data_grouped, on=['TRADEDATE', 'Industry I'], suffixes=('_P', '_B'))
        
        portfolio_df['Q1_daily'] = portfolio_df['WEIGHT_B'] * portfolio_df['RETURN_B']
        portfolio_df['Q2_daily'] = portfolio_df['WEIGHT_P'] * portfolio_df['RETURN_B']
        portfolio_df['Q3_daily'] = portfolio_df['WEIGHT_B'] * portfolio_df['RETURN_P']
        portfolio_df['Q4_daily'] = portfolio_df['WEIGHT_P'] * portfolio_df['RETURN_P']
    
        portfolio_df['excess_return_daily'] = portfolio_df['Q4_daily'] - portfolio_df['Q1_daily']
        portfolio_df['allocation_effect_daily'] = portfolio_df['Q2_daily'] - portfolio_df['Q1_daily']
        portfolio_df['selection_effect_daily'] = portfolio_df['Q3_daily'] - portfolio_df['Q1_daily']
        portfolio_df['interaction_effect_daily'] = portfolio_df['Q4_daily'] - portfolio_df['Q3_daily'] - portfolio_df['Q2_daily'] + portfolio_df['Q1_daily']
    
        # 计算各行业的累积超额收益,每日收益率累乘-1
        portfolio_df['RETURN_P_compound']=(1 + portfolio_df['RETURN_P']).groupby([portfolio_df.iloc[:, 1]]).cumprod()-1
        portfolio_df['RETURN_B_compound']=(1 + portfolio_df['RETURN_B']).groupby([portfolio_df.iloc[:, 1]]).cumprod()-1
        portfolio_df['Q1_compound']=(1 + portfolio_df['Q1_daily']).groupby([portfolio_df.iloc[:, 1]]).cumprod()-1
        portfolio_df['Q2_compound']=(1 + portfolio_df['Q2_daily']).groupby([portfolio_df.iloc[:, 1]]).cumprod()-1
        portfolio_df['Q3_compound']=(1 + portfolio_df['Q3_daily']).groupby([portfolio_df.iloc[:, 1]]).cumprod()-1
        portfolio_df['Q4_compound']=(1 + portfolio_df['Q4_daily']).groupby([portfolio_df.iloc[:, 1]]).cumprod()-1
    
        #四种效应的累计收益
        portfolio_df['excess_return_compound'] = portfolio_df['Q4_compound'] - portfolio_df['Q1_compound']
        portfolio_df['allocation_effect_compound'] = portfolio_df['Q2_compound'] - portfolio_df['Q1_compound']
        portfolio_df['selection_effect_compound'] = portfolio_df['Q3_compound'] - portfolio_df['Q1_compound']
        portfolio_df['interaction_effect_compound'] = portfolio_df['Q4_compound'] - portfolio_df['Q3_compound'] - portfolio_df['Q2_compound'] + portfolio_df['Q1_compound']
    
        #对 portfolio_df 数据框进行了索引重置,以确保索引重新从零开始,以便于后续处理和分析。
        portfolio_df=portfolio_df.reset_index()
    
        # 计算整个投资组合的累积超额收益
        #四种效应下的累计收益按交易日期分组求和然后索引重置
        #excess_return_compound = portfolio_df.groupby('TRADEDATE')['excess_return_compound'].sum().reset_index()
        #allocation_effect_compound = portfolio_df.groupby('TRADEDATE')['allocation_effect_compound'].sum().reset_index()
        #selection_effect_compound = portfolio_df.groupby('TRADEDATE')['selection_effect_compound'].sum().reset_index()
        #interaction_effect_compound = portfolio_df.groupby('TRADEDATE')['interaction_effect_compound'].sum().reset_index()
    
        # 为特定行业绘制图表
        if industry is not None:
            industry_data = portfolio_df[portfolio_df['Industry I'] == industry]
            x_data = industry_data['TRADEDATE'].to_numpy() 
            y1_data = industry_data['excess_return_compound'].to_numpy() 
            y2_data = industry_data['allocation_effect_compound'].to_numpy() 
            y3_data = industry_data['selection_effect_compound'].to_numpy() 
            y4_data = industry_data['interaction_effect_compound'].to_numpy()
            
            # 创建一个图形和坐标轴
            plt.figure(figsize=(12, 9))
            fig, ax = plt.subplots()
            
            # 绘制折线图
            ax.plot(x_data, y1_data, color='blue', marker='o', label='excess_return_compound')
            ax.plot(x_data, y2_data, color='red', marker='o', label='allocation_effect_compound')
            ax.plot(x_data, y3_data, color='black', marker='o', label='selection_effect_compoundt')
            ax.plot(x_data, y4_data, color='purple', marker='o', label='interaction_effect_compound')
            
            plt.xticks(rotation=45)
            plt.title(f"Excess Return in {industry}")
            plt.xlabel('TRADEDATE')
            plt.ylabel('excess_return_compound')
            plt.legend()
            # Show the plot
            left = sht2.range('G3').left
            top = sht2.range('G3').top
            sht2.pictures.add(fig, name='Industry', update=True, left=left, top=top)
            
            #生成单行业时间序列
            industry_data = industry_data[['TRADEDATE','excess_return_compound','allocation_effect_compound','selection_effect_compound','interaction_effect_compound']]
            industry_data.rename(columns={'TRADEDATE':'交易日'}, inplace=True)
            industry_data.rename(columns={'excess_return_compound': '超额收益','allocation_effect_compound': '配置效应'}, inplace=True)
            industry_data.rename(columns={'selection_effect_compound': '选股效应','interaction_effect_compound': '交叉效应'}, inplace=True)
            # 将后四列的数据格式化为 #.###### 格式
            decimal_format = lambda x: f'{x:.6f}'
            columns_to_format = ['超额收益', '配置效应', '选股效应', '交叉效应']
            #写入excel
            sht2.range('A2').options(index=False, header=True, numbers=str).value = industry_data
            
            
        
    
        # 为特定组合绘制图表
        excess_return_compound = portfolio_df.groupby('TRADEDATE')['excess_return_compound'].sum().reset_index()
        allocation_effect_compound = portfolio_df.groupby('TRADEDATE')['allocation_effect_compound'].sum().reset_index()
        selection_effect_compound = portfolio_df.groupby('TRADEDATE')['selection_effect_compound'].sum().reset_index()
        interaction_effect_compound = portfolio_df.groupby('TRADEDATE')['interaction_effect_compound'].sum().reset_index()
    
        x21_data = excess_return_compound['TRADEDATE'].to_numpy() 
        y21_data = excess_return_compound['excess_return_compound'].to_numpy() 
        y22_data = allocation_effect_compound['allocation_effect_compound'].to_numpy()
        y23_data = selection_effect_compound['selection_effect_compound'].to_numpy()
        y24_data = interaction_effect_compound['interaction_effect_compound'].to_numpy() 
    
        # 创建一个图形和坐标轴
        plt.figure(figsize=(12, 9))
        fig, ax = plt.subplots()
    
        ax.plot(x21_data, y21_data, color='blue', marker='o', label='excess_return_compound')
        ax.plot(x21_data, y22_data, color='red', marker='o', label='allocation_effect_compound')
        ax.plot(x21_data, y23_data, color='black', marker='o', label='selection_effect_compoundt')
        ax.plot(x21_data, y24_data, color='purple', marker='o', label='interaction_effect_compound')
    
        plt.title('Entire Portfolio Compound Excess Return')
        plt.xlabel('Trade Date')
        plt.ylabel('Excess Return')
    
        # 添加图例
        ax.legend()
        plt.xticks(rotation=45)
    
           
        # 插入第二个图表到 sheet1
        left = sht.range('J3').left
        top = sht.range('J3').top
        sht.pictures.add(fig, name='PortfolioChart2', update=True, left=left, top=top)
        
        
        #生成投资组合快照
        portfolio_snap = portfolio_df[portfolio_df['TRADEDATE'] == endDate]
        portfolio_snap = portfolio_snap[['Industry I','WEIGHT_P','WEIGHT_B','RETURN_P_compound','RETURN_B_compound','excess_return_compound','allocation_effect_compound','selection_effect_compound','interaction_effect_compound']]
        portfolio_snap = portfolio_snap.sort_values('selection_effect_compound', ascending=False)
        portfolio_snap.rename(columns={'Industry I':'行业','WEIGHT_P':'组合权重','WEIGHT_B':'指数权重','RETURN_P_compound': '组合回报','RETURN_B_compound': '指数回报'}, inplace=True)
        portfolio_snap.rename(columns={'excess_return_compound': '超额收益','allocation_effect_compound': '配置效应'}, inplace=True)
        portfolio_snap.rename(columns={'selection_effect_compound': '选股效应','interaction_effect_compound': '交叉效应'}, inplace=True)
        # 将后四列的数据格式化为 #.###### 格式
        decimal_format = lambda x: f'{x:.6f}'
        columns_to_format = ['组合权重','指数权重','组合回报','超额收益', '配置效应', '选股效应', '交叉效应']
        #写入excel
        sht.range('A2').options(index=False, header=True, numbers=str).value = portfolio_snap
        
    except Exception as e:
        print(f"Error: {e}")
    
    finally:
        conn.close()
    

    def generate():
    # 建立与Excel的互动 wb = xw.Book.caller() sht = wb.sheets['sheet1'] portfolioID = sht.range('b1').value#300Active,300Standard…… beginDate = sht.range('j1').value endDate = sht.range('l1').value analyst = sht.range('h1').value industry = sht.range('e1').value#一级行业

    brinson(portfolioID, beginDate, endDate, analyst, industry)  
                
    

    if name == "main": generate() 我写了一个brinson归因分析的脚本,现在遇到一个问题。如果从参数beginDate到endDate,某个analyst在某个industry的weight_p上一直>0,那么 在#生成投资组合快照这一段会出现有关该industry的数据,但一旦endDate那天,该industry的weight_p=0,数据就不显示了。问题出在哪里呢?因为即便在endDate,不再持有该industry的股票,在期间还是会有累积超额收益数据啊

  2. Assistant

    问题可能出在筛选特定行业的代码中。在if industry is not None:这个分支中,您使用了portfolio_df[portfolio_df['Industry I'] == industry]来筛选特定行业的数据。但是,如果在endDate时不再持有该行业的股票,那么在portfolio_df中就不会有该行业的数据,所以在绘制图表和生成投资组合快照时就无法获取相应的数据。

    为了解决这个问题,您可以将筛选特定行业的代码放在portfolio_data_grouped之前,这样可以确保获取到整个期间的行业数据。然后,在绘制图表和生成投资组合快照时,再根据industry条件进行筛选,但这时您需要使用portfolio_df而不是industry_data来获取相应的数据。

    修改后的代码如下:

    # 查询数据库
    portfolio_query = "SELECT * FROM Mockportfolio WHERE ID = ? AND TRADEDATE >= ? AND tradedate <= ?"
    portfolio_data = pd.read_sql(portfolio_query, conn, params=[portfolioID, beginDate, endDate])
    # 修改 benchmark_query,使用参数化查询
    benchmark_query = "SELECT * FROM Benchmark WHERE BenchID = ? AND TRADEDATE >= ? AND tradedate <= ?"
    benchmark_data = pd.read_sql(benchmark_query, conn, params=[benchID, beginDate, endDate])
    
    portfolio_data['RETURN'] = portfolio_data['RETURN'].astype(float)
    benchmark_data['Weight'] = benchmark_data['Weight'].astype(float)
    benchmark_data['Return'] = benchmark_data['Return'].astype(float)
    
    portfolio_data['CONTRIBUTION'] = portfolio_data['WEIGHT'] * portfolio_data['RETURN']
    benchmark_data['CONTRIBUTION'] = benchmark_data['Weight'] * benchmark_data['Return']
    
    if analyst is not None:
        # 为指定的分析师过滤投资组合数据
        portfolio_data = portfolio_data[portfolio_data['Analyst'] == analyst]
        benchmark_data = benchmark_data[benchmark_data['Analyst'] == analyst]
    
    # 为特定行业绘制图表
    if industry is not None:
        industry_data = portfolio_data[portfolio_data['Industry I'] == industry]
        x_data = industry_data['TRADEDATE'].to_numpy() 
        y1_data = industry_data['excess_return_compound'].to_numpy() 
        y2_data = industry_data['allocation_effect_compound'].to_numpy() 
        y3_data = industry_data['selection_effect_compound'].to_numpy() 
        y4_data = industry_data['interaction_effect_compound'].to_numpy()
        
        # 创建一个图形和坐标轴
        plt.figure(figsize=(12, 9))
        fig, ax = plt.subplots()
        
        # 绘制折线图
        ax.plot(x_data, y1_data, color='blue', marker='o', label='excess_return_compound')
        ax.plot(x_data, y2_data, color='red', marker='o', label='allocation_effect_compound')
        ax.plot(x_data, y3_data, color='black', marker='o', label='selection_effect_compoundt')
        ax.plot(x_data, y4_data, color='purple', marker='o', label='interaction_effect_compound')
        
        plt.xticks(rotation=45)
        plt.title(f"Excess Return in {industry}")
        plt.xlabel('TRADEDATE')
        plt.ylabel('excess_return_compound')
        plt.legend()
        # Show the plot
        left = sht2.range('G3').left
        top = sht2.range('G3').top
        sht2.pictures.add(fig, name='Industry', update=True, left=left, top=top)
            
    # 将特定行业的数据重新赋值为portfolio_data,以便后续处理和分析
    portfolio_data = industry_data
    
    # 继续后面的代码
    benchmark_data_grouped = benchmark_data.groupby(['TradeDate', 'Industry I'])[['Weight',