test_sp500_only.py

import pandas as pd
import requests
from io import StringIO

def test_sp500_backtrack():
    print("Testing dynamic S&P 500 Wikipedia parsing with MultiIndex flattening...")
    url = "https://en.wikipedia.org/wiki/List_of_S%26P_500_companies"
    headers = {"User-Agent": "Mozilla/5.0"}
    
    response = requests.get(url, headers=headers)
    if response.status_code != 200:
        print(f"Failed to fetch: HTTP {response.status_code}")
        return
        
    tables = pd.read_html(StringIO(response.text))
    df_current = tables[0]
    df_changes = tables[1]
    
    # Flatten MultiIndex columns: e.g. ('Added', 'Ticker') -> 'Added_Ticker'
    df_changes.columns = [f"{c[0]}_{c[1]}" if isinstance(c, tuple) else c for c in df_changes.columns]
    
    current_symbols = set(df_current['Symbol'].dropna().tolist())
    
    # Use the flattened date column 'Effective Date_Effective Date'
    date_col = 'Effective Date_Effective Date'
    df_changes[date_col] = pd.to_datetime(df_changes[date_col], errors='coerce')
    df_changes = df_changes.dropna(subset=[date_col]).sort_values(by=date_col, ascending=False)
    
    cutoff = pd.to_datetime("2020-01-01")
    symbols = set(current_symbols)
    
    for idx, row in df_changes.iterrows():
        if row[date_col] < cutoff:
            break
        
        # added and removed columns are uniquely named now
        added = str(row.get('Added_Ticker') or '').strip()
        removed = str(row.get('Removed_Ticker') or '').strip()
        
        if added and added != 'nan' and added != '':
            symbols.discard(added)
        if removed and removed != 'nan' and removed != '':
            symbols.add(removed)
            
    print(f"✅ Success! Reconstructed S&P 500 symbols as of 2020-01-01: {len(symbols)}")
    print(f"Sample of 10 symbols: {list(symbols)[:10]}")

if __name__ == "__main__":
    try:
        test_sp500_backtrack()
    except Exception as e:
        print(f"❌ Failed: {e}")
        import traceback
        traceback.print_exc()