ib-excel-formatting 0.1.0

Check and apply IB Excel formatting conventions (black=formulas, blue=hardcoded, green=cross-sheet, red=external/provider)

ib-excel-formatting

A Python package to check and apply Investment Banking Excel formatting conventions. This tool helps maintain financial modeling standards by ensuring cell font colors correctly reflect the nature of their content (hardcoded vs. formula vs. cross-reference).

Overview

In professional financial modeling, specific font colors are used to denote the source of data in a cell:

• Blue: Hardcoded non-string values.
• Black: Formulas referencing the same sheet.
• Green: Formulas referencing other sheets within the same workbook.
• Red: Formulas referencing external workbooks or external data providers (Bloomberg, Capital IQ, FactSet).

ib-excel-formatting automates the verification and application of these conventions using openpyxl.

Installation

The package requires Python 3.10 or higher and depends on openpyxl.

pip install ib-excel-formatting

Usage

The package exports two main functions: check_formatting_conventions and apply_formatting_conventions. By default, both functions process all sheets in the workbook unless a specific sheet or cell range is provided.

Checking Conventions

Use check_formatting_conventions to identify cells that do not follow the IB standard.

from pathlib import Path
from ib_excel_formatting import check_formatting_conventions

file_path = "financial_model.xlsx"
passed, violations = check_formatting_conventions(file_path)

if not passed:
    print(f"Found {len(violations)} formatting violations:")
    for violation in violations:
        print(f" - {violation}")
else:
    print("All cells follow IB formatting conventions.")

Applying Conventions

Use apply_formatting_conventions to automatically update the font colors of cells based on their content.

from ib_excel_formatting import apply_formatting_conventions

file_path = "financial_model.xlsx"
output_path = "financial_model_formatted.xlsx"

# Apply formatting to all sheets
changes_count, details = apply_formatting_conventions(
    file_path,
    output_path=output_path,
)

print(f"Applied {changes_count} formatting changes.")

Targeted Application

You can restrict the check or application to specific sheets or cells:

# Check only specific cells on a specific sheet
passed, violations = check_formatting_conventions(
    file_path,
    sheet="Income Statement",
    cells=["B5:F20", "H5:H20"],
)

Formatting Rules

The package follows these specific detection rules:

Content Type	Font Color	Details
Hardcoded	Blue	Non-string values (numbers, booleans, dates).
Formula (Local)	Black	Formulas referencing only the current sheet.
Formula (Cross-sheet)	Green	Formulas referencing other sheets in the same workbook.
External/Data	Red	External workbook references or data provider functions.

External Data Providers

The following data provider functions are automatically detected and marked as red:

• Bloomberg: BDP, BDH, BDS
• Capital IQ: CIQ, CIQRANGE
• FactSet: FDS, FDSINFO, FQL

Limitations

• Theme Colors: Excel theme and tint colors are resolved using heuristics. Some custom themes may not be perfectly identified.
• Complex Formulas: Detection of cross-sheet references relies on explicit Sheet!Cell syntax. References via INDIRECT or named ranges may not be correctly identified.
• Strings: Hardcoded strings are generally ignored by the formatting logic to avoid affecting labels and headers.

Dependencies

• Python >= 3.10
• openpyxl >= 3.1.5