关于xlwings库读取Excel文件，进行计算的一个问题
本帖最后由 shadowmage 于 2024-10-24 18:50 编辑如题，我构建了一个函数，想要用Python帮助我计算一连串数据的XIRR值。这个在python里挺麻烦，需要不断试错，但是Excel本身就有xirr函数。但是运行结果总是不太对。我想知道错在哪
程序代码如下：
import xlwings as xw
def Excel_Xirr(file, sheet_name, date_col, cash_col, present_value_col, start_cell):
wb = xw.Book(file)
if '计算' in wb.sheet_names:
sheet = wb.sheets['计算']
sheet.api.Cells.Clear()
else:
sheet = wb.sheets.add('计算')
data_sheet = wb.sheets
start_row = 2
end_row = data_sheet.api.Cells(data_sheet.api.Rows.Count, date_col).End(xlUp).Row
for i in range(start_row, end_row + 1):
sheet.api.Cells.Clear()
# 复制日期和现金流数据到'计算' sheet
date_range = data_sheet.api.Range(f"{date_col}{i}:{date_col}{i}").Value
cash_range = data_sheet.api.Range(f"{cash_col}{i}:{cash_col}{i}").Value
present_value = data_sheet.api.Range(f"{present_value_col}{i}").Value
# 复制第一笔现金流的数据
sheet.api.Range('A1').Value = date_range
sheet.api.Range('B1').Value = cash_range
# 复制后续现金流的数据
date_range_next = data_sheet.api.Range(f"{date_col}{i + 1}:{date_col}{end_row}").Value
cash_range_next = data_sheet.api.Range(f"{cash_col}{i + 1}:{cash_col}{end_row}").Value
for j, (date, cash) in enumerate(zip(date_range_next, cash_range_next), start=2):
sheet.api.Range(f'A{j}').Value = date
sheet.api.Range(f'B{j}').Value = cash
if cash_range_next[-1] != 0:
sheet.api.Range(f'B{j + 1}').Value = cash_range_next[-1] + present_value
else:
sheet.api.Range(f'B{j + 1}').Value = present_value
sheet.api.Range(f'C{j}').Formula = '=XIRR(B1:B{j + 1}, A1:A{j + 1})'
data_sheet.api.Range(f"{start_cell}{i}").Value = sheet.api.Range(f'C{j}').Value
# 保存并关闭Excel文件
wb.save(file)
wb.close()
# 使用示例
Excel_Xirr('现金流示例2.xlsx', '006593', 'F', 'G', 'P', 'K3')
论坛没法上传xlsx，只能把截图发上来。
你的代码有一些地方需要调整，以下是修正后的代码以及一些简化和优化：
import xlwings as xw
def Excel_Xirr(file, sheet_name, date_col, cash_col, present_value_col, start_cell):
wb = xw.Book(file)
# 检查并选择正确的 sheet
if '计算' in wb.sheets:
sheet = wb.sheets['计算']
sheet.clear_contents()
else:
sheet = wb.sheets.add('计算')
data_sheet = wb.sheets
start_row = 2
end_row = data_sheet.range(f'{date_col}1').end('down').row
for i in range(start_row, end_row + 1):
sheet.clear_contents()
# 复制日期和现金流数据到'计算' sheet
date = data_sheet.range(f"{date_col}{i}").value
cash = data_sheet.range(f"{cash_col}{i}").value
present_value = data_sheet.range(f"{present_value_col}{i}").value
sheet.range('A1').value = date
sheet.range('B1').value = cash
for j in range(i + 1, end_row + 1):
next_date = data_sheet.range(f"{date_col}{j}").value
next_cash = data_sheet.range(f"{cash_col}{j}").value
sheet.range(f'A{j-i+1}').value = next_date
sheet.range(f'B{j-i+1}').value = next_cash
if cash != 0:
sheet.range(f'B{end_row-i+2}').value = cash + present_value
else:
sheet.range(f'B{end_row-i+2}').value = present_value
# 使用 Excel 公式 XIRR
xirr_formula = f'=XIRR(B1:B{end_row-i+2}, A1:A{end_row-i+2})'
sheet.range(f'C1').formula = xirr_formula
# 将结果写回原始表
data_sheet.range(f"{start_cell}{i}").value = sheet.range('C1').value
# 保存并关闭Excel文件
wb.save()
wb.close()
# 使用示例
Excel_Xirr('现金流示例2.xlsx', '006593', 'F', 'G', 'P', 'K3')
注意事项：
1. 确保 `xlwings` 和 Excel 都安装正确。
2. 确保 Excel 的计算功能正常。
3. 使用 `range` 而不是 `api`，代码更简洁。
4. 检查数据是否按照预期在 Excel 中。
5. 避免频繁清空 sheet 内容，直接使用新的起始位置。
请根据具体需求调整代码。
以上回复来自 FishC AI 小助理，如未能正确解答您的问题，请继续追问。 FishC 发表于 2024-10-24 18:54
你的代码有一些地方需要调整，以下是修正后的代码以及一些简化和优化：
实际上，刚刚发现，这个计算xirr的功能，还是用VBA实现更靠谱一点 shadowmage 发表于 2024-11-01 23:48
实际上，刚刚发现，这个计算xirr的功能，还是用VBA实 ...
确实，使用VBA来计算XIRR可能会更直接和高效。以下是一个简单的VBA代码示例，可以帮助你在Excel中计算XIRR：
1. 打开Excel，按 `Alt + F11` 打开VBA编辑器。
2. 在VBA编辑器中，选择 `Insert` > `Module`，然后在模块中粘贴以下代码：
Function CalculateXIRR(dates As Range, cashFlows As Range) As Double
On Error GoTo ErrorHandler
CalculateXIRR = Application.WorksheetFunction.Xirr(cashFlows, dates)
Exit Function
ErrorHandler:
CalculateXIRR = CVErr(xlErrValue)
End Function
3. 返回Excel工作表，在单元格中输入公式 `=CalculateXIRR(A1:A10, B1:B10)`，其中 `A1:A10` 是日期范围，`B1:B10` 是现金流范围。
这个VBA函数会调用Excel内置的XIRR函数来计算结果，并且在遇到错误时返回一个错误值。这样可以避免在Python中处理复杂的Excel操作，同时利用Excel的内置功能来简化计算过程。
以上回复来自 FishC AI 小助理，如未能正确解答您的问题，请继续追问。
页:
[1]