xlwings Lite: MF Portfolio Holdings Analyzer

Analyze changes in mutual fund holdings between two periods with automated data quality checks, ISIN name standardization, and human-in-the-loop correction workflow.

How xlwings Lite Works: The Python code is embedded directly inside the Excel file itself! Download the Excel file from the link below to get the complete app with all code and methodology documentation included.

⚠️ Prerequisite Step: This analyzer requires standardized text files as input. If you have raw mutual fund Excel files (with varying formats), first use the MF Portfolio File Converter with AI Schema Detection to convert them to the required format. The converter handles varying Excel layouts using AI-powered schema detection.

• Download Excel (.xlsm)

What This App Does

A deterministic Python pipeline for analyzing mutual fund portfolio holdings across two periods:

• Smart file import: Auto-detects CSV/TXT/pipe-delimited formats and delimiters
• Intelligent 2-period detection: Automatically identifies latest two months and updates all headers in reports & charts
• Automatic ISIN name standardization and deduplication
• Data quality validation reports (name conflicts, multiple ISINs)
• Human-in-the-loop correction workflow for edge cases
• Generate summary analysis tables with holdings changes
• Automated Top 10 holdings charts
• Excel button-driven interface (no coding required)

How to Use

1. Prerequisite - Prepare Data: If you have raw MF Excel files with varying formats, first convert them using the MF File Converter with AI Schema Detection to get standardized text files.
2. Download the app and install xlwings Lite from the Add-in button in Excel.
3. Step 1 - Import Data:
   • Click "Import Data" button on Control Sheet.
   • Select your holdings file (.csv, .txt, or .pipe).
   • Data is imported into DATA sheet as table T_DATA.
4. Step 2 - Run Stage 1:
   • Click "Run Stage 1" button.
   • Review ISIN_Mapping sheet for name standardization.
   • Check Namecut_Exceptions & Multiple_ISINs reports.
5. Manual Correction: Edit the standardized_name_display column in ISIN_Mapping sheet to fix any grouping issues.
6. Step 3 - Run Stage 2: Click "Run Stage 2" to generate final corrected analysis.
7. View results: Check Summary_Analysis sheet and Charts for final output.

How It Works

This app implements a 3-step workflow designed for data quality and accuracy. Data Source: Raw mutual fund portfolio disclosure files (from fund websites) have varying formats and are first converted to standardized text using the MF File Converter with AI Schema Detection, which handles format variations automatically. A detailed methodology document is embedded in the Excel file. Here are the key technical components:

Excel-Python Bridge (xlwings Architecture):

• Excel VBA buttons trigger Python functions via xlwings.
• Bidirectional data flow: Python reads from Excel tables, writes analysis back to Excel sheets.
• Smart file import: Automatic format detection (CSV/TXT/pipe-delimited) and intelligent delimiter detection (comma, tab, pipe).
• 2-period auto-detection: Automatically identifies the two most recent months in MONTH_END column and dynamically updates all report headers and chart titles.
• All processing happens in-memory with structured table outputs.

ISIN Name Standardization Engine:

• The Core Challenge: Same security can have multiple name variations ("HDFC", "HDFC Bank Ltd", "HDFC Bank Limited").
• Algorithm selects the "best" name for each unique ISIN based on length and completeness.
• Creates a standardized_name_display field for consistent grouping across periods.
• Name truncation logic (first 7 characters) helps identify potential grouping conflicts.

Data Quality Validation Reports:

• Validation_Totals: High-level totals to verify against source data.
• Namecut_Exceptions: Flags where a short name maps to multiple different full names (e.g., "relianc" → "Reliance Industries" + "Reliance Capital").
• Multiple_ISINs: Identifies where a single short name maps to multiple ISINs.
• These reports guide the human review process for edge cases.

Human-in-the-Loop Correction Workflow:

• Why it matters: Automated name matching can't handle all edge cases (mergers, renamings, similar company names).
• User manually edits ISIN_Mapping sheet to enforce consistent grouping.
• Stage 2 re-runs analysis using corrected mappings.
• Ensures 100% accuracy in final summary reports and charts.

Key Design Principles:

• Deterministic: Same input always produces same output (no ML/AI uncertainty).
• Transparent: All intermediate steps visible in Excel sheets.
• User Control: Final data quality decisions made by humans, not algorithms.
• Button-Driven: Non-technical users can operate without coding knowledge.
• Embedded Documentation: Complete methodology document included in Excel file.

Source Code & Resources

Source Code & Methodology: All Python source code and a detailed methodology document are embedded inside the Excel file! You can download the Excel file with complete code and documentation from the link above.

• xlwings Lite: Official xlwings Lite website with installation instructions and examples.
• xlwings Documentation: Comprehensive documentation with Excel object reference and API documentation.

Created by Felix Zumstein, xlwings Lite delivers a powerful and flexible solution for integrating Python with Excel - enabling native Excel support for databases, AI agents, LLMs, advanced analytics, machine learning, APIs, web services, and complete automation workflows.