def run_volest_report(today_txt): """ today_txt '%Y%m%d' """ log = logger("Volest report ...") globalconf = config.GlobalConfig() if today_txt is None: today = dt.date.today() last_date = today.strftime('%Y%m%d') else: last_date = today_txt vol = volest.VolatilityEstimator(db_type="underl_ib_hist", symbol="SPY", expiry=None, last_date=last_date, num_days_back=200, resample="1D", estimator="GarmanKlass", clean=True) window = 30 windows = [30, 60, 90, 120] quantiles = [0.25, 0.75] bins = 100 normed = True # vol.term_sheet_to_png(window, windows, quantiles, bins, normed) # vol.term_sheet_to_json(window, windows, quantiles, bins, normed) # vol.term_sheet_to_html(window, windows, quantiles, bins, normed) #vol.term_sheet_to_db(window, windows, quantiles, bins, normed) # p = vol.cones_bokeh(windows=windows, quantiles=quantiles) vol.cones_data(windows=windows, quantiles=quantiles)
def test_get_market_db_file(self): globalconf = config.GlobalConfig() db_type = "optchain_ib" expiry = "222222" db_file = get_market_db_file(db_type, expiry) print(db_file) self.assertEqual(db_file, "KKKK")
def test_volestimator(self): import core.vol_estimators as volest log = logger("Testing ...") globalconf = config.GlobalConfig() today = dt.date.today() vol = volest.VolatilityEstimator(db_type="underl_ib_hist", symbol="AAPL", expiry=None, last_date=today.strftime('%Y%m%d'), num_days_back=200, resample="1D", estimator="GarmanKlass", clean=True) #fig, plt = vol.cones(windows=[30, 60, 90, 120], quantiles=[0.25, 0.75]) #plt.show() window = 30 windows = [30, 60, 90, 120] quantiles = [0.25, 0.75] bins = 100 normed = True bench = 'SPY' # creates a pdf term sheet with all metrics vol.term_sheet_to_pdf(window, windows, quantiles, bins, normed, bench) # vol.term_sheet_to_png(window, windows, quantiles, bins, normed, bench) # vol.term_sheet_to_html(window, windows, quantiles, bins, normed, bench) self.assertEqual(100, 100)
def run(): """ Used as command to consolidate in the main h5 anciliary h5 generated due to column length exceptions """ globalconf = config.GlobalConfig() path = globalconf.config['paths']['data_folder'] log = logger("fix_h5 optchain") os.chdir(path) if not os.path.exists(path + "/optchain_ib_backups"): os.makedirs(path + "/optchain_ib_backups") optchain_orig = 'optchain_ib_hist_db.h5' pattern_optchain = 'optchain_ib_hist_db.h5*' optchain_out = 'optchain_ib_hist_db_complete.h5' lst1 = glob.glob(pattern_optchain) lst1.remove(optchain_orig) if not lst1: log.info("No ancilliary files to append, exiting ... ") return log.info(("List of ancilliary files that will be appended: ", lst1)) dataframe = pd.DataFrame() for x in lst1: store_in1 = pd.HDFStore(path + x) root1 = store_in1.root log.info(("Root pathname of the input store: ", root1._v_pathname)) for lvl1 in root1: log.info(("Level 1 pathname in the root if the H5: ", lvl1._v_pathname)) if lvl1: df1 = store_in1.select(lvl1._v_pathname) dataframe = dataframe.append(df1) log.info(("Store_in1", len(df1), x)) store_in1.close() os.rename(path + x, path + "/optchain_ib_backups/" + x) store_in1 = pd.HDFStore(path + optchain_orig) store_out = pd.HDFStore(path + optchain_out) root1 = store_in1.root root2 = store_out.root log.info(("Root pathname of the input store: ", root1._v_pathname)) log.info(("Root pathname of the output store: ", root2._v_pathname)) for lvl1 in root1: print (lvl1._v_pathname) if lvl1: df1 = store_in1.select(lvl1._v_pathname) dataframe = dataframe.append(df1) log.info(("Store_in1 length and name", len(df1), optchain_orig)) store_in1.close() os.rename(path + optchain_orig, path + "/optchain_ib_backups/" + datetime.now().strftime('%Y%m%d%H%M%S') + optchain_orig) dataframe.sort_index(inplace=True,ascending=[True]) names = dataframe['symbol'].unique().tolist() for name in names: print ("Storing " + name + " in ABT ..." + str(len(dataframe))) joe = dataframe[dataframe.symbol == name] joe=joe.sort_values(by=['symbol', 'current_datetime', 'expiry', 'strike', 'right']) store_out.append("/" + name, joe, data_columns=True,min_itemsize={'comboLegsDescrip': 25}) store_out.close() os.rename(path + optchain_out, path + optchain_orig)
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 test_get_expiries(self): from persist.sqlite_methods import get_expiries import core.config as config globalconf = config.GlobalConfig() list = get_expiries(dsId='optchain_ib_exp', symbol="SPY") print(max(list)) self.assertEqual(max(list), '2017-09')
def migrate_h5_to_sqllite_optchain(hdf5_pattern, h5_db_alias, drop_expiry, filter_symbol): """ """ globalconf = config.GlobalConfig() log = logger("migrate_h5_to_sqllite_optchain") path = globalconf.config['paths']['data_folder'] lst1 = glob.glob(path + hdf5_pattern) if not lst1: log.info("No h5 files to append ... ") else: log.info(("List of h5 files that will be appended: ", lst1)) time.sleep(1) try: input("Press Enter to continue...") except SyntaxError: pass for hdf5_path in lst1: store_file = pd.HDFStore(hdf5_path) root1 = store_file.root # todos los nodos hijos de root que son los underlying symbols list = [x._v_pathname for x in root1] log.info(("Processing file: ", hdf5_path)) # only migrate the symbol indicated if available if filter_symbol != "ALL": list = [filter_symbol] store_file.close() log.info(("List of symbols: " + str(list))) for symbol in list: store_file = pd.HDFStore(hdf5_path) node1 = store_file.get_node(symbol) if node1: log.info(("Symbol: " + symbol)) # Unfortunately th JSON field doesnt contain more info that the already present fields # df1['json_dict'] = df1['JSON'].apply(CustomParser) # following line converts dict column into n columns for the dataframe: # https://stackoverflow.com/questions/20680272/reading-a-csv-into-pandas-where-one-column-is-a-json-string # df1 = pd.concat([df1.drop(['json_dict','JSON'], axis=1), df1['json_dict'].apply(pd.Series)], axis=1) # df1 = df1.drop(['JSON'], axis=1) # this is a specifc case for underlying hisotry if symbol == "/ES" and h5_db_alias == "underl_ib_hist": for lvl1 in node1: log.info(("Level 1 pathname in the root if the H5: ", lvl1._v_pathname)) if lvl1: df1 = store_file.select(lvl1._v_pathname) df1['expiry'] = lvl1._v_pathname mkt.write_market_data_to_sqllite(df1, h5_db_alias) else: df1 = store_file.select(node1._v_pathname) # Expiry is already in the index if drop_expiry == True: df1 = df1.drop(['Expiry'], axis=1) mkt.write_market_data_to_sqllite(df1, h5_db_alias) store_file.close()
def consolidate_anciliary_h5_account(): """ Used as command to consolidate in the main h5 anciliary h5 generated due to column length exceptions """ globalconf = config.GlobalConfig(level=logger.DEBUG) log = globalconf.log path = globalconf.config['paths']['data_folder'] os.chdir(path) if not os.path.exists(path + "/account_backups"): os.makedirs(path + "/account_backups") acc_orig = 'account_db_new.h5' pattern_acc = 'account_db_new.h5*' acc_out = 'account_db_complete.h5' lst1 = glob.glob(pattern_acc) lst1.remove(acc_orig) if not lst1: log.info("No ancilliary files to append, exiting ... ") return log.info(("List of ancilliary files that will be appended: ", lst1)) dataframe = pd.DataFrame() for x in lst1: store_in1 = pd.HDFStore(path + x) root1 = store_in1.root log.info(("Root pathname of the input store: ", root1._v_pathname)) for lvl1 in root1: log.info(("Level 1 pathname in the root if the H5: ", lvl1._v_pathname)) if lvl1: df1 = store_in1.select(lvl1._v_pathname) dataframe = dataframe.append(df1) log.info(("Store_in1", len(df1), x)) store_in1.close() os.rename(path + x, path + "/account_backups/" + x) store_in1 = pd.HDFStore(path + acc_orig) store_out = pd.HDFStore(path + acc_out) root1 = store_in1.root log.info(("Root pathname of the input store: ", root1._v_pathname)) root2 = store_out.root log.info(("Root pathname of the output store: ", root2._v_pathname)) for lvl1 in root1: print (lvl1._v_pathname) if lvl1: df1 = store_in1.select(lvl1._v_pathname) dataframe = dataframe.append(df1) log.info(("Store_in1 length and name", len(df1), acc_orig)) store_in1.close() os.rename(path + acc_orig, path + "/account_backups/" + datetime.now().strftime('%Y%m%d%H%M%S') + acc_orig) dataframe.sort_index(inplace=True,ascending=[True]) write_acc_summary_to_h5(globalconf, log, dataframe, store_out) store_out.close() os.rename(path + acc_out, path + acc_orig)
def write_momentum_to_sqllite(dataframe,table): """ Write to sqllite the momentum snapshot passed as argument """ log.info("Appending momentum data [" +table+ "] to sqllite ... " ) globalconf = config.GlobalConfig() db_file = globalconf.config['sqllite']['momentum_db'] path = globalconf.config['paths']['data_folder'] store = sqlite3.connect(path + db_file) dataframe.to_sql(table, store, if_exists='append') store.close()
def test_market_data_from_sqllite(self): log = logger("Testing ...") globalconf = config.GlobalConfig() df = read_market_data_from_sqllite(db_type="underl_ib_hist", symbol="USO", expiry=None, last_date="20170623", num_days_back=50, resample="1D") print(df) self.assertEqual(len(df), 100)
def run_trend_report(today_txt): log = logger("Trend report ...") globalconf = config.GlobalConfig() if today_txt is None: today = dt.date.today() last_date = today.strftime('%Y%m%d') else: last_date = today_txt am.graph_coppock(symbol="SPY", period="1D") am.graph_emas(symbol="SPY") am.graph_volatility(symbol="SPY") am.graph_fast_move(symbol="SPY")
def test_get_optchain_datasources(self): from persist.sqlite_methods import get_optchain_datasources import core.config as config globalconf = config.GlobalConfig() dict = get_optchain_datasources() print(dict['optchain_ib_exp']['SPY']['expiries']) print(dict['optchain_ib_exp']['IWM']['expiries']) self.assertListEqual(dict['optchain_ib_exp']['IWM']['expiries'], ['2016-09', '2016-08'])
def test_graphs(self): globalconf = config.GlobalConfig() from core import analytics_methods as am # am.print_coppock_diario(symbol="SPX",period="1D") # p = am.graph_coppock(symbol="SPX",period="1D") # p = am.graph_emas(symbol="SPY") # p = am.graph_volatility(symbol="SPY") # p = am.graph_fast_move(symbol="SPY") # show(p) am.graph_volatility_cone(symbol='SPY') # print (md.get_datasources(globalconf)) self.assertGreater(1, 0)
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 consolidate_anciliary_h5_orders(): globalconf = config.GlobalConfig() path = globalconf.config['paths']['data_folder'] # path = '/home/david/data/' # http://stackoverflow.com/questions/2225564/get-a-filtered-list-of-files-in-a-directory os.chdir(path) orders_orig = 'orders_db.h5' pattern_orders = 'orders_db.h5*' orders_out = 'orders_db_new.h5' lst1 = glob.glob(pattern_orders) lst1.remove(orders_orig) print (lst1) dataframe = pd.DataFrame() for x in lst1: store_in1 = pd.HDFStore(path + x) root1 = store_in1.root print (root1._v_pathname) for lvl1 in root1: print (lvl1._v_pathname) if lvl1: df1 = store_in1.select(lvl1._v_pathname) dataframe = dataframe.append(df1) print ("store_in1", len(df1), x) store_in1.close() store_in1 = pd.HDFStore(path + orders_orig) store_out = pd.HDFStore(path + orders_out) root1 = store_in1.root print (root1._v_pathname) for lvl1 in root1: print (lvl1._v_pathname) if lvl1: df1 = store_in1.select(lvl1._v_pathname) dataframe = dataframe.append(df1) print ("store_in1", len(df1), orders_orig) store_in1.close() dataframe.sort_index(inplace=True,ascending=[True]) names = dataframe['account'].unique().tolist() for name in names: print ("Storing " + name + " in ABT ..." + str(len(dataframe))) joe = dataframe[dataframe.account == name] joe=joe.sort_values(by=['current_datetime']) store_out.append("/" + name, joe, data_columns=True) store_out.close()
def __init__(self): """ Connection to the IB API """ # Creation of Connection class object = config.GlobalConfig() port1 = int(object.config['ib_api']['port']) host1 = str(object.config['ib_api']['host']) clientid1 = int(object.config['ib_api']['clientid_data']) print("Calling connection port=%d host=%s clientid=%d" % (port1, host1, clientid1)) self.connection = ibConnection(port=port1, host=host1, clientId=clientid1) # Register data handlers self.connection.registerAll(self.process_messages) # Connect self.connection.connect()
def write_portfolio_to_sqllite(dataframe): """ Write to sqllite the portfolio snapshot passed as argument """ log.info("Appending portfolio data to sqllite ... ") globalconf = config.GlobalConfig() db_file = globalconf.config['sqllite']['portfolio_db'] path = globalconf.config['paths']['data_folder'] store = sqlite3.connect(path + db_file) names=dataframe['accountName'].unique().tolist() for name in names: joe = dataframe.loc[dataframe['accountName']==name] # include this field which is sometimes used (options) to be used if 'multiplier' not in joe.columns: joe['multiplier'] = "" joe.to_sql(name, store, if_exists='append') store.close()
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 test_extrae_detalle_operaciones(self): from persist.portfolio_and_account_data_methods import extrae_detalle_operaciones import datetime as dt symbol = "SPY" expiry = "20170317" secType = "OPT" import core.config as config globalconf = config.GlobalConfig() accountid = globalconf.get_accountid() fecha_valoracion = dt.datetime.now( ) # dt.datetime(year=2017,month=2,day=2) # scenarioMode = "N" simulName = "spy0317dls" df1 = extrae_detalle_operaciones(valuation_dttm=fecha_valoracion, symbol=symbol, expiry=expiry, secType=secType, accountid=accountid, scenarioMode=scenarioMode, simulName=simulName) print(df1)
def migrate_h5_to_sqllite_portfolio(): """ migrate_h5_to_sqllite_portfolio """ hdf5_pattern = "portfolio_db*.h5*" globalconf = config.GlobalConfig() log = logger("migrate_h5_to_sqllite_portfolio") path = globalconf.config['paths']['data_folder'] lst1 = glob.glob(path + hdf5_pattern) if not lst1: log.info("No h5 files to append ... ") else: log.info(("List of h5 files that will be appended: ", lst1)) time.sleep(1) try: input("Press Enter to continue...") except SyntaxError: pass for hdf5_path in lst1: store_file = pd.HDFStore(hdf5_path) root1 = store_file.root # todos los nodos hijos de root que son los account ids list = [x._v_pathname for x in root1] log.info(("Root pathname of the input store: ", root1._v_pathname)) store_file.close() log.info(("List of account ids: " + str(list))) for accountid in list: store_file = pd.HDFStore(hdf5_path) node1 = store_file.get_node(accountid) if node1: log.info(("accountid: " + accountid)) df1 = store_file.select(node1._v_pathname) df1.set_index(keys=['conId'], drop=True, inplace=True) persist.sqlite_methods.write_portfolio_to_sqllite(df1) store_file.close()
def consolidate_anciliary_h5_portfolio(): """ Used as command to consolidate in the main h5 anciliary h5 generated due to column length exceptions """ globalconf = config.GlobalConfig() log = logger("consolidate_anciliary_h5_portfolio") path = globalconf.config['paths']['data_folder'] os.chdir(path) if not os.path.exists(path + "/portfolio_backups"): os.makedirs(path + "/portfolio_backups") port_orig = 'portfolio_db.h5' pattern_port = 'portfolio_db.h5*' port_out = 'portfolio_db_complete.h5' lst1 = glob.glob(pattern_port) lst1.remove(port_orig) dataframe = pd.DataFrame() old_format = False if not lst1: log.info("No ancilliary files to append ... ") else: log.info(("List of ancilliary files that will be appended: ", lst1)) for x in lst1: store_in1 = pd.HDFStore(path + x) root1 = store_in1.root log.info(("Root pathname of the input store: ", root1._v_pathname)) for lvl1 in root1: log.info(("Level 1 pathname in the root if the H5: ", x, lvl1._v_pathname)) if lvl1: try: df1 = store_in1.select(lvl1._v_pathname) dataframe = dataframe.append(df1) log.info(("Store_in1", len(df1), x)) except (TypeError) as e: log.info("This is the old format of the portfolio file...") old_format = True break if old_format: for lvl1 in root1: for lvl2 in store_in1.get_node(lvl1._v_pathname): for lvl3 in store_in1.get_node(lvl2._v_pathname): for lvl4 in store_in1.get_node(lvl3._v_pathname): for lvl5 in store_in1.get_node(lvl4._v_pathname): log.info(("Pathname level 5: ", x, lvl5._v_pathname)) if lvl5: df1 = store_in1.select(lvl5._v_pathname) dataframe = dataframe.append(df1) store_in1.close() os.rename(path + x, path + "/portfolio_backups/" + x) store_in1 = pd.HDFStore(path + port_orig) store_out = pd.HDFStore(path + port_out) root1 = store_in1.root root2 = store_out.root old_format = False log.info(("Root pathname of the input store: ", root1._v_pathname, " and output the store: ", root2._v_pathname)) for lvl1 in root1: log.info(("Level 1 pathname in the root if the H5: ", port_orig, lvl1._v_pathname)) if lvl1: try: df1 = store_in1.select(lvl1._v_pathname) dataframe = dataframe.append(df1) log.info(("Store_in1", len(df1), port_orig)) except (TypeError) as e: log.info("This is the old format of the portfolio file...") old_format = True break if old_format: for lvl1 in root1: for lvl2 in store_in1.get_node(lvl1._v_pathname): for lvl3 in store_in1.get_node(lvl2._v_pathname): for lvl4 in store_in1.get_node(lvl3._v_pathname): for lvl5 in store_in1.get_node(lvl4._v_pathname): log.info(("Pathname level 5: ", port_orig, lvl5._v_pathname)) if lvl5: df1 = store_in1.select(lvl5._v_pathname) dataframe = dataframe.append(df1) store_in1.close() os.rename(path + port_orig, path + "/portfolio_backups/" + datetime.now().strftime('%Y%m%d%H%M%S') + port_orig) dataframe.sort_values(by=['current_datetime'], inplace=True) dataframe = dataframe.dropna(subset=['current_datetime']) dataframe.drop('multiplier', axis=1, inplace=True) # dataframe.drop('multiplier', axis=1, inplace=True) write_portfolio_to_h5(globalconf, log, dataframe, store_out) store_out.close() os.rename(path + port_out, path + port_orig)
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 https://www.barchart.com/stocks/momentum ... ", 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.barchart.com/stocks/momentum" globalconf = config.GlobalConfig() url = base 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 not b_html is None: soup = BeautifulSoup(b_html, 'html.parser') fixed_html = soup.prettify() tables = soup.find_all('table') # Summary Of Stocks With New Highs And Lows table = tables[1] df1 = pd.read_html(str(table))[0] #df1.columns = df1.columns.astype(str) #df1 = df1.add_prefix('data_') df1.columns = [ 'description', 'data_5days', 'data_1month', 'data_3months', 'data_6months', 'data_52weeks', 'data_ytd' ] df1 = df1.replace(np.nan, 'Default', regex=True) df1['load_dttm'] = now df1.set_index(keys=['load_dttm'], drop=True, inplace=True) sql.write_momentum_to_sqllite(df1, "stocks_hi_lo_summary") # Market Performance Indicator # The percentage of stocks in $BCMM above their individual Moving Average per period. # # Barchart Market Momentum Index ($BCMM) is an exclusive index used as an indicator of change # in overall markets. It reflects the movement of stocks who fit the following criteria: must have # current SEC filings, must have traded for a minimum of 6-months, and must be trading above $2. table = tables[2] df2 = pd.read_html(str(table))[0] #df1.columns = df1.columns.astype(str) #df1 = df1.add_prefix('data_') df2.columns = [ 'description', '5day_ma', '20day_ma', '50day_ma', '100day_ma', '150day_ma', '200day_ma' ] df2 = df2.replace(np.nan, 'Default', regex=True) df2['load_dttm'] = now df2.set_index(keys=['load_dttm'], drop=True, inplace=True) sql.write_momentum_to_sqllite(df2, "stocks_bcmm_above_ma_summary")
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")
def store_underlying_ib_to_db(): log=logger("historical data loader") log.info("Getting historical underlying data from IB ... ") globalconf = config.GlobalConfig() path = globalconf.config['paths']['data_folder'] underly_def = globalconf.get_tickers_historical_ib() client = IBClient() clientid1 = int(globalconf.config['ib_api']['clientid_data']) client.connect(clientid1=clientid1) dt_now=dt.datetime.now() endDateTime = dt_now.strftime('%Y%m%d %H:%M:%S') # lo mas que se puede pedir para barras de 30 min es un mes historico # barSizeSetting = "30 mins" barSizeSetting = "1 min" whatToShow = "TRADES" useRTH = 1 formatDate = 1 wait_secs = 40 db_file = get_market_db_file(db_type="underl_ib_hist", expiry="NONE") store = sqlite3.connect(path + db_file) for index, row_req in underly_def.iterrows(): log.info("underl=[%s] [%s] [%s] [%d] [%s] [%s] [%s] [%s] [%d]" % ( str(row_req['symbol']), str(row_req['underl_type']), str(row_req['underl_expiry']), 0, '', '', str(row_req['underl_ex']), str(row_req['underl_curr']), int(index) ) ) ticker = RequestUnderlyingData(str(row_req['symbol']), str(row_req['underl_type']), str(row_req['underl_expiry']), 0, '', '', str(row_req['underl_ex']), str(row_req['underl_curr']), int(index)) symbol_expiry = "/" + str(row_req['symbol']) + "/" + str(row_req['underl_expiry']) sql = "SELECT count(*) as count1 FROM sqlite_master WHERE type='table' AND name='" + str(row_req['symbol']) + "';" tbl_exist = pd.read_sql_query(sql, store)['count1'][0] log.info("Does this symbol has a table in sqlite? " + str(tbl_exist)) if tbl_exist: if int(row_req['underl_expiry']) > 0: sql = "SELECT MAX(DATE) as max1 FROM " + str(row_req['symbol']) +" WHERE EXPIRY = '" + symbol_expiry + "' ;" else: sql = "SELECT MAX(DATE) as max1 FROM " + str(row_req['symbol']) + " ;" last_date = pd.read_sql_query( sql , store)['max1'][0] log.info("Last date in data store for symbol " + symbol_expiry + " is " + str(last_date) ) if last_date is not None: last_record_stored = dt.datetime.strptime(str(last_date), '%Y%m%d %H:%M:%S') # no se debe usar .seconds que da respuesta incorrecta debe usarse .total_seconds() # days= int(round( (dt_now - last_record_stored).total_seconds() / 60 / 60 / 24 ,0)) # lo anterior no sirve porque debe considerarse la diferencia en business days # days = np.busday_count(last_record_stored.date(), dt_now.date()) bh = utils.BusinessHours(last_record_stored, dt_now, worktiming=[15, 21], weekends=[6, 7]) days = bh.getdays() durationStr = str(days) + " D" else: last_record_stored = 0 durationStr = "30 D" barSizeSetting = "30 mins" if str(row_req['symbol']) in ['NDX','SPX','VIX']: barSizeSetting = "30 mins" else: last_record_stored = 0 durationStr = "30 D" barSizeSetting = "30 mins" log.info( "last_record_stored=[%s] endDateTime=[%s] durationStr=[%s] barSizeSetting=[%s]" % ( str(last_record_stored), str(endDateTime) , durationStr, barSizeSetting) ) historicallist = client.get_historical(ticker, endDateTime, durationStr, barSizeSetting,whatToShow, useRTH,formatDate) dataframe = pd.DataFrame() if historicallist: for reqId, request in historicallist.items(): for date, row in request.items(): temp1 = pd.DataFrame(row, index=[0]) temp1['symbol'] = str(row_req['symbol']) if int(row_req['underl_expiry']) > 0: temp1['expiry'] = symbol_expiry else: temp1['expiry'] = str(row_req['underl_expiry']) temp1['type'] = str(row_req['underl_type']) temp1['load_dttm'] = endDateTime dataframe = dataframe.append(temp1.reset_index().drop('index', 1)) dataframe = dataframe.sort_values(by=['date']).set_index('date') log.info( "appending data to sqlite ...") write_market_data_to_sqllite(dataframe, "underl_ib_hist") log.info("sleeping [%s] secs ..." % (str(wait_secs))) sleep(wait_secs) client.disconnect() store.close()
def historical_data_loader(): """ ADVERTENCIA USO DATOS HISTORICOS: Se inserta un registro duplicado en cada carga incremental. Es decir: se vuelve a insertar la barra de la ultima media hora que estaba cargada ya en el hdf5 y tipicamente el close de esta barra es distinto al cargado inicialmente. La analitica que se haga sobre esta tabla debe contemplar eliminar primero de los registros duplicados porque asumimos que el segundo es el valido (dado que es igual al open de la siguiente barra de media hora como se hgha observado) este error se puede eliminar o mitigar si solamente se piden los datos histoticos con el mercado cerrado que es lo que se hace en el modo automatico (crontab) Validar esto. :return: """ log=logger("historical data loader") log.info("Getting historical underlying data from IB ... ") globalconf = config.GlobalConfig() underly_def = globalconf.get_tickers_historical_ib() client = IBClient() clientid1 = int(globalconf.config['ib_api']['clientid_data']) client.connect(clientid1=clientid1) dt_now=datetime.now() endDateTime = dt_now.strftime('%Y%m%d %H:%M:%S') # lo mas que se puede pedir para barras de 30 min es un mes historico # barSizeSetting = "30 mins" barSizeSetting = "1 min" whatToShow = "TRADES" useRTH = 1 formatDate = 1 wait_secs = 40 f = globalconf.open_historical_store() for index, row_req in underly_def.iterrows(): log.info("underl=[%s] [%s] [%s] [%d] [%s] [%s] [%s] [%s] [%d]" % ( str(row_req['symbol']), str(row_req['underl_type']), str(row_req['underl_expiry']), 0, '', '', str(row_req['underl_ex']), str(row_req['underl_curr']), int(index) ) ) ticker = RequestUnderlyingData(str(row_req['symbol']), str(row_req['underl_type']), str(row_req['underl_expiry']), 0, '', '', str(row_req['underl_ex']), str(row_req['underl_curr']), int(index)) path_h5 = "/" + str(row_req['symbol']) if long(row_req['underl_expiry']) > 0: path_h5 = path_h5 + "/" + str(row_req['underl_expiry']) last_record_stored = 0 node = f.get_node(path_h5) if node: df1 = f.select(node._v_pathname) df1= df1.reset_index()['date'] last_record_stored = datetime.strptime(str(df1.max()), '%Y%m%d %H:%M:%S') # no se debe usar .seconds que da respuesta incorrecta debe usarse .total_seconds() #days= int(round( (dt_now - last_record_stored).total_seconds() / 60 / 60 / 24 ,0)) # lo anterior no sirve porque debe considerarse la diferencia en business days #days = np.busday_count(last_record_stored.date(), dt_now.date()) bh= misc_utilities.BusinessHours(last_record_stored, dt_now, worktiming=[15, 21], weekends=[6, 7]) days = bh.getdays() durationStr = str( days ) + " D" else: durationStr = "30 D" barSizeSetting = "30 mins" if str(row_req['symbol']) in ['NDX','SPX','VIX']: barSizeSetting = "30 mins" log.info( "last_record_stored=[%s] endDateTime=[%s] durationStr=[%s] barSizeSetting=[%s]" % ( str(last_record_stored), str(endDateTime) , durationStr, barSizeSetting) ) historicallist = client.get_historical(ticker, endDateTime, durationStr, barSizeSetting,whatToShow, useRTH,formatDate) #print historicallist dataframe = pd.DataFrame() if historicallist: for reqId, request in historicallist.items(): for date, row in request.items(): # print ("date [%s]: row[%s]" % (date, str(row))) temp1 = pd.DataFrame(row, index=[0]) temp1['symbol'] = str(row_req['symbol']) temp1['expiry'] = str(row_req['underl_expiry']) temp1['type'] = str(row_req['underl_type']) temp1['load_dttm'] = endDateTime dataframe = dataframe.append(temp1.reset_index().drop('index', 1)) dataframe = dataframe.sort_values(by=['date']).set_index('date') log.info( "appending data in hdf5 ...") f.append(path_h5, dataframe, data_columns=dataframe.columns) log.info("sleeping [%s] secs ..." % (str(wait_secs))) sleep(wait_secs) client.disconnect() f.close() # Close file
html = urllib2.urlopen(request).read() except urllib2.URLError as e: log.error('Download error: %s' % (str(e.reason))) html = None if num_retries > 0: if hasattr(e, 'code') and 500 <= e.code < 600: # retry 5XX HTTP errors return download(url, user_agent, num_retries - 1) return html if __name__ == "__main__": log = logger("ivolatility download") log.info("Getting volatility data from ivolatility.com ... ") url = "http://www.ivolatility.com/options.j?ticker=SPX&R=0" globalconf = config.GlobalConfig() log.info('[%s] Downloading: %s' % (str(dt.datetime.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" ) soup = BeautifulSoup(b_html, 'html.parser') fixed_html = soup.prettify() ul = soup.find_all('font', attrs={'class': 's2'}) #type(ul) #for i in ul: # print i.text
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 read_10_days_acc_summary_and_current_positions(): """ Summary information about account and portfolio for last 10 days is read from db and printed to console also.""" days = 10 globalconf = config.GlobalConfig(level=logger.ERROR) log = logger("print_10_days_acc_summary_and_current_positions") client = ib.IBClient() clientid1 = int(globalconf.config['ib_api']['clientid_orders']) client.connect(clientid1=clientid1) # this is to try to fit in one line each row od a dataframe when printing to console pd.set_option('display.max_rows', 500) pd.set_option('display.max_columns', 400) pd.set_option('display.width', 1000) now = dt.datetime.now() # Get current time acclist, summarylist = read_acc_summary_and_portfolio_from_ib(client, globalconf, log) print("Real time valuation: %s" % (str(now))) 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 = dataframe[[ u'averageCost', u'conId', u'expiry', u'localSymbol', u'right', u'marketPrice', u'marketValue', u'multiplier', u'position', u'strike', u'symbol', u'unrealizedPNL' ]] print("Portfolio = ") print(dataframe) if summarylist: dataframe2 = pd.DataFrame.from_dict(summarylist).transpose() dataframe2['current_date'] = dt.datetime.now().strftime('%Y%m%d') dataframe2['current_datetime'] = dt.datetime.now().strftime('%Y%m%d%H%M%S') # sort the dataframe dataframe2.sort_values(by=['AccountCode_'], inplace=True) # set the index to be this and don't drop dataframe2.set_index(keys=['AccountCode_'], drop=False,inplace=True) dataframe2 = dataframe2[[u'Cushion_', u'FullInitMarginReq_USD', u'FullMaintMarginReq_USD', u'GrossPositionValue_USD', u'NetLiquidation_USD', u'RegTEquity_USD', u'TotalCashBalance_BASE', u'UnrealizedPnL_BASE']] print("____________________________________________________________________________________________") print("Summary = ") print(dataframe2.transpose()) client.disconnect() print("____________________________________________________________________________________________") print("Summary Account Last %d days valuation:" % (days)) accountid = globalconf.get_accountid() df1 = read_historical_acc_summary_from_sqllite(globalconf, log, accountid) df1 = df1[[u'current_date', u'current_datetime_txt', u'FullInitMarginReq_USD', u'FullMaintMarginReq_USD', u'GrossPositionValue_USD', u'RegTMargin_USD', u'TotalCashBalance_BASE', u'UnrealizedPnL_BASE']] df1 = df1.groupby('current_date').last() df1 = df1.ix[-10:] print(df1)