getMetaData.py

import yfinance as yf
import pandas as pd
import time
import os
from datetime import datetime

# Create output directory if it doesn't exist
output_dir = "company_data"
if not os.path.exists(output_dir):
    os.makedirs(output_dir)

# Complete ticker mappings for all companies
ticker_mappings = {
    # A
    "3M Company": "MMM",
    "AB Volvo": "VOLV-B.ST",
    "ABB": "ABB",
    "ACS Group S.A.": "ACS.MC",
    "AIA Group": "1299.HK",
    "AIG": "AIG",
    "ANZ Group Holdings": "ANZ.AX",
    "ATT": "T",
    "AXA": "CS.PA",
    "AbbVie": "ABBV",
    "Abbott Laboratories": "ABT",
    "Accenture PLC": "ACN",
    "Aeon Co": "8267.T",
    "Agricultural Bank of China Limited (ABC Bank)": "601288.SS",
    "Airbus SE": "AIR.PA",
    "Aisin Corporation": "7259.T",
    "Albertsons": "ACI",
    "Alibaba Group": "BABA",
    "Alimentation Couche-Tard": "ATD.TO",
    "Allianz": "ALV.DE",
    "Allstate": "ALL",
    "Alphabet Inc. (Google)": "GOOGL",
    "Amazon": "AMZN",
    "America Movil": "AMX",
    "American Airlines Group": "AAL",
    "American Express": "AXP",
    "Anheuser-Busch InBev S.A. NV (AB InBev)": "BUD",
    "Apollo Global Management": "APO",
    "Apple": "AAPL",
    "ArcelorMittal": "MT",
    "Archer Daniels Midland": "ADM",
    "Arrow Electronics": "ARW",
    "Assicurazioni Generali S.p.A.": "G.MI",
    "AstraZeneca": "AZN",
    
    # B
    "BASF SE": "BAS.DE",
    "BHP Group": "BHP",
    "BMW Group": "BMW.DE",
    "BP PLC": "BP",
    "BYD": "002594.SZ",
    "Banco Bilbao Vizcaya Argentaria": "BBVA",
    "Banco Bradesco S.A.": "BBD",
    "Banco Santander": "SAN",
    "Bank of America Corporation": "BAC",
    "Bank of China Limited": "601988.SS",
    "Bank of Communications Co., Ltd.": "601328.SS",
    "Bank of Montreal": "BMO",
    "Bank of New York Mellon": "BK",
    "Bank of Nova Scotia (Scotiabank)": "BNS",
    "Barclays": "BARC.L",
    "Bayer": "BAYN.DE",
    "Beijing Jianlong Heavy Industry Group": None,  # Private company
    "Best Buy": "BBY",
    "Bharat Petroleum Corporation Limited": "BPCL.NS",
    "Boeing": "BA",
    "Bosch Group": None,  # Private company
    "Bouygues": "EN.PA",
    "Bristol-Myers Squibb Company": "BMY",
    "British American Tobacco": "BTI",
    "Broadcom": "AVGO",
    "Brookfield Corporation": "BN",
    "Bunge": "BG",
    
    # C
    "CITIC Group": "0267.HK",
    "CK Hutchison Holdings Limited": "0001.HK",
    "CNOOC Limited (China National Offshore Oil Corporation Limited)": "0883.HK",
    "COFCO Capital Holdings Co., Ltd.": "002423.SZ",
    "COSCO Shipping Development Co., Ltd.": "601866.SS",
    "CPC": "CPC.TW",
    "CRH": "CRH",
    "CRRC Group": "601766.SS",
    "CVS Health": "CVS",
    "Caixa Economica Federal": None,  # Government bank
    "Canadian Imperial Bank of Commerce": "CM",
    "Capital One Financial": "COF",
    "Cardinal Health": "CAH",
    "Carrefour": "CA.PA",
    "Caterpillar": "CAT",
    "Cencora": "COR",
    "Cenovus Energy": "CVE",
    "Centene": "CNC",
    "Charter Communications": "CHTR",
    "Chery Holding Group": None,  # Private company
    "Chevron": "CVX",
    "China Baowu Steel Group": "600019.SS",
    "China Communications Construction Co., Ltd.": "601800.SS",
    "China Construction Bank Co., Ltd.": "601939.SS",
    "China Datang": "601991.SS",
    "China Electronics Huada Technology Co., Ltd.": None,  # Private/subsidiary
    "China Energy Engineering Group": "3996.HK",
    "China FAW Group": "000800.SZ",
    "China Huaneng Group": "600011.SS",
    "China Life Insurance": "601628.SS",
    "China Merchants Bank Co., Ltd. (CMB)": "600036.SS",
    "China Minmetals Nonferrous Metals Co., Ltd.": "000630.SZ",
    "China Minsheng Banking Corporation (CMBC)": "600016.SS",
    "China Mobile Communications": "0941.HK",
    "China National Building Material Group": "3323.HK",
    "China National Coal Group": "601088.SS",
    "China National Nuclear Power Co., Ltd. (CNNC)": "601985.SS",
    "China National Petroleum": "601857.SS",
    "China North Industries Group": None,  # State-owned
    "China Pacific Insurance (Group)": "601601.SS",
    "China Railway Engineering Group": "601390.SS",
    "China Resources Beer": "0291.HK",
    "China South Industries Group": None,  # State-owned
    "China State Construction Engineering": "601668.SS",
    "China State Shipbuilding": "600150.SS",
    "China Telecommunications": "0728.HK",
    "China United Network Communications Limited (China Unicom)": "0762.HK",
    "China Vanke Co., Ltd.": "000002.SZ",
    "Christian Dior": "CDI.PA",
    "Chubb": "CB",
    "Cigna": "CI",
    "Cisco": "CSCO",
    "Citigroup": "C",
    "Coca-Cola": "KO",
    "Comcast": "CMCSA",
    "Commonwealth Bank of Australia": "CBA.AX",
    "Compagnie de Saint-Gobain": "SGO.PA",
    "Compass Group": "CPG.L",
    "ConocoPhillips": "COP",
    "Contemporary Amperex Technology Co., Ltd. (CATL)": "300750.SZ",
    "Continental": "CON.DE",
    "Coop Group": None,  # Cooperative
    "Costco Wholesale": "COST",
    "Credit Agricole S.A.": "ACA.PA",
    "Cummins": "CMI",
    
    # D
    "D.R. Horton Inc.": "DHI",
    "DHL Group": "DPW.DE",
    "DZ Bank": None,  # Cooperative bank
    "Dai-Ichi Life Holdings Inc.": "8750.T",
    "Daimler Truck Holding AG": "DTG.DE",
    "Daiwa House Industry Co., Ltd": "1925.T",
    "Daiwa House Industry Co., Ltd.": "1925.T",  # Duplicate entry
    "Deere": "DE",
    "Dell Technologies": "DELL",
    "Delta Air Lines": "DAL",
    "Denso Corporation": "6902.T",
    "Deutsche Bahn AG (German Railways)": None,  # State-owned
    "Deutsche Bank": "DB",
    "Deutsche Telekom": "DTE.DE",
    "Dollar General Corp": "DG",
    "Dongfeng Motor Group Co., Ltd.": "0489.HK",
    "Dow": "DOW",
    
    # E
    "E.ON SE": "EOAN.DE",
    "ELO": None,  # Private retail group
    "ENI": "ENI.MI",
    "EXOR Group": "EXO.MI",
    "Ecopetrol S.A.": "EC",
    "Electricite de France": "EDF.PA",
    "Elevance Health": "ELV",
    "Eli Lilly": "LLY",
    "Emirates Group": None,  # State-owned
    "Enbridge": "ENB",
    "Enel": "ENEL.MI",
    "Eneos Holdings Inc.": "5020.T",
    "Energy Transfer": "ET",
    "Engie": "ENGI.PA",
    "Enterprise Products Partners": "EPD",
    "Equinor": "EQNR",
    "Exxon Mobil": "XOM",
    
    # F
    "Fairfax Financial Holdings": "FFH.TO",
    "FedEx": "FDX",
    "Fomento Economico Mexicano": "FMX",
    "Ford Motor": "F",
    "Freddie Mac": "FMCC",
    
    # G
    "GS Caltex": None,  # Joint venture
    "GSK": "GSK",
    "Gazprom": "GAZP.ME",
    "General Dynamics": "GD",
    "General Electric Company (GE)": "GE",
    "General Motors": "GM",
    "George Weston": "WN.TO",
    "Glencore PLC": "GLEN.L",
    "Goldman Sachs Group": "GS",
    "Greenland Holding Group": "600606.SS",
    
    # H
    "HCA Healthcare": "HCA",
    "HDFC Bank": "HDB",
    "HP Inc.": "HPQ",
    "HSBC Holdings": "HSBC",
    "Haier Smart Home Co., Ltd.": "600690.SS",
    "Hanwha Corp": "000880.KS",
    "Heineken": "HEIA.AS",
    "Hengli Group": "600346.SS",
    "Hitachi": "6501.T",
    "Home Depot": "HD",
    "Hon Hai Precision Industry Co., Ltd. (Foxconn)": "2317.TW",
    "Honda Motor Co., Ltd.": "HMC",
    "Honeywell International": "HON",
    "Humana": "HUM",
    "Hyundai Mobis Co., Ltd.": "012330.KS",
    "Hyundai Motor": "005380.KS",
    
    # I
    "IBM": "IBM",
    "ING Group": "ING",
    "Iberdrola": "IBE.MC",
    "Idemitsu Kosan Co., Ltd.": "5019.T",
    "Indian Oil Corporation Limited": "IOC.NS",
    "Industrial Bank Co., Ltd ": "601166.SS",
    "Industrial Bank Co., Ltd.": "601166.SS",  # Duplicate entry
    "Industrial and Commercial Bank of China Ltd. (ICBC)": "601398.SS",
    "Ingka Group": None,  # Private (IKEA holding)
    "Intel": "INTC",
    "Intesa Sanpaolo S.p.A.": "ISP.MI",
    "Itochu Corporation": "8001.T",
    
    # J
    "J. Sainsbury": "SBRY.L",
    "JBS S.A.": "JBSS3.SA",
    "JD.com": "JD",
    "JFE Holdings Inc.": "5411.T",
    "JPMorgan Chase": "JPM",
    "Jabil": "JBL",
    "Jardine Matheson": "J36.SI",
    "Jeronimo Martins": "JMT.LS",
    "Jiangxi Copper Co., Ltd.": "600362.SS",
    "Johnson and Johnson": "JNJ",
    
    # K
    "KB Financial Group": "105560.KS",
    "KDDI Corporation": "9433.T",
    "Kia": "000270.KS",
    "Koc Holding A.S.": "KCHOL.IS",
    "Korea Electric Power Corporation (KEPCO)": "015760.KS",
    "Korea Gas Corp (KOGAS)": "036460.KS",
    "Kroger": "KR",
    
    # L
    "L'Oréal": "OR.PA",
    "LG Chem Ltd.": "051910.KS",
    "LG Electronics": "066570.KS",
    "La Poste": None,  # State-owned
    "Landesbank Baden-Wurttemberg": None,  # State-owned bank
    "Lennar": "LEN",
    "Lenovo Group Limited": "0992.HK",
    "Linde": "LIN",
    "Lloyds Banking Group": "LLOY.L",
    "Lockheed Martin": "LMT",
    "Louis Dreyfus": None,  # Private company
    "Lowe's": "LOW",
    "Lufthansa Group": "LHA.DE",
    "Lukoil": "LKOH.ME",
    "Luxshare Precision Industry Co., Ltd.": "002475.SZ",
    "LyondellBasell Industries NV": "LYB",
    
    # M
    "MSAD Insurance Group Holdings": "8725.T",
    "Maersk Group": "MAERSK-B.CO",
    "Magna International": "MGA",
    "Marathon Petroleum": "MPC",
    "Marubeni Corporation": "8002.T",
    "Massachusetts Mutual Life Insurance": None,  # Mutual company
    "Mazda Motor Corporation": "7261.T",
    "McKesson": "MCK",
    "Meituan": "3690.HK",
    "Mercadona S.A.": None,  # Private company
    "Mercedes-Benz Group": "MBG.DE",
    "Merck": "MRK",
    "MetLife": "MET",
    "Meta Platforms": "META",
    "Metro": "B4B.DE",
    "Microsoft": "MSFT",
    "Midea Group Co., Ltd.": "000333.SZ",
    "Mitsubishi": "8058.T",
    "Mitsubishi Electric": "6503.T",
    "Mitsubishi Heavy Industries Ltd. (MHI)": "7011.T",
    "Mitsubishi UFJ Financial Group": "8306.T",
    "Mitsui": "8031.T",
    "Mizuho Financial Group": "8411.T",
    "Molina Healthcare": "MOH",
    "Mondelez International": "MDLZ",
    "Morgan Stanley": "MS",
    "Munich Re": "MUV2.DE",
    
    # N
    "National Australia Bank": "NAB.AX",
    "Nationwide Building Society": None,  # Mutual society
    "Nestle": "NESN.SW",
    "Netflix": "NFLX",
    "New Hope Holding Group": "000876.SZ",
    "New York Life Insurance and Annuity Corp": None,  # Mutual company
    "Nike": "NKE",
    "Nippon Life Insurance": None,  # Mutual company
    "Nippon Steel Corporation": "5401.T",
    "Nippon Telegraph and Telephone Corporation (NTT)": "9432.T",
    "Nissan Motor": "7201.T",
    "Northrop Grumman": "NOC",
    "Northwestern Mutual": None,  # Mutual company
    "Novartis": "NVS",
    "Novo Nordisk": "NVO",
    "Nucor": "NUE",
    "Nvidia": "NVDA",
    
    # O
    "OMV Group": "OMV.VI",
    "Oil  Natural Gas": "ONGC.NS",
    "Olam Group Limited": "O32.SI",
    "Oracle": "ORCL",
    "Orange": "ORA.PA",
    "Orlen S.A.": "PKN.WA",
    
    # P
    "PBF Energy Inc.": "PBF",
    "PDD Holdings Inc.": "PDD",
    "PTT PCL": "PTT.BK",
    "Panasonic Holdings Corporation": "6752.T",
    "Pegatron Corporation": "4938.TW",
    "Pemex": None,  # State-owned
    "PepsiCo": "PEP",
    "Performance Food Group": "PFGC",
    "Pertamina": None,  # State-owned
    "Petrobras": "PBR",
    "Petronas": None,  # State-owned
    "Pfizer": "PFE",
    "Philip Morris International": "PM",
    "Phillips 66": "PSX",
    "Phoenix Pharmahandel GmbH and Co KG": None,  # Private company
    "Ping An Insurance": "601318.SS",
    "Plains GP Holdings": "PAGP",
    "Posco Holdings Inc. ": "005490.KS",
    "Power Construction Corporation of China (PowerChina)": "601669.SS",
    "Power Corp. of Canada": "POW.TO",
    "Procter  Gamble": "PG",
    "Progressive": "PGR",
    "Prudential Financial": "PRU",
    
    # Q
    "Qualcomm": "QCOM",
    "Quanta Computer Inc.": "2382.TW",
    
    # R
    "RTX": "RTX",
    "Raizen S.A.": "RAIZ4.SA",
    "Reliance Industries": "RELIANCE.NS",
    "Renault": "RNO.PA",
    "Repsol S.A.": "REP.MC",
    "Roche Group": "ROG.SW",
    "Rosneft Oil": "ROSN.ME",
    "Royal Bank of Canada": "RY",
    
    # S
    "S.F. Holding Co., Ltd.": "002352.SZ",
    "SAIC Motor Corporation Limited": "600104.SS",
    "SAP": "SAP",
    "SK": "034730.KS",
    "SNCF Group": None,  # State-owned
    "Salesforce": "CRM",
    "Samsung CT Corporation": "000830.KS",
    "Samsung Electronics Co., Ltd.": "005930.KS",
    "Sanofi": "SAN.PA",
    "Saudi Aramco": "2222.SR",
    "Sberbank of Russia": "SBER.ME",
    "Schlumberger": "SLB",
    "Schneider Electric": "SU.PA",
    "Seven  I Holdings": "3382.T",
    "Shaanxi Coal Chemical Industry": "601225.SS",
    "Shaanxi Yanchang Petroleum (Group)": None,  # State-owned
    "Shandong Energy Group": None,  # State-owned
    "Shandong Hi-Speed Group": "600350.SS",
    "Shanghai Pharmaceuticals Holding Co., Ltd.": "601607.SS",
    "Shanghai Pudong Development Bank (SPDB)": "600000.SS",
    "Shell": "SHEL",
    "Shenzhen Investment Holdings Bay Area Development Co., Ltd.": "000042.SZ",
    "Shougang Group": "000959.SZ",
    "Siemens": "SIE.DE",
    "Siemens Energy AG": "ENR.DE",
    "Sinochem Holdings": "600500.SS",
    "Sinomach": None,  # State-owned
    "Sinopec Group": "600028.SS",
    "Sinopharm Group Co., Ltd.": "1099.HK",
    "Societe Generale": "GLE.PA",
    "SoftBank Group Corp": "9984.T",
    "Sompo Holdings Inc.": "8630.T",
    "Sony": "SONY",
    "Standard Chartered": "STAN.L",
    "Starbucks": "SBUX",
    "State Bank of India (SBI)": "SBIN.NS",
    "State Power Investment": None,  # State-owned
    "Stellantis": "STLA",
    "Subaru": "7270.T",
    "Sumitomo": "8053.T",
    "Sumitomo Mitsui Financial Group": "8316.T",
    "Suncor Energy": "SU",
    "Suzuki Motor Corporation": "7269.T",
    "Swiss Re AG": "SREN.SW",
    "Sysco": "SYY",
    
    # T
    "TD Synnex": "SNX",
    "TJX": "TJX",
    "TSX": None,  # This seems to be an error - TSX is the Toronto Stock Exchange
    "Taiwan Semiconductor Manufacturing": "TSM",
    "Talanx": "TLX.DE",
    "Target": "TGT",
    "Tata Motors Limited": "TATAMOTORS.NS",
    "Tencent Holdings Limited": "0700.HK",
    "Tesco": "TSCO.L",
    "Tesla": "TSLA",
    "Thermo Fisher Scientific": "TMO",
    "ThyssenKrupp": "TKA.DE",
    "Tokio Marine Holdings Inc.": "8766.T",
    "Tokyo Electric Power Company Holdings Inc. (TEPCO)": "9501.T",
    "Tongwei Group": "600438.SS",
    "TotalEnergies": "TTE",
    "Toyota Motor": "TM",
    "Toyota Tsusho Corporation": "8015.T",
    "Trafigura Group Pte Ltd.": None,  # Private company
    "Travelers": "TRV",
    "Truist Financial": "TFC",
    "Tyson Foods": "TSN",
    
    # U
    "U.S. Postal Service": None,  # Government agency
    "UBS Group AG": "UBS",
    "US Bancorp": "USB",
    "US Foods Holding": "USFD",
    "Uber Technologies": "UBER",
    "UniCredit Group": "UCG.MI",
    "Unilever": "UL",
    "Uniper": "UN01.DE",
    "United Airlines Holdings": "UAL",
    "United Parcel Service": "UPS",
    "UnitedHealth Group": "UNH",
    
    # V
    "VTB Bank": "VTBR.ME",
    "Vale": "VALE",
    "Valero Energy": "VLO",
    "Veolia Environnement": "VIE.PA",
    "Verizon Communications": "VZ",
    "Vibra Energia S.A.": "VBBR3.SA",
    "Vinci": "DG.PA",
    "Visa": "V",
    "Vodafone Group": "VOD.L",
    "Volkswagen": "VOW3.DE",
    
    # W
    "Walgreens Boots Alliance": "WBA",
    "Walmart": "WMT",
    "Walt Disney": "DIS",
    "Warner Bros. Discovery Inc.": "WBD",
    "Wells Fargo": "WFC",
    "Wilmar International Limited": "F34.SI",
    "Woolworths Group": "WOW.AX",
    "World Kinect": "WKC",
    
    # X
    "X5 Retail Group NV": "FIVE.L",
    "Xiamen CD Inc.": "600153.SS",
    "Xiaomi Corporation": "1810.HK",
    
    # Z
    "ZF Friedrichshafen AG": None,  # Private company
    "Zhejiang Geely Holding Group": "0175.HK",
    "Zijin Mining Group": "601899.SS",
    "Zurich Insurance Group AG": "ZURN.SW",


    "ABB": "ABBN",
    "Beijing Jianlong Heavy Industry Group": None,  # Private company
    "Bosch Group": "BOSCHLTD.NS",
    "Caixa Economica Federal": "CXSE3",
    "Chery Holding Group": None,  # Private company
    "China Electronics Huada Technology Co., Ltd.": "0085",
    "China North Industries Group": "000519",
    "China South Industries Group": "000520",
    "Coop Group": "COOP",
    "CPC": "157785",
    "Deutsche Bahn AG (German Railways)": None,  # Private company
    "DHL Group": "DHL",
    "DZ Bank": "DEDW21TX",
    "Electricite de France": "EDF",
    "ELO": "ELO",
    "Emirates Group": "?",
    "EXOR Group": "EXO",
    "GS Caltex": None,  # Private company
    "Ingka Group": None,  # Private company
    "JBS S.A.": "JBS",
    "La Poste": "PST.MI",
    "Landesbank Baden-Wurttemberg": "FHUU.DU",
    "Louis Dreyfus": "LOUP.PA",
    "Massachusetts Mutual Life Insurance": "MMSNX",
    "Mercadona S.A.": None,  # Private company
    "Metro": "B4B.DE",
    "Nationwide Building Society": "NBS",
    "New York Life Insurance and Annuity Corp": "?",
    "Nippon Life Insurance": "NAM-INDIA.NS",
    "Northwestern Mutual": None,  # Private company
    "Olam Group Limited": "VC2.SI",
    "Pemex": "PEMEX",
    "Pertamina": "PGEO.JK",
    "Petronas": "PGAS.KL",
    "Phoenix Pharmahandel GmbH and Co KG": "PHNX.L",
    "Samsung CT Corporation": "028260",
    "Shaanxi Yanchang Petroleum (Group)": "600248",
    "Shandong Energy Group": "1250",
    "Sinomach": "601399",
    "SNCF Group": "SNCF",
    "State Power Investment": "000958.SZ",
    "Trafigura Group Pte Ltd.": None,  # Private company
    "TSX": "GSPTSE",
    "U.S. Postal Service": None,  # Private company
    "Uniper": "UN0",
    "ZF Friedrichshafen AG": "private company"
}

# Read the Excel file
df = pd.read_excel('../raw_data/Companies.xlsx')
print(f"Found {len(df)} companies in the Excel file")

# Function to get ticker from company name
def get_ticker(company_name):
    # Direct lookup in the mappings
    return ticker_mappings.get(company_name, None)

# Results storage
results = []
failed_companies = []

# Process each company
for index, row in df.iterrows():
    company_name = row['Company Name']
    country = row['country']
    
    print(f"\nProcessing {index + 1}/{len(df)}: {company_name}")
    
    # Get ticker
    ticker = get_ticker(company_name)
    
    if not ticker:
        print(f"  ⚠️ No ticker mapping for {company_name} (likely private/state-owned)")
        failed_companies.append({
            'Company Name': company_name,
            'Country': country,
            'Reason': 'No ticker (private/state-owned/mutual company)'
        })
        continue
    
    print(f"  Using ticker: {ticker}")
    
    try:
        # Fetch company data
        company = yf.Ticker(ticker)
        info = company.info
        
        if not info or 'longName' not in info:
            print(f"  ⚠️ No data found for ticker {ticker}")
            failed_companies.append({
                'Company Name': company_name,
                'Country': country,
                'Ticker Attempted': ticker,
                'Reason': 'No data from yfinance'
            })
            continue
        
        # Extract data
        company_data = {
            'Company Name (Original)': company_name,
            'Country (Excel)': country,
            'Ticker': ticker,
        }
        
        # Add ALL fields from Yahoo Finance info
        for key, value in info.items():
            # Convert lists and dicts to strings for CSV compatibility
            if isinstance(value, (list, dict)):
                company_data[key] = str(value)
            else:
                company_data[key] = value
        
        results.append(company_data)
        
        print(f"  ✅ Data collected for {company_name}")
        
        # Add delay to avoid rate limiting
        time.sleep(0.5)
        
    except Exception as e:
        print(f"  ❌ Error processing {company_name}: {str(e)}")
        failed_companies.append({
            'Company Name': company_name,
            'Country': country,
            'Ticker Attempted': ticker,
            'Reason': str(e)
        })
        continue

# Save ALL data in one comprehensive CSV file
if results:
    # Create DataFrame with all results
    consolidated_df = pd.DataFrame(results)
    
    # Reorder columns to put important ones first
    priority_cols = ['Company Name (Original)', 'Country (Excel)', 'Ticker', 'longName', 
                     'sector', 'industry', 'marketCap', 'totalRevenue', 'fullTimeEmployees',
                     'country', 'city', 'address1', 'phone', 'website', 'currency']
    
    # Get all other columns
    other_cols = [col for col in consolidated_df.columns if col not in priority_cols]
    
    # Reorder columns (only include priority cols that exist)
    ordered_cols = [col for col in priority_cols if col in consolidated_df.columns] + other_cols
    consolidated_df = consolidated_df[ordered_cols]
    
    # Save to CSV with all data
    output_file = f"{output_dir}/all_companies_complete_data.csv"
    consolidated_df.to_csv(output_file, index=False, encoding='utf-8-sig')
    print(f"\n✅ Complete data saved to {output_file}")
    print(f"Successfully processed {len(results)} companies")
    print(f"Total columns in dataset: {len(consolidated_df.columns)}")
    
    # Show info about the data collected
    print("\nData fields collected (first 30):")
    for i, col in enumerate(consolidated_df.columns[:30], 1):
        print(f"  {i}. {col}")
    if len(consolidated_df.columns) > 30:
        print(f"  ... and {len(consolidated_df.columns) - 30} more fields")

# Save failed companies list
if failed_companies:
    failed_df = pd.DataFrame(failed_companies)
    failed_df.to_csv(f"{output_dir}/failed_companies.csv", index=False)
    print(f"\n⚠️ Failed to process {len(failed_companies)} companies")
    print(f"Failed companies list saved to {output_dir}/failed_companies.csv")

# Summary statistics
print("\n" + "="*50)
print("SUMMARY")
print("="*50)
print(f"Total companies in Excel: {len(df)}")
print(f"Successfully processed: {len(results)}")
print(f"Failed to process: {len(failed_companies)}")
if len(df) > 0:
    print(f"Success rate: {len(results)/len(df)*100:.1f}%")

# Show sample of results
if results:
    print("\nSample of processed companies:")
    sample_df = pd.DataFrame(results[:5])
    print(sample_df[['Original Name', 'Ticker', 'Sector', 'Market Cap']].to_string())