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='{}'