Arun K. Lama

ETL architecture

Screening & chart logic

Rules combine trend geometry (close vs MA stack), event-style crosses (including MA50 vs MA200), candle bias, relative volume, and S/R context. Outputs are partitioned for a fundamentally pruned market summary deck versus a technically tiered buy watchlist reel — same core data, different product surfaces.

• Deterministic filename conventions so video ordering and AI caption context stay in lockstep.
• Reel body merge then intro prepend; temp assets deleted only after successful mux — safe operational defaults.
• Operational runbook (PIPELINE_AND_VIDEO.md) documents dimensions, visibility flags, and regression-prone merge settings.

1. Data extraction

Unadjusted prices (system of record)

The batch job reads the latest unadjusted OHLCV for all active tickers from a central Google Sheet using gspread with a Google Cloud service account (credentials supplied via environment — no keys in the repo). That sheet is the hand-off point from earlier collection work (for example notebooks that scrape NEPSE or related sources); this repository focuses on adjustment and refresh, not on re-scraping the exchange on every run.

Dividend history

download_dividend.py hits ShareSansar proposed dividend by fiscal year, pulls recent years, and merges them into the live dividend-history sheet so book-closure dates and cash/bonus percentages stay current.

Rights history

download_right.py drives headless Chrome (Selenium) on ShareSansar’s existing issues / rights table (pagination-aware), parses HTML with Beautiful Soup, and writes refreshed rights rows (symbol, ratio, book closure) back into the rights sheet.

2. Ingest prep and quality

price_adjustment.py normalizes the price sheet after load: strip thousands separators and bad tokens, coerce OHLCV columns to numeric, sort by date, and index by session date. Each ticker is processed in a try/except loop so one bad series does not stop the whole market; failures are collected for review. The chart below illustrates the kind of raw-series sanity checks (gaps, spikes, missing logic) that motivate strict cleaning before adjustment.

3. Adjustment engine (PriceAdjuster)

For each symbol, corporate actions are unified into one timeline: cash dividend %, bonus %, and rights ratio (a:b), each with a book closure date. Actions before the first price observation are dropped; the remainder are sorted chronologically.

The OHLC series is fragmented into segments between consecutive book-closure dates. On each segment, prices are restated in a backward style so past levels remain comparable after payouts and rights:

• Cash dividend: the announced percentage is converted to rupees per share using a par-value heuristic (Rs 10 vs 100 inferred from the pre-closing price level). Historical OHLC on that segment is divided by 1 + (dividend per share / pre-close) — but only when that implied yield exceeds 10%; smaller declared amounts are left unchanged to avoid over-adjusting noisy small percentages.
• Bonus: OHLC is divided by 1 + bonus%/100 on the affected segment.
• Rights: the rights ratio feeds a per-share adjustment; OHLC is transformed with the implemented mapping (P + k) / (1 + r) so the rights issue is reflected consistently across the window.

Segments are concatenated into one continuous adjusted history per ticker. All tickers are concatenated and written to adjusted price/all_adj_companies_data.csv (the same artifact other pipelines consume via GitHub). An optional write back to a master Google Sheet is present in code but left disabled in favor of the CSV artifact.