notebooks/dbg-stock-clustering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clustering Similar Stocks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, we attempt to find similar stocks. A technique such as this would be useful for:\n",
    "\n",
    "- finding stocks that behave similarly (or dissimilarly) to one of interest\n",
    "- building trading strategies\n",
    "- identifying anomalies (e.g. if two stocks are normally correlated but fall out of line in a particular day, you might want to investigate)\n",
    "- discarding bad stocks (if stocks do not correlate with other stocks they might need investigating).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install hdbscan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import io\n",
    "import s3fs\n",
    "import boto3\n",
    "import sagemaker\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.decomposition import TruncatedSVD\n",
    "from sklearn.manifold import TSNE\n",
    "import hdbscan\n",
    "import time\n",
    "import seaborn as sns\n",
    "import collections\n",
    "%matplotlib inline\n",
    "mpl.rcParams['figure.figsize'] = (5, 3) # use bigger graphs\n",
    "interval = \"D\"\n",
    "role = sagemaker.get_execution_role()\n",
    "session = sagemaker.Session()\n",
    "s3_data_key = 'dbg-stockdata/source'\n",
    "s3_bucket = session.default_bucket()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we load the data resampled at daily interval, from the S3 bucket location that we saved in the data preparation notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "def date_part(dt):\n",
    "    return str(dt).split(' ')[0]\n",
    "\n",
    "def load_resampled_from_s3(interval, bucket, s3_data_key):\n",
    "    s3 = boto3.client('s3')\n",
    "    obj = s3.get_object(Bucket=bucket, Key=\"{}/{}/resampled_stockdata.csv\".format(s3_data_key, interval))\n",
    "    loaded = pd.read_csv(io.BytesIO(obj['Body'].read()), index_col=0, parse_dates=True)\n",
    "    mnemonics = list(loaded.Mnemonic.unique())   \n",
    "    unique_days = sorted(list(set(map(date_part , list(loaded.index.unique())))))\n",
    "    return loaded, mnemonics, unique_days\n",
    "\n",
    "interval = \"D\"\n",
    "stockdata, stocksymbols, unique_days = load_resampled_from_s3(interval, s3_bucket, s3_data_key)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, in order to visualize in the plot with meaningful names for stock symbols, we refer to this list, as provided by Deutsche Borse, that maps the mnemonics to company names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mnemonic_names = {\n",
    "    '1COV': 'COVESTRO AG  O.N.',\n",
    "     '3W9K': '3W POWER S.A. EO -,01',\n",
    "     'AB1': 'AIR BERLIN PLC    EO -,25',\n",
    "     'ADS': 'ADIDAS AG NA O.N.',\n",
    "     'ADV': 'ADVA OPT.NETW.SE  O.N.',\n",
    "     'AIXA': 'AIXTRON SE NA O.N.',\n",
    "     'ALV': 'ALLIANZ SE NA O.N.',\n",
    "     'AOX': 'ALSTRIA OFFICE REIT-AG',\n",
    "     'ARL': 'AAREAL BANK AG',\n",
    "     'AT1': 'AROUNDTOWN EO-,01',\n",
    "     'B4B': 'METRO AG   ST O.N.',\n",
    "     'BAS': 'BASF SE NA O.N.',\n",
    "     'BAYN': 'BAYER AG NA O.N.',\n",
    "     'BEI': 'BEIERSDORF AG O.N.',\n",
    "     'BMW': 'BAY.MOTOREN WERKE AG ST',\n",
    "     'BNR': 'BRENNTAG AG NA O.N.',\n",
    "     'BOSS': 'HUGO BOSS AG NA O.N.',\n",
    "     'BPE5': 'BP PLC             DL-,25',\n",
    "     'BVB': 'BORUSSIA DORTMUND',\n",
    "     'CAP': 'ENCAVIS AG  INH. O.N.',\n",
    "     'CBK': 'COMMERZBANK AG',\n",
    "     'CEC': 'CECONOMY AG  ST O.N.',\n",
    "     'CON': 'CONTINENTAL AG O.N.',\n",
    "     'DAI': 'DAIMLER AG NA O.N.',\n",
    "     'DB1': 'DEUTSCHE BOERSE NA O.N.',\n",
    "     'DBK': 'DEUTSCHE BANK AG NA O.N.',\n",
    "     'DEQ': 'DEUTSCHE EUROSHOP',\n",
    "     'DEZ': 'DEUTZ AG O.N.',\n",
    "     'DHER': 'DELIVERY HERO',\n",
    "     'DLG': 'DIALOG SEMICOND.   LS-,10',\n",
    "     'DPW': 'DEUTSCHE POST AG NA O.N.',\n",
    "     'DRI': '1+1 DRILLISCH AG O.N.',\n",
    "     'DTE': 'DT.TELEKOM AG NA',\n",
    "     'DWNI': 'DEUTSCHE WOHNEN SE INH',\n",
    "     'EOAN': 'E.ON SE NA O.N.',\n",
    "     'EVK': 'EVONIK INDUSTRIES NA O.N.',\n",
    "     'EVT': 'EVOTEC AG O.N.',\n",
    "     'FME': 'FRESEN.MED.CARE KGAA O.N.',\n",
    "     'FNTN': 'FREENET AG NA O.N.',\n",
    "     'FRE': 'FRESENIUS SE+CO.KGAA O.N.',\n",
    "     'G1A': 'GEA GROUP AG',\n",
    "     'GAZ': 'GAZPROM ADR SP./2 RL 5L 5',\n",
    "     'GYC': 'GRAND CITY PROPERT.EO-,10',\n",
    "     'HDD': 'HEIDELBERG.DRUCKMA.O.N.',\n",
    "     'HEI': 'HEIDELBERGCEMENT AG O.N.',\n",
    "     'HEN3': 'HENKEL AG+CO.KGAA VZO',\n",
    "     'IFX': 'INFINEON TECH.AG NA O.N.',\n",
    "     'IGY': 'INNOGY SE  INH. O.N.',\n",
    "     'KCO': 'KLOECKNER + CO SE NA O.N.',\n",
    "     'KGX': 'KION GROUP AG',\n",
    "     'LEO': 'DREYFUS STRATEGIC MUNI',\n",
    "     'LHA': ',LUFTHANSA AG VNA O.N.',\n",
    "     'LIN': 'LINDE AG O.N.',\n",
    "     'LINU': 'LINDE AG O.N. Z.UMT.',\n",
    "     'LLD': 'LLOYDS BKG GRP     LS-,10',\n",
    "     'LXS': 'LANXESS AG',\n",
    "     'MDG1': 'MEDIGENE AG NA O.N.',\n",
    "     'MRK': 'MERCK KGAA O.N.',\n",
    "     'MUV2': 'MUENCH.RUECKVERS.VNA O.N.',\n",
    "     'NDA': 'AURUBIS AG',\n",
    "     'NDX1': 'NORDEX SE O.N.',\n",
    "     'NOA3': 'NOKIA OYJ EO-,06',\n",
    "     'O2D': 'TELEFONICA DTLD HLDG NA',\n",
    "     'OSR': 'OSRAM LICHT AG NA O.N.',\n",
    "     'PAH3': 'PORSCHE AUTOM.HLDG VZO',\n",
    "     'PBB': 'DT.PFANDBRIEFBK AG',\n",
    "     'PNE3': 'PNE WIND AG NA O.N.',\n",
    "     'PSM': 'PROSIEBENSAT.1  NA O.N.',\n",
    "     'QIA': 'QIAGEN NV         EO -,01',\n",
    "     'QSC': 'QSC AG NA O.N.',\n",
    "     'RIB': 'RIB SOFTWARE SE  NA EO 1',\n",
    "     'RKET': 'ROCKET INTERNET SE',\n",
    "     'RWE': 'RWE AG ST O.N.',\n",
    "     'SANT': 'S+T AG (Z.REG.MK.Z.)O.N.',\n",
    "     'SAP': 'SAP SE O.N.',\n",
    "     'SDF': 'K+S AG NA O.N.',\n",
    "     'SGL': 'SGL CARBON SE O.N.',\n",
    "     'SHA': 'SCHAEFFLER AG INH. VZO',\n",
    "     'SHL': 'SIEMENS HEALTH.AG NA O.N.',\n",
    "     'SIE': 'SIEMENS AG NA',\n",
    "     'SNH': 'STEINHOFF INT.HLDG.EO-,50',\n",
    "     'SOW': 'SOFTWARE AG  NA O.N.',\n",
    "     'SVAB': 'STOCKHOLM IT VENTURES AB',\n",
    "     'SY1': 'SYMRISE AG INH. O.N.',\n",
    "     'SZG': 'SALZGITTER AG O.N.',\n",
    "     'SZU': 'SUEDZUCKER AG  O.N.',\n",
    "     'TC1': 'TELE COLUMBUS',\n",
    "     'TEG': 'TAG IMMOBILIEN AG',\n",
    "     'TKA': 'THYSSENKRUPP AG O.N.',\n",
    "     'TTI': 'TOM TAILOR HLDG  NA O.N.',\n",
    "     'TUI1': 'TUI AG NA O.N.',\n",
    "     'UN01': 'UNIPER SE  NA O.N.',\n",
    "     'USE': 'BEATE UHSE AG',\n",
    "     'UTDI': 'UTD.INTERNET AG NA',\n",
    "     'VNA': 'VONOVIA SE NA O.N.',\n",
    "     'VODI': 'VODAFONE GROUP PLC',\n",
    "     'VOW3': 'VOLKSWAGEN AG VZO O.N.',\n",
    "     'WAF': 'SILTRONIC AG NA O.N.',\n",
    "     'WDI': 'WIRECARD AG',\n",
    "     'ZAL': 'ZALANDO SE',\n",
    "     'ZIL2': 'ELRINGKLINGER AG NA O.N.',\n",
    "     'TINA': 'TINA',\n",
    "     'ANO': 'ANO',\n",
    "     'ARO': 'ARO'                   \n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Methodology\n",
    "\n",
    "1. Select a time frame within which to analyze the stocks (e.g. 60 days).\n",
    "- Select an interval within which to aggregate the prices (e.g. 1 day).\n",
    "- Select a function of the price such as percent change or log returns.\n",
    "- Select a similarity function between the timeseries, such as dot product, cosine or correlation coefficient.\n",
    "- Select a clustering algorithm.\n",
    "- Visualize the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def prepare_single_stock(df, mnemonic, interval):\n",
    "    single_stock = df[df.Mnemonic == mnemonic].copy()\n",
    "    single_stock['Avg4Price'] = 0.25*(single_stock['MaxPrice'] + single_stock['MinPrice'] + \n",
    "                                      single_stock['StartPrice'] + single_stock['EndPrice'])\n",
    "    \n",
    "    resampled = pd.DataFrame({\n",
    "        'MeanAvg4Price': single_stock['Avg4Price'].resample(interval).mean(),          \n",
    "        'Mnemonic': mnemonic,\n",
    "    })\n",
    "    resampled['PctChange'] = resampled['MeanAvg4Price'].pct_change().fillna(0.0)\n",
    "    return resampled[['Mnemonic', 'PctChange', 'MeanAvg4Price']]\n",
    "\n",
    "selected_days = unique_days[0:60]\n",
    "subset_df = stockdata[stockdata.index.isin(list(selected_days))]\n",
    "mnemonics = subset_df['Mnemonic'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "single_stocks_dfs = []\n",
    "\n",
    "interval = 'D'\n",
    "\n",
    "for mnemonic in mnemonics:\n",
    "    single_stock = prepare_single_stock(subset_df, mnemonic, interval)\n",
    "    single_stocks_dfs.append(single_stock)\n",
    "\n",
    "# the dataframe for clustering    \n",
    "clustering_df = pd.concat(single_stocks_dfs, axis=0)\n",
    "clustering_df['CalcDateTime'] = clustering_df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cluster_by_feature = 'PctChange' \n",
    "subset = clustering_df.pivot(index='CalcDateTime', columns='Mnemonic', values=cluster_by_feature)\n",
    "corr_mat = subset.corr().fillna(0.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_most_correlated(corr_mat, mnemonic, n=10):\n",
    "    results = corr_mat[[mnemonic]].sort_values(mnemonic, ascending=False).head(n).copy()\n",
    "\n",
    "    results['Desc'] = list(map(lambda m: mnemonic_names[m], list(results.index)))\n",
    "\n",
    "    results['Corr'] = results[mnemonic]\n",
    "    \n",
    "    return results[['Desc', 'Corr']]\n",
    "\n",
    "find_most_correlated(corr_mat, 'BMW')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Cluster:\n",
    "    def __init__(self, cluster_id, members):\n",
    "        self.cluster_id = cluster_id\n",
    "        self.members = members\n",
    "        \n",
    "    def __repr__(self):\n",
    "        printstr = \"\\nCluster {}:\".format(self.cluster_id+2)\n",
    "        for mem in self.members:\n",
    "            printstr = printstr + \"\\n\\t{}\".format(mem)\n",
    "        return  printstr\n",
    "        \n",
    "def build_clusters(data, algorithm, args, kwds, names):\n",
    "    membership_labels = algorithm(*args, **kwds).fit_predict(data)\n",
    "    d = collections.defaultdict(list)\n",
    "    i = 0\n",
    "    for label in membership_labels:\n",
    "        d[label].append(names[i])\n",
    "        i += 1\n",
    "    clusters = []\n",
    "    for k,v in d.items():\n",
    "        clusters.append(Cluster(k, v))\n",
    "    clusters.sort(key=lambda x: x.cluster_id)\n",
    "\n",
    "    return membership_labels, clusters\n",
    "\n",
    "\n",
    "friendly_labels = []\n",
    "\n",
    "def truncate_str(v):\n",
    "    t = 12\n",
    "    if len(v) <= t:\n",
    "        return v\n",
    "    return v[0:10] + \"...\"\n",
    "\n",
    "for m in list(corr_mat.index):\n",
    "    friendly_labels.append(\"{}({})\".format(m, mnemonic_names[m]))\n",
    "   \n",
    "membership_labels, clusters = build_clusters(corr_mat, hdbscan.HDBSCAN, (), {'min_cluster_size':2}, friendly_labels)\n",
    "print(clusters)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Although the result of clustering depends on the time period in which the clustering was done, the discretization interval and similarity function chosen, in general you should see somewhat similar stocks clustered together.\n",
    "\n",
    "For example, automobile companies such as these are cluetered together:\n",
    "\n",
    "    BMW (BMW)\n",
    "    Daimler (DAI)\n",
    "    Porshe (PAH3)\n",
    "    Continental (CON)\n",
    "    Volkswagen (VOW3)\n",
    "\n",
    "Also telecommunication companies are clutered together:\n",
    "\n",
    "    Nokia (NOA3)\n",
    "    Vodafone (VODI)\n",
    "    Telefonica (O2D)\n",
    "    Deutche Telecom (DTE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "mpl.rcParams['figure.figsize'] = (25, 16) # use bigger graphs\n",
    "\n",
    "model = TSNE(n_components=2, perplexity=25, verbose=2, random_state=686861).fit_transform(corr_mat)\n",
    "\n",
    "x_axis=model[:,0]\n",
    "y_axis=model[:,1]\n",
    "x_norm = (x_axis-np.min(x_axis)) / (np.max(x_axis) - np.min(x_axis))\n",
    "y_norm = (y_axis-np.min(y_axis)) / (np.max(y_axis) - np.min(y_axis))\n",
    "\n",
    "fig, ax = plt.subplots()\n",
    "palette = sns.color_palette('deep', np.unique(membership_labels).max() + 1)\n",
    "colors = [palette[x] if x >= 0 else (0.0, 0.0, 0.0) for x in membership_labels]\n",
    "    \n",
    "ax.scatter(x_norm, y_norm, c = colors)\n",
    "names = list(corr_mat.index)\n",
    "\n",
    "for i, name in enumerate(names):\n",
    "    ax.annotate(truncate_str(mnemonic_names[name]), (x_norm[i],y_norm[i]))    \n",
    "    \n",
    "    \n",
    "fig.savefig('stockclusters.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Symbols plotted graphically visually depicts, where you can find some of the prominent clusters, as described above."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}