ETFArbTest

ETFArbTest.py

# -*- coding: utf-8 -*-
"""
Created on Tue May 26 11:07:33 2015

@author: assa
"""

import pandas as pd
import sqlite3 as lite

conn = lite.connect('TAQ_20150522.db')
sqltext = """ SELECT * From EquityTickData Where TAQ = 'Q' and Time betwee '09:00:00' and '14:50:00' """
df = pd.read_sql(sqltext, conn)

prevcloseprice_dict = {}
prevcloseprice_dict['A069500'] = 26500
prevcloseprice_dict['A102110'] = 26525
prevcloseprice_dict['A105190'] = 26575
prevcloseprice_dict['A114800'] = 7580
prevcloseprice_dict['A122630'] = 12045
prevcloseprice_dict['A123310'] = 8145
prevcloseprice_dict['A123320'] = 10815
prevcloseprice_dict['A145670'] = 9055


# %time 72ms + 21ms
prev_series_lst = [prevcloseprice_dict[item] for item in list(df['ShortCD'])]
df['PrevClose'] = prev_series_lst

# %time 2.63s
#df['PrevClose'] = df.apply(lambda row: prevcloseprice_dict[row['ShortCD']], axis=1)

df_kodex200 = df[df['ShortCD'] == 'A069500']
df_kodexinverse = df[df['ShortCD'] == 'A114800']
df_kodexleverage = df[df['ShortCD'] == 'A122630']


df_kodex200['Bid1'] = df_kodex200['Bid1'].astype(int)
df_kodex200['Ask1'] = df_kodex200['Ask1'].astype(int)

df_kodexinverse['Bid1'] = df_kodexinverse['Bid1'].astype(int)
df_kodexinverse['Ask1'] = df_kodexinverse['Ask1'].astype(int)

df_kodexleverage['Bid1'] = df_kodexleverage['Bid1'].astype(int)
df_kodexleverage['Ask1'] = df_kodexleverage['Ask1'].astype(int)



df_kodex200['Bid1_rate'] = df_kodex200['Bid1'] / df_kodex200['PrevClose'] - 1.0
df_kodex200['Ask1_rate'] = df_kodex200['Ask1'] / df_kodex200['PrevClose'] - 1.0

df_kodexinverse['Bid1_rate'] = df_kodexinverse['Bid1'] / df_kodexinverse['PrevClose'] - 1.0
df_kodexinverse['Ask1_rate'] = df_kodexinverse['Ask1'] / df_kodexinverse['PrevClose'] - 1.0


df_kodexleverage['Bid1_rate'] = (df_kodexleverage['Bid1'] / df_kodexleverage['PrevClose'] - 1.0) * .5
df_kodexleverage['Ask1_rate'] = (df_kodexleverage['Ask1'] / df_kodexleverage['PrevClose'] - 1.0) * .5


df_kodex200_tmp = df_kodex200[['Id', 'Time', 'ShortCD', 'Bid1', 'Ask1', 'Bid1_rate', 'Ask1_rate']]
df_kodexinverse_tmp = df_kodexinverse[['Id', 'Time', 'ShortCD', 'Bid1', 'Ask1', 'Bid1_rate', 'Ask1_rate']]
df_kodexleverage_tmp = df_kodexleverage[['Id', 'Time', 'ShortCD', 'Bid1', 'Ask1', 'Bid1_rate', 'Ask1_rate']]

df_test = df_kodex200_tmp.merge(df_kodexinverse_tmp, left_on=['Id', 'Time'], right_on=['Id', 'Time'], how = 'outer')

df_test = df_test.sort('Id')
df_test = df_test.reset_index(drop=True)

df_test = df_test.fillna(method='ffill')

df_test['Arb_Ask'] = df_test['Ask1_rate_x'] + df_test['Ask1_rate_y']
df_test['Arb_Bid'] = df_test['Bid1_rate_x'] + df_test['Bid1_rate_y']


df_test1 = df_kodexleverage_tmp.merge(df_kodexinverse_tmp, left_on=['Id', 'Time'], right_on=['Id', 'Time'], how = 'outer')

df_test1 = df_test1.sort('Id')
df_test1 = df_test1.reset_index(drop=True)

df_test1 = df_test1.fillna(method='ffill')

df_test1['Arb_Ask'] = df_test1['Ask1_rate_x'] + df_test1['Ask1_rate_y']
df_test1['Arb_Bid'] = df_test1['Bid1_rate_x'] + df_test1['Bid1_rate_y']

global upperbound
global lowerbound
global position
global max_position
global cash

upperbound = 0.0012
lowerbound = 0.0010
position = 0
max_position = 100

def signalfunc(row):
    global lowerbound
    global upperbound
    if row['Arb_Ask'] < lowerbound:
        return 1.0
    elif row['Arb_Bid'] > upperbound:
        return -1.0
    else:
        return 0.0

df_test['Signal'] = df_test.apply(signalfunc, axis=1)

def tradefunc(row):
    global position
    global max_position
    if row['Signal'] < 0 and position > 0:
        position -= 1
        return -1.0
    elif row['Signal'] > 0 and position < max_position:
        position += 1
        return 1.0
    else:
        return 0.0
        
df_test['Trade'] = df_test.apply(tradefunc, axis=1)

def cashfunc(row):
    global cash    
    if row['Trade'] > 0:
        cash += row['Arb_Ask'] * -1.0
    elif row['Trade'] < 0:
        cash += row['Arb_Bid']
    return cash 

df_test['Cash'] = df_test.apply(cashfunc, axis=1)

df_test['Position'] = df_test['Trade'].cumsum()
df_test['MTM'] = df_test['Position'] * df_test['Arb_Bid']
df_test['PnL'] = df_test['Cash'] + df_test['MTM']
df_test['Turnover'] = abs(df_test['Trade']).cumsum()