{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# TheBest Insight · 미국 주도주 팩터 데이터 수집 v12\n",
    "\n",
    "- 기본값은 `MODE=\"auto\"`: Firebase 데이터가 있으면 증분 업데이트, 없으면 최초 전체 구축입니다.\n",
    "- 구성종목은 복수 소스를 순차 사용해 단일 사이트 장애로 전체 실행이 멈추지 않습니다.\n",
    "- Firebase 인증/저장 오류가 발생해도 뒤 셀이 연쇄 NameError로 빨갛게 실패하지 않고 최종 진단에 실제 원인을 표시합니다.\n",
    "- **처음부터 `런타임 → 모두 실행`** 하세요. 마지막 셀만 단독 실행하지 마세요.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip -q install -U yfinance lxml html5lib beautifulsoup4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ===== 사용자 설정 =====\n",
    "UNIVERSE = \"ndx100\"  # @param [\"ndx100\", \"sp500\"]\n",
    "MODE = \"auto\"        # @param [\"auto\", \"update\", \"full\"]\n",
    "YEARS = 10            # @param {type:\"integer\"}\n",
    "FUNDAMENTALS_MODE = \"auto\"  # @param [\"auto\", \"always\", \"off\"]\n",
    "FIREBASE_WRITE = True        # @param {type:\"boolean\"}\n",
    "\n",
    "# Firebase 인증 방식\n",
    "# auto      : 익명 인증을 먼저 시도하고, 막혀 있으면 Colab Google 계정 인증으로 자동 전환\n",
    "# anonymous : Firebase Authentication의 익명 로그인을 사용\n",
    "# google    : Colab에 로그인한 Google 계정의 프로젝트 IAM 권한을 사용\n",
    "AUTH_MODE = \"auto\"           # @param [\"auto\", \"anonymous\", \"google\"]\n",
    "\n",
    "# Firebase 연결이 안 된 상태에서 update/auto 실행 시 10년 전체를 다시 받지 않습니다.\n",
    "# 오류 원인을 먼저 표시하고 데이터 수집을 중단하여 Yahoo/Firebase 사용량을 낭비하지 않습니다.\n",
    "CONTINUE_ON_FIREBASE_ERROR = True  # @param {type:\"boolean\"}\n",
    "\n",
    "PROJECT_ID = \"stockfactor-7d3b8\"  # @param {type:\"string\"}\n",
    "API_KEY = \"AIzaSyCXz5oxrUJE3rwWL0bpHUeT6pIBkEK4SfI\"  # @param {type:\"string\"}\n",
    "\n",
    "PRICE_COLLECTION = \"factor\"\n",
    "TV_COLLECTION = \"factor_tv\"\n",
    "META_COLLECTION = \"factor_meta\"\n",
    "US_DOC_PREFIX = \"us\"\n",
    "\n",
    "MAX_DOC_BYTES = 890_000\n",
    "DOWNLOAD_BATCH = 60\n",
    "FUNDAMENTAL_WORKERS = 6\n",
    "NEW_TICKER_BACKFILL_DAYS = 460\n",
    "FUNDAMENTAL_REFRESH_DAYS = 30\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from __future__ import annotations\n",
    "import io, json, math, time, zipfile, traceback\n",
    "from concurrent.futures import ThreadPoolExecutor, as_completed\n",
    "from datetime import date, datetime, timedelta, timezone\n",
    "from pathlib import Path\n",
    "from urllib.parse import quote\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import requests\n",
    "import yfinance as yf\n",
    "from IPython.display import display, FileLink\n",
    "\n",
    "assert UNIVERSE in {\"ndx100\", \"sp500\"}\n",
    "assert MODE in {\"auto\", \"update\", \"full\"}\n",
    "assert AUTH_MODE in {\"auto\", \"anonymous\", \"google\"}\n",
    "\n",
    "ROOT = Path('/content')\n",
    "BENCHMARK = '^NDX' if UNIVERSE == 'ndx100' else '^GSPC'\n",
    "BENCH_KEY = 'NDX' if UNIVERSE == 'ndx100' else 'SPX'\n",
    "UNIVERSE_LABEL = 'NASDAQ-100' if UNIVERSE == 'ndx100' else 'S&P 500'\n",
    "\n",
    "RUN_STATE={\n",
    "    'constituents':False,\n",
    "    'firebase_read':False,\n",
    "    'market':False,\n",
    "    'fundamentals':False,\n",
    "    'firebase_write':False,\n",
    "    'files':False,\n",
    "    'errors':[]\n",
    "}\n",
    "\n",
    "def mark_error(stage, exc):\n",
    "    msg=f'{stage}: {type(exc).__name__}: {exc}'\n",
    "    RUN_STATE['errors'].append(msg)\n",
    "    print('❌', msg)\n",
    "\n",
    "_HTTP_HEADERS={\n",
    "    'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 Chrome/153 Safari/537.36',\n",
    "    'Accept-Language':'en-US,en;q=0.9',\n",
    "    'Cache-Control':'no-cache'\n",
    "}\n",
    "\n",
    "def js_ticker(x: str) -> str:\n",
    "    return str(x).strip().upper().replace('.', '-')\n",
    "\n",
    "def clean_num(v, digits=6):\n",
    "    try:\n",
    "        x=float(v)\n",
    "        if not math.isfinite(x): return None\n",
    "        return round(x, digits)\n",
    "    except Exception:\n",
    "        return None\n",
    "\n",
    "def pct_like(v):\n",
    "    x=clean_num(v, 6)\n",
    "    if x is None: return None\n",
    "    return x*100 if abs(x) <= 2 else x\n",
    "\n",
    "def http_text(url: str, attempts=3):\n",
    "    last=None\n",
    "    for k in range(attempts):\n",
    "        try:\n",
    "            r=requests.get(url,headers=_HTTP_HEADERS,timeout=35)\n",
    "            r.raise_for_status()\n",
    "            return r.text\n",
    "        except Exception as e:\n",
    "            last=e\n",
    "            time.sleep(1.0*(k+1))\n",
    "    raise RuntimeError(f'URL 읽기 실패: {url} / {last}')\n",
    "\n",
    "def read_csv_url(url: str):\n",
    "    return pd.read_csv(io.StringIO(http_text(url)))\n",
    "\n",
    "def read_html_url(url: str):\n",
    "    return pd.read_html(io.StringIO(http_text(url)))\n",
    "\n",
    "def normalize_constituents(df, symbol_candidates, name_candidates, sector_candidates=()):\n",
    "    if df is None or len(df)==0: return None\n",
    "    cols={str(c).strip().lower():c for c in df.columns}\n",
    "    def pick(cands):\n",
    "        for c in cands:\n",
    "            if c.lower() in cols: return cols[c.lower()]\n",
    "        return None\n",
    "    sc=pick(symbol_candidates); nc=pick(name_candidates); sec=pick(sector_candidates)\n",
    "    if sc is None or nc is None: return None\n",
    "    out=pd.DataFrame({\n",
    "        'ticker':df[sc].map(js_ticker),\n",
    "        'name':df[nc].astype(str).str.strip(),\n",
    "        'sector':df[sec].astype(str).str.strip() if sec is not None else ''\n",
    "    })\n",
    "    out=out[out['ticker'].str.match(r'^[A-Z0-9\\-]+$',na=False)]\n",
    "    out=out.drop_duplicates('ticker').sort_values('ticker').reset_index(drop=True)\n",
    "    return out\n",
    "\n",
    "def load_constituents(universe: str) -> pd.DataFrame:\n",
    "    errors=[]\n",
    "    candidates=[]\n",
    "    if universe=='ndx100':\n",
    "        # 1) 현재 CSV 미러 2개 → 2) Wikipedia 순서. 한 곳이 막혀도 계속 진행.\n",
    "        for label,url in [\n",
    "            ('NASDAQ100 CSV #1','https://raw.githubusercontent.com/Gary-Strauss/NASDAQ100_Constituents/master/data/nasdaq100_constituents.csv'),\n",
    "            ('NASDAQ100 CSV #2','https://raw.githubusercontent.com/yfiua/index-constituents/main/docs/constituents-nasdaq100.csv'),\n",
    "        ]:\n",
    "            try:\n",
    "                df=read_csv_url(url)\n",
    "                out=normalize_constituents(df,['Ticker','Symbol'],['Company','Name'],['GICS_Sector','GICS Sector','ICB Industry'])\n",
    "                if out is not None and len(out)>=90:\n",
    "                    print(f'✅ 구성종목: {label} ({len(out)}개)')\n",
    "                    return out\n",
    "                errors.append(f'{label}: 표 형식/종목수 오류')\n",
    "            except Exception as e: errors.append(f'{label}: {e}')\n",
    "        try:\n",
    "            tables=read_html_url('https://en.wikipedia.org/wiki/Nasdaq-100')\n",
    "            for df in tables:\n",
    "                out=normalize_constituents(df,['Ticker','Symbol'],['Company','Name'],['GICS Sector','ICB Industry','Industry'])\n",
    "                if out is not None and len(out)>=90:\n",
    "                    print(f'✅ 구성종목: Wikipedia ({len(out)}개)')\n",
    "                    return out\n",
    "            errors.append('Wikipedia: 적합한 구성종목 표 없음')\n",
    "        except Exception as e: errors.append(f'Wikipedia: {e}')\n",
    "        min_n=90\n",
    "    else:\n",
    "        for label,url in [\n",
    "            ('S&P500 CSV','https://raw.githubusercontent.com/datasets/s-and-p-500-companies/master/data/constituents.csv'),\n",
    "        ]:\n",
    "            try:\n",
    "                df=read_csv_url(url)\n",
    "                out=normalize_constituents(df,['Symbol','Ticker'],['Security','Company','Name'],['GICS Sector','GICS_Sector','Sector'])\n",
    "                if out is not None and len(out)>=450:\n",
    "                    print(f'✅ 구성종목: {label} ({len(out)}개)')\n",
    "                    return out\n",
    "                errors.append(f'{label}: 표 형식/종목수 오류')\n",
    "            except Exception as e: errors.append(f'{label}: {e}')\n",
    "        try:\n",
    "            tables=read_html_url('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')\n",
    "            for df in tables:\n",
    "                out=normalize_constituents(df,['Symbol','Ticker'],['Security','Company','Name'],['GICS Sector','Sector'])\n",
    "                if out is not None and len(out)>=450:\n",
    "                    print(f'✅ 구성종목: Wikipedia ({len(out)}개)')\n",
    "                    return out\n",
    "            errors.append('Wikipedia: 적합한 구성종목 표 없음')\n",
    "        except Exception as e: errors.append(f'Wikipedia: {e}')\n",
    "        min_n=450\n",
    "    raise RuntimeError('구성종목을 확보하지 못했습니다.\\n- '+'\\n- '.join(errors))\n",
    "\n",
    "constituents=pd.DataFrame(columns=['ticker','name','sector'])\n",
    "try:\n",
    "    constituents=load_constituents(UNIVERSE)\n",
    "    RUN_STATE['constituents']=True\n",
    "    print(f'✅ {UNIVERSE_LABEL}: {len(constituents)}개 현재 구성종목')\n",
    "    display(constituents.head())\n",
    "except Exception as e:\n",
    "    mark_error('구성종목 수집',e)\n",
    "    print('아래 단계는 연쇄 오류를 내지 않고 자동 건너뜁니다.')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ===== Firebase 연결 / 실행 모드 결정 =====\n",
    "_session=requests.Session()\n",
    "_token_cache={'token':None,'exp':0,'kind':None}\n",
    "FIREBASE_ACTIVE=False\n",
    "FIREBASE_ERROR=''\n",
    "existing_meta=None\n",
    "last_saved=None\n",
    "current_symbols=constituents['ticker'].tolist() if RUN_STATE['constituents'] else []\n",
    "new_symbols=[]\n",
    "removed_symbols=[]\n",
    "fund_due=True\n",
    "EFFECTIVE_MODE=MODE\n",
    "\n",
    "\n",
    "def _err_message(r):\n",
    "    try:\n",
    "        j=r.json(); err=j.get('error')\n",
    "        if isinstance(err,dict): return err.get('message') or str(err)[:500]\n",
    "        return str(j)[:500]\n",
    "    except Exception:\n",
    "        return r.text[:500]\n",
    "\n",
    "def _anonymous_token():\n",
    "    u=f'https://identitytoolkit.googleapis.com/v1/accounts:signUp?key={quote(API_KEY)}'\n",
    "    r=_session.post(u,json={'returnSecureToken':True},timeout=30)\n",
    "    if not r.ok:\n",
    "        raise RuntimeError(f'Firebase 익명 인증 실패 ({r.status_code}): {_err_message(r)}')\n",
    "    j=r.json()\n",
    "    return j['idToken'], time.time()+int(j.get('expiresIn',3600))-120, 'anonymous'\n",
    "\n",
    "def _google_token():\n",
    "    try:\n",
    "        from google.colab import auth as colab_auth\n",
    "        colab_auth.authenticate_user()\n",
    "        import google.auth\n",
    "        from google.auth.transport.requests import Request as GoogleAuthRequest\n",
    "        scopes=['https://www.googleapis.com/auth/cloud-platform','https://www.googleapis.com/auth/datastore']\n",
    "        creds,_=google.auth.default(scopes=scopes)\n",
    "        if not creds.valid or creds.expired or not creds.token:\n",
    "            creds.refresh(GoogleAuthRequest())\n",
    "        exp=(creds.expiry.timestamp()-120) if getattr(creds,'expiry',None) else time.time()+3000\n",
    "        return creds.token,exp,'google'\n",
    "    except Exception as e:\n",
    "        raise RuntimeError(f'Colab Google 인증 실패: {e}')\n",
    "\n",
    "def fb_token():\n",
    "    now=time.time()\n",
    "    if _token_cache['token'] and _token_cache['exp']>now+60:\n",
    "        return _token_cache['token']\n",
    "    order=['anonymous','google'] if AUTH_MODE=='auto' else [AUTH_MODE]\n",
    "    errors=[]\n",
    "    for kind in order:\n",
    "        try:\n",
    "            token,exp,used=_anonymous_token() if kind=='anonymous' else _google_token()\n",
    "            _token_cache.update(token=token,exp=exp,kind=used)\n",
    "            print(f'✅ Firebase 인증: {\"익명 인증\" if used==\"anonymous\" else \"Colab Google 계정\"}')\n",
    "            return token\n",
    "        except Exception as e:\n",
    "            errors.append(str(e))\n",
    "    raise RuntimeError('Firebase 인증 실패\\n- '+'\\n- '.join(errors))\n",
    "\n",
    "def fb_url(collection,doc_id):\n",
    "    did=quote(str(doc_id),safe='')\n",
    "    return f'https://firestore.googleapis.com/v1/projects/{quote(PROJECT_ID)}/databases/(default)/documents/{collection}/{did}?key={quote(API_KEY)}'\n",
    "\n",
    "def _fb_error(r,action,collection,doc_id):\n",
    "    detail=_err_message(r); used=_token_cache.get('kind') or AUTH_MODE\n",
    "    if r.status_code in (401,403):\n",
    "        if used=='google':\n",
    "            hint='선택한 Google 계정에 stockfactor-7d3b8 프로젝트 Firestore 접근 IAM 권한이 필요합니다.'\n",
    "        else:\n",
    "            hint='Firebase Authentication > 로그인 방법에서 익명(Anonymous)을 활성화하고 Firestore 규칙을 확인하세요.'\n",
    "        raise RuntimeError(f'Firebase {action} 권한 오류 ({r.status_code}) | {collection}/{doc_id}\\n{hint}\\nFirebase: {detail}')\n",
    "    raise RuntimeError(f'Firebase {action} 실패 ({r.status_code}) | {collection}/{doc_id}\\nFirebase: {detail}')\n",
    "\n",
    "def fb_get(collection,doc_id):\n",
    "    r=_session.get(fb_url(collection,doc_id),headers={'Authorization':f'Bearer {fb_token()}'},timeout=45)\n",
    "    if r.status_code==404: return None\n",
    "    if not r.ok: _fb_error(r,'읽기',collection,doc_id)\n",
    "    s=r.json().get('fields',{}).get('series',{}).get('stringValue')\n",
    "    return json.loads(s) if s else None\n",
    "\n",
    "def compact_json(obj):\n",
    "    return json.dumps(obj,ensure_ascii=False,separators=(',',':'),allow_nan=False)\n",
    "\n",
    "def fb_put(collection,doc_id,obj):\n",
    "    s=compact_json(obj); n=len(s.encode('utf-8'))\n",
    "    if n>=MAX_DOC_BYTES:\n",
    "        raise RuntimeError(f'{collection}/{doc_id}: {n:,} bytes — 문서 한도 보호로 저장 중단')\n",
    "    r=_session.patch(fb_url(collection,doc_id),headers={\n",
    "        'Authorization':f'Bearer {fb_token()}','Content-Type':'application/json'\n",
    "    },json={'fields':{'series':{'stringValue':s}}},timeout=60)\n",
    "    if not r.ok: _fb_error(r,'쓰기',collection,doc_id)\n",
    "    return n\n",
    "\n",
    "def quarter_id(ds):\n",
    "    y,m=int(ds[:4]),int(ds[5:7]); return f'{y}Q{(m-1)//3+1}'\n",
    "def us_meta_id(): return f'{US_DOC_PREFIX}__{UNIVERSE}'\n",
    "def us_chunk_id(q): return f'{US_DOC_PREFIX}__{UNIVERSE}__{q}'\n",
    "def latest_saved_date(meta):\n",
    "    chunks=sorted((meta or {}).get('chunks') or [])\n",
    "    if not chunks:return None\n",
    "    doc=fb_get(PRICE_COLLECTION,us_chunk_id(chunks[-1]))\n",
    "    ds=(doc or {}).get('d') or []\n",
    "    return max(ds) if ds else None\n",
    "\n",
    "def parse_iso_date(x):\n",
    "    try:return datetime.fromisoformat(str(x).replace('Z','+00:00')).date()\n",
    "    except Exception:return None\n",
    "\n",
    "if RUN_STATE['constituents'] and FIREBASE_WRITE:\n",
    "    try:\n",
    "        fb_token()\n",
    "        existing_meta=fb_get(META_COLLECTION,us_meta_id())\n",
    "        last_saved=latest_saved_date(existing_meta) if existing_meta else None\n",
    "        FIREBASE_ACTIVE=True\n",
    "        RUN_STATE['firebase_read']=True\n",
    "        print('✅ Firebase 연결 확인 완료')\n",
    "    except Exception as e:\n",
    "        FIREBASE_ERROR=str(e); FIREBASE_ACTIVE=False\n",
    "        mark_error('Firebase 연결',e)\n",
    "        print('⚠️ 인증/권한 문제가 해결되기 전에는 update/auto가 10년 전체 데이터를 다시 받지 않도록 차단합니다.')\n",
    "elif not FIREBASE_WRITE:\n",
    "    print('ℹ️ FIREBASE_WRITE=False — Firebase를 사용하지 않습니다.')\n",
    "\n",
    "if MODE=='auto':\n",
    "    EFFECTIVE_MODE='update' if (FIREBASE_ACTIVE and existing_meta) else 'full'\n",
    "else:\n",
    "    EFFECTIVE_MODE=MODE\n",
    "\n",
    "# Firebase 장애 + auto/update는 무거운 전체 다운로드를 하지 않음\n",
    "CAN_FETCH = RUN_STATE['constituents'] and not (FIREBASE_WRITE and not FIREBASE_ACTIVE and MODE in {'auto','update'})\n",
    "if not CAN_FETCH and RUN_STATE['constituents']:\n",
    "    print('⛔ 데이터 수집 보류: Firebase 연결을 먼저 해결한 뒤 이 셀부터 다시 실행하세요.')\n",
    "    print('   전체 CSV만 별도로 만들 목적이면 MODE=\"full\", FIREBASE_WRITE=False 로 실행할 수 있습니다.')\n",
    "\n",
    "if FIREBASE_ACTIVE:\n",
    "    current_symbols=current_symbols\n",
    "    existing_symbols=set((existing_meta or {}).get('tickers') or [])\n",
    "    new_symbols=[t for t in current_symbols if t not in existing_symbols] if existing_symbols else ([] if EFFECTIVE_MODE=='full' else current_symbols)\n",
    "    removed_symbols=sorted(existing_symbols-set(current_symbols))\n",
    "    last_fund_dt=parse_iso_date((existing_meta or {}).get('fundamentals_updated'))\n",
    "    fund_due=(last_fund_dt is None) or ((date.today()-last_fund_dt).days>=FUNDAMENTAL_REFRESH_DAYS)\n",
    "else:\n",
    "    existing_symbols=set(); new_symbols=[]; removed_symbols=[]; fund_due=True\n",
    "\n",
    "print('실행 모드:', MODE, '→', EFFECTIVE_MODE)\n",
    "print('Firebase 최근 저장일:', last_saved or '없음')\n",
    "print('신규 편입:',len(new_symbols),'개 / 제외:',len(removed_symbols),'개')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ===== 시세 / 거래대금 수집 =====\n",
    "prices=pd.DataFrame(); turnover=pd.DataFrame(); symbols=current_symbols[:]\n",
    "backfill_start=None; main_start=None; end=None\n",
    "\n",
    "def extract_field(raw,field,symbols):\n",
    "    if raw is None or raw.empty:return pd.DataFrame()\n",
    "    if isinstance(raw.columns,pd.MultiIndex):\n",
    "        if field in raw.columns.get_level_values(0): x=raw[field].copy()\n",
    "        elif field in raw.columns.get_level_values(1): x=raw.xs(field,axis=1,level=1).copy()\n",
    "        else:return pd.DataFrame(index=raw.index)\n",
    "    else:\n",
    "        x=raw[[field]].copy() if field in raw.columns else pd.DataFrame(index=raw.index)\n",
    "        if len(symbols)==1 and not x.empty:x.columns=[symbols[0]]\n",
    "    if isinstance(x,pd.Series):x=x.to_frame()\n",
    "    x.columns=[js_ticker(c) for c in x.columns]\n",
    "    return x\n",
    "\n",
    "def yf_batch(symbols,start,end,retries=3):\n",
    "    if not symbols or start>=end:return pd.DataFrame(),pd.DataFrame()\n",
    "    last=None\n",
    "    for attempt in range(retries):\n",
    "        try:\n",
    "            raw=yf.download(symbols,start=start,end=end,auto_adjust=True,actions=False,\n",
    "                            progress=False,threads=True,group_by='column',timeout=40)\n",
    "            c=extract_field(raw,'Close',symbols); v=extract_field(raw,'Volume',symbols)\n",
    "            if not c.empty:return c,v\n",
    "            last=RuntimeError('빈 데이터')\n",
    "        except Exception as e:last=e\n",
    "        time.sleep(1.5*(attempt+1))\n",
    "    print(f'⚠️ Yahoo batch 실패 ({len(symbols)}종목): {last}')\n",
    "    return pd.DataFrame(),pd.DataFrame()\n",
    "\n",
    "def fetch_market(symbols,start,end,batch=60):\n",
    "    closes=[];vols=[]\n",
    "    for i in range(0,len(symbols),batch):\n",
    "        part=symbols[i:i+batch]\n",
    "        print(f'가격 수집 {i+1:>3}-{min(i+len(part),len(symbols)):>3} / {len(symbols)}',end='\\r')\n",
    "        c,v=yf_batch(part,start,end)\n",
    "        if not c.empty:closes.append(c)\n",
    "        if not v.empty:vols.append(v)\n",
    "    print(' '*90,end='\\r')\n",
    "    close=pd.concat(closes,axis=1) if closes else pd.DataFrame()\n",
    "    volume=pd.concat(vols,axis=1) if vols else pd.DataFrame()\n",
    "    if not close.empty:close=close.loc[:,~close.columns.duplicated()].sort_index()\n",
    "    if not volume.empty:volume=volume.loc[:,~volume.columns.duplicated()].sort_index()\n",
    "    return close,volume\n",
    "\n",
    "if CAN_FETCH:\n",
    "    try:\n",
    "        today=date.today(); end=(today+timedelta(days=1)).isoformat()\n",
    "        if EFFECTIVE_MODE=='update' and last_saved:\n",
    "            main_start=(datetime.strptime(last_saved,'%Y-%m-%d').date()+timedelta(days=1)).isoformat()\n",
    "        else:\n",
    "            main_start=(today-timedelta(days=max(370,YEARS*366))).isoformat()\n",
    "        main_close,main_volume=fetch_market(symbols,main_start,end,DOWNLOAD_BATCH)\n",
    "        back_close=pd.DataFrame();back_volume=pd.DataFrame()\n",
    "        if EFFECTIVE_MODE=='update' and last_saved and new_symbols:\n",
    "            backfill_start=(today-timedelta(days=NEW_TICKER_BACKFILL_DAYS)).isoformat()\n",
    "            back_close,back_volume=fetch_market(new_symbols,backfill_start,end,min(DOWNLOAD_BATCH,40))\n",
    "        close=main_close.combine_first(back_close) if not main_close.empty else back_close.copy()\n",
    "        volume=main_volume.combine_first(back_volume) if not main_volume.empty else back_volume.copy()\n",
    "        earliest_start=min([x for x in [main_start,backfill_start] if x])\n",
    "        bench_close,_=yf_batch([BENCHMARK],earliest_start,end)\n",
    "        bench=bench_close.iloc[:,0].rename('BENCH') if not bench_close.empty else pd.Series(dtype=float,name='BENCH')\n",
    "        if close.empty and bench.empty:\n",
    "            raise RuntimeError('Yahoo Finance에서 가격 데이터를 한 건도 받지 못했습니다.')\n",
    "        idx=bench.dropna().index if len(bench.dropna()) else close.index\n",
    "        close=close.reindex(idx).sort_index();volume=volume.reindex(idx).sort_index();bench=bench.reindex(idx)\n",
    "        for t in symbols:\n",
    "            if t not in close.columns:close[t]=np.nan\n",
    "            if t not in volume.columns:volume[t]=np.nan\n",
    "        close=close[symbols];volume=volume[symbols];turnover=close*volume\n",
    "        prices=pd.concat([close,bench],axis=1)\n",
    "        prices.index=pd.to_datetime(prices.index).strftime('%Y-%m-%d')\n",
    "        turnover.index=pd.to_datetime(turnover.index).strftime('%Y-%m-%d')\n",
    "        prices.index.name='date';turnover.index.name='date'\n",
    "        valid=sum(close[c].notna().any() for c in symbols)\n",
    "        if valid<max(10,int(len(symbols)*0.7)):\n",
    "            raise RuntimeError(f'유효 가격 종목이 너무 적습니다: {valid}/{len(symbols)}')\n",
    "        RUN_STATE['market']=True\n",
    "        print(f'✅ 수집 구간: {earliest_start} → {end} / {len(prices):,} 거래일 / 유효 {valid}/{len(symbols)}')\n",
    "        if backfill_start:print(f'신규 편입 백필: {backfill_start} → {end} / {len(new_symbols)}종목')\n",
    "        display(prices.tail())\n",
    "    except Exception as e:\n",
    "        mark_error('시세 수집',e)\n",
    "else:\n",
    "    print('ℹ️ 시세 수집 단계 건너뜀')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ===== 현재 기본 재무 수집 =====\n",
    "meta_now={};master=pd.DataFrame()\n",
    "\n",
    "def one_info(ticker):\n",
    "    try:\n",
    "        x=yf.Ticker(ticker).get_info() or {}\n",
    "        return ticker,{\n",
    "            'mcap':clean_num(x.get('marketCap'),2),'per':clean_num(x.get('trailingPE'),4),\n",
    "            'pbr':clean_num(x.get('priceToBook'),4),'roe':pct_like(x.get('returnOnEquity')),\n",
    "            'debt':pct_like(x.get('debtToEquity')),\n",
    "            'opgrowth':pct_like(x.get('earningsGrowth') if x.get('earningsGrowth') is not None else x.get('revenueGrowth')),\n",
    "            'eps_growth':pct_like(x.get('earningsQuarterlyGrowth')),'eps_rev':pct_like(x.get('earningsGrowth')),\n",
    "        }\n",
    "    except Exception:return ticker,{}\n",
    "\n",
    "if RUN_STATE['market']:\n",
    "    try:\n",
    "        if FUNDAMENTALS_MODE=='always':fund_targets=symbols\n",
    "        elif FUNDAMENTALS_MODE=='off':fund_targets=[]\n",
    "        else:fund_targets=symbols if (not existing_meta or fund_due) else new_symbols\n",
    "        fund={}\n",
    "        if fund_targets:\n",
    "            print(f'기본 재무 수집 ({len(fund_targets)}종목)')\n",
    "            with ThreadPoolExecutor(max_workers=FUNDAMENTAL_WORKERS) as ex:\n",
    "                futs=[ex.submit(one_info,t) for t in fund_targets]\n",
    "                for i,f in enumerate(as_completed(futs),1):\n",
    "                    tk,row=f.result();fund[tk]=row\n",
    "                    if i%25==0 or i==len(futs):print(f'  {i}/{len(futs)}')\n",
    "        else:print('기본 재무: 기존 Firebase 값을 재사용')\n",
    "        old=(existing_meta or {}).get('meta') or {}\n",
    "        c=constituents.set_index('ticker')\n",
    "        for tk in symbols:\n",
    "            m=dict(old.get(tk) or {});r=c.loc[tk];m.update(name=str(r['name']),sector=str(r['sector']))\n",
    "            for k,v in (fund.get(tk) or {}).items():\n",
    "                if v is not None:m[k]=v\n",
    "            meta_now[tk]=m\n",
    "        master=pd.DataFrame([{'ticker':tk,**meta_now.get(tk,{})} for tk in symbols])\n",
    "        for k in ['name','sector','mcap','per','pbr','roe','debt','opgrowth','eps_growth','eps_rev']:\n",
    "            if k not in master.columns:master[k]=None\n",
    "        master=master[['ticker','name','sector','mcap','per','pbr','roe','debt','opgrowth','eps_growth','eps_rev']]\n",
    "        master.to_csv(ROOT/'master.csv',index=False,encoding='utf-8-sig')\n",
    "        prices.to_csv(ROOT/'prices.csv',encoding='utf-8-sig');turnover.to_csv(ROOT/'turnover.csv',encoding='utf-8-sig')\n",
    "        RUN_STATE['fundamentals']=True\n",
    "        display(master.head())\n",
    "    except Exception as e:\n",
    "        mark_error('재무/CSV 생성',e)\n",
    "else:\n",
    "    print('ℹ️ 재무 단계 건너뜀')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ===== 분기 청크 병합 / Firebase 저장 =====\n",
    "written=[];total_added=0;total_filled=0;UPLOAD_ERROR=''\n",
    "\n",
    "def frame_values(frame,dates,col,digits=6,integer=False):\n",
    "    out=[]\n",
    "    for d in dates:\n",
    "        try:v=frame.at[d,col]\n",
    "        except Exception:v=np.nan\n",
    "        if pd.isna(v) or not np.isfinite(v):out.append(None)\n",
    "        elif integer:out.append(int(round(float(v))))\n",
    "        else:out.append(round(float(v),digits))\n",
    "    return out\n",
    "\n",
    "def incoming_price_chunk(qdates):\n",
    "    p={'BENCH':frame_values(prices,qdates,'BENCH',6)}\n",
    "    for tk in symbols:p[tk]=frame_values(prices,qdates,tk,6)\n",
    "    return {'d':qdates,'p':p}\n",
    "\n",
    "def incoming_tv_chunk(qdates):\n",
    "    return {'d':qdates,'v':{tk:frame_values(turnover,qdates,tk,0,True) for tk in symbols}}\n",
    "\n",
    "def merge_chunk(old,inc,key):\n",
    "    old=old if isinstance(old,dict) else {};old_dates=list(old.get('d') or [])\n",
    "    src=old.get(key) or {};incsrc=inc.get(key) or {}\n",
    "    cols=list(dict.fromkeys([*src.keys(),*incsrc.keys()]));pos={d:i for i,d in enumerate(old_dates)};ds=list(old_dates)\n",
    "    values={c:[(src.get(c) or [None]*len(ds))[i] if i<len(src.get(c) or []) else None for i in range(len(ds))] for c in cols}\n",
    "    added=filled=skipped=0\n",
    "    for i,d in enumerate(list(inc.get('d') or [])):\n",
    "        j=pos.get(d)\n",
    "        if j is None:\n",
    "            j=len(ds);pos[d]=j;ds.append(d);added+=1\n",
    "            for c in cols:values[c].append(None)\n",
    "        else:skipped+=1\n",
    "        for c in cols:\n",
    "            arr=incsrc.get(c) or [];v=arr[i] if i<len(arr) else None\n",
    "            if v is not None and values[c][j] is None:values[c][j]=v;filled+=1\n",
    "    order=sorted(range(len(ds)),key=lambda i:ds[i])\n",
    "    return {'d':[ds[i] for i in order],key:{c:[values[c][i] for i in order] for c in cols}},added,filled,skipped\n",
    "\n",
    "if RUN_STATE['market'] and RUN_STATE['fundamentals'] and FIREBASE_ACTIVE and FIREBASE_WRITE:\n",
    "    try:\n",
    "        affected={}\n",
    "        for d in list(prices.index):affected.setdefault(quarter_id(d),[]).append(d)\n",
    "        for q,qdates in sorted(affected.items()):\n",
    "            doc_id=us_chunk_id(q)\n",
    "            oldp=fb_get(PRICE_COLLECTION,doc_id);merged,added,filled,_=merge_chunk(oldp,incoming_price_chunk(qdates),'p')\n",
    "            if added or filled:\n",
    "                n=fb_put(PRICE_COLLECTION,doc_id,merged);written.append((f'{PRICE_COLLECTION}/{doc_id}',added,filled,n));total_added+=added;total_filled+=filled\n",
    "            oldv=fb_get(TV_COLLECTION,doc_id);mergedv,addedv,filledv,_=merge_chunk(oldv,incoming_tv_chunk(qdates),'v')\n",
    "            if addedv or filledv:\n",
    "                n=fb_put(TV_COLLECTION,doc_id,mergedv);written.append((f'{TV_COLLECTION}/{doc_id}',addedv,filledv,n))\n",
    "        oldmeta=existing_meta or {};chunks=sorted(set((oldmeta.get('chunks') or [])+list(affected)))\n",
    "        membership_changed=(list(oldmeta.get('tickers') or [])!=symbols)\n",
    "        meta_base={\n",
    "            'schema':'factor-us-quarter-v1','universe':UNIVERSE,'benchmark':BENCH_KEY,'tickers':symbols,'meta':meta_now,\n",
    "            'chunks':chunks,'hasTV':True,\n",
    "            'constituents_updated':date.today().isoformat() if membership_changed or not oldmeta else oldmeta.get('constituents_updated'),\n",
    "            'fundamentals_updated':date.today().isoformat() if (FUNDAMENTALS_MODE!='off' and (not oldmeta or fund_due or new_symbols)) else oldmeta.get('fundamentals_updated')\n",
    "        }\n",
    "        old_compare={k:v for k,v in oldmeta.items() if k!='updated'}\n",
    "        if compact_json(meta_base)!=compact_json(old_compare) or total_added or total_filled:\n",
    "            meta={**meta_base,'updated':datetime.now(timezone.utc).isoformat().replace('+00:00','Z')}\n",
    "            n=fb_put(META_COLLECTION,us_meta_id(),meta);written.append((f'{META_COLLECTION}/{us_meta_id()}',0,0,n))\n",
    "        RUN_STATE['firebase_write']=True\n",
    "        print(f'✅ Firebase 신규 거래일 {total_added}일 / 결측 보강 {total_filled:,}셀')\n",
    "        for path,nrow,nfill,nbyte in written:print(f'  {path:<44} +{nrow:>3}일 / 보강 {nfill:>6} / {nbyte/1024:>7.1f} KB')\n",
    "    except Exception as e:\n",
    "        UPLOAD_ERROR=str(e);FIREBASE_ACTIVE=False\n",
    "        mark_error('Firebase 저장',e)\n",
    "        print('⚠️ CSV/ZIP 생성은 계속됩니다. 위 오류를 해결한 뒤 이 저장 셀만 다시 실행할 수 있습니다.')\n",
    "elif RUN_STATE['market'] and FIREBASE_WRITE and not FIREBASE_ACTIVE:\n",
    "    print('⚠️ Firebase 연결이 없어 저장을 건너뜁니다.')\n",
    "elif RUN_STATE['market'] and not FIREBASE_WRITE:\n",
    "    print('ℹ️ FIREBASE_WRITE=False — Firebase 저장 생략')\n",
    "else:\n",
    "    print('ℹ️ 선행 데이터가 없어 Firebase 저장 단계 건너뜀')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ===== 결과 파일 / 최종 진단 =====\n",
    "zip_path=None\n",
    "if RUN_STATE['market'] and RUN_STATE['fundamentals']:\n",
    "    try:\n",
    "        fb_status='uploaded' if RUN_STATE['firebase_write'] else ('disabled' if not FIREBASE_WRITE else 'not-uploaded')\n",
    "        readme=(f'TheBest Insight US factor data\\nUniverse: {UNIVERSE_LABEL}\\nMode: {EFFECTIVE_MODE}\\n'\n",
    "                f'Collected: {date.today().isoformat()}\\nRows in this run: {len(prices)}\\nBenchmark: {BENCHMARK}\\n'\n",
    "                f'Firebase: {fb_status}\\n\\n'\n",
    "                '현재 구성종목 기준 데이터입니다. update는 신규 거래일과 신규 편입 종목 결측만 보강합니다.\\n')\n",
    "        (ROOT/'README_US_DATA.txt').write_text(readme,encoding='utf-8')\n",
    "        zip_path=ROOT/f'thebest_{UNIVERSE}_factor_data.zip'\n",
    "        with zipfile.ZipFile(zip_path,'w',zipfile.ZIP_DEFLATED) as z:\n",
    "            for fn in ['prices.csv','turnover.csv','master.csv','README_US_DATA.txt']:\n",
    "                p=ROOT/fn\n",
    "                if p.exists():z.write(p,arcname=fn)\n",
    "        RUN_STATE['files']=True\n",
    "        print('✅ 결과 파일 완료:',zip_path)\n",
    "\n",
    "        # Colab에서는 FileLink가 브라우저에 따라 다운로드로 연결되지 않을 수 있어\n",
    "        # google.colab.files.download()를 기본 다운로드 방식으로 사용합니다.\n",
    "        try:\n",
    "            from google.colab import files as colab_files\n",
    "            print('⬇️ 다운로드를 시작합니다. 브라우저에서 여러 파일 다운로드/팝업 허용이 필요할 수 있습니다.')\n",
    "            colab_files.download(str(zip_path))\n",
    "            RUN_STATE['download_triggered']=True\n",
    "        except Exception as download_error:\n",
    "            RUN_STATE['download_triggered']=False\n",
    "            print('⚠️ 자동 다운로드를 시작하지 못했습니다:', download_error)\n",
    "            print('   아래 링크를 클릭하거나 Colab 왼쪽 파일 탭에서 ZIP 파일을 우클릭 → 다운로드 하세요.')\n",
    "            display(FileLink(str(zip_path)))\n",
    "    except Exception as e:\n",
    "        mark_error('결과 ZIP 생성',e)\n",
    "else:\n",
    "    print('ℹ️ 시세/CSV가 완성되지 않아 결과 ZIP 생성을 건너뜁니다.')\n",
    "\n",
    "print('\\n================ 실행 결과 ================')\n",
    "for k in ['constituents','firebase_read','market','fundamentals','firebase_write','files']:\n",
    "    print(('✅' if RUN_STATE[k] else '— '),f'{k:16}',RUN_STATE[k])\n",
    "if RUN_STATE.get('files'):\n",
    "    print(('✅' if RUN_STATE.get('download_triggered') else 'ℹ️'), f\"{'download':16}\", RUN_STATE.get('download_triggered', False))\n",
    "if RUN_STATE['errors']:\n",
    "    print('\\n[확인할 오류]')\n",
    "    for i,e in enumerate(RUN_STATE['errors'],1):print(f'{i}. {e}')\n",
    "    print('\\n※ 이 셀까지 빨간 오류 없이 도달하도록 구성했습니다. 위의 첫 번째 오류가 실제 원인입니다.')\n",
    "else:\n",
    "    print('\\n✅ 전체 파이프라인 정상 완료')\n"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "name": "주도주_팩터엔진_US_Colab.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.x"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}