def update_uqer_index_components(ds, **kwargs): ref_date, this_date = process_date(ds) table = 'index_components' index_codes = ['000001', '000300', '000905', '000016', '399005', '399006'] total_data = pd.DataFrame() for index in index_codes: df = api.IdxCloseWeightGet(ticker=index, beginDate=dt.datetime(this_date.year - 1, this_date.month, this_date.day).strftime( '%Y%m%d'), endDate=ref_date) df = df[df.effDate == df.effDate.unique()[-1]] df.rename(columns={'ticker': 'indexCode', 'secShortName': 'indexShortName', 'consTickerSymbol': 'Code', 'consExchangeCD': 'exchangeCD', 'consShortName': 'secShortName'}, inplace=True) df['indexCode'] = df.indexCode.astype(int) df['Code'] = df.Code.astype(int) df['Date'] = this_date del df['secID'] del df['consID'] total_data = total_data.append(df) engine2.execute("delete from {0} where Date = '{1}';".format(table, ref_date)) total_data.to_sql(table, engine2, index=False, if_exists='append')
def fetch_index_uqer(self, trade_date, index): index_stock = DataAPI.IdxCloseWeightGet( secID=u"", ticker=str(index.split('.')[0]), beginDate=trade_date - relativedelta(years=1), endDate=trade_date, field=u"effDate,consID,weight", pandas="1").rename(columns={ 'consID': 'code' }).set_index('effDate') trade_date_sets = list(set(index_stock.index)) trade_date_sets.sort(reverse=False) index_stock = index_stock.loc[trade_date_sets[-1]] index_stock['trade_date'] = trade_date return index_stock
def update_uqer_index_components(ds, **kwargs): ref_date, this_date = process_date(ds) flag = check_holiday(this_date) if not flag: return index_codes = [ '000001', '000002', '000003', '000004', '000005', '000006', '000007', '000008', '000009', '000010', '000015', '000016', '000020', '000090', '000132', '000133', '000300', '000852', '000902', '000903', '000904', '000905', '000906', '000907', '000922', '399001', '399002', '399004', '399005', '399006', '399007', '399008', '399009', '399010', '399011', '399012', '399013', '399107', '399324', '399330', '399333', '399400', '399401', '399649' ] total_data = pd.DataFrame() for index in index_codes: df = api.IdxCloseWeightGet(ticker=index, beginDate=ref_date, endDate=ref_date) if df.empty: ref_previous_date = advanceDateByCalendar('china.sse', this_date, '-1b') query = select([IndexComponent]).where( and_(IndexComponent.trade_date == ref_previous_date, IndexComponent.indexCode == int(index))) df = pd.read_sql(query, engine) df['trade_date'] = this_date if df.empty: continue alpha_logger.info('{0} is finished with previous data {1}'.format( index, len(df))) else: ################################ # 2017-10-09, patch for uqer bug def filter_out_eqy(code: str): if code[0] in ['0', '3'] and code[-4:] in ['XSHE']: return True elif code[0] in ['6'] and code[-4:] in ['XSHG']: return True else: return False df = df[df.consID.apply(lambda x: filter_out_eqy(x))] ################################ df.rename(columns={ 'ticker': 'indexCode', 'secShortName': 'indexShortName', 'consTickerSymbol': 'code', 'consExchangeCD': 'exchangeCD', 'consShortName': 'secShortName' }, inplace=True) df['indexCode'] = df.indexCode.astype(int) df['code'] = df.code.astype(int) df['trade_date'] = this_date del df['secID'] del df['consID'] alpha_logger.info('{0} is finished with new data {1}'.format( index, len(df))) total_data = total_data.append(df) index_codes = total_data.indexCode.unique() index_codes = [int(index) for index in index_codes] query = delete(IndexComponent).where( and_(IndexComponent.trade_date == this_date, IndexComponent.indexCode.in_(index_codes))) engine.execute(query) if total_data.empty: return data_info_log(total_data, IndexComponent) format_data(total_data) total_data.to_sql(IndexComponent.__table__.name, engine, index=False, if_exists='append')
def update_uqer_index_components(ds, **kwargs): ref_date, this_date = process_date(ds) flag = check_holiday(this_date) if not flag: return index_codes = [ '000001', '000002', '000003', '000004', '000005', '000006', '000007', '000008', '000009', '000010', '000015', '000016', '000020', '000090', '000132', '000133', '000300', '000852', '000902', '000903', '000904', '000905', '000906', '000907', '000922', '399001', '399002', '399004', '399005', '399006', '399007', '399008', '399009', '399010', '399011', '399012', '399013', '399107', '399324', '399330', '399333', '399400', '399401', '399649' ] total_data = pd.DataFrame() for index in index_codes: df = api.IdxCloseWeightGet(ticker=index, beginDate=ref_date, endDate=ref_date) if df.empty: ref_previous_date = advanceDateByCalendar('china.sse', this_date, '-1b') query = select([IndexComponent]).where( and_(IndexComponent.trade_date == ref_previous_date, IndexComponent.indexCode == int(index))) df = pd.read_sql(query, engine) df['trade_date'] = this_date if df.empty: continue else: df.rename(columns={ 'ticker': 'indexCode', 'secShortName': 'indexShortName', 'consTickerSymbol': 'code', 'consExchangeCD': 'exchangeCD', 'consShortName': 'secShortName' }, inplace=True) df['indexCode'] = df.indexCode.astype(int) df['code'] = df.code.astype(int) df['trade_date'] = this_date del df['secID'] del df['consID'] total_data = total_data.append(df) index_codes = total_data.indexCode.unique() index_codes = [int(index) for index in index_codes] query = delete(IndexComponent).where( and_(IndexComponent.trade_date == this_date, IndexComponent.indexCode.in_(index_codes))) engine.execute(query) if total_data.empty: return data_info_log(total_data, IndexComponent) format_data(total_data) total_data.to_sql(IndexComponent.__table__.name, engine, index=False, if_exists='append')