def store_orders_from_ib_to_db():
"""
Method to retrieve orders -everything from the last business day-, intended for batch usage
"""
log=logger("store_orders_from_ib_to_sqllite")
if dt.datetime.now().date() in utils.get_trading_close_holidays(dt.datetime.now().year):
log.info("This is a US Calendar holiday. Ending process ... ")
return
log.info("Getting orders data from IB ... ")
client = ib.IBClient()
clientid1 = int(globalconf.config['ib_api']['clientid_data'])
client.connect(clientid1=clientid1)
## Get the executions (gives you everything for last business day)
execlist = client.get_executions(10)
client.disconnect()
log.info("execlist length = [%d]" % ( len(execlist) ))
if execlist:
dataframe = pd.DataFrame.from_dict(execlist).transpose()
dataframe['current_date'] = dt.datetime.now().strftime('%Y%m%d')
dataframe['current_datetime'] = dt.datetime.now().strftime('%Y%m%d%H%M%S')
log.info("Appending orders to sqllite store ...")
dataframe=dataframe.sort_values(by=['account'])
dataframe.set_index(keys=['execid'], drop=True, inplace=True)
write_orders_to_sqllite(dataframe)
else:
log.info("No orders to append ...")
def store_etf_stocks_yahoo_to_db():
log = logger("yahoo etf stocks")
globalconf = config.GlobalConfig()
if dt.datetime.now().date() in utils.get_trading_close_holidays(dt.datetime.now().year):
log.info("This is a US Calendar holiday. Ending process ... ")
return
path = globalconf.config['paths']['data_folder']
optchain_def = globalconf.get_tickers_optchain_yahoo()
source1 = globalconf.config['use_case_yahoo_options']['source']
log.info("Getting etf stocks data from yahoo w pandas_datareader ... [%s] [%s]"
% (dt.datetime.now().strftime('%Y-%m-%d %H:%M:%S') , source1 ) )
wait_secs=10
for _,row in optchain_def.iterrows():
log.info("Init yahoo quotes downloader symbol=%s" % (row.symbol) )
db_file = get_market_db_file(db_type="underl_yhoo", expiry="NONE")
store = sqlite3.connect(path + db_file)
sql = "SELECT count(*) as count1 FROM sqlite_master WHERE type='table' AND name='" + str(row['symbol']) + "';"
tbl_exist = pd.read_sql_query(sql, store)['count1'][0]
log.info("Does this symbol has a table in sqlite? " + str(tbl_exist))
last_record_stored = None
if tbl_exist:
sql = "SELECT MAX(DATE) as max1 FROM " + str(row['symbol']) + " ;"
last_date = pd.read_sql_query( sql , store)['max1'][0]
log.info("Last date in data store for symbol " + row['symbol'] + " is " + str(last_date) )
if last_date is not None:
if dt.datetime.strptime(str(last_date), '%Y-%m-%d %H:%M:%S').date() == dt.datetime.today().date():
log.info("All data available is already in the store. ")
continue
last_record_stored = dt.datetime.strptime(str(last_date), '%Y-%m-%d %H:%M:%S') + dt.timedelta(days=1)
try:
df = web.DataReader(
name=row.symbol,
data_source=source1,
start=last_record_stored,
end=None,
retry_count=3,
pause=0.1
)
df['Quote_Time'] = dt.datetime.now()
df['Quote_Time_txt'] = df['Quote_Time'].dt.strftime("%Y-%m-%d %H:%M:%S")
df = df.reset_index().set_index("Quote_Time")
df['Expiry_txt'] = datetime.datetime(9999,12,31,23,59).strftime("%Y-%m-%d %H:%M:%S")
df['Symbol'] = row.symbol
count_row = df.shape[0]
log.info("Number of new rows [%s] " % (str(count_row)) )
write_market_data_to_sqllite(df, "underl_yhoo")
sleep(wait_secs)
log.info("sleeping [%s] secs ..." % (str(wait_secs)))
except (RemoteDataError,TypeError) as err:
log.info("No information for ticker [%s] Error=[%s] sys_info=[%s]" % (str(row.symbol) , str(err) , sys.exc_info()[0] ))
continue
except KeyError as e:
log.warn("KeyError raised [" + str(e) + "]...")
continue
def store_orders_from_ib_to_h5():
"""
Method to retrieve orders -everything from the last business day-, intended for batch usage
"""
log=logger("store_orders_from_ib_to_h5")
if dt.datetime.now().date() in misc_utilities.get_trading_close_holidays(dt.datetime.now().year):
log.info("This is a US Calendar holiday. Ending process ... ")
return
log.info("Getting orders data from IB ... ")
globalconf = config.GlobalConfig()
client = ib.IBClient()
clientid1 = int(globalconf.config['ib_api']['clientid_data'])
client.connect(clientid1=clientid1)
## Get the executions (gives you everything for last business day)
execlist = client.get_executions(10)
client.disconnect()
log.info("execlist length = [%d]" % ( len(execlist) ))
if execlist:
dataframe = pd.DataFrame.from_dict(execlist).transpose()
f = globalconf.open_orders_store()
dataframe['current_date'] = dt.datetime.now().strftime('%Y%m%d')
dataframe['current_datetime'] = dt.datetime.now().strftime('%Y%m%d%H%M%S')
log.info("Appending orders to HDF5 store ...")
# sort the dataframe
#dataframe.sort(columns=['account'], inplace=True) DEPRECATED
dataframe=dataframe.sort_values(by=['account'])
# set the index to be this and don't drop
dataframe.set_index(keys=['account'], drop=False, inplace=True)
# get a list of names
names = dataframe['account'].unique().tolist()
for name in names:
# now we can perform a lookup on a 'view' of the dataframe
log.info("Storing " + name + " in ABT ...")
joe = dataframe.loc[dataframe['account'] == name]
#joe.sort(columns=['current_datetime'], inplace=True) DEPRECATED
joe = joe.sort_values(by=['current_datetime'])
try:
f.append("/" + name, joe, data_columns=joe.columns)
except ValueError as e:
log.warn("ValueError raised [" + str(e) + "] Creating ancilliary file ...")
aux = globalconf.open_orders_store_value_error()
aux.append("/" + name, joe, data_columns=True)
aux.close()
f.close()
else:
log.info("No orders to append ...")
def store_optchain_yahoo_to_db():
globalconf = config.GlobalConfig()
optchain_def = globalconf.get_tickers_optchain_yahoo()
source1 = globalconf.config['use_case_yahoo_options']['source']
log = logger("yahoo options chain")
if dt.datetime.now().date() in utils.get_trading_close_holidays(dt.datetime.now().year):
log.info("This is a US Calendar holiday. Ending process ... ")
return
log.info("Getting options chain data from yahoo w pandas_datareader ... [%s]"
% (dt.datetime.now().strftime('%Y-%m-%d %H:%M:%S') ))
wait_secs=3
for _,row in optchain_def.iterrows():
log.info("Init yahoo quotes downloader symbol=%s" % (row.symbol) )
try:
option = web.YahooOptions(row.symbol)
# FIX "Yahoo Options has been immediately deprecated due to large breaks in the API"
# option = web.Options(symbol,source1)
for j in option.expiry_dates:
log.info("expiry=%s" % (str(j)))
try:
joe = pd.DataFrame()
call_data = option.get_call_data(expiry=j)
put_data = option.get_put_data(expiry=j)
if call_data.values.any():
joe = joe.append(call_data)
if put_data.values.any():
joe = joe.append(put_data)
joe = joe.sort_index()
joe['Quote_Time_txt'] = joe['Quote_Time'].dt.strftime("%Y-%m-%d %H:%M:%S")
joe['Last_Trade_Date_txt'] = joe['Last_Trade_Date'].dt.strftime("%Y-%m-%d %H:%M:%S")
joe = joe.reset_index().set_index("Quote_Time")
joe['Expiry_txt'] = joe['Expiry'].dt.strftime("%Y-%m-%d %H:%M:%S")
if 'JSON' in joe.columns:
joe['JSON'] = ""
write_market_data_to_sqllite(joe, "optchain_yhoo")
except KeyError as e:
log.warn("KeyError raised [" + str(e) + "]...")
sleep(wait_secs)
except (RemoteDataError,TypeError) as err:
log.info("No information for ticker [%s] Error=[%s] sys_info=[%s]" % (str(row.symbol) , str(err) , sys.exc_info()[0] ))
continue
def store_acc_summary_and_portfolio_from_ib_to_db():
""" Stores Snapshot Summary information about account and portfolio from IB into db """
log=logger("store_acc_summary_and_portfolio_from_ib_to_db")
if dt.datetime.now().date() in get_trading_close_holidays(dt.datetime.now().year):
log.info("This is a US Calendar holiday. Ending process ... ")
return
globalconf = config.GlobalConfig()
client = ib.IBClient()
clientid1 = int(globalconf.config['ib_api']['clientid_data'])
client.connect(clientid1=clientid1)
acclist, summarylist = read_acc_summary_and_portfolio_from_ib(client, globalconf, log)
log.info("acclist length [%d] " % ( len(acclist) ))
log.info("summarylist length [%d]" % ( len(summarylist) ))
if acclist:
dataframe = pd.DataFrame.from_dict(acclist).transpose()
dataframe['current_date'] = dt.datetime.now().strftime('%Y%m%d')
dataframe['current_datetime'] = dt.datetime.now().strftime('%Y%m%d%H%M%S')
dataframe.sort_values(by=['current_datetime'], inplace=True)
dataframe.index=dataframe['current_datetime']
dataframe.drop('current_datetime',axis=1,inplace=True)
dataframe.drop('multiplier', axis=1, inplace=True)
write_portfolio_to_sqllite(dataframe)
else:
log.info("Nothing to append to sqlite ... ")
if summarylist:
dataframe2 = pd.DataFrame.from_dict(summarylist).transpose()
# print("dataframe = ",dataframe)
dataframe2['current_date'] = dt.datetime.now().strftime('%Y%m%d')
dataframe2['current_datetime'] = dt.datetime.now().strftime('%Y%m%d%H%M%S')
dataframe2.sort_values(by=['current_datetime'], inplace=True)
dataframe2.index=pd.to_datetime(dataframe2['current_datetime'], format="%Y%m%d%H%M%S")
dataframe2.drop('current_datetime',axis=1,inplace=True)
dataframe2['current_datetime_txt'] = dataframe2.index.strftime("%Y-%m-%d %H:%M:%S")
dataframe2.drop('Guarantee_C_USD', axis=1, inplace=True)
write_acc_summary_to_sqllite(dataframe2)
else:
log.info("Nothing to append to HDF5 ... ")
client.disconnect()
def run_reader(now1 = None):
"""
Run with "NA" string to get today's
:param now1:
:return:
"""
locale.setlocale(locale.LC_ALL, 'en_US.UTF-8')
now = dt.datetime.now() # + dt.timedelta(days=-4)
mydate = utils.expiry_date(now1)
if mydate:
now = mydate
weekday = now.strftime("%A").lower()
log.info(("Getting data from www.itpm.com ... ",now))
if ( weekday in ("saturday","sunday") or
now.date() in utils.get_trading_close_holidays(dt.datetime.now().year)):
log.info("This is a US Calendar holiday or weekend. Ending process ... ")
return
base = "https://www.itpm.com/wraps_post/"
month = now.strftime("%B").lower() # Changed format update 27DEC2019 nov --> november
#daymonth = now.strftime("%d").zfill(2)
# Fix URL change format: without leading zeroes
daymonth = str(now.day)
year = now.strftime("%Y")
wrapTypes = ("opening","closing")
globalconf = config.GlobalConfig()
# example https://www.itpm.com/wraps_post/closing-wrap-friday-nov-17-2017/
# Changed format update 27DEC2019: https://www.itpm.com/wraps_post/closing-wrap-friday-december-20-2019/
for wrapType in wrapTypes:
url = base + wrapType + "-wrap-" + weekday + "-" + month + "-" + daymonth + "-" + year + "/"
log.info ('[%s] Downloading: %s' % (str(now) , url ) )
b_html= download(url=url, log=log,
user_agent="Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.102 Safari/537.36"
)
if b_html is None:
continue
soup = BeautifulSoup(b_html, 'html.parser')
fixed_html = soup.prettify()
#print(fixed_html)
#ul = soup.find_all('p', attrs={'class':'s2'})
wrap = {
'datetime': now,
'type': wrapType
}
ul = soup.find_all('p')
type(ul)
#n =1
for i in ul:
#print((str(i.text),n))
if "European Equity Markets" in str(i.text):
wrap.update({"euro_equity_comment" : str(i.text)})
wrap.update({"euro_equity_sentiment": get_sentiment(str(i.text)) })
elif "Currency Markets" in str(i.text):
wrap.update({"fx_comment" : str(i.text)})
wrap.update({"fx_comment_sentiment": get_sentiment(str(i.text))})
elif "Commodities Markets" in str(i.text):
wrap.update({"commodities_comment" : str(i.text)})
wrap.update({"commodities_sentiment": get_sentiment(str(i.text))})
elif "US Equity Markets" in str(i.text):
wrap.update({"us_equity_comment" : str(i.text)})
wrap.update({"us_equity_sentiment": get_sentiment(str(i.text))})
elif "Bond Markets" in str(i.text):
wrap.update({"bonds_comment" : str(i.text)})
wrap.update({"bonds_sentiment": get_sentiment(str(i.text))})
#n=n+1
#print(wrap)
da.save_docs_to_db(globalconf, log, wrap, collection_name="itrm-wraps")
def run_reader():
globalconf = config.GlobalConfig()
log = globalconf.log
optchain_def = globalconf.get_tickers_optchain_ib()
source1 = globalconf.config['use_case_ib_options']['source']
if dt.datetime.now().date() in utils.get_trading_close_holidays(
dt.datetime.now().year):
log.info("This is a US Calendar holiday. Ending process ... ")
return
log.info("Getting realtime option chains data from IB ... ")
underl = {}
for index, row in optchain_def.iterrows():
log.info("underl=[%s] [%s] [%s] [%s] [%s] [%d]" %
(row['symbol'], row['type'], str(row['Expiry']),
row['Exchange'], row['Currency'], int(index)))
underl.update({
int(index):
RequestOptionData(row['symbol'], row['type'], str(row['Expiry']),
0, '', '', row['Exchange'], row['Currency'],
int(index))
})
underl_under = {}
for index, row in optchain_def.iterrows():
log.info("underl_under=[%s] [%s] [%s] [%s] [%s] [%d]" %
(row['symbol'], row['underl_type'], str(row['underl_expiry']),
row['underl_ex'], row['underl_curr'], int(index)))
underl_under.update({
int(index):
RequestUnderlyingData(row['symbol'], row['underl_type'],
str(row['underl_expiry']), 0, '', '',
row['underl_ex'], row['underl_curr'],
int(index))
})
client = ib.IBClient()
clientid1 = int(globalconf.config['ib_api']['clientid_data'])
client.connect(clientid1=clientid1)
#causa error en ubuntu : porque pone a isDOne la primera ::
# print("Get time from server [%s] isConnected? [%s] " % ( str(client.getTime()) , str(client.isConnected() ) ) )
underl_prc = client.getMktData(underl_under)
row1 = 0
opt_chain_ranges = {}
for reqId, request in underl_prc.items():
if reqId in request.get_out_data().keys():
row1 += 1
if "closePrice" in request.get_out_data()[reqId]:
log.info("Requestid [%d]: Option[%s] Results length [%d]" %
(reqId, str(request.get_in_data()),
len(request.get_out_data())))
opt_chain_ranges.update({
request.get_in_data()["symbol"]:
request.get_out_data()[reqId]["closePrice"]
})
#print ("Requestid [%d]: modelVega=%0.5f" % ( reqId, request.get_out_data()[reqId]['modelVega'] ) )
log.info("opt_chain_ranges = [%s] " % (str(opt_chain_ranges)))
# get options chain
list_results = client.getOptionsChain(underl)
log.info("Number of requests [%d]" % (len(list_results)))
for reqId, request in list_results.items():
log.info(
"Requestid [%d]: Option[%s] Len Results [%d]" %
(reqId, str(request.get_in_data()), len(request.optionsChain)))
contr = {}
num = 100
pct_range_opt_chain = float(
globalconf.config['ib_api']['pct_range_opt_chain'])
for reqId, request in list_results.items():
#print ("Requestid [%d]: Chain size [%d] detail [%s]"
# % ( reqId , len( request.optionsChain ) , str(request.optionsChain) ))
for opt1 in request.optionsChain:
if opt1["symbol"] in opt_chain_ranges:
filtro_strikes = opt_chain_ranges[opt1["symbol"]]
#print ("filtro_strikes = " , filtro_strikes , "opt1 = " , opt1 )
if opt1["strike"] >= (filtro_strikes * ( 1 - pct_range_opt_chain ) ) \
and opt1["strike"]<= (filtro_strikes * ( 1 + pct_range_opt_chain ) ):
contr[num] = RequestOptionData(
opt1["symbol"], opt1["secType"], opt1["expiry"],
opt1["strike"], opt1["right"], opt1["multiplier"],
opt1["exchange"], opt1["currency"], num)
num += 1
list_results2 = client.getMktData(contr)
client.disconnect()
dataframe = pd.DataFrame()
row1 = 0
for reqId, request in list_results2.items():
row1 += 1
#print ("Requestid [%d]: Option[%s] Results [%s]" % ( reqId , str(request.get_in_data()), str(request.get_out_data()) ))
#print ("Requestid [%d]: modelVega=%0.5f" % ( reqId, request.get_out_data()[reqId]['modelVega'] ) )
dict1 = request.get_in_data().copy()
if reqId in request.get_out_data():
dict1.update(request.get_out_data()[reqId])
for key in dict1.keys():
dataframe.loc[row1, key] = dict1[key]
log.info(str(list(dataframe.columns.values)))
dataframe['current_date'] = dt.datetime.now().strftime('%Y%m%d')
dataframe['current_datetime'] = dt.datetime.now().strftime('%Y%m%d%H%M%S')
#dataframe.to_hdf(f,"IB_IDX",format='table')
# c_day da un warning
# NaturalNameWarning: object name is not a valid Python identifier: '9'; it does not match the pattern
# ``^[a-zA-Z_][a-zA-Z0-9_]*$``; you will not be able to use natural naming to access this object; using
# ``getattr()`` will still work, though NaturalNameWarning)
#f.append( c_year+"/"+c_month+"/"+c_day+"/"+c_hour+"/"+c_minute , dataframe, data_columns=dataframe.columns)
#f.close() # Close file
# sort the dataframe
dataframe = dataframe[dataframe.current_datetime.notnull()]
types = dataframe.apply(lambda x: pd.lib.infer_dtype(x.values))
# print str(types)
for col in types[types == 'floating'].index:
dataframe[col] = dataframe[col].map(lambda x: np.nan
if x > 1e200 else x)
dataframe['index'] = dataframe['current_datetime'].apply(
lambda x: dt.datetime.strptime(x, '%Y%m%d%H%M%S'))
dataframe = dataframe.sort_values(by=['index'], inplace=False)
# set the index to be this and don't drop
dataframe.set_index(keys=['index'], drop=True, inplace=True)
# get a list of names
names = dataframe['symbol'].unique().tolist()
for name in names:
# now we can perform a lookup on a 'view' of the dataframe
log.info("Storing " + name + " in ABT ...")
joe = dataframe[dataframe.symbol == name]
joe = joe.sort_values(
by=['symbol', 'current_datetime', 'expiry', 'strike', 'right'])
# joe.to_excel(name+".xlsx")
write_market_data_to_sqllite(joe, "optchain_ib")