以下代码(1)报错:C:\Users\zh\AppData\Roaming\JetBrains\PyCharm2024.1\scratches\scratch_1.py:165: DeprecationWarning: Call to deprecated function create_named_range (Assign scoped named ranges directly to worksheets or global ones to the workbook. Deprecated in 3.1).
wb.create_named_range(
尝试修复(这个脚本会读取原始Excel文件,添加用于计算ESG得分的各列,并设置公式结构。最终结果将保存为3.xlsx,所有计算将在Excel中执行。)
(1):
```python
import pandas as pd
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill
def add_esg_formulas(input_path, output_path):
# 加载工作簿和工作表
wb = openpyxl.load_workbook(input_path)
ws = wb.active
# 添加新列标题
new_columns = [
"基础ESG得分",
"行业系数",
"动态进步分",
"供应链分",
"垄断矫正分",
"数据异常扣分",
"总分",
"MSCI转换分",
"晨星转换分",
"标普转换分",
"华证转换分",
"中证转换分",
"Wind转换分",
"Wind评级提升分",
"减排技术研发投入率★",
"Tier1供应商合规率★",
"碳强度年降幅★",
"赫芬达尔指数★",
"平台佣金率★",
"社会议题投入占比★"
]
start_col = ws.max_column + 1
for i, col_name in enumerate(new_columns):
col_letter = get_column_letter(start_col + i)
ws[f"{col_letter}1"] = col_name
# 为需要补充数据的列添加黄色背景
if "★" in col_name:
for row in range(2, ws.max_row + 1):
ws[f"{col_letter}{row}"].fill = PatternFill(
start_color="FFFF00", end_color="FFFF00", fill_type="solid"
)
# 设置公式
for row in range(3, ws.max_row + 1): # 从第3行开始(数据行)
# 字母评级转换公式
letter_rating_formula = (
f'IF(ISBLANK(B{row}), "", '
f'IF(B{row}="AAA",9,'
f'IF(B{row}="AA",8,'
f'IF(B{row}="A",7,'
f'IF(B{row}="BBB",6,'
f'IF(B{row}="BB",5,'
f'IF(B{row}="B",4,'
f'IF(B{row}="CCC",3,3)))))))'
)
# 晨星评分转换公式
morningstar_formula = (
f'IF(ISBLANK(E{row}), "", '
f'IF(E{row}>=40,9,'
f'IF(E{row}>=30,7,'
f'IF(E{row}>=20,6,'
f'IF(E{row}>=10,5,3))))'
)
# 基础ESG得分公式
base_esg_formula = (
f'=((IFERROR({get_column_letter(start_col+6)}{row},0)+'
f'IFERROR({get_column_letter(start_col+7)}{row},0)+'
f'IFERROR({get_column_letter(start_col+8)}{row},0)+'
f'IFERROR({get_column_letter(start_col+9)}{row},0)+'
f'IFERROR({get_column_letter(start_col+10)}{row},0)+'
f'IFERROR({get_column_letter(start_col+11)}{row},0))/'
f'MAX(1,COUNT({get_column_letter(start_col+6)}{row},'
f'{get_column_letter(start_col+7)}{row},'
f'{get_column_letter(start_col+8)}{row},'
f'{get_column_letter(start_col+9)}{row},'
f'{get_column_letter(start_col+10)}{row},'
f'{get_column_letter(start_col+11)}{row})))*0.4'
)
# 行业系数公式
industry_formula = (
f'=IF(OR(T{row}="能源类",T{row}="工业类"),'
f'IF({get_column_letter(start_col+15)}{row}>='
f'IF(T{row}="能源类",0.08,0.05),1.2,0.9),'
f'IF(OR(T{row}="科技类",T{row}="消费类"),'
f'IF({get_column_letter(start_col+18)}{row}>='
f'IF(T{row}="科技类",0.025,0.018),1.1,1.0),1.0))'
)
# Wind评级提升分公式
wind_improve_formula = (
f'=((IF(AND(NOT(ISBLANK(S{row})),NOT(ISBLANK(R{row}))),'
f'MAX(VLOOKUP(R{row},RatingTable,2,0)-VLOOKUP(S{row},RatingTable,2,0),0),0)+'
f'IF(AND(NOT(ISBLANK(R{row})),NOT(ISBLANK(Q{row}))),'
f'MAX(VLOOKUP(Q{row},RatingTable,2,0)-VLOOKUP(R{row},RatingTable,2,0),0),0))/3)*10'
)
# 动态进步分公式
progress_formula = (
f'={get_column_letter(start_col+12)}{row}+'
f'MIN(10,({get_column_letter(start_col+13)}{row}/1.8)*10)+'
f'MIN(10,({get_column_letter(start_col+14)}{row}/0.7)*10)'
)
# 垄断矫正分公式
monopoly_formula = (
f'=IF(AND(OR(T{row}="金融类",T{row}="能源类"),U{row}="国企"),'
f'-5*{get_column_letter(start_col+16)}{row},'
f'IF(AND(OR(T{row}="科技类",T{row}="消费类"),'
f'IF({get_column_letter(start_col+17)}{row}>0.1,-3*{get_column_letter(start_col+17)}{row},0),0))'
)
# 数据异常扣分公式
penalty_formula = (
f'=IF(ABS('
f'AVERAGE({get_column_letter(start_col+6)}{row},{get_column_letter(start_col+8)}{row})'
f'-AVERAGE({get_column_letter(start_col+9)}{row},{get_column_letter(start_col+10)}{row},{get_column_letter(start_col+11)}{row})'
f')>=2,-3,0)'
)
# 总分公式
total_formula = (
f'=({get_column_letter(start_col)}{row}*{get_column_letter(start_col+1)}{row})'
f'+{get_column_letter(start_col+2)}{row}'
f'+{get_column_letter(start_col+3)}{row}'
f'+{get_column_letter(start_col+4)}{row}'
f'+{get_column_letter(start_col+5)}{row}'
)
# 写入公式
ws[f"{get_column_letter(start_col+6)}{row}"] = letter_rating_formula # MSCI转换分
ws[f"{get_column_letter(start_col+7)}{row}"] = morningstar_formula # 晨星转换分
for col_offset in [8, 9, 10, 11]: # 标普/华证/中证/Wind转换分
ws[f"{get_column_letter(start_col+col_offset)}{row}"] = letter_rating_formula.replace("B{row}", get_column_letter(2+col_offset-8)+str(row))
ws[f"{get_column_letter(start_col)}{row}"] = base_esg_formula # 基础ESG得分
ws[f"{get_column_letter(start_col+1)}{row}"] = industry_formula # 行业系数
ws[f"{get_column_letter(start_col+12)}{row}"] = wind_improve_formula # Wind评级提升分
ws[f"{get_column_letter(start_col+2)}{row}"] = progress_formula # 动态进步分
ws[f"{get_column_letter(start_col+4)}{row}"] = monopoly_formula # 垄断矫正分
ws[f"{get_column_letter(start_col+5)}{row}"] = penalty_formula # 数据异常扣分
ws[f"{get_column_letter(start_col+6)}{row}"] = total_formula # 总分
# 创建评级转换表
ws["A1000"] = "评级转换表"
ratings = ["AAA", "AA", "A", "BBB", "BB", "B", "CCC"]
scores = [9, 8, 7, 6, 5, 4, 3]
for i, (rating, score) in enumerate(zip(ratings, scores), start=1001):
ws[f"A{i}"] = rating
ws[f"B{i}"] = score
# 定义名称"RatingTable"引用这个区域
if "RatingTable" not in wb.defined_names:
wb.create_named_range(
"RatingTable",
ws,
f"$A$1001:$B${1000+len(ratings)}"
)
# 添加说明文本
ws["A1050"] = "★需要手动补充的数据项:"
ws["A1051"] = "1. 减排技术研发投入率 = (自主研发减碳技术投入/总营收)"
ws["A1052"] = "2. Tier1供应商ESG合规率 = 接入区块链碳管理平台的供应商比例"
ws["A1053"] = "3. 碳强度年降幅 = (上年碳排放强度 - 本年碳排放强度)/上年碳排放强度"
ws["A1054"] = "4. 赫芬达尔指数(HHI) = Σ(企业市场份额)^2 (金融/能源类国企填写)"
ws["A1055"] = "5. 平台商户佣金率 (科技/消费类填写)"
ws["A1056"] = "6. 社会议题投入占比 = 数据隐私/安全投入/总营收"
# 保存工作簿
wb.save(output_path)
# 执行函数
input_file = "D:/2.xlsx"
output_file = "D:/3.xlsx"
add_esg_formulas(input_file, output_file)
```
### 功能说明:
1. **添加的列**:
- 基础ESG得分、行业系数、动态进步分等核心计算列
- 各评级机构的转换分列
- 带★号的外部数据补充列(标记为黄色背景)
- 总分列
2. **核心公式实现**:
- **基础ESG得分**:自动转换各机构评级为分数,计算平均值后乘以40%
- **行业系数**:根据行业类型和补充数据动态调整
- **动态进步分**:包含Wind评级提升、减排技术投入和供应链进步
- **垄断矫正**:针对金融/能源国企和科技/消费平台企业
- **数据异常扣分**:检测国内外评级差异
3. **特殊处理**:
- 创建评级转换表(AAA→9分,AA→8分,...,CCC→3分)
- 添加详细的数据补充说明(A1050-A1056)
- 黄色背景标记需要手动补充的数据单元格
4. **使用说明**:
- 在黄色标记的★列补充相应数据
- 总分列会自动计算最终ESG得分
- ≥75分表示高概率上榜福布斯ESG 50
此脚本保留了原始设计的所有核心逻辑,同时确保所有计算都在Excel中执行。用户只需在黄色单元格补充外部数据,即可自动生成最终ESG评分。
最新发布