Skip to content

Instantly share code, notes, and snippets.

@AnthonyFJGarner

AnthonyFJGarner/ED_Future.ipynb

Last active November 27, 2019 00:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save AnthonyFJGarner/52be2ce843a350cdb0b0a73b9e37b03f to your computer and use it in GitHub Desktop.
Save AnthonyFJGarner/52be2ce843a350cdb0b0a73b9e37b03f to your computer and use it in GitHub Desktop.
Download ZIP
Rolling Eurodollar Futures Part 1
Display the source blob
Display the rendered blob
Raw
ED_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": 2,
"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": 3,
"metadata": {},
"outputs": [],
"source": [
"@jit()\n",
"def calculator(a):\n",
" res = np.empty(df.VAMI.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": 83,
"metadata": {},
"outputs": [],
"source": [
"path = '..\\\\data\\\\Futures\\\\ED' # 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",
"ed_master = pd.DataFrame(columns=[\"Date\",\"Open\",\"High\",\"Low\",\"Close\",\"Volume\",\"Open_Interest\",\"Return\",\\\n",
" 'VAMI','Contract','Start','End'])\n",
"# Create a dictionary to which to list key aspects of the data for reference\n",
"edDict={}\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 VAMI\n",
" df['VAMI']=0.0\n",
" #use the calculator function to populate the VAMI column\n",
" df['VAMI']=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.replace('H', '-03')\n",
" contract=contract.replace('M', '-06')\n",
" contract=contract.replace('U', '-09')\n",
" contract=contract.replace('Z', '-12')\n",
" contract=contract.strip('ED')\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",
" ed_master=pd.concat([ed_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",
" ed_master.sort_values(by=['Date', 'End'],inplace=True)\n",
" #populate the edDict with meta date for the files included in the master file\n",
" edDict[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": 85,
"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",
"ed_futures = pd.DataFrame(edDict).T\n",
"ed_futures.columns=[\"Path\",\"Contract\",\"Start\",\"End\",\"Pct_Profit\",\"Length\"]\n",
"ed_futures.to_csv('..\\\\data\\\\Futures//ed_futures.csv',index=None)\n",
"#save the master file for retrieval andfurther processing in the notebook entitled \"ED_Roll\"\n",
"ed_master.to_csv('..\\\\data\\\\Futures//ed_master.csv',index=None)"
]
},
{
"cell_type": "code",
"execution_count": 74,
"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>VAMI</th>\n",
" <th>Start</th>\n",
" <th>End</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>2019-04-08</td>\n",
" <td>97.650</td>\n",
" <td>97.650</td>\n",
" <td>97.650</td>\n",
" <td>97.650</td>\n",
" <td>82091</td>\n",
" <td>101826</td>\n",
" <td>-0.000102</td>\n",
" <td>ED2023H</td>\n",
" <td>100.010242</td>\n",
" <td>2019-04-01</td>\n",
" <td>2023-03-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>2019-04-09</td>\n",
" <td>97.680</td>\n",
" <td>97.680</td>\n",
" <td>97.680</td>\n",
" <td>97.680</td>\n",
" <td>67924</td>\n",
" <td>102708</td>\n",
" <td>0.000307</td>\n",
" <td>ED2023H</td>\n",
" <td>100.040967</td>\n",
" <td>2019-04-01</td>\n",
" <td>2023-03-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>2019-04-10</td>\n",
" <td>97.710</td>\n",
" <td>97.710</td>\n",
" <td>97.710</td>\n",
" <td>97.710</td>\n",
" <td>106436</td>\n",
" <td>107846</td>\n",
" <td>0.000307</td>\n",
" <td>ED2023H</td>\n",
" <td>100.071692</td>\n",
" <td>2019-04-01</td>\n",
" <td>2023-03-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>2019-04-11</td>\n",
" <td>97.675</td>\n",
" <td>97.675</td>\n",
" <td>97.675</td>\n",
" <td>97.675</td>\n",
" <td>53472</td>\n",
" <td>103667</td>\n",
" <td>-0.000358</td>\n",
" <td>ED2023H</td>\n",
" <td>100.035846</td>\n",
" <td>2019-04-01</td>\n",
" <td>2023-03-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>2019-04-12</td>\n",
" <td>97.610</td>\n",
" <td>97.610</td>\n",
" <td>97.610</td>\n",
" <td>97.610</td>\n",
" <td>53472</td>\n",
" <td>103667</td>\n",
" <td>-0.000665</td>\n",
" <td>ED2023H</td>\n",
" <td>99.969275</td>\n",
" <td>2019-04-01</td>\n",
" <td>2023-03-15</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Date Open High Low Close Volume Open_Interest Return \\\n",
"5 2019-04-08 97.650 97.650 97.650 97.650 82091 101826 -0.000102 \n",
"6 2019-04-09 97.680 97.680 97.680 97.680 67924 102708 0.000307 \n",
"7 2019-04-10 97.710 97.710 97.710 97.710 106436 107846 0.000307 \n",
"8 2019-04-11 97.675 97.675 97.675 97.675 53472 103667 -0.000358 \n",
"9 2019-04-12 97.610 97.610 97.610 97.610 53472 103667 -0.000665 \n",
"\n",
" Contract VAMI Start End \n",
"5 ED2023H 100.010242 2019-04-01 2023-03-15 \n",
"6 ED2023H 100.040967 2019-04-01 2023-03-15 \n",
"7 ED2023H 100.071692 2019-04-01 2023-03-15 \n",
"8 ED2023H 100.035846 2019-04-01 2023-03-15 \n",
"9 ED2023H 99.969275 2019-04-01 2023-03-15 "
]
},
"execution_count": 74,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#inspect the last contract\n",
"df.tail() "
]
},
{
"cell_type": "code",
"execution_count": 14,
"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>160</th>\n",
" <td>..\\data\\Futures\\ED\\ED2022H.CSV</td>\n",
" <td>ED2022H</td>\n",
" <td>2018-05-07 00:00:00</td>\n",
" <td>2019-04-12 00:00:00</td>\n",
" <td>0.00804621</td>\n",
" <td>145</td>\n",
" </tr>\n",
" <tr>\n",
" <th>161</th>\n",
" <td>..\\data\\Futures\\ED\\ED2022M.CSV</td>\n",
" <td>ED2022M</td>\n",
" <td>2018-07-17 00:00:00</td>\n",
" <td>2019-04-12 00:00:00</td>\n",
" <td>0.00669792</td>\n",
" <td>136</td>\n",
" </tr>\n",
" <tr>\n",
" <th>162</th>\n",
" <td>..\\data\\Futures\\ED\\ED2022U.CSV</td>\n",
" <td>ED2022U</td>\n",
" <td>2018-10-17 00:00:00</td>\n",
" <td>2019-04-12 00:00:00</td>\n",
" <td>0.00935256</td>\n",
" <td>112</td>\n",
" </tr>\n",
" <tr>\n",
" <th>163</th>\n",
" <td>..\\data\\Futures\\ED\\ED2022Z.CSV</td>\n",
" <td>ED2022Z</td>\n",
" <td>2019-01-02 00:00:00</td>\n",
" <td>2019-04-12 00:00:00</td>\n",
" <td>0.00205255</td>\n",
" <td>70</td>\n",
" </tr>\n",
" <tr>\n",
" <th>164</th>\n",
" <td>..\\data\\Futures\\ED\\ED2023H.CSV</td>\n",
" <td>ED2023H</td>\n",
" <td>2019-04-02 00:00:00</td>\n",
" <td>2019-04-12 00:00:00</td>\n",
" <td>-0.00056315</td>\n",
" <td>9</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Path Contract Start \\\n",
"160 ..\\data\\Futures\\ED\\ED2022H.CSV ED2022H 2018-05-07 00:00:00 \n",
"161 ..\\data\\Futures\\ED\\ED2022M.CSV ED2022M 2018-07-17 00:00:00 \n",
"162 ..\\data\\Futures\\ED\\ED2022U.CSV ED2022U 2018-10-17 00:00:00 \n",
"163 ..\\data\\Futures\\ED\\ED2022Z.CSV ED2022Z 2019-01-02 00:00:00 \n",
"164 ..\\data\\Futures\\ED\\ED2023H.CSV ED2023H 2019-04-02 00:00:00 \n",
"\n",
" End Pct_Profit Length \n",
"160 2019-04-12 00:00:00 0.00804621 145 \n",
"161 2019-04-12 00:00:00 0.00669792 136 \n",
"162 2019-04-12 00:00:00 0.00935256 112 \n",
"163 2019-04-12 00:00:00 0.00205255 70 \n",
"164 2019-04-12 00:00:00 -0.00056315 9 "
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#inspect the metadata file\n",
"ed_futures.tail()"
]
},
{
"cell_type": "code",
"execution_count": 80,
"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>VAMI</th>\n",
" <th>Contract</th>\n",
" <th>Start</th>\n",
" <th>End</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>103056</th>\n",
" <td>2019-04-12</td>\n",
" <td>97.720</td>\n",
" <td>97.720</td>\n",
" <td>97.720</td>\n",
" <td>97.720</td>\n",
" <td>53472</td>\n",
" <td>379128</td>\n",
" <td>-0.000716</td>\n",
" <td>100.690366</td>\n",
" <td>ED2022H</td>\n",
" <td>2018-04-13</td>\n",
" <td>2022-03-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>103057</th>\n",
" <td>2019-04-12</td>\n",
" <td>97.695</td>\n",
" <td>97.695</td>\n",
" <td>97.695</td>\n",
" <td>97.695</td>\n",
" <td>53472</td>\n",
" <td>283435</td>\n",
" <td>-0.000716</td>\n",
" <td>100.700923</td>\n",
" <td>ED2022M</td>\n",
" <td>2018-07-02</td>\n",
" <td>2022-06-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>103058</th>\n",
" <td>2019-04-12</td>\n",
" <td>97.670</td>\n",
" <td>97.670</td>\n",
" <td>97.670</td>\n",
" <td>97.670</td>\n",
" <td>53472</td>\n",
" <td>198781</td>\n",
" <td>-0.000716</td>\n",
" <td>100.903972</td>\n",
" <td>ED2022U</td>\n",
" <td>2018-10-16</td>\n",
" <td>2022-09-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>103059</th>\n",
" <td>2019-04-12</td>\n",
" <td>97.640</td>\n",
" <td>97.640</td>\n",
" <td>97.640</td>\n",
" <td>97.640</td>\n",
" <td>53472</td>\n",
" <td>174926</td>\n",
" <td>-0.000665</td>\n",
" <td>100.236115</td>\n",
" <td>ED2022Z</td>\n",
" <td>2018-12-31</td>\n",
" <td>2022-12-15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>103068</th>\n",
" <td>2019-04-12</td>\n",
" <td>97.610</td>\n",
" <td>97.610</td>\n",
" <td>97.610</td>\n",
" <td>97.610</td>\n",
" <td>53472</td>\n",
" <td>103667</td>\n",
" <td>-0.000665</td>\n",
" <td>99.969275</td>\n",
" <td>ED2023H</td>\n",
" <td>2019-04-01</td>\n",
" <td>2023-03-15</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Date Open High Low Close Volume Open_Interest \\\n",
"103056 2019-04-12 97.720 97.720 97.720 97.720 53472 379128 \n",
"103057 2019-04-12 97.695 97.695 97.695 97.695 53472 283435 \n",
"103058 2019-04-12 97.670 97.670 97.670 97.670 53472 198781 \n",
"103059 2019-04-12 97.640 97.640 97.640 97.640 53472 174926 \n",
"103068 2019-04-12 97.610 97.610 97.610 97.610 53472 103667 \n",
"\n",
" Return VAMI Contract Start End \n",
"103056 -0.000716 100.690366 ED2022H 2018-04-13 2022-03-15 \n",
"103057 -0.000716 100.700923 ED2022M 2018-07-02 2022-06-15 \n",
"103058 -0.000716 100.903972 ED2022U 2018-10-16 2022-09-15 \n",
"103059 -0.000665 100.236115 ED2022Z 2018-12-31 2022-12-15 \n",
"103068 -0.000665 99.969275 ED2023H 2019-04-01 2023-03-15 "
]
},
"execution_count": 80,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#inspect the master file\n",
"ed_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