Projects

Excel Workflow Automation with xlwings Lite

Developed a Python automation script using xlwings Lite to streamline and optimize high-volume Excel data processing for optical distribution operations. The script enables efficient data extraction, transformation, and aggregation between multiple worksheets, reducing manual effort and minimizing errors in day-to-day business tasks.

Key Features

Automates multi-sheet data transfer: Connects two worksheets ("DataDump" and "Sheet1") and processes up to 9000 rows of data using pandas DataFrames for rapid calculations and data cleanup.
Brand code mapping and aggregation: Performs rule-based transformation of brand codes for standardized reporting, allowing easier analysis of product and distributor performance metrics.
Sum calculations and condition-based lookups: Computes distributor-wise and brand-wise totals for orders, dynamically populating the destination worksheet for instant, actionable insights.
Last empty row identification: Implements a robust method for identifying the insertion point for new distributor data, helping prevent overwriting and ensuring data integrity.
Pythonic error reduction: Handles missing values and non-numeric entries, ensuring stability across varied datasets commonly encountered in invoice workflows.
Scalable for high-volume invoicing: Supports hundreds of records and multiple brand/distributor combinations, greatly improving productivity for frequent, large-scale invoice generation for Tally Prime and similar ERP systems.

Technologies Used

Python 3, pandas, numpy
xlwings Lite for Excel workbook automation

• Data validation, aggregation, rule-based mapping, and reporting

rubaitul-azad-GauA0hiEwDk-unsplash_edite

Receivables Ageing Automation with xlwings

Built a robust Python automation tool using the full version of xlwings to calculate receivables ageing schedules directly from multi-sheet Excel workbooks, streamlining critical accounts management and reconciliation for optical distribution operations. This solution manages hundreds of customer accounts in bulk, drastically reducing manual workload and improving the accuracy of financial reporting.

Key Features

Automated multi-sheet processing: Iterates over customer-specific sheets within a consolidated Excel workbook, extracting and aggregating financial data based on payment dates, outstanding balances, and unique customer metrics.
Dynamic ageing calculations: Classifies receivables into industry-standard ageing buckets (0-30, 31-60, 61-90, 91-120, 121-150, 151-180, 180+ days), delivering instant, granular insights into overdue balances and payment trends.
Error handling and balance matching: Incorporates logic to handle missing cell values and discrepancies between running balances and closing values, ensuring accurate slot allocation and improved auditability.
Customizable for sector requirements: Adaptable logic for different worksheet formats and column mapping, making it suitable for Tally Prime exports or sector-specific ageing analysis.
Batch updates to ageing summary: Automatically populates a centralized ageing summary worksheet, facilitating rapid review, compliance, and actionable follow-up for credit control teams.

Technologies Used

Python 3, xlwings (full version)

• Advanced Excel integration, bulk account analysis, and automated slot allocation

Automated Sales Report Generation with xlwings

Developed an advanced sales reporting tool using Python and xlwings to automate the extraction, aggregation, and cross-referencing of large-scale sales records between Excel workbooks. This application facilitates seamless data integration for outlet-wise sales tracking, driving greater efficiency and accuracy in business reporting for optical distribution management.

Key Features

Pivot table creation and aggregation: Efficiently extracts sales data from raw reports and performs multi-level aggregation using pandas pivot tables for outlet, product, and item-level summaries.
Excel-to-Excel quant transfer: Cross-references outlet IDs and updates item quantities in corresponding cells of a secondary workbook, eliminating manual copy-paste inefficiencies and reducing errors in inventory reconciliation.
Dynamic last-row detection: Programmatically identifies the end of relevant data in each worksheet to ensure scalable, error-free iteration over varying dataset sizes.
Group ID-based mapping: Matches sales quantities by unique outlet and product codes, automating complex mapping tasks for large outlet networks and thousands of records.
Fully integrated Python-Excel workflow: Combines the full power of xlwings and pandas for live Excel file interaction and high-speed reporting suitable for ERP-compatible business processes.

Technologies Used

Python 3, pandas, xlwings (full version)

• Pivot table automation, cross-file Excel updates, dynamic report generation

Bulk Excel File Merge & Dual Customer-Type Reporting with Python

Developed a Python automation suite using xlwings and pandas to efficiently merge multiple downloaded Excel files into a single master report and generate segmented data outputs for two customer categories—credit and cash dealers. This solution enables seamless bulk data integration, precise customer filtering, and targeted reporting to support distribution business operations.

Key Features

Automated multi-file merge: Consolidates all Excel files in a designated folder into one master workbook, enabling high-volume data management with zero manual intervention.
Segmented customer analysis: Generates separate data reports for credit and cash dealers by importing custom lists and filtering master data accordingly, ensuring tailored communication and strategic follow-up for distinct business needs.
Excel-to-Excel workflow with xlwings: Utilizes invisible Excel application instances for direct interaction, cleaning, and saving, maintaining the integrity of original inputs while producing ready-to-use outputs for further processing.
Menu-driven user interface: Provides clear selection options for merging, credit dealer, or cash dealer report generation, simplifying operation for business users and improving adoption.
Robust data handling: Handles inconsistent data formats, ensures only valid customer IDs are included, and outputs cleaned, segment-specific datasets for downstream analysis or ERP integration.

Technologies Used

Python 3, pandas, xlwings (full version)
Automated file discovery, master workbook creation, segment-wise data extraction

Automated Sales Data Update from Tally Prime Export using Python and xlwings

Designed and implemented a Python tool leveraging pandas and the full xlwings library to automate the import, filtering, and updating of sales register data exported from Tally Prime into Excel workbooks. This automation streamlines the sales data consolidation process, improving data accuracy and accelerating reporting workflows for optical distribution operations.

Key Features

Selective data filtering and cleanup: Extracts Luxottica-specific sales entries from raw exports, excluding unwanted consignments to generate focused, relevant datasets for further processing.
Data type conversion and alignment: Ensures date and numerical fields are properly typed and formatted before Excel output, preventing common data consistency issues.
Dynamic worksheet updates: Matches consignee names with predefined dealer lists to determine target sheets, then appends new sales records including date, invoice number, quantity, amount, and narration to appropriate customer sheets.
Robust last-row detection: Uses a utility function to programmatically find the next insertion row on each sheet, guaranteeing data is appended without overwriting existing entries.
Seamless Excel-Python integration: Enables direct, automated Excel workbook interaction and live data updates through xlwings, supporting real-time business analytics and record keeping.

Technologies Used

Python 3, pandas, xlwings (full version)

• Excel automation, data filtering, dynamic sheet management, and sales reporting

Comprehensive Month-End Sales and Dealer Data Automation with Python and xlwings

Developed a robust Python automation system leveraging xlwings and pandas to perform a wide array of month-end financial and sales data processing tasks for an optical distribution business. This modular toolset integrates multiple Excel workbooks, cleanses and aggregates dealer and sales data, applies complex discounting logic, generates detailed reports, and prepares billing data for ERP import, significantly improving operational efficiency and data accuracy.

Key Features

Dealer-specific data generation: Dynamically creates and names dealer worksheets based on source data, enabling tailored data handling for each customer.
Multi-sheet data transfer and filtering: Extracts relevant sales and GST details from source files into calculation workbooks, and applies specialized filters to prepare datasets for analysis.
Granular brand-wise segmentation: Separates data by brand categories to populate dealer-specific views, supporting targeted insights and performance tracking.
Comprehensive sales reporting: Aggregates dealer-wise sales figures including various product lines, synthesizing data into consolidated summary reports for management review.
Complex discount logic: Automates both Bizom and product-wise percentage discounts based on quantity thresholds and product characteristics, reflecting sector-specific business rules.
Automated billing pivot creation: Generates multiple pivot tables with conditional formatting for quick identification of key financial thresholds, easing billing reconciliation processes.
Dealer name normalization and sorted listing: Standardizes dealer naming conventions for data consistency and prepares sorted customer lists to facilitate organized reporting.
User-friendly menu-driven system: Provides interactive command-line interface enabling selective execution of individual tasks or full workflows, promoting operational flexibility and ease of use.
Scalable integration across multiple Excel workbooks: Coordinates data flow across several files (DataSource, DealerData, Calculations), reflecting a complex but well-orchestrated data ecosystem.

Technologies Used

Python 3, pandas, xlwings (full version)
Advanced Excel automation, data cleansing, pivot table generation, discount calculations, and ERP data prep