如何为非连续日期的季度持有期回报（HPR）定义百分比变化函数并批量计算多只债券季度HPR

Awesome question—manual calculation for 180 bond-quarter combinations is tedious and error-prone. Let's use pandas to automate this entirely, leveraging your existing large DataFrame instead of splitting into individual bond DataFrames. Here's how to do it:

Step 1: Preprocess Your Data #

First, we need to fix the date format and add a quarterly identifier to group our data properly:

import pandas as pd

# Convert the 'date' column to datetime (your format is day/month/year, so use dayfirst=True)
df['date'] = pd.to_datetime(df['date'], dayfirst=True)

# Sort the DataFrame by bond (isin) and date to ensure we're grabbing the correct start/end prices
df = df.sort_values(['isin', 'date'])

# Add a column for the quarter (e.g., 2013Q1 for Jan-Mar 2013)
df['quarter'] = df['date'].dt.to_period('Q')

Step 2: Calculate Quarterly HPR in Bulk #

Now we'll group the data by each bond and quarter, compute the start/end prices, and calculate the HPR percentage—all in one line:

# Group by bond (isin + asset_name for clarity) and quarter, then compute HPR
quarterly_hpr = df.groupby(['isin', 'asset_name', 'quarter']).agg(
    start_price=('price', 'first'),  # First price in the quarter
    end_price=('price', 'last')      # Last price in the quarter
).assign(
    # Calculate HPR percentage (matches your manual formula exactly)
    hpr_pct=lambda x: 100 * (x['end_price'] - x['start_price']) / x['start_price']
).reset_index()

# View the results
print(quarterly_hpr)

How This Works #

• Date Conversion & Sorting: Ensures we correctly interpret your date format and that each bond's data is ordered chronologically, so first/last prices correspond to the start/end of the quarter.
• Quarterly Grouping: dt.to_period('Q') automatically maps each date to its calendar quarter, so we don't have to manually define date ranges for every quarter.
• Bulk Calculation: The groupby operation handles all 5 bonds and 9 years of data at once, generating 180 HPR values (5 bonds × 9 years × 4 quarters) without any repetitive manual work.

Example Output #

For your CLIFFS NATURAL RESOURCES INC bond (US18683KAC53) in Q1 2013, the code will compute:

100 * (87.073997 - 88.974998) / 88.974998 ≈ -2.14%

Which matches the logic of your manual calculation.

Bonus: Filtering Time Range #

If you want to restrict results to your 2013-2021 time frame, just add a filter before grouping:

# Filter to only include dates between 2013 and 2021
filtered_df = df[(df['date'] >= '2013-01-01') & (df['date'] <= '2021-12-31')]

# Run the grouping calculation on filtered_df instead
quarterly_hpr = filtered_df.groupby(['isin', 'asset_name', 'quarter']).agg(
    start_price=('price', 'first'),
    end_price=('price', 'last')
).assign(
    hpr_pct=lambda x: 100 * (x['end_price'] - x['start_price']) / x['start_price']
).reset_index()

内容的提问来源于stack exchange，提问作者Lauren95