AFML 14.1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Backtest Statistics\n",
    "\n",
    "Most of the functions below can be found under:\n",
    "\n",
    "* Backtest/stats_measure\n",
    "* Sample_data/make_data\n",
    "\n",
    "A quick slideshow: [Seven Sins of Backtesting](https://newyork.qwafafew.org/wp-content/uploads/sites/4/2015/10/Luo_20150128.pdf)\n",
    "\n",
    "If you are keen on generating synthetic data for your research, copy the code snippets [Generate synthetic raw data](https://gist.github.com/boyboi86/5e00faf48f60abfdbe838fbdee269471) in my gist.\n",
    "\n",
    "[Additional resource](https://en.wikipedia.org/wiki/Euler%E2%80%93Mascheroni_constant)\n",
    "\n",
    "Contact: boyboi86@gmail.com"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Num of CPU core:  4\n",
      "Machine info:  Windows-10-10.0.18362-SP0\n",
      "Python 3.7.4 (default, Aug  9 2019, 18:34:13) [MSC v.1915 64 bit (AMD64)]\n",
      "Numpy 1.18.5\n",
      "Pandas 1.0.4\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import research as rs\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sample Portfolio Construct:\n",
      "=======================================================\n",
      "Equity label: 1773\n",
      "Bond label: 227\n",
      "Equity to Debt Ratio: 7.8106\n",
      "\n",
      "Junk bond (Below BBB-grade): 11.35 %\n",
      "Dividend equity: 88.65 %\n"
     ]
    }
   ],
   "source": [
    "portfolio_mtx = rs.create_portfolio(price = [95, 1000], \n",
    "                                 position = [1000, 10000], \n",
    "                                 n_sample = 2000, \n",
    "                                 imbalance = True)\n",
    "\n",
    "# this is a dividend based investor.."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**\n",
    "\n",
    "The below is our entire portfolio universe, expressed in matrix format.\n",
    "\n",
    "Sythetic portfolio assumptions:\n",
    "* In this portfolio, there are 2,000 assets (Label 0 = equity, label 1 = bond).\n",
    "* There can only be 2 kind of assets (You may modify the code to include more).\n",
    "* All long positions, however if you are doing both long/ short style investing. Just change sign of open position.\n",
    "* Guaranteed to have dividend or interest.\n",
    "* Clean price are meant for bond assets, for equity just refer to open/ close price.\n",
    "* Formula for bond yield:\n",
    "\n",
    "        Accured interest = dirty close - clean close\n",
    "        \n",
    "* Position size will not turn from negative to positive and vice-versa (No flipping).\n",
    "* Open price can be replaced with average transacted price, if you were executing average cost pricing.\n",
    "* If average cost pricing method is employed, only account for single direction transaction.\n",
    "* Same asset/ securities but different direction will be considered in a seperate row.\n",
    "* Each position can only reflect 1 direction throughout that period.\n",
    "* Datetime Index are beginning period, t1 is the ending period.\n",
    "* The below are expressed in single unit of asset:\n",
    "    1. Open\n",
    "    2. Close\n",
    "    3. Yield\n",
    "    4. Clean Close\n",
    "    5. Clean Open\n",
    "\n",
    "\n",
    "* Junk bonds and dividend equities are expressed in percentage of entire portfolio.\n",
    "* Clean and dirty price is unique to only bonds (Label = 1).\n",
    "* Yield is expressed as a nominal term (The portfolio generator based on close price).\n",
    "* If profit/ loss made in sale/ purchase from long/ short position will be reflected in yield.\n",
    "* All yield must be positive, the function will reflect short sales.\n",
    "* Expense represents all costs incurred that is due to asset during sub-period.\n",
    "* Expense is expressed in nominal terms, which consist:\n",
    "\n",
    "    1. Taxes\n",
    "    2. Transaction fees\n",
    "    3. Out-of-pockets\n",
    "    4. Custodian charges\n",
    "    5. Margin Facilities\n",
    "    6. Any misc charges/ expenses\n",
    "    \n",
    "    \n",
    "**Note**\n",
    "\n",
    "If you are shorting instead, it will not increase your AUM but it will increase profit/ loss.\n",
    "\n",
    "**Note**\n",
    "\n",
    "This code snippet is not from the book. But I feel that it's necessary for me since I do not have S&P 500, to finish this tutorial.\n",
    "\n",
    "Therefore, if you are keen to use any code snippets, please go through them before using it. \n",
    "\n",
    "At the same time, if you feel that you have a better way to run this algo, let me know."
   ]
  },
  {
   "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>t1</th>\n",
       "      <th>open</th>\n",
       "      <th>close</th>\n",
       "      <th>open_pos</th>\n",
       "      <th>close_pos</th>\n",
       "      <th>yield</th>\n",
       "      <th>expense</th>\n",
       "      <th>label</th>\n",
       "      <th>clean_open</th>\n",
       "      <th>clean_close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-10-18 16:48:42.537666</th>\n",
       "      <td>2012-10-23 20:36:38.274374</td>\n",
       "      <td>712.341641</td>\n",
       "      <td>591.554442</td>\n",
       "      <td>4914.0</td>\n",
       "      <td>3205.0</td>\n",
       "      <td>39.150070</td>\n",
       "      <td>33.009637</td>\n",
       "      <td>0.0</td>\n",
       "      <td>664.491780</td>\n",
       "      <td>552.404372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-19 16:48:42.537666</th>\n",
       "      <td>2012-10-26 12:09:08.160024</td>\n",
       "      <td>237.697665</td>\n",
       "      <td>198.375014</td>\n",
       "      <td>9161.0</td>\n",
       "      <td>6750.0</td>\n",
       "      <td>10.015237</td>\n",
       "      <td>6.405694</td>\n",
       "      <td>0.0</td>\n",
       "      <td>224.334496</td>\n",
       "      <td>188.359777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-22 16:48:42.537666</th>\n",
       "      <td>2012-10-30 18:47:16.978825</td>\n",
       "      <td>337.199346</td>\n",
       "      <td>384.569274</td>\n",
       "      <td>1381.0</td>\n",
       "      <td>3611.0</td>\n",
       "      <td>26.156680</td>\n",
       "      <td>13.082456</td>\n",
       "      <td>0.0</td>\n",
       "      <td>319.682111</td>\n",
       "      <td>358.412593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-23 16:48:42.537666</th>\n",
       "      <td>2012-10-30 23:42:13.197475</td>\n",
       "      <td>498.166803</td>\n",
       "      <td>569.613622</td>\n",
       "      <td>6083.0</td>\n",
       "      <td>9891.0</td>\n",
       "      <td>21.248672</td>\n",
       "      <td>4.134028</td>\n",
       "      <td>0.0</td>\n",
       "      <td>459.372735</td>\n",
       "      <td>548.364950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 16:48:42.537666</th>\n",
       "      <td>2012-11-03 00:21:43.647673</td>\n",
       "      <td>377.400237</td>\n",
       "      <td>328.747226</td>\n",
       "      <td>4013.0</td>\n",
       "      <td>2955.0</td>\n",
       "      <td>17.097073</td>\n",
       "      <td>18.412937</td>\n",
       "      <td>0.0</td>\n",
       "      <td>356.027994</td>\n",
       "      <td>311.650153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-11 16:48:42.537666</th>\n",
       "      <td>2020-06-12 23:06:59.648676</td>\n",
       "      <td>123.455649</td>\n",
       "      <td>136.305746</td>\n",
       "      <td>5323.0</td>\n",
       "      <td>5670.0</td>\n",
       "      <td>6.113479</td>\n",
       "      <td>2.186776</td>\n",
       "      <td>1.0</td>\n",
       "      <td>116.760033</td>\n",
       "      <td>130.192266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-12 16:48:42.537666</th>\n",
       "      <td>2020-06-14 07:59:29.097476</td>\n",
       "      <td>451.172391</td>\n",
       "      <td>427.303924</td>\n",
       "      <td>6690.0</td>\n",
       "      <td>9007.0</td>\n",
       "      <td>49.815237</td>\n",
       "      <td>2.481167</td>\n",
       "      <td>0.0</td>\n",
       "      <td>415.737965</td>\n",
       "      <td>377.488687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-15 16:48:42.537666</th>\n",
       "      <td>2020-06-21 11:50:42.088945</td>\n",
       "      <td>779.196802</td>\n",
       "      <td>898.547842</td>\n",
       "      <td>4260.0</td>\n",
       "      <td>3949.0</td>\n",
       "      <td>54.633637</td>\n",
       "      <td>27.484208</td>\n",
       "      <td>0.0</td>\n",
       "      <td>732.210613</td>\n",
       "      <td>843.914205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-16 16:48:42.537666</th>\n",
       "      <td>2020-06-26 13:02:44.560157</td>\n",
       "      <td>814.751467</td>\n",
       "      <td>812.444718</td>\n",
       "      <td>9597.0</td>\n",
       "      <td>5006.0</td>\n",
       "      <td>27.454042</td>\n",
       "      <td>24.794070</td>\n",
       "      <td>0.0</td>\n",
       "      <td>762.422269</td>\n",
       "      <td>784.990675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-17 16:48:42.537666</th>\n",
       "      <td>2020-06-26 06:45:14.294269</td>\n",
       "      <td>587.205694</td>\n",
       "      <td>559.298560</td>\n",
       "      <td>4923.0</td>\n",
       "      <td>4932.0</td>\n",
       "      <td>46.968234</td>\n",
       "      <td>8.365209</td>\n",
       "      <td>0.0</td>\n",
       "      <td>553.458598</td>\n",
       "      <td>512.330327</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2000 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   t1        open       close  \\\n",
       "2012-10-18 16:48:42.537666 2012-10-23 20:36:38.274374  712.341641  591.554442   \n",
       "2012-10-19 16:48:42.537666 2012-10-26 12:09:08.160024  237.697665  198.375014   \n",
       "2012-10-22 16:48:42.537666 2012-10-30 18:47:16.978825  337.199346  384.569274   \n",
       "2012-10-23 16:48:42.537666 2012-10-30 23:42:13.197475  498.166803  569.613622   \n",
       "2012-10-24 16:48:42.537666 2012-11-03 00:21:43.647673  377.400237  328.747226   \n",
       "...                                               ...         ...         ...   \n",
       "2020-06-11 16:48:42.537666 2020-06-12 23:06:59.648676  123.455649  136.305746   \n",
       "2020-06-12 16:48:42.537666 2020-06-14 07:59:29.097476  451.172391  427.303924   \n",
       "2020-06-15 16:48:42.537666 2020-06-21 11:50:42.088945  779.196802  898.547842   \n",
       "2020-06-16 16:48:42.537666 2020-06-26 13:02:44.560157  814.751467  812.444718   \n",
       "2020-06-17 16:48:42.537666 2020-06-26 06:45:14.294269  587.205694  559.298560   \n",
       "\n",
       "                            open_pos  close_pos      yield    expense  label  \\\n",
       "2012-10-18 16:48:42.537666    4914.0     3205.0  39.150070  33.009637    0.0   \n",
       "2012-10-19 16:48:42.537666    9161.0     6750.0  10.015237   6.405694    0.0   \n",
       "2012-10-22 16:48:42.537666    1381.0     3611.0  26.156680  13.082456    0.0   \n",
       "2012-10-23 16:48:42.537666    6083.0     9891.0  21.248672   4.134028    0.0   \n",
       "2012-10-24 16:48:42.537666    4013.0     2955.0  17.097073  18.412937    0.0   \n",
       "...                              ...        ...        ...        ...    ...   \n",
       "2020-06-11 16:48:42.537666    5323.0     5670.0   6.113479   2.186776    1.0   \n",
       "2020-06-12 16:48:42.537666    6690.0     9007.0  49.815237   2.481167    0.0   \n",
       "2020-06-15 16:48:42.537666    4260.0     3949.0  54.633637  27.484208    0.0   \n",
       "2020-06-16 16:48:42.537666    9597.0     5006.0  27.454042  24.794070    0.0   \n",
       "2020-06-17 16:48:42.537666    4923.0     4932.0  46.968234   8.365209    0.0   \n",
       "\n",
       "                            clean_open  clean_close  \n",
       "2012-10-18 16:48:42.537666  664.491780   552.404372  \n",
       "2012-10-19 16:48:42.537666  224.334496   188.359777  \n",
       "2012-10-22 16:48:42.537666  319.682111   358.412593  \n",
       "2012-10-23 16:48:42.537666  459.372735   548.364950  \n",
       "2012-10-24 16:48:42.537666  356.027994   311.650153  \n",
       "...                                ...          ...  \n",
       "2020-06-11 16:48:42.537666  116.760033   130.192266  \n",
       "2020-06-12 16:48:42.537666  415.737965   377.488687  \n",
       "2020-06-15 16:48:42.537666  732.210613   843.914205  \n",
       "2020-06-16 16:48:42.537666  762.422269   784.990675  \n",
       "2020-06-17 16:48:42.537666  553.458598   512.330327  \n",
       "\n",
       "[2000 rows x 10 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolio_mtx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rtn_by_tw(mtx: pd.DataFrame):\n",
    "    df0 = pd.DataFrame(index=mtx.index).assign(t1 = mtx.t1)\n",
    "    mtx['equity_diff'] = mtx['close'].sub(mtx['open'])\n",
    "    mtx['bond_diff'] = mtx['clean_close'].sub(mtx['clean_open'])\n",
    "    for idx in mtx.index:\n",
    "        _close_pos = mtx.loc[idx, 'close_pos']\n",
    "        _open_pos = mtx.loc[idx, 'open_pos']\n",
    "        if ((_open_pos) > .0 and (_close_pos) >= .0) or ((_open_pos) < .0 and (_close_pos) >= .0):\n",
    "            mtx.loc[idx, 't1_pos'] = max(0, float(_close_pos - _open_pos))\n",
    "            mtx.loc[idx, 't1_vol'] = float(_open_pos - _close_pos)\n",
    "        if ((_open_pos) > .0 and (_close_pos) <= .0) or ((_open_pos) > .0 and (_close_pos) <= .0):\n",
    "            mtx.loc[idx, 't1_pos'] = max(0, float(_close_pos - _open_pos)) #shorting doesn't increase your aum\n",
    "            mtx.loc[idx, 't1_vol'] = float(_open_pos - _close_pos)\n",
    "            mtx[idx, 'yield'].mul(-1) #if you short; you will pay for the div/ interest instead\n",
    "        \n",
    "        #but it does add to your profit.. or losses\n",
    "        if mtx.loc[idx, 'label'] == 0:\n",
    "            mtx.loc[idx, 'pnl'] = (mtx.loc[idx, 'bond_diff'] + mtx.loc[idx, 'yield']) * mtx.loc[idx, 't1_vol']\n",
    "        else:\n",
    "            mtx.loc[idx, 'pnl'] = (mtx.loc[idx, 'equity_diff'] + mtx.loc[idx, 'yield']) * mtx.loc[idx, 't1_vol']\n",
    "            \n",
    "    \n",
    "    t0_val = mtx['close'].mul(mtx['open_pos'])\n",
    "    t1_val = mtx['close'].mul(mtx['t1_pos'])\n",
    "    \n",
    "    df0['rtn'] = mtx['pnl'].sub(mtx['expense']).div(t0_val.add(t1_val))\n",
    "    df0['rtn'].add(1)\n",
    "\n",
    "    return df0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>t1</th>\n",
       "      <th>rtn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-10-18 16:48:42.537666</th>\n",
       "      <td>2012-10-23 20:36:38.274374</td>\n",
       "      <td>-0.042892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-19 16:48:42.537666</th>\n",
       "      <td>2012-10-26 12:09:08.160024</td>\n",
       "      <td>-0.034444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-22 16:48:42.537666</th>\n",
       "      <td>2012-10-30 18:47:16.978825</td>\n",
       "      <td>-0.104208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-23 16:48:42.537666</th>\n",
       "      <td>2012-10-30 23:42:13.197475</td>\n",
       "      <td>-0.074512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 16:48:42.537666</th>\n",
       "      <td>2012-11-03 00:21:43.647673</td>\n",
       "      <td>-0.021892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-11 16:48:42.537666</th>\n",
       "      <td>2020-06-12 23:06:59.648676</td>\n",
       "      <td>-0.008517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-12 16:48:42.537666</th>\n",
       "      <td>2020-06-14 07:59:29.097476</td>\n",
       "      <td>-0.006964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-15 16:48:42.537666</th>\n",
       "      <td>2020-06-21 11:50:42.088945</td>\n",
       "      <td>0.013507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-16 16:48:42.537666</th>\n",
       "      <td>2020-06-26 13:02:44.560157</td>\n",
       "      <td>0.029451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-17 16:48:42.537666</th>\n",
       "      <td>2020-06-26 06:45:14.294269</td>\n",
       "      <td>-0.000022</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2000 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   t1       rtn\n",
       "2012-10-18 16:48:42.537666 2012-10-23 20:36:38.274374 -0.042892\n",
       "2012-10-19 16:48:42.537666 2012-10-26 12:09:08.160024 -0.034444\n",
       "2012-10-22 16:48:42.537666 2012-10-30 18:47:16.978825 -0.104208\n",
       "2012-10-23 16:48:42.537666 2012-10-30 23:42:13.197475 -0.074512\n",
       "2012-10-24 16:48:42.537666 2012-11-03 00:21:43.647673 -0.021892\n",
       "...                                               ...       ...\n",
       "2020-06-11 16:48:42.537666 2020-06-12 23:06:59.648676 -0.008517\n",
       "2020-06-12 16:48:42.537666 2020-06-14 07:59:29.097476 -0.006964\n",
       "2020-06-15 16:48:42.537666 2020-06-21 11:50:42.088945  0.013507\n",
       "2020-06-16 16:48:42.537666 2020-06-26 13:02:44.560157  0.029451\n",
       "2020-06-17 16:48:42.537666 2020-06-26 06:45:14.294269 -0.000022\n",
       "\n",
       "[2000 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = rtn_by_tw(mtx = portfolio_mtx)\n",
    "\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**\n",
    "\n",
    "Based on the above, we will have to run the next function using parallelization, so that we can link these performance geometrically and weight these returns based on time.\n",
    "\n",
    "Otherwise, it may take much longer than expected.\n",
    "\n",
    "If you are interested in parallelization but want some demostration.\n",
    "\n",
    "[AFML 13.1](https://github.com/boyboi86/AFML/blob/master/AFML%2013.1.ipynb)\n",
    "\n",
    "**Note**\n",
    "\n",
    "Running parallel operations for binary integer is significantly faster than float.\n",
    "\n",
    "If required try downcast dtype (Last resort)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[                            0     1     2     3     4     5     6     7     \\\n",
      "2015-05-13 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 10:02:26.269215   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 17:20:26.760815   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 21:08:17.860822   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "...                          ...   ...   ...   ...   ...   ...   ...   ...   \n",
      "2017-11-27 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-28 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-29 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 13:06:22.281616   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "\n",
      "                            8     9     ...  1990  1991  1992  1993  1994  \\\n",
      "2015-05-13 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 10:02:26.269215   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 17:20:26.760815   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 21:08:17.860822   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "...                          ...   ...  ...   ...   ...   ...   ...   ...   \n",
      "2017-11-27 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-28 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-29 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 13:06:22.281616   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "\n",
      "                            1995  1996  1997  1998  1999  \n",
      "2015-05-13 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 10:02:26.269215   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 17:20:26.760815   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 21:08:17.860822   1.0   1.0   1.0   1.0   1.0  \n",
      "...                          ...   ...   ...   ...   ...  \n",
      "2017-11-27 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-28 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-29 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-30 13:06:22.281616   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-30 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "\n",
      "[1333 rows x 2000 columns]] this out\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2020-06-17 16:49:59.654105 33.33% _rtn_by_tw done after 1.18 mins. Remaining 2.35 mins.\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[                            0     1     2     3     4     5     6     7     \\\n",
      "2015-05-13 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 10:02:26.269215   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 17:20:26.760815   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 21:08:17.860822   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "...                          ...   ...   ...   ...   ...   ...   ...   ...   \n",
      "2017-11-27 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-28 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-29 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 13:06:22.281616   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "\n",
      "                            8     9     ...  1990  1991  1992  1993  1994  \\\n",
      "2015-05-13 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 10:02:26.269215   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 17:20:26.760815   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 21:08:17.860822   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "...                          ...   ...  ...   ...   ...   ...   ...   ...   \n",
      "2017-11-27 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-28 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-29 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 13:06:22.281616   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "\n",
      "                            1995  1996  1997  1998  1999  \n",
      "2015-05-13 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 10:02:26.269215   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 17:20:26.760815   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 21:08:17.860822   1.0   1.0   1.0   1.0   1.0  \n",
      "...                          ...   ...   ...   ...   ...  \n",
      "2017-11-27 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-28 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-29 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-30 13:06:22.281616   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-30 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "\n",
      "[1333 rows x 2000 columns],                             0     1     2     3     4     5     6     7     \\\n",
      "2017-12-01 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-02 19:16:28.567377   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-03 06:37:04.836268   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-04 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-05 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "...                          ...   ...   ...   ...   ...   ...   ...   ...   \n",
      "2020-06-16 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-17 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-21 11:50:42.088945   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-26 06:45:14.294269   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-26 13:02:44.560157   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "\n",
      "                            8     9     ...  1990  1991  1992  1993  1994  \\\n",
      "2017-12-01 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-02 19:16:28.567377   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-03 06:37:04.836268   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-04 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-05 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "...                          ...   ...  ...   ...   ...   ...   ...   ...   \n",
      "2020-06-16 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-17 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-21 11:50:42.088945   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-26 06:45:14.294269   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-26 13:02:44.560157   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "\n",
      "                            1995  1996      1997      1998      1999  \n",
      "2017-12-01 16:48:42.537666   1.0   1.0  1.000000  1.000000  1.000000  \n",
      "2017-12-02 19:16:28.567377   1.0   1.0  1.000000  1.000000  1.000000  \n",
      "2017-12-03 06:37:04.836268   1.0   1.0  1.000000  1.000000  1.000000  \n",
      "2017-12-04 16:48:42.537666   1.0   1.0  1.000000  1.000000  1.000000  \n",
      "2017-12-05 16:48:42.537666   1.0   1.0  1.000000  1.000000  1.000000  \n",
      "...                          ...   ...       ...       ...       ...  \n",
      "2020-06-16 16:48:42.537666   1.0   1.0  1.013507  1.029451  1.000000  \n",
      "2020-06-17 16:48:42.537666   1.0   1.0  1.013507  1.029451  0.999978  \n",
      "2020-06-21 11:50:42.088945   1.0   1.0  1.013507  1.029451  0.999978  \n",
      "2020-06-26 06:45:14.294269   1.0   1.0  1.000000  1.029451  0.999978  \n",
      "2020-06-26 13:02:44.560157   1.0   1.0  1.000000  1.029451  1.000000  \n",
      "\n",
      "[1333 rows x 2000 columns]] this out\n",
      "[                            0     1     2     3     4     5     6     7     \\\n",
      "2015-05-13 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 10:02:26.269215   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 17:20:26.760815   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 21:08:17.860822   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "...                          ...   ...   ...   ...   ...   ...   ...   ...   \n",
      "2017-11-27 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-28 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-29 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 13:06:22.281616   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "\n",
      "                            8     9     ...  1990  1991  1992  1993  1994  \\\n",
      "2015-05-13 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 10:02:26.269215   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 17:20:26.760815   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2015-05-14 21:08:17.860822   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "...                          ...   ...  ...   ...   ...   ...   ...   ...   \n",
      "2017-11-27 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-28 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-29 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 13:06:22.281616   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-11-30 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "\n",
      "                            1995  1996  1997  1998  1999  \n",
      "2015-05-13 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 10:02:26.269215   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 17:20:26.760815   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-14 21:08:17.860822   1.0   1.0   1.0   1.0   1.0  \n",
      "...                          ...   ...   ...   ...   ...  \n",
      "2017-11-27 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-28 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-29 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-30 13:06:22.281616   1.0   1.0   1.0   1.0   1.0  \n",
      "2017-11-30 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  \n",
      "\n",
      "[1333 rows x 2000 columns],                             0     1     2     3     4     5     6     7     \\\n",
      "2017-12-01 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-02 19:16:28.567377   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-03 06:37:04.836268   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-04 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-05 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "...                          ...   ...   ...   ...   ...   ...   ...   ...   \n",
      "2020-06-16 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-17 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-21 11:50:42.088945   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-26 06:45:14.294269   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-26 13:02:44.560157   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0   \n",
      "\n",
      "                            8     9     ...  1990  1991  1992  1993  1994  \\\n",
      "2017-12-01 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-02 19:16:28.567377   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-03 06:37:04.836268   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-04 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2017-12-05 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "...                          ...   ...  ...   ...   ...   ...   ...   ...   \n",
      "2020-06-16 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-17 16:48:42.537666   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-21 11:50:42.088945   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-26 06:45:14.294269   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "2020-06-26 13:02:44.560157   1.0   1.0  ...   1.0   1.0   1.0   1.0   1.0   \n",
      "\n",
      "                            1995  1996      1997      1998      1999  \n",
      "2017-12-01 16:48:42.537666   1.0   1.0  1.000000  1.000000  1.000000  \n",
      "2017-12-02 19:16:28.567377   1.0   1.0  1.000000  1.000000  1.000000  \n",
      "2017-12-03 06:37:04.836268   1.0   1.0  1.000000  1.000000  1.000000  \n",
      "2017-12-04 16:48:42.537666   1.0   1.0  1.000000  1.000000  1.000000  \n",
      "2017-12-05 16:48:42.537666   1.0   1.0  1.000000  1.000000  1.000000  \n",
      "...                          ...   ...       ...       ...       ...  \n",
      "2020-06-16 16:48:42.537666   1.0   1.0  1.013507  1.029451  1.000000  \n",
      "2020-06-17 16:48:42.537666   1.0   1.0  1.013507  1.029451  0.999978  \n",
      "2020-06-21 11:50:42.088945   1.0   1.0  1.013507  1.029451  0.999978  \n",
      "2020-06-26 06:45:14.294269   1.0   1.0  1.000000  1.029451  0.999978  \n",
      "2020-06-26 13:02:44.560157   1.0   1.0  1.000000  1.029451  1.000000  \n",
      "\n",
      "[1333 rows x 2000 columns],                                 0         1         2         3     4     \\\n",
      "2012-10-18 16:48:42.537666  0.957108  1.000000  1.000000  1.000000   1.0   \n",
      "2012-10-19 16:48:42.537666  0.957108  0.965556  1.000000  1.000000   1.0   \n",
      "2012-10-22 16:48:42.537666  0.957108  0.965556  0.895792  1.000000   1.0   \n",
      "2012-10-23 16:48:42.537666  0.957108  0.965556  0.895792  0.925488   1.0   \n",
      "2012-10-23 20:36:38.274374  0.957108  0.965556  0.895792  0.925488   1.0   \n",
      "...                              ...       ...       ...       ...   ...   \n",
      "2015-05-11 16:35:48.143191  1.000000  1.000000  1.000000  1.000000   1.0   \n",
      "2015-05-11 16:48:42.537666  1.000000  1.000000  1.000000  1.000000   1.0   \n",
      "2015-05-12 16:48:42.537666  1.000000  1.000000  1.000000  1.000000   1.0   \n",
      "2015-05-12 19:42:48.499056  1.000000  1.000000  1.000000  1.000000   1.0   \n",
      "2015-05-13 15:20:30.907070  1.000000  1.000000  1.000000  1.000000   1.0   \n",
      "\n",
      "                            5     6     7     8     9     ...  1990  1991  \\\n",
      "2012-10-18 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  ...   1.0   1.0   \n",
      "2012-10-19 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  ...   1.0   1.0   \n",
      "2012-10-22 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  ...   1.0   1.0   \n",
      "2012-10-23 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  ...   1.0   1.0   \n",
      "2012-10-23 20:36:38.274374   1.0   1.0   1.0   1.0   1.0  ...   1.0   1.0   \n",
      "...                          ...   ...   ...   ...   ...  ...   ...   ...   \n",
      "2015-05-11 16:35:48.143191   1.0   1.0   1.0   1.0   1.0  ...   1.0   1.0   \n",
      "2015-05-11 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  ...   1.0   1.0   \n",
      "2015-05-12 16:48:42.537666   1.0   1.0   1.0   1.0   1.0  ...   1.0   1.0   \n",
      "2015-05-12 19:42:48.499056   1.0   1.0   1.0   1.0   1.0  ...   1.0   1.0   \n",
      "2015-05-13 15:20:30.907070   1.0   1.0   1.0   1.0   1.0  ...   1.0   1.0   \n",
      "\n",
      "                            1992  1993  1994  1995  1996  1997  1998  1999  \n",
      "2012-10-18 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0  \n",
      "2012-10-19 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0  \n",
      "2012-10-22 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0  \n",
      "2012-10-23 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0  \n",
      "2012-10-23 20:36:38.274374   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0  \n",
      "...                          ...   ...   ...   ...   ...   ...   ...   ...  \n",
      "2015-05-11 16:35:48.143191   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-11 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-12 16:48:42.537666   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-12 19:42:48.499056   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0  \n",
      "2015-05-13 15:20:30.907070   1.0   1.0   1.0   1.0   1.0   1.0   1.0   1.0  \n",
      "\n",
      "[1334 rows x 2000 columns]] this out\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2020-06-17 16:50:00.076069 66.67% _rtn_by_tw done after 1.18 mins. Remaining 0.59 mins.\r",
      "2020-06-17 16:50:00.111380 100.0% _rtn_by_tw done after 1.18 mins. Remaining 0.0 mins.\n"
     ]
    }
   ],
   "source": [
    "# After parallelization!\n",
    "\n",
    "tw_rtn = rs.rtn_by_tw(mtx = portfolio_mtx, num_threads=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**\n",
    "\n",
    "Assuming all the asset and transaction was provided by the portfolio manager.\n",
    "\n",
    "The below is the final outcome for the portfolio performance over her career.\n",
    "\n",
    "If the return is negative at any time, it means the manager is having a negative return during that period.\n",
    "\n",
    "**Hint**\n",
    "\n",
    "Notice I only generated 2000 samples, but I have 4000 returns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012-10-18 16:48:42.537666   -0.042892\n",
       "2012-10-19 16:48:42.537666   -0.038677\n",
       "2012-10-22 16:48:42.537666   -0.061037\n",
       "2012-10-23 16:48:42.537666   -0.064424\n",
       "2012-10-23 20:36:38.274374   -0.064424\n",
       "                                ...   \n",
       "2020-06-16 16:48:42.537666    0.021448\n",
       "2020-06-17 16:48:42.537666    0.014241\n",
       "2020-06-21 11:50:42.088945    0.014241\n",
       "2020-06-26 06:45:14.294269    0.014607\n",
       "2020-06-26 13:02:44.560157    0.029451\n",
       "Length: 4000, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we will be using this as out back-test returns\n",
    "\n",
    "tw_rtn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**\n",
    "\n",
    "Key high-frequency trading strategy charactertistics:\n",
    "1. low return on transaction cost\n",
    "2. short holding period\n",
    "3. High number of bets\n",
    "\n",
    "If we factor high leverage as part of cost (Margin facilities), each transaction will bear additional cost.\n",
    "\n",
    "This will increase as part of it's variable cost per transaction basis, but if strategy also exhibits:\n",
    "\n",
    "1. High turnover\n",
    "2. High sharpe ratio\n",
    "\n",
    "There is a good chance that it will have a large capacity (High profitability) even with leverage, in fact leverage can further increase it's profit ratios."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-10-31</th>\n",
       "      <td>-0.037441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-11-30</th>\n",
       "      <td>0.007150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-12-31</th>\n",
       "      <td>-0.005919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-31</th>\n",
       "      <td>-0.006310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-28</th>\n",
       "      <td>0.007525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29</th>\n",
       "      <td>0.012841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-31</th>\n",
       "      <td>0.005994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-30</th>\n",
       "      <td>-0.008560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-31</th>\n",
       "      <td>-0.018319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-30</th>\n",
       "      <td>0.009112</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>93 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               close\n",
       "2012-10-31 -0.037441\n",
       "2012-11-30  0.007150\n",
       "2012-12-31 -0.005919\n",
       "2013-01-31 -0.006310\n",
       "2013-02-28  0.007525\n",
       "...              ...\n",
       "2020-02-29  0.012841\n",
       "2020-03-31  0.005994\n",
       "2020-04-30 -0.008560\n",
       "2020-05-31 -0.018319\n",
       "2020-06-30  0.009112\n",
       "\n",
       "[93 rows x 1 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'close': tw_rtn}).resample('M').mean().fillna(0)\n",
    "\n",
    "df1\n",
    "\n",
    "#we have about 93 months of data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**\n",
    "\n",
    "The below ratios are calculated based on 252 trading days.\n",
    "\n",
    "This is not correct, because we have a mixed of bonds and equities.\n",
    "\n",
    "252 trading days are meant for equities only. But for simplicity I used 252.\n",
    "\n",
    "Please check the number of trading days for each asset classes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Herfindahl-Hirschman Index (M frequency)\n",
      "=======================================================\n",
      "HHI positive: 0.031088\n",
      "HHI Negative: 0.016851\n",
      "HHI Time between: 0.000000\n",
      "\n",
      "\n",
      "Sharpe Ratio: -1.798303\n",
      "Information Ratio: -1.798303\n"
     ]
    }
   ],
   "source": [
    "positive_hhi, negative_hhi, time_hhi = rs.hh_idx(rtn = df1, freq = 'M')\n",
    "\n",
    "sr = rs.sharpe_ratio(rtn = df1.squeeze(), rf_param = .0, t_days = 252)\n",
    "ir = rs.inform_ratio(rtn = df1.squeeze(), benchmark = .0, t_days = 252)\n",
    "\n",
    "print(\"\\nSharpe Ratio: {0:.6f}\\nInformation Ratio: {1:.6f}\".format(sr, ir))\n",
    "\n",
    "# Almost quite guarantee this portfolio manager will lose your money"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Herfindahl-Hirschman Index (HHI)\n",
    "\n",
    "Measures the imbalance between positive and negative returns, which will reflect how skewed return distribution.\n",
    "\n",
    "HHI time shown above indicates the frequency of these returns (concentration).\n",
    "\n",
    "All scores return should be as low as possible.\n",
    "\n",
    "* Low Postive HHI (No right fat tail)\n",
    "* Low Negative HHI (No left fat tail)\n",
    "* Low HHI Time (Return frequency is uniformly distributed)\n",
    "\n",
    "This will impact Probabilistic Sharpe Ratio (PSR) and Deflated Sharpe Ratio (DSR). Since sampls are simulated and not actual inputs.\n",
    "\n",
    "Therefore ideally, it should be as \"normal\" as possible."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The probability indicates that our sharpe ratio: -1.798303 is not correct\n",
      "It is 0.8261832625852505 which is much lesser than 0.95\n"
     ]
    }
   ],
   "source": [
    "from scipy.stats import skew, kurtosis\n",
    "\n",
    "pct = rs.proba_sr(obs_sr = sr,\n",
    "             benchmark_sr = -1.9, # since the sr was negative to begin with\n",
    "             num_returns = 93, #monthly basis\n",
    "             skew_returns = df1.skew(),\n",
    "             kurt_returns = df1.kurtosis())\n",
    "\n",
    "print(\"The probability indicates that our sharpe ratio: {0:.6f} is not correct\\nIt is {1} which is much lesser than 0.95\".format(sr, pct[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The probability indicates that our sharpe ratio: -1.798303 is correct\n",
      "It is 0.9994188490783524 more than 0.95\n"
     ]
    }
   ],
   "source": [
    "pct = rs.proba_sr(obs_sr = sr,\n",
    "             benchmark_sr = -2.15, # since the sr was negative to begin with\n",
    "             num_returns = 93, #monthly basis\n",
    "             skew_returns = df1.skew(),\n",
    "             kurt_returns = df1.kurtosis())\n",
    "\n",
    "print(\"The probability indicates that our sharpe ratio: {0:.6f} is correct\\nIt is {1} more than 0.95\".format(sr, pct[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Probabilistic Sharpe Ratio (PSR)\n",
    "\n",
    "Notice the change in benchmark can reflect how correct the sharpe ratios are.\n",
    "\n",
    "In the first example, we set benchmark as -1.9. We have a score much lesser than 0.95. (0.95 > 0.8261832625852505)\n",
    "\n",
    "But when we change the benchmark to be below our existing sharp ratio, it became \"correct\".\n",
    "\n",
    "Like-wise if we were to set a benchmark closer to the sharpe ratio, it becomes more \"reliable\". (0.95 < 0.9994188490783524)\n",
    "\n",
    "### Deflated Sharpe Ratio (DSR) probability\n",
    "\n",
    "Notice how high is the benchmark as a result deflated sharpe ratio probability was reduced. (4.980385988784981e-241 < 0.95)\n",
    "\n",
    "If you reduce the number of trials, you will notice there will be an increase in DSR probability."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Deflated Sharpe Ratio: 4.980385988784981e-241\n",
      "Benchmark set: 1.7894064662732079\n"
     ]
    }
   ],
   "source": [
    "std_ = 0.5\n",
    "n_trials = 100\n",
    "\n",
    "dsr, benchmark = rs.deflated_sr(obs_sr = sr,\n",
    "                                sr_est = [std_, n_trials],\n",
    "                                num_returns = 93,\n",
    "                                skew_returns = df1.skew(), \n",
    "                                kurt_returns = df1.kurtosis(),\n",
    "                                est_param = True)\n",
    "\n",
    "print(\"Deflated Sharpe Ratio Probability: {0}\\nBenchmark set: {1}\".format(dsr[0], benchmark))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Conclusion\n",
    "\n",
    "When running backtest performance metrics, the following key factors will impact the reliablility of outcome.\n",
    "\n",
    "1. Return's skewness\n",
    "2. Return's Kurtosis\n",
    "3. Frequency of return report\n",
    "4. Risk-free rate/ benchmark\n",
    "\n",
    "For the first 4 factors will result in deviations from normal, as a result it will mean either \"too good to be true\" or \"too bad to be true\". Where benchmark will act as a mean of return distribution.\n",
    "\n",
    "For DSR, which corrects SR for inflationary effect due to non-normal return, track record length and selection bias. Requires multiple testing (n_trials/ number of backtest performed). As well as standard deviation of these resulted sharpe ratio.\n",
    "\n",
    "As discussed previously overfitting is common during backtest, hence more trials performed will require higher benchmark to prevent any possible overfitting.\n",
    "\n",
    "**Note**\n",
    "\n",
    "In the book, Dr Marcos mentioned time weighted return calculation for a single portfolio. \n",
    "\n",
    "Which was what we used previously, however the remaining exercise is more directed at individual securities.\n",
    "\n",
    "But for completeness, I load whatever S&P500 data I have for the below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Loss Metric (0.95 quartile)\n",
      "=======================================================\n",
      "Drawdown: $56.20\n",
      "Time under water (Based on 252 days): 12.205734\n",
      "\n",
      "\n",
      "Accuracy Metric Estimate\n",
      "=======================================================\n",
      "Total postive returns: 11579\n",
      "Total negative returns: 12500\n",
      "\n",
      "Average postive returns: 0.000998\n",
      "Average negative returns: -0.000916\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('./research/Sample_data/dollar_bars.txt', \n",
    "                 sep=',', \n",
    "                 header=0, \n",
    "                 parse_dates = True, \n",
    "                 index_col=['date_time'])\n",
    "\n",
    "df = df['close'].to_frame()\n",
    "\n",
    "draw_dn, time_ddn = rs.drawdn_period(data = df, dollars = True, q_param = .95)\n",
    "\n",
    "a_rtn = rs.annualized_rtn(data = df.squeeze(), t_days = 252, verbose = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Interesting Fact**\n",
    "\n",
    "When I tried to run the synthetic data generator to create a portofolio based on random uniform distribution.\n",
    "\n",
    "I have never gotten once a \"portfolio\" with performance that has positive performance, the current portfolio in this exercise has the best \"performance\".\n",
    "\n",
    "At the very least, it does not return a NaN probability.\n",
    "\n",
    "Indirectly, it means it is almost impossible to generate alpha on random basis (Luck)."
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}