如何在Excel中高效生成多证券账户年度收益（业绩）表？

我有一组含日期（Date）、账户（Account）、**价值（Value）**的数据集，共8500行，记录为每年首日或月度间隔数据。需要生成年度收益表，其中当年业绩需显示YTD（年初至今）：

• 收益计算公式：(期末值 - 年初值) / 年初值
• 若为当前年份（如2024年6月），年末值未知，采用最新日期的数值计算
• 我原本的思路是先筛选对应账户的时段数据，再用MIN/MAX函数判断日期来计算全年收益或YTD，但数据集较大，想找更优雅、计算量更低的实现方式

示例数据集 #

目标年度收益表 #

方案1：SQL实现（适合数据库存储的数据集） #

核心用窗口函数分组计算，仅需一次扫描数据集，避免多次筛选：

1. 提取每条记录的年份，标记当前年份
2. 用FIRST_VALUE窗口函数获取每个账户每年的年初值（该年最早日期的Value）
3. 用条件逻辑区分当前年与历史年，分别取最新值/年末值作为期末值
4. 计算收益率后转置成目标表格

示例SQL代码（MySQL）：

WITH yearly_data AS (
    SELECT
        Account,
        YEAR(STR_TO_DATE(Date, '%d/%m/%Y')) AS Year,
        Value,
        Date,
        -- 获取当年年初值
        FIRST_VALUE(Value) OVER (PARTITION BY Account, YEAR(STR_TO_DATE(Date, '%d/%m/%Y')) ORDER BY STR_TO_DATE(Date, '%d/%m/%Y')) AS Year_Start_Value,
        -- 标记当前年份
        CASE WHEN YEAR(STR_TO_DATE(Date, '%d/%m/%Y')) = YEAR(CURDATE()) THEN 1 ELSE 0 END AS Is_Current_Year
    FROM your_dataset
),
yearly_end_values AS (
    SELECT
        Account,
        Year,
        Year_Start_Value,
        -- 非当前年取年末值，当前年取最新值
        MAX(CASE 
            WHEN Is_Current_Year = 1 THEN Value 
            ELSE CASE WHEN Date = CONCAT('01/01/', Year + 1) THEN LAG(Value) OVER (PARTITION BY Account ORDER BY Year) ELSE NULL END
        END) OVER (PARTITION BY Account, Year) AS Year_End_Value
    FROM yearly_data
)
-- 计算收益率并去重
SELECT DISTINCT
    Account,
    Year,
    ROUND(((Year_End_Value - Year_Start_Value) / Year_Start_Value) * 100, 2) AS Return_Percent
FROM yearly_end_values
ORDER BY Account, Year;

后续可通过SQL的PIVOT功能（如PostgreSQL的crosstab、MySQL动态SQL）转置成目标表格格式。

方案2：Python Pandas实现（适合本地数据集） #

利用分组聚合和向量化操作，避免循环，提升处理效率：

1. 转换日期格式并提取年份
2. 按账户+年份分组，自定义聚合逻辑计算年初值与期末值
3. 计算收益率后转置成透视表

示例代码：

import pandas as pd
from datetime import datetime

# 读取数据（替换为你的数据源路径）
df = pd.read_csv('your_data.csv')
# 转换日期格式
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
# 提取年份
df['Year'] = df['Date'].dt.year

# 获取当前年份
current_year = datetime.now().year

# 自定义聚合函数：计算单账户单年度的年初/期末值
def calc_yearly_values(group):
    year = group['Year'].iloc[0]
    # 年初值：当年最早日期的Value
    year_start = group.loc[group['Date'] == group['Date'].min(), 'Value'].iloc[0]
    # 期末值：当前年取最新值，历史年取当年最晚日期值
    year_end = group['Value'].iloc[-1] if year == current_year else group.loc[group['Date'] == group['Date'].max(), 'Value'].iloc[0]
    return pd.Series({'Year_Start': year_start, 'Year_End': year_end})

# 分组计算年度指标
yearly_stats = df.groupby(['Account', 'Year']).apply(calc_yearly_values).reset_index()
# 计算收益率
yearly_stats['Return_Percent'] = round(((yearly_stats['Year_End'] - yearly_stats['Year_Start']) / yearly_stats['Year_Start']) * 100, 2)
# 转置成目标表格格式，格式化百分比
result_table = yearly_stats.pivot(index='Account', columns='Year', values='Return_Percent').fillna(0)
result_table = result_table.applymap(lambda x: f"{x:.2f}%".replace('.', ','))

print(result_table)

优化要点 #

• 减少数据集扫描：用窗口函数（SQL）或一次分组（Pandas）完成所有计算，避免多次筛选
• 向量化操作：Pandas中优先使用内置分组聚合函数，避免循环遍历
• 索引优化：SQL给Account+Date建联合索引；Pandas设置Account+Year为索引，提升分组效率

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