11.2 WTI 선물 롤오버.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df1 = pd.read_excel('11.2 WTI.xlsx', 'Spot', index_col='Date')\n",
    "df2 = pd.read_excel('11.2 WTI.xlsx', 'Front', index_col='Date')\n",
    "df3 = pd.read_excel('11.2 WTI.xlsx', 'Second', index_col='Date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "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>Spot</th>\n",
       "      <th>Front</th>\n",
       "      <th>Second</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-01-08</th>\n",
       "      <td>82.75</td>\n",
       "      <td>82.75</td>\n",
       "      <td>83.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-07</th>\n",
       "      <td>82.66</td>\n",
       "      <td>82.66</td>\n",
       "      <td>83.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-06</th>\n",
       "      <td>83.18</td>\n",
       "      <td>83.18</td>\n",
       "      <td>83.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>81.77</td>\n",
       "      <td>81.77</td>\n",
       "      <td>82.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>81.51</td>\n",
       "      <td>81.51</td>\n",
       "      <td>82.12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Spot  Front  Second\n",
       "Date                            \n",
       "2010-01-08  82.75  82.75   83.30\n",
       "2010-01-07  82.66  82.66   83.19\n",
       "2010-01-06  83.18  83.18   83.75\n",
       "2010-01-05  81.77  81.77   82.41\n",
       "2010-01-04  81.51  81.51   82.12"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.concat([df1, df2, df3], axis=1)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "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>Spot</th>\n",
       "      <th>Front</th>\n",
       "      <th>Second</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-12-13</th>\n",
       "      <td>56.60</td>\n",
       "      <td>56.60</td>\n",
       "      <td>56.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-12</th>\n",
       "      <td>57.14</td>\n",
       "      <td>57.14</td>\n",
       "      <td>57.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-11</th>\n",
       "      <td>57.99</td>\n",
       "      <td>57.99</td>\n",
       "      <td>58.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-08</th>\n",
       "      <td>57.36</td>\n",
       "      <td>57.36</td>\n",
       "      <td>57.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-07</th>\n",
       "      <td>56.69</td>\n",
       "      <td>56.69</td>\n",
       "      <td>56.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Spot  Front  Second\n",
       "Date                            \n",
       "2017-12-13  56.60  56.60   56.59\n",
       "2017-12-12  57.14  57.14   57.16\n",
       "2017-12-11  57.99  57.99   58.05\n",
       "2017-12-08  57.36  57.36   57.44\n",
       "2017-12-07  56.69  56.69   56.75"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.fillna(method='ffill')\n",
    "df = df.fillna(method='bfill')\n",
    "df[10:15]"
   ]
  },
  {
   "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>Ticker</th>\n",
       "      <th>Month</th>\n",
       "      <th>Last Trade</th>\n",
       "      <th>Last Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>CLG10</td>\n",
       "      <td>2017-02-10</td>\n",
       "      <td>01/20/10</td>\n",
       "      <td>77.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>CLH10</td>\n",
       "      <td>2017-03-10</td>\n",
       "      <td>02/22/10</td>\n",
       "      <td>80.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>CLJ10</td>\n",
       "      <td>2017-04-10</td>\n",
       "      <td>03/22/10</td>\n",
       "      <td>81.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>CLK10</td>\n",
       "      <td>2017-05-10</td>\n",
       "      <td>04/20/10</td>\n",
       "      <td>83.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>CLM10</td>\n",
       "      <td>2017-06-10</td>\n",
       "      <td>05/20/10</td>\n",
       "      <td>68.01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Ticker      Month Last Trade  Last Price\n",
       "120  CLG10 2017-02-10   01/20/10       77.62\n",
       "121  CLH10 2017-03-10   02/22/10       80.16\n",
       "122  CLJ10 2017-04-10   03/22/10       81.25\n",
       "123  CLK10 2017-05-10   04/20/10       83.45\n",
       "124  CLM10 2017-06-10   05/20/10       68.01"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_e = pd.read_excel('11.2 WTI.xlsx', 'Expiry')\n",
    "df_e[120:125]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    01/20/00\n",
       "1    02/22/00\n",
       "2    03/21/00\n",
       "3    04/19/00\n",
       "4    05/22/00\n",
       "Name: Last Trade, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 만기일 추출\n",
    "df_e['Last Trade'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2000-01-20\n",
       "1   2000-02-22\n",
       "2   2000-03-21\n",
       "3   2000-04-19\n",
       "4   2000-05-22\n",
       "Name: Last Trade, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 만기일을 날짜 형식으로 변환\n",
    "pd.to_datetime(df_e['Last Trade']).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2000-01-20\n",
       "1   2000-02-22\n",
       "2   2000-03-21\n",
       "3   2000-04-19\n",
       "4   2000-05-22\n",
       "Name: Last Trade, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 추출한 만기일 정보를 Series에 저장\n",
    "expiry = pd.Series(pd.to_datetime(df_e['Last Trade']))\n",
    "expiry.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2000-01-20', '2000-02-22', '2000-03-21', '2000-04-19',\n",
       "               '2000-05-22', '2000-06-20', '2000-07-20', '2000-08-22',\n",
       "               '2000-09-20', '2000-10-20',\n",
       "               ...\n",
       "               '2023-08-22', '2023-09-20', '2023-10-20', '2023-11-20',\n",
       "               '2024-05-21', '2024-11-20', '2025-05-20', '2025-11-20',\n",
       "               '2026-05-19', '2026-11-20'],\n",
       "              dtype='datetime64[ns]', length=293, freq=None)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime(expiry.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DatetimeIndex(['2017-12-28', '2017-12-27', '2017-12-26', '2017-12-22',\n",
      "               '2017-12-21', '2017-12-20', '2017-12-19', '2017-12-18',\n",
      "               '2017-12-15', '2017-12-14',\n",
      "               ...\n",
      "               '2010-01-15', '2010-01-14', '2010-01-13', '2010-01-12',\n",
      "               '2010-01-11', '2010-01-08', '2010-01-07', '2010-01-06',\n",
      "               '2010-01-05', '2010-01-04'],\n",
      "              dtype='datetime64[ns]', name='Date', length=2014, freq=None)\n"
     ]
    }
   ],
   "source": [
    "print(df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Spot</th>\n",
       "      <th>Front</th>\n",
       "      <th>Second</th>\n",
       "      <th>Expiry</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-12-28</th>\n",
       "      <td>59.84</td>\n",
       "      <td>59.84</td>\n",
       "      <td>59.87</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-27</th>\n",
       "      <td>59.64</td>\n",
       "      <td>59.64</td>\n",
       "      <td>59.69</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-26</th>\n",
       "      <td>59.97</td>\n",
       "      <td>59.97</td>\n",
       "      <td>60.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-22</th>\n",
       "      <td>58.42</td>\n",
       "      <td>58.47</td>\n",
       "      <td>58.54</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-21</th>\n",
       "      <td>58.33</td>\n",
       "      <td>58.36</td>\n",
       "      <td>58.40</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Spot  Front  Second  Expiry\n",
       "Date                                    \n",
       "2017-12-28  59.84  59.84   59.87     NaN\n",
       "2017-12-27  59.64  59.64   59.69     NaN\n",
       "2017-12-26  59.97  59.97   60.00     NaN\n",
       "2017-12-22  58.42  58.47   58.54     NaN\n",
       "2017-12-21  58.33  58.36   58.40     NaN"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Expiry'] = np.nan\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "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>Spot</th>\n",
       "      <th>Front</th>\n",
       "      <th>Second</th>\n",
       "      <th>Expiry</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-12-13</th>\n",
       "      <td>56.60</td>\n",
       "      <td>56.60</td>\n",
       "      <td>56.59</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-12</th>\n",
       "      <td>57.14</td>\n",
       "      <td>57.14</td>\n",
       "      <td>57.16</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-11</th>\n",
       "      <td>57.99</td>\n",
       "      <td>57.99</td>\n",
       "      <td>58.05</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-08</th>\n",
       "      <td>57.36</td>\n",
       "      <td>57.36</td>\n",
       "      <td>57.44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-07</th>\n",
       "      <td>56.69</td>\n",
       "      <td>56.69</td>\n",
       "      <td>56.75</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Spot  Front  Second  Expiry\n",
       "Date                                    \n",
       "2017-12-13  56.60  56.60   56.59     NaN\n",
       "2017-12-12  57.14  57.14   57.16     NaN\n",
       "2017-12-11  57.99  57.99   58.05     NaN\n",
       "2017-12-08  57.36  57.36   57.44     NaN\n",
       "2017-12-07  56.69  56.69   56.75     NaN"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Trading day 중 만기일 찾아서 표시하기\n",
    "for d in df.index:\n",
    "    if d in pd.to_datetime(expiry.values):\n",
    "        df.loc[d].Expiry = 1\n",
    "df[10:15] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "손익 (현재가격 - 초기가격) :  -21,670.00\n"
     ]
    }
   ],
   "source": [
    "# 현물에 투자했다면\n",
    "# 손익 = 현재가격 - 초기가격\n",
    "price_now = float(df['2017-12-28']['Spot'])\n",
    "price_before = float(df['2010-01-04']['Spot'])\n",
    "profit = (price_now - price_before) * 1000\n",
    "print('손익 (현재가격 - 초기가격) : ', '{:,.2f}'.format(profit))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "누적 롤오버 비용 : 40.68\n"
     ]
    }
   ],
   "source": [
    "# 선물에 투자했다면\n",
    "# 현재가격 - 초기가격 - 롤오버 비용\n",
    "roll_over_cost_acc = 0\n",
    "for d in df.index:\n",
    "    if (df.loc[d].Expiry == 1):\n",
    "        # 롤오버 비용 = 차근월물 가격 - 최근월물 가격\n",
    "        roll_over_cost = df.loc[d].Second - df.loc[d].Front\n",
    "        roll_over_cost_acc = roll_over_cost_acc + roll_over_cost\n",
    "        \n",
    "print('누적 롤오버 비용 :', '{:,.2f}'.format(roll_over_cost_acc))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "누적 손익 -62,350.00\n"
     ]
    }
   ],
   "source": [
    "price_now = float(df['2017-12-28']['Front'])\n",
    "price_before = float(df['2010-01-04']['Front'])\n",
    "profit = (price_now - price_before - roll_over_cost_acc) * 1000\n",
    "print('누적 손익', '{:,.2f}'.format(profit))"
   ]
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}