Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

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

  1. User

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

    def brinson(portfolioID, beginDate, endDate, analyst=None, industry=None):

    # Setup connection to the database
    conn_str = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\LIQI\Documents\portfolio.accdb"
    try:
        conn = pyodbc.connect(conn_str)
    
    
        # Define the portfolio-benchmark mapping dictionary
        portfolio_benchmark_mapping = {
            '300Active': 'CSI300',
            '300Standard': 'CSI300',
            '500Active': 'CSI500',
            '500Standard':'CSI500'
            }
    
        # Get the corresponding benchID for the given portfolioID
        if portfolioID not in portfolio_benchmark_mapping:
            print(f"No benchmark found for portfolioID: {portfolioID}")
            conn.close()
            return
    
        benchID = portfolio_benchmark_mapping[portfolioID]
    
        # Query the portfolio table
        portfolio_query = f"SELECT * FROM Mockportfolio WHERE ID = {portfolioID} AND TRADEDATE >= '{beginDate}' AND tradedate <= '{endDate}'"
        portfolio_data = pd.read_sql(portfolio_query, conn)
        portfolio_data['CONTRIBUTION'] = portfolio_data['WEIGHT']*portfolio_data['RETURN']        
    
        # Query the benchmark table
        benchmark_query = f"SELECT * FROM benchmark WHERE BenchID = {benchID} AND TRADEDATE >= '{beginDate}' AND tradedate <= '{endDate}'"
        benchmark_data = pd.read_sql(benchmark_query, conn)
        benchmark_data['CONTRIBUTION'] = benchmark_data['WEIGHT']*benchmark_data['RETURN']
    
    
        if analyst is not None:
            # Filter portfolio data for the specified analyst
            portfolio_data = portfolio_data[portfolio_data['Analyst'] == analyst]
            benchmark_data = benchmark_data[benchmark_data['Analyst'] == analyst]
    
        # Group by industry and calculate the necessary values
        benchmark_data_grouped = benchmark_data.groupby(['TRADEDATE', 'INDUSTRY']).sum()
        portfolio_data_grouped = portfolio_data.groupby(['TRADEDATE', 'INDUSTRY']).sum()
    
        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_df = pd.merge(portfolio_data_grouped, benchmark_data_grouped, on=['TRADEDATE', 'INDUSTRY'], 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']
    
        # Calculate the compounded excess return of each industry
        portfolio_df['Q1_compound']=(1 + portfolio_df['Q1_daily']).groupby(['INDUSTRY']).cumprod()-1
        portfolio_df['Q2_compound']=(1 + portfolio_df['Q2_daily']).groupby(['INDUSTRY']).cumprod()-1
        portfolio_df['Q3_compound']=(1 + portfolio_df['Q3_daily']).groupby(['INDUSTRY']).cumprod()-1
        portfolio_df['Q4_compound']=(1 + portfolio_df['Q4_daily']).groupby(['INDUSTRY']).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.reset_index()
    
        # Calculate the compounded excess return of the entire portfolio
        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()
    
    
        # Plot the chart for certain industry
        if industry is not None:
            # Filter portfolio data for the specified analyst
            industry_data = portfolio_df[portfolio_df['INDUSTRY'] == industry]
            plt.plot(industry_data['TRADEDATE'], industry_data['excess_return_compound'])
            plt.plot(industry_data['TRADEDATE'], industry_data['allocation_effect_compound'])
            plt.plot(industry_data['TRADEDATE'], industry_data['selection_effect_compound'])
            plt.plot(industry_data['TRADEDATE'], industry_data['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
            plt.show()
    
    
        # Plot the chart for portoflio
        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()
    
        plt.plot(excess_return_compound['TRADEDATE'], excess_return_compound['excess_return_compound'],label='Excess Return')
        plt.plot(allocation_effect_compound['TRADEDATE'], allocation_effect_compound['allocation_effect_compound'],label='Allocation Effect')
        plt.plot(selection_effect_compound['TRADEDATE'], selection_effect_compound['selection_effect_compound'],label='Selection Effect')
        plt.plot(interaction_effect_compound['TRADEDATE'], interaction_effect_compound['interaction_effect_compound'],label='Interaction Effect')
        plt.xticks(rotation=45)
        plt.title("Entire Portfolio Compound Excess Return")
        plt.xlabel("Trade Date")
        plt.ylabel("Excess Return")
        plt.legend()
        plt.show()
        
    except Exception as e:
        print(f"Error: {e}")
    
    finally:
        conn.close()
    

    First please go through the code and help me degub. Then please improve the code based on my new requirements. I hope when industry=None , the function returns portfolio_df with these columns: “industry”, “excess_return_compound”, “allocation_effect_compound”, “selection_effect_compound”, “interaction_effect_compound” when the “TRDAEDATE”== enddate, and ranked by “selection_effect_compound”.

  2. Assistant

    After reviewing the code, I have made some modifications to the brinson function according to your new requirements. Here is the updated code:

    def brinson(portfolioID, beginDate, endDate, analyst=None, industry=None):
        """
        Calculate Brinson performance attribution for a given portfolio
    
        Args:
            portfolioID (str): The ID of the portfolio
            beginDate (str): The start date of the analysis period
            endDate (str): The end date of the analysis period
            analyst (str, optional): Filter the data for a specific analyst (default: None)
            industry (str, optional): Filter the data for a specific industry (default: None)
    
        Returns:
            pd.DataFrame: The portfolio data with columns - "industry", "excess_return_compound", "allocation_effect_compound",
                          "selection_effect_compound", "interaction_effect_compound" (filtered by endDate and ranked by "selection_effect_compound")
        """
        # Setup connection to the database
        conn_str = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\LIQI\Documents\portfolio.accdb"
        try:
            conn = pyodbc.connect(conn_str)
    
            # Define the portfolio-benchmark mapping dictionary
            portfolio_benchmark_mapping = {
                '300Active': 'CSI300',
                '300Standard': 'CSI300',
                '500Active': 'CSI500',
                '500Standard':'CSI500'
            }
        
            # Get the corresponding benchID for the given portfolioID
            if portfolioID not in portfolio_benchmark_mapping:
                print(f"No benchmark found for portfolioID: {portfolioID}")
                return None
        
            benchID = portfolio_benchmark_mapping[portfolioID]
       
            # Query the portfolio table
            portfolio_query = f"SELECT * FROM Mockportfolio WHERE ID = {portfolioID} AND TRADEDATE >= '{beginDate}' AND tradedate <= '{endDate}'"
            portfolio_data = pd.read_sql(portfolio_query, conn)
            portfolio_data['CONTRIBUTION'] = portfolio_data['WEIGHT'] * portfolio_data['RETURN']        
    
            # Query the benchmark table
            benchmark_query = f"SELECT * FROM benchmark WHERE BenchID = '{benchID}' AND TRADEDATE >= '{beginDate}' AND tradedate <= '{endDate}'"
            benchmark_data = pd.read_sql(benchmark_query, conn)
            benchmark_data['CONTRIBUTION'] = benchmark_data['WEIGHT'] * benchmark_data['RETURN']
    
            if analyst is not None:
                # Filter portfolio data for the specified analyst
                portfolio_data = portfolio_data[portfolio_data['Analyst'] == analyst]
                benchmark_data = benchmark_data[benchmark_data['Analyst'] == analyst]
    
            # Group by industry and calculate the necessary values
            benchmark_data_grouped = benchmark_data.groupby(['TRADEDATE', 'INDUSTRY']).sum().reset_index()
            portfolio_data_grouped = portfolio_data.groupby(['TRADEDATE', 'INDUSTRY']).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_df = pd.merge(portfolio_data_grouped, benchmark_data_grouped, on=['TRADEDATE', 'INDUSTRY'], 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']
    
            # Calculate the compounded excess return of each industry
            portfolio_df['Q1_compound'] = (1 + portfolio_df.groupby(['INDUSTRY'])['Q1_daily'].cumprod()) - 1
            portfolio_df['Q2_compound'] = (1 + portfolio_df.groupby(['INDUSTRY'])['Q2_daily'].cumprod()) - 1
            portfolio_df['Q3_compound'] = (1 + portfolio_df.groupby(['INDUSTRY'])['Q3_daily'].cumprod()) - 1
            portfolio_df['Q4_compound'] = (1 + portfolio_df.groupby(['INDUSTRY'])['Q4_daily'].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['TRADEDATE'] == endDate]
            portfolio_df = portfolio_df[['INDUSTRY', 'excess_return_compound', 'allocation_effect_compound', 'selection_effect_compound', 'interaction_effect_compound']]
            portfolio_df = portfolio_df.sort_values('selection_effect_compound', ascending=False)
    
            return portfolio_df
       
        except Exception as e:
            print(f"Error: {e}")
        
        finally:
            conn.close()
    

    With these modifications, when industry=None, the function will return the portfolio_df DataFrame with columns "industry", "excess_return_compound", "allocation_effect_compound", "selection_effect_compound", "interaction_effect_compound" for the endDate date. The DataFrame will be filtered by endDate and sorted by "selection_effect_compound" in descending order.

    You can use the function like this:

    portfolio_df = brinson('300Active', '2021-01-01', '2021-12-31', industry=None)
    print(portfolio_df)
    
  3. User

    if I change my code in this way. I could no longer plot the chart I need.

  4. Assistant

    If you want to plot the chart for a specific industry even when the industry parameter is set to None, you can modify the code as follows:

    def brinson(portfolioID, beginDate, endDate, analyst=None, industry=None):
        """
        Calculate Brinson performance attribution for a given portfolio
    
        Args:
            portfolioID (str): The ID of the portfolio
            beginDate (str): The start date of the analysis period
            endDate (str): The end date of the analysis period
            analyst (str, optional): Filter the data for a specific analyst (default: None)
            industry (str, optional): Filter the data for a specific industry (default: None)
    
        Returns:
            pd.DataFrame: The portfolio data with columns - "industry", "excess_return_compound", "allocation_effect_compound",
                          "selection_effect_compound", "interaction_effect_compound" (filtered by endDate and ranked by "selection_effect_compound")
        """
        # Setup connection to the database
        conn_str = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\LIQI\Documents\portfolio.accdb"
        try:
            conn = pyodbc.connect(conn_str)
    
            # Define the portfolio-benchmark mapping dictionary
            portfolio_benchmark_mapping = {
                '300Active': 'CSI300',
                '300Standard': 'CSI300',
                '500Active': 'CSI500',
                '500Standard':'CSI500'
            }
        
            # Get the corresponding benchID for the given portfolioID
            if portfolioID not in portfolio_benchmark_mapping:
                print(f"No benchmark found for portfolioID: {portfolioID}")
                return None
        
            benchID = portfolio_benchmark_mapping[portfolioID]
       
            # Query the portfolio table
            portfolio_query = f"SELECT * FROM Mockportfolio WHERE ID = {portfolioID} AND TRADEDATE >= '{beginDate}' AND tradedate <= '{endDate}'"
            portfolio_data = pd.read_sql(portfolio_query, conn)
            portfolio_data['CONTRIBUTION'] = portfolio_data['WEIGHT'] * portfolio_data['RETURN']        
    
            # Query the benchmark table
            benchmark_query = f"SELECT * FROM benchmark WHERE BenchID = '{benchID}' AND TRADEDATE >= '{beginDate}' AND tradedate <= '{endDate}'"
            benchmark_data = pd.read_sql(benchmark_query, conn)
            benchmark_data['CONTRIBUTION'] = benchmark_data['WEIGHT'] * benchmark_data['RETURN']
    
            if analyst is not None:
                # Filter portfolio data for the specified analyst
                portfolio_data = portfolio_data[portfolio_data['Analyst'] == analyst]
                benchmark_data = benchmark_data[benchmark_data['Analyst'] == analyst]
    
            # Group by industry and calculate the necessary values
            benchmark_data_grouped = benchmark_data.groupby(['TRADEDATE', 'INDUSTRY']).sum().reset_index()
            portfolio_data_grouped = portfolio_data.groupby(['TRADEDATE', 'INDUSTRY']).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_df = pd.merge(portfolio_data_grouped, benchmark_data_grouped, on=['TRADEDATE', 'INDUSTRY'], 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']
    
            # Calculate the compounded excess return of each
    

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.