sridattapradeep/India_Equity_Scanner

1"""2excel_export.py — build .xlsx workbooks for the screener lists.3 4Workbooks are generated in memory on demand from the latest completed scan and5streamed to the client — nothing is written to disk or the DB. Values are6scan-time (EOD-refreshed every 30 min); the meta sheet states the scan7timestamp so the file is self-describing.8"""9from __future__ import annotations10 11from datetime import datetime, timezone12from io import BytesIO13from typing import Optional14 15from openpyxl import Workbook16from openpyxl.styles import Alignment, Font, PatternFill17from openpyxl.utils import get_column_letter18 19# Column specs per screener: (dict key, header, width, number format | None)20_NUM   = "#,##0.00"21_PCT2  = "0.00"22_INT   = "0"23 24COLUMNS: dict[str, list[tuple]] = {25    "momentum": [26        ("symbol",            "Symbol",          14, None),27        ("close",             "Close ₹",         12, _NUM),28        ("change_pct",        "Change %",        10, _PCT2),29        ("rs_rating",         "RS Rating",       10, _PCT2),30        ("rsi_14",            "RSI 14",           9, _PCT2),31        ("sma_50",            "SMA 50",          12, _NUM),32        ("sma_150",           "SMA 150",         12, _NUM),33        ("sma_200",           "SMA 200",         12, _NUM),34        ("sma_200_rising",    "200D Rising",     11, None),35        ("volume_ratio",      "Vol Ratio",       10, _PCT2),36        ("pct_from_52w_high", "% From 52w High", 15, _PCT2),37        ("pct_above_52w_low", "% Above 52w Low", 15, _PCT2),38        ("passes_template",   "Passes Template", 14, None),39        ("vcp_stage",         "VCP Stage",       10, None),40        ("vcp_score",         "VCP Score",       10, _INT),41        ("vcp_pct_from_pivot","VCP % From Pivot",15, _PCT2),42    ],43    "swing": [44        ("symbol",               "Symbol",        14, None),45        ("entry_price",          "Entry ₹",       12, _NUM),46        ("stop_loss",            "Stop Loss ₹",   12, _NUM),47        ("target_1",             "Target 1 ₹",    12, _NUM),48        ("target_2",             "Target 2 ₹",    12, _NUM),49        ("rr_ratio",             "R:R",            7, _PCT2),50        ("risk_pct",             "Risk %",         9, _PCT2),51        ("atr_14",               "ATR 14",        10, _NUM),52        ("position_size_shares", "Shares (1% risk)", 15, _INT),53        ("rs_rating",            "RS Rating",     10, _PCT2),54        ("rsi_14",               "RSI 14",         9, _PCT2),55        ("volume_ratio",         "Vol Ratio",     10, _PCT2),56    ],57    "smc": [58        ("symbol",           "Symbol",          14, None),59        ("confluence_score", "Confluence",      11, _PCT2),60        ("pd_zone",          "PD Zone",         12, None),61        ("structure_type",   "Structure",       10, None),62        ("structure_dir",    "Direction",       10, None),63        ("structure_price",  "Structure ₹",     12, _NUM),64        ("ob_bottom",        "Bull OB Bottom ₹",15, _NUM),65        ("ob_top",           "Bull OB Top ₹",   13, _NUM),66        ("fvg_bottom",       "FVG Bottom ₹",    13, _NUM),67        ("fvg_top",          "FVG Top ₹",       11, _NUM),68        ("liq_type",         "Liquidity",       10, None),69        ("liq_price",        "Liquidity ₹",     12, _NUM),70        ("liq_swept",        "Swept",            8, None),71    ],72    "reversal": [73        ("symbol",          "Symbol",            14, None),74        ("close",           "Close ₹",           12, _NUM),75        ("sma_200",         "SMA 200 ₹",         12, _NUM),76        ("cross_date",      "Cross Date",        12, None),77        ("days_above_200",  "Days Above 200DMA", 17, _INT),78        ("rsi_14",          "RSI 14",             9, _PCT2),79        ("rsi_improving",   "RSI Improving",     13, None),80        ("volume_on_cross", "Vol Ratio On Cross",17, _PCT2),81        ("rs_rating",       "RS Rating",         10, _PCT2),82    ],83}84 85SHEET_TITLES = {86    "momentum": "Momentum (Minervini)",87    "swing":    "Swing Setups",88    "smc":      "ICT-SMC",89    "reversal": "Reversal Watchlist",90}91 92_HEADER_FILL = PatternFill("solid", fgColor="0F172A")93_HEADER_FONT = Font(bold=True, color="22C55E", size=10)94 95 96def _write_sheet(ws, screener: str, rows: list[dict]) -> None:97    cols = COLUMNS[screener]98    for c, (_, header, width, _) in enumerate(cols, start=1):99        cell = ws.cell(row=1, column=c, value=header)100        cell.fill = _HEADER_FILL101        cell.font = _HEADER_FONT102        cell.alignment = Alignment(horizontal="center")103        ws.column_dimensions[get_column_letter(c)].width = width104    for r, row in enumerate(rows, start=2):105        for c, (key, _, _, numfmt) in enumerate(cols, start=1):106            v = row.get(key)107            if isinstance(v, bool):108                v = "Yes" if v else "No"109            cell = ws.cell(row=r, column=c, value=v)110            if numfmt and isinstance(v, (int, float)):111                cell.number_format = numfmt112    ws.freeze_panes = "A2"113    ws.auto_filter.ref = ws.dimensions114 115 116def build_workbook(117    screener_rows: dict[str, list[dict]],118    scan_time: Optional[str],119    run_id: Optional[int],120) -> bytes:121    """Build an .xlsx with one sheet per screener (insertion order) plus an122    About sheet carrying scan metadata. Returns the file bytes."""123    wb = Workbook()124    first = True125    for screener, rows in screener_rows.items():126        if screener not in COLUMNS:127            continue128        if first:129            ws = wb.active130            ws.title = SHEET_TITLES[screener]131            first = False132        else:133            ws = wb.create_sheet(SHEET_TITLES[screener])134        _write_sheet(ws, screener, rows)135 136    about = wb.create_sheet("About")137    about.column_dimensions["A"].width = 22138    about.column_dimensions["B"].width = 52139    lines = [140        ("Source", "India Equity Scanner — india-equity-scanner.vercel.app"),141        ("Scan run", str(run_id) if run_id is not None else "—"),142        ("Scan time (UTC)", scan_time or "—"),143        ("Exported (UTC)", datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S")),144        ("Universe", "Nifty 500 (NSE)"),145        ("Note", "Values are scan-time (refreshed every 30 minutes during "146                 "market hours), not live ticks."),147        ("Disclaimer", "For informational purposes only. Not financial advice."),148    ]149    for r, (k, v) in enumerate(lines, start=1):150        about.cell(row=r, column=1, value=k).font = Font(bold=True, size=10)151        about.cell(row=r, column=2, value=v)152 153    buf = BytesIO()154    wb.save(buf)155    return buf.getvalue()156