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

def brinson(portfolioID, benchID, beginDate, endDate):

    # Setup connection to the database
    conn_str = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\LIQI\Documents\portfolio.accdb"
    conn = pyodbc.connect(conn_str)
    
    # 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']
    
    conn.close()
    
    # 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
    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()
    # Plot the chart


I finished the brinson attribution function for a portfolio. I want to further improve this. First, I try to imbed a  dictionanry, in which each portfolioID links with an identical benchID, so that I can decrease the number of function above. Would you please help. thanks.