{ "cells": [ { "cell_type": "code", "execution_count": 2, "metadata": { "ExecuteTime": { "end_time": "2018-10-18T12:45:44.192244Z", "start_time": "2018-10-18T12:45:43.850069Z" } }, "outputs": [], "source": [ "#Imports\n", "import pandas as pd\n", "import numpy as np\n", "from typing import Dict, List\n", "from collections import OrderedDict\n", "import datetime as datetime\n", "from pandas.tseries.offsets import *\n", "from IPython.core.debugger import set_trace\n", "#import pixiedust\n", "#ctrl-shift-p for the command palette\n", "pd.set_option(\"max_colwidth\", 100)\n", "pd.set_option(\"display.max_rows\", 100000)\n", "pd.set_option(\"display.max_columns\", 1000)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "ExecuteTime": { "end_time": "2018-10-18T16:21:37.413860Z", "start_time": "2018-10-18T16:21:37.351988Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
underlying_symbolquote_daterootexpirationstrikeoption_typeopenhighlowclosetrade_volumebid_size_1545bid_1545ask_size_1545ask_1545underlying_bid_1545underlying_ask_1545bid_size_eodask_size_eodvwapopen_interestdelivery_codefuturesmid_eodmid_underlying
0^VIX2006-02-27VIX2006-04-1910.0c2.802.802.82.810.01.02.7050.02.9011.4711.471.010.02.8023.0012.52.95011.59
1^VIX2006-02-27VIX2006-04-1910.0p0.000.000.00.00.00.00.0050.00.1511.4711.470.050.00.000.0012.50.07511.59
2^VIX2006-02-27VIX2006-04-1912.5c1.151.151.11.130.010.01.1550.01.2011.4711.4710.010.01.13560.0012.51.30011.59
3^VIX2006-02-27VIX2006-04-1912.5p0.000.000.00.00.050.00.751.00.9011.4711.4710.010.00.00975.0012.50.87511.59
4^VIX2006-02-27VIX2006-04-1912.5c0.000.000.00.00.00.00.000.00.0011.4711.470.00.00.000.0012.50.00011.59
\n", "
" ], "text/plain": [ " underlying_symbol quote_date root expiration strike option_type open \\\n", "0 ^VIX 2006-02-27 VIX 2006-04-19 10.0 c 2.80 \n", "1 ^VIX 2006-02-27 VIX 2006-04-19 10.0 p 0.00 \n", "2 ^VIX 2006-02-27 VIX 2006-04-19 12.5 c 1.15 \n", "3 ^VIX 2006-02-27 VIX 2006-04-19 12.5 p 0.00 \n", "4 ^VIX 2006-02-27 VIX 2006-04-19 12.5 c 0.00 \n", "\n", " high low close trade_volume bid_size_1545 bid_1545 ask_size_1545 \\\n", "0 2.80 2.8 2.8 10.0 1.0 2.70 50.0 \n", "1 0.00 0.0 0.0 0.0 0.0 0.00 50.0 \n", "2 1.15 1.1 1.1 30.0 10.0 1.15 50.0 \n", "3 0.00 0.0 0.0 0.0 50.0 0.75 1.0 \n", "4 0.00 0.0 0.0 0.0 0.0 0.00 0.0 \n", "\n", " ask_1545 underlying_bid_1545 underlying_ask_1545 bid_size_eod \\\n", "0 2.90 11.47 11.47 1.0 \n", "1 0.15 11.47 11.47 0.0 \n", "2 1.20 11.47 11.47 10.0 \n", "3 0.90 11.47 11.47 10.0 \n", "4 0.00 11.47 11.47 0.0 \n", "\n", " ask_size_eod vwap open_interest delivery_code futures mid_eod \\\n", "0 10.0 2.80 23.0 0 12.5 2.950 \n", "1 50.0 0.00 0.0 0 12.5 0.075 \n", "2 10.0 1.13 560.0 0 12.5 1.300 \n", "3 10.0 0.00 975.0 0 12.5 0.875 \n", "4 0.0 0.00 0.0 0 12.5 0.000 \n", "\n", " mid_underlying \n", "0 11.59 \n", "1 11.59 \n", "2 11.59 \n", "3 11.59 \n", "4 11.59 " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "options_data = '../data/Options/vix_w_futures.csv'\n", "\n", "options = pd.read_csv(\n", " options_data,\n", " header=0,\n", " parse_dates=[\"quote_date\", \"expiration\"],\n", ")\n", "options.head()" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "ExecuteTime": { "end_time": "2018-10-18T12:45:50.874434Z", "start_time": "2018-10-18T12:45:50.860470Z" } }, "outputs": [], "source": [ "expiry_dates=options.expiration.unique()" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T16:29:13.028743Z", "start_time": "2018-10-06T16:29:12.018163Z" }, "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
quote_dateexpirationstriketrade_volumefuturesmid_eodmid_underlying
02006-02-272006-04-1910.00.012.50.07511.59
12006-02-272006-04-1912.50.012.50.87511.59
22006-02-272006-04-1912.50.012.50.00011.59
32006-02-272006-04-1915.00.012.52.70011.59
42006-02-272006-04-1915.00.012.50.00011.59
\n", "
" ], "text/plain": [ " quote_date expiration strike trade_volume futures mid_eod \\\n", "0 2006-02-27 2006-04-19 10.0 0.0 12.5 0.075 \n", "1 2006-02-27 2006-04-19 12.5 0.0 12.5 0.875 \n", "2 2006-02-27 2006-04-19 12.5 0.0 12.5 0.000 \n", "3 2006-02-27 2006-04-19 15.0 0.0 12.5 2.700 \n", "4 2006-02-27 2006-04-19 15.0 0.0 12.5 0.000 \n", "\n", " mid_underlying \n", "0 11.59 \n", "1 11.59 \n", "2 11.59 \n", "3 11.59 \n", "4 11.59 " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "puts_data = '../data/Options/vixPuts.csv'\n", "#puts_data='../data/Options/vxxPuts.csv'\n", "puts = pd.read_csv(\n", " puts_data,\n", " header=0,\n", " parse_dates=[\"quote_date\", \"expiration\"],\n", " usecols=[\n", " \"quote_date\", \"expiration\", \"strike\", 'trade_volume',\"mid_eod\", \"mid_underlying\",\"futures\"\n", " ])\n", "puts.head()" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T16:29:13.040391Z", "start_time": "2018-10-06T16:29:13.031414Z" } }, "outputs": [ { "data": { "text/plain": [ "154" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "len(puts.expiration.unique())" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T16:29:13.126238Z", "start_time": "2018-10-06T16:29:13.042385Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
quote_dateexpirationstriketrade_volumefuturesmid_eodmid_underlyingDSameMSame
02006-02-272006-04-1910.00.012.50.07511.59TrueFalse
12006-02-272006-04-1912.50.012.50.87511.59TrueFalse
22006-02-272006-04-1912.50.012.50.00011.59TrueFalse
32006-02-272006-04-1915.00.012.52.70011.59TrueFalse
42006-02-272006-04-1915.00.012.50.00011.59TrueFalse
\n", "
" ], "text/plain": [ " quote_date expiration strike trade_volume futures mid_eod \\\n", "0 2006-02-27 2006-04-19 10.0 0.0 12.5 0.075 \n", "1 2006-02-27 2006-04-19 12.5 0.0 12.5 0.875 \n", "2 2006-02-27 2006-04-19 12.5 0.0 12.5 0.000 \n", "3 2006-02-27 2006-04-19 15.0 0.0 12.5 2.700 \n", "4 2006-02-27 2006-04-19 15.0 0.0 12.5 0.000 \n", "\n", " mid_underlying DSame MSame \n", "0 11.59 True False \n", "1 11.59 True False \n", "2 11.59 True False \n", "3 11.59 True False \n", "4 11.59 True False " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#insert some column markers so we know when days, months change\n", "\n", "first_filter=puts.quote_date.isin(expiry_dates)\n", "second_filter=puts.quote_date.isin(expiry_dates).shift(1)\n", "\n", "puts['DSame'] = puts.quote_date.eq(puts.quote_date.shift(-1))\n", "#puts['MSame'] = puts.quote_date.dt.month.eq(\n", "# puts.quote_date.dt.month.shift(1))\n", "puts['MSame']=(first_filter==True) & (second_filter==False)\n", "\n", "puts.head()" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T16:29:14.148457Z", "start_time": "2018-10-06T16:29:13.127157Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
quote_dateexpirationstriketrade_volumefuturesmid_eodmid_underlying
02006-02-272006-04-1910.010.012.52.9511.59
12006-02-272006-04-1912.530.012.51.3011.59
22006-02-272006-04-1912.50.012.50.0011.59
32006-02-272006-04-1915.03001.012.50.6511.59
42006-02-272006-04-1915.00.012.50.0011.59
\n", "
" ], "text/plain": [ " quote_date expiration strike trade_volume futures mid_eod \\\n", "0 2006-02-27 2006-04-19 10.0 10.0 12.5 2.95 \n", "1 2006-02-27 2006-04-19 12.5 30.0 12.5 1.30 \n", "2 2006-02-27 2006-04-19 12.5 0.0 12.5 0.00 \n", "3 2006-02-27 2006-04-19 15.0 3001.0 12.5 0.65 \n", "4 2006-02-27 2006-04-19 15.0 0.0 12.5 0.00 \n", "\n", " mid_underlying \n", "0 11.59 \n", "1 11.59 \n", "2 11.59 \n", "3 11.59 \n", "4 11.59 " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "calls_data = '../data/Options/vixCalls.csv'\n", "#calls_data='../data/Options/vxxCalls.csv'\n", "\n", "calls = pd.read_csv(\n", " calls_data,\n", " header=0,\n", " parse_dates=[\"quote_date\", \"expiration\"],\n", " usecols=[\n", " \"quote_date\", \"expiration\", \"strike\", 'trade_volume', \"mid_eod\",\n", " \"mid_underlying\",\"futures\"\n", " ])\n", "calls.head()" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T16:29:14.158402Z", "start_time": "2018-10-06T16:29:14.149422Z" } }, "outputs": [ { "data": { "text/plain": [ "154" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "len(calls.expiration.unique())" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T16:29:14.316981Z", "start_time": "2018-10-06T16:29:14.159396Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
quote_dateexpirationstriketrade_volumefuturesmid_eodmid_underlyingDSameMSame
02006-02-272006-04-1910.010.012.52.9511.59TrueFalse
12006-02-272006-04-1912.530.012.51.3011.59TrueFalse
22006-02-272006-04-1912.50.012.50.0011.59TrueFalse
32006-02-272006-04-1915.03001.012.50.6511.59TrueFalse
42006-02-272006-04-1915.00.012.50.0011.59TrueFalse
\n", "
" ], "text/plain": [ " quote_date expiration strike trade_volume futures mid_eod \\\n", "0 2006-02-27 2006-04-19 10.0 10.0 12.5 2.95 \n", "1 2006-02-27 2006-04-19 12.5 30.0 12.5 1.30 \n", "2 2006-02-27 2006-04-19 12.5 0.0 12.5 0.00 \n", "3 2006-02-27 2006-04-19 15.0 3001.0 12.5 0.65 \n", "4 2006-02-27 2006-04-19 15.0 0.0 12.5 0.00 \n", "\n", " mid_underlying DSame MSame \n", "0 11.59 True False \n", "1 11.59 True False \n", "2 11.59 True False \n", "3 11.59 True False \n", "4 11.59 True False " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#insert some column markers so we know when days, months change\n", "first_filter=calls.quote_date.isin(expiry_dates)\n", "second_filter=calls.quote_date.isin(expiry_dates).shift(1)\n", "calls['DSame'] = calls.quote_date.eq(calls.quote_date.shift(-1))\n", "#calls['MSame'] = calls.quote_date.dt.month.eq(\n", " #calls.quote_date.dt.month.shift(1))\n", "calls['MSame'] =(first_filter==True) & (second_filter==False)\n", "calls.head()" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T17:11:40.171377Z", "start_time": "2018-10-06T17:11:40.167387Z" } }, "outputs": [], "source": [ "#initialise variables\n", "#what length of option are you looking for in days?\n", "put_Expiration = int(200)\n", "#<1=OTM, 1=ATM, >1=ITM\n", "put_moneyness=1.00" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T17:11:54.603705Z", "start_time": "2018-10-06T17:11:40.897416Z" }, "code_folding": [], "pixiedust": { "displayParams": {} } }, "outputs": [], "source": [ "#%%pixie_debugger\n", "#initialise variables\n", "\n", "temp_puts = {}\n", "a = 0\n", "for i, row in enumerate(puts.itertuples(), 0):\n", "\n", " if i == 0:\n", " start = False\n", " #break\n", " if (row.MSame == True):\n", " #reset target expiration, target strike 1st day of each month\n", " start=True\n", " newMonth = row.quote_date\n", " targetExpiration = row.quote_date + put_Expiration * Day()\n", " targetExpirationDifference = abs(row.expiration - targetExpiration)\n", " #if row.mid_underlying <20: put_moneyness= 1.00\n", " #if row.mid_underlying >20 and row.mid_underlying <30: put_moneyness= 1.00\n", " #if row.mid_underlying >30: put_moneyness= 0.80\n", " strikeTarget = row.mid_underlying * put_moneyness\n", " myStrikeDiff = abs(row.strike - strikeTarget)\n", " tempExpiration = row.expiration\n", " strikeLocation = (i)\n", " if start == True:\n", "\n", " if row.quote_date == newMonth and row.MSame == False:\n", " #iterate through the many different expirations and strikes for the first day of the new month\n", " #find the closest match to (a) targetExpiration and then (b) targetStrike\n", "\n", " if abs(targetExpiration -\n", " row.expiration) < targetExpirationDifference:\n", "\n", " #error correction routine - when diff between target expiration and expiration continues to decline,\n", " #algo chooses the closer expirataion and then moves onto choose closest strike\n", " targetExpirationDifference = abs(targetExpiration -\n", " row.expiration)\n", " myStrikeDiff = abs(row.strike - strikeTarget)\n", " tempExpiration = row.expiration\n", " strikeLocation = (i)\n", "\n", " if tempExpiration == row.expiration:\n", " if abs(strikeTarget - row.strike) <= myStrikeDiff:\n", " myStrikeDiff = abs(strikeTarget - row.strike)\n", " strikeLocation = (i)\n", "\n", " #closest to target expiration has been chosen. Using error correction algo now chooses\n", " #strike nearest to target strike\n", "\n", " if row.quote_date == newMonth and row.DSame == False:\n", " temp_puts[puts.quote_date[i]] = [\n", " i,\n", " puts.quote_date[i],\n", " puts.expiration[strikeLocation],\n", " puts.strike[strikeLocation],\n", " puts.trade_volume[strikeLocation],\n", " puts.mid_eod[strikeLocation], #today's price \n", " newMonth,\n", " targetExpiration,\n", " targetExpirationDifference,\n", " puts.mid_underlying[strikeLocation],\n", " puts.futures[strikeLocation],\n", " strikeTarget,\n", " strikeLocation,\n", " myStrikeDiff\n", " ]\n", "\n", " if row.quote_date != newMonth and row.MSame == False:\n", " if row.expiration == puts.expiration[strikeLocation] and row.strike == puts.strike[strikeLocation]:\n", " temp_puts[puts.quote_date[i]] = [\n", " i,\n", " puts.quote_date[i],\n", " puts.expiration[strikeLocation],\n", " puts.strike[strikeLocation],\n", " puts.trade_volume[i],\n", " puts.mid_eod[i], #today's price \n", " newMonth,\n", " targetExpiration,\n", " targetExpirationDifference,\n", " puts.mid_underlying[i],\n", " puts.futures[i],\n", " strikeTarget,\n", " strikeLocation,\n", " myStrikeDiff\n", " ]" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T17:11:54.750309Z", "start_time": "2018-10-06T17:11:54.604700Z" }, "code_folding": [], "scrolled": false }, "outputs": [], "source": [ "rolling_puts = pd.DataFrame(temp_puts).T.fillna(method='ffill')\n", "rolling_puts.index.name = 'Date'\n", "rolling_puts.columns = [\n", " 'i', 'quote_date', 'expiration', 'strike', 'trade_volume', 'price',\n", " 'newMonth', 'targetExpiration', 'ExpDiff', 'mid_underlying','futures',\n", " 'strikeTarget', 'strikeLocation', 'StrikeDiff'\n", "]\n", "first_filter=rolling_puts.quote_date.isin(expiry_dates)\n", "second_filter=rolling_puts.quote_date.isin(expiry_dates).shift(1)\n", "rolling_puts['entry'] =(first_filter==True) & (second_filter==False)\n", "rolling_puts['exit'] =rolling_puts.entry.shift(-1)\n", "#rolling_puts['exit'] =(first_filter==True) & (second_filter==False)\n" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
iquote_dateexpirationstriketrade_volumepricenewMonthtargetExpirationExpDiffmid_underlyingfuturesstrikeTargetstrikeLocationStrikeDiffentryexit
Date
2006-04-197142006-04-192006-05-1722.50.09.902006-04-192006-11-05172 days11.3222.511.3271411.18FalseFalse
2006-04-207212006-04-202006-05-1722.50.012.552006-04-192006-11-05172 days11.6412.511.3271411.18FalseFalse
2006-04-217292006-04-212006-05-1722.50.012.352006-04-192006-11-05172 days11.5912.511.3271411.18FalseFalse
2006-04-247682006-04-242006-05-1722.520.012.452006-04-192006-11-05172 days11.7512.511.3271411.18FalseFalse
2006-04-258072006-04-252006-05-1722.50.09.902006-04-192006-11-05172 days11.7612.511.3271411.18FalseFalse
\n", "
" ], "text/plain": [ " i quote_date expiration strike trade_volume price newMonth \\\n", "Date \n", "2006-04-19 714 2006-04-19 2006-05-17 22.5 0.0 9.90 2006-04-19 \n", "2006-04-20 721 2006-04-20 2006-05-17 22.5 0.0 12.55 2006-04-19 \n", "2006-04-21 729 2006-04-21 2006-05-17 22.5 0.0 12.35 2006-04-19 \n", "2006-04-24 768 2006-04-24 2006-05-17 22.5 20.0 12.45 2006-04-19 \n", "2006-04-25 807 2006-04-25 2006-05-17 22.5 0.0 9.90 2006-04-19 \n", "\n", " targetExpiration ExpDiff mid_underlying futures strikeTarget \\\n", "Date \n", "2006-04-19 2006-11-05 172 days 11.32 22.5 11.32 \n", "2006-04-20 2006-11-05 172 days 11.64 12.5 11.32 \n", "2006-04-21 2006-11-05 172 days 11.59 12.5 11.32 \n", "2006-04-24 2006-11-05 172 days 11.75 12.5 11.32 \n", "2006-04-25 2006-11-05 172 days 11.76 12.5 11.32 \n", "\n", " strikeLocation StrikeDiff entry exit \n", "Date \n", "2006-04-19 714 11.18 False False \n", "2006-04-20 714 11.18 False False \n", "2006-04-21 714 11.18 False False \n", "2006-04-24 714 11.18 False False \n", "2006-04-25 714 11.18 False False " ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rolling_puts.head()" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T17:11:54.882955Z", "start_time": "2018-10-06T17:11:54.751322Z" } }, "outputs": [], "source": [ "#puts_file='../data/Options/vxxRolling_puts.csv'\n", "puts_file='../data/Options/vix_rolling_puts.csv'\n", "rolling_puts.to_csv(puts_file)" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T17:13:17.875042Z", "start_time": "2018-10-06T17:13:17.871053Z" } }, "outputs": [], "source": [ "#initialise variables\n", "#what length of option are you looking for in days?\n", "call_Expiration = int(200)\n", "#<1=ITM, 1=ATM, >1=OTM\n", "call_moneyness = 2.00" ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T17:13:32.295572Z", "start_time": "2018-10-06T17:13:18.632102Z" } }, "outputs": [], "source": [ "#%%pixie_debugger\n", "#initialise variables\n", "\n", "temp_calls = {}\n", "a = 0\n", "for i, row in enumerate(calls.itertuples(), 0):\n", "\n", " if i == 0:\n", " start = False\n", " #break\n", " if (row.MSame == True):\n", " #reset target expiration, target strike 1st day of each month\n", " start=True\n", " newMonth = row.quote_date\n", " targetExpiration = row.quote_date + call_Expiration * Day()\n", " targetExpirationDifference = abs(row.expiration - targetExpiration)\n", " strikeTarget = row.mid_underlying * call_moneyness\n", " myStrikeDiff = abs(row.strike - strikeTarget)\n", " tempExpiration = row.expiration\n", " strikeLocation = (i)\n", " if start == True:\n", "\n", " if row.quote_date == newMonth and row.MSame == False:\n", " #iterate through the many different expirations and strikes for the first day of the new month\n", " #find the closest match to (a) targetExpiration and then (b) targetStrike\n", "\n", " if abs(targetExpiration -\n", " row.expiration) < targetExpirationDifference:\n", "\n", " #error correction routine - when diff between target expiration and expiration continues to decline,\n", " #algo chooses the closer expirataion and then moves onto choose closest strike\n", " targetExpirationDifference = abs(targetExpiration -\n", " row.expiration)\n", " myStrikeDiff = abs(row.strike - strikeTarget)\n", " tempExpiration = row.expiration\n", " strikeLocation = (i)\n", "\n", " if tempExpiration == row.expiration:\n", " if abs(strikeTarget - row.strike) <= myStrikeDiff:\n", " myStrikeDiff = abs(strikeTarget - row.strike)\n", " strikeLocation = (i)\n", "\n", "# #closest to target expiration has been chosen. Using error correction algo now chooses\n", "#strike nearest to target strike\n", "\n", " if row.quote_date == newMonth and row.DSame == False:\n", "\n", " temp_calls[calls.quote_date[i]] = [\n", " i,\n", " calls.quote_date[i],\n", " calls.expiration[strikeLocation],\n", " calls.strike[strikeLocation],\n", " calls.trade_volume[strikeLocation],\n", " calls.mid_eod[strikeLocation], #today's price \n", " newMonth,\n", " targetExpiration,\n", " targetExpirationDifference,\n", " calls.mid_underlying[strikeLocation],\n", " calls.futures[strikeLocation],\n", " strikeTarget,\n", " strikeLocation,\n", " myStrikeDiff\n", " ]\n", "\n", " if row.quote_date != newMonth and row.MSame == False:\n", " if row.expiration == calls.expiration[strikeLocation] and row.strike == calls.strike[strikeLocation]:\n", " temp_calls[calls.quote_date[i]] = [\n", " i,\n", " calls.quote_date[i],\n", " calls.expiration[strikeLocation],\n", " calls.strike[strikeLocation],\n", " calls.trade_volume[i],\n", " calls.mid_eod[i], #today's price \n", " newMonth,\n", " targetExpiration,\n", " targetExpirationDifference,\n", " calls.mid_underlying[i],\n", " calls.futures[i],\n", " strikeTarget,\n", " strikeLocation,\n", " myStrikeDiff\n", " ]" ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T17:13:32.452118Z", "start_time": "2018-10-06T17:13:32.297567Z" } }, "outputs": [], "source": [ "rolling_calls = pd.DataFrame(temp_calls).T.fillna(method='ffill')\n", "rolling_calls.index.name = 'Date'\n", "rolling_calls.columns = [\n", " 'i', 'quote_date','expiration', 'strike','trade_volume','price', 'newMonth',\n", " 'targetExpiration', 'ExpDiff', 'mid_underlying', 'futures','strikeTarget',\n", " 'strikeLocation', 'StrikeDiff'\n", "]\n", "first_filter=rolling_calls.quote_date.isin(expiry_dates)\n", "second_filter=rolling_calls.quote_date.isin(expiry_dates).shift(1)\n", "rolling_calls['entry'] =(first_filter==True) & (second_filter==False)\n", "rolling_calls['exit'] =rolling_calls.entry.shift(-1)\n" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
iquote_dateexpirationstriketrade_volumepricenewMonthtargetExpirationExpDiffmid_underlyingfuturesstrikeTargetstrikeLocationStrikeDiffentryexit
Date
2006-04-197142006-04-192006-05-1722.50.00.0752006-04-192006-11-05172 days11.3222.522.647140.14FalseFalse
2006-04-207212006-04-202006-05-1722.50.00.0752006-04-192006-11-05172 days11.6412.522.647140.14FalseFalse
2006-04-217292006-04-212006-05-1722.50.00.0752006-04-192006-11-05172 days11.5912.522.647140.14FalseFalse
2006-04-247682006-04-242006-05-1722.50.00.0752006-04-192006-11-05172 days11.7512.522.647140.14FalseFalse
2006-04-258072006-04-252006-05-1722.50.00.0502006-04-192006-11-05172 days11.7612.522.647140.14FalseFalse
\n", "
" ], "text/plain": [ " i quote_date expiration strike trade_volume price newMonth \\\n", "Date \n", "2006-04-19 714 2006-04-19 2006-05-17 22.5 0.0 0.075 2006-04-19 \n", "2006-04-20 721 2006-04-20 2006-05-17 22.5 0.0 0.075 2006-04-19 \n", "2006-04-21 729 2006-04-21 2006-05-17 22.5 0.0 0.075 2006-04-19 \n", "2006-04-24 768 2006-04-24 2006-05-17 22.5 0.0 0.075 2006-04-19 \n", "2006-04-25 807 2006-04-25 2006-05-17 22.5 0.0 0.050 2006-04-19 \n", "\n", " targetExpiration ExpDiff mid_underlying futures strikeTarget \\\n", "Date \n", "2006-04-19 2006-11-05 172 days 11.32 22.5 22.64 \n", "2006-04-20 2006-11-05 172 days 11.64 12.5 22.64 \n", "2006-04-21 2006-11-05 172 days 11.59 12.5 22.64 \n", "2006-04-24 2006-11-05 172 days 11.75 12.5 22.64 \n", "2006-04-25 2006-11-05 172 days 11.76 12.5 22.64 \n", "\n", " strikeLocation StrikeDiff entry exit \n", "Date \n", "2006-04-19 714 0.14 False False \n", "2006-04-20 714 0.14 False False \n", "2006-04-21 714 0.14 False False \n", "2006-04-24 714 0.14 False False \n", "2006-04-25 714 0.14 False False " ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rolling_calls.head()" ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T17:13:32.585947Z", "start_time": "2018-10-06T17:13:32.453132Z" } }, "outputs": [], "source": [ "#calls_file='../data/Options/vxxRolling_calls.csv'\n", "calls_file = '../data/Options/vix_rolling_calls.csv'\n", "rolling_calls.to_csv(calls_file)" ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T16:29:43.638088Z", "start_time": "2018-10-06T16:29:43.634061Z" } }, "outputs": [], "source": [ "calls_file='../data/Options/vix_rolling_callsL.csv'\n", "#rolling_calls.to_csv(calls_file)" ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "ExecuteTime": { "end_time": "2018-10-06T16:29:43.648046Z", "start_time": "2018-10-06T16:29:43.639050Z" } }, "outputs": [], "source": [ "calls_file='../data/Options/vix_rolling_callsS.csv'\n", "#rolling_calls.to_csv(calls_file)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "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.6.5" }, "varInspector": { "cols": { "lenName": 16, "lenType": 16, "lenVar": 40 }, "kernels_config": { "python": { "delete_cmd_postfix": "", "delete_cmd_prefix": "del ", "library": "var_list.py", "varRefreshCmd": "print(var_dic_list())" }, "r": { "delete_cmd_postfix": ") ", "delete_cmd_prefix": "rm(", "library": "var_list.r", "varRefreshCmd": "cat(var_dic_list()) " } }, "oldHeight": 579.181818, "position": { "height": "601px", "left": "1117.44px", "right": "20px", "top": "112px", "width": "358px" }, "types_to_exclude": [ "module", "function", "builtin_function_or_method", "instance", "_Feature" ], "varInspector_section_display": "block", "window_display": false } }, "nbformat": 4, "nbformat_minor": 2 }