version: "1.0.0" name: fortax-xlsx-manipulation description: Build or edit an Excel workbook with Python (openpyxl) the way a CA's working paper should be - live formulas instead of pasted values, an assumptions block, Indian number formats (1,00,000 and Rs), check cells that must show 0, a Source column, conditional formatting, dropdowns, charts, frozen headers - and verify it recalculates without errors before handing it over. For computations, schedules, reconciliations, MIS, lead schedules and any workbook the other Fortax skills produce. Typical asks - "Excel bana do formulas ke saath", "is sheet me total aur check lagao", "lakh format me dikhao", "dropdown daalo", "chart add karo", "is workbook ko clean karo". license: Apache-2.0 metadata: author: Fortax version: "1.0.0" homepage: https://github.com/amit-voais/fortax-skills credits: "openpyxl techniques and examples adapted from claude-office-skills/skills, https://github.com/claude-office-skills/skills (MIT)"

Excel workbooks with live formulas

Create and edit .xlsx files with openpyxl (pip install openpyxl). Write the Python, run it, then verify the result. The workbook is the working paper: a reviewer must be able to click any figure and see where it comes from.

Rules for a Fortax workbook

1. Formulas, not values. Every total, difference, tax, percentage and check is an Excel formula

(=SUM(D2:D40)), never a number computed in Python and pasted. Inputs are the only typed numbers.

1. Assumptions block. Rates, thresholds, dates and other inputs sit in one labelled block (a sheet

Inputs or the top rows), each with its source ("s.40A(3), Income-tax Act 1961 — confirm", "kb, captured 2026-08-01"). Formulas refer to those cells (or named ranges), never to a rate typed inside a formula.

1. Inputs look different: blue font for typed inputs, black for formulas (the usual convention);

say so in a legend.

1. Check cells. Each sheet that must tie has a check row that must be 0 or TRUE (debits - credits,

total assets - total liabilities, reconciliation difference), with conditional formatting that turns red when it is not.

1. Source column on every data row: file, sheet/page, row or voucher number.
2. Indian formats: amounts with lakh grouping, dates dd-mm-yyyy, and the period and entity in the

sheet title.

1. Do not destroy the client's file. Edit a copy (<name>_working.xlsx), never overwrite the

original; keep their sheets, formats and formulas.

1. Verify before handing over (below). A workbook with #REF! or a non-zero check is not done.
2. No client names in examples; made-up names (Sharma Traders).

Fundamentals

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.chart import BarChart, Reference
wb = Workbook()          # new workbook
ws = wb.active
wb = load_workbook("existing.xlsx")   # open existing (formulas kept as formulas)
ws = wb["Sheet1"]

Structure:

Workbook
  worksheets (sheets / tabs)
    cells (data), rows / columns (formatting), merged cells, charts
  defined_names (named ranges)
  styles

Cells

ws["A1"] = "Header"
ws["B1"] = 42
ws.cell(row=1, column=3, value="Data")
ws.append(["Row", "Data", "Here"])          # next empty row
value = ws["A1"].value
for row in ws.iter_rows(min_row=2, max_row=10, min_col=1, max_col=3):
    for cell in row:
        print(cell.coordinate, cell.value)

Note: ws["A1:C1"] = [...] does not assign a range; write cell by cell or use append.

Formulas and named ranges

ws["D1"] = "=SUM(A1:C1)"
ws["D2"] = "=AVERAGE(A2:C2)"
ws["E1"] = '=IF(D1>100,"High","Low")'
from openpyxl.workbook.defined_name import DefinedName
wb.defined_names["GST_RATE"] = DefinedName("GST_RATE", attr_text="Inputs!$B$3")  # openpyxl 3.1+
ws["F2"] = "=E2*GST_RATE"

Use English function names and commas as separators. openpyxl does not calculate formulas — the values appear when Excel / LibreOffice opens and recalculates the file (see Verify).

Formatting

ws["A1"].font = Font(name="Arial", size=12, bold=True, color="FFFFFF")
ws["A1"].fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid")
thin = Side(style="thin")
ws["A1"].border = Border(left=thin, right=thin, top=thin, bottom=thin)
ws["A1"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws["B2"].font = Font(color="0000FF")      # typed input

Number formats (Indian)

LAKH = '[>=10000000]##\\,##\\,##\\,##0.00;[>=100000]##\\,##\\,##0.00;##,##0.00'
ws["B2"].number_format = LAKH                 # 1,23,45,678.00 — positive numbers
ws["B3"].number_format = '"Rs "#,##0.00'      # simple rupee format (international grouping)
ws["C2"].number_format = "0.00%"
ws["D2"].number_format = "DD-MM-YYYY"
ws["E2"].number_format = '#,##0.00 "units"'

The lakh format uses conditional sections, so it cannot also carry a negative section; for columns that go negative, show negatives in a separate Dr/Cr column or use #,##0.00;(#,##0.00). Do not use the $ formats from generic examples.

Conditional formatting

from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
red = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
ws.conditional_formatting.add("H50", CellIsRule(operator="notEqual", formula=["0"], fill=red))  # check cell
ws.conditional_formatting.add("F2:F200", FormulaRule(formula=["ABS(F2)>Inputs!$B$5"], fill=red))  # over threshold
ws.conditional_formatting.add("G2:G200", ColorScaleRule(start_type="min", start_color="F8696B",
                                                       end_type="max", end_color="63BE7B"))

Data validation (dropdowns, ranges)

from openpyxl.worksheet.datavalidation import DataValidation
dv = DataValidation(type="list", formula1='"Timing,Adjustment,Investigate"', allow_blank=True)
dv.error, dv.errorTitle = "Pick from the list", "Invalid category"
ws.add_data_validation(dv)
dv.add("J2:J500")
dv_rate = DataValidation(type="decimal", operator="between", formula1="0", formula2="0.28")
ws.add_data_validation(dv_rate)
dv_rate.add("Inputs!B3")

Charts

from openpyxl.chart import BarChart, LineChart, PieChart, Reference
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=13)
cats = Reference(ws, min_col=1, min_row=2, max_row=13)
bar = BarChart(); bar.type = "col"; bar.title = "Sales by month"
bar.add_data(data, titles_from_data=True); bar.set_categories(cats)
ws.add_chart(bar, "E2")
line = LineChart(); line.title = "Trend"
line.add_data(data, titles_from_data=True); line.set_categories(cats)
ws.add_chart(line, "E20")
pie = PieChart(); pie.add_data(Reference(ws, min_col=2, min_row=1, max_row=5), titles_from_data=True)
pie.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5)); ws.add_chart(pie, "M2")

Waterfall / bridge charts: openpyxl has no native waterfall; use a stacked column chart with an invisible base series (see fortax-variance-analysis).

Sheets, rows and columns

ws2 = wb.create_sheet("Data")
ws0 = wb.create_sheet("Summary", 0)      # at position 0
ws.title = "Lead Schedule"
del wb["Sheet2"]
copy = wb.copy_worksheet(wb["Template"])
ws.column_dimensions["A"].width = 32
ws.row_dimensions[1].height = 30
ws.column_dimensions["C"].hidden = True
ws.freeze_panes = "B2"                   # freeze row 1 and column A
ws.auto_filter.ref = "A1:K500"

Verify before handing over

openpyxl writes formulas but not their results, so check the workbook the way the CA will see it:

1. Recalculate with LibreOffice if installed:

soffice --headless --convert-to xlsx --outdir /tmp/recalc <file>.xlsx (on macOS the binary may be /Applications/LibreOffice.app/Contents/MacOS/soffice). If LibreOffice is not available, tell the CA the file must be opened once in Excel to calculate, and do the checks below on the formulas you wrote.

1. Read the recalculated values: load_workbook(path, data_only=True); scan every cell for

#REF!, #DIV/0!, #VALUE!, #NAME?, #N/A.

1. Check cells all 0 / TRUE.
2. Spot-check two or three totals against the source file with a script (not by eye).
3. Say in your reply: which checks ran, and that the values were recalculated (or not).

Good practice

1. Start from the client's or firm's template when there is one.
2. Batch writes; avoid cell-by-cell loops over very large ranges when append will do.
3. Named ranges for inputs used in many formulas.
4. Data validation on columns people will type into.
5. For large files, write in stages and save.
6. Keep sheet order: Inputs, Summary, working sheets, Data, Checks.

Longer patterns (CSV import, report template, monthly tracker, dashboard) are in references/examples.md.

Limitations

• Cannot run VBA macros (a .xlsm loaded with keep_vba=True keeps them but does not run them).
• Pivot tables are not really supported — build a formula summary (SUMIFS) instead.
• Limited sparklines; no external data connections; some chart types unavailable.
• Old binary .xls files: convert first (LibreOffice --convert-to xlsx) or read with pandas + xlrd.
• load_workbook(data_only=True) then save replaces formulas with values — never save a file opened

that way.

Resources

• openpyxl documentation: https://openpyxl.readthedocs.io/
• Styles: https://openpyxl.readthedocs.io/en/stable/styles.html

Credit

Techniques and examples adapted from claude-office-skills/skills (MIT; notice in LICENSE-THIRD-PARTY-claude-office-skills.txt). Changed by Fortax: working-paper rules (formulas, assumptions block, checks, sources), Indian formats, verification step, CA examples.