DCF Valuation with Python

# library imports
import requests  
import pandas as pd  
import pandas as pd
import numpy as np
import yfinance as yf

# stock symbol
ticker = 'aapl'  

# Mapping each URL to a descriptive sheet name  
url_sheet_map = {  
    f"https://stockanalysis.com/stocks/{ticker}/": "Overview",  
    f"https://stockanalysis.com/stocks/{ticker}/financials/": "Income Statement",  
    f"https://stockanalysis.com/stocks/{ticker}/financials/balance-sheet/": "Balance Sheet",  
    f"https://stockanalysis.com/stocks/{ticker}/financials/cash-flow-statement/": "Cash Flow",  
    f"https://stockanalysis.com/stocks/{ticker}/financials/ratios/": "Ratios"  
}  
  
# Create an Excel writer  
with pd.ExcelWriter(f"{ticker}_financial_statements.xlsx") as writer:  
    # Loop through each URL and its corresponding sheet name  
    for url, sheet_name in url_sheet_map.items():  
        print(f"Processing: {url}")  
        response = requests.get(url)  
        response.raise_for_status()  # Ensure the request was successful  
  
        # Parse all tables from the current URL  
        tables = pd.read_html(response.text)  
        print(f"Found {len(tables)} tables at {url}.")  
  
        # If multiple tables, we write them sequentially in the same sheet  
        startrow = 0  # Initial row position for writing  
        # Use a list to collect dataframes if you prefer concatenation, but here we write them one after another  
        for idx, table in enumerate(tables):  
            # Optionally, add a header row in the Excel sheet to indicate table separation  
            header = pd.DataFrame({f"Table {idx} from {sheet_name}": []})  
            header.to_excel(writer, sheet_name=sheet_name, startrow=startrow)  
            startrow += 1  # Move down one row for the table data  
              
            # Write the table to the current sheet starting at the designated row  
            table.to_excel(writer, sheet_name=sheet_name, startrow=startrow)  
              
            # Update the startrow for the next table (current table rows + 2 extra rows as spacer)  
            startrow += len(table.index) + 2  
  
print("All tables have been saved into 'tables_by_url.xlsx', each URL in its own sheet.")

# Parameters
TICKER = "AAPL"
EXCEL = f"{TICKER}_financial_statements.xlsx"
FY_COL = "FY2024"

def parse_value(val):
    if isinstance(val, str):
        val = val.replace(",", "").strip()
        if val in ['-', '', 'NA', 'N/A']:
            return np.nan
        if "%" in val:
            try: 
                return float(val.replace("%", "").strip()) / 100
            except: 
                return np.nan
        m = {'B': 1e9, 'M': 1e6, 'T': 1e12}
        if val[-1] in m:
            try: 
                return float(val[:-1].strip()) * m[val[-1]]
            except: 
                return np.nan
        try: 
            return float(val) * 1e6 if val[-1].isdigit() else np.nan
        except: 
            return np.nan
    return np.nan if pd.isna(val) else val

def clean_sheet(sheet, file):
    df = pd.read_excel(file, sheet_name=sheet, header=None).iloc[4:].reset_index(drop=True)
    if pd.api.types.is_numeric_dtype(df.iloc[:,0]) and (df.iloc[:,0].fillna(-1) == pd.Series(range(len(df)))).all():
        df = df.iloc[:,1:]
    n = df.shape[1]
    if n == 7:
        df.columns = ["Item", FY_COL, "FY2023", "FY2022", "FY2021", "FY2020", "Notes"]
    elif n == 8:
        df.columns = ["Item", FY_COL, "FY2023", "FY2022", "FY2021", "FY2020", "Extra", "Notes"]
    else:
        df.columns = [f"Col{i}" for i in range(n)]
    for c in df.columns:
        if c not in ["Item", "Notes"]:
            df[c] = df[c].apply(parse_value)
    return df

# Load Data
fin = clean_sheet("Income Statement", EXCEL)
bal = clean_sheet("Balance Sheet", EXCEL).set_index("Item")
cf  = clean_sheet("Cash Flow", EXCEL)

def get_val(df, key, col=FY_COL, default=None):
    row = df[df["Item"].str.contains(key, case=False, na=False)]
    return row[col].values[0] if not row.empty else default

# Extract metrics
EBIT    = get_val(fin, "EBIT|Operating Income")
tax     = 0.21  # Default effective tax rate
depr    = get_val(cf, "Depreciation", default=0)
capex   = abs(get_val(cf, "Capital Expenditure", default=0))

# Calculate working capital change if available
if "Working Capital" in bal.index:
    wc = bal.loc["Working Capital"]
    delta_wc = wc.iloc[0] - (wc.iloc[1] if len(wc) > 1 else 0)
else:
    delta_wc = 0

# Calculate net debt
if "Net Cash (Debt)" in bal.index:
    net_debt = bal.loc["Net Cash (Debt)", FY_COL]
else:
    net_debt = (bal.loc["Total Debt", FY_COL] - bal.loc["Cash & Equivalents", FY_COL]
                if "Total Debt" in bal.index and "Cash & Equivalents" in bal.index else 0)

FCFF0   = get_val(cf, "Free Cash Flow", EBIT * (1 - tax) + depr - capex - delta_wc)

# Forecasting FCFF
DEFAULT_GROWTH = 0.15  # 15% growth assumption
FORECAST_YEARS = 5

growth = DEFAULT_GROWTH
forecast = [FCFF0 * (1 + growth) ** t for t in range(1, FORECAST_YEARS + 1)] if FCFF0 else [None] * FORECAST_YEARS

# WACC Calculation
# Retrieve beta
info = yf.Ticker(TICKER).info
beta   = info.get("beta", 1.0)
shares = info.get("sharesOutstanding", None)

# Risk-free rate from TLT
rf = yf.Ticker("TLT").info.get("yield", 0.022)  
spy_hist = yf.Ticker("SPY").history(period="20y")["Close"].resample("Y").last()
if len(spy_hist) >= 2:
    spy_cagr = (spy_hist.iloc[-1] / spy_hist.iloc[0])**(1 / (len(spy_hist)-1)) - 1
else:
    spy_cagr = 0.08
mrp = spy_cagr - rf
# Cost of Equity
ce = rf + beta * mrp  
# Cost of Debt
de = DEFAULT_COST_DEBT  # Assumed cost of debt

# Determine market value weights
market_equity = shares * price if shares and price else None
market_debt = bal.loc["Total Debt", FY_COL] if "Total Debt" in bal.index else 0
if market_equity and (market_equity + market_debt) > 0:
    we = market_equity / (market_equity + market_debt)
    wd = market_debt / (market_equity + market_debt)
else:
    we, wd = 1, 0

WACC = we * ce + wd * de * (1 - tax)

# Terminal Value and DCF Valuation
TERM_GROWTH = 0.04  # Terminal growth rate assumption

# Discount forecasted FCFFs
disc_FCFF = [f / ((1 + WACC) ** t) for t, f in enumerate(forecast, start=1)]

# Calculate Terminal Value using the last year's FCFF
term_val = forecast[-1] * (1 + TERM_GROWTH) / (WACC - TERM_GROWTH)
disc_term = term_val / ((1 + WACC) ** FORECAST_YEARS)

# Enterprise Value (EV)
EV = sum(disc_FCFF) + disc_term

# Equity Value and Intrinsic Share Price
eq_val = EV - net_debt
intrinsic = eq_val / shares if shares else None

info = yf.Ticker(TICKER).info
price  = info.get("currentPrice", None)
print(f"Intrinsic Price: {intrinsic:,.2f}, Current Price: {price:,.2f}")