#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
ETF历史数据
"""
import numpy as np
from uqer import Client, DataAPI
import sys
sys.path.append(r'D:\CXM\Project\SQLLINK')
sys.path.append(r'D:\CXM\Project\ScenarioAnalysis')
import SASQL
import Constant
login = Client(token=Constant.token)
df_all = DataAPI.FundGet(etfLof="ETF", listStatusCd="L", field="", pandas="1")
df = df_all[(df_all.tradeAbbrName.str.contains('上证50ETF') == True) |
(df_all.tradeAbbrName.str.contains('中证500ETF') == True) |
(df_all.tradeAbbrName.str.contains('沪深300ETF') == True)][[
'secID', 'tradeAbbrName'
]]
#
dbsa = SASQL.ScenarioAnalysis()
#
#
col_query = "select name from syscolumns where id = object_id('{}')".format(
'HistData_Stock')
cols = dbsa.ExecQuery(col_query)
for i in range(0, df.shape[0]):
# query = "delete from HistData_Stock where InstrumentID=''".format(df.iat[i,0])
universe_task.set_upstream(risk_model_task)
_ = PythonOperator(task_id='update_uqer_halt_list',
provide_context=True,
python_callable=update_uqer_halt_list,
dag=dag)
factor_master_task = PythonOperator(task_id='update_factor_master',
provide_context=True,
python_callable=update_factor_master,
dag=dag)
factor_master_task.set_upstream(uqer_task)
if __name__ == '__main__':
codes = api.FundGet().secID.values
steps = 1000
dates = pd.date_range('2005-01-01', '2018-11-30', freq='Q')
for d in dates:
start = 0
total_df = pd.DataFrame()
while start < len(codes):
df = api.FundHoldingsGet(secID=codes[start:start + steps],
reportDate=d)
start += steps
total_df = total_df.append(df)
total_df.to_sql('fund_holding',
con=engine,
index=False,
if_exists='append')
