sp500.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "import yfinance as yf\n",
    "pd.set_option('display.max_row', 1500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extracting actual companies in S&P 500 Index + calculating lenght of companies in the index\n",
    "table=pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')\n",
    "sp500 = table[0]\n",
    "sp500 = sp500[\"Symbol\"].tolist()\n",
    "sp500_len = len(sp500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "value_stock = yf.Ticker(sp500[0])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Metrics</th>\n",
       "      <th>Date</th>\n",
       "      <th>Value</th>\n",
       "      <th>Stock</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>Pretax Income</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>7204.0</td>\n",
       "      <td>MMM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>Other Income Expense</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>389.0</td>\n",
       "      <td>MMM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>Salaries And Wages</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>-134.0</td>\n",
       "      <td>MMM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Normalized Income</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>-7020.992</td>\n",
       "      <td>MMM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Basic Average Shares</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>553.9</td>\n",
       "      <td>MMM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Metrics       Date     Value Stock\n",
       "127         Pretax Income 2021-12-31    7204.0   MMM\n",
       "177  Other Income Expense 2020-12-31     389.0   MMM\n",
       "191    Salaries And Wages 2020-12-31    -134.0   MMM\n",
       "13      Normalized Income 2023-12-31 -7020.992   MMM\n",
       "18   Basic Average Shares 2023-12-31     553.9   MMM"
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table=pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')\n",
    "sp500 = table[0]\n",
    "sp500 = sp500[\"Symbol\"].tolist()\n",
    "sp500_len = len(sp500)\n",
    "value_stock = yf.Ticker(sp500[0])\n",
    "sp500_len = len(sp500)\n",
    "income_statement = value_stock.income_stmt\n",
    "income_statement = income_statement.reset_index().rename(columns={'index': 'Metrics'})\n",
    "income_statement = pd.melt(income_statement, id_vars=['Metrics'], var_name='Date', value_name='Value')\n",
    "income_statement['Stock'] = value_stock.info['symbol']\n",
    "income_statement['Value'] = income_statement['Value'] / 1000000\n",
    "income_statement.sample(5)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Metrics</th>\n",
       "      <th>Date</th>\n",
       "      <th>Value</th>\n",
       "      <th>Stock</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25865</th>\n",
       "      <td>Total Expenses</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>3646.0</td>\n",
       "      <td>EW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63724</th>\n",
       "      <td>Gross Profit</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>1387.424</td>\n",
       "      <td>ROL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20040</th>\n",
       "      <td>Operating Income</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>2112.0</td>\n",
       "      <td>GLW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71084</th>\n",
       "      <td>Interest Expense</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>2321.0</td>\n",
       "      <td>TFC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67873</th>\n",
       "      <td>Net Income From Continuing And Discontinued Op...</td>\n",
       "      <td>2022-06-30</td>\n",
       "      <td>1358.768</td>\n",
       "      <td>SYY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Metrics       Date     Value  \\\n",
       "25865                                     Total Expenses 2021-12-31    3646.0   \n",
       "63724                                       Gross Profit 2022-12-31  1387.424   \n",
       "20040                                   Operating Income 2021-12-31    2112.0   \n",
       "71084                                   Interest Expense 2022-12-31    2321.0   \n",
       "67873  Net Income From Continuing And Discontinued Op... 2022-06-30  1358.768   \n",
       "\n",
       "      Stock  \n",
       "25865    EW  \n",
       "63724   ROL  \n",
       "20040   GLW  \n",
       "71084   TFC  \n",
       "67873   SYY  "
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_statement_cobined = pd.DataFrame()\n",
    "\n",
    "# Iterate through each company in the S&P 500\n",
    "for symbol in sp500:\n",
    "    value_stock = yf.Ticker(symbol)\n",
    "    income_statement = value_stock.income_stmt\n",
    "    income_statement = income_statement.reset_index().rename(columns={'index': 'Metrics'})\n",
    "    income_statement = pd.melt(income_statement, id_vars=['Metrics'], var_name='Date', value_name='Value')\n",
    "    income_statement['Stock'] = value_stock.info['symbol']\n",
    "    income_statement['Value'] = income_statement['Value'] / 1000000\n",
    "        \n",
    "    # Concatenate data for the current company to the combined DataFrame\n",
    "    income_statement_cobined = pd.concat([income_statement_cobined, income_statement], ignore_index=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Metrics</th>\n",
       "      <th>Date</th>\n",
       "      <th>Value</th>\n",
       "      <th>Stock</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39633</th>\n",
       "      <td>Current Debt</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10240</th>\n",
       "      <td>Total Liabilities Net Minority Interest</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>35158.0</td>\n",
       "      <td>ADM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69686</th>\n",
       "      <td>Available For Sale Securities</td>\n",
       "      <td>2020-11-30</td>\n",
       "      <td>68.771</td>\n",
       "      <td>LEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30751</th>\n",
       "      <td>Common Stock</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>7.0</td>\n",
       "      <td>CTVA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117494</th>\n",
       "      <td>Other Properties</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>110.8</td>\n",
       "      <td>WRK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        Metrics       Date    Value Stock\n",
       "39633                              Current Debt 2022-03-31      NaN    EA\n",
       "10240   Total Liabilities Net Minority Interest 2022-12-31  35158.0   ADM\n",
       "69686             Available For Sale Securities 2020-11-30   68.771   LEN\n",
       "30751                              Common Stock 2021-12-31      7.0  CTVA\n",
       "117494                         Other Properties 2020-09-30    110.8   WRK"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "balance_sheet_cobined = pd.DataFrame()\n",
    "\n",
    "# Iterate through each company in the S&P 500\n",
    "for symbol in sp500:\n",
    "    value_stock = yf.Ticker(symbol)\n",
    "    balance_sheet = value_stock.balance_sheet\n",
    "    balance_sheet = balance_sheet.reset_index().rename(columns={'index': 'Metrics'})\n",
    "    balance_sheet = pd.melt(balance_sheet, id_vars=['Metrics'], var_name='Date', value_name='Value')\n",
    "    balance_sheet['Stock'] = value_stock.info['symbol']\n",
    "    balance_sheet['Value'] = balance_sheet['Value'] / 1000000\n",
    "        \n",
    "    # Concatenate data for the current company to the combined DataFrame\n",
    "    balance_sheet_cobined = pd.concat([balance_sheet_cobined, balance_sheet], ignore_index=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Metrics</th>\n",
       "      <th>Date</th>\n",
       "      <th>Value</th>\n",
       "      <th>Stock</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>37761</th>\n",
       "      <td>Net Foreign Currency Exchange Gain Loss</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>FI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78338</th>\n",
       "      <td>Common Stock Payments</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>-431.666</td>\n",
       "      <td>SBAC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90257</th>\n",
       "      <td>Change In Account Payable</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>-70.4</td>\n",
       "      <td>VTRS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84964</th>\n",
       "      <td>Repurchase Of Capital Stock</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>-1168.0</td>\n",
       "      <td>TXT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69879</th>\n",
       "      <td>Purchase Of PPE</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>-94.102</td>\n",
       "      <td>PAYC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13773</th>\n",
       "      <td>Asset Impairment Charge</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>533.0</td>\n",
       "      <td>BSX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34627</th>\n",
       "      <td>Depreciation And Amortization</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>1159.059</td>\n",
       "      <td>ES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34762</th>\n",
       "      <td>Cash Flow From Continuing Investing Activities</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>-4336.0</td>\n",
       "      <td>EXC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29544</th>\n",
       "      <td>Net Other Financing Charges</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>-54.8</td>\n",
       "      <td>DHI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19470</th>\n",
       "      <td>Sale Of Business</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>CVX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43518</th>\n",
       "      <td>Change In Tax Payable</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>-16.158</td>\n",
       "      <td>HSY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71565</th>\n",
       "      <td>Gain Loss On Investment Securities</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>-451.0</td>\n",
       "      <td>PXD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27541</th>\n",
       "      <td>Depreciation Amortization Depletion</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>2223.0</td>\n",
       "      <td>DVN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37168</th>\n",
       "      <td>Operating Gains Losses</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>-408.0</td>\n",
       "      <td>FITB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71676</th>\n",
       "      <td>Earnings Losses From Equity Investments</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PNC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50222</th>\n",
       "      <td>Deferred Tax</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>85.0</td>\n",
       "      <td>JBL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4816</th>\n",
       "      <td>Cash Dividends Paid</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61396</th>\n",
       "      <td>Net Common Stock Issuance</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>-307.572</td>\n",
       "      <td>MHK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15944</th>\n",
       "      <td>Change In Working Capital</td>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>464.0</td>\n",
       "      <td>CAH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89958</th>\n",
       "      <td>Deferred Income Tax</td>\n",
       "      <td>2023-03-31</td>\n",
       "      <td>-53.554</td>\n",
       "      <td>VFC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30395</th>\n",
       "      <td>Cash Flow From Continuing Investing Activities</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>-29.0</td>\n",
       "      <td>EMN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34327</th>\n",
       "      <td>Deferred Income Tax</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>7.3</td>\n",
       "      <td>EVRG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90281</th>\n",
       "      <td>End Cash Position</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>850.0</td>\n",
       "      <td>VTRS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49086</th>\n",
       "      <td>Capital Expenditure</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>-137.0</td>\n",
       "      <td>INTU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45620</th>\n",
       "      <td>Pension And Employee Benefit Expense</td>\n",
       "      <td>2020-10-31</td>\n",
       "      <td>214.0</td>\n",
       "      <td>HPQ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4275</th>\n",
       "      <td>Interest Paid Supplemental Data</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>1772.0</td>\n",
       "      <td>AMZN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29842</th>\n",
       "      <td>Net Business Purchase And Sale</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>DTE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3019</th>\n",
       "      <td>Net Issuance Payments Of Debt</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>ALGN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56385</th>\n",
       "      <td>Change In Payables And Accrued Expense</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>-5.93</td>\n",
       "      <td>LULU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1613</th>\n",
       "      <td>Effect Of Exchange Rate Changes</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>104.0</td>\n",
       "      <td>AFL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20833</th>\n",
       "      <td>Cash Flow From Continuing Operating Activities</td>\n",
       "      <td>2021-07-31</td>\n",
       "      <td>15454.0</td>\n",
       "      <td>CSCO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40843</th>\n",
       "      <td>Operating Gains Losses</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>45.0</td>\n",
       "      <td>GE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12480</th>\n",
       "      <td>Cash Flow From Continuing Operating Activities</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>4229.8</td>\n",
       "      <td>BIIB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54105</th>\n",
       "      <td>Other Non Cash Items</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>283.7</td>\n",
       "      <td>LH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41163</th>\n",
       "      <td>Long Term Debt Payments</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>-1396.7</td>\n",
       "      <td>GIS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12025</th>\n",
       "      <td>Effect Of Exchange Rate Changes</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>12.427</td>\n",
       "      <td>BIO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49057</th>\n",
       "      <td>Capital Expenditure Reported</td>\n",
       "      <td>2021-07-31</td>\n",
       "      <td>-72.0</td>\n",
       "      <td>INTU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91832</th>\n",
       "      <td>Depreciation Amortization Depletion</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>5345.0</td>\n",
       "      <td>DIS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19562</th>\n",
       "      <td>Repurchase Of Capital Stock</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>-1757.0</td>\n",
       "      <td>CVX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49399</th>\n",
       "      <td>Cash Flow From Continuing Financing Activities</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>117.3</td>\n",
       "      <td>IVZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87905</th>\n",
       "      <td>Purchase Of Investment</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>-11232.0</td>\n",
       "      <td>UAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6401</th>\n",
       "      <td>Long Term Debt Payments</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>-169.6</td>\n",
       "      <td>APH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18277</th>\n",
       "      <td>Changes In Account Receivables</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>-2453.0</td>\n",
       "      <td>CNC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44380</th>\n",
       "      <td>Net Intangibles Purchase And Sale</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>0.0</td>\n",
       "      <td>HOLX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12352</th>\n",
       "      <td>Net PPE Purchase And Sale</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>342.3</td>\n",
       "      <td>BIIB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5687</th>\n",
       "      <td>Net Issuance Payments Of Debt</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>1398.0</td>\n",
       "      <td>AWK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64981</th>\n",
       "      <td>Depreciation And Amortization</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>1119.0</td>\n",
       "      <td>NKE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8715</th>\n",
       "      <td>Common Stock Issuance</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>1546.7</td>\n",
       "      <td>AJG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49063</th>\n",
       "      <td>Change In Payables And Accrued Expense</td>\n",
       "      <td>2021-07-31</td>\n",
       "      <td>198.0</td>\n",
       "      <td>INTU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9938</th>\n",
       "      <td>Net Common Stock Issuance</td>\n",
       "      <td>2020-08-31</td>\n",
       "      <td>-862.511</td>\n",
       "      <td>AZO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Metrics       Date     Value  \\\n",
       "37761         Net Foreign Currency Exchange Gain Loss 2020-12-31       0.0   \n",
       "78338                           Common Stock Payments 2022-12-31  -431.666   \n",
       "90257                       Change In Account Payable 2021-12-31     -70.4   \n",
       "84964                     Repurchase Of Capital Stock 2023-12-31   -1168.0   \n",
       "69879                                 Purchase Of PPE 2020-12-31   -94.102   \n",
       "13773                         Asset Impairment Charge 2020-12-31     533.0   \n",
       "34627                   Depreciation And Amortization 2020-12-31  1159.059   \n",
       "34762  Cash Flow From Continuing Investing Activities 2020-12-31   -4336.0   \n",
       "29544                     Net Other Financing Charges 2023-09-30     -54.8   \n",
       "19470                                Sale Of Business 2022-12-31    1200.0   \n",
       "43518                           Change In Tax Payable 2020-12-31   -16.158   \n",
       "71565              Gain Loss On Investment Securities 2021-12-31    -451.0   \n",
       "27541             Depreciation Amortization Depletion 2022-12-31    2223.0   \n",
       "37168                          Operating Gains Losses 2020-12-31    -408.0   \n",
       "71676         Earnings Losses From Equity Investments 2022-12-31       NaN   \n",
       "50222                                    Deferred Tax 2023-08-31      85.0   \n",
       "4816                              Cash Dividends Paid 2022-12-31       0.0   \n",
       "61396                       Net Common Stock Issuance 2022-12-31  -307.572   \n",
       "15944                       Change In Working Capital 2023-06-30     464.0   \n",
       "89958                             Deferred Income Tax 2023-03-31   -53.554   \n",
       "30395  Cash Flow From Continuing Investing Activities 2021-12-31     -29.0   \n",
       "34327                             Deferred Income Tax 2022-12-31       7.3   \n",
       "90281                               End Cash Position 2020-12-31     850.0   \n",
       "49086                             Capital Expenditure 2020-07-31    -137.0   \n",
       "45620            Pension And Employee Benefit Expense 2020-10-31     214.0   \n",
       "4275                  Interest Paid Supplemental Data 2021-12-31    1772.0   \n",
       "29842                  Net Business Purchase And Sale 2022-12-31     -13.0   \n",
       "3019                    Net Issuance Payments Of Debt 2021-12-31       0.0   \n",
       "56385          Change In Payables And Accrued Expense 2023-01-31     -5.93   \n",
       "1613                  Effect Of Exchange Rate Changes 2022-12-31     104.0   \n",
       "20833  Cash Flow From Continuing Operating Activities 2021-07-31   15454.0   \n",
       "40843                          Operating Gains Losses 2022-12-31      45.0   \n",
       "12480  Cash Flow From Continuing Operating Activities 2020-12-31    4229.8   \n",
       "54105                            Other Non Cash Items 2020-12-31     283.7   \n",
       "41163                         Long Term Debt Payments 2020-05-31   -1396.7   \n",
       "12025                 Effect Of Exchange Rate Changes 2020-12-31    12.427   \n",
       "49057                    Capital Expenditure Reported 2021-07-31     -72.0   \n",
       "91832             Depreciation Amortization Depletion 2020-09-30    5345.0   \n",
       "19562                     Repurchase Of Capital Stock 2020-12-31   -1757.0   \n",
       "49399  Cash Flow From Continuing Financing Activities 2021-12-31     117.3   \n",
       "87905                          Purchase Of Investment 2022-12-31  -11232.0   \n",
       "6401                          Long Term Debt Payments 2022-12-31    -169.6   \n",
       "18277                  Changes In Account Receivables 2021-12-31   -2453.0   \n",
       "44380               Net Intangibles Purchase And Sale 2020-09-30       0.0   \n",
       "12352                       Net PPE Purchase And Sale 2022-12-31     342.3   \n",
       "5687                    Net Issuance Payments Of Debt 2022-12-31    1398.0   \n",
       "64981                   Depreciation And Amortization 2020-05-31    1119.0   \n",
       "8715                            Common Stock Issuance 2021-12-31    1546.7   \n",
       "49063          Change In Payables And Accrued Expense 2021-07-31     198.0   \n",
       "9938                        Net Common Stock Issuance 2020-08-31  -862.511   \n",
       "\n",
       "      Stock  \n",
       "37761    FI  \n",
       "78338  SBAC  \n",
       "90257  VTRS  \n",
       "84964   TXT  \n",
       "69879  PAYC  \n",
       "13773   BSX  \n",
       "34627    ES  \n",
       "34762   EXC  \n",
       "29544   DHI  \n",
       "19470   CVX  \n",
       "43518   HSY  \n",
       "71565   PXD  \n",
       "27541   DVN  \n",
       "37168  FITB  \n",
       "71676   PNC  \n",
       "50222   JBL  \n",
       "4816    AAL  \n",
       "61396   MHK  \n",
       "15944   CAH  \n",
       "89958   VFC  \n",
       "30395   EMN  \n",
       "34327  EVRG  \n",
       "90281  VTRS  \n",
       "49086  INTU  \n",
       "45620   HPQ  \n",
       "4275   AMZN  \n",
       "29842   DTE  \n",
       "3019   ALGN  \n",
       "56385  LULU  \n",
       "1613    AFL  \n",
       "20833  CSCO  \n",
       "40843    GE  \n",
       "12480  BIIB  \n",
       "54105    LH  \n",
       "41163   GIS  \n",
       "12025   BIO  \n",
       "49057  INTU  \n",
       "91832   DIS  \n",
       "19562   CVX  \n",
       "49399   IVZ  \n",
       "87905   UAL  \n",
       "6401    APH  \n",
       "18277   CNC  \n",
       "44380  HOLX  \n",
       "12352  BIIB  \n",
       "5687    AWK  \n",
       "64981   NKE  \n",
       "8715    AJG  \n",
       "49063  INTU  \n",
       "9938    AZO  "
      ]
     },
     "execution_count": 190,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cashflow_cobined = pd.DataFrame()\n",
    "\n",
    "# Iterate through each company in the S&P 500\n",
    "for symbol in sp500:\n",
    "    value_stock = yf.Ticker(symbol)\n",
    "    cashflow = value_stock.cashflow\n",
    "    cashflow = cashflow.reset_index().rename(columns={'index': 'Metrics'})\n",
    "    cashflow = pd.melt(cashflow, id_vars=['Metrics'], var_name='Date', value_name='Value')\n",
    "    cashflow['Stock'] = value_stock.info['symbol']\n",
    "    cashflow['Value'] = cashflow['Value'] / 1000000\n",
    "        \n",
    "    # Concatenate data for the current company to the combined DataFrame\n",
    "    cashflow_cobined = pd.concat([cashflow_cobined, cashflow], ignore_index=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cahflow = value_stock.balance_sheet\n",
    "cahflow = cahflow.reset_index().rename(columns={'index': 'Metrics'})\n",
    "cahflow = pd.melt(cahflow, id_vars=['Metrics'], var_name='Date', value_name='Value')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "quarterly_income_stmt = value_stock.quarterly_income_stmt\n",
    "quarterly_balance_sheet = value_stock.quarterly_balance_sheet\n",
    "quarterly_cashflow = value_stock.quarterly_cashflow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Metrics</th>\n",
       "      <th>Date</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Free Cash Flow</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>1628000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Repurchase Of Capital Stock</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>-2000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Repayment Of Debt</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>-652000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Issuance Of Debt</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>824000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Capital Expenditure</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>-358000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>Depreciation And Amortization</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>460000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271</th>\n",
       "      <td>Operating Gains Losses</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>54000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272</th>\n",
       "      <td>Pension And Employee Benefit Expense</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>54000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>273</th>\n",
       "      <td>Gain Loss On Sale Of Business</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>274</th>\n",
       "      <td>Net Income From Continuing Operations</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>543000000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>275 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Metrics       Date         Value\n",
       "0                           Free Cash Flow 2023-12-31  1628000000.0\n",
       "1              Repurchase Of Capital Stock 2023-12-31    -2000000.0\n",
       "2                        Repayment Of Debt 2023-12-31  -652000000.0\n",
       "3                         Issuance Of Debt 2023-12-31   824000000.0\n",
       "4                      Capital Expenditure 2023-12-31  -358000000.0\n",
       "..                                     ...        ...           ...\n",
       "270          Depreciation And Amortization 2022-12-31   460000000.0\n",
       "271                 Operating Gains Losses 2022-12-31    54000000.0\n",
       "272   Pension And Employee Benefit Expense 2022-12-31    54000000.0\n",
       "273          Gain Loss On Sale Of Business 2022-12-31           0.0\n",
       "274  Net Income From Continuing Operations 2022-12-31   543000000.0\n",
       "\n",
       "[275 rows x 3 columns]"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "quarterly_cashflow = quarterly_cashflow.reset_index()\n",
    "quarterly_cashflow = quarterly_cashflow.rename(columns={'index': 'Metrics'})\n",
    "quarterly_cashflow = pd.melt(quarterly_cashflow, id_vars=['Metrics'], var_name='Date', value_name='Value')\n",
    "quarterly_cashflow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\Nino\\anaconda3\\lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1412: FutureWarning: The default value of `n_init` will change from 10 to 'auto' in 1.4. Set the value of `n_init` explicitly to suppress the warning\n",
      "  super()._check_params_vs_input(X, default_n_init=10)\n",
      "c:\\Users\\Nino\\anaconda3\\lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1436: UserWarning: KMeans is known to have a memory leak on Windows with MKL, when there are less chunks than available threads. You can avoid it by setting the environment variable OMP_NUM_THREADS=1.\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cluster centroids:\n",
      "[[1. 2.]\n",
      " [4. 2.]]\n",
      "Cluster assignments:\n",
      "[0 0 0 1 1 1]\n"
     ]
    }
   ],
   "source": [
    "from sklearn.cluster import KMeans\n",
    "import numpy as np\n",
    "\n",
    "# Generate some sample data\n",
    "X = np.array([[1, 2], [1, 4], [1, 0],\n",
    "              [4, 2], [4, 4], [4, 0]])\n",
    "\n",
    "# Define the number of clusters (K)\n",
    "k = 2\n",
    "\n",
    "# Initialize the KMeans model\n",
    "kmeans = KMeans(n_clusters=k)\n",
    "\n",
    "# Fit the model to the data\n",
    "kmeans.fit(X)\n",
    "\n",
    "# Get the cluster centroids\n",
    "centroids = kmeans.cluster_centers_\n",
    "\n",
    "# Get the cluster assignments for each data point\n",
    "labels = kmeans.labels_\n",
    "\n",
    "print(\"Cluster centroids:\")\n",
    "print(centroids)\n",
    "print(\"Cluster assignments:\")\n",
    "print(labels)\n"
   ]
  }
 ],
 "metadata": {
  "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": 2
}