xlsx

xlsx - Excel File Processing and Data Analysis Skill

Skill Overview

xlsx is a comprehensive spreadsheet creation, editing, and analysis skill that supports processing Excel files (.xlsx, .xlsm, .csv, .tsv, etc.) in the Claude environment, providing formula calculation, formatting, data analysis, and visualization capabilities.

Applicable Scenarios

1. Financial Modeling and Analysis

Create professional financial models, including financial statement analysis, budget forecasting, valuation modeling, and more. The skill supports industry-standard color-coding conventions (blue for input values, black for formulas, green for internal links, and red for external links) and follows financial modeling best practices to ensure that all formulas are correct and traceable.

2. Data Visualization and Report Generation

Import data from various sources and create dynamic pivot tables, charts, and visual reports. The skill supports batch data processing and statistical analysis and can generate professional reports tailored to business requirements, making it suitable for business analysis, management presentations, and other scenarios.

3. Automated Data Processing Workflows

Process Excel files in batches, including data cleaning, transformation, and import/export operations. Existing Excel templates can be modified while preserving all formulas. LibreOffice can be used to recalculate formula values, and formula errors (#REF!, #DIV/0!, #VALUE!, etc.) can be automatically checked and fixed, improving efficiency and accuracy.

Core Features

1. Intelligent Formula Processing

Use Excel formulas rather than hard-coded values for calculations, ensuring that spreadsheets remain dynamic and updateable. The skill provides comprehensive formula error checking, supports delivery with zero formula errors, and can use LibreOffice integration to recalculate all formulas. It follows financial modeling rules for formula construction, including placing assumptions separately, using cell references instead of hard-coded values, and adding documentation comments.

2. Dual-Mode Data Processing

Integrates the two major libraries, pandas and openpyxl: pandas is suitable for data analysis, batch operations, and simple data exports, providing powerful data manipulation capabilities; openpyxl focuses on advanced formatting, formulas, and Excel-specific features, enabling precise control over cell styles, fonts, alignment, and column widths. The two modes can be selected flexibly based on task requirements.

3. Enterprise-Grade Financial Modeling Standards

Provides comprehensive standards for financial model creation, including number-formatting conventions (years, currencies, percentages, multiples, etc.), color-coding standards, and formula documentation requirements. It supports precise preservation of existing templates, ensuring that modified files comply with the original formatting and stylistic conventions. This makes it suitable for professional scenarios such as corporate financial planning, investment analysis, and business forecasting.

Frequently Asked Questions

How does the xlsx skill handle Excel files containing formulas?

The xlsx skill prioritizes the use of Excel formulas over calculating and hard-coding results in Python. When creating or modifying an Excel file, the skill writes formula strings (such as =SUM(A1:A10)) into cells and then uses LibreOffice to recalculate all formulas, ensuring that the spreadsheet remains dynamic and updateable. This approach allows the spreadsheet to recalculate automatically when the source data changes, providing a more flexible user experience.

What is the difference between using openpyxl and pandas to process Excel files?

openpyxl is suitable for scenarios requiring precise control over Excel formatting, formulas, and specific features. It can set cell styles, fonts, colors, alignment, and other Excel attributes while preserving all formulas. pandas is better suited for data analysis, batch operations, and simple data exports, offering powerful data manipulation and analysis capabilities but limited support for Excel-specific features. It is recommended to choose based on the task requirements: use openpyxl for complex formatting and formulas, and pandas for data analysis and batch processing.

How can I ensure that an Excel model contains no formula errors?

The xlsx skill provides a comprehensive formula validation process, including testing sample references to ensure correctness, verifying column mappings and row offsets, checking the handling of NaN values, confirming that all referenced cells exist, and testing edge cases such as zero values, negative numbers, and large values. After recalculating formulas using the built-in recalc.py script, it returns a detailed error report, including the error type, location, and count, helping quickly identify and fix all formula errors and ensuring that the delivered Excel model contains zero errors.