""" Batch stock performance analyzer. Reads up to 15 (ticker, entry price) pairs from an input Excel file (column B = ticker, column D = entry price, starting at row 2), then for a single user-supplied start date, downloads historical price data per ticker and writes a results spreadsheet with max gain, max drawdown, and current return relative to each entry price. """ import argparse import os import sys from datetime import datetime import pandas as pd import yfinance as yf MAX_TICKERS = 15 OUTPUT_COLUMNS = [ "Ticker", "Company Name", "Start Date", "Entry Price", "Latest Date", "Latest Close", "Current Return (%)", "Highest Price", "Highest Price Date", "Maximum Gain (%)", "Lowest Price", "Lowest Price Date", "Maximum Drawdown (%)", "Status", ] def read_inputs(path: str, limit: int = MAX_TICKERS) -> list[tuple[str, float]]: """Read up to `limit` (ticker, entry_price) pairs from columns B and D.""" df = pd.read_excel(path, header=0, usecols="B,D") df.columns = ["Ticker", "EntryPrice"] pairs: list[tuple[str, float]] = [] for _, row in df.iterrows(): ticker = row["Ticker"] price = row["EntryPrice"] if pd.isna(ticker) or pd.isna(price): continue ticker_str = str(ticker).strip().upper() if not ticker_str: continue try: price_f = float(price) except (TypeError, ValueError): continue pairs.append((ticker_str, price_f)) if len(pairs) >= limit: break return pairs def lookup_company_name(ticker: str) -> str: """Best-effort company name lookup via yfinance. Returns "" on failure.""" try: t = yf.Ticker(ticker) try: info = t.get_info() except Exception: info = getattr(t, "info", {}) or {} for key in ("longName", "shortName", "displayName"): name = info.get(key) if name: return str(name) except Exception: pass return "" def analyze_one(ticker: str, entry_price: float, start_date: str) -> dict: """Compute metrics for one ticker. Raises on download/empty failures.""" df = yf.download( ticker, start=start_date, progress=False, auto_adjust=False, ) if df is None or df.empty: raise ValueError("No price data returned") if isinstance(df.columns, pd.MultiIndex): df.columns = df.columns.get_level_values(0) close = df["Close"] latest_close = float(close.iloc[-1]) latest_date = df.index[-1].strftime("%Y-%m-%d") # Exclude the start date itself from high/low calculations. start_ts = pd.Timestamp(start_date) after_start = df[df.index > start_ts] if after_start.empty: raise ValueError("No trading days available after the start date") high = after_start["High"] low = after_start["Low"] highest_price = float(high.max()) highest_date = high.idxmax().strftime("%Y-%m-%d") lowest_price = float(low.min()) lowest_date = low.idxmin().strftime("%Y-%m-%d") return { "Ticker": ticker, "Company Name": lookup_company_name(ticker), "Start Date": start_date, "Entry Price": round(entry_price, 4), "Latest Date": latest_date, "Latest Close": round(latest_close, 4), "Current Return (%)": round((latest_close - entry_price) / entry_price * 100, 2), "Highest Price": round(highest_price, 4), "Highest Price Date": highest_date, "Maximum Gain (%)": round((highest_price - entry_price) / entry_price * 100, 2), "Lowest Price": round(lowest_price, 4), "Lowest Price Date": lowest_date, "Maximum Drawdown (%)": round((lowest_price - entry_price) / entry_price * 100, 2), "Status": "OK", } def failure_row(ticker: str, entry_price: float, start_date: str, reason: str) -> dict: row = {col: "" for col in OUTPUT_COLUMNS} row["Ticker"] = ticker row["Company Name"] = lookup_company_name(ticker) row["Start Date"] = start_date row["Entry Price"] = round(entry_price, 4) row["Status"] = f"FAILED: {reason}" return row def export(rows: list[dict], out_path: str) -> str: df = pd.DataFrame(rows, columns=OUTPUT_COLUMNS) ext = os.path.splitext(out_path)[1].lower() if ext not in (".xlsx", ".xls"): out_path = os.path.splitext(out_path)[0] + ".xlsx" if ext else out_path + ".xlsx" try: df.to_excel(out_path, index=False) except ModuleNotFoundError: csv_path = os.path.splitext(out_path)[0] + ".csv" print("openpyxl not installed; falling back to CSV.") df.to_csv(csv_path, index=False) return csv_path return out_path def parse_args(argv: list[str] | None = None) -> argparse.Namespace: parser = argparse.ArgumentParser( description="Batch stock performance analyzer. Reads tickers and entry " "prices from an input Excel file and writes per-ticker metrics to an " "output Excel file. The start date is prompted for interactively." ) parser.add_argument("input", help="Path to the input Excel file (column B = ticker, column D = entry price).") parser.add_argument("output", help="Path to the output Excel file to create.") return parser.parse_args(argv) def main(argv: list[str] | None = None) -> None: args = parse_args(argv) in_path = args.input out_path = args.output if not os.path.isfile(in_path): print(f"Input file not found: {in_path}", file=sys.stderr) sys.exit(1) start_date_raw = input("Start date (YYYY-MM-DD): ").strip() try: start_date = datetime.strptime(start_date_raw, "%Y-%m-%d").strftime("%Y-%m-%d") except ValueError: print("Invalid date format. Use YYYY-MM-DD.", file=sys.stderr) sys.exit(1) try: pairs = read_inputs(in_path) except Exception as e: print(f"Failed to read input file: {e}", file=sys.stderr) sys.exit(1) if not pairs: print("No valid (ticker, entry price) rows found.", file=sys.stderr) sys.exit(1) print(f"Processing {len(pairs)} ticker(s) from {start_date}...\n") rows: list[dict] = [] for ticker, entry_price in pairs: try: result = analyze_one(ticker, entry_price, start_date) rows.append(result) print( f" {ticker:<8} OK latest={result['Latest Close']} " f"gain={result['Maximum Gain (%)']}% dd={result['Maximum Drawdown (%)']}%" ) except Exception as e: reason = str(e).splitlines()[0][:200] or e.__class__.__name__ rows.append(failure_row(ticker, entry_price, start_date, reason)) print(f" {ticker:<8} FAILED: {reason}") written = export(rows, out_path) print(f"\nSaved {len(rows)} row(s) to: {written}") if __name__ == "__main__": main()