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 TotalRatios(self, startdate):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
query = getattr(Q, 'TotalAsset')(startdate)
reslist = ms.ExecQuery(query)
TAsset = pd.DataFrame(
reslist, columns=['publdate', 'enddate', 'ticker', 'TAsset'])
query = getattr(Q, 'TotalLiability')(startdate)
reslist = ms.ExecQuery(query)
TLiability = pd.DataFrame(
reslist, columns=['publdate', 'enddate', 'ticker', 'TLiability'])
return (TAsset, TLiability)
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_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 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 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 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 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 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 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 Rec_alltickers(self):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="jyzb_new_1") #This is PROD
sql = "select code from jyzb_new_1.dbo.cmb_report_research where create_date>='2004-01-01'"
reslist = ms.ExecQuery(sql)
df = pd.DataFrame(reslist, columns=['ticker'])
df = df[df['ticker'].apply(lambda x: len(x) == 6)]
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 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 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 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 FinancialRatios(self, startdate):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
query = getattr(Q, 'FinancialLiability')(startdate)
reslist = ms.ExecQuery(query)
FLiability = pd.DataFrame(
reslist, columns=['publdate', 'enddate', 'ticker', 'FLiability'])
query = getattr(Q, 'FinancialAsset')(startdate)
reslist = ms.ExecQuery(query)
FAsset = pd.DataFrame(reslist,
columns=[
'publdate', 'enddate', 'ticker',
'TradingAssets', 'HoldForSaleAssets',
'HoldToMaturityInvestments',
'OthEquityInstrument', 'OthNonCurFinAssets',
'DebtInvestment', 'OthDebtInvestment'
])
FAsset = self.Sumcolumns(FAsset)
FAsset = FAsset.rename(columns={'sum': 'FAsset'})
FAsset = FAsset[['publdate', 'enddate', 'ticker', 'FAsset']]
return (FAsset, FLiability)
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 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)
def FundPerf(self):
ms = MSSQL(host="10.27.10.10:1433",
user="******",
pwd="hyzb2018",
db="hyzb")
histfundperf = pd.read_csv("D:/SecR/FundDaily.csv")
maxday = histfundperf['date'].max()
sql = "select convert(varchar,NAV_date,23), code, dtd_change from mutual_fund_performance where NAV_date>'" + maxday + "' and code like '%.OF%'"
#sql="select NAV_date, code, dtd_change from mutual_fund_performance where NAV_date>'"+lastyeardate+"' and code in ("+str(tickerlist)[1:-1]+")"
reslist = ms.ExecQuery(sql)
rechist = pd.DataFrame(reslist,
columns=['date', 'fundticker', 'dailyreturn'])
if (rechist.shape[0] > 0):
rechist['date'] = [s[0:10] for s in rechist['date']]
rechist = rechist.dropna()
histfundperf = histfundperf.append(rechist)
histfundperf.to_csv("D:/SecR/FundDaily.csv", index=False)
return (histfundperf)
def SectorPrep(self):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
sectormap = pd.read_csv('D:/SecR/sector_map.csv')
sectormap['sector'] = sectormap['sector'].astype(str)
sectormap['ticker'] = sectormap['ticker'].astype(str)
sectormap['sector'] = [x.zfill(2) for x in sectormap['sector']]
sectormap['ticker'] = [x.zfill(6) for x in sectormap['ticker']]
tickerlist = list(sectormap['ticker'].unique())
sql = "select convert(varchar,TradingDay,23), SM.SecuCode, ChangePCT/100 from JYDBBAK.dbo.QT_IndexQuote IQ left join JYDBBAK.dbo.SecuMain SM on IQ.InnerCode=SM.InnerCode where TradingDay>'2019-12-31' and SM.SecuCode in (" + str(
tickerlist)[1:-1] + ")"
reslist = ms.ExecQuery(sql)
indexreturn = pd.DataFrame(reslist,
columns=['date', 'ticker', 'dailyreturn'])
indexreturn = indexreturn.sort_values(by=['date'], ascending=[True])
indexreturn['dailyreturn'] = indexreturn['dailyreturn'].astype('float')
return (indexreturn, sectormap)
def Bank_download(self):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="JYDBBAK")
siglist = [
'23000', '22800', '55000', '26200', '26000', '30240', '21300'
]
#indicatorname=['不良贷款比率','拨备覆盖率','核心一级资本充足','净息差','净利差','杠杆率','拨贷比'] #Banks ratios' code in 聚源Bankingn sheet
query = "Select FS.InfoPublDate,FS.EndDate,SM.SecuCode,FS.RatioEOP,FS.IndicatorCode from JYDBBAK.dbo.LC_FSpecialIndicators FS left join JYDBBAK.dbo.SecuMain SM on FS.CompanyCode=SM.CompanyCode where FS.Mark=2 and FS.Indicatorcode in (" + str(
siglist
)[1:-1] + ") and SM.SecuCategory = 1 and FS.InfoPublDate>'2003-01-01'"
reslist = ms.ExecQuery(query)
df = pd.DataFrame(
reslist,
columns=['publdate', 'enddate', 'ticker', 'sigvalue', 'signame'])
sighist = df.loc[df['ticker'].str[0].isin(['6', '0'])].copy()
sighist['sigvalue'] = sighist['sigvalue'].astype(float)
sighist['signame'] = sighist['signame'].astype(str)
sighist = self.Ratio_treat(sighist)
return (sighist)
def DataExtract(self, rebaldaylist, signame):
ms = MSSQL(host="GS-UATVDBSRV01\GSUATSQL",
user="******",
pwd="SASThom111",
db="zyyx")
tradingday = pd.read_csv("D:/SecR/Tradingday.csv")
loc = tradingday.loc[tradingday['date'] == rebaldaylist[0], :].index[0]
firstday = tradingday.iloc[loc - 20, 0]
newrebaldaylist = rebaldaylist.copy()
newrebaldaylist.insert(0, firstday)
query = "select convert(varchar,con_date,23),stock_code,con_year, " + signame + ", con_np_type from zyyx.dbo.con_forecast_stk where con_date in (" + str(
newrebaldaylist)[1:-1] + ")"
reslist = ms.ExecQuery(query)
df = pd.DataFrame(
reslist,
columns=['date', 'ticker', 'con_year', signame, 'con_np_type'])
df = df.loc[df['con_np_type'] ==
1, :].copy() #the one with the highest estimates
df['targetyear'] = df['date'].str[0:4]
df['targetyear'] = df['targetyear'].astype(int) + 1
selectdf = df.loc[df['con_year'] == df['targetyear'], :].copy()
selectdf = selectdf.loc[selectdf[signame].isnull() == False, :]
selectdf = selectdf.loc[selectdf[signame] != 0, :]
return (selectdf)
hkdata['datenew'] = pd.to_datetime(hkdata['date'])
hkdata['sharesheld'] = hkdata['sharesheld'].astype('float')
hkdata = hkdata.sort_values(by=['ticker', 'date'], ascending=[True, True])
hkdata['date_diff'] = hkdata['datenew'] - hkdata['datenew'].shift(1)
hkdata['date_diff'] = pd.to_numeric(hkdata['date_diff'].dt.days,
downcast='integer')
hkdata['nthoccur'] = hkdata.groupby('ticker').cumcount() + 1
hkdata['ticker'] = [s[0:5] for s in hkdata['ticker']]
hkdata['index'] = hkdata['date'] + hkdata['ticker']
#Mcap data
datelist = list(hkdata['date'].unique())
tickerlist = list(hkdata['ticker'].unique())
sql = "select TradingDay, SM.SecuCode, NegotiableMV, ClosePrice from JYDBBAK.dbo.QT_HKDailyQuoteIndex HKQI left join JYDBBAK.dbo.HK_SecuMain SM on HKQI.InnerCode=SM.InnerCode where SM.SecuCode in (" + str(
tickerlist)[1:-1] + ") and TradingDay>'2017-01-01'"
reslist = ms.ExecQuery(sql)
mcaphist = pd.DataFrame(reslist, columns=['date', 'ticker', 'mcap', 'price'])
mcaphist['date'] = mcaphist['date'].astype(str)
mcaphist = mcaphist.loc[mcaphist['date'].isin(datelist), :].copy()
mcaphist['index'] = mcaphist['date'] + mcaphist['ticker']
#Merge Southbound and Mcap, calculate Notional held by southbound flow: last trading day's price*shares held
hkdata = pd.merge(hkdata,
mcaphist[['index', 'mcap', 'price']],
on='index',
how='left')
hkdata['price'] = hkdata['price'].astype(float)
hkdata['notionalheld'] = hkdata['sharesheld'] * hkdata['price']
#Calculate notional increase %
hkdata['holding_increase'] = hkdata['notionalheld'] / hkdata[