Python 30个高效处理财务Excel数据的自动化脚本

import pandas as pd
df = pd.read_excel('financial_data.xlsx')

df.to_excel('processed_data.xlsx', index=False)

selected = df[['Date', 'Revenue']]

filtered = df[df['Revenue'] > 1000]

df_clean = df.dropna()

df_filled = df.fillna(0)  # 或 df.fillna(df.mean())

df.rename(columns={'OldName': 'NewName'}, inplace=True)

total_revenue = df['Revenue'].sum()
avg_cost = df['Cost'].mean()

df['Pct_Change'] = df['Revenue'].pct_change() * 100

df['Cumulative_Revenue'] = df['Revenue'].cumsum()

df['YoY_Growth'] = df['Revenue'].pct_change(periods=12) * 100  # 假设月度数据

df['Debt_Ratio'] = df['Total_Liabilities'] / df['Total_Assets']
df['Net_Margin'] = df['Net_Income'] / df['Revenue']

pivot = pd.pivot_table(df, values='Revenue', index='Category', aggfunc='sum')

import glob
files = glob.glob('*.xlsx')
combined = pd.concat([pd.read_excel(f) for f in files])

for category, group in df.groupby('Category'):
      group.to_excel(f'{category}_report.xlsx', index=False)

df['Date'] = pd.to_datetime(df['Date'])
df_sorted = df.sort_values(by='Date')

grouped = df.groupby('Category').agg({'Revenue': 'mean', 'Cost': 'sum'})

df_unique = df.drop_duplicates()

plt.plot(df['Date'], df['Revenue'])
plt.title('月度收入趋势')

plt.bar(df['Category'], df['Revenue'])

plt.pie(df['Revenue'], labels=df['Category'], autopct='%1.1f%%'

sns.boxplot(x='Category', y='Revenue', data=df)

sns.heatmap(df.corr(), annot=True)

with open('report.txt', 'w') as f:
    f.write(f"总收入: {total_revenue}\n净利润率: {net_margin:.2%}")

cashflows = [-1000, 300, 300, 300]  # 初始投资+现金流
npv = np.npv(0.05, cashflows)  # 贴现率5%

irr = np.irr(cashflows) * 100  # 转换为百分比

df['ROE'] = (df['Net_Income'] / df['Revenue']) * (df['Revenue'] / df['Assets']) * (df['Assets'] / df['Equity'])

df['Free_Cash_Flow'] = df['Operating_Cash_Flow'] - df['Capital_Expenditure']

from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
df['Revenue_Std'] = scaler.fit_transform(df[['Revenue']])

def analyze_financials(file_path):
      df = pd.read_excel(file_path)
    # 添加清洗、计算、可视化步骤
      return analysis_report