07b-another-backtest.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "The only changes in this code relative to the second backtest are\n",
    "\n",
    "(1) definition of the pipeline using OneHotEncoder in the first cell\n",
    "\n",
    "(2) the two cells \"Add industry variable ...\" and \"Add deviations ...\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pymssql\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.preprocessing import QuantileTransformer\n",
    "from sklearn.preprocessing import PolynomialFeatures\n",
    "from sklearn.compose import make_column_transformer\n",
    "from sklearn.preprocessing import OneHotEncoder\n",
    "from sklearn.pipeline import make_pipeline\n",
    "\n",
    "transform = make_column_transformer(\n",
    "    (OneHotEncoder(), [\"industry\"]),\n",
    "    remainder=\"passthrough\"\n",
    ")\n",
    "\n",
    "model = LinearRegression(fit_intercept=False)\n",
    "qt = QuantileTransformer(output_distribution=\"normal\")\n",
    "poly = PolynomialFeatures(degree=2, include_bias=False)\n",
    "pipe = make_pipeline(\n",
    "    transform,\n",
    "    poly,\n",
    "    model\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "server = \"mssql-82792-0.cloudclusters.net:16272\"\n",
    "username = \"user\"\n",
    "password = \"RiceOwls1912\" \n",
    "dfbase = \"ghz\"\n",
    "string = \"mssql+pymssql://\" + username + \":\" + password + \"@\" + server + \"/\" + dfbase\n",
    "conn = create_engine(string).connect()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql(\n",
    "    \"\"\"\n",
    "    select ticker, date, ret, roeq, bm, mom12m, mve, siccd\n",
    "    from data\n",
    "    where date>='2000-01'\n",
    "    order by date, ticker\n",
    "    \"\"\", \n",
    "    conn\n",
    ")\n",
    "df = df.dropna()\n",
    "conn.close()\n",
    "\n",
    "features = [\"roeq\", \"bm\", \"mom12m\"]\n",
    "df = df.set_index([\"date\", \"ticker\"])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Drop largest 500 stocks each month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"size_rnk\"] = df.groupby(\"date\").mve.rank(ascending=False)\n",
    "df = df[df.size_rnk>500]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Transform features each month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def qt_df(d):\n",
    "    x = qt.fit_transform(d)\n",
    "    return pd.DataFrame(x, columns=d.columns, index=d.index)\n",
    "\n",
    "df[features] = df.groupby(\"date\", group_keys=False)[features].apply(qt_df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Transform target each month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def qt_ser(s):\n",
    "    x = s.copy()\n",
    "    x = x.to_numpy().reshape(-1, 1)\n",
    "    x = qt.fit_transform(x).flatten()\n",
    "    return pd.Series(x, index=s.index)\n",
    "\n",
    "df[\"target\"] = df.groupby(\"date\", group_keys=False).ret.apply(qt_ser)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add industry variable to dataframe\n",
    "\n",
    "The following code can be used in \"rank and trade\" to make predictions by downloading siccd from the today table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "inds = pd.read_csv(\"files/siccodes12.csv\", index_col=\"industry\")\n",
    "ind_names = inds.index.unique().to_list()\n",
    "\n",
    "def industry(sic):\n",
    "  try:\n",
    "    return inds[(inds.start<=sic)&(sic<=inds.end)].index[0]\n",
    "  except:\n",
    "    return \"Other\"\n",
    "    \n",
    "siccds = df.groupby(\"ticker\").siccd.last()\n",
    "siccds = pd.DataFrame(siccds)\n",
    "siccds[\"industry\"] = siccds.siccd.map(industry)\n",
    "siccds = siccds.drop(columns = \"siccd\")\n",
    "\n",
    "df = df.reset_index().merge(siccds, on=\"ticker\", how=\"left\").set_index([\"date\", \"ticker\"])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add deviations from industry medians"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in features:\n",
    "    mn = df.reset_index().groupby(['date', 'industry'])[x].median()\n",
    "    mn.name = x+\"_mn\"\n",
    "    df = df.reset_index().merge(mn, on=[\"date\", \"industry\"]).set_index([\"date\", \"ticker\"])\n",
    "    df[x+\"_dev\"] = df[x] - df[x+\"_mn\"]\n",
    "\n",
    "features = features + [x+\"_dev\" for x in features] \n",
    "features.append(\"industry\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Train and predict in a loop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# features.append(\"industry\")\n",
    "predictions = None\n",
    "\n",
    "dates = [\"2005-01\", \"2010-01\", \"2015-01\", \"2020-01\", \"3000-01\"]\n",
    "for train_date, end_date in zip(dates[:-1], dates[1:]):\n",
    "\n",
    "    filter1 = df.index.get_level_values(\"date\") < train_date\n",
    "    filter2 = df.index.get_level_values(\"date\") < end_date\n",
    "\n",
    "    train = df[filter1]\n",
    "    test = df[~filter1 & filter2]\n",
    "\n",
    "    Xtrain = train[features]\n",
    "    ytrain = train[\"target\"]\n",
    "    Xtest = test[features]\n",
    "\n",
    "    pipe.fit(Xtrain, ytrain)\n",
    "    pred = pipe.predict(Xtest)\n",
    "    pred = pd.Series(pred, index=test.index)\n",
    "    predictions = pd.concat((predictions, pred))\n",
    "\n",
    "df[\"predict\"] = predictions"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compute returns of portfolios of best and worst stocks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna(subset=[\"predict\"])\n",
    "\n",
    "numstocks = 200\n",
    "\n",
    "df[\"rnk\"] = df.groupby(\"date\").predict.rank(method=\"first\", ascending=False)\n",
    "best = df[df.rnk<=numstocks]\n",
    "\n",
    "df[\"rnk\"] = df.groupby(\"date\").predict.rank(method=\"first\")\n",
    "worst = df[df.rnk<=numstocks]\n",
    "\n",
    "best_rets = best.groupby(\"date\").ret.mean()\n",
    "worst_rets = worst.groupby(\"date\").ret.mean()\n",
    "rets = pd.concat((best_rets, worst_rets), axis=1)\n",
    "rets.columns = [\"best\", \"worst\"]\n",
    "       \n",
    "rets.to_csv(\"files/rets_another_backtest.csv\")\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "a98f34b6005ae2330f135078450db511ef8910ecc790235ef0560aa88aa6ce12"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}