def index_components(date_, symbol_):
thsLogin = THS_iFinDLogin('htqh1015', '990252')
# conn_LOCAL_mysql = sqlalchemy.create_engine(str(r"mysql+pymysql://root:[email protected]:3306/ashare_new1"))
# db = dl.Db('192.168.1.3','root','x','ashare_new1',3306)
conn_LOCAL_mysql = sqlalchemy.create_engine(
str(r"mysql+pymysql://root:x@localhost:3306/ths"))
db = dl.Db('10.3.135.14', 'root', 'x', 'ths', 3306)
if not (thsLogin == 0 or thsLogin == -201):
# if False:
print("登录失败")
else:
now = datetime.datetime.now()
table_name = 'index_components_ths'
raw_data = THS_DataPool('index', f'{date_};{symbol_}',
'date:Y,thscode:Y', True)
temp = json.loads(raw_data.decode('GB2312'))['tables']
if len(temp) != 0:
df = pd.DataFrame(temp[0]['table'])
df.rename(columns={
'DATE': 'date',
'THSCODE': 'symbol'
},
inplace=True)
df['index_symbol'] = symbol_
df.to_sql(table_name,
if_exists='append',
index=False,
con=conn_LOCAL_mysql)
else:
print('no data')
def get_exchange_rate(table_name):
thsLogin = THS_iFinDLogin('htqh1015','990252')
# conn_LOCAL_mysql = sqlalchemy.create_engine(str(r"mysql+pymysql://root:[email protected]:3306/ashare_new1"))
# db = dl.Db('192.168.1.3','root','x','ashare_new1',3306)
conn_LOCAL_mysql = sqlalchemy.create_engine(str(r"mysql+pymysql://root:x@localhost:3306/ths"))
db = dl.Db('10.3.135.14','root','x','ths',3306)
# symbols = pd.read_sql(sql=f'select distinct currency from eco_rmb_rate',con=conn_LOCAL_mysql).values
# symbols = [("mgbp",),("meur",),("musd",),("mjpy",)]
symbols =[("gbp",),("eur",),("usd",)]
for symbol in symbols:
now = datetime.datetime.now()
start_tp = db.check_rate_date(table_name,symbol[0])[0][0]
if start_tp is not None :
start_date = start_tp + datetime.timedelta(days=1)
start_date = start_date.strftime('%Y-%m-%d')
else :
start_date = '2000-01-01'
end_date = datetime.date.today().strftime('%Y-%m-%d')
if end_date>start_date:
print(symbol)
symbol_ths = adjust(symbol[0])
raw_data = THS_EDBQuery(symbol_ths,start_date,end_date,True)
temp = json.loads(raw_data.decode('utf-8'))['tables']
df = pd.DataFrame(temp[0]['value'],columns=['rate'])
df['currency'] = symbol[0]
df['date'] = temp[0]['time']
df['ctime'] =now
df['utime'] = now
df.to_sql(table_name,if_exists='append',index=False,con=conn_LOCAL_mysql)
def index_daily(symbols):
thsLogin = THS_iFinDLogin('htqh1015','990252')#调用登录函数，账号密码登录）
conn_LOCAL_mysql = sqlalchemy.create_engine(str(r"mysql+pymysql://root:x@localhost:3306/ths"))
# symbols = pd.read_sql(sql=f'select distinct code from index_components',con=conn_LOCAL_mysql).values
# print(symbols)
# db = dl.Db('192.168.1.3','root','x!','ashare_new1',3306)
db = dl.Db('10.3.135.14','root','x','ths',3306)
if not (thsLogin == 0 or thsLogin == -201):
# if False:
print("登录失败")
else:
table_name = 'index_history_day'
# 000300.SH
# symbols = [("000852.SH",),("000905.SH",),("000300.SH",),]
# symbols = [("000300.SH",),]
for symbol in symbols:
print(symbol)
if True:
now = datetime.datetime.now()
start_tp = db.check_index_date(table_name,symbol[0])[0][0]
if start_tp is not None :
start_date = start_tp + datetime.timedelta(days=1)
start_date = start_date.strftime('%Y-%m-%d')
else :
start_date = '2000-01-01'
# end_date = datetime.date.today().strftime('%Y-%m-%d')
end_date = datetime.date.today()- datetime.timedelta(days=1)
end_date = end_date.strftime('%Y-%m-%d')
if end_date >start_date:
raw_data=THS_HistoryQuotes(symbol[0],'preClose;open;high;low;close;avgPrice;change;changeRatio;volume;turnoverRatio;totalCapital;floatCapital;pe_ttm_index;pb_mrq;pe_indexPublisher','Interval:D,CPS:1,baseDate:1900-01-01,Currency:YSHB,fill:Previous',start_date,end_date,True)
temp = json.loads(raw_data.decode('utf-8'))['tables']
df = pd.DataFrame(temp[0]['table'])
# df['date'] = df_status['date']
# df['status'] = df_status['status']
df['date'] = temp[0]['time']
df['index_symbol'] = temp[0]['thscode']
df['ctime'] =now
df['utime'] = now
# df['status'] = status
df.rename(columns={'preClose':'pre_close','change':'change_','changeRatio':'change_ratio','turnoverRatio':'turnover_ratio','avgPrice':'avg_price','totalCapital':'total_capital','floatCapital':'float_capital','pe_indexPublisher':'pe_index_publisher'},inplace=True)
df.to_sql(table_name,if_exists='append',index=False,con=conn_LOCAL_mysql)
else:
print('no data')
def trading_date():
thsLogin = THS_iFinDLogin('htqh1015', '990252')
# conn_LOCAL_mysql = sqlalchemy.create_engine(str(r"mysql+pymysql://root:[email protected]:3306/ashare_new1"))
# db = dl.Db('192.168.1.3','root','x','ashare_new1',3306)
conn_LOCAL_mysql = sqlalchemy.create_engine(
str(r"mysql+pymysql://root:x@localhost:3306/ths"))
db = dl.Db('10.3.135.14', 'root', 'x', 'ths', 3306)
if not (thsLogin == 0 or thsLogin == -201):
print("登录失败")
else:
table_name = 'sys_trade_date'
raw_data = THS_DateQuery('SZSE', 'dateType:0,period:D,dateFormat:0',
'2020-01-01', '2020-12-31')
temp = raw_data['tables']['time']
if len(temp) != 0:
df = pd.DataFrame(temp, columns={'trade_date'})
df['exchange'] = 'SZSE'
df.to_sql(table_name,
if_exists='append',
index=False,
con=conn_LOCAL_mysql)
def dl_income(table_name):
#'htqh1015' '990252'
thsLogin = THS_iFinDLogin('htqh1015','990252')#调用登录函数，账号密码登录）
conn_LOCAL_mysql = sqlalchemy.create_engine(str(r"mysql+pymysql://root:x@localhost:3306/ths"))
db = dl.Db('10.3.135.14','root','root','ths',3306)
# conn_LOCAL_mysql = sqlalchemy.create_engine(str(r"mysql+pymysql://root:[email protected]:3306/ashare_new1"))
index_name = '000300.SH'
# db = dl.Db('192.168.1.3','root','x','ashare_new1',3306)
symbols = db.get_symbol('index_components_ths',index_name)
# symbols = [("000001.SZ",)]
if not (thsLogin == 0 or thsLogin == -201):
print("登录失败")
else:
for symbol in symbols[1:]:
now = datetime.datetime.now()
start_tp = db.check_financial_date(table_name,symbol[0])[0][0]
if start_tp is not None :
start_date = start_tp + datetime.timedelta(days=1)
start_date = start_date.strftime('%Y-%m-%d')
else :
start_date = '2000-01-01'
# start_date='2018-01-01'
# end_date = datetime.date.today().strftime('%Y-%m-%d')
end_date='2020-10-01'
if table_name == "stock_sheet_income_ths":
if end_date>start_date:
print(symbol)
datelist = get_time_range_list(start_date,end_date)
for t_date in datelist:
print(t_date)
raw_data = THS_DateSerial(symbol[0],'ths_np_stock;ths_regular_report_actual_dd_stock','100;','Days:Alldays,Fill:Blank,Interval:D',t_date,t_date,True)
temp = json.loads(raw_data.decode('utf-8'))['tables']
df = pd.DataFrame(temp[0]['table'])
# print(len(df),'--------------------------')
df.rename(columns={'ths_np_stock':'np','ths_regular_report_actual_dd_stock':'date'},inplace=True)
df['report_date'] = temp[0]['time']
df['symbol'] = temp[0]['thscode']
df['ctime'] =now
df['utime'] = now
if (df.np.values[0]!='')&(df.date.values[0]!='')&(df.report_date.values[0]!=''):
if len(df)!=1:
print('error')
# df = df[(df.np != '')&(df.report_date!='')]
# df = df[df.date != '']
# if not df.empty:
print(df)
print('-------------------------',symbol)
df.to_sql(table_name,if_exists='append',index=False,con=conn_LOCAL_mysql)
elif table_name == 'stock_sheet_cash_flow_ths':
if end_date>start_date:
print(symbol)
datelist = get_time_range_list(start_date,end_date)
print(datelist)
for t_date in datelist:
raw_data = THS_DateSerial(symbol[0],'ths_regular_report_actual_dd_stock;ths_ncf_from_oa_stock',';100','Days:Alldays,Fill:Blank,Interval:D',t_date,t_date,True)
temp = json.loads(raw_data.decode('utf-8'))['tables']
df = pd.DataFrame(temp[0]['table'])
df.rename(columns={'ths_ncf_from_oa_stock':'ncf_from_oa','ths_regular_report_actual_dd_stock':'date'},inplace=True)
df['report_date'] = temp[0]['time']
df['symbol'] = temp[0]['thscode']
df['ctime'] =now
df['utime'] = now
if (df.ncf_from_oa.values[0]!='')&(df.report_date.values[0]!='')&(df.date.values[0]!=''):
if len(df)!=1:
print('error')
# df = df[(df.ncf_from_oa != '')&(df.report_date!='')]
df.to_sql(table_name,if_exists='append',index=False,con=conn_LOCAL_mysql)
elif table_name == 'stock_sheet_balance_ths':
if end_date>start_date:
print(symbol)
datelist = get_time_range_list(start_date,end_date)
for t_date in datelist:
raw_data = THS_DateSerial(symbol[0],'ths_regular_report_actual_dd_stock;ths_total_owner_equity_stock;ths_minority_equity_stock;ths_other_equity_instruments_stock;ths_total_noncurrent_liab_stock;ths_bond_payable_stock;ths_lt_staff_salary_payable_stock;ths_dt_liab_stock;ths_other_liab_stock;ths_noncurrent_liab_diff_sri_stock;ths_noncurrent_liab_diff_sbi_stock;ths_total_liab_stock;ths_total_assets_stock',';100;100;100;100;100;100;100;100;100;100;100;100','Days:Alldays,Fill:Blank,Interval:D',t_date,t_date,True)
temp = json.loads(raw_data.decode('utf-8'))['tables']
df = pd.DataFrame(temp[0]['table'])
df.rename(columns={'ths_regular_report_actual_dd_stock':'date','ths_total_owner_equity_stock':'total_owner_equity','ths_minority_equity_stock':'minority_equity','ths_other_equity_instruments_stock':'other_equity_instruments'\
,'ths_total_noncurrent_liab_stock':'total_noncurrent_liab','ths_bond_payable_stock':'bond_payable','ths_lt_staff_salary_payable_stock':'lt_staff_salary_payable','ths_dt_liab_stock':'dt_liab'\
,'ths_other_liab_stock':'other_liab','ths_noncurrent_liab_diff_sri_stock':'noncurrent_liab_diff_sri','ths_noncurrent_liab_diff_sbi_stock':'noncurrent_liab_diff_sbi','ths_total_liab_stock':'total_liab','ths_total_assets_stock':'total_assets'},inplace=True)
df['report_date'] = temp[0]['time']
df['symbol'] = temp[0]['thscode']
df['ctime'] =now
df['utime'] = now
df.dropna(subset=['report_date'],inplace=True)
if (df.date.values[0]!='')&(df.report_date.values[0]!=''):
if len(df)!=1:
print('error')
df = df.applymap(lambda x: np.nan if x == '' else x)
df = df.where(pd.notnull(df), None)
df.to_sql(table_name,if_exists='append',index=False,con=conn_LOCAL_mysql)
elif table_name == 'stock_dividend_indicator_ths':
if end_date>start_date:
print(symbol)
datelist = get_time_range_list(start_date,end_date)
for t_date in datelist:
# print(t_date)
raw_data = THS_DateSerial(symbol[0],'ths_dividend_ps_before_tax_stock;ths_ex_dividend_date_stock;ths_dividend_sign_stock',';;','Days:Tradedays,Fill:Blank,Interval:D',t_date,t_date,True)
temp = json.loads(raw_data.decode('GB2312'))['tables']
#两类巨坑 so no 1----------------------------------------------------------------------------
if temp!=[]:
df = pd.DataFrame(temp[0]['table'])
df.rename(columns={'ths_dividend_ps_before_tax_stock':'dividend_ps_before_tax','ths_ex_dividend_date_stock':'date','ths_dividend_sign_stock':'dividend_sign'},inplace=True)
df['report_date'] = temp[0]['time']
df['symbol'] = temp[0]['thscode']
df['ctime'] =now
df['utime'] =now
#两类巨坑 so no 2----------------------------------------------------------------------------
if (df.dividend_sign.values[0] == '是')&(df.dividend_ps_before_tax.values[0]!='')&(df.report_date.values[0]!='')&(df.date.values[0]!=''):
if len(df)!=1:
print('error')
# df = df[(df.dividend_sign == '是')]
# df = df[(df.dividend_ps_before_tax != '')&(df.report_date!='')]
df['dividend_sign'] = df['dividend_sign'].replace('是',1)
df.to_sql(table_name,if_exists='append',index=False,con=conn_LOCAL_mysql)
def get_forecast_data(table_name,forecast_year):
# conn_LOCAL_mysql = sqlalchemy.create_engine(str(r"mysql+pymysql://root:[email protected]:3306/ashare_new1"))
conn_LOCAL_mysql = sqlalchemy.create_engine(str(r"mysql+pymysql://root:x@localhost:3306/ths"))
db = dl.Db('10.3.135.14','root','x','ths',3306)
index_name = '000300.SH'
# db = dl.Db('192.168.1.3','root','x','ashare_new1',3306)
symbols = db.get_symbol('index_components_ths',index_name)
count=0
while count<=len(symbols):
try:
thsLogin = THS_iFinDLogin('htqh1015','x')#调用登录函数，账号密码登录）
if not (thsLogin == 0 or thsLogin == -201):
print("登录失败")
else:
now = datetime.datetime.now()
for symbol in symbols[count:]:
dflist=[]
count+=1
print(symbol[0],str(round(count/len(symbols)*100,2))+'%',count)
start_tp = db.check_forecast_date(table_name,symbol[0],forecast_year)[0][0]
if start_tp is not None :
start_date = start_tp + datetime.timedelta(days=1)
start_date = start_date.strftime('%Y-%m-%d')
else :
start_date = '2005-01-01'
# start_date='2005-01-01'
# end_date = datetime.date.today().strftime('%Y-%m-%d')
end_date='2020-10-01'
# t1=time.time()
if end_date>start_date:
# print(symbol[0])
datelist = get_time_range_list(start_date,end_date)
for t_date in datelist:
# print(time.time()-t1,'---',1)
for i in range(3):
forecast_year= datetime.datetime.strptime(t_date,'%Y-%m-%d').timetuple()[0]+i
raw_data = THS_DateSerial(symbol[0],'ths_eps_fore_org_num_consensus_stock;ths_fore_np_median_consensus_stock',f'{forecast_year};{forecast_year}','Days:Tradedays,Fill:Previous,Interval:D',t_date,t_date,True)
temp = json.loads(raw_data.decode('utf-8'))['tables']
if temp!=[]:
# print(temp[0]['table'])
df = pd.DataFrame(temp[0]['table'])
df.rename(columns={'ths_eps_fore_org_num_consensus_stock':'eps_fore_org_num','ths_fore_np_median_consensus_stock':'fore_np_median'},inplace=True)
df['forecast_year'] = forecast_year
df['date'] = temp[0]['time']
df['symbol'] = temp[0]['thscode']
df['ctime'] =now
df['utime'] =now
dflist.append(df)
# print(time.time()-t1,'---',2)
dfr=df
for dfs in dflist[:-1]:
dfr=pd.concat([dfr,dfs])
# print(time.time()-t1,'---',3)
dfr.to_sql(table_name,if_exists='append',index=False,con=conn_LOCAL_mysql)
# print(time.time()-t1,'---',4)
time.sleep(2)
except Exception as e:
print(e)
count-=1
def dl_THS_Daily(start, end, table_name, index_symbol):
thsLogin = THS_iFinDLogin('htqh1015', '990252') #调用登录函数，账号密码登录）
# conn_LOCAL_mysql = sqlalchemy.create_engine(str(r"mysql+pymysql://zcb:[email protected]:8848/ashare_new"))
conn_LOCAL_mysql = sqlalchemy.create_engine(
str(r"mysql+pymysql://root:x@localhost:3306/ths"))
symbols = pd.read_sql(
sql=
f'select distinct symbol from index_components_ths where date BETWEEN "{start}" AND "{end}" and index_symbol="{index_symbol}" ',
con=conn_LOCAL_mysql).values
# symbols = [("001872.SZ",)]
# db = dl.Db('192.168.1.4','zcb','x','ashare_new',8848)
db = dl.Db('10.3.135.14', 'root', 'x', 'ths', 3306)
if not (thsLogin == 0 or thsLogin == -201):
# if False:
print("登录失败")
else:
count = 0
for symbol in symbols:
count += 1
print(symbol, str(round(count / len(symbols) * 100, 2)) + '%')
try:
# if True:
now = datetime.datetime.now()
start_tp = db.check_date(table_name, symbol[0])[0][0]
if start_tp is not None:
start_date = start_tp + datetime.timedelta(days=1)
start_date = start_date.strftime('%Y-%m-%d')
else:
start_date = '2000-01-01'
end_date = datetime.date.today().strftime('%Y-%m-%d')
# end_date = '2020-09-01'
if end_date > start_date:
print(start_date, end_date)
# trading_status = THS_DateSerial(symbol[0],'ths_trading_status_stock','','Days:Tradedays,Fill:Blank,Interval:D',start_date,end_date,True)
# temp2 = json.loads(trading_status.decode('GB2312'))['tables']
# tp = pd.DataFrame(data = {'date':temp2[0]['time'],'status':temp2[0]['table']['ths_trading_status_stock']})
#
# df_status = tp[tp.status != '终止上市']
# if not df_status.empty:
# df_status['status'] = df_status['status'].apply(lambda x:0 if x == '交易'else 1)
# status = [0 if i =='交易'else 1 for i in temp2[0]['table']['ths_trading_status_stock']]
# start_ = df_status['date'].min()
# end_ = df_status['date'].max()
if table_name == 'stock_history_day_nfq':
# 不复权
raw_data = THS_HistoryQuotes(
symbol[0],
'preClose;open;high;low;close;avgPrice;change;changeRatio;volume;amount;turnoverRatio;transactionAmount;totalShares;totalCapital;floatSharesOfAShares;floatSharesOfBShares;floatCapitalOfAShares;floatCapitalOfBShares;pe_ttm;pe;pb;ps;pcf',
'Interval:D,CPS:1,baseDate:1900-01-01,Currency:YSHB,fill:Previous',
start_date, end_date, True)
elif table_name == 'stock_history_day':
# 后复权（分红在投）
raw_data = THS_HistoryQuotes(
symbol[0],
'preClose;open;high;low;close;avgPrice;change;changeRatio;volume;amount;turnoverRatio;transactionAmount;totalShares;totalCapital;floatSharesOfAShares;floatSharesOfBShares;floatCapitalOfAShares;floatCapitalOfBShares;pe_ttm;pe;pb;ps;pcf',
'Interval:D,CPS:3,baseDate:1900-01-01,Currency:YSHB,fill:Previous',
start_date, end_date, True)
temp = json.loads(raw_data.decode('utf-8'))['tables']
df = pd.DataFrame(temp[0]['table'])
# df['date'] = df_status['date']
# df['status'] = df_status['status']
df['date'] = temp[0]['time']
df['symbol'] = temp[0]['thscode']
df['ctime'] = now
df['utime'] = now
# df['status'] = status
df.rename(columns={'preClose':'pre_close','change':'change_','changeRatio':'change_ratio','turnoverRatio':'turnover_ratio','avgPrice':'avg_price','transactionAmount':'transaction_amount','totalShares':'total_shares','totalCapital':'total_capital','floatSharesOfAShares':'float_shares_of_ashares','floatSharesOfBShares':'float_shares_of_bshares'\
,'floatCapitalOfAShares':'float_capital_of_ashares','floatCapitalOfBShares':'float_capital_of_bshares'},inplace=True)
df.to_sql(table_name,
if_exists='append',
index=False,
con=conn_LOCAL_mysql)
except Exception as e:
print(symbol[0])
print(e)
write_log(symbol[0], str(e))