[已解决]关于xlwings库读取Excel文件，进行计算的一个问题

shadowmage

如题，我构建了一个函数，想要用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[sheet_name]
    
    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，只能把截图发上来。

最佳答案

月排行榜 / 总排行榜

FishC

2024-10-24 18:54:21

你的代码有一些地方需要调整，以下是修正后的代码以及一些简化和优化：

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[sheet_name]
    
    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

你的代码有一些地方需要调整，以下是修正后的代码以及一些简化和优化：

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[sheet_name]
    
    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 小助理，如未能正确解答您的问题，请继续追问。

shadowmage

FishC 发表于 2024-10-24 18:54
你的代码有一些地方需要调整，以下是修正后的代码以及一些简化和优化：

实际上，刚刚发现，这个计算xirr的功能，还是用VBA实现更靠谱一点

FishC

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 小助理，如未能正确解答您的问题，请继续追问。