""" Sports Card Pricing Model v1 ============================== Reads a card inventory spreadsheet produced by eBay API / PWCC data pulls and outputs an Excel file with estimated sale prices, price ranges, confidence tiers, and best-time-to-sell analysis per card. Similarity dimensions (weights must always sum to 1.0): Card Variant/Type 35% Base vs. refractor vs. auto vs. patch /10 is the dominant price driver — more than set or manufacturer Set / Mfr Tier 25% Topps Chrome vs. Topps Series 1 vs. generic Temporal (recency) 20% Player value moves fast; recent comps weighted heavily Parallel / Color 12% Gold /10 vs. Silver /25 vs. base parallel Condition / Grade 8% Raw condition; graded vs. raw handled by multiplier Weighted price estimate uses sim² — similarity squared — as the comp weight. This concentrates pricing power in the highest-scoring comps and discounts borderline comps sharply (sim 0.40 → weight 0.16; sim 0.90 → weight 0.81). This is appropriate for sports cards where like-for-like comps are strongly preferred. For markets where weaker comps carry more signal (e.g. thin markets, fashion resale, general collectibles), consider reducing the exponent toward sim^1.5 or sim^1.0 to widen the effective comp pool. Graded vs. raw: Graded and raw cards are treated as separate markets via a cross-type multiplier (analogous to cross-gender in fashion). Grading company weights are adjustable — AI/laser graders (AGS) at the top, PSA discounted for known reputational issues. Special flags (surface in Notes, do not exclude): [ROOKIE], [AUTO], [PATCH], [NUMBERED], [ERROR], [LIMITED], [HYPE], [HYPE-HISTORICAL], [HYPE-EXCLUDED] Flags use bracket ALL-CAPS format. To switch to emoji flags instead, replace the bracket strings in the estimate_price() notes block with the emoji equivalents: 🌟 [ROOKIE], ✍️ [AUTO], 🏷️ [PATCH], 🔢 [NUMBERED], ⚠️ [ERROR], ⚠️ [LIMITED], 🔥 [HYPE], 📉 [HYPE-HISTORICAL], ℹ️ [HYPE-EXCLUDED] Sport multiplier: Same sport ×1.00 | Cross-sport ×0.10 (effectively excluded) Mixed martial arts / boxing treated as their own sport. Hype / trend detection: Same rolling-window logic as fashion model. Rookie breakouts, playoff runs, and scandal drops all produce detectable volume spikes. The hype detection scan is O(n²) over scored comps. This is intentional and acceptable — MAX_SNAPS caps n at 150, making the worst case 22,500 iterations, which is negligible. Do not replace with a more complex algorithm unless MAX_SNAPS grows substantially. """ import re import sys import logging import argparse import calendar from datetime import date as date_type, datetime, timedelta from pathlib import Path from collections import defaultdict import openpyxl from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter # ── Logging ─────────────────────────────────────────────────────────────────── logging.basicConfig( level=logging.INFO, format="%(asctime)s %(levelname)-7s %(message)s", datefmt="%H:%M:%S", ) log = logging.getLogger(__name__) # ══════════════════════════════════════════════════════════════════════════════ # CONFIGURATION # ══════════════════════════════════════════════════════════════════════════════ WEIGHT_VARIANT = 0.35 WEIGHT_SET = 0.25 WEIGHT_TEMPORAL = 0.20 WEIGHT_PARALLEL = 0.12 WEIGHT_CONDITION = 0.08 MIN_SIMILARITY = 0.35 RECENT_DAYS = 90 CONF_HIGH_MIN_COMPS = 10 CONF_HIGH_RECENT_COMPS = 4 CONF_MED_MIN_COMPS = 5 CONF_MED_RECENT_COMPS = 2 CONF_LOW_MIN_COMPS = 3 # Hype / trend detection # Recommended thresholds: # 5 → sensitive (may flag popular steady sellers) # 12 → balanced (default — requires genuine volume spike) # 20 → strict (only unmistakable events trigger) HYPE_WINDOW_DAYS = 30 HYPE_WINDOW_THRESHOLD = 12 # Grading company weights # Scale: 1.00 = full weight, 0.00 = effectively excluded. # Applied as a multiplier on top of similarity when comp is graded. # # Key weights to tune: # - Raise SGC if you find their slabs trading at PSA parity in your comps # - Lower PSA further if population inflation is affecting your specific set # - Add new graders as they emerge (e.g., CSG, HGA) GRADING_COMPANY_WEIGHTS = { "ags": 1.00, "cgx": 1.00, "bgs": 0.90, "bgsa": 0.92, "sgc": 0.85, "gai": 0.80, "csg": 0.75, "hga": 0.70, "psa": 0.65, "raw": 1.00, "unknown": 0.80, } # Cross-type multiplier: graded vs. raw are effectively separate markets. # At 0.15, most cross-type comps fall below MIN_SIMILARITY (0.35) and # are excluded. Set higher if you want cross-type comps as a fallback. GRADED_RAW_CROSS_MULTIPLIER = 0.15 # Sport multipliers — same sport ×1.00, cross-sport effectively excluded at ×0.10. SPORT_MULT = { tuple(sorted(("baseball", "baseball"))): 1.00, tuple(sorted(("basketball", "basketball"))): 1.00, tuple(sorted(("football", "football"))): 1.00, tuple(sorted(("hockey", "hockey"))): 1.00, tuple(sorted(("soccer", "soccer"))): 1.00, tuple(sorted(("mma", "mma"))): 1.00, tuple(sorted(("boxing", "boxing"))): 1.00, tuple(sorted(("baseball", "basketball"))): 0.10, tuple(sorted(("baseball", "football"))): 0.10, tuple(sorted(("baseball", "hockey"))): 0.10, tuple(sorted(("basketball", "football"))): 0.10, tuple(sorted(("basketball", "hockey"))): 0.10, tuple(sorted(("football", "hockey"))): 0.10, } def sport_multiplier(sport_a: str, sport_b: str) -> float: a, b = (sport_a or "unknown").lower(), (sport_b or "unknown").lower() if a == "unknown" or b == "unknown": return 0.80 return SPORT_MULT.get(tuple(sorted((a, b))), 0.10) # Category price floors — no card prices below these regardless of comps. # Floors are intentionally low — card floors are less predictable than fashion. CATEGORY_PRICE_FLOORS = { "baseball/auto": 15.00, "baseball/patch_auto": 25.00, "baseball/numbered": 10.00, "baseball/refractor": 5.00, "baseball/base": 1.00, "basketball/auto": 20.00, "basketball/patch_auto": 35.00, "basketball/numbered": 12.00, "basketball/refractor": 8.00, "basketball/base": 1.00, "football/auto": 15.00, "football/patch_auto": 25.00, "football/numbered": 10.00, "football/refractor": 5.00, "football/base": 1.00, "hockey/auto": 10.00, "hockey/patch_auto": 20.00, "hockey/numbered": 8.00, "hockey/refractor": 5.00, "hockey/base": 1.00, "DEFAULT": 1.00, } # Book value outlier thresholds # Cards don't have a clean "retail" price the way fashion does. # Using PSA/Beckett book value as the retail analog where provided. # Trigger a warning note if book value is this many × the median comp. RETAIL_OUTLIER_RATIO = 5.0 RETAIL_OUTLIER_BY_CATEGORY = { "baseball/base": 8.0, "basketball/base": 8.0, "football/base": 8.0, "hockey/base": 8.0, } # Spreadsheet layout COL_ITEM = 1 COL_PLAYER = 2 COL_SPORT = 3 COL_SET = 4 COL_YEAR = 5 COL_BOOK_VALUE = 6 COL_CONDITION = 7 COL_SNAP_START = 8 FIELDS_PER_SNAP = 8 N_BUCKETS = 5 MAX_COMPS_PER_BUCKET = 30 MAX_SNAPS = N_BUCKETS * MAX_COMPS_PER_BUCKET # 150 COL_BUCKET_COUNTS = COL_SNAP_START + MAX_SNAPS * FIELDS_PER_SNAP SEASONS = { "Winter": [(12, 12), (1, 2)], "Spring": [(3, 5)], "Summer": [(6, 8)], "Fall": [(9, 11)], } _BUCKET_WIDTH_DAYS = 90 # ══════════════════════════════════════════════════════════════════════════════ # SPECIAL FLAG DETECTION # ══════════════════════════════════════════════════════════════════════════════ RC_RE = re.compile( r'\b(rc|rookie|rookie card|1st|first year|debut)\b', re.I ) AUTO_RE = re.compile( r'\b(auto|autograph|signed|sp auto|rpa|on[- ]card auto|on[- ]card)\b', re.I ) PATCH_RE = re.compile( r'\b(patch|relic|jersey|mem|memorabilia|game[- ]used|game[- ]worn|' r'logoman|tag|laundry tag|nameplate)\b', re.I ) NUMBERED_RE = re.compile( r'(/\d+|1/1|one[- ]of[- ]one|\d+/\d+|ssp|short print|superfractor)', re.I ) REFRACTOR_RE = re.compile( r'\b(refractor|prizm|chrome|optic|mosaic|select|spectra|' r'gold|silver|red|blue|green|orange|pink|purple|black|' r'rainbow|hyper|atomic|wave|shimmer|holo|foil|xfractor)\b', re.I ) ERROR_RE = re.compile( r'\b(error|variation|var|misprint|misspell|sp|photo variation|' r'printing error|upside down|inverted)\b', re.I ) LIMITED_RE = re.compile( r'\b(limited|exclusive|rare|insert|case hit|hobby exclusive|' r'retail exclusive|box topper|jumbo|mega box|blaster exclusive|' r'award winner|hall of fame|hof|mvp|all[- ]star|gold label)\b', re.I ) def detect_card_flags(text: str) -> dict: t = text or "" flags = { "is_rc": bool(RC_RE.search(t)), "is_auto": bool(AUTO_RE.search(t)), "is_patch": bool(PATCH_RE.search(t)), "is_numbered": bool(NUMBERED_RE.search(t)), "is_refractor": bool(REFRACTOR_RE.search(t)), "is_error": bool(ERROR_RE.search(t)), "is_limited": bool(LIMITED_RE.search(t)), } m = re.search(r'/(\d+)', t) flags["print_run"] = int(m.group(1)) if m else None return flags def extract_numbered_from_title(title: str) -> int | None: m = re.search(r'/(\d+)', title or "") return int(m.group(1)) if m else None def numbered_similarity(item_run: int | None, comp_run: int | None) -> float: if item_run is None and comp_run is None: return 0.70 if item_run is None or comp_run is None: return 0.50 if item_run == comp_run: return 1.00 ratio = min(item_run, comp_run) / max(item_run, comp_run) return round(0.30 + 0.70 * ratio, 3) # ══════════════════════════════════════════════════════════════════════════════ # MANUFACTURER / SET TIER KNOWLEDGE # ══════════════════════════════════════════════════════════════════════════════ SET_TIERS = { "premium": { "topps chrome", "topps update chrome", "bowman chrome", "bowman draft", "bowman's best", "topps finest", "topps tier one", "topps triple threads", "topps museum collection", "topps five star", "topps gold label", "panini national treasures", "panini flawless", "panini immaculate", "panini one", "panini noir", "panini definitive", "panini prizm", "panini select", "panini spectra", "upper deck exquisite", "upper deck the cup", "upper deck ultimate collection", "upper deck black diamond", "upper deck ice", "sp authentic", "upper deck fleer ultra", }, "mid": { "topps series 1", "topps series 2", "topps heritage", "topps allen & ginter", "topps gypsy queen", "topps stadium club", "topps opening day", "topps holiday", "topps archives", "topps throwback thursday", "bowman", "bowman platinum", "panini donruss optic", "panini donruss", "panini mosaic", "panini hoops", "panini chronicles", "panini contenders", "panini absolute", "panini certified", "panini score", "upper deck series 1", "upper deck series 2", "upper deck artifacts", "o-pee-chee platinum", "upper deck trilogy", }, "base": { "topps", "bowman", "panini donruss", "panini score", "panini hoops", "upper deck", "o-pee-chee", "fleer", "skybox", "stadium club", "score", }, } KNOWN_SETS = sorted( list(SET_TIERS["premium"]) + list(SET_TIERS["mid"]) + list(SET_TIERS["base"]), key=len, reverse=True ) SET_TIER_COMPAT = { ("premium", "premium"): 1.00, ("premium", "mid"): 0.35, ("premium", "base"): 0.10, ("premium", "unknown"): 0.20, ("mid", "mid"): 1.00, ("mid", "base"): 0.40, ("mid", "unknown"): 0.30, ("base", "base"): 1.00, ("base", "unknown"): 0.40, ("unknown", "unknown"): 0.50, } def get_set_tier(set_name: str) -> str: s = (set_name or "").lower().strip() for tier, sets in SET_TIERS.items(): if s in sets or any(known in s for known in sets): return tier return "unknown" def set_tier_score(tier_a: str, tier_b: str) -> float: return SET_TIER_COMPAT.get(tuple(sorted((tier_a, tier_b))), 0.20) def extract_set_from_title(title: str) -> str: if not title: return "" tl = title.lower() for s in KNOWN_SETS: if s in tl: return s return "" # ══════════════════════════════════════════════════════════════════════════════ # CARD VARIANT / TYPE KNOWLEDGE (dominant weight: 35%) # ══════════════════════════════════════════════════════════════════════════════ VARIANT_TIERS = { "tier_1": { "superfractor", "1/1", "one of one", "printing plate", "logoman", "laundry tag", "nameplate", }, "tier_2": { "patch auto", "rpa", "on-card auto", "auto patch", "jumbo patch auto", "triple patch auto", }, "tier_3": { "auto", "autograph", "signed", "gold refractor", "gold prizm", "gold parallel", "red refractor", "black refractor", }, "tier_4": { "refractor", "prizm", "chrome", "optic", "atomic", "wave", "shimmer", "holo", "xfractor", "hyper", "insert", "sp", "short print", }, "tier_5": { "parallel", "color", "rainbow", "silver", }, "tier_6": { "base", "standard", }, } VARIANT_COMPAT = { ("tier_1", "tier_1"): 1.00, ("tier_1", "tier_2"): 0.40, ("tier_1", "tier_3"): 0.20, ("tier_1", "tier_4"): 0.10, ("tier_1", "tier_5"): 0.05, ("tier_1", "tier_6"): 0.05, ("tier_2", "tier_2"): 1.00, ("tier_2", "tier_3"): 0.50, ("tier_2", "tier_4"): 0.25, ("tier_2", "tier_5"): 0.15, ("tier_2", "tier_6"): 0.05, ("tier_3", "tier_3"): 1.00, ("tier_3", "tier_4"): 0.55, ("tier_3", "tier_5"): 0.30, ("tier_3", "tier_6"): 0.10, ("tier_4", "tier_4"): 1.00, ("tier_4", "tier_5"): 0.65, ("tier_4", "tier_6"): 0.30, ("tier_5", "tier_5"): 1.00, ("tier_5", "tier_6"): 0.60, ("tier_6", "tier_6"): 1.00, } def get_variant_tier(title: str) -> str: tl = (title or "").lower() for tier, keywords in VARIANT_TIERS.items(): if any(kw in tl for kw in keywords): return tier return "tier_6" def variant_tier_score(tier_a: str, tier_b: str) -> float: return VARIANT_COMPAT.get(tuple(sorted((tier_a, tier_b))), 0.10) # ══════════════════════════════════════════════════════════════════════════════ # PARALLEL / COLOR KNOWLEDGE # ══════════════════════════════════════════════════════════════════════════════ PARALLEL_TIERS = { "one_of_one": {"superfractor", "1/1", "printing plate", "logoman"}, "ultra_rare": {"gold /10", "red /5", "black /1", "gold /5", "orange /8"}, "rare": {"gold /25", "red /25", "black /10", "orange /25", "purple /10", "gold refractor", "gold prizm"}, "uncommon": {"blue /50", "green /75", "silver /99", "purple /49", "orange /75", "red /49"}, "common": {"silver", "blue", "green", "pink", "purple", "base refractor", "base prizm", "base chrome"}, "base": {"base", "standard", "no parallel"}, } PARALLEL_COMPAT = { ("one_of_one", "one_of_one"): 1.00, ("one_of_one", "ultra_rare"): 0.40, ("one_of_one", "rare"): 0.20, ("one_of_one", "uncommon"): 0.10, ("one_of_one", "common"): 0.05, ("one_of_one", "base"): 0.05, ("ultra_rare", "ultra_rare"): 1.00, ("ultra_rare", "rare"): 0.60, ("ultra_rare", "uncommon"): 0.35, ("ultra_rare", "common"): 0.20, ("ultra_rare", "base"): 0.10, ("rare", "rare"): 1.00, ("rare", "uncommon"): 0.65, ("rare", "common"): 0.35, ("rare", "base"): 0.15, ("uncommon", "uncommon"): 1.00, ("uncommon", "common"): 0.70, ("uncommon", "base"): 0.30, ("common", "common"): 1.00, ("common", "base"): 0.55, ("base", "base"): 1.00, } def get_parallel_tier(title: str, print_run: int | None = None) -> str: tl = (title or "").lower() if print_run == 1 or "1/1" in tl or "superfractor" in tl: return "one_of_one" if print_run and print_run <= 10: return "ultra_rare" if print_run and print_run <= 25: return "rare" if print_run and print_run <= 99: return "uncommon" for tier, keywords in PARALLEL_TIERS.items(): if any(kw in tl for kw in keywords): return tier return "base" def parallel_tier_score(tier_a: str, tier_b: str) -> float: return PARALLEL_COMPAT.get(tuple(sorted((tier_a, tier_b))), 0.10) # ══════════════════════════════════════════════════════════════════════════════ # CONDITION / GRADE KNOWLEDGE # ══════════════════════════════════════════════════════════════════════════════ GRADE_RE = re.compile( r'\b(psa|bgs|sgc|ags|cgx|gai|csg|hga|bgsa)\s*(\d+(?:\.\d+)?)\b', re.I ) RAW_CONDITION_RE = re.compile( r'\b(gem mint|gem|mint|nm[- ]?mt|nm|near mint|ex[- ]?mt|excellent|' r'vg[- ]?ex|vg|good|fair|poor|pr|g|f|damaged|auth|authentic)\b', re.I ) def parse_grading_info(condition_str: str) -> dict: out = {"grader": "raw", "grade": None, "is_graded": False, "raw_condition": None} if not condition_str: return out cs = str(condition_str).strip() m = GRADE_RE.search(cs) if m: out["grader"] = m.group(1).lower() out["grade"] = float(m.group(2)) out["is_graded"] = True return out m2 = RAW_CONDITION_RE.search(cs) if m2: out["raw_condition"] = m2.group(1).lower().replace(" ", "-") return out RAW_CONDITION_ORDER = [ "gem mint", "gem", "mint", "nm-mt", "nm", "near mint", "ex-mt", "excellent", "vg-ex", "vg", "good", "fair", "poor", "damaged" ] def raw_condition_score(cond_a: str | None, cond_b: str | None) -> float: if not cond_a and not cond_b: return 0.70 if not cond_a or not cond_b: return 0.60 ca = cond_a.lower().strip() cb = cond_b.lower().strip() if ca == cb: return 1.00 try: ia = RAW_CONDITION_ORDER.index(ca) ib = RAW_CONDITION_ORDER.index(cb) dist = abs(ia - ib) return {0: 1.00, 1: 0.80, 2: 0.60, 3: 0.40}.get(dist, 0.20) except ValueError: return 0.50 def grade_score(info_a: dict, info_b: dict) -> float: if info_a["is_graded"] and info_b["is_graded"]: ga, gb = info_a.get("grade"), info_b.get("grade") if ga is None or gb is None: return 0.60 diff = abs(ga - gb) return {0.0: 1.00, 0.5: 0.80, 1.0: 0.55, 1.5: 0.35, 2.0: 0.20}.get(diff, 0.10) if not info_a["is_graded"] and not info_b["is_graded"]: return raw_condition_score(info_a.get("raw_condition"), info_b.get("raw_condition")) return 0.50 def grader_weight(grader: str) -> float: return GRADING_COMPANY_WEIGHTS.get(grader.lower(), GRADING_COMPANY_WEIGHTS["unknown"]) # ══════════════════════════════════════════════════════════════════════════════ # COMP FLAGS # ══════════════════════════════════════════════════════════════════════════════ def parse_comp_flags(flags_str: str) -> dict: out = { "comp_sport": "unknown", "comp_grader": "unknown", "comp_grade": None, "comp_is_rc": False, "comp_is_auto": False, "comp_numbered": None, "comp_is_error": False, } if not flags_str: return out for part in str(flags_str).split("|"): if ":" not in part: continue k, v = part.split(":", 1) k, v = k.strip().lower(), v.strip().lower() if k == "sport": out["comp_sport"] = v elif k == "grader": out["comp_grader"] = v elif k == "grade": try: out["comp_grade"] = float(v) except ValueError: pass elif k == "rc": out["comp_is_rc"] = v == "1" elif k == "auto": out["comp_is_auto"] = v == "1" elif k == "numbered": try: out["comp_numbered"] = int(v) except ValueError: pass elif k == "error": out["comp_is_error"] = v == "1" return out # ══════════════════════════════════════════════════════════════════════════════ # TEMPORAL / SEASON SCORING # ══════════════════════════════════════════════════════════════════════════════ def month_to_season(month: int) -> str: for season, ranges in SEASONS.items(): for (s, e) in ranges: if s <= month <= e: return season return "Unknown" def current_season() -> str: return month_to_season(date_type.today().month) def seasonal_score(comp_date_str: str | None) -> float: if not comp_date_str: return 0.25 try: comp_date = datetime.strptime(str(comp_date_str).strip()[:10], "%Y-%m-%d").date() except ValueError: return 0.25 days_ago = (date_type.today() - comp_date).days if days_ago < 0: return 0.20 if days_ago <= 7: return 1.00 elif days_ago <= 14: return 0.95 elif days_ago <= 30: return 0.90 elif days_ago <= 60: return 0.78 elif days_ago <= 90: return 0.65 elif days_ago <= 135: return 0.45 elif days_ago <= 225: return 0.28 elif days_ago <= 315: return 0.15 elif days_ago <= 410: base = 0.12 if current_season() == month_to_season(comp_date.month): base *= 1.60 return min(base, 0.30) else: return 0.04 # ══════════════════════════════════════════════════════════════════════════════ # HYPE / TREND DETECTION # ══════════════════════════════════════════════════════════════════════════════ def detect_hype_windows(comps: list[dict], window_days: int = HYPE_WINDOW_DAYS, threshold: int = HYPE_WINDOW_THRESHOLD) -> list[dict]: today = date_type.today() current_start = today - timedelta(days=window_days) dated = [] for c in comps: raw = c.get("date") if not raw: continue try: d = datetime.strptime(str(raw).strip()[:10], "%Y-%m-%d").date() dated.append((d, c)) except ValueError: continue if not dated: return [] dated.sort(key=lambda x: x[0]) windows_found = [] seen_anchors = set() for anchor_date, _ in dated: window_end = anchor_date + timedelta(days=window_days) in_window = [(d, c) for (d, c) in dated if anchor_date <= d < window_end] if len(in_window) < threshold: continue anchor_week = anchor_date - timedelta(days=anchor_date.weekday()) if anchor_week in seen_anchors: continue seen_anchors.add(anchor_week) prices = [c["price"] for (_, c) in in_window if c.get("price")] avg_p = sum(prices) / len(prices) if prices else 0.0 urls = [c.get("url") for (_, c) in in_window if c.get("url")] is_current = window_end >= current_start and anchor_date <= today windows_found.append({ "anchor": anchor_date, "end": window_end, "count": len(in_window), "avg_price": round(avg_p, 2), "is_current": is_current, "comp_urls": urls, }) return windows_found def comps_in_hype_windows(comps: list[dict], hype_windows: list[dict], window_days: int = HYPE_WINDOW_DAYS) -> set[str]: exclude_urls: set[str] = set() for hw in hype_windows: if hw["is_current"]: continue for c in comps: url = c.get("url") if not url: continue raw = c.get("date") if not raw: continue try: d = datetime.strptime(str(raw).strip()[:10], "%Y-%m-%d").date() except ValueError: continue if hw["anchor"] <= d < hw["end"]: exclude_urls.add(url) return exclude_urls def read_bucket_counts(ws, row: int) -> list[int]: out = [] for bi in range(N_BUCKETS): val = ws.cell(row, COL_BUCKET_COUNTS + bi).value try: out.append(int(val)) except (TypeError, ValueError): out.append(0) return out def detect_hype_from_counts(bucket_counts: list[int], bucket_labels: list[str] | None = None, window_days: int = HYPE_WINDOW_DAYS, bucket_width: int = _BUCKET_WIDTH_DAYS, threshold: int = HYPE_WINDOW_THRESHOLD) -> list[dict]: if not bucket_labels: bucket_labels = ["Recent", "90-day", "180-day", "270-day", "365-day"] flagged = [] for bi, cnt in enumerate(bucket_counts[:N_BUCKETS]): if cnt == 0: continue density = cnt * window_days / bucket_width if density >= threshold: flagged.append({ "bucket_idx": bi, "label": bucket_labels[bi] if bi < len(bucket_labels) else str(bi), "raw_count": cnt, "density_30d": round(density, 1), "is_current": bi == 0, }) return flagged # ══════════════════════════════════════════════════════════════════════════════ # SIMILARITY SCORING # ══════════════════════════════════════════════════════════════════════════════ def compute_similarity(item: dict, comp: dict) -> float: """ Weight a comp against the item across all dimensions. Weights: Variant/type 35% — dominant price driver (base vs. auto vs. 1/1) Set/mfr tier 25% — Topps Chrome vs. generic base Temporal 20% — recency; card values move fast Parallel 12% — color/rarity parallel tier Condition 8% — raw condition or grade proximity Multipliers applied on top: Sport: cross-sport x0.10 (effectively excluded) Graded/raw: cross-type x0.15 (effectively excluded) Grader: per-company weight (AGS 1.00, PSA 0.65, etc.) """ item_variant = get_variant_tier(item.get("name", "") + " " + item.get("condition", "")) comp_variant = get_variant_tier(comp.get("title", "") + " " + comp.get("condition", "")) v_score = variant_tier_score(item_variant, comp_variant) item_set_tier = get_set_tier(item.get("set", "")) comp_set_tier = get_set_tier(extract_set_from_title(comp.get("title", ""))) s_score = set_tier_score(item_set_tier, comp_set_tier) t_score = seasonal_score(comp.get("date")) item_run = item.get("print_run") comp_run = comp.get("comp_numbered") or extract_numbered_from_title(comp.get("title", "")) item_par = get_parallel_tier(item.get("name", ""), item_run) comp_par = get_parallel_tier(comp.get("title", ""), comp_run) p_score = parallel_tier_score(item_par, comp_par) item_grade = parse_grading_info(item.get("condition", "")) comp_grade = parse_grading_info(comp.get("condition", "")) c_score = grade_score(item_grade, comp_grade) raw = (WEIGHT_VARIANT * v_score + WEIGHT_SET * s_score + WEIGHT_TEMPORAL * t_score + WEIGHT_PARALLEL * p_score + WEIGHT_CONDITION * c_score) raw *= sport_multiplier( item.get("sport", "unknown"), comp.get("comp_sport", "unknown") ) item_is_graded = item_grade["is_graded"] comp_is_graded = comp_grade["is_graded"] if item_is_graded != comp_is_graded: raw *= GRADED_RAW_CROSS_MULTIPLIER if comp_is_graded: raw *= grader_weight(comp_grade.get("grader", "unknown")) return round(raw, 4) # ══════════════════════════════════════════════════════════════════════════════ # CARD CATEGORY HELPER # ══════════════════════════════════════════════════════════════════════════════ def card_category(sport: str, is_auto: bool, is_patch: bool, is_numbered: bool, is_refractor: bool) -> str: if is_auto and is_patch: variant = "patch_auto" elif is_auto: variant = "auto" elif is_patch: variant = "patch_auto" elif is_numbered: variant = "numbered" elif is_refractor: variant = "refractor" else: variant = "base" return f"{sport}/{variant}" if sport else "DEFAULT" # ══════════════════════════════════════════════════════════════════════════════ # COMP SNAPSHOT READER (shared by eBay and PWCC) # ══════════════════════════════════════════════════════════════════════════════ def read_comp_snapshots(ws, row: int, item_name: str) -> list[dict]: comps = [] for snap_idx in range(MAX_SNAPS): base = COL_SNAP_START + snap_idx * FIELDS_PER_SNAP price = ws.cell(row, base).value if not price: continue try: price_f = float(price) except (TypeError, ValueError): continue if price_f <= 0: continue date_val = ws.cell(row, base + 1).value cond_val = ws.cell(row, base + 2).value par_val = ws.cell(row, base + 3).value set_val = ws.cell(row, base + 4).value player_v = ws.cell(row, base + 5).value sport_v = ws.cell(row, base + 6).value flags_v = ws.cell(row, base + 7).value if isinstance(date_val, datetime): date_str = date_val.strftime("%Y-%m-%d") else: date_str = str(date_val).strip()[:10] if date_val else None flags = parse_comp_flags(str(flags_v) if flags_v else "") comps.append({ "price": price_f, "date": date_str, "condition": str(cond_val).strip() if cond_val else None, "parallel": str(par_val).strip() if par_val else None, "title": str(set_val).strip() if set_val else item_name, "comp_player": str(player_v).strip() if player_v else None, "comp_sport": flags["comp_sport"], "comp_grader": flags["comp_grader"], "comp_grade": flags["comp_grade"], "comp_is_rc": flags["comp_is_rc"], "comp_is_auto": flags["comp_is_auto"], "comp_numbered": flags["comp_numbered"], "comp_is_error": flags["comp_is_error"], }) return comps # ══════════════════════════════════════════════════════════════════════════════ # SPREADSHEET READER # ══════════════════════════════════════════════════════════════════════════════ def read_inventory(ws, extra_comp_map: dict | None = None) -> list[dict]: items = [] for row in range(2, ws.max_row + 1): name = ws.cell(row, COL_ITEM).value if not name: continue name = str(name).strip() condition_raw = str(ws.cell(row, COL_CONDITION).value or "").strip() grade_info = parse_grading_info(condition_raw) card_flags = detect_card_flags(name) sport = str(ws.cell(row, COL_SPORT).value or "").strip().lower() item = { "row": row, "name": name, "player": str(ws.cell(row, COL_PLAYER).value or "").strip(), "sport": sport, "set": str(ws.cell(row, COL_SET).value or "").strip(), "year": ws.cell(row, COL_YEAR).value, "book_value": ws.cell(row, COL_BOOK_VALUE).value, "condition": condition_raw, "grade_info": grade_info, "is_rc": card_flags["is_rc"], "is_auto": card_flags["is_auto"], "is_patch": card_flags["is_patch"], "is_numbered": card_flags["is_numbered"], "is_refractor": card_flags["is_refractor"], "is_error": card_flags["is_error"], "is_limited": card_flags["is_limited"], "print_run": card_flags["print_run"], "category": card_category( sport, card_flags["is_auto"], card_flags["is_patch"], card_flags["is_numbered"], card_flags["is_refractor"], ), } comps = read_comp_snapshots(ws, row, name) if extra_comp_map and name in extra_comp_map: comps.extend(extra_comp_map[name]) item["comps"] = comps item["bucket_counts"] = read_bucket_counts(ws, row) items.append(item) return items # ══════════════════════════════════════════════════════════════════════════════ # CATEGORY MEDIANS (fallback) # ══════════════════════════════════════════════════════════════════════════════ def compute_category_medians(items: list[dict]) -> dict: cat_prices = defaultdict(list) for item in items: for comp in item.get("comps", []): if comp.get("price"): cat_prices[item["category"]].append(comp["price"]) medians = {} for cat, prices in cat_prices.items(): if len(prices) >= 3: sp = sorted(prices) mid = len(sp) // 2 medians[cat] = sp[mid] if len(sp) % 2 == 1 else (sp[mid-1] + sp[mid]) / 2 return medians # ══════════════════════════════════════════════════════════════════════════════ # PRICE FLOOR # ══════════════════════════════════════════════════════════════════════════════ def get_price_floor(category: str) -> float: return CATEGORY_PRICE_FLOORS.get(category, CATEGORY_PRICE_FLOORS["DEFAULT"]) # ══════════════════════════════════════════════════════════════════════════════ # PRICE ESTIMATION # ══════════════════════════════════════════════════════════════════════════════ def estimate_price(item: dict, category_medians: dict) -> dict: comps = item.get("comps", []) book_value = item.get("book_value") result = { "estimated_price": None, "price_low": None, "price_high": None, "confidence": "INSUFFICIENT", "best_month": None, "best_season": None, "peak_price": None, "offpeak_price": None, "peak_premium_pct": None, "qualifying_comps": 0, "recent_comps": 0, "hype_windows_excluded": 0, "notes": [], } if item.get("is_rc"): result["notes"].append("[ROOKIE] Rookie Card: elevated collector demand") if item.get("is_auto"): result["notes"].append("[AUTO] Autograph: on-card or sticker — verify in listing") if item.get("is_patch"): result["notes"].append("[PATCH] Patch/Relic: game-used — verify swatch quality") if item.get("print_run") and item["print_run"] <= 10: result["notes"].append(f"[NUMBERED] /{item['print_run']} — ultra-rare; verify pop report") elif item.get("is_numbered"): pr = item.get("print_run") result["notes"].append(f"[NUMBERED]{f' /{pr}' if pr else ''} — comp print run must match") if item.get("is_error"): result["notes"].append("[ERROR] Error/Variation — niche collector market; comps may be sparse") if item.get("is_limited"): result["notes"].append("[LIMITED] Limited/Exclusive insert — verify comp set matches exactly") if not comps: result["notes"].append("No comp data — run eBay API / PWCC pull") cat_med = category_medians.get(item["category"]) if cat_med: result["estimated_price"] = max(cat_med, get_price_floor(item["category"])) result["notes"].append(f"Category fallback: ${cat_med:.2f}") return result today = date_type.today() scored = [] cross_sport = 0 cross_type = 0 rc_mismatch = 0 for comp in comps: cs = sport_multiplier(item.get("sport"), comp.get("comp_sport", "unknown")) if cs <= 0.10: cross_sport += 1 item_graded = item["grade_info"]["is_graded"] comp_graded = parse_grading_info(comp.get("condition", ""))["is_graded"] if item_graded != comp_graded: cross_type += 1 if item.get("is_rc") and not comp.get("comp_is_rc"): rc_mismatch += 1 sim = compute_similarity(item, comp) if sim >= MIN_SIMILARITY: try: d = datetime.strptime(comp["date"], "%Y-%m-%d").date() days_ago = (today - d).days except (ValueError, TypeError, KeyError): days_ago = 999 scored.append({**comp, "sim": sim, "days_ago": days_ago}) if cross_sport > 0: result["notes"].append( f"{cross_sport} cross-sport comp(s) excluded — verify sport field" ) if cross_type > 0: result["notes"].append( f"{cross_type} graded/raw mismatch comp(s) excluded — " f"{'graded' if item['grade_info']['is_graded'] else 'raw'}-specific comps needed" ) if rc_mismatch > len(comps) * 0.5: result["notes"].append( "[ROOKIE] Most comps are non-RC — RC premium may not be reflected in estimate" ) hype_windows = detect_hype_windows(scored) hype_excluded_urls = comps_in_hype_windows(scored, hype_windows) current_hype = [hw for hw in hype_windows if hw["is_current"]] past_hype = [hw for hw in hype_windows if not hw["is_current"]] bucket_counts = item.get("bucket_counts", []) count_flags = detect_hype_from_counts(bucket_counts) if bucket_counts else [] count_current = [f for f in count_flags if f["is_current"]] count_past = [f for f in count_flags if not f["is_current"]] if current_hype: hw = current_hype[0] result["notes"].append( f"[HYPE] TRENDING NOW — {hw['count']} sales in {HYPE_WINDOW_DAYS} days " f"(avg ${hw['avg_price']:.0f}) — SELL NOW for best price!" ) elif count_current: cf = count_current[0] result["notes"].append( f"[HYPE] TRENDING NOW — {cf['raw_count']} raw listings in recent window " f"({cf['density_30d']:.0f}/30 days) — SELL NOW!" ) if past_hype: total_excluded = len(hype_excluded_urls) for hw in past_hype: result["notes"].append( f"[HYPE-HISTORICAL] Spike: {hw['count']} sales around " f"{hw['anchor'].strftime('%b %Y')} (avg ${hw['avg_price']:.0f}) — " f"excluded as trend-inflated" ) result["notes"].append( f"[HYPE-EXCLUDED] {total_excluded} hype comp(s) removed from price model" ) elif count_past: for cf in count_past: result["notes"].append( f"[HYPE-HISTORICAL] Density in {cf['label']} bucket: " f"{cf['raw_count']} listings ({cf['density_30d']:.0f}/30 days) — verify" ) if hype_excluded_urls: scored = [c for c in scored if c.get("url") not in hype_excluded_urls] result["hype_windows_excluded"] = len(past_hype) + len(count_past) qualifying = len(scored) recent = sum(1 for c in scored if c["days_ago"] <= RECENT_DAYS) result["qualifying_comps"] = qualifying result["recent_comps"] = recent if qualifying >= CONF_HIGH_MIN_COMPS and recent >= CONF_HIGH_RECENT_COMPS: result["confidence"] = "HIGH" elif qualifying >= CONF_MED_MIN_COMPS and recent >= CONF_MED_RECENT_COMPS: result["confidence"] = "MEDIUM" elif qualifying >= CONF_LOW_MIN_COMPS: result["confidence"] = "LOW" else: result["confidence"] = "INSUFFICIENT" if qualifying < CONF_LOW_MIN_COMPS: cat_med = category_medians.get(item["category"]) if cat_med: result["estimated_price"] = max(cat_med, get_price_floor(item["category"])) result["notes"].append( f"Insufficient comps ({qualifying}) — category fallback: ${cat_med:.2f}" ) else: result["notes"].append( f"Insufficient comps ({qualifying}) — no fallback available" ) return result prices = [c["price"] for c in scored] weights = [c["sim"] ** 2 for c in scored] total_w = sum(weights) estimate = sum(p * w for p, w in zip(prices, weights)) / total_w sp_sorted = sorted(prices) n = len(sp_sorted) price_low = sp_sorted[int(n * 0.25)] price_high = sp_sorted[min(n - 1, int(n * 0.75))] floor = get_price_floor(item["category"]) estimate = max(estimate, floor) price_low = max(price_low, floor) price_high = max(price_high, floor) result["estimated_price"] = round(estimate, 2) result["price_low"] = round(price_low, 2) result["price_high"] = round(price_high, 2) if book_value: try: bv = float(book_value) median_comp = sorted(prices)[n // 2] ratio = bv / median_comp if median_comp > 0 else None threshold = RETAIL_OUTLIER_BY_CATEGORY.get( item["category"], RETAIL_OUTLIER_RATIO ) if ratio and ratio >= threshold: result["notes"].append( f"Book value (${bv:.0f}) is {ratio:.1f}x median comp — " f"book value likely stale; trust comps over book" ) except (TypeError, ValueError): pass if result["confidence"] == "LOW": cat_med = category_medians.get(item["category"]) if cat_med: result["notes"].append( f"Low confidence — category median for reference: ${cat_med:.2f}" ) month_prices = defaultdict(list) for comp in scored: if comp.get("date"): try: m = datetime.strptime(comp["date"], "%Y-%m-%d").month month_prices[m].append(comp["price"]) except ValueError: pass monthly_avg = {m: sum(ps)/len(ps) for m, ps in month_prices.items() if len(ps) >= 2} if monthly_avg: best_m = max(monthly_avg, key=monthly_avg.get) worst_m = min(monthly_avg, key=monthly_avg.get) peak = monthly_avg[best_m] offpeak = monthly_avg[worst_m] result["best_month"] = calendar.month_name[best_m] result["best_season"] = month_to_season(best_m) result["peak_price"] = round(peak, 2) result["offpeak_price"] = round(offpeak, 2) result["peak_premium_pct"] = round( (peak - offpeak) / offpeak * 100, 1 ) if offpeak > 0 else None else: result["notes"].append("Insufficient monthly spread for best-time-to-sell") return result # ══════════════════════════════════════════════════════════════════════════════ # EXCEL OUTPUT # ══════════════════════════════════════════════════════════════════════════════ CONF_COLORS = { "HIGH": "C6EFCE", "MEDIUM": "FFEB9C", "LOW": "FDBF7A", "INSUFFICIENT": "FFC7CE", } OUTPUT_COLUMNS = [ ("Card", 30), ("Player", 18), ("Sport", 10), ("Set", 22), ("Year", 8), ("Condition / Grade", 16), ("RC", 6), ("Auto", 6), ("Patch", 6), ("Numbered", 10), ("Book Value", 11), ("Est. Sale Price", 14), ("Price Low", 11), ("Price High", 11), ("Confidence", 14), ("Best Month to Sell", 18), ("Best Season", 12), ("Peak Price", 11), ("Off-Peak Price", 13), ("Peak Premium %", 14), ("Qualifying Comps", 16), ("Recent Comps (90d)", 16), ("Hype Windows Excluded", 18), ("Notes", 60), ] def cell_fill(hex_color: str) -> PatternFill: return PatternFill("solid", fgColor=hex_color) def write_row(ws, ri: int, row_fill, items_and_results): item, res = items_and_results data_align = Alignment(vertical="center", wrap_text=True) center = Alignment(horizontal="center", vertical="center") alt_fill = cell_fill("F2F2F2") def w(col, val, fmt=None, align=None, bold=False, fill=None, color="000000"): c = ws.cell(ri, col, val) c.font = Font(bold=bold, size=10, color=color) c.alignment = align or data_align if fill: c.fill = fill elif row_fill: c.fill = row_fill if fmt: c.number_format = fmt w(1, item.get("name", "")) w(2, item.get("player", "")) w(3, item.get("sport", "").title(), align=center) w(4, item.get("set", "")) w(5, item.get("year"), align=center) w(6, item.get("condition", ""), align=center) w(7, "Y" if item.get("is_rc") else "", align=center, color="1A7A5E" if item.get("is_rc") else "000000") w(8, "Y" if item.get("is_auto") else "", align=center, color="1F5FA6" if item.get("is_auto") else "000000") w(9, "Y" if item.get("is_patch") else "", align=center, color="B86800" if item.get("is_patch") else "000000") pr = item.get("print_run") w(10, f"/{pr}" if pr else ("Yes" if item.get("is_numbered") else ""), align=center) w(11, item.get("book_value"), fmt='$#,##0.00', align=center) conf = res["confidence"] conf_fill = cell_fill(CONF_COLORS.get(conf, "F2F2F2")) est = res.get("estimated_price") ec = ws.cell(ri, 12, est) ec.font = Font(bold=True, size=10) ec.number_format = '$#,##0.00' ec.alignment = center if est is not None: ec.fill = conf_fill w(13, res.get("price_low"), fmt='$#,##0.00', align=center) w(14, res.get("price_high"), fmt='$#,##0.00', align=center) cc = ws.cell(ri, 15, conf) cc.font = Font(bold=True, size=10) cc.alignment = center cc.fill = conf_fill w(16, res.get("best_month"), align=center) w(17, res.get("best_season"), align=center) w(18, res.get("peak_price"), fmt='$#,##0.00', align=center) w(19, res.get("offpeak_price"), fmt='$#,##0.00', align=center) prem = res.get("peak_premium_pct") w(20, f"+{prem:.0f}%" if prem is not None else None, align=center) w(21, res.get("qualifying_comps", 0), align=center) w(22, res.get("recent_comps", 0), align=center) hype_excl = res.get("hype_windows_excluded", 0) hc = ws.cell(ri, 23, hype_excl if hype_excl else None) hc.font = Font(bold=(hype_excl > 0), size=10, color="9C0006" if hype_excl > 0 else "000000") hc.alignment = center if hype_excl > 0: hc.fill = cell_fill("FFC7CE") elif row_fill: hc.fill = row_fill w(24, " | ".join(res.get("notes", [])) or None) ws.row_dimensions[ri].height = 18 def write_output(items: list[dict], results: list[dict], output_path): wb = openpyxl.Workbook() ws = wb.active ws.title = "Card Pricing Estimates" ws.freeze_panes = "A2" hdr_font = Font(bold=True, color="FFFFFF", size=10) hdr_fill = cell_fill("1F3864") hdr_align = Alignment(horizontal="center", vertical="center", wrap_text=True) for ci, (name, width) in enumerate(OUTPUT_COLUMNS, 1): c = ws.cell(1, ci, name) c.font, c.fill, c.alignment = hdr_font, hdr_fill, hdr_align ws.column_dimensions[get_column_letter(ci)].width = width ws.row_dimensions[1].height = 32 alt_fill = cell_fill("F2F2F2") for ri, (item, res) in enumerate(zip(items, results), 2): row_fill = alt_fill if ri % 2 == 0 else None write_row(ws, ri, row_fill, (item, res)) ws2 = wb.create_sheet("Summary") ws2.column_dimensions["A"].width = 38 ws2.column_dimensions["B"].width = 18 total = len(items) conf_counts = defaultdict(int) for r in results: conf_counts[r["confidence"]] += 1 hype_items = sum(1 for r in results if r.get("hype_windows_excluded", 0) > 0) trending_now = sum(1 for r in results if any("TRENDING NOW" in n for n in r.get("notes", []))) summary = [ ("SPORTS CARD PRICING MODEL v1", ""), (f"Run date: {date_type.today().strftime('%B %d, %Y')}", ""), (f"Hype threshold: {HYPE_WINDOW_THRESHOLD} comps / {HYPE_WINDOW_DAYS} days", ""), ("", ""), ("Total cards", total), ("HIGH confidence", conf_counts["HIGH"]), ("MEDIUM confidence", conf_counts["MEDIUM"]), ("LOW confidence", conf_counts["LOW"]), ("INSUFFICIENT data", conf_counts["INSUFFICIENT"]), ("", ""), ("Rookie Cards (RC)", sum(1 for i in items if i.get("is_rc"))), ("Autographed cards", sum(1 for i in items if i.get("is_auto"))), ("Patch / relic cards", sum(1 for i in items if i.get("is_patch"))), ("Numbered cards", sum(1 for i in items if i.get("is_numbered"))), ("Error / variation cards", sum(1 for i in items if i.get("is_error"))), ("Cards with 0 comps", sum(1 for i in items if not i.get("comps"))), ("", ""), ("[HYPE] Currently trending (SELL NOW)", trending_now), ("[HYPE-HISTORICAL] Hype windows excl.", hype_items), ] for si, (label, val) in enumerate(summary, 1): ws2.cell(si, 1, label).font = Font(bold=(si == 1), size=10) ws2.cell(si, 2, val).font = Font(size=10) wb.save(str(output_path)) log.info(f"Saved -> {output_path}") # ══════════════════════════════════════════════════════════════════════════════ # MAIN # ══════════════════════════════════════════════════════════════════════════════ def run(input_path: str, output_path: str | None = None, pwcc_path: str | None = None, verbose: bool = False): if verbose: log.setLevel(logging.DEBUG) ip = Path(input_path) if not ip.exists(): log.error(f"File not found: {ip}") sys.exit(1) op = Path(output_path) if output_path else \ ip.parent / (ip.stem + "_card_pricing_output.xlsx") extra_comp_map: dict = defaultdict(list) if pwcc_path: pp = Path(pwcc_path) if pp.exists(): log.info(f"Loading PWCC comps: {pp}") wb2 = openpyxl.load_workbook(str(pp)) ws2 = wb2.active for row in range(2, ws2.max_row + 1): name = ws2.cell(row, COL_ITEM).value if not name: continue name = str(name).strip() comps = read_comp_snapshots(ws2, row, name) if comps: extra_comp_map[name].extend(comps) log.info(f" {len(extra_comp_map)} cards with PWCC comps loaded") else: log.warning(f"PWCC file not found: {pp} — skipping") log.info(f"Loading: {ip}") wb = openpyxl.load_workbook(str(ip)) ws = wb.active log.info("Reading inventory...") items = read_inventory(ws, dict(extra_comp_map) if extra_comp_map else None) log.info( f" {len(items)} cards | " f"{sum(1 for i in items if i['comps'])}/{len(items)} with comps | " f"{sum(1 for i in items if i.get('is_rc'))}/{len(items)} RCs | " f"{sum(1 for i in items if i.get('is_auto'))}/{len(items)} autos" ) log.info("Computing category medians...") cat_medians = compute_category_medians(items) log.info("Scoring and estimating prices...") results = [] conf_counts = defaultdict(int) for item in items: res = estimate_price(item, cat_medians) results.append(res) conf_counts[res["confidence"]] += 1 if verbose: log.debug( f" [{res['confidence']:12s}] {item['name'][:55]:<55}" f" -> ${res['estimated_price'] or 'N/A'}" ) total = len(items) hype_items = sum(1 for r in results if r.get("hype_windows_excluded", 0) > 0) trending_now = sum(1 for r in results if any("TRENDING NOW" in n for n in r.get("notes", []))) log.info( f"\n{'='*58}" f"\n Cards processed : {total}" f"\n HIGH confidence : {conf_counts['HIGH']} ({conf_counts['HIGH']/total*100:.0f}%)" f"\n MEDIUM confidence : {conf_counts['MEDIUM']} ({conf_counts['MEDIUM']/total*100:.0f}%)" f"\n LOW confidence : {conf_counts['LOW']} ({conf_counts['LOW']/total*100:.0f}%)" f"\n INSUFFICIENT : {conf_counts['INSUFFICIENT']} ({conf_counts['INSUFFICIENT']/total*100:.0f}%)" f"\n [HYPE] Trending NOW : {trending_now} item(s)" f"\n Hype windows excl. : {hype_items} item(s) had inflated comps removed" f"\n Hype threshold : {HYPE_WINDOW_THRESHOLD} comps / {HYPE_WINDOW_DAYS} days" f"\n{'='*58}" ) log.info(f"Writing output -> {op}") write_output(items, results, op) return results if __name__ == "__main__": parser = argparse.ArgumentParser( description="Sports Card Pricing Model v1", formatter_class=argparse.RawDescriptionHelpFormatter, epilog=""" Similarity weights: Variant/type 35% (base vs. refractor vs. auto vs. patch /10) Set/mfr tier 25% (Topps Chrome vs. generic base) Temporal 20% (card values move fast — steep recency decay) Parallel 12% (gold /10 vs. silver /25 vs. base) Condition 8% (raw condition / grade; graded vs. raw via multiplier) Grader weights (adjustable in GRADING_COMPANY_WEIGHTS): AGS / CGX 1.00 PSA 0.65 BGS 0.90 SGC 0.85 Examples: python3 card_pricing_model.py --input card_inventory.xlsx python3 card_pricing_model.py --input card_inventory.xlsx --pwcc pwcc_comps.xlsx -v """ ) parser.add_argument("--input", required=True) parser.add_argument("--output", default=None) parser.add_argument("--pwcc", default=None, help="Optional PWCC comp file to merge before scoring") parser.add_argument("--verbose", "-v", action="store_true") args = parser.parse_args() run(input_path=args.input, output_path=args.output, pwcc_path=args.pwcc, verbose=args.verbose)