calc_Beta.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import liberaries and functions\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import statsmodels.api as sm\n",
    "# import statsmodels.formula.api as smf\n",
    "from tqdm.auto import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>mktrf</th>\n",
       "      <th>smb</th>\n",
       "      <th>hml</th>\n",
       "      <th>rf</th>\n",
       "      <th>umd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2006-01-03</th>\n",
       "      <td>0.0150</td>\n",
       "      <td>-0.0020</td>\n",
       "      <td>0.0016</td>\n",
       "      <td>0.00017</td>\n",
       "      <td>0.0115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-04</th>\n",
       "      <td>0.0046</td>\n",
       "      <td>0.0036</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>0.00017</td>\n",
       "      <td>0.0024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-05</th>\n",
       "      <td>0.0003</td>\n",
       "      <td>0.0028</td>\n",
       "      <td>-0.0010</td>\n",
       "      <td>0.00017</td>\n",
       "      <td>-0.0055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-06</th>\n",
       "      <td>0.0092</td>\n",
       "      <td>0.0011</td>\n",
       "      <td>-0.0024</td>\n",
       "      <td>0.00017</td>\n",
       "      <td>0.0029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-09</th>\n",
       "      <td>0.0045</td>\n",
       "      <td>0.0052</td>\n",
       "      <td>-0.0017</td>\n",
       "      <td>0.00017</td>\n",
       "      <td>-0.0005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             mktrf     smb     hml       rf     umd\n",
       "date                                               \n",
       "2006-01-03  0.0150 -0.0020  0.0016  0.00017  0.0115\n",
       "2006-01-04  0.0046  0.0036  0.0003  0.00017  0.0024\n",
       "2006-01-05  0.0003  0.0028 -0.0010  0.00017 -0.0055\n",
       "2006-01-06  0.0092  0.0011 -0.0024  0.00017  0.0029\n",
       "2006-01-09  0.0045  0.0052 -0.0017  0.00017 -0.0005"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rf_rates = pd.read_csv(\"Data/rf_rates.csv\", parse_dates=[\"date\"], index_col=\"date\")\n",
    "rf_rates.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "mktrf > Excess Return on the Market\\\n",
    "rf > Risk-Free Return Rate (One Month Treasury Bill Rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>CIK</th>\n",
       "      <th>VOLUME</th>\n",
       "      <th>TTLCMNSHARESOUT</th>\n",
       "      <th>CLOSEPRICE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>datadate</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2006-01-03</th>\n",
       "      <td>20</td>\n",
       "      <td>4632.0</td>\n",
       "      <td>2576000.0</td>\n",
       "      <td>37.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-04</th>\n",
       "      <td>20</td>\n",
       "      <td>1806.0</td>\n",
       "      <td>2576000.0</td>\n",
       "      <td>36.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-05</th>\n",
       "      <td>20</td>\n",
       "      <td>495.0</td>\n",
       "      <td>2576000.0</td>\n",
       "      <td>36.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>2576000.0</td>\n",
       "      <td>37.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-09</th>\n",
       "      <td>20</td>\n",
       "      <td>1250.0</td>\n",
       "      <td>2576000.0</td>\n",
       "      <td>37.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CIK  VOLUME  TTLCMNSHARESOUT  CLOSEPRICE\n",
       "datadate                                            \n",
       "2006-01-03   20  4632.0        2576000.0       37.03\n",
       "2006-01-04   20  1806.0        2576000.0       36.50\n",
       "2006-01-05   20   495.0        2576000.0       36.75\n",
       "2006-01-06   20  1100.0        2576000.0       37.00\n",
       "2006-01-09   20  1250.0        2576000.0       37.00"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prices_df = pd.read_csv(\"Data\\WRDS_prices.csv\", parse_dates=[\"datadate\"], index_col='datadate')\n",
    "prices_df.rename(columns={'cik': 'CIK'}, inplace=True)\n",
    "prices_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate returns\n",
    "# prices_df[\"CLOSEPRICE\"] = prices_df.groupby(\"CIK\")[\"CLOSEPRICE\"].ffill(limit=1)\n",
    "prices_df[\"Return\"] = prices_df.groupby(\"CIK\")[\"CLOSEPRICE\"].pct_change(1, fill_method=None)\n",
    "# prices_df[\"log_Return\"] = np.log(prices_df[\"Return\"] + 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "Beta_df = pd.merge(\n",
    "    left=prices_df[['CIK', 'Return']],\n",
    "    right=rf_rates,\n",
    "    left_index=True,\n",
    "    right_index=True,\n",
    "    how='left'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>CIK</th>\n",
       "      <th>Return</th>\n",
       "      <th>mktrf</th>\n",
       "      <th>smb</th>\n",
       "      <th>hml</th>\n",
       "      <th>rf</th>\n",
       "      <th>ex_Return</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>datadate</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2006-01-04</th>\n",
       "      <td>20</td>\n",
       "      <td>-0.014313</td>\n",
       "      <td>0.0046</td>\n",
       "      <td>0.0036</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>0.00017</td>\n",
       "      <td>-0.014483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-05</th>\n",
       "      <td>20</td>\n",
       "      <td>0.006849</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>0.0028</td>\n",
       "      <td>-0.0010</td>\n",
       "      <td>0.00017</td>\n",
       "      <td>0.006679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>0.006803</td>\n",
       "      <td>0.0092</td>\n",
       "      <td>0.0011</td>\n",
       "      <td>-0.0024</td>\n",
       "      <td>0.00017</td>\n",
       "      <td>0.006633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-09</th>\n",
       "      <td>20</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0045</td>\n",
       "      <td>0.0052</td>\n",
       "      <td>-0.0017</td>\n",
       "      <td>0.00017</td>\n",
       "      <td>-0.000170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-11</th>\n",
       "      <td>20</td>\n",
       "      <td>-0.012500</td>\n",
       "      <td>0.0028</td>\n",
       "      <td>-0.0023</td>\n",
       "      <td>-0.0011</td>\n",
       "      <td>0.00017</td>\n",
       "      <td>-0.012670</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CIK    Return   mktrf     smb     hml       rf  ex_Return\n",
       "datadate                                                             \n",
       "2006-01-04   20 -0.014313  0.0046  0.0036  0.0003  0.00017  -0.014483\n",
       "2006-01-05   20  0.006849  0.0003  0.0028 -0.0010  0.00017   0.006679\n",
       "2006-01-06   20  0.006803  0.0092  0.0011 -0.0024  0.00017   0.006633\n",
       "2006-01-09   20  0.000000  0.0045  0.0052 -0.0017  0.00017  -0.000170\n",
       "2006-01-11   20 -0.012500  0.0028 -0.0023 -0.0011  0.00017  -0.012670"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Beta_df[\"ex_Return\"] = Beta_df[\"Return\"] - Beta_df[\"rf\"]\n",
    "Beta_df.dropna(subset=['ex_Return', 'mktrf', 'smb', 'hml'], inplace=True)\n",
    "Beta_df.drop(columns=['umd'], inplace=True)\n",
    "Beta_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 63 # 3 month\n",
    "\n",
    "Beta_df[f\"mktrf_var_{N}\"] = (\n",
    "    Beta_df.groupby('CIK')[\"mktrf\"]\n",
    "    .rolling(N, min_periods=N//2).var()\n",
    ").values\n",
    "\n",
    "Beta_df[f\"covar_{N}\"] = (\n",
    "    Beta_df.groupby(\"CIK\")[[\"mktrf\", \"ex_Return\"]]\n",
    "    .rolling(N, min_periods=N//2).cov()\n",
    "    .unstack()[\"mktrf\"][\"ex_Return\"]\n",
    ").values\n",
    "\n",
    "Beta_df[f\"Beta_{N}\"] = Beta_df[f\"covar_{N}\"] / Beta_df[f\"mktrf_var_{N}\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 126 # 6 month\n",
    "\n",
    "Beta_df[f\"mktrf_var_{N}\"] = (\n",
    "    Beta_df.groupby('CIK')[\"mktrf\"]\n",
    "    .rolling(N, min_periods=N//2).var()\n",
    ").values\n",
    "\n",
    "Beta_df[f\"covar_{N}\"] = (\n",
    "    Beta_df.groupby(\"CIK\")[[\"mktrf\", \"ex_Return\"]]\n",
    "    .rolling(N, min_periods=N//2).cov()\n",
    "    .unstack()[\"mktrf\"][\"ex_Return\"]\n",
    ").values\n",
    "\n",
    "Beta_df[f\"Beta_{N}\"] = Beta_df[f\"covar_{N}\"] / Beta_df[f\"mktrf_var_{N}\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 252 # 1 year\n",
    "\n",
    "Beta_df[f\"mktrf_var_{N}\"] = (\n",
    "    Beta_df.groupby('CIK')[\"mktrf\"]\n",
    "    .rolling(N, min_periods=N//2).var()\n",
    ").values\n",
    "\n",
    "Beta_df[f\"covar_{N}\"] = (\n",
    "    Beta_df.groupby(\"CIK\")[[\"mktrf\", \"ex_Return\"]]\n",
    "    .rolling(N, min_periods=N//2).cov()\n",
    "    .unstack()[\"mktrf\"][\"ex_Return\"]\n",
    ").values\n",
    "\n",
    "Beta_df[f\"Beta_{N}\"] = Beta_df[f\"covar_{N}\"] / Beta_df[f\"mktrf_var_{N}\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Abnormal Return = Actual Return - Expected Return \n",
    "# Estimation window: 10 days before the event day\n",
    "Beta_df[\"AR\"] = Beta_df[\"Return\"] - (\n",
    "    Beta_df[\"rf\"] + Beta_df.groupby(\"CIK\")[\"Beta_126\"].shift(10)*Beta_df[\"mktrf\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cumulative AR - Event window: -5 and +5 days\n",
    "# sum of values over the event window at the event day\n",
    "components = (\n",
    "    Beta_df.groupby(\"CIK\")[[\"Return\", \"rf\", \"mktrf\"]]\n",
    "    .rolling(11, min_periods=6).sum().groupby(\"CIK\").shift(-5)\n",
    ")\n",
    "\n",
    "Beta_df[\"CAR_5\"] = (\n",
    "    (components[\"Return\"] - components[\"rf\"]).values - \n",
    "    Beta_df.groupby(\"CIK\")[\"Beta_126\"].shift(10).values * components[\"mktrf\"].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cumulative AR - Event window: -5 and +10 days\n",
    "# sum of values over the event window at the event day (.shift(-10))\n",
    "components = (\n",
    "    Beta_df.groupby(\"CIK\")[[\"Return\", \"rf\", \"mktrf\"]]\n",
    "    .rolling(16, min_periods=9).sum().groupby(\"CIK\").shift(-10)\n",
    ")\n",
    "\n",
    "Beta_df[\"CAR_10\"] = (\n",
    "    (components[\"Return\"] - components[\"rf\"]).values - \n",
    "    Beta_df.groupby(\"CIK\")[\"Beta_126\"].shift(10).values * components[\"mktrf\"].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cumulative AR - Event window: -2 and 2 days\n",
    "# sum of values over the event window at the event day\n",
    "components = (\n",
    "    Beta_df.groupby(\"CIK\")[[\"Return\", \"rf\", \"mktrf\"]]\n",
    "    .rolling(5, min_periods=3).sum().groupby(\"CIK\").shift(-2)\n",
    ")\n",
    "\n",
    "Beta_df[\"CAR_2\"] = (\n",
    "    (components[\"Return\"] - components[\"rf\"]).values - \n",
    "    Beta_df.groupby(\"CIK\")[\"Beta_126\"].shift(10).values * components[\"mktrf\"].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "Beta_df.reset_index(inplace=True)\n",
    "Beta_df.dropna(subset='Beta_63', inplace=True)\n",
    "Beta_df.drop(\n",
    "    columns=[\n",
    "        'Return', 'mktrf', 'smb', 'hml', 'rf', 'ex_Return', 'mktrf_var_63', 'covar_63', \n",
    "        'mktrf_var_126', 'covar_126', 'mktrf_var_252', 'covar_252', 'AR'\n",
    "    ],\n",
    "    inplace=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datadate          0\n",
       "CIK               0\n",
       "Beta_63           0\n",
       "Beta_126     424026\n",
       "Beta_252    1249182\n",
       "CAR_2        582126\n",
       "CAR_5        621596\n",
       "CAR_10       687307\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Beta_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "Beta_df.to_csv(\"Data\\Beta_AR.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "market_idx = pd.read_csv('Data\\market_daily.csv')\n",
    "\n",
    "market_idx[\"DATE\"] = pd.to_datetime(market_idx[\"DATE\"])\n",
    "market_idx.set_index(\"DATE\", inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "vwretd > Value-Weighted Return (includes distributions)\\\n",
    "vwretx > Value-Weighted Return (excluding dividends)\\\n",
    "ewretd > Equal-Weighted Return (includes distributions)\\\n",
    "ewretx > Equal-Weighted Return (excluding dividends)\\\n",
    "totval > Total Market Value\\\n",
    "totcnt > Total Market Count\\\n",
    "usdval > Market Value of Securities Used\\\n",
    "usdcnt > Count of Securities Used\\\n",
    "sprtrn > Return on S&P Composite Index\\\n",
    "spindx > Level on S&P Composite Index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fama French Beta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_beta(data, N):\n",
    "    betas = []\n",
    "    for date in data.index:\n",
    "        start = date - pd.Timedelta(days=N)\n",
    "        if start >= data.index.min():\n",
    "            Y = data.loc[start:date, 'ex_Return']\n",
    "            X = data.loc[start:date, ['mktrf', 'smb', 'hml']]\n",
    "            X = sm.add_constant(X)\n",
    "            model = sm.OLS(Y, X).fit()\n",
    "            betas.append(model.params[['mktrf', 'smb', 'hml']])\n",
    "    return betas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "B_data = Beta_df[Beta_df['CIK']==1750]\n",
    "betas = calc_beta(data=B_data, N=182)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Beta_list = []\n",
    "n = 182\n",
    "# Run the rolling regression for each security\n",
    "for cik in tqdm(Beta_df['CIK'].unique()):\n",
    "    B_data = Beta_df[Beta_df['CIK']==cik]\n",
    "    betas = calc_beta(data=B_data, N=n)\n",
    "    df = pd.DataFrame(betas, index=B_data.index[-len(betas):])\n",
    "    df[\"CIK\"] = cik\n",
    "    Beta_list.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "FF_Betas = pd.concat(Beta_list).reset_index().rename(columns={'mktrf': 'B_mktrf', 'smb': 'B_smb', 'hml': 'B_hml'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "FF_Betas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean WRDS prices dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\u0147656\\AppData\\Local\\Temp\\ipykernel_13700\\4091792494.py:2: DtypeWarning: Columns (1,4,13) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  prices_df=pd.read_csv(\"WRDS_prices.csv\", usecols=['iid', 'datadate', 'cusip', 'ajexdi', 'cshoc', 'cshtrd', 'prccd', 'tpci', 'cik'])\n"
     ]
    }
   ],
   "source": [
    "prices_df=pd.read_csv(\"WRDS_prices.csv\", usecols=['iid', 'datadate', 'cusip', 'ajexdi', 'cshoc', 'cshtrd', 'prccd', 'tpci', 'cik'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(87057203, 9)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prices_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cikmap = pd.read_csv('Data/CIK_Ticker_CUSIP.csv', usecols=['cik', 'cusip']).dropna()\n",
    "cikmap['cusip'] = cikmap['cusip'].str.split()\n",
    "cikmap = cikmap.explode('cusip')\n",
    "prices_df = pd.merge(\n",
    "    left=prices_df,\n",
    "    right=cikmap,\n",
    "    on='cusip',\n",
    "    how='left',\n",
    "    suffixes=(\"\", \"_2\")\n",
    ")\n",
    "prices_df.fillna({'cik': prices_df['cik_2']}, inplace=True)\n",
    "prices_df.dropna(subset='cik', inplace=True)\n",
    "prices_df.drop(columns=['cik_2', 'cusip'], inplace=True)\n",
    "prices_df['cik'] = prices_df['cik'].astype(int)\n",
    "\n",
    "prices_df.dropna(subset='prccd', inplace=True)\n",
    "prices_df['CLOSEPRICE'] = prices_df['prccd']/prices_df['ajexdi']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# keep CIKs that are in disclosure database\n",
    "CIKs = pd.read_csv(\n",
    "    filepath_or_buffer=\"Data/All_1Afiles.csv\", \n",
    "    usecols=['CIK']).drop_duplicates()\n",
    "\n",
    "prices_df = prices_df[prices_df['cik'].isin(CIKs['CIK'].unique())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop ciks with multiple issues\n",
    "prices_df = prices_df.sort_values(['cik', 'datadate', 'tpci', 'iid']).reset_index(drop=True)\n",
    "prices_df = prices_df[(prices_df['tpci'].isin(['0', 0, '1', 1]))&(prices_df['cshtrd']>0)]\n",
    "prices_df['cshoc'] = prices_df.groupby(['cik', 'tpci'])['cshoc'].ffill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "prices_df = prices_df.groupby(['cik', 'datadate']).agg(\n",
    "    VOLUME=('cshtrd', 'sum'),\n",
    "    TTLCMNSHARESOUT=('cshoc', 'sum'),\n",
    "    CLOSEPRICE=('CLOSEPRICE', 'mean')\n",
    ").reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "prices_df.to_csv(\"Data/WRDS_prices.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_beta(B_data):\n",
    "    # Define the regression formula\n",
    "    FamaFrench_model = smf.ols(formula='ex_Return ~ mktrf + smb + hml', data=B_data)\n",
    "\n",
    "    # Fit the regression\n",
    "    FamaFrench_fit = FamaFrench_model.fit()\n",
    "\n",
    "    return FamaFrench_fit.params['mktrf']\n",
    "\n",
    "calc_beta(Beta_df[Beta_df['CIK']==1750])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_beta(data, N):\n",
    "    betas = []\n",
    "    for start in range(len(data) - N + 1):\n",
    "        end = start + N\n",
    "        Y = data.iloc[start:end, 4]\n",
    "        X = data.iloc[start:end, 1:4]\n",
    "        X = sm.add_constant(X)\n",
    "        model = sm.OLS(Y, X).fit()\n",
    "        betas.append(model.params[['mktrf', 'smb', 'hml']])\n",
    "    return betas"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ETM_env",
   "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.14"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "2e814d5f901f2e2f1812217256e7a2386c6b3dac449e52472436b21a420d1617"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}