Methodology — Python

How the automation reads data, computes KPIs, and generates Excel/ Word outputs

Repository Back to portfolio

Architecture — overview

  • Single input: one Excel workbook in run/ (closed before running)
  • Deterministic pipeline: parse sheets → compute 12 KPIs → build Summary (norms, trend, change)
  • Two outputs: Excel Summary (12 KPIs) + Word report section (4 liquidity KPIs)

Full architecture (GitHub)

Data flow

  1. Detect input. The script expects exactly one .xlsx in run/
  2. Load sheets. Read years , parameters , income_statement , balance_sheet , ratio_norms
  3. Compute metrics. Derive 12 ratios and build a Summary dataframe with:
    • Benchmark — from ratio_norms (better_is, norm_low, norm_high, unit, optional note)
    • Trend — direction across the last three years (↑, ↓, →)
    • Change — delta last − first year, formatted by unit
  4. Write Excel. Replace the Summary sheet with consistent formatting
  5. Generate Word. Produce analysis_of_liquidity_ratios.docx with a captioned table and narrative for the four liquidity KPIs
  6. (Portfolio option) For the v2 variant, paste selected Power BI charts into the narrative

Outputs

Excel Summary (12 KPIs)

Cash ratio, current ratio, quick ratio, months to repay, equity ratio, Debt-to-equity, working capital, ROA, ROE, net profit margin, EBITDA margin, and the Altman index.

Word report section (in this repo)

Focuses on the four liquidity KPIs only. The portfolio shows the same section in a v2 layout with manually added visuals.

Full KPI catalog — definitions, ranges, and notes: open on GitHub

Business value and deliverables

Business questions answered

  • How short-term obligations are covered by cash and liquid assets
  • Whether liquidity improved or deteriorated over three years
  • Key drivers visible in Cash/Current/Quick ratios and Months to repay

Deliverables

  • Excel Summary with 12 KPIs — norms, trends, changes
  • Word report section — table + narrative for 4 liquidity KPIs

Full case study (GitHub)

Word layout

Title: Analysis of liquidity ratios

Caption: Table: Liquidity ratios, Y1–Y3 (or Company liquidity ratios, … when company_name is provided)

Table: Columns mirror the Excel Summary; four rows: cash ratio, current ratio, quick ratio, months to repay

Narrative: Per-metric paragraphs followed by Overall assessment , Key observations , and Recommended actions

Portfolio variants: v1 (Python only) — document as generated; v2 (Python + visuals) — same document after manually adding selected Power BI charts

Operational constraints

  • Run from run/ with exactly one .xlsx present
  • Close the Excel workbook before running (Windows file locking)
  • Ensure no pre‑existing analysis_of_liquidity_ratios.docx in run/

Reproducibility checklist

  1. Create and activate a virtual environment; pip install -r ../requirements.txt
  2. Place/verify a single input workbook in run/
  3. Run python summary.py
  4. Verify: Excel Summary updated; Word report section created

Get in touch

Connect on LinkedIn, explore projects on GitHub, or send an email