import pandas as pd import matplotlib.pyplot as plt import pyodbc def…
- 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”.
- Assistant
After reviewing the code, I have made some modifications to the
brinsonfunction 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 theportfolio_dfDataFrame with columns "industry", "excess_return_compound", "allocation_effect_compound", "selection_effect_compound", "interaction_effect_compound" for theendDatedate. The DataFrame will be filtered byendDateand 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) - User
if I change my code in this way. I could no longer plot the chart I need.
- Assistant
If you want to plot the chart for a specific industry even when the
industryparameter is set toNone, 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
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.