def divide_into_tables():
symbols = get_symbols()
sql = 'select INSTRUMENT, SYMBOL, EXPIRY_DT, STRIKE_PR, OPTION_TYP, OPEN, HIGH, LOW, CLOSE, SETTLE_PR, CONTRACTS, VAL_INLAKH, OPEN_INT, CHG_IN_OI, TIMESTAMP, MONTH_CODE from fut_opt_hist where symbol="%s"'
for symbol in symbols.symbol:
db = MySQLdb.connect(config.host, config.user, config.password, "NSE")
data = pd.read_sql_query(sql % symbol, db)
try:
data.to_sql(symbol, db, flavor="mysql", if_exists="replace", chunksize=200)
except ValueError:
print symbol
continue
db.close()
def perform_calc_present():
symbols=get_symbols()
for symbol in symbols:
if symbol in config.symbols_table_not_created:
continue
print symbol.__str__()+"\n"
data=get_data_present(symbol)
if data.empty:
print symbol
continue
expiry,fut,option=filter_data(data)
if option.empty:
continue
option=implied_vol_calc(expiry, fut, option)
save_to_sql(option,symbol)
def perform_smile_skew_today():
db=MySQLdb.connect(config.host,config.user,config.password,'NSE')
cursor=db.cursor()
today=date.today().__str__()
symbols=get_symbols()
skew_present_data=pd.DataFrame(columns=["TIME","SYMBOL","ATM_VOL","SKEW","SMILE","PUT_CALL_RATIO"])
for symbol in symbols:
print symbol,today
data=get_opt_vol_data(symbol, today)
if data.empty:
continue
(atm_vol,skew,smile,put_call_ratio)=calculate_atm_skew_smile(data)
skew_present_data=skew_present_data.append({"TIME":today,"SYMBOL":symbol,"ATM_VOL":atm_vol,"SKEW":skew,"SMILE":smile,"PUT_CALL_RATIO":put_call_ratio},ignore_index=1)
cursor.execute('delete from ATM_SKEW_SMILE_HIST where TIME=CURDATE()')
skew_present_data.to_sql("ATM_SKEW_SMILE_HIST",db,flavor='mysql', if_exists='append', chunksize=200)
db.close()
def perform_calc_present():
symbols = get_symbols()
for symbol in symbols:
if symbol in config.symbols_table_not_created:
continue
print symbol.__str__() + "\n"
data = get_data_present(symbol)
if data.empty:
print symbol
continue
expiry, fut, option = filter_data(data)
if option.empty:
continue
option = implied_vol_calc(expiry, fut, option)
save_to_sql(option, symbol)
def perform_calc_hist():
symbols=get_symbols()
for symbol in symbols:
if symbol in config.symbols_table_not_created:
continue
dates=unique_date(symbol)
for d in dates.timestamp:
print symbol,d.__str__()+"\n"
data=get_data_history(symbol,d)
if data.empty:
print symbol
continue
expiry,fut,option=filter_data(data)
if option.empty:
continue
option=implied_vol_calc(expiry, fut, option)
save_to_sql(option)
def divide_into_tables():
symbols = get_symbols()
sql = 'select INSTRUMENT, SYMBOL, EXPIRY_DT, STRIKE_PR, OPTION_TYP, OPEN, HIGH, LOW, CLOSE, SETTLE_PR, CONTRACTS, VAL_INLAKH, OPEN_INT, CHG_IN_OI, TIMESTAMP, MONTH_CODE from fut_opt_hist where symbol="%s"'
for symbol in symbols.symbol:
db = MySQLdb.connect(config.host, config.user, config.password, 'NSE')
data = pd.read_sql_query(sql % symbol, db)
try:
data.to_sql(symbol,
db,
flavor='mysql',
if_exists='replace',
chunksize=200)
except ValueError:
print symbol
continue
db.close()
def perform_calc_hist():
symbols = get_symbols()
for symbol in symbols:
if symbol in config.symbols_table_not_created:
continue
dates = unique_date(symbol)
for d in dates.timestamp:
print symbol, d.__str__() + "\n"
data = get_data_history(symbol, d)
if data.empty:
print symbol
continue
expiry, fut, option = filter_data(data)
if option.empty:
continue
option = implied_vol_calc(expiry, fut, option)
save_to_sql(option)
def update_month_code():
symbols = get_symbols()
sql = 'update %s a,(select EXPIRY_DT,TIMESTAMP from fut_opt_hist where INSTRUMENT="FUTIDX" group by TIMESTAMP order by TIMESTAMP,EXPIRY_DT ) b\
set a.MONTH_CODE="1M" where\
a.EXPIRY_DT=b.EXPIRY_DT and a.TIMESTAMP=b.TIMESTAMP'
for symbol in symbols.symbol:
print symbol
db = MySQLdb.connect(config.host, config.user, config.password, "NSE")
cursor = db.cursor()
try:
cursor.execute(sql % (symbol))
except:
pass
finally:
db.commit()
db.close()
def update_month_code():
symbols = get_symbols()
sql = 'update %s a,(select EXPIRY_DT,TIMESTAMP from fut_opt_hist where INSTRUMENT="FUTIDX" group by TIMESTAMP order by TIMESTAMP,EXPIRY_DT ) b\
set a.MONTH_CODE="1M" where\
a.EXPIRY_DT=b.EXPIRY_DT and a.TIMESTAMP=b.TIMESTAMP'
for symbol in symbols.symbol:
print symbol
db = MySQLdb.connect(config.host, config.user, config.password, 'NSE')
cursor = db.cursor()
try:
cursor.execute(sql % (symbol))
except:
pass
finally:
db.commit()
db.close()
def create_fut_history():
db = MySQLdb.connect(config.host, config.user, config.password, "NSE")
try:
for symbol in get_symbols():
if symbol in config.symbols_table_not_created:
continue
print "Creating Future History for Symbol:" + symbol
data = pd.read_sql(
'select symbol,open,high,low,close,CONTRACTS,OPEN_INT,TIMESTAMP from %s where OPTION_TYP="XX" and MONTH_CODE="1M" '
% symbol,
db,
)
data.to_sql("FUT_HIST", db, flavor="mysql", if_exists="append", chunksize=200)
except:
pass
finally:
db.close()
def create_fut_history():
db = MySQLdb.connect(config.host, config.user, config.password, 'NSE')
try:
for symbol in get_symbols():
if symbol in config.symbols_table_not_created:
continue
print "Creating Future History for Symbol:" + symbol
data = pd.read_sql(
'select symbol,open,high,low,close,CONTRACTS,OPEN_INT,TIMESTAMP from %s where OPTION_TYP="XX" and MONTH_CODE="1M" '
% symbol, db)
data.to_sql('FUT_HIST',
db,
flavor='mysql',
if_exists='append',
chunksize=200)
except:
pass
finally:
db.close()
last_lema=data.LMA.values[-1]
last_sema=data.SMA.values[-1]
close=data.CLOSE.values[-1]
buydiff=(last_sema-last_lema*(1+threshold))/close
selldiff=(last_sema-last_lema*(1-threshold))/close
if (buydiff>0):
return ("Buy",buydiff)
elif (selldiff<0):
return ("Sell",selldiff)
else:
return ("Neutral",0)
if __name__=='__main__':
sma=11
lma=22
threshold=0.02
symbols=get_symbols()
result=pd.DataFrame()
for symbol in symbols:
data=get_symbol_future(symbol)
if data.empty:
continue
(signal,strength)=calc_ema_slow_fast(data, sma, lma, symbol, threshold)
#print symbol+"\t"+"Signal="+signal+"\n"
result=result.append({'SYMBOL':symbol,'SIGNAL':signal,'STRENGTH':strength},ignore_index=True)
result=result.sort(columns='STRENGTH',ascending=False)
print "Top Buyers are"
print result.head(5)
print "Top Sellers are"
print result.tail(5)