def PM_Alpha_download(self, fundtickerlist):
ms = MSSQL(host="10.27.10.10:1433",
user="******",
pwd="hyzb2018",
db="hyzb")
sql = "select code,fund_fundmanager,fund_corp_fundmanagementcompany from MUTUAL_FUND_INFO where code in (" + str(
fundtickerlist)[1:-1] + ")"
reslist = ms.ExecQuery(sql)
pminfo = pd.DataFrame(reslist,
columns=['fundticker', 'pm', 'fundcorp'])
pmlist = list(pminfo['pm'].unique())
pmlist = [x.encode('latin-1').decode('gbk') for x in pmlist]
sql = "select fund_fundmanager, fund_corp_fundmanagementcompany,fund_manager_totalreturnoverbenchmark from MUTUAL_FUND_manager where fund_fundmanager in (" + str(
pmlist)[1:-1] + ")"
reslist = ms.ExecQuery(sql)
pmoutperf = pd.DataFrame(reslist,
columns=['pm', 'fundcorp', 'outperf'])
pmoutperf['pm'] = pmoutperf['pm'] + pmoutperf['fundcorp']
pmoutperf['pm'] = [
x.encode('latin-1').decode('gbk') for x in pmoutperf['pm']
]
pminfo['pm'] = pminfo['pm'] + pminfo['fundcorp']
pminfo['pm'] = [
x.encode('latin-1').decode('gbk') for x in pminfo['pm']
]
pminfo = pd.merge(pminfo, pmoutperf, on=['pm'], how='left')
return (pminfo)
def convertditc_dataframe(rebaldaylist, tickerlist):
start = time.time()
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
query = "SELECT LEI.CancelDate,SecuCode,FirstIndustryCode 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 and SM.ListedState = 1 and SM.ListedSector in (1,2,6)"
reslist = ms.ExecQuery(query)
tickersectors = pd.DataFrame(reslist,
columns=['date', 'ticker', 'primecode'])
tickersectors['date'] = tickersectors['date'].astype(str)
tempdict = {}
for rebalday in rebaldaylist:
tempdict[rebalday] = tickerlist
df = pd.DataFrame.from_dict(tempdict)
rebalday_ticker = pd.melt(df,
value_vars=list(df.columns),
value_name='ticker',
var_name='date')
rebalday_ticker['primecode'] = np.nan
stock_sector = rebalday_ticker.append(tickersectors)
stock_sector = stock_sector.sort_values(by=['ticker', 'date'],
ascending=[True, True])
stock_sector = stock_sector.reset_index(drop=True)
stock_sector['primecode'] = stock_sector['primecode'].fillna(
method='bfill')
stock_sector = stock_sector.loc[stock_sector['date'].isin(rebaldaylist)]
end = time.time()
print(end - start)
return (stock_sector)
def SectorPrep(self, rebaldaylist, publisher):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
if publisher == 'CITIC':
query = "SELECT convert(varchar,LEI.CancelDate,23),SecuCode,FirstIndustryCode FROM JYDBBAK.dbo.SecuMain SM LEFT JOIN JYDBBAK.dbo.LC_ExgIndustry LEI on SM.CompanyCode=LEI.CompanyCode where LEI.Standard=37 and SM.SecuCategory=1"
if publisher == 'CSI':
query = "SELECT convert(varchar,LEI.CancelDate,23),SecuCode,FirstIndustryCode FROM JYDBBAK.dbo.SecuMain SM LEFT JOIN JYDBBAK.dbo.LC_ExgIndustry LEI on SM.CompanyCode=LEI.CompanyCode where LEI.Standard=28 and SM.SecuCategory=1"
reslist = ms.ExecQuery(query)
tickersectors = pd.DataFrame(reslist,
columns=['date', 'ticker', 'primecode'])
tempdict = {}
newtickerlist = tickersectors['ticker'].unique().tolist()
tempdict = {rebalday: newtickerlist for rebalday in rebaldaylist}
df = pd.DataFrame.from_dict(tempdict)
rebalday_ticker = pd.melt(df,
value_vars=list(df.columns),
value_name='ticker',
var_name='date')
rebalday_ticker['primecode'] = np.nan
sector = rebalday_ticker.append(tickersectors)
sector = sector.sort_values(by=['ticker', 'date'],
ascending=[True, True])
sector = sector.reset_index(drop=True)
sector['primecode'] = sector['primecode'].fillna(method='bfill')
return (sector)
def Benchmark_membs(self, benchmark, startdate):
membstartdate = str(
pd.to_datetime(startdate) - relativedelta(years=1))[0:10]
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
if benchmark == 'CSI300':
sql = "select EndDate,SM.SecuCode,weight from LC_IndexComponentsWeight IC left join JYDBBAK.dbo.SecuMain SM on IC.InnerCode=SM.InnerCode where IndexCode='3145'and EndDate >DATEADD(month,-3,'" + membstartdate + "')"
if benchmark == 'CSI500':
sql = "select EndDate,SM.SecuCode,weight from LC_IndexComponentsWeight IC left join JYDBBAK.dbo.SecuMain SM on IC.InnerCode=SM.InnerCode where IndexCode='4978'and EndDate >DATEADD(month,-3,'" + membstartdate + "')"
if benchmark == 'CSI800':
sql = "select EndDate,SM.SecuCode,weight from LC_IndexComponentsWeight IC left join JYDBBAK.dbo.SecuMain SM on IC.InnerCode=SM.InnerCode where IndexCode='4982'and EndDate >DATEADD(month,-3,'" + membstartdate + "')"
if benchmark == 'SuperTech':
sql = "select EndDate,SM.SecuCode,weight from LC_IndexComponentsWeight IC left join JYDBBAK.dbo.SecuMain SM on IC.InnerCode=SM.InnerCode where IndexCode='229190'and EndDate >DATEADD(month,-3,'" + membstartdate + "')"
if benchmark == 'SuperHealthcare':
sql = "select EndDate,SM.SecuCode,weight from LC_IndexComponentsWeight IC left join JYDBBAK.dbo.SecuMain SM on IC.InnerCode=SM.InnerCode where IndexCode='8890'and EndDate >DATEADD(month,-3,'" + membstartdate + "')"
if benchmark == 'SuperConDisc':
sql = "select EndDate,SM.SecuCode,weight from LC_IndexComponentsWeight IC left join JYDBBAK.dbo.SecuMain SM on IC.InnerCode=SM.InnerCode where IndexCode='8886'and EndDate >DATEADD(month,-3,'" + membstartdate + "')"
if benchmark == 'SuperConStap':
sql = "select EndDate,SM.SecuCode,weight from LC_IndexComponentsWeight IC left join JYDBBAK.dbo.SecuMain SM on IC.InnerCode=SM.InnerCode where IndexCode='8887'and EndDate >DATEADD(month,-3,'" + membstartdate + "')"
if benchmark == 'CSIAll':
sql = "select EndDate,SM.SecuCode,weight from LC_IndexComponentsWeight IC left join JYDBBAK.dbo.SecuMain SM on IC.InnerCode=SM.InnerCode where IndexCode='14110'and EndDate >DATEADD(month,-3,'" + membstartdate + "')"
reslist = ms.ExecQuery(sql)
df = pd.DataFrame(reslist)
df.columns = ['date', 'ticker', 'weight']
df['weight'] = df['weight'].astype(float)
df['date'] = df['date'].astype(str)
df = df.sort_values(by=['date', 'weight'], ascending=[True, False])
df = df.reset_index(drop=True)
return (df)
def Hotstock(self,rebalday):
lookback_period=60
ms2 = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",user="******",pwd="SASThom111",db="jyzb_new_1") #This is PROD
sql="Select '"+rebalday+"' as date, code, count(*) Reccount 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 into_date>=dateadd(day,-"+str(lookback_period)+",'"+rebalday+"') and into_date<'"+rebalday+"' and (sys_class=7 OR sys_class=5) GROUP BY code ORDER BY Reccount DESC "
reslist=ms2.ExecQuery(sql)
rechist=pd.DataFrame(reslist,columns=['date','ticker','RecomCounts'])
return(rechist)
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 Tradingday(self):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
sql = "select distinct TradingDay from QT_Performance where TradingDay>'2003-01-01' order by TradingDay ASC"
reslist = ms.ExecQuery(sql)
df = pd.DataFrame(reslist, columns=['date'])
df.to_csv("D:/SecR/Tradingday.csv", index=False)
return ()
def Index_download(self, seccode):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
sql = "Select ICW.EndDate, SM.SecuCode, ICW.Weight from JYDBBAK.dbo.LC_IndexComponentsWeight ICW left join JYDBBAK.dbo.SecuMain SM on ICW.InnerCode=SM.InnerCode where ICW.IndexCode='" + seccode + "'"
reslist = ms.ExecQuery(sql)
df = pd.DataFrame(reslist, columns=['date', 'ticker', 'weight'])
df['date'] = [str(x)[0:10] for x in df['date']]
return (df)
def TotalEquity(self, startdate):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
query = getattr(Q, 'TotalEquity')(startdate)
reslist = ms.ExecQuery(query)
TotalEquity = pd.DataFrame(
reslist, columns=['publdate', 'enddate', 'ticker', 'TotalEquity'])
return (TotalEquity)
def Funda_download(self, startdate, signal):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
query = getattr(Q, signal)(startdate)
reslist = ms.ExecQuery(query)
df = pd.DataFrame(
reslist, columns=['publdate', 'enddate', 'ticker', 'sigvalue'])
return (df)
def Valuation_hist_daily(self, signame, tickerlist, startdate):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
sql = "Select TradingDay, SM.SecuCode, V." + signame + " from JYDBBAK.dbo.LC_DIndicesForValuation V left join JYDBBAK.dbo.SecuMain SM on V.InnerCode=SM.InnerCode where SM.SecuCategory = 1 and SM.SecuCode in (" + str(
tickerlist)[1:-1] + ") and TradingDay>'" + startdate + "'"
reslist = ms.ExecQuery(sql)
df = pd.DataFrame(reslist, columns=['date', 'ticker', signame])
df['date'] = [str(x)[0:10] for x in df['date']]
return (df)
def ValuationReciprocal_download(self,rebaldaylist,signal):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",user="******",pwd="SASThom111",db="JYDBBAK")
sql=[]
for rebalday in rebaldaylist:
sqlpart=getattr(Q,'Valuation_Reciprocal')(signal,rebalday)
sql.append(sqlpart)
reslist=ms.ExecListQuery(sql)
df=pd.DataFrame(reslist,columns=['publdate','enddate','ticker','sigvalue'])
df['sigvalue']=df['sigvalue'].astype(float)
df['sigvalue']=df['sigvalue'].round(5)
return(df)
def Rec_NonFIGtickers(self):
tickerlist = self.Rec_alltickers()
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="jyzb_new_1") #This is PROD
sql = "SELECT SecuCode FROM JYDBBAK.dbo.SecuMain SM LEFT JOIN JYDBBAK.dbo.LC_ExgIndustry LEI on SM.CompanyCode=LEI.CompanyCode where (LEI.Standard=3 and FirstIndustryCode not in (40,41) and SM.SecuCategory=1 and SM.SecuCode in (" + str(
tickerlist)[1:-1] + "))"
reslist = ms.ExecQuery(sql)
df = pd.DataFrame(reslist, columns=['ticker'])
tickerlist = df['ticker'].unique().tolist()
return (tickerlist)
def Sec_name(self, publisher):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
if publisher == 'CITIC':
sql = "SELECT distinct LEI.FirstIndustryCode,FirstIndustryName from JYDBBAK.dbo.LC_ExgIndustry LEI where LEI.standard=37"
if publisher == 'CSI':
sql = "SELECT distinct LEI.FirstIndustryCode,FirstIndustryName from JYDBBAK.dbo.LC_ExgIndustry LEI where LEI.standard=28"
reslist = ms.ExecQuery(sql)
sec_name = pd.DataFrame(reslist, columns=['sector', 'sectorname'])
return (sec_name)
def PNLTTM(self, startdate):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
query = getattr(Q, 'PNLTTM')(startdate)
reslist = ms.ExecQuery(query)
PNLTTM = pd.DataFrame(reslist,
columns=[
'publdate', 'enddate', 'ticker', 'ORTTM',
'OPTTM', 'FExpTTM'
])
return (PNLTTM)
def Hengheng_Shen6Holding(self):
ms = MSSQL(host="10.27.10.10:1433",
user="******",
pwd="hyzb2018",
db="hyzb")
sql = "select convert(varchar,date,23), code from shen_six_holding SIX where date in (select Max(date) from shen_six_holding SIX)"
fundlist = ms.ExecQuery(sql)
selectedfund = pd.DataFrame(fundlist, columns=['date', 'fundticker'])
fundlist = list(selectedfund['fundticker'].unique())
lastholding = self.Pick_fund_holding()
selectedfundholding = lastholding.loc[
lastholding['fundticker'].isin(fundlist), :].copy()
return (selectedfundholding)
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 GenerateFundlist(self, rebalday):
ms = MSSQL(host="10.27.10.10:1433",
user="******",
pwd="hyzb2018",
db="hyzb")
sql1 = "select convert(varchar,NAV_date,23), code from mutual_fund_performance where NAV_date='" + rebalday + "'"
reslist = ms.ExecQuery(sql1)
rechist = pd.DataFrame(reslist, columns=['date', 'ticker'])
tickerlist = list(rechist['ticker'].unique())
lastquarterend = self.lastquarterend(rebalday)
sql2 = "select convert(varchar,NAV_date, 23), code from mutual_fund_performance where NAV_date='" + lastquarterend + "'"
reslist2 = ms.ExecQuery(sql2)
rechist2 = pd.DataFrame(reslist2, columns=['date', 'ticker'])
tickerlist = list(set(rechist['ticker']) & set(rechist2['ticker']))
return (tickerlist)
def Benchmark_return(self, benchmark, startdate):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
if benchmark == 'CSI300':
sql = "SELECT TradingDay,ChangePCT FROM JYDBBAK.dbo.QT_IndexQuote WHERE InnerCode = '3145' and TradingDay>'" + startdate + "'"
if benchmark == 'CSI500':
sql = "SELECT TradingDay,ChangePCT FROM JYDBBAK.dbo.QT_IndexQuote WHERE InnerCode = '4978' and TradingDay>'" + startdate + "'"
if benchmark == 'CSI800':
sql = "SELECT TradingDay,ChangePCT FROM JYDBBAK.dbo.QT_IndexQuote WHERE InnerCode = '4982' and TradingDay>'" + startdate + "'"
reslist = ms.ExecQuery(sql)
df = pd.DataFrame(reslist, columns=['date', 'bmdailyreturn'])
df['date'] = df['date'].astype(str)
df['bmdailyreturn'] = df['bmdailyreturn'].astype(float)
return (df)
def Pick_fund_holding(self, ref):
lastupdate = self.DC.lastquarterend(ref)
ms = MSSQL(host="10.27.10.10:1433",
user="******",
pwd="hyzb2018",
db="hyzb")
sql = "select convert(varchar,report_date,23),code,id, prt_topstockwindcode,stk_mkv_ratio from mutual_fund_holdings where report_date='" + lastupdate + "'"
reslist = ms.ExecQuery(sql)
rechist = pd.DataFrame(
reslist,
columns=['date', 'fundticker', 'fundid', 'ticker', 'portNav%'])
rechist['sum%'] = rechist.groupby('fundticker')['portNav%'].transform(
'sum')
rechist = rechist.loc[
rechist['sum%'] >= 50, :].copy() #只保留前10持仓之和超过50的，不然没有抄作业的价值
return (rechist)
def regdoi(doi: Optional[str] = ""):
#return {"TEst":doi}
# DOI_Entity.DOI = DOI_Post.DOI
# DOI_Entity.UserName = "******"
# DOI_Entity.PortalName = "oa"
# DOI_Entity.View = DOI_Post.View
# DOI_Entity.GUID = uuid.uuid4()
#Check if DOI exists
#DOI_Exists = session.query(DOI_Entity).filter_by(DOI=DOI_Post.DOI).one()
DOI_Exists = MSSQL.get_by("doi", doi)[0] #Set the rec ID
return {"doiid": DOI_Exists}
if (DOI_Exists == 0):
#Insert a new DOI
'''
Response.Write("add doi<br>");
sql.insert("TblDOI");
id = GetDOIID(con, doi);
'''
# session.add(DOI_Post)
# session.commit()
# inserted_Id = session.query(DOI_Entity).filter_by(DOI=DOI_Post.DOI).one()
inserted_Id = MSSQL.insert_doi(DOI_Post)
else:
#Update an existing DOI
'''
Response.Write("edit doi<br>");
sql.where("fDOIID", id);
sql.edit("TblDOI");
'''
# session._update_impl(DOI_Post)
# session.commit()
# inserted_Id = session.query(DOI_Entity).filter_by(DOI=DOI_Post.DOI).one()
inserted_Id = MSSQL.update_doi(DOI_Post)
#Select the relevant GUID by the DOI_Exists Id
'''
sql.add("fDOIID", id);
guid = sql.select("TblDOI", "fGUID").ToString();
'''
#session.query(DOI_Entity).filter_by(key=institution.parent_key).one() if institution.parent_key else None
return {"GUID" : MSSQL.get_by("pkID", inserted_Id)}
def SQLList_Deq():
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",user="******",pwd="SASThom111",db="JYDBBAK")
startdate='2010-12-28'
rebal_period=20
rebaldaylist=DC.Rebaldaylist(startdate,rebal_period)
signal='PE'
sql=[]
print('startnow')
start=time.time()
for rebalday in rebaldaylist:
sqlpart=getattr(Q,'Valuation_Reciprocal')(signal,rebalday)
sql.append(sqlpart)
reslist=ms.ExecDeqQuery(sql)
end=time.time()
print(end-start)
a=pd.DataFrame(reslist)
return(a)
def Mcap_hist(self, rebaldaylist, df):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
sql = []
for rebalday in rebaldaylist:
tickerlist = df.loc[df['date'] == rebalday, 'ticker'].tolist()
sqlpart = "With QTP as (select TradingDay, NegotiableMV, InnerCode from JYDBBAK.dbo.QT_Performance where TradingDay ='" + rebalday + "' )"
sqlpart = sqlpart + ", SM as (select SecuCode,InnerCode from JYDBBAK.dbo.SecuMain where SecuCode in (" + str(
tickerlist)[1:-1] + ")) "
sqlpart = sqlpart + "Select QTP.TradingDay,SM.SecuCode,QTP.NegotiableMV from SM left join QTP on QTP.InnerCode=SM.InnerCode where TradingDay='" + rebalday + "'"
sql.append(sqlpart)
reslist = ms.ExecListQuery(sql)
mcaphist = pd.DataFrame(reslist, columns=['date', 'ticker', 'mcap'])
mcaphist['date'] = mcaphist['date'].astype(str)
return (mcaphist)
def FundPerf_History(self):
#tickerlist=self.GenerateFundlist()
ms = MSSQL(host="10.27.10.10:1433",
user="******",
pwd="hyzb2018",
db="hyzb")
#lastyeardate=datetime.datetime.now()-relativedelta(years=1)
#lastyeardate=lastyeardate.strftime('%Y-%m-%d')
#sql="select NAV_date, code, dtd_change from mutual_fund_performance where NAV_date>'"+lastyeardate+"'"
sql = "select convert(varchar,NAV_date,23), code, dtd_change from mutual_fund_performance where NAV_date>'2015-01-01' and code like '%.OF%'"
reslist = ms.ExecQuery(sql)
rechist = pd.DataFrame(reslist,
columns=['date', 'fundticker', 'dailyreturn'])
rechist['date'] = [s[0:10] for s in rechist['date']]
rechist = rechist.dropna()
rechist.to_csv("D:/SecR/FundDaily.csv", index=False)
return (rechist)
def TopHolding(self, tickerlist):
ms = MSSQL(host="10.27.10.10:1433",
user="******",
pwd="hyzb2018",
db="hyzb")
lasttwoyeardate = datetime.datetime.now() - relativedelta(years=2)
lasttwoyeardate = lasttwoyeardate.strftime('%Y-%m-%d')
sql = "select report_date,code, prt_topstockwindcode,stk_mkv_ratio from mutual_fund_holdings where report_date>'" + lasttwoyeardate + "' and code in (" + str(
tickerlist)[1:-1] + ")"
reslist = ms.ExecQuery(sql)
rechist = pd.DataFrame(
reslist, columns=['date', 'ticker', 'stockticker', 'portNav%'])
rechist['date'] = [s[0:10] for s in rechist['date']]
rechist = rechist.dropna()
rechist['portNav%'] = rechist['portNav%'] / 100
rechist['stockticker'] = [s[0:6] for s in rechist['stockticker']]
return (rechist)