Skip to content

Instantly share code, notes, and snippets.

@AnthonyFJGarner

AnthonyFJGarner/US_Future.ipynb

Last active November 27, 2019 00:55
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save AnthonyFJGarner/354d6782e7f3b0e1f6e6d8e8d382b098 to your computer and use it in GitHub Desktop.
Save AnthonyFJGarner/354d6782e7f3b0e1f6e6d8e8d382b098 to your computer and use it in GitHub Desktop.
Download ZIP
Concatenation US 30 Years Bond Futures
Display the source blob
Display the rendered blob
Raw
US_Future.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Introduction\n",
"This notebook retrieves individual futures contracts from the relevant directory on your hard drive and concatenates them into a single time series."
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {},
"outputs": [],
"source": [
"#Imports\n",
"import pandas as pd\n",
"import numpy as np\n",
"from numba import jit\n",
"import os"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The following function takes daily returns over the whole series and calculates VAMI. \n",
"The idea is to calculate an index of price so as to avoid concatenating contracts \n",
"using the distorting panama canal method."
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {},
"outputs": [],
"source": [
"@jit()\n",
"def calculator(a):\n",
" res = np.empty(df.VADI.shape)\n",
" res[0] = 100\n",
" for i in range(1, res.shape[0]):\n",
" res[i] = res[i-1] +(res[i-1]* a[i])\n",
" return res"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {},
"outputs": [],
"source": [
"path = '..\\\\data\\\\Futures\\\\US' # use your path to directory where your contract data is stored\n",
"\n",
"# Create a master dataframe to which you will append individual futures contracts\n",
"us_master = pd.DataFrame(columns=[\"Date\",\"Open\",\"High\",\"Low\",\"Close\",\"Volume\",\"Open_Interest\",\"Return\",\\\n",
" 'VADI','Contract','Start','End'])\n",
"# Create a dictionary to which to list key aspects of the data for reference\n",
"usDict={}\n",
"\n",
"# Establish a counter for the rows of the dictionary\n",
"a=0\n",
"\n",
"# Loop through all the invidual contracts in the directory\n",
"for file in os.listdir(path):\n",
" fname = os.path.splitext(file)[0]\n",
" cc=os.path.join(path, fname+\".CSV\")\n",
" #Read each futures contract into a dataframe one by one as the loop goes round\n",
" df= pd.read_csv(cc, header=None,names=[\"Date\",\"Open\",\"High\",\"Low\",\"Close\",\"Volume\",\"Open_Interest\"],parse_dates=[0])\n",
" #create a column for the daily return\n",
" df[\"Return\"]=df[\"Close\"].pct_change()\n",
" #convert the first day's return on each contract to zero rather than NAN\n",
" df.iloc[0,7]=0.0\n",
" #list the contract name in the Contract column\n",
" df['Contract']=fname \n",
" #Create a blank column foor the VADI\n",
" df['VADI']=0.0\n",
" #use the calculator function to populate the VADI column\n",
" df['VADI']=calculator(*df[list(df.loc[:, ['Return']])].values.T)\n",
" #Create a \"Start\" and \"End\" date column for later use in choosing which contracts to use\n",
" df['Start']=df.iloc[0,0]\n",
" df['End']=df.iloc[-1,0] \n",
" #Don't use the first row / date of each contract since it will not contain a daily return\n",
" df = df.iloc[1:] \n",
" #Since my data ends on 12th April 2019 I need to adjust the contract end dates\n",
" #for the last few contracts which are still current.\n",
" if a>=152:\n",
" contract=fname \n",
" contract=contract.lstrip('US')\n",
" contract=contract.replace('H', '-03')\n",
" contract=contract.replace('M', '-06')\n",
" contract=contract.replace('U', '-09')\n",
" contract=contract.replace('Z', '-12') \n",
" contract=(contract+'-15')\n",
" df['End']=contract \n",
" #concatenate each individual futures contract to the master file as we loop through the individual contracts\n",
" us_master=pd.concat([us_master,df], ignore_index=True,sort=False)\n",
" #Sort so that each date will contain each contract which trades on that date in expiry date order.\n",
" us_master.sort_values(by=['Date', 'End'],inplace=True)\n",
" #populate the usDict with meta date for the files included in the master file\n",
" usDict[a]=[cc,fname,df.iloc[0,0],df.iloc[-1,0],df.iloc[-1,4]/df.iloc[0,4]-1,len(df)]\n",
" a=a+1"
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {},
"outputs": [],
"source": [
"#create a date frame for the meta data and save the meta data to CSV file for eas of inspection and retrieval \n",
"us_futures = pd.DataFrame(usDict).T\n",
"us_futures.columns=[\"Path\",\"Contract\",\"Start\",\"End\",\"Pct_Profit\",\"Length\"]\n",
"us_futures.to_csv('..\\\\data\\\\Futures//us_futures.csv',index=None)\n",
"#save the master file for retrieval andfurther processing in the notebook entitled \"ED_Roll\"\n",
"us_master.to_csv('..\\\\data\\\\Futures//us_master.csv',index=None)"
]
},
{
"cell_type": "code",
"execution_count": 37,
"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>Date</th>\n",
" <th>Open</th>\n",
" <th>High</th>\n",
" <th>Low</th>\n",
" <th>Close</th>\n",
" <th>Volume</th>\n",
" <th>Open_Interest</th>\n",
" <th>Return</th>\n",
" <th>Contract</th>\n",
" <th>VADI</th>\n",
" <th>Start</th>\n",
" <th>End</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>2019-04-08</td>\n",
" <td>146.93750</td>\n",
" <td>146.93750</td>\n",
" <td>146.93750</td>\n",
" <td>146.93750</td>\n",
" <td>157099</td>\n",
" <td>2</td>\n",
" <td>0.002772</td>\n",
" <td>US2019Z</td>\n",
" <td>100.384287</td>\n",
" <td>2019-03-20</td>\n",
" <td>2019-12-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>2019-04-09</td>\n",
" <td>146.59375</td>\n",
" <td>146.59375</td>\n",
" <td>146.59375</td>\n",
" <td>146.59375</td>\n",
" <td>223482</td>\n",
" <td>2</td>\n",
" <td>-0.002339</td>\n",
" <td>US2019Z</td>\n",
" <td>100.149445</td>\n",
" <td>2019-03-20</td>\n",
" <td>2019-12-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>2019-04-10</td>\n",
" <td>146.81250</td>\n",
" <td>146.81250</td>\n",
" <td>146.81250</td>\n",
" <td>146.81250</td>\n",
" <td>235953</td>\n",
" <td>2</td>\n",
" <td>0.001492</td>\n",
" <td>US2019Z</td>\n",
" <td>100.298890</td>\n",
" <td>2019-03-20</td>\n",
" <td>2019-12-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>2019-04-11</td>\n",
" <td>146.31250</td>\n",
" <td>146.31250</td>\n",
" <td>146.31250</td>\n",
" <td>146.31250</td>\n",
" <td>238198</td>\n",
" <td>2</td>\n",
" <td>-0.003406</td>\n",
" <td>US2019Z</td>\n",
" <td>99.957301</td>\n",
" <td>2019-03-20</td>\n",
" <td>2019-12-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>2019-04-12</td>\n",
" <td>145.46875</td>\n",
" <td>145.46875</td>\n",
" <td>145.46875</td>\n",
" <td>145.46875</td>\n",
" <td>238198</td>\n",
" <td>2</td>\n",
" <td>-0.005767</td>\n",
" <td>US2019Z</td>\n",
" <td>99.380871</td>\n",
" <td>2019-03-20</td>\n",
" <td>2019-12-15</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Date Open High Low Close Volume \\\n",
"13 2019-04-08 146.93750 146.93750 146.93750 146.93750 157099 \n",
"14 2019-04-09 146.59375 146.59375 146.59375 146.59375 223482 \n",
"15 2019-04-10 146.81250 146.81250 146.81250 146.81250 235953 \n",
"16 2019-04-11 146.31250 146.31250 146.31250 146.31250 238198 \n",
"17 2019-04-12 145.46875 145.46875 145.46875 145.46875 238198 \n",
"\n",
" Open_Interest Return Contract VADI Start End \n",
"13 2 0.002772 US2019Z 100.384287 2019-03-20 2019-12-15 \n",
"14 2 -0.002339 US2019Z 100.149445 2019-03-20 2019-12-15 \n",
"15 2 0.001492 US2019Z 100.298890 2019-03-20 2019-12-15 \n",
"16 2 -0.003406 US2019Z 99.957301 2019-03-20 2019-12-15 \n",
"17 2 -0.005767 US2019Z 99.380871 2019-03-20 2019-12-15 "
]
},
"execution_count": 37,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#inspect the last contract\n",
"df.tail() "
]
},
{
"cell_type": "code",
"execution_count": 38,
"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>Path</th>\n",
" <th>Contract</th>\n",
" <th>Start</th>\n",
" <th>End</th>\n",
" <th>Pct_Profit</th>\n",
" <th>Length</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>167</th>\n",
" <td>..\\data\\Futures\\US\\US2018Z.CSV</td>\n",
" <td>US2018Z</td>\n",
" <td>2018-03-21 00:00:00</td>\n",
" <td>2018-12-19 00:00:00</td>\n",
" <td>0.016886</td>\n",
" <td>184</td>\n",
" </tr>\n",
" <tr>\n",
" <th>168</th>\n",
" <td>..\\data\\Futures\\US\\US2019H.CSV</td>\n",
" <td>US2019H</td>\n",
" <td>2018-06-21 00:00:00</td>\n",
" <td>2019-03-20 00:00:00</td>\n",
" <td>0.0255236</td>\n",
" <td>184</td>\n",
" </tr>\n",
" <tr>\n",
" <th>169</th>\n",
" <td>..\\data\\Futures\\US\\US2019M.CSV</td>\n",
" <td>US2019M</td>\n",
" <td>2018-09-20 00:00:00</td>\n",
" <td>2019-04-12 00:00:00</td>\n",
" <td>0.0573549</td>\n",
" <td>141</td>\n",
" </tr>\n",
" <tr>\n",
" <th>170</th>\n",
" <td>..\\data\\Futures\\US\\US2019U.CSV</td>\n",
" <td>US2019U</td>\n",
" <td>2018-12-20 00:00:00</td>\n",
" <td>2019-04-12 00:00:00</td>\n",
" <td>0.0143043</td>\n",
" <td>78</td>\n",
" </tr>\n",
" <tr>\n",
" <th>171</th>\n",
" <td>..\\data\\Futures\\US\\US2019Z.CSV</td>\n",
" <td>US2019Z</td>\n",
" <td>2019-03-21 00:00:00</td>\n",
" <td>2019-04-12 00:00:00</td>\n",
" <td>-0.00192967</td>\n",
" <td>17</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Path Contract Start \\\n",
"167 ..\\data\\Futures\\US\\US2018Z.CSV US2018Z 2018-03-21 00:00:00 \n",
"168 ..\\data\\Futures\\US\\US2019H.CSV US2019H 2018-06-21 00:00:00 \n",
"169 ..\\data\\Futures\\US\\US2019M.CSV US2019M 2018-09-20 00:00:00 \n",
"170 ..\\data\\Futures\\US\\US2019U.CSV US2019U 2018-12-20 00:00:00 \n",
"171 ..\\data\\Futures\\US\\US2019Z.CSV US2019Z 2019-03-21 00:00:00 \n",
"\n",
" End Pct_Profit Length \n",
"167 2018-12-19 00:00:00 0.016886 184 \n",
"168 2019-03-20 00:00:00 0.0255236 184 \n",
"169 2019-04-12 00:00:00 0.0573549 141 \n",
"170 2019-04-12 00:00:00 0.0143043 78 \n",
"171 2019-04-12 00:00:00 -0.00192967 17 "
]
},
"execution_count": 38,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#inspect the metadata file\n",
"us_futures.tail()"
]
},
{
"cell_type": "code",
"execution_count": 39,
"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>Date</th>\n",
" <th>Open</th>\n",
" <th>High</th>\n",
" <th>Low</th>\n",
" <th>Close</th>\n",
" <th>Volume</th>\n",
" <th>Open_Interest</th>\n",
" <th>Return</th>\n",
" <th>VADI</th>\n",
" <th>Contract</th>\n",
" <th>Start</th>\n",
" <th>End</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>70548</th>\n",
" <td>2019-04-11</td>\n",
" <td>147.34375</td>\n",
" <td>147.71875</td>\n",
" <td>147.00000</td>\n",
" <td>147.09375</td>\n",
" <td>238198</td>\n",
" <td>107</td>\n",
" <td>-0.003388</td>\n",
" <td>101.861069</td>\n",
" <td>US2019U</td>\n",
" <td>2018-12-19</td>\n",
" <td>2019-09-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>70566</th>\n",
" <td>2019-04-11</td>\n",
" <td>146.31250</td>\n",
" <td>146.31250</td>\n",
" <td>146.31250</td>\n",
" <td>146.31250</td>\n",
" <td>238198</td>\n",
" <td>2</td>\n",
" <td>-0.003406</td>\n",
" <td>99.957301</td>\n",
" <td>US2019Z</td>\n",
" <td>2019-03-20</td>\n",
" <td>2019-12-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>70549</th>\n",
" <td>2019-04-12</td>\n",
" <td>147.87500</td>\n",
" <td>148.06250</td>\n",
" <td>146.81250</td>\n",
" <td>146.90625</td>\n",
" <td>238198</td>\n",
" <td>945767</td>\n",
" <td>-0.005500</td>\n",
" <td>105.545577</td>\n",
" <td>US2019M</td>\n",
" <td>2018-09-19</td>\n",
" <td>2019-06-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>70550</th>\n",
" <td>2019-04-12</td>\n",
" <td>146.50000</td>\n",
" <td>147.37500</td>\n",
" <td>146.21875</td>\n",
" <td>146.25000</td>\n",
" <td>238198</td>\n",
" <td>107</td>\n",
" <td>-0.005736</td>\n",
" <td>101.276780</td>\n",
" <td>US2019U</td>\n",
" <td>2018-12-19</td>\n",
" <td>2019-09-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>70567</th>\n",
" <td>2019-04-12</td>\n",
" <td>145.46875</td>\n",
" <td>145.46875</td>\n",
" <td>145.46875</td>\n",
" <td>145.46875</td>\n",
" <td>238198</td>\n",
" <td>2</td>\n",
" <td>-0.005767</td>\n",
" <td>99.380871</td>\n",
" <td>US2019Z</td>\n",
" <td>2019-03-20</td>\n",
" <td>2019-12-15</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Date Open High Low Close Volume \\\n",
"70548 2019-04-11 147.34375 147.71875 147.00000 147.09375 238198 \n",
"70566 2019-04-11 146.31250 146.31250 146.31250 146.31250 238198 \n",
"70549 2019-04-12 147.87500 148.06250 146.81250 146.90625 238198 \n",
"70550 2019-04-12 146.50000 147.37500 146.21875 146.25000 238198 \n",
"70567 2019-04-12 145.46875 145.46875 145.46875 145.46875 238198 \n",
"\n",
" Open_Interest Return VADI Contract Start End \n",
"70548 107 -0.003388 101.861069 US2019U 2018-12-19 2019-09-15 \n",
"70566 2 -0.003406 99.957301 US2019Z 2019-03-20 2019-12-15 \n",
"70549 945767 -0.005500 105.545577 US2019M 2018-09-19 2019-06-15 \n",
"70550 107 -0.005736 101.276780 US2019U 2018-12-19 2019-09-15 \n",
"70567 2 -0.005767 99.380871 US2019Z 2019-03-20 2019-12-15 "
]
},
"execution_count": 39,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#inspect the master file\n",
"us_master.tail()"
]
}
],
"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.8"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment