st_time = time.time() for i, Code_lst in enumerate(Code_lst_tot): Sql_S = """ Select SecID as Code, Item, EstCurr, convert(varchar(8), StartDate, 112) as BASE_DT, convert(varchar(8), isnull(EndDate, GETDATE()), 112) as expire_DT, Value_ from SM2DArmAAm with (nolock) where Item = 45 and SecId in ({}) and Value_ is not null and Value_ <> -99999 and Value_ <> -9999 and StartDate >= '{}' """.format(list2sqlstr(Code_lst), rtvStart) CONNECT_QAD = connectDB(ODBC_NAME="MSSQL_QAD") DF = pd.read_sql(Sql_S, CONNECT_QAD) CONNECT_QAD.close() DF = chg_to_type(DF, ['Code', 'Item', 'BASE_DT', 'expire_DT'], type_=str) DF['PermRegion'] = '1' DF_trim = pd.DataFrame() for code_ in Code_lst: B = DF[DF['Code']==code_].copy() B['BASE_DT'] = pd.to_datetime(B['BASE_DT'], format='%Y%m%d') if B.shape[0] > 0: A = pd.to_datetime(seq_DT, format='%Y%m%d').to_frame('BASE_DT') DF_ = pd.merge_asof(A, B, on='BASE_DT') DF_.dropna(subset=['Code'], inplace=True)
upl_files.append('IBES_ErnRev3M.p') print('>>> Files to be Uploaded:') for k in range(0, len(upl_files), 5): print(' ' + ' | '.join(upl_files[k:k + 3])) print('\n') # ---------------------- print('>>> Checking if "FactorBuild_adjSignal.csv" Exists:') factor_adjMap = pd.read_csv('batch_utils/key_files/FactorBuild_adjSignal.csv', index_col=0) print(factor_adjMap.head()) print('\n') # ---------------------- print('>>> Getting QAD Master Security Information for Reference Info:') conn = connectDB('MSSQL_QAD') secInf = up.get_refInformation(conn) conn.close() print(' Done.\n') # ---------------------- server_ = 'MSSQL_DEV' db_name = 'EJ_WRK_FACTORS3' print('>>> Checking if DB: {} exists:'.format(db_name)) conn = connectDB(server_) if not check_exist(conn, db_name): while True: input_ = input( ' Table does not exist! Proceed to create one?? (y/n)') if input_ in ['y', 'n']: break
from batch_utils.utils_dateSeq import batch_sequence from batch_utils.common import list2sqlstr, _conv2strCol, rm_backward, chunker from batch_utils import WS_currVal bkfil, rtvStart, seq_DT = batch_sequence('backfill', 'M') Table = 'WSPITCmpIssFData' Code = ['6751'] Item = '9802' # beta cal_dt_ = seq_DT.rename('marketdate') S0 = pd.Series({'marketdate': 'datetime'}) primary = 'marketdate' typeStr = create_typeStr(S0, primary=primary) conn = connectDB(ODBC_NAME="MSSQL_QAD") create_table(conn, '#Calendar', typeStr, primary=primary) update_table(conn, '#Calendar', cal_dt_, typeStr, verbose=False) Sql_S = """ Select convert(varchar(8),mm.marketdate,112) as marketdate, dt.Code, dt.Item, convert(varchar(8), dt.StartDate,112) as StartDate, convert(varchar(8), dt.EndDate,112) as EndDate, dt.Value_ from #Calendar mm left outer join {} dt with (nolock) on mm.marketdate >= dt.StartDate and mm.marketdate <= isnull(dt.EndDate, dateadd(d,2,GETDATE())) where dt.Code in ({}) and dt.Value_ <> -1e+38 and not (dt.Value_ = 0 and dt.EndDate is NULL) and dt.Item='{}'