通过Python驱动Excel调用Bloomberg数据时宏报错问题排查

用户通过Python的win32com库驱动Excel，从Bloomberg终端获取固定收益类数据，已准备好Ticker代码列表，创建Excel工作簿并写入首列后，运行时触发宏相关报错，即使启用宏仍无法解决。

原代码 #

import win32com.client
import xlsxwriter
import xlwings as xl
import datetime

path = 'mypath/TempData.xlsx'
work_sheet_name = "Data"

Excelworkbook = xlsxwriter.Workbook(path)
worksheet = Excelworkbook.add_worksheet(work_sheet_name)

#List of tickers
Tickers = ['1124Z MK EQUITY','TD CN EQUITY','MIZC JP EQUITY','N91 LN EQUITY',
              'COST US EQUITY','6857Z LN EQUITY','DZBK GR EQUITY','55601Z US EQUITY','BMO CN EQUITY']

worksheet.write(0, 0, 'Tickers')

row = 1
col = 0

for ticker in Tickers:    
    worksheet.write(row, col,ticker)   
    row += 1

Excelworkbook.close()

#Connect to Excel.
bb = 'C:/blp/API/Office Tools/BloombergUI.xla'
xl = win32com.client.DispatchEx("Excel.Application")  
xl.Workbooks.Open(bb)
xl.AddIns("Bloomberg Excel Tools").Installed = False

wb = xl.Workbooks.Open(Filename=path) 
data_Sheet = wb.Worksheets(work_sheet_name)

xl.Visible = False
xl.EnableEvents = False
xl.DisplayAlerts = False 

# Open the Excel file and read the tickers from the first column
wb = xl.Workbooks.Open(Filename=path)
data_Sheet = wb.Worksheets(work_sheet_name)
max_row = data_Sheet.UsedRange.Rows.Count

tickers = []
for row in range(2, max_row+1):
    ticker = data_Sheet.Cells(row, 1).Value
    tickers.append(ticker)

# Fetch data for each ticker using Bloomberg formulas
for i, ticker in enumerate(tickers):
    # Retrieve the coupon yield
    yield_cell = f'B{i+1}'
    coupon_yield = xl.Run("=@BDP(\"" + ticker + "\" ,\"CPN\"")")
    data_Sheet.Range(yield_cell).Value = coupon_yield
    
    # Retrieve the maturity date
    maturity_cell = f'C{i+1}'
    maturity_raw = xl.Run("=@BDP(\"" + ticker + "\" ,\"MATURITY\"")")
    maturity_date = datetime.strptime(maturity_raw, '%m/%d/%Y').date()
    data_Sheet.Range(maturity_cell).Value = maturity_date

# Save and close the Excel file
wb.Save()
wb.Close()
xl.Quit()

报错信息 #

line 44668, in Run
    return self._ApplyTypes_(259, 1, (12, 0), ((12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12,     self._oleobj_.InvokeTypes(dispid, 0, wFlags, retType, argTypes, *args),
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Cannot run the macro \'=@BDP("1124Z MK EQUITY","CPN")\'. The macro may not be available in this workbook or all macros may be disabled.', 'xlmain11.chm', 0, -2146827284), None)

• BDP函数调用方式错误：BDP是Bloomberg提供的Excel工作表函数，并非VBA宏，不能用xl.Run()执行，Run方法仅用于运行宏，因此Excel提示找不到该宏。
• Bloomberg插件被禁用：代码中xl.AddIns("Bloomberg Excel Tools").Installed = False直接禁用了Bloomberg插件，导致BDP函数无法被Excel识别。
• 重复打开工作簿：代码中两次执行wb = xl.Workbooks.Open(Filename=path)，造成资源冗余，可能引发异常。
• 字符串拼接语法错误：调用xl.Run时的字符串存在括号和引号不匹配问题（"=@BDP(\"" + ticker + "\" ,\"CPN\"")"多了一个闭合的引号和括号）。

1. 直接写入BDP工作表函数 #

不要用Run执行BDP，直接在目标单元格中写入BDP公式，让Excel自动计算数据。

2. 正确加载Bloomberg插件 #

确保Bloomberg Excel插件处于启用状态，无需手动打开.xla文件，直接启用插件即可。

3. 移除重复的工作簿打开操作 #

只打开一次目标工作簿，避免资源冲突。

4. 修正语法错误 #

确保字符串拼接时引号和括号匹配。

修正后的代码 #

import win32com.client
import xlsxwriter
import datetime

path = 'mypath/TempData.xlsx'
work_sheet_name = "Data"

# 第一步：创建Excel并写入Ticker列表
Excelworkbook = xlsxwriter.Workbook(path)
worksheet = Excelworkbook.add_worksheet(work_sheet_name)

Tickers = ['1124Z MK EQUITY','TD CN EQUITY','MIZC JP EQUITY','N91 LN EQUITY',
              'COST US EQUITY','6857Z LN EQUITY','DZBK GR EQUITY','55601Z US EQUITY','BMO CN EQUITY']

# 写入表头
worksheet.write(0, 0, 'Tickers')
worksheet.write(0, 1, 'Coupon')
worksheet.write(0, 2, 'Maturity Date')

# 写入Ticker数据
for row, ticker in enumerate(Tickers, start=1):
    worksheet.write(row, 0, ticker)

Excelworkbook.close()

# 第二步：连接Excel并调用Bloomberg函数
xl = win32com.client.DispatchEx("Excel.Application")
# 启用Bloomberg Excel插件
xl.AddIns("Bloomberg Excel Tools").Installed = True

# 只打开一次工作簿
wb = xl.Workbooks.Open(Filename=path)
data_Sheet = wb.Worksheets(work_sheet_name)

xl.Visible = False
xl.EnableEvents = False
xl.DisplayAlerts = False 

max_row = data_Sheet.UsedRange.Rows.Count

# 为每个Ticker写入BDP公式
for row in range(2, max_row + 1):
    ticker = data_Sheet.Cells(row, 1).Value
    # 写入Coupon的BDP公式
    data_Sheet.Cells(row, 2).Formula = f'=BDP("{ticker}", "CPN")'
    # 写入到期日的BDP公式
    data_Sheet.Cells(row, 3).Formula = f'=BDP("{ticker}", "MATURITY")'

# 等待Excel完成数据刷新（可选，根据网络情况调整等待时间）
xl.CalculateUntilAsyncQueriesDone()

# 保存并关闭
wb.Save()
wb.Close()
xl.Quit()

额外说明 #

• 如果需要将公式结果转为静态值，可以在刷新完成后遍历单元格，执行data_Sheet.Cells(row, 2).Value = data_Sheet.Cells(row, 2).Value
• 确保本地已正确安装Bloomberg终端及Excel插件，且终端处于登录状态

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