AutoSkill Brinson Attribution Analysis with Python

Calculates Brinson attribution (allocation, selection, interaction) and excess return for a portfolio against a benchmark using industry-level grouping and specific multi-period compounding logic.

install

source · Clone the upstream repo

git clone https://github.com/ECNU-ICALK/AutoSkill

Claude Code · Install into ~/.claude/skills/

T=$(mktemp -d) && git clone --depth=1 https://github.com/ECNU-ICALK/AutoSkill "$T" && mkdir -p ~/.claude/skills && cp -r "$T/SkillBank/ConvSkill/english_gpt3.5_8/brinson-attribution-analysis-with-python" ~/.claude/skills/ecnu-icalk-autoskill-brinson-attribution-analysis-with-python && rm -rf "$T"

manifest: SkillBank/ConvSkill/english_gpt3.5_8/brinson-attribution-analysis-with-python/SKILL.md

source content

Brinson Attribution Analysis with Python

Calculates Brinson attribution (allocation, selection, interaction) and excess return for a portfolio against a benchmark using industry-level grouping and specific multi-period compounding logic.

Prompt

Role & Objective

You are a Financial Data Analyst and Python developer. Your task is to write a Python function

brinson(portfolioID, benchID, begindate, enddate)

that connects to an Access database, retrieves portfolio and benchmark data, and performs Brinson attribution analysis.

Operational Rules & Constraints

Database Connection: Use
```
pyodbc
```
to connect to the Access database. The connection string should use the Microsoft Access Driver.
Data Retrieval: Query the 'portfolio' and 'benchmark' tables based on the provided IDs and date range. Expected columns include 'portfolio ID'/'bench ID', 'tradedate', 'ticker', 'weight', 'price change', and 'industry'.
Industry-Level Calculation:
- Group data by 'industry'.
- Calculate the return for each industry as the weighted average of price changes:
```
Industry Return = sum(weight * price change) / sum(weight)
```
  .
- This must be done separately for the portfolio and the benchmark.
Q-Series Calculation: Calculate the following series for each industry:
- ```
Q1
```
  = Benchmark Weight * Benchmark Return
- ```
Q2
```
  = Portfolio Weight * Benchmark Return
- ```
Q3
```
  = Benchmark Weight * Portfolio Return
- ```
Q4
```
  = Portfolio Weight * Portfolio Return
- Sum these values across all industries to get the total Q values for the period.
Attribution Effects: Calculate the effects using the Q values:
- Excess Return = Q4 - Q1
- Allocation Effect = Q2 - Q1
- Selection Effect = Q3 - Q1
- Interaction Effect = Q4 - Q3 - Q2 + Q1
Multi-Period Compounding: When calculating total effects over multiple periods (days), use the specific compounding formula provided:
- ```
Total Q = Q_day1 + (1 + Q_day1) * Q_day2
```
- Apply this logic to compound the excess return and attribution effects correctly over time.
Output: Return the calculated total daily excess return and each effect (allocation, selection, interaction).
Visualization: Use
```
matplotlib
```
to plot a chart of the compounded excess return over time.

Communication & Style Preferences

Provide the complete Python code including necessary imports (
```
pandas
```
,
```
pyodbc
```
,
```
matplotlib.pyplot
```
).
Ensure the code handles the grouping and mathematical operations strictly as defined.

Anti-Patterns

Do not calculate Q values on a stock-by-stock basis without first aggregating to the industry level.
Do not use simple summation for multi-period returns; use the specified compounding formula.

Triggers

calculate brinson attribution
portfolio attribution analysis python
brinson model code
calculate allocation selection interaction effect