{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import sys\n", "import pandas as pd\n", "import yfinance as yf\n", "import warnings\n", "\n", "from prophet import Prophet\n", "from prophet.diagnostics import cross_validation, performance_metrics\n", "import matplotlib.pyplot as plt\n", "import numpy as np\n", "from sklearn.linear_model import LinearRegression\n", "\n", "from sklearn.model_selection import train_test_split\n", "from sklearn.metrics import mean_absolute_error\n", "import lightgbm as lgb\n", "\n", "from google.oauth2.service_account import Credentials\n", "from googleapiclient.discovery import build\n", "\n", "from yahooquery import Ticker\n", "from polygon import RESTClient\n", "\n", "import os\n", "import requests\n", "from datetime import datetime\n", "from dateutil.relativedelta import relativedelta\n", "\n", "warnings.filterwarnings(\"ignore\", category=FutureWarning)\n", "\n", "sys.path.append(os.path.abspath(os.path.join(os.getcwd(), \"..\")))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from dotenv import load_dotenv\n", "import os\n", "\n", "load_dotenv()\n", "\n", "POLYGON_API_KEY = os.getenv(\"POLYGON_API_KEY\")\n", "\n", "if POLYGON_API_KEY:\n", " print(\"API Key successfully loaded!\")\n", "else:\n", " print(\"Error: API Key not found!\")\n", "\n", "ALPHA_VANTAGE_API_KEY = os.getenv(\"ALPHA_VANTAGE_API_KEY\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import gspread\n", "\n", "# gc = gspread.service_account(filename='stocksflags-ec5c40f2f2ee.json')\n", "gc = gspread.service_account(filename='rational-diode-456114-m0-79243525427e.json')\n", "\n", "\n", "ginzu_spreadsheet_link = 'https://docs.google.com/spreadsheets/d/1xclQf2xrgw0swp2CRwE25OBaEhQdDTOkm8VVkTcpVks/edit?usp=sharing'\n", "pricer = gc.open_by_url(ginzu_spreadsheet_link)\n", "input_worksheet = pricer.get_worksheet(0)\n", "valuation_worksheet = pricer.get_worksheet(1)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "SCOPES = ['https://www.googleapis.com/auth/spreadsheets']\n", "SERVICE_ACCOUNT_FILE = 'rational-diode-456114-m0-79243525427e.json'\n", "\n", "spreadsheet_id = '1xclQf2xrgw0swp2CRwE25OBaEhQdDTOkm8VVkTcpVks'\n", "sheet_name = 'Input sheet'\n", "\n", "creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)\n", "service = build('sheets', 'v4', credentials=creds)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def get_related_tickers(ticker):\n", " client = RESTClient(POLYGON_API_KEY)\n", " try:\n", " related = client.get_related_companies(ticker.upper())\n", " return [r.ticker for r in related]\n", " except Exception as e:\n", " print(f\"Error fetching related companies: {e}\")\n", " return []" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def get_dropdown_options(spreadsheet_id: str, sheet_name: str, cell: str) -> list:\n", " \"\"\"\n", " Given a spreadsheet ID, sheet name, and cell (e.g., 'B7'),\n", " returns the dropdown options (either hardcoded or from a range).\n", " \"\"\"\n", " range_name = f\"'{sheet_name}'!{cell}\"\n", " \n", " try:\n", " response = service.spreadsheets().get(\n", " spreadsheetId=spreadsheet_id,\n", " ranges=[range_name],\n", " includeGridData=True,\n", " fields='sheets.data.rowData.values.dataValidation'\n", " ).execute()\n", "\n", " validation = response['sheets'][0]['data'][0]['rowData'][0]['values'][0]['dataValidation']\n", " condition = validation['condition']\n", " dropdown_values = [v['userEnteredValue'] for v in condition.get('values', [])]\n", "\n", " dropdown_ref = dropdown_values[0]\n", " if dropdown_ref.startswith(\"=\"):\n", " raw_ref = dropdown_ref.lstrip(\"=\").replace(\"'\", \"\")\n", " ref_sheet, ref_range = raw_ref.split(\"!\")\n", "\n", " country_resp = service.spreadsheets().values().get(\n", " spreadsheetId=spreadsheet_id,\n", " range=f\"{ref_sheet}!{ref_range}\"\n", " ).execute()\n", "\n", " return [row[0].strip() for row in country_resp.get(\"values\", []) if row and row[0].strip()]\n", " else:\n", " return dropdown_values\n", "\n", " except (KeyError, IndexError):\n", " print(f\"No dropdown (data validation) found at {sheet_name}!{cell}.\")\n", " return []\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def get_alpha_vantage_data(function, symbol):\n", " url = f\"https://www.alphavantage.co/query?function={function}&symbol={symbol}&apikey={ALPHA_VANTAGE_API_KEY}\"\n", " response = requests.get(url)\n", " return response.json()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def safe_float(value):\n", " try:\n", " # Attempt to convert to float; if it fails, return 0\n", " return float(value) if value not in [None, 'None', ''] else 0\n", " except ValueError:\n", " return 0.0" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def get_latest_10k_financials(symbol):\n", " overview_data = get_alpha_vantage_data(\"OVERVIEW\", symbol)\n", " income_data = get_alpha_vantage_data(\"INCOME_STATEMENT\", symbol)\n", " balance_data = get_alpha_vantage_data(\"BALANCE_SHEET\", symbol)\n", " quote_data = get_alpha_vantage_data(\"TIME_SERIES_DAILY\", symbol)\n", "\n", " try:\n", " company_name = overview_data[\"Name\"]\n", " inc_reports = income_data[\"annualReports\"]\n", " bal_reports = balance_data[\"annualReports\"]\n", " price_series = quote_data[\"Time Series (Daily)\"]\n", " except KeyError:\n", " raise ValueError(\"Missing data from Alpha Vantage. Check symbol or API limit.\")\n", "\n", " inc_reports.sort(key=lambda x: x[\"fiscalDateEnding\"], reverse=True)\n", " bal_reports.sort(key=lambda x: x[\"fiscalDateEnding\"], reverse=True)\n", "\n", " inc_curr, inc_prev = inc_reports[0], inc_reports[1]\n", " bal_curr, bal_prev = bal_reports[0], bal_reports[1]\n", "\n", " b11 = safe_float(inc_curr.get(\"totalRevenue\", 0))\n", " c11 = safe_float(inc_prev.get(\"totalRevenue\", 0))\n", "\n", " b12 = safe_float(inc_curr.get(\"ebit\", 0))\n", " c12 = safe_float(inc_prev.get(\"ebit\", 0))\n", "\n", " b13 = safe_float(inc_curr.get(\"interestExpense\", 0))\n", " c13 = safe_float(inc_prev.get(\"interestExpense\", 0))\n", "\n", " b14 = safe_float(bal_curr.get(\"totalShareholderEquity\", 0))\n", " c14 = safe_float(bal_prev.get(\"totalShareholderEquity\", 0))\n", "\n", " current_debt = safe_float(bal_curr.get(\"currentDebt\", 0))\n", " long_term_debt = safe_float(bal_curr.get(\"currentLongTermDebt\", 0)) \n", " b15 = current_debt + long_term_debt \n", "\n", " prev_current_debt = safe_float(bal_prev.get(\"currentDebt\", 0))\n", " prev_long_term_debt = safe_float(bal_prev.get(\"currentLongTermDebt\", 0)) \n", " c15 = prev_current_debt + prev_long_term_debt\n", "\n", " b18 = safe_float(bal_curr.get(\"cashAndCashEquivalentsAtCarryingValue\", 0))\n", " c18 = safe_float(bal_prev.get(\"cashAndCashEquivalentsAtCarryingValue\", 0))\n", "\n", " b19 = safe_float(bal_curr.get(\"otherCurrentAssets\", 0)) \n", " c19 = safe_float(bal_prev.get(\"otherCurrentAssets\", 0)) \n", "\n", " b20 = safe_float(bal_curr.get(\"totalShareholderEquity\", 0)) - safe_float(bal_curr.get(\"commonStockEquity\", 0))\n", " c20 = safe_float(bal_prev.get(\"totalShareholderEquity\", 0)) - safe_float(bal_prev.get(\"commonStockEquity\", 0))\n", "\n", " b21 = int(bal_curr.get(\"commonStockSharesOutstanding\", 0))\n", "\n", " # Get most recent stock price\n", " latest_day = sorted(price_series.keys())[-1]\n", " b22 = safe_float(price_series[latest_day][\"4. close\"])\n", "\n", " d11 = relativedelta(datetime.strptime(inc_curr[\"fiscalDateEnding\"], \"%Y-%m-%d\"),\n", " datetime.strptime(inc_prev[\"fiscalDateEnding\"], \"%Y-%m-%d\")).years\n", "\n", " b16 = \"Yes\" if \"researchAndDevelopment\" in inc_curr or \"researchAndDevelopment\" in inc_prev else \"No\"\n", "\n", " b17 = \"Yes\" if \"operatingLease\" in inc_curr or \"operatingLease\" in inc_prev else \"No\"\n", "\n", " # Calculate Effective Tax Rate (B23)\n", " income_tax_expense = safe_float(inc_curr.get(\"incomeTaxExpense\", 0))\n", " pre_tax_income = safe_float(inc_curr.get(\"ebit\", 0)) # EBIT as proxy for pre-tax income\n", " b23 = income_tax_expense / pre_tax_income if pre_tax_income != 0 else 0\n", "\n", " b24 = 0.21 \n", "\n", " # Define rf w/ 10 year treasury yield\n", " b33 = 0.04378\n", "\n", " return {\n", " \"B11\": b11, \"C11\": c11, \"D11\": d11,\n", " \"B12\": b12, \"C12\": c12, \"B13\": b13, \"C13\": c13,\n", " \"B14\": b14, \"C14\": c14, \"B15\": b15, \"C15\": c15,\n", " \"B16\": b16, \"B17\": b17,\n", " \"B18\": b18, \"C18\": c18, \"B19\": b19, \"C19\": c19,\n", " \"B20\": b20, \"C20\": c20, \"B21\": b21, \"B22\": b22,\n", " \"B23\": b23, \"B24\": b24, \"B4\": company_name,\n", " \"B33\": b33,\n", " }" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "company_ticker = input(\"Enter the company ticker: \")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "financials = get_latest_10k_financials(company_ticker)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "financials" ] }, { "cell_type": "markdown", "metadata": { "id": "-apU1EN-CHHY" }, "source": [ "## Date of Valuation and Company Name" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def retrieve_date():\n", " valuation_date = datetime.today()\n", " formatted_date = valuation_date.strftime('%b-%d')\n", " return formatted_date" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "xsSb7iyHCGoK" }, "outputs": [], "source": [ "date_of_valuation = retrieve_date()\n", "company_name = financials[\"B4\"]\n", "input_worksheet.update('B3', [[date_of_valuation]])\n", "input_worksheet.update('B4', [[company_name]])" ] }, { "cell_type": "markdown", "metadata": { "id": "yUpdCCbWCmpn" }, "source": [ "## Country of Incorporation, Industry (Global) and Industry (US)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def prompt_user_choice(options_list, prompt_message=\"Please choose an option:\"):\n", " print(\"\\nYou can type part of the name to search for matches.\")\n", " print(\"Example: typing 'tech' will show 'Technology', 'Biotech', etc.\\n\")\n", "\n", " while True:\n", " search_input = input(f\"{prompt_message} (type to search): \").strip().lower()\n", " \n", " matching_options = [opt for opt in options_list if search_input in opt.lower()]\n", " \n", " if not matching_options:\n", " print(\"No matches found. Please try again.\")\n", " continue\n", "\n", " print(\"\\nMatches found:\")\n", " for idx, opt in enumerate(matching_options[:10], start=1):\n", " print(f\"{idx}. {opt}\")\n", " \n", " if len(matching_options) > 10:\n", " print(f\"...and {len(matching_options) - 10} more matches.\\n\")\n", " else:\n", " print(\"\") \n", "\n", " selected_option = input(\"Type your exact choice from the matches above: \").strip()\n", " selected_exact = [opt for opt in matching_options if opt.lower() == selected_option.lower()]\n", " \n", " if selected_exact:\n", " return selected_exact[0]\n", " else:\n", " print(\"Invalid selection from matches. Please try again.\\n\")\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "country_names = get_dropdown_options(spreadsheet_id, sheet_name, 'B7')\n", "us_industry = get_dropdown_options(spreadsheet_id, sheet_name, 'B8')\n", "global_industry = get_dropdown_options(spreadsheet_id, sheet_name, 'B9')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "country_names" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "us_industry" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "global_industry" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "country = prompt_user_choice(country_names, \"Choose a country from the list above:\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "us_industry_choice = prompt_user_choice(us_industry, \"Choose a US industry from the list above:\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "global_industry_choice = prompt_user_choice(global_industry, \"Choose a Global industry from the list above:\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "t6sKVDOlCl6F" }, "outputs": [], "source": [ "input_worksheet.update('B7', [[country]])\n", "input_worksheet.update('B8', [[us_industry_choice]])\n", "input_worksheet.update('B9', [[global_industry_choice]])" ] }, { "cell_type": "markdown", "metadata": { "id": "4MLGXn0XC8jL" }, "source": [ "## Years Since Last 10K" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "GXc2AAEjB_KI" }, "outputs": [], "source": [ "years_since_last_10k = financials[\"D11\"]\n", "input_worksheet.update('D11',[[years_since_last_10k]])" ] }, { "cell_type": "markdown", "metadata": { "id": "HYGcUzcaDQ9t" }, "source": [ "## Revenues (Most Recent 12 Months, Last 10K)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "Pjcp2rY8DdHi" }, "outputs": [], "source": [ "current_revenues = financials[\"B11\"]/10000000\n", "last_10k_revenues = financials[\"C11\"]/10000000\n", "input_worksheet.update('B11', [[current_revenues]])\n", "input_worksheet.update('C11', [[last_10k_revenues]])" ] }, { "cell_type": "markdown", "metadata": { "id": "IePKIJxyDdlf" }, "source": [ "## Operating Income / EBIT (Most Recent 12 Months, Last 10K)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "DCG9xuFaDd-n" }, "outputs": [], "source": [ "current_operating_income = financials[\"B12\"]/10000000\n", "last_10k_operating_income = financials[\"C12\"]/10000000\n", "input_worksheet.update('B12', [[current_operating_income]])\n", "input_worksheet.update('C12', [[last_10k_operating_income]])" ] }, { "cell_type": "markdown", "metadata": { "id": "gfGr3G6OEuBq" }, "source": [ "## Interest Expense (Most Recent 12 Months, Last 10K)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "5CSIvjkdEuiB" }, "outputs": [], "source": [ "current_interest_expense = financials[\"B13\"]/10000000\n", "last_10k_interest_expense = financials[\"C13\"]/10000000\n", "input_worksheet.update('B13', [[current_interest_expense]])\n", "input_worksheet.update('C13', [[last_10k_interest_expense]])" ] }, { "cell_type": "markdown", "metadata": { "id": "6bIVV-nSP1G1" }, "source": [ "## Book Value of Equity (Most Recent 12 Months, Last 10K)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "Nz7nuX3mP1lc" }, "outputs": [], "source": [ "current_book_value_of_equity = financials[\"B14\"]/10000000\n", "last_10k_book_value_of_equity = financials[\"C14\"]/10000000\n", "input_worksheet.update('B14', [[current_book_value_of_equity]])\n", "input_worksheet.update('C14', [[last_10k_book_value_of_equity]])" ] }, { "cell_type": "markdown", "metadata": { "id": "XlWrc4rNQHOJ" }, "source": [ "## Book Value of Debt (Most Recent 12 Months, Last 10K)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "qWJu8aZGQG8L" }, "outputs": [], "source": [ "current_book_value_of_debt = financials[\"B15\"]/10000000\n", "last_10k_book_value_of_debt = financials[\"C15\"]/10000000\n", "input_worksheet.update('B15', [[current_book_value_of_debt]])\n", "input_worksheet.update('C15', [[last_10k_book_value_of_debt]])" ] }, { "cell_type": "markdown", "metadata": { "id": "zlsF27_hQSu2" }, "source": [ "## R&D Expenses to Capitalize + Operating Lease Commitments" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "FlPc7LguQRPu" }, "outputs": [], "source": [ "r_and_d = financials[\"B16\"]\n", "operating_lease_commitments = financials[\"B17\"]\n", "input_worksheet.update('B16', [[r_and_d]])\n", "input_worksheet.update('B17', [[operating_lease_commitments]])" ] }, { "cell_type": "markdown", "metadata": { "id": "th01qislQm4n" }, "source": [ "## Cash and Marketable Securities (Most Recent 12 Months, Last 10K)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "ShX1inB6Ql8t" }, "outputs": [], "source": [ "current_cash_and_marketable_securities = financials[\"B18\"]/10000000\n", "last_10k_cash_and_marketable_securities = financials[\"C18\"]/10000000\n", "input_worksheet.update('B18', [[current_cash_and_marketable_securities]])\n", "input_worksheet.update('C18', [[last_10k_cash_and_marketable_securities]])" ] }, { "cell_type": "markdown", "metadata": { "id": "Q97KRTmNQwx7" }, "source": [ "## Investments (Most Recent 12 Months, Last 10K)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "Pj-EsGKoQxWg" }, "outputs": [], "source": [ "if financials.get(\"B19\") is not None:\n", " cross_holdings_and_other_non_operating_assets = financials[\"B19\"] / 1e7\n", " input_worksheet.update('B19', [[cross_holdings_and_other_non_operating_assets]])\n", "else:\n", " print(\"B19 (Cross holdings and other non-operating assets) is missing.\")\n", "\n", "if financials.get(\"C19\") is not None:\n", " last_10k_cross_holdings_and_other_non_operating_assets = financials[\"C19\"] / 1e7\n", " input_worksheet.update('C19', [[last_10k_cross_holdings_and_other_non_operating_assets]])\n", "else:\n", " print(\"C19 (Last 10K cross holdings and other non-operating assets) is missing.\")\n" ] }, { "cell_type": "markdown", "metadata": { "id": "FJlSX4yvQ_Ix" }, "source": [ "## Minority Interests (Most Recent 12 Months, Last 10K)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "yc13z0lDQ-Z8" }, "outputs": [], "source": [ "current_minority_interests = financials[\"B20\"]/10000000\n", "last_10k_minority_interests = financials[\"C20\"]/10000000\n", "input_worksheet.update('B20', [[current_minority_interests]])\n", "input_worksheet.update('C20', [[last_10k_minority_interests]])" ] }, { "cell_type": "markdown", "metadata": { "id": "xxaGAGFiRKmz" }, "source": [ "## Number of Shares Outstanding, Current Stock Price, Effect and Marginal Tax Rate" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "X9G6rsqyRHW1" }, "outputs": [], "source": [ "number_of_shares_outstanding = financials[\"B21\"]/10000000\n", "current_stock_price = financials[\"B22\"]\n", "effective_tax_rate = financials[\"B23\"]\n", "marginal_tax_rate = financials[\"B24\"]\n", "input_worksheet.update('B21', [[number_of_shares_outstanding]])\n", "input_worksheet.update('B22', [[current_stock_price]])\n", "input_worksheet.update('B23', [[effective_tax_rate]])\n", "input_worksheet.update('B24', [[marginal_tax_rate]])" ] }, { "cell_type": "markdown", "metadata": { "id": "6tclk_bORW3z" }, "source": [ "## Value Drivers (Rev Growth)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def get_annual_revenue(symbol, api_key):\n", " url = (\n", " f\"https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol={symbol}\"\n", " f\"&apikey={api_key}\"\n", " )\n", " response = requests.get(url)\n", " if response.status_code != 200:\n", " raise ValueError(f\"Request failed with status code {response.status_code}\")\n", " \n", " data = response.json()\n", " if \"annualReports\" not in data:\n", " raise ValueError(f\"No annualReports found for symbol {symbol}. Response: {data}\")\n", " \n", " reports = data[\"annualReports\"]\n", " records = []\n", " for report in reports:\n", " try:\n", " date = pd.to_datetime(report[\"fiscalDateEnding\"])\n", " revenue = int(report[\"totalRevenue\"])\n", " records.append({\"ds\": date, \"y\": revenue})\n", " except Exception as e:\n", " print(f\"Skipping report due to error: {e}\")\n", " \n", " df = pd.DataFrame(records).sort_values(\"ds\").reset_index(drop=True)\n", " return df" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Main forecasting function with automatic tuning of cps\n", "def forecast_revenue_growth_rate(symbol, api_key, history_years=5, cps_grid=None):\n", " if cps_grid is None:\n", " cps_grid = [0.01, 0.05, 0.1, 0.2, 0.3] # Grid for tuning\n", " \n", " df_revenue = get_annual_revenue(symbol, api_key)\n", "\n", " # Filter last N years of data\n", " cutoff_date = pd.Timestamp.today() - pd.DateOffset(years=history_years)\n", " df_revenue = df_revenue[df_revenue['ds'] >= cutoff_date]\n", " \n", " if len(df_revenue) < 4:\n", " raise ValueError(f\"Not enough data to train Prophet for {symbol}\")\n", "\n", " # Hyperparameter tuning using cross-validation\n", " tuning = []\n", " days_per_year = 365.25\n", " initial_years = min(3, len(df_revenue) - 1)\n", " \n", " for cps in cps_grid:\n", " m = Prophet(\n", " yearly_seasonality=False,\n", " changepoint_prior_scale=cps,\n", " n_changepoints=min(5, len(df_revenue)-1)\n", " )\n", " m.fit(df_revenue)\n", " try:\n", " df_cv = cross_validation(\n", " m,\n", " initial=f\"{int(initial_years * days_per_year)} days\",\n", " period=f\"{int(1 * days_per_year)} days\",\n", " horizon=f\"{int(1 * days_per_year)} days\",\n", " parallel=\"processes\"\n", " )\n", " perf = performance_metrics(df_cv)\n", " tuning.append({\n", " \"cps\": cps,\n", " \"mae\": perf['mae'].mean(),\n", " \"mape\": perf['mape'].mean(),\n", " \"rmse\": perf['rmse'].mean()\n", " })\n", " except Exception as e:\n", " print(f\"Cross-validation failed for cps={cps}: {e}\")\n", " \n", " if not tuning:\n", " raise RuntimeError(\"All Prophet cross-validations failed.\")\n", "\n", " # Choose best cps by lowest MAPE\n", " best_cps = min(tuning, key=lambda x: x[\"mape\"])[\"cps\"]\n", "\n", " # Final model with best cps\n", " model = Prophet(\n", " yearly_seasonality=False,\n", " changepoint_prior_scale=best_cps,\n", " n_changepoints=min(5, len(df_revenue)-1)\n", " )\n", " model.fit(df_revenue)\n", "\n", " # Forecast next year\n", " future = model.make_future_dataframe(periods=1, freq='Y')\n", " forecast = model.predict(future)\n", " \n", " last_actual = df_revenue.iloc[-1][\"y\"]\n", " next_forecast = forecast.iloc[-1][\"yhat\"]\n", "\n", " growth_rate = (next_forecast - last_actual) / last_actual * 100\n", "\n", " # Optional: Plot\n", " model.plot(forecast)\n", " plt.title(f\"Forecasted Revenue for {symbol}\")\n", " plt.xlabel(\"Date\")\n", " plt.ylabel(\"Revenue\")\n", " plt.tight_layout()\n", " plt.show()\n", "\n", " return {\n", " \"symbol\": symbol,\n", " \"last_actual_revenue\": last_actual,\n", " \"forecasted_next_year_revenue\": next_forecast,\n", " \"growth_rate\": growth_rate,\n", " \"best_cps\": best_cps\n", " }" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "result = forecast_revenue_growth_rate(company_ticker, ALPHA_VANTAGE_API_KEY)\n", "print(f\"\\nForecasted Revenue Growth for {result['symbol']}: {result['growth_rate']:.2f}%\")\n", "print(f\"Last Revenue: {result['last_actual_revenue']:.0f}\")\n", "print(f\"Forecasted Revenue: {result['forecasted_next_year_revenue']:.0f}\")\n", "print(f\"Optimal changepoint prior scale: {result['best_cps']}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Value Drivers (Operating Margin)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def get_annual_revenue_and_operating_income(symbol: str, api_key: str):\n", " \"\"\"\n", " Fetches the annual income statement data from Alpha Vantage and calculates operating margin.\n", " Returns a DataFrame with the operating margin over time.\n", " \"\"\"\n", " url = f\"https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol={symbol}&apikey={api_key}\"\n", " response = requests.get(url)\n", " \n", " if response.status_code != 200:\n", " raise ValueError(f\"Request failed with status code {response.status_code}\")\n", " \n", " data = response.json()\n", " if \"annualReports\" not in data:\n", " raise ValueError(f\"No annualReports found for symbol {symbol}. Response: {data}\")\n", " \n", " reports = data[\"annualReports\"]\n", " records = []\n", " \n", " for report in reports:\n", " try:\n", " date = pd.to_datetime(report[\"fiscalDateEnding\"])\n", " operating_income = float(report[\"operatingIncome\"])\n", " total_revenue = float(report[\"totalRevenue\"])\n", " if total_revenue != 0:\n", " margin = (operating_income / total_revenue) * 100\n", " records.append({\"ds\": date, \"y\": margin})\n", " except Exception as e:\n", " print(f\"Skipping report due to error: {e}\")\n", " \n", " df = pd.DataFrame(records).sort_values(\"ds\").reset_index(drop=True)\n", " return df" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def forecast_operating_margin(symbol: str, api_key: str, history_years: int = 10, cps_grid=None) -> dict:\n", " \"\"\"\n", " Forecasts next year's operating margin (%) using Prophet,\n", " tuning changepoint_prior_scale via time-series CV on annual data from Alpha Vantage.\n", "\n", " Returns:\n", " {\n", " best_cps: float,\n", " cv_results: DataFrame,\n", " margin_forecast: float, # next-year operating margin (%)\n", " lower_bound: float,\n", " upper_bound: float,\n", " forecast_df: DataFrame # ds, yhat, yhat_lower, yhat_upper\n", " }\n", " \"\"\"\n", " # Default changepoint prior scale grid\n", " if cps_grid is None:\n", " cps_grid = [0.01, 0.05, 0.1, 0.3, 0.5]\n", " \n", " # 1) Pull annual statements from Alpha Vantage\n", " df_margin = get_annual_revenue_and_operating_income(symbol, api_key)\n", "\n", " # 2) Use only the last `history_years` years of data\n", " cutoff_date = pd.Timestamp.today() - pd.DateOffset(years=history_years)\n", " df_margin = df_margin[df_margin['ds'] >= cutoff_date]\n", " \n", " if len(df_margin) < 2:\n", " raise ValueError(f\"Not enough data to train Prophet for {symbol}\")\n", " \n", " # 3) Prepare data for Prophet\n", " df = df_margin[['ds', 'y']]\n", "\n", " # 4) Determine if tuning is feasible\n", " cv_results = pd.DataFrame()\n", " total_days = (df['ds'].max() - df['ds'].min()).days\n", " horizon_days = 365\n", " if total_days >= 2 * horizon_days:\n", " # Compute initial window to leave horizon_days for testing\n", " initial_days = total_days - horizon_days\n", " tuning = []\n", " for cps in cps_grid:\n", " m = Prophet(\n", " yearly_seasonality=False,\n", " changepoint_prior_scale=cps,\n", " n_changepoints=min(5, len(df)-1)\n", " )\n", " m.fit(df)\n", " df_cv = cross_validation(\n", " m,\n", " initial=f\"{initial_days} days\",\n", " period=\"365 days\", # One year\n", " horizon=\"365 days\",\n", " parallel=\"processes\"\n", " )\n", " perf = performance_metrics(df_cv)\n", " tuning.append({\n", " 'cps': cps,\n", " 'mae': perf['mae'].mean(),\n", " 'mape': perf['mape'].mean(),\n", " 'rmse': perf['rmse'].mean()\n", " })\n", " cv_results = pd.DataFrame(tuning)\n", " best_cps = cv_results.loc[cv_results['mape'].idxmin(), 'cps']\n", " else:\n", " best_cps = 0.05\n", "\n", " # 5) Fit final model with the best changepoint prior scale\n", " model = Prophet(\n", " yearly_seasonality=False,\n", " changepoint_prior_scale=best_cps,\n", " n_changepoints=min(5, len(df)-1)\n", " )\n", " model.fit(df)\n", "\n", " # 6) Forecast the next year\n", " future = model.make_future_dataframe(periods=1, freq='A')\n", " forecast = model.predict(future)\n", " last = forecast.iloc[-1]\n", " margin_forecast = last['yhat']\n", " lower, upper = last['yhat_lower'], last['yhat_upper']\n", "\n", " # 7) Visualization\n", " plt.figure(figsize=(10,6))\n", " plt.plot(df['ds'], df['y'], 'o-', label='Historical Margin')\n", " plt.plot(forecast['ds'], forecast['yhat'], '--', label='Forecast')\n", " plt.fill_between(\n", " forecast['ds'], forecast['yhat_lower'], forecast['yhat_upper'],\n", " color='orange', alpha=0.3, label='Uncertainty'\n", " )\n", " plt.axvline(df['ds'].iloc[-1], color='gray', linestyle=':')\n", " plt.title(f\"{symbol} Operating Margin Forecast (cps={best_cps})\")\n", " plt.xlabel(\"Year\")\n", " plt.ylabel(\"Operating Margin (%)\")\n", " plt.legend()\n", " plt.grid(True)\n", " plt.tight_layout()\n", " plt.show()\n", "\n", " return {\n", " 'best_cps': best_cps,\n", " 'cv_results': cv_results,\n", " 'margin_forecast': margin_forecast,\n", " 'lower_bound': lower,\n", " 'upper_bound': upper,\n", " 'forecast_df': forecast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']]\n", " }" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "result = forecast_operating_margin(company_ticker, ALPHA_VANTAGE_API_KEY)\n", "latest_year = result['forecast_df']['ds'].dt.year.max()\n", "forecasted_year = latest_year + 1\n", "print(f\"Forecasted Operating Margin for {forecasted_year}: {result['margin_forecast']:.2f}%\")\n", "print(f\"Lower Bound: {result['lower_bound']:.2f}%\")\n", "print(f\"Upper Bound: {result['upper_bound']:.2f}%\")\n", "print(f\"Optimal changepoint prior scale: {result['best_cps']}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Value Drivers (CAGR)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def get_annual_revenue(symbol: str, api_key: str, history_years: int = 15) -> pd.DataFrame:\n", " \"\"\"\n", " Fetches the annual income statement data from Alpha Vantage and extracts revenue.\n", " \n", " Args:\n", " symbol (str): stock ticker\n", " api_key (str): Alpha Vantage API key\n", " history_years (int): number of years of historical data to fetch\n", "\n", " Returns:\n", " DataFrame with revenue history for the last `history_years` years\n", " \"\"\"\n", " url = f\"https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol={symbol}&apikey={api_key}\"\n", " response = requests.get(url)\n", " \n", " if response.status_code != 200:\n", " raise ValueError(f\"Request failed with status code {response.status_code}\")\n", " \n", " data = response.json()\n", " \n", " if \"annualReports\" not in data:\n", " raise ValueError(f\"No annualReports found for symbol {symbol}. Response: {data}\")\n", " \n", " reports = data[\"annualReports\"]\n", " records = []\n", " \n", " for report in reports:\n", " try:\n", " date = pd.to_datetime(report[\"fiscalDateEnding\"])\n", " total_revenue = float(report[\"totalRevenue\"])\n", " records.append({\"ds\": date, \"y\": total_revenue})\n", " except Exception as e:\n", " print(f\"Skipping report due to error: {e}\")\n", " \n", " df = pd.DataFrame(records).sort_values(\"ds\").reset_index(drop=True)\n", " \n", " # Use only the last `history_years` years of data\n", " cutoff_date = pd.Timestamp.today() - pd.DateOffset(years=history_years)\n", " df = df[df['ds'] >= cutoff_date]\n", " \n", " return df\n", "\n", "def forecast_revenue_cagr(\n", " symbol: str,\n", " api_key: str,\n", " forecast_years: int = 5,\n", " history_years: int = 15,\n", " cps_grid: list = [0.05, 0.1, 0.2, 0.3, 0.5],\n", " growth: str = 'linear',\n", " plot: bool = True\n", ") -> dict:\n", " \"\"\"\n", " Forecast revenue with Prophet, auto-tuning changepoint_prior_scale (CPS).\n", "\n", " Args:\n", " symbol (str): stock ticker\n", " api_key (str): Alpha Vantage API key\n", " forecast_years (int): how many years forward to forecast\n", " history_years (int): how much historical data to use\n", " cps_grid (list): list of CPS values to try\n", " growth (str): 'linear' or 'logistic'\n", " plot (bool): whether to plot results\n", "\n", " Returns:\n", " dict with initial revenue, forecast revenue, CAGR, bounds, best CPS\n", " \"\"\"\n", " # 1) Pull data from Alpha Vantage\n", " df = get_annual_revenue(symbol, api_key, history_years)\n", " \n", " if len(df) < 3:\n", " raise ValueError(f\"Not enough revenue history for tuning ({symbol})\")\n", "\n", " # 2) Auto-tune CPS by splitting into train/validation\n", " errors = []\n", " for cps in cps_grid:\n", " model = Prophet(\n", " growth=growth,\n", " yearly_seasonality=False,\n", " changepoint_prior_scale=cps,\n", " n_changepoints=min(10, len(df)-1)\n", " )\n", " if growth == 'logistic':\n", " cap = df['y'].max() * 1.5\n", " df['cap'] = cap\n", "\n", " model.fit(df.iloc[:-1]) # train on all but last year\n", "\n", " future = model.make_future_dataframe(periods=1, freq='A')\n", " if growth == 'logistic':\n", " future['cap'] = cap\n", "\n", " forecast = model.predict(future)\n", "\n", " predicted = forecast['yhat'].iloc[-1]\n", " actual = df['y'].iloc[-1]\n", " error = np.abs(predicted - actual) / actual # relative error\n", " errors.append((cps, error))\n", "\n", " # 3) Choose best CPS\n", " best_cps, best_error = min(errors, key=lambda x: x[1])\n", " print(f\"Best CPS: {best_cps:.3f} with validation error: {best_error:.2%}\")\n", "\n", " # 4) Retrain model on full data\n", " model = Prophet(\n", " growth=growth,\n", " yearly_seasonality=False,\n", " changepoint_prior_scale=best_cps,\n", " n_changepoints=min(10, len(df)-1)\n", " )\n", " if growth == 'logistic':\n", " cap = df['y'].max() * 1.5\n", " df['cap'] = cap\n", "\n", " model.fit(df)\n", "\n", " future = model.make_future_dataframe(periods=forecast_years, freq='A')\n", " if growth == 'logistic':\n", " future['cap'] = cap\n", "\n", " forecast = model.predict(future)\n", "\n", " # 5) Compute CAGRs\n", " initial_revenue = df['y'].iloc[-1]\n", " forecast_row = forecast.iloc[-1]\n", " forecast_revenue = forecast_row['yhat']\n", " lower_revenue = forecast_row['yhat_lower']\n", " upper_revenue = forecast_row['yhat_upper']\n", "\n", " n = forecast_years\n", " cagr_forecast = (forecast_revenue / initial_revenue) ** (1/n) - 1\n", " lower_bound = (lower_revenue / initial_revenue) ** (1/n) - 1\n", " upper_bound = (upper_revenue / initial_revenue) ** (1/n) - 1\n", "\n", " if plot:\n", " plt.figure(figsize=(10,6))\n", " plt.plot(df['ds'], df['y'], 'o-', label='Historical Revenue')\n", " plt.plot(forecast['ds'], forecast['yhat'], '--', label='Forecast Revenue')\n", " plt.fill_between(\n", " forecast['ds'], forecast['yhat_lower'], forecast['yhat_upper'],\n", " color='orange', alpha=0.3, label='Uncertainty'\n", " )\n", " plt.axvline(df['ds'].iloc[-1], color='gray', linestyle=':')\n", " plt.title(f\"{symbol} Revenue Forecast (5 Years) [Best CPS={best_cps}]\")\n", " plt.xlabel(\"Year\")\n", " plt.ylabel(\"Revenue (USD)\")\n", " plt.legend()\n", " plt.grid(True)\n", " plt.tight_layout()\n", " plt.show()\n", "\n", " return {\n", " 'initial_revenue': initial_revenue,\n", " 'forecast_revenue': forecast_revenue,\n", " 'cagr_forecast': cagr_forecast * 100,\n", " 'lower_bound': lower_bound * 100,\n", " 'upper_bound': upper_bound * 100,\n", " 'forecast_df': forecast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']],\n", " 'best_cps': best_cps\n", " }" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "result = forecast_revenue_cagr(\n", " company_ticker,\n", " ALPHA_VANTAGE_API_KEY,\n", " forecast_years=5,\n", " growth='logistic'\n", ")\n", "\n", "print(f\"5-Year Forecasted CAGR: {result['cagr_forecast']:.2f}%\")\n", "print(f\"90% confidence: {result['lower_bound']:.2f}% → {result['upper_bound']:.2f}%\")\n", "print(f\"Tuned CPS: {result['best_cps']:.2f}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Value Drivers (Target Pre-Tax Operating Margin, Years to Convergence)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def forecast_pretarget_operating_margin(symbol: str, history_years: int = 5, api_key: str = ALPHA_VANTAGE_API_KEY) -> dict:\n", " \"\"\"\n", " Forecasts the Target Pre-Tax Operating Margin and estimates\n", " how many years it'll take to converge using Alpha Vantage API data.\n", "\n", " Returns:\n", " {\n", " 'current_margin': float (%),\n", " 'target_margin': float (%),\n", " 'annual_change': float (%/year),\n", " 'years_to_converge': float,\n", " 'margin_trend_df': DataFrame\n", " }\n", " \"\"\"\n", " # 1. Pull financials from Alpha Vantage API\n", " url = f'https://www.alphavantage.co/query'\n", " params = {\n", " 'function': 'INCOME_STATEMENT',\n", " 'symbol': symbol,\n", " 'apikey': api_key\n", " }\n", " \n", " try:\n", " response = requests.get(url, params=params)\n", " data = response.json()\n", "\n", " # Check if the data was successfully fetched\n", " if 'annualReports' not in data:\n", " raise ValueError(f\"No annual income statement data available for {symbol}\")\n", "\n", " income_statement = pd.DataFrame(data['annualReports'])\n", " except Exception as e:\n", " raise ValueError(f\"Failed to fetch data from Alpha Vantage: {e}\")\n", " \n", " # Ensure data is in correct format\n", " income_statement = income_statement[['fiscalDateEnding', 'operatingIncome', 'totalRevenue']]\n", " income_statement['fiscalDateEnding'] = pd.to_datetime(income_statement['fiscalDateEnding'])\n", " income_statement = income_statement.sort_values('fiscalDateEnding')\n", " \n", " # 2. Calculate operating margin (EBIT / TotalRevenue)\n", " income_statement['operating_margin'] = income_statement['operatingIncome'].astype(float) / income_statement['totalRevenue'].astype(float)\n", " \n", " # 3. Limit to recent history\n", " recent = income_statement.tail(history_years)\n", " \n", " if len(recent) < 2:\n", " raise ValueError(\"Not enough historical margin data for trend estimation.\")\n", "\n", " # 4. Fit linear trend (year vs margin)\n", " X = recent['fiscalDateEnding'].dt.year.values.reshape(-1, 1)\n", " y = recent['operating_margin'].values\n", "\n", " model = LinearRegression()\n", " model.fit(X, y)\n", "\n", " slope = model.coef_[0] # margin change per year\n", " intercept = model.intercept_\n", "\n", " # 5. Forecast target margin (say, extrapolate +5 years ahead)\n", " future_year = X[-1][0] + 5\n", " target_margin = model.predict(np.array([[future_year]]))[0]\n", "\n", " # 6. Current margin\n", " current_margin = y[-1]\n", "\n", " # 7. Time to converge\n", " if np.isclose(slope, 0):\n", " years_to_converge = np.inf # Never converges\n", " else:\n", " years_to_converge = (target_margin - current_margin) / slope\n", "\n", " # 8. Package results\n", " margin_trend_df = pd.DataFrame({'year': X.flatten(), 'operating_margin': y})\n", "\n", " # Visualization\n", " plt.figure(figsize=(10, 6))\n", " plt.plot(recent['fiscalDateEnding'].dt.year, recent['operating_margin'], 'o-', label='Historical Operating Margin', color='blue')\n", " plt.plot(recent['fiscalDateEnding'].dt.year, model.predict(X), '--', label='Regression Line', color='green')\n", " plt.axvline(future_year, color='red', linestyle=':', label=f'Forecast for {future_year}')\n", " plt.scatter(future_year, target_margin, color='red', zorder=5)\n", " plt.text(future_year, target_margin, f'Target Margin: {target_margin*100:.2f}%', \n", " verticalalignment='bottom', horizontalalignment='right', color='red')\n", " \n", " plt.title(f'{symbol} Operating Margin Forecast and Convergence')\n", " plt.xlabel('Year')\n", " plt.ylabel('Operating Margin (%)')\n", " plt.legend()\n", " plt.grid(True)\n", " plt.tight_layout()\n", " plt.show()\n", "\n", " return {\n", " 'current_margin': current_margin * 100,\n", " 'target_margin': target_margin * 100,\n", " 'annual_change': slope * 100,\n", " 'years_to_converge': years_to_converge,\n", " 'margin_trend_df': margin_trend_df\n", " }" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "result = forecast_pretarget_operating_margin(company_ticker, history_years=5, api_key=ALPHA_VANTAGE_API_KEY)\n", "\n", "print(f\"Current Margin: {result['current_margin']:.2f}%\")\n", "print(f\"Target Margin (5yr): {result['target_margin']:.2f}%\")\n", "print(f\"Annual Change: {result['annual_change']:.2f}% per year\")\n", "print(f\"Years to Converge: {result['years_to_converge']:.1f} years\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Value Drivers (Sales to Capital Ratio)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def forecast_sales_to_capital_ratio(\n", " symbol: str,\n", " history_years: int = 15,\n", " cps: float = 0.05,\n", " api_key: str = ALPHA_VANTAGE_API_KEY\n", ") -> dict:\n", " \"\"\"\n", " Forecasts the sales-to-capital ratio (Revenue / InvestedCapital) for\n", " the next 1–5 years and 6–10 years using Alpha Vantage API data and Prophet.\n", "\n", " Returns:\n", " {\n", " 'avg_1_5': float, # average ratio for years 1–5\n", " 'avg_6_10': float, # average ratio for years 6–10\n", " 'forecast_df': DataFrame # full 10-year forecast with yhat, yhat_lower, yhat_upper\n", " }\n", " \"\"\"\n", " # 1) Fetch annual data from Alpha Vantage API\n", " # Fetch Income Statement (Total Revenue)\n", " url_income = f'https://www.alphavantage.co/query'\n", " params_income = {\n", " 'function': 'INCOME_STATEMENT',\n", " 'symbol': symbol,\n", " 'apikey': api_key\n", " }\n", " try:\n", " response_income = requests.get(url_income, params=params_income)\n", " data_income = response_income.json()\n", " if 'annualReports' not in data_income:\n", " raise ValueError(f\"No income statement data available for {symbol}\")\n", " income_data = pd.DataFrame(data_income['annualReports'])\n", " except Exception as e:\n", " raise ValueError(f\"Failed to fetch income data from Alpha Vantage: {e}\")\n", "\n", " # Fetch Balance Sheet (Total Assets as a proxy for Invested Capital)\n", " url_balance = f'https://www.alphavantage.co/query'\n", " params_balance = {\n", " 'function': 'BALANCE_SHEET',\n", " 'symbol': symbol,\n", " 'apikey': api_key\n", " }\n", " try:\n", " response_balance = requests.get(url_balance, params=params_balance)\n", " data_balance = response_balance.json()\n", " if 'annualReports' not in data_balance:\n", " raise ValueError(f\"No balance sheet data available for {symbol}\")\n", " balance_data = pd.DataFrame(data_balance['annualReports'])\n", " except Exception as e:\n", " raise ValueError(f\"Failed to fetch balance sheet data from Alpha Vantage: {e}\")\n", " \n", " # 2) Keep only 12M rows and parse dates\n", " income_data = income_data[['fiscalDateEnding', 'totalRevenue']].copy()\n", " balance_data = balance_data[['fiscalDateEnding', 'totalAssets']].copy()\n", " \n", " income_data['fiscalDateEnding'] = pd.to_datetime(income_data['fiscalDateEnding'])\n", " balance_data['fiscalDateEnding'] = pd.to_datetime(balance_data['fiscalDateEnding'])\n", " \n", " # 3) Merge Revenue & Invested Capital on date\n", " df = pd.merge(\n", " income_data[['fiscalDateEnding', 'totalRevenue']],\n", " balance_data[['fiscalDateEnding', 'totalAssets']],\n", " on='fiscalDateEnding',\n", " how='inner'\n", " ).sort_values('fiscalDateEnding')\n", "\n", " # 4) Limit to the last history_years (drop any nulls)\n", " cutoff = df['fiscalDateEnding'].max() - pd.DateOffset(years=history_years)\n", " df = df[df['fiscalDateEnding'] >= cutoff].dropna(subset=['totalRevenue', 'totalAssets'])\n", "\n", " # 5) Compute the ratio (Revenue / Invested Capital)\n", " df['ratio'] = df['totalRevenue'].astype(float) / df['totalAssets'].astype(float)\n", "\n", " # 6) Prepare for Prophet\n", " df_prophet = df[['fiscalDateEnding', 'ratio']].rename(columns={'fiscalDateEnding': 'ds', 'ratio': 'y'})\n", "\n", " # 7) Fit Prophet model\n", " m = Prophet(\n", " yearly_seasonality=False,\n", " changepoint_prior_scale=cps\n", " )\n", " m.fit(df_prophet)\n", "\n", " # 8) Forecast 10 years ahead (1–10)\n", " future = m.make_future_dataframe(periods=10, freq='A')\n", " fc = m.predict(future)\n", "\n", " # 9) Extract just the 10 forecast points (after the last historical year)\n", " last_hist = df_prophet['ds'].max()\n", " fut = fc[fc['ds'] > last_hist].reset_index(drop=True)\n", "\n", " # 10) Compute averages for years 1–5 and 6–10\n", " avg_1_5 = fut.loc[0:4, 'yhat'].mean()\n", " avg_6_10 = fut.loc[5:9, 'yhat'].mean()\n", "\n", " # 11) Plot historical + forecast\n", " plt.figure(figsize=(10, 5))\n", " plt.plot(df_prophet['ds'], df_prophet['y'], 'o-', label='Historical Ratio')\n", " plt.plot(fut['ds'], fut['yhat'], '--', label='Forecast Ratio')\n", " plt.fill_between(fut['ds'], fut['yhat_lower'], fut['yhat_upper'],\n", " color='orange', alpha=0.3, label='Uncertainty')\n", " plt.axvline(last_hist, color='gray', linestyle=':')\n", " plt.title(f\"{symbol} Sales-to-Capital Ratio Forecast\")\n", " plt.xlabel(\"Year\")\n", " plt.ylabel(\"Revenue / Invested Capital\")\n", " plt.legend()\n", " plt.grid(True)\n", " plt.tight_layout()\n", " plt.show()\n", "\n", " return {\n", " 'avg_1_5': avg_1_5,\n", " 'avg_6_10': avg_6_10,\n", " 'forecast_df': fut[['ds', 'yhat', 'yhat_lower', 'yhat_upper']]\n", " }" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "result = forecast_sales_to_capital_ratio(company_ticker, history_years=10, cps=0.1, api_key=ALPHA_VANTAGE_API_KEY)\n", "\n", "print(f\"Sales-to-Capital (Years 1–5): {result['avg_1_5']:.2f}\")\n", "print(f\"Sales-to-Capital (Years 6–10): {result['avg_6_10']:.2f}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "kOwfaOSlRV3A" }, "outputs": [], "source": [ "rev_growth_rate_for_next_year = input('What is the revenue growth rate for the next year?')\n", "operating_margin_for_next_year = input('What is the operating margin for the next year?')\n", "compounded_annual_growth_rate = input('What is the compounded annual growth rate?')\n", "target_pre_tax_operating_margin = input('What is the target pre-tax operating margin?')\n", "years_of_convergence_for_margin = input('How many years of convergence for the margin?')\n", "sales_to_capital_ratio_for_years_1_to_5 = input('What is the sales to capital ratio for the first 5 years?')\n", "sales_to_capital_ratio_for_years_6_to_10 = input('What is the sales to capital ratio for the last 5 years?')\n", "input_worksheet.update('B26', [[rev_growth_rate_for_next_year]])\n", "input_worksheet.update('B27', [[operating_margin_for_next_year]])\n", "input_worksheet.update('B28', [[compounded_annual_growth_rate]])\n", "input_worksheet.update('B29', [[target_pre_tax_operating_margin]])\n", "input_worksheet.update('B30', [[years_of_convergence_for_margin]])\n", "input_worksheet.update('B31', [[sales_to_capital_ratio_for_years_1_to_5]])\n", "input_worksheet.update('B32', [[sales_to_capital_ratio_for_years_6_to_10]])" ] }, { "cell_type": "markdown", "metadata": { "id": "Qdscex7HRt9q" }, "source": [ "## Market Numbers" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "2ScuwpcbRuQY" }, "outputs": [], "source": [ "rf = financials[\"B34\"]\n", "input_worksheet.update('B34', [[rf]])" ] }, { "cell_type": "markdown", "metadata": { "id": "wCta4Xe0R09j" }, "source": [ "## Valuation Metrics" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 35 }, "id": "5JI72ycT610M", "outputId": "07e5bca6-4a3a-44d7-bede-d2dffa3fda3f" }, "outputs": [], "source": [ "valuation_worksheet.acell('B33').value" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "valuation_worksheet.acell('B34').value" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "valuation_worksheet.acell('B35').value" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "colab": { "provenance": [] }, "kernelspec": { "display_name": "base", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.9" } }, "nbformat": 4, "nbformat_minor": 0 }