give me an data set with 1000 rows simulating actual data with these variables Liquidity…
- User
give me an data set with 1000 rows simulating actual data with these variables Liquidity Ratios: Need Current Assets, Current Liabilities, Cash, Inventory, Accounts Receivable. Profitability Ratios: Net Income, Gross Profit, Operating Income, Revenue. Efficiency Ratios: Revenue, Assets, Inventory, Receivables, Payables, Employees. Leverage Ratios: Total Debt, Equity, Interest Expense. Valuation Multiples: Market Cap, Enterprise Value, EBITDA, EPS, Book Value Per Share. Growth Rates: YoY changes in Revenue, Net Income, EPS, Free Cash Flow.
Assistant
I'll create a realistic financial dataset with 1000 rows representing quarterly data for 50 companies over 5 years (2019-2023). This dataset will include all the variables needed for the ratio analyses you mentioned.
import pandas as pd import numpy as np from datetime import datetime, timedelta import random # Set random seed for reproducibility np.random.seed(42) random.seed(42) # Define companies and industries companies = [ ("Apple Inc.", "AAPL", "Technology", "Large"), ("Microsoft Corp.", "MSFT", "Technology", "Large"), ("Amazon.com Inc.", "AMZN", "E-Commerce", "Large"), ("JPMorgan Chase", "JPM", "Banking", "Large"), ("Johnson & Johnson", "JNJ", "Healthcare", "Large"), ("Walmart Inc.", "WMT", "Retail", "Large"), ("Procter & Gamble", "PG", "Consumer Goods", "Large"), ("Tesla Inc.", "TSLA", "Automotive", "Large"), ("Coca-Cola Co.", "KO", "Beverages", "Large"), ("Netflix Inc.", "NFLX", "Entertainment", "Large"), ("Pfizer Inc.", "PFE", "Pharmaceuticals", "Large"), ("ExxonMobil Corp.", "XOM", "Energy", "Large"), ("Bank of America", "BAC", "Banking", "Large"), ("Home Depot Inc.", "HD", "Retail", "Large"), ("Visa Inc.", "V", "Financial Services", "Large"), ("UnitedHealth Group", "UNH", "Healthcare", "Large"), ("Mastercard Inc.", "MA", "Financial Services", "Large"), ("NVIDIA Corp.", "NVDA", "Technology", "Large"), ("Adobe Inc.", "ADBE", "Technology", "Mid"), ("Salesforce Inc.", "CRM", "Technology", "Mid"), ("PayPal Holdings", "PYPL", "Financial Services", "Mid"), ("Starbucks Corp.", "SBUX", "Restaurants", "Mid"), ("Target Corp.", "TGT", "Retail", "Mid"), ("CVS Health Corp.", "CVS", "Healthcare", "Mid"), ("General Motors", "GM", "Automotive", "Mid"), ("Ford Motor Co.", "F", "Automotive", "Mid"), ("Delta Air Lines", "DAL", "Airlines", "Mid"), ("American Airlines", "AAL", "Airlines", "Mid"), ("Marriott Intl.", "MAR", "Hotels", "Mid"), ("Hilton Worldwide", "HLT", "Hotels", "Mid"), ("Chipotle Mexican", "CMG", "Restaurants", "Mid"), ("McDonald's Corp.", "MCD", "Restaurants", "Mid"), ("Yum! Brands Inc.", "YUM", "Restaurants", "Mid"), ("Best Buy Co.", "BBY", "Retail", "Mid"), ("Gap Inc.", "GPS", "Retail", "Small"), ("Macy's Inc.", "M", "Retail", "Small"), ("Nordstrom Inc.", "JWN", "Retail", "Small"), ("Peloton Interactive", "PTON", "Fitness", "Small"), ("Zoom Video", "ZM", "Technology", "Small"), ("DocuSign Inc.", "DOCU", "Technology", "Small"), ("Spotify Technology", "SPOT", "Entertainment", "Small"), ("Square Inc.", "SQ", "Financial Services", "Small"), ("Robinhood Markets", "HOOD", "Financial Services", "Small"), ("DoorDash Inc.", "DASH", "Technology", "Small"), ("Airbnb Inc.", "ABNB", "Travel", "Small"), ("Uber Technologies", "UBER", "Technology", "Small"), ("Lyft Inc.", "LYFT", "Technology", "Small"), ("Pinterest Inc.", "PINS", "Technology", "Small"), ("Snap Inc.", "SNAP", "Technology", "Small"), ("Twitter Inc.", "TWTR", "Technology", "Small") ] # Industry characteristics (for realistic data generation) industry_profiles = { "Technology": {"margin": 0.25, "asset_turnover": 0.8, "debt_ratio": 0.3, "growth": 0.15}, "Banking": {"margin": 0.30, "asset_turnover": 0.05, "debt_ratio": 0.85, "growth": 0.08}, "Healthcare": {"margin": 0.20, "asset_turnover": 0.6, "debt_ratio": 0.4, "growth": 0.10}, "Retail": {"margin": 0.05, "asset_turnover": 2.0, "debt_ratio": 0.5, "growth": 0.05}, "E-Commerce": {"margin": 0.03, "asset_turnover": 1.5, "debt_ratio": 0.4, "growth": 0.20}, "Consumer Goods": {"margin": 0.15, "asset_turnover": 0.8, "debt_ratio": 0.4, "growth": 0.04}, "Automotive": {"margin": 0.08, "asset_turnover": 0.9, "debt_ratio": 0.6, "growth": 0.03}, "Energy": {"margin": 0.10, "asset_turnover": 0.5, "debt_ratio": 0.5, "growth": 0.02}, "Financial Services": {"margin": 0.35, "asset_turnover": 0.1, "debt_ratio": 0.3, "growth": 0.12}, "Pharmaceuticals": {"margin": 0.22, "asset_turnover": 0.5, "debt_ratio": 0.4, "growth": 0.08}, "Beverages": {"margin": 0.20, "asset_turnover": 0.6, "debt_ratio": 0.5, "growth": 0.06}, "Entertainment": {"margin": 0.15, "asset_turnover": 0.7, "debt_ratio": 0.5, "growth": 0.15}, "Restaurants": {"margin": 0.08, "asset_turnover": 1.5, "debt_ratio": 0.6, "growth": 0.07}, "Airlines": {"margin": 0.05, "asset_turnover": 0.8, "debt_ratio": 0.7, "growth": 0.04}, "Hotels": {"margin": 0.12, "asset_turnover": 0.4, "debt_ratio": 0.6, "growth": 0.06}, "Fitness": {"margin": 0.10, "asset_turnover": 0.6, "debt_ratio": 0.5, "growth": 0.20}, "Travel": {"margin": 0.15, "asset_turnover": 0.8, "debt_ratio": 0.4, "growth": 0.25} } # Generate data data = [] for company_name, ticker, industry, size in companies: # Get industry profile profile = industry_profiles.get(industry, industry_profiles["Technology"]) # Base values (in millions) - vary by company size if size == "Large": base_revenue = np.random.uniform(20000, 100000) base_assets = base_revenue / profile["asset_turnover"] * np.random.uniform(0.8, 1.2) base_employees = int(np.random.uniform(50000, 200000)) elif size == "Mid": base_revenue = np.random.uniform(5000, 20000) base_assets = base_revenue / profile["asset_turnover"] * np.random.uniform(0.8, 1.2) base_employees = int(np.random.uniform(10000, 50000)) else: # Small base_revenue = np.random.uniform(500, 5000) base_assets = base_revenue / profile["asset_turnover"] * np.random.uniform(0.8, 1.2) base_employees = int(np.random.uniform(1000, 10000)) # Generate quarterly data for 5 years (2019-2023) for year in range(2019, 2024): for quarter in range(1, 5): # Date date = f"{year}-Q{quarter}" # Apply growth and seasonality years_from_start = year - 2019 + (quarter - 1) / 4 growth_factor = (1 + profile["growth"]) ** years_from_start seasonal_factor = 1 + 0.1 * np.sin(2 * np.pi * (quarter - 1) / 4) # Seasonal variation # Revenue (with some randomness) revenue = base_revenue * growth_factor * seasonal_factor * np.random.uniform(0.9, 1.1) # Profitability metrics gross_margin = profile["margin"] * np.random.uniform(0.8, 1.2) gross_profit = revenue * gross_margin operating_margin = gross_margin * np.random.uniform(0.6, 0.9) operating_income = revenue * operating_margin net_margin = operating_margin * np.random.uniform(0.6, 0.85) net_income = revenue * net_margin # Balance Sheet Items total_assets = base_assets * growth_factor * np.random.uniform(0.95, 1.05) # Current assets components current_ratio = np.random.uniform(1.0, 2.5) current_assets_ratio = np.random.uniform(0.3, 0.5) current_assets = total_assets * current_assets_ratio cash_ratio = np.random.uniform(0.2, 0.4) cash = current_assets * cash_ratio receivables_days = np.random.uniform(30, 90) accounts_receivable = (revenue / 4) * (receivables_days / 90) inventory_turnover = np.random.uniform(4, 12) if industry in ["Banking", "Financial Services", "Technology"]: inventory = 0 # Service companies have no inventory else: inventory = (revenue * (1 - gross_margin) / 4) / (inventory_turnover / 4) # Current liabilities current_liabilities = current_assets / current_ratio payables_days = np.random.uniform(30, 60) accounts_payable = (revenue * (1 - gross_margin) / 4) * (payables_days / 90) # Debt and Equity debt_to_assets = profile["debt_ratio"] * np.random.uniform(0.7, 1.3) total_debt = total_assets * debt_to_assets equity = total_assets - total_debt - current_liabilities # Interest expense (assuming 4-6% interest rate) interest_rate = np.random.uniform(0.04, 0.06) interest_expense = total_debt * interest_rate / 4 # Quarterly # Market data pe_ratio = np.random.uniform(10, 35) shares_outstanding = np.random.uniform(100, 5000) # In millions eps = net_income / shares_outstanding stock_price = eps * pe_ratio * 4 # Annualized EPS market_cap = stock_price * shares_outstanding # Enterprise Value enterprise_value = market_cap + total_debt - cash # EBITDA (approximation) depreciation_rate = 0.05 # 5% of assets per year depreciation = total_assets * depreciation_rate / 4 ebitda = operating_income + depreciation # Book value per share book_value_per_share = equity / shares_outstanding # Employees (with some growth) employees = int(base_employees * growth_factor * np.random.uniform(0.95, 1.05)) # Free Cash Flow (simplified) capex = total_assets * 0.03 # 3% of assets working_capital_change = (accounts_receivable + inventory - accounts_payable) * 0.1 free_cash_flow = net_income + depreciation - capex - working_capital_change # Create row row = { "Company_Name": company_name, "Ticker": ticker, "Industry": industry, "Date": date, "Year": year, "Quarter": quarter, # Income Statement "Revenue": round(revenue, 2), "Gross_Profit": round(gross_profit, 2), "Operating_Income": round(operating_income, 2), "Net_Income": round(net_income, 2), "Interest_Expense": round(interest_expense, 2), # Balance Sheet "Total_Assets": round(total_assets, 2), "Current_Assets": round(current_assets, 2), "Cash": round(cash, 2), "Accounts_Receivable": round(accounts_receivable, 2), "Inventory": round(inventory, 2), "Current_Liabilities": round(current_liabilities, 2), "Accounts_Payable": round(accounts_payable, 2), "Total_Debt": round(total_debt, 2), "Equity": round(equity, 2), # Market Data "Market_Cap": round(market_cap, 2), "Enterprise_Value": round(enterprise_value, 2), "Stock_Price": round(stock_price, 2), "Shares_Outstanding": round(shares_outstanding, 2), "EPS": round(eps, 4), "Book_Value_Per_Share": round(book_value_per_share, 2), # Other Metrics "EBITDA": round(ebitda, 2), "Free_Cash_Flow": round(free_cash_flow, 2), "Employees": employees } data.append(row) # Create DataFrame df = pd.DataFrame(data) # Calculate YoY growth rates df = df.sort_values(['Ticker', 'Year', 'Quarter']) for ticker in df['Ticker'].unique(): mask = df['Ticker'] == ticker ticker_data = df[mask].copy() # Calculate YoY changes df.loc[mask, 'Revenue_YoY_Growth'] = ticker_data['Revenue'].pct_change(4) df.loc[mask, 'Net_Income_YoY_Growth'] = ticker_data['Net_Income'].pct_change(4) df.loc[mask, 'EPS_YoY_Growth'] = ticker_data['EPS'].pct_change(4) df.loc[mask, 'FCF_YoY_Growth'] = ticker_data['Free_Cash_Flow'].pct_change(4) # Fill NaN values in growth rates with 0 growth_columns = ['Revenue_YoY_Growth', 'Net_Income_YoY_Growth', 'EPS_YoY_Growth', 'FCF_YoY_Growth'] df[growth_columns] = df[growth_columns].fillna(0) # Round growth rates for col in growth_columns: df[col] = df[col].round(4) # Display first few rows print(f"Dataset shape: {df.shape}") print("\nFirst 5 rows:") print(df.head()) # Save to CSV df.to_csv('financial_analysis_dataset.csv', index=False) print("\nDataset saved to 'financial_analysis_dataset.csv'") # Display summary statistics print("\nSummary by Industry:")
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.