def Sec_sector(self):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",user="******",pwd="SASThom111",db="JYDBBAK")
query="SELECT SecuCode,InfoSource,LEI.Standard,FirstIndustryName, FirstIndustryCode, SecondIndustryName,SecondIndustryCode,LEI.CancelDate FROM JYDBBAK.dbo.SecuMain SM LEFT JOIN JYDBBAK.dbo.LC_ExgIndustry LEI on SM.CompanyCode=LEI.CompanyCode where LEI.Standard=3 and SM.SecuCategory=1"
reslist=ms.ExecNonQuery(query)
df=pd.DataFrame(reslist.fetchall())
df.columns=['ticker','broker','standard','priname','primcode','secname','seccode','canceldate']
return(df)
def Marketcap(self,tickerlist):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",user="******",pwd="SASThom111",db="JYDBBAK")
sql="select TradingDay, SM.SecuCode, TotalMV from JYDBBAK.dbo.QT_Performance QTP left join JYDBBAK.dbo.SecuMain SM on QTP.InnerCode=SM.InnerCode where SM.SecuCode in ('" +"','".join((n for n in tickerlist))+ "')"+" and TradingDay>'2015-01-01'"
reslist=ms.ExecNonQuery(sql)
df=pd.DataFrame(reslist.fetchall())
df.columns=['date','ticker','TotalMCap']
return(df)
def Rec_download(self,startdate,enddate): #Get data from JYDBBAK-（aka.Juyuan-fnundamental)
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",user="******",pwd="SASThom111",db="jyzb_new_1") #This is PROD
sql="select code,into_date,organ_id from jyzb_new_1.dbo.cmb_report_research R left join jyzb_new_1.dbo.I_SYS_CLASS C on C.SYS_CLASS=R.score_id left join jyzb_new_1.dbo.I_ORGAN_SCORE S on S.ID=R.organ_score_id where create_date>='"+startdate+"'"+"and create_date<='"+enddate+"' and (sys_class=7 OR sys_class=5)"
reslist=ms.ExecNonQuery(sql)
df=pd.DataFrame(reslist.fetchall())
df.columns=['ticker','into_date','firmid']
df=df[df['ticker'].apply(lambda x: len(x)==6)]
return(df)
def Rec_alltickers(self):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",user="******",pwd="SASThom111",db="jyzb_new_1") #This is PROD
sql="select distinct code from jyzb_new_1.dbo.cmb_report_research where create_date>='2015-01-01'"
reslist=ms.ExecNonQuery(sql)
df=pd.DataFrame(reslist.fetchall())
df.columns=['ticker']
df=df[df['ticker'].apply(lambda x: len(x)==6)]
tickerlist=df['ticker'].values.tolist()
return(tickerlist)
def Industry(self):
ms = MSSQL(host="10.27.10.10:1433",user="******",pwd="hyzb2018",db="hyzb")
sql="select NAME from SYS_DICT D where D.TYPE='idea_industry'"
reslist=ms.ExecNonQuery(sql)
df=pd.DataFrame(reslist.fetchall())
df.columns=['Industry']
industry_list=df['Industry'].unique()
industry_list=[x.strip() for x in industry_list]
return(industry_list)
def Return_hist(self):
tickerlist=self.Rec_alltickers()
#tickerlist=[x+'.SH' if x.startswith('6') else x+'.SZ' for x in tickerlist]
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",user="******",pwd="SASThom111",db="JYDBBAK")
sql="select TradingDay, SM.SecuCode, ChangePCT from JYDBBAK.dbo.QT_Performance QTP left join JYDBBAK.dbo.SecuMain SM on QTP.InnerCode=SM.InnerCode where SM.SecuCode in ('" +"','".join((n for n in tickerlist))+ "')"+" and TradingDay>'2014-01-01'"
#sql="select TIME,CODE,CHANGEPER from BASIC_PRICE_HIS where CODE in('" +"','".join((n for n in tickerlist))+ "')" +" and TIME>='2015-01-01'"
reslist=ms.ExecNonQuery(sql)
df=pd.DataFrame(reslist.fetchall())
df.columns=['date','ticker','dailyreturn']
df['dailyreturn']=df['dailyreturn'].apply(float)
df['dailyreturn']=df['dailyreturn']/100
df['ticker']=df['ticker'].str.zfill(6)
df['ticker']=df['ticker'].apply(lambda x:x+'.SH' if x.startswith('6') else x+'.SZ')
df.to_csv("U:/S/SecR/DailyReturnNew.csv",index=False)
return()
def RSI_Db(self, rebalday):
ms = MSSQL(host="10.27.10.10:1433",
user="******",
pwd="hyzb2018",
db="hyzb")
sql = "select CODE, rsi_24d from BASIC_PRICE_HIS H where TIME='" + rebalday + "'"
reslist = ms.ExecNonQuery(sql)
df = pd.DataFrame(reslist.fetchall())
df.columns = ['ticker', 'RSIB']
df['enddate'] = rebalday
df['publdate'] = df['enddate']
df = df.dropna()
df['ticker'] = df['ticker'].str[0:6]
df = df[['publdate', 'enddate', 'ticker', 'RSIB']]
return (df)
def Returnhist(self, startdate):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
sql = "select convert(varchar,TradingDay, 23) as date, SM.SecuCode, ClosePrice,ChangePCT,NegotiableMV,TurnoverRateRW,TurnoverVolume from JYDBBAK.dbo.QT_Performance QTP left join JYDBBAK.dbo.SecuMain SM on QTP.InnerCode=SM.InnerCode where SM.SecuCategory = 1 and TradingDay>'" + startdate + "'"
reslist = ms.ExecNonQuery(sql)
df = pd.DataFrame(reslist.fetchall())
df.columns = [
'date', 'ticker', 'closeprice', 'dailyreturn', 'mcap',
'turnoverweek', 'dailyvolume'
]
df['closeprice'] = df['closeprice'].astype(float)
df['dailyreturn'] = df['dailyreturn'].astype(float)
df['mcap'] = df['mcap'].astype(float)
df['turnoverweek'] = df['turnoverweek'].astype(float)
df['dailyvolume'] = df['dailyvolume'].astype(float)
df['dailyreturn'] = df['dailyreturn'] / 100
df['ticker'] = df['ticker'].str.zfill(6)
#df['ticker']=df['ticker'].apply(lambda x:x+'.SH' if x.startswith('6') else x+'.SZ')
df['date'] = df['date'].astype(str)
return (df)