import pandas as pd from openpyxl import load_workbook, Workbook from openpyxl.styles import PatternFill, Font, Alignment, Border, Side from openpyxl.utils.dataframe import dataframe_to_rows import numpy as np from datetime import datetime, date import os import calendar class SrikaraSettlementComparison: def __init__(self): # Define colors based on sample report self.hims_collection_fill = PatternFill(fgColor='D5E8D4', fill_type='solid') # Light green self.settlement_fill = PatternFill(fgColor='DAE8FC', fill_type='solid') # Light blue self.net_amount_fill = PatternFill(fgColor='F8CECC', fill_type='solid') # Light red/pink self.charges_fill = PatternFill(fgColor='E1D5E7', fill_type='solid') # Light purple self.header_font = Font(bold=True, size=10) self.data_font = Font(size=9) self.alignment = Alignment(horizontal='center', vertical='center') self.border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) # Location mapping for Srikara (7 locations as mentioned in requirements) self.location_mapping = { 'SRIKARA MAIN': 'SRIKARA MAIN', 'SRIKARA BRANCH 1': 'SRIKARA BRANCH 1', 'SRIKARA BRANCH 2': 'SRIKARA BRANCH 2', 'SRIKARA BRANCH 3': 'SRIKARA BRANCH 3', 'SRIKARA BRANCH 4': 'SRIKARA BRANCH 4', 'SRIKARA BRANCH 5': 'SRIKARA BRANCH 5', 'SRIKARA BRANCH 6': 'SRIKARA BRANCH 6' } def load_hims_collection_report(self, file_path): """Load HIMS collection report and process for report format""" try: hims_df = pd.read_excel(file_path) # Map payment modes to categories payment_mode_mapping = { 'Credit Card': 'Cards', 'UPI': 'Online', 'Net Banking': 'Online', 'Wallet': 'Online', # Add others/phonepe as needed } # Create payment category hims_df['Payment_Category'] = hims_df['Payment Mode Name'].map(payment_mode_mapping) hims_df['Payment_Category'] = hims_df['Payment_Category'].fillna('Others/PhonePe') # Rename columns to match expected names and convert date hims_df = hims_df.rename(columns={ 'Bill Dt': 'Date', 'Unit/Location': 'Location' }) # Convert Date to datetime hims_df['Date'] = pd.to_datetime(hims_df['Date']) # Group by Date and Payment Category for summary hims_summary = hims_df.groupby(['Date', 'Payment_Category']).agg({ 'Amount': 'sum' }).reset_index() return hims_summary except Exception as e: print(f"Error loading HIMS collection report: {e}") return pd.DataFrame() def load_axis_bank_settlement(self, file_path): """Load Axis bank settlement report for Card transactions""" try: axis_df = pd.read_excel(file_path) # Rename columns to match expected format axis_df = axis_df.rename(columns={ 'PROCESS_DATE': 'Date', 'GROSS_AMT': 'Gross_Amount', 'MDR': 'MDR', 'GST_AMT': 'GST', 'EMI_AMT': 'EMI', 'NET_AMT': 'Net_Amount' }) # Convert Date to datetime axis_df['Date'] = pd.to_datetime(axis_df['Date']) # Handle GST as separate columns if needed (IGST, SGST, CGST) if 'GST' not in axis_df.columns: gst_cols = ['IGST', 'SGST', 'CGST'] existing_gst_cols = [col for col in gst_cols if col in axis_df.columns] if existing_gst_cols: axis_df['GST'] = axis_df[existing_gst_cols].sum(axis=1) else: axis_df['GST'] = 0 # Calculate charges deducted: MDR + GST + EMI axis_df['Charges_Deducted'] = (axis_df['MDR'].fillna(0) + axis_df['GST'].fillna(0) + axis_df['EMI'].fillna(0)) # Calculate Net Amount: Gross amount - Total Charges deducted axis_df['Net_Amount_Calculated'] = (axis_df['Gross_Amount'].fillna(0) - axis_df['Charges_Deducted']) # Group by date axis_summary = axis_df.groupby('Date').agg({ 'Gross_Amount': 'sum', 'Net_Amount_Calculated': 'sum', 'Charges_Deducted': 'sum' }).reset_index() axis_summary['Payment_Category'] = 'Cards' return axis_summary except Exception as e: print(f"Error loading Axis bank settlement report: {e}") return pd.DataFrame() def load_paytm_settlement(self, file_path): """Load Paytm settlement report for Online/UPI transactions""" try: # Handle both CSV and Excel files if file_path.endswith('.csv'): paytm_df = pd.read_csv(file_path) else: paytm_df = pd.read_excel(file_path) # Rename columns to match expected format paytm_df = paytm_df.rename(columns={ 'date_added': 'Date', 'total_amount': 'Gross_Amount', 'commission': 'Commission', 'gst': 'GST', 'net_amount': 'Net_Amount' }) # Convert Date to datetime paytm_df['Date'] = pd.to_datetime(paytm_df['Date']) # Calculate charges deducted: Commission + GST paytm_df['Charges_Deducted'] = (paytm_df['Commission'].fillna(0) + paytm_df['GST'].fillna(0)) # Calculate Net Amount: Gross amount - Total Charges deducted paytm_df['Net_Amount_Calculated'] = (paytm_df['Gross_Amount'].fillna(0) - paytm_df['Charges_Deducted']) # Group by date paytm_summary = paytm_df.groupby('Date').agg({ 'Gross_Amount': 'sum', 'Net_Amount_Calculated': 'sum', 'Charges_Deducted': 'sum' }).reset_index() paytm_summary['Payment_Category'] = 'Online' return paytm_summary except Exception as e: print(f"Error loading Paytm settlement report: {e}") return pd.DataFrame() def load_phonepe_settlement(self, file_path): """Load PhonePe settlement report for Others/PhonePe transactions""" try: # Create empty placeholder for now as PhonePe is not used phonepe_summary = pd.DataFrame(columns=['Date', 'Payment_Category', 'Gross_Amount', 'Net_Amount_Calculated', 'Charges_Deducted']) return phonepe_summary except Exception as e: print(f"Error loading PhonePe settlement report: {e}") return pd.DataFrame() def prepare_consolidated_data(self, hims_data, axis_data, paytm_data, phonepe_data): """Prepare consolidated data grouped by date with proper categories""" # Combine all settlement data settlement_data = [] # Add axis data (Cards) if not axis_data.empty: settlement_data.append(axis_data[['Date', 'Payment_Category', 'Gross_Amount', 'Net_Amount_Calculated', 'Charges_Deducted']]) # Add paytm data (Online) if not paytm_data.empty: settlement_data.append(paytm_data[['Date', 'Payment_Category', 'Gross_Amount', 'Net_Amount_Calculated', 'Charges_Deducted']]) # Add phonepe data (Others/PhonePe) - placeholder if not phonepe_data.empty: settlement_data.append(phonepe_data[['Date', 'Payment_Category', 'Gross_Amount', 'Net_Amount_Calculated', 'Charges_Deducted']]) # Combine settlement data if settlement_data: settlement_combined = pd.concat(settlement_data, ignore_index=True) else: settlement_combined = pd.DataFrame(columns=['Date', 'Payment_Category', 'Gross_Amount', 'Net_Amount_Calculated', 'Charges_Deducted']) # Get all unique dates from both HIMS and settlement data all_dates = set() if not hims_data.empty: all_dates.update(hims_data['Date'].unique()) if not settlement_combined.empty: all_dates.update(settlement_combined['Date'].unique()) # Create consolidated report data consolidated_data = [] for date_val in sorted(all_dates): # HIMS data for this date hims_date = hims_data[hims_data['Date'] == date_val] if not hims_data.empty else pd.DataFrame() # Settlement data for this date settlement_date = settlement_combined[settlement_combined['Date'] == date_val] if not settlement_combined.empty else pd.DataFrame() # HIMS Collection totals by category hims_cards = hims_date[hims_date['Payment_Category'] == 'Cards']['Amount'].sum() if not hims_date.empty else 0 hims_others = hims_date[hims_date['Payment_Category'] == 'Others/PhonePe']['Amount'].sum() if not hims_date.empty else 0 hims_online = hims_date[hims_date['Payment_Category'] == 'Online']['Amount'].sum() if not hims_date.empty else 0 # Settlement totals by category settlement_cards_gross = settlement_date[settlement_date['Payment_Category'] == 'Cards']['Gross_Amount'].sum() if not settlement_date.empty else 0 settlement_others_gross = settlement_date[settlement_date['Payment_Category'] == 'Others/PhonePe']['Gross_Amount'].sum() if not settlement_date.empty else 0 settlement_online_gross = settlement_date[settlement_date['Payment_Category'] == 'Online']['Gross_Amount'].sum() if not settlement_date.empty else 0 # Net amounts after charges deduction net_cards = settlement_date[settlement_date['Payment_Category'] == 'Cards']['Net_Amount_Calculated'].sum() if not settlement_date.empty else 0 net_others = settlement_date[settlement_date['Payment_Category'] == 'Others/PhonePe']['Net_Amount_Calculated'].sum() if not settlement_date.empty else 0 net_online = settlement_date[settlement_date['Payment_Category'] == 'Online']['Net_Amount_Calculated'].sum() if not settlement_date.empty else 0 # Charges deducted charges_cards = settlement_date[settlement_date['Payment_Category'] == 'Cards']['Charges_Deducted'].sum() if not settlement_date.empty else 0 charges_others = settlement_date[settlement_date['Payment_Category'] == 'Others/PhonePe']['Charges_Deducted'].sum() if not settlement_date.empty else 0 charges_online = settlement_date[settlement_date['Payment_Category'] == 'Online']['Charges_Deducted'].sum() if not settlement_date.empty else 0 # Calculate Diff = HIMS Collection Total - Settlement Total diff_cards = hims_cards - settlement_cards_gross diff_others = hims_others - settlement_others_gross diff_online = hims_online - settlement_online_gross consolidated_data.append({ 'Date': date_val, # HIMS Collection total 'HIMS_Cards': hims_cards, 'HIMS_Others_PhonePe': hims_others, 'HIMS_Online': hims_online, # Bank/Provider settlement total 'Settlement_Cards': settlement_cards_gross, 'Settlement_Others_PhonePe': settlement_others_gross, 'Settlement_Online': settlement_online_gross, 'Diff_Cards': diff_cards, 'Diff_Others_PhonePe': diff_others, 'Diff_Online': diff_online, # Net amount after Charges deduction 'Net_Cards': net_cards, 'Net_Others_PhonePe': net_others, 'Net_Online': net_online, # Charges deducted 'Charges_Cards': charges_cards, 'Charges_Others_PhonePe': charges_others, 'Charges_Online': charges_online }) return pd.DataFrame(consolidated_data) def create_excel_report(self, consolidated_df, output_file): """Create Excel report with format matching sample report""" if consolidated_df.empty: print("No data to create report") return # Get month name from the data first_date = consolidated_df['Date'].min() if hasattr(first_date, 'strftime'): month_name = first_date.strftime("%B %Y") else: month_name = "Payment Recon" # Create workbook wb = Workbook() ws = wb.active ws.title = "Settlement Comparison" # Title row title = f"Payment Recon {month_name} - Unit Name" ws.merge_cells('A1:R1') ws['A1'] = title ws['A1'].font = Font(bold=True, size=12) ws['A1'].alignment = Alignment(horizontal='center') # Subtitle row subtitle = f"Collection on {month_name}" ws.merge_cells('A2:R2') ws['A2'] = subtitle ws['A2'].font = Font(bold=True, size=10) ws['A2'].alignment = Alignment(horizontal='center') # Table 1 Header ws.merge_cells('A4:R4') ws['A4'] = "Table-1" ws['A4'].font = Font(bold=True) # Create Table 1 - Monthly Totals self._create_table1_monthly_totals(ws, consolidated_df) # Table 2 Header (starts after Table 1 + some spacing) table2_start_row = 10 # Adjust based on Table 1 size ws.merge_cells(f'A{table2_start_row}:R{table2_start_row}') ws[f'A{table2_start_row}'] = "Table-2" ws[f'A{table2_start_row}'].font = Font(bold=True) # Create Table 2 - Date-wise breakdown self._create_table2_datewise(ws, consolidated_df, table2_start_row + 1) # Create Summary Sheet - Date-wise totals summary_ws = wb.create_sheet(title="Summary") self._create_summary_sheet(summary_ws, consolidated_df) # Auto-adjust column widths for col_idx in range(1, 17): # Columns A to P max_length = 0 column_letter = chr(64 + col_idx) # Convert to letter for row in ws.iter_rows(min_col=col_idx, max_col=col_idx): for cell in row: try: if hasattr(cell, 'value') and cell.value is not None: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = min(max_length + 2, 20) ws.column_dimensions[column_letter].width = adjusted_width # Save workbook wb.save(output_file) print(f"Settlement comparison report saved to: {output_file}") def _create_table1_monthly_totals(self, ws, df): """Create Table 1 with monthly totals""" # Calculate monthly totals totals = { 'HIMS_Cards': df['HIMS_Cards'].sum(), 'HIMS_Others_PhonePe': df['HIMS_Others_PhonePe'].sum(), 'HIMS_Online': df['HIMS_Online'].sum(), 'Settlement_Cards': df['Settlement_Cards'].sum(), 'Settlement_Others_PhonePe': df['Settlement_Others_PhonePe'].sum(), 'Settlement_Online': df['Settlement_Online'].sum(), 'Diff_Cards': df['Diff_Cards'].sum(), 'Diff_Others_PhonePe': df['Diff_Others_PhonePe'].sum(), 'Diff_Online': df['Diff_Online'].sum(), 'Net_Cards': df['Net_Cards'].sum(), 'Net_Others_PhonePe': df['Net_Others_PhonePe'].sum(), 'Net_Online': df['Net_Online'].sum(), 'Charges_Cards': df['Charges_Cards'].sum(), 'Charges_Others_PhonePe': df['Charges_Others_PhonePe'].sum(), 'Charges_Online': df['Charges_Online'].sum() } # Row 5 - Main headers main_headers = ["Unit Name", "HIMS Collection total", "", "", "Bank/Provider settlement total", "", "", "", "Net amount after Charges deduction", "", "", "Charges deducted", "", ""] for col, header in enumerate(main_headers, 1): ws.cell(row=5, column=col, value=header) ws.cell(row=5, column=col).font = self.header_font ws.cell(row=5, column=col).alignment = self.alignment ws.cell(row=5, column=col).border = self.border # Set background colors for main header sections # HIMS Collection (B-D) for col in range(2, 5): ws.cell(row=5, column=col).fill = self.hims_collection_fill # Bank/Provider settlement (E-H) for col in range(5, 9): ws.cell(row=5, column=col).fill = self.settlement_fill # Net amount (I-K) for col in range(9, 12): ws.cell(row=5, column=col).fill = self.net_amount_fill # Charges deducted (L-N) for col in range(12, 15): ws.cell(row=5, column=col).fill = self.charges_fill # Row 6 - Sub headers sub_headers = ["Viewsap", "HIMS Collection Total", "Cards", "Others/Phone Pe", "Online", "Settlement Total", "Cards", "Others/Phone Pe", "Online", "Diff", "Cards", "Others/Phone Pe", "Online", "Cards", "Others/Phone Pe", "Online"] for col, header in enumerate(sub_headers, 1): ws.cell(row=6, column=col, value=header) ws.cell(row=6, column=col).font = self.header_font ws.cell(row=6, column=col).alignment = self.alignment ws.cell(row=6, column=col).border = self.border # Set background colors for sub headers ws.cell(row=6, column=1).fill = PatternFill(fgColor='FFFFFF', fill_type='solid') for col in range(2, 5): ws.cell(row=6, column=col).fill = self.hims_collection_fill for col in range(5, 10): ws.cell(row=6, column=col).fill = self.settlement_fill for col in range(10, 13): ws.cell(row=6, column=col).fill = self.net_amount_fill for col in range(13, 16): ws.cell(row=6, column=col).fill = self.charges_fill # Row 7 - Data hims_total = totals['HIMS_Cards'] + totals['HIMS_Others_PhonePe'] + totals['HIMS_Online'] settlement_total = totals['Settlement_Cards'] + totals['Settlement_Others_PhonePe'] + totals['Settlement_Online'] diff_total = hims_total - settlement_total data_row = ["Total", hims_total, totals['HIMS_Cards'], totals['HIMS_Others_PhonePe'], totals['HIMS_Online'], settlement_total, totals['Settlement_Cards'], totals['Settlement_Others_PhonePe'], totals['Settlement_Online'], diff_total, totals['Net_Cards'], totals['Net_Others_PhonePe'], totals['Net_Online'], totals['Charges_Cards'], totals['Charges_Others_PhonePe'], totals['Charges_Online']] for col, value in enumerate(data_row, 1): ws.cell(row=7, column=col, value=value) if col > 1 and isinstance(value, (int, float)): ws.cell(row=7, column=col).number_format = '#,##0.00' ws.cell(row=7, column=col).font = self.data_font ws.cell(row=7, column=col).alignment = self.alignment ws.cell(row=7, column=col).border = self.border # Set background colors for data row ws.cell(row=7, column=1).fill = PatternFill(fgColor='FFFFFF', fill_type='solid') for col in range(2, 5): ws.cell(row=7, column=col).fill = self.hims_collection_fill for col in range(5, 10): ws.cell(row=7, column=col).fill = self.settlement_fill for col in range(10, 13): ws.cell(row=7, column=col).fill = self.net_amount_fill for col in range(13, 16): ws.cell(row=7, column=col).fill = self.charges_fill def _create_table2_datewise(self, ws, df, start_row): """Create Table 2 with date-wise breakdown""" current_row = start_row # Subtitle month_name = df['Date'].min().strftime("%B %Y") if hasattr(df['Date'].min(), 'strftime') else "June 2025" subtitle = f"Srik {month_name} (Collection in HIMS vs Bank/Provider Settlement)" ws.merge_cells(f'A{current_row}:P{current_row}') ws[f'A{current_row}'] = subtitle ws[f'A{current_row}'].font = Font(bold=True, size=10) ws[f'A{current_row}'].alignment = Alignment(horizontal='center') current_row += 1 # Main headers - same as Table 1 but with Date column main_headers = ["Date", "HIMS Collection total", "", "", "Bank/Provider settlement total", "", "", "", "Net amount after Charges deduction", "", "", "Charges deducted", "", ""] for col, header in enumerate(main_headers, 1): ws.cell(row=current_row, column=col, value=header) ws.cell(row=current_row, column=col).font = self.header_font ws.cell(row=current_row, column=col).alignment = self.alignment ws.cell(row=current_row, column=col).border = self.border # Set background colors for main headers ws.cell(row=current_row, column=1).fill = PatternFill(fgColor='FFFFFF', fill_type='solid') for col in range(2, 5): ws.cell(row=current_row, column=col).fill = self.hims_collection_fill for col in range(5, 9): ws.cell(row=current_row, column=col).fill = self.settlement_fill for col in range(9, 12): ws.cell(row=current_row, column=col).fill = self.net_amount_fill for col in range(12, 15): ws.cell(row=current_row, column=col).fill = self.charges_fill current_row += 1 # Sub headers sub_headers = ["Date", "HIMS Collection Total", "Cards", "Others/Phone Pe", "Online", "Settlement Total", "Cards", "Others/Phone Pe", "Online", "Diff", "Cards", "Others/Phone Pe", "Online", "Cards", "Others/Phone Pe", "Online"] for col, header in enumerate(sub_headers, 1): ws.cell(row=current_row, column=col, value=header) ws.cell(row=current_row, column=col).font = self.header_font ws.cell(row=current_row, column=col).alignment = self.alignment ws.cell(row=current_row, column=col).border = self.border # Set background colors for sub headers ws.cell(row=current_row, column=1).fill = PatternFill(fgColor='FFFFFF', fill_type='solid') for col in range(2, 5): ws.cell(row=current_row, column=col).fill = self.hims_collection_fill for col in range(5, 10): ws.cell(row=current_row, column=col).fill = self.settlement_fill for col in range(10, 13): ws.cell(row=current_row, column=col).fill = self.net_amount_fill for col in range(13, 16): ws.cell(row=current_row, column=col).fill = self.charges_fill current_row += 1 # Data rows - one per date for _, row in df.iterrows(): date_val = row['Date'] if hasattr(date_val, 'strftime'): date_str = date_val.strftime('%d-%m-%Y') else: date_str = str(date_val) hims_total = row['HIMS_Cards'] + row['HIMS_Others_PhonePe'] + row['HIMS_Online'] settlement_total = row['Settlement_Cards'] + row['Settlement_Others_PhonePe'] + row['Settlement_Online'] diff_total = hims_total - settlement_total data_row = [date_str, hims_total, row['HIMS_Cards'], row['HIMS_Others_PhonePe'], row['HIMS_Online'], settlement_total, row['Settlement_Cards'], row['Settlement_Others_PhonePe'], row['Settlement_Online'], diff_total, row['Net_Cards'], row['Net_Others_PhonePe'], row['Net_Online'], row['Charges_Cards'], row['Charges_Others_PhonePe'], row['Charges_Online']] for col, value in enumerate(data_row, 1): ws.cell(row=current_row, column=col, value=value) if col > 1 and isinstance(value, (int, float)): ws.cell(row=current_row, column=col).number_format = '#,##0.00' ws.cell(row=current_row, column=col).font = self.data_font ws.cell(row=current_row, column=col).alignment = self.alignment ws.cell(row=current_row, column=col).border = self.border # Set background colors for data rows ws.cell(row=current_row, column=1).fill = PatternFill(fgColor='FFFFFF', fill_type='solid') for col in range(2, 5): ws.cell(row=current_row, column=col).fill = self.hims_collection_fill for col in range(5, 10): ws.cell(row=current_row, column=col).fill = self.settlement_fill for col in range(10, 13): ws.cell(row=current_row, column=col).fill = self.net_amount_fill for col in range(13, 16): ws.cell(row=current_row, column=col).fill = self.charges_fill current_row += 1 def _create_summary_sheet(self, ws, df): """Create Summary sheet with date-wise transaction totals""" # Title ws.merge_cells('A1:B1') ws['A1'] = "Date-wise Transaction Summary" ws['A1'].font = Font(bold=True, size=14) ws['A1'].alignment = Alignment(horizontal='center') # Headers ws['A3'] = "Date" ws['B3'] = "Total Transaction Value" # Style headers for col in ['A3', 'B3']: ws[col].font = self.header_font ws[col].alignment = self.alignment ws[col].border = self.border ws[col].fill = PatternFill(fgColor='E6E6E6', fill_type='solid') # Light gray background # Data rows current_row = 4 for _, row in df.iterrows(): date_val = row['Date'] if hasattr(date_val, 'strftime'): date_str = date_val.strftime('%d-%m-%Y') else: date_str = str(date_val) # Calculate total transaction value for the date (HIMS Collection Total) total_value = row['HIMS_Cards'] + row['HIMS_Others_PhonePe'] + row['HIMS_Online'] # Set date ws.cell(row=current_row, column=1, value=date_str) ws.cell(row=current_row, column=1).font = self.data_font ws.cell(row=current_row, column=1).alignment = self.alignment ws.cell(row=current_row, column=1).border = self.border # Set total value ws.cell(row=current_row, column=2, value=total_value) ws.cell(row=current_row, column=2).font = self.data_font ws.cell(row=current_row, column=2).alignment = self.alignment ws.cell(row=current_row, column=2).border = self.border ws.cell(row=current_row, column=2).number_format = '#,##0.00' current_row += 1 # Auto-adjust column widths for summary sheet for col_letter in ['A', 'B']: max_length = 0 for row in ws.iter_rows(min_col=ord(col_letter)-64, max_col=ord(col_letter)-64): for cell in row: try: if hasattr(cell, 'value') and cell.value is not None: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = min(max_length + 2, 25) ws.column_dimensions[col_letter].width = adjusted_width def generate_settlement_report(self, hims_file, axis_file, paytm_file, phonepe_file=None, output_file=None): """Main function to generate settlement comparison report""" if output_file is None: output_file = f"Srikara_Settlement_Comparison_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx" print("Loading data files...") # Load data hims_data = self.load_hims_collection_report(hims_file) axis_data = self.load_axis_bank_settlement(axis_file) paytm_data = self.load_paytm_settlement(paytm_file) phonepe_data = self.load_phonepe_settlement(phonepe_file) if phonepe_file else pd.DataFrame() if hims_data.empty: print("Warning: HIMS collection report is empty or could not be loaded") return print("Creating settlement comparison report...") # Prepare consolidated data consolidated_df = self.prepare_consolidated_data( hims_data, axis_data, paytm_data, phonepe_data ) if consolidated_df.empty: print("No data to generate report") return # Create Excel report self.create_excel_report(consolidated_df, output_file) print("Settlement comparison report generated successfully!") return output_file # Example usage if __name__ == "__main__": # Initialize the settlement comparison class settlement_comparison = SrikaraSettlementComparison() # File paths (these should be updated with actual file paths) hims_file = "SrikaraintegratedHIS.xlsx" # HIMS collection report axis_file = "MomentsPaySrikara_Bank_new.xlsx" # Axis bank settlement report paytm_file = "Srikaramomentpay06-06.csv" # Paytm settlement report # Generate settlement comparison report try: output_file = settlement_comparison.generate_settlement_report( hims_file=hims_file, axis_file=axis_file, paytm_file=paytm_file ) print(f"Report generated: {output_file}") except Exception as e: print(f"Error generating report: {e}") print("Please ensure all input files exist and have the correct format.")