def zqAmo(self,sdate,edate):
#这里最后一级目录用\\，虽然也可用/，但/+'...'无法被识别
fdirtitle=u'E:/工作/数据备份/资金流/day\\'
flist=glob.glob(fdirtitle+'*.csv')
#筛选日期范围内数据
sindex=flist.index(fdirtitle+sdate+'.csv')
eindex=flist.index(fdirtitle+edate+'.csv')
flist=flist[sindex:eindex+1]
zqAmoData=pd.DataFrame()
for f in flist:
tmpdata=pd.read_csv(f,encoding='gbk',usecols=[1,2,5,8,9,10],index_col=0)
zqAmoData=zqAmoData.append(tmpdata)
names=zqAmoData['name'].drop_duplicates()
zqAmoData=zqAmoData.groupby(level=0).sum()
zqAmoData['name']=names
zqAmoData.reset_index(level=0,inplace=True)
zqAmoData['vbigper']=zqAmoData['bigD']/zqAmoData['amosum']
zqAmoData.sort_values('vbigper',ascending=False,inplace=True)
c=colligation.ZH(sdate,edate)
zqAmoData30=c.getRF(30,zqAmoData)
zqAmoData200=c.getRF(200,zqAmoData)#.head(len(zqAmoData30))
return zqAmoData200,zqAmoData30,zqAmoData
def zqAmoDay(self, stime, etime):
amosql = "select code,amo from stocksystem.cashflowday where date>='" + stime + "'and date <='" + etime + "'"
print amosql
amodata = pd.read_sql(amosql, con=self.engine,
index_col='code').drop_duplicates()
amodata = amodata.groupby(level=0).sum()
amodata.sort_values('amo', ascending=False, inplace=True)
c = colligation.ZH(stime, etime)
amodata = c.getBoard(3000, amodata)
amodata.to_csv(u'E:\\work\\数据备份\\板块资金流\\day\\stock' +
stime.replace(':', '时') + '至' +
etime.replace(':', '时') + '.csv',
encoding='gbk',
float_format='%.2f')
amodata = amodata.groupby('board_name').sum()
amosum = np.abs(amodata['amo']).sum()
amodata['amoper'] = amodata['amo'] / amosum * 100
amodata.sort_values('amo', ascending=False, inplace=True)
amodata.to_csv(u'E:\\work\\数据备份\\板块资金流\\day\\index' +
stime.replace(':', '时') + '至' +
etime.replace(':', '时') + '.csv',
encoding='gbk',
float_format='%.2f')
return amodata
def sortAmo(self,s1):
# vbigs=s1.loc['vbigS'].sort_values('amo',ascending=False).set_index('code')
# lbigs=s1.loc['lbigS'].sort_values('amo',ascending=False).set_index('code')
# vbigb=s1.loc['vbigB'].sort_values('amo',ascending=False).set_index('code')
# lbigb=s1.loc['lbigB'].sort_values('amo',ascending=False).set_index('code')
# vbigs['name']=stockNames['stock_name']
# vbigb['name']=stockNames['stock_name']
# lbigs['name']=stockNames['stock_name']
# lbigb['name']=stockNames['stock_name']
# bigs['name']=stockNames['stock_name']
# bigb['name']=stockNames['stock_name']
# bigs=(vbigs+lbigs).sort_values('amo',ascending=False)
# bigb=(vbigb+lbigb).sort_values('amo',ascending=False)
# vbigs=vbigs.dropna().head(30)
# vbigb=vbigb.dropna().head(30)
# lbigs=lbigs.dropna().head(30)
# lbigb=lbigb.dropna().head(30)
# bigs=bigs.dropna().head(30)
# bigb=bigb.dropna().head(30)
s1.sort_values('vbigper',ascending=False,inplace=True)
c=colligation.ZH()
s2=c.getRF(30,s1)
s1=s1[s1.vbigB>0.1]
#s1.index=np.arange(len(s1))
vbiglist=list(s1['vbigD'].sort_values(ascending=False).head(5).index)
lbiglist=list(s1['lbigD'].sort_values(ascending=False).head(5).index)
biglist=list(s1['bigD'].sort_values(ascending=False).head(5).index)
#sigindex=set(vbiglist+lbiglist+biglist)
sigindex=[]
sigindex.append(vbiglist)
sigindex.append(lbiglist)
sigindex.append(biglist)
return s1,s2,sigindex
def getTrading(self, sdate, edate, marginflag):
#ATR指标
newdate = self.getNewDate(sdate, 6)
#获取所有股票数据
rfcodestr = '>=000001 '
rfall_atr = self.getStockData(rfcodestr, newdate, edate, 'D')
atrall_colunms = list(rfall_atr.columns)
#获取最后一个数据
atr_all = rfall_atr[[atrall_colunms[-1]]]
atr_all['hq_code'] = atr_all.index
atr_allindexlist = range(len(atr_all))
atr_all.rename(columns={atrall_colunms[-1]: 'atr'}, inplace=True)
atr_all.sort_values(by=['atr'], ascending=False, inplace=True)
atr_all.index = atr_allindexlist
# 处理融资余额
# startDate = start_date.strftime('%Y-%m-%d')
#
# endDate = end_date.strftime('%Y-%m-%d')
#self.get_SH_Margins(startDate,endDate,rf30codestr)
if marginflag == True:
self.dealMarginData(sdate, edate)
#处理上海融资融券数据
shrzye_df = self.get_SH_Margins(sdate, edate)
#处理深圳融资融券数据
szrzye_df = self.get_SZ_Margins(sdate, edate)
#计算所有融资余额
szrzye_group = szrzye_df.groupby('mt_code')
shrzye_group = shrzye_df.groupby('mt_code')
szrzye_dict = dict(list(szrzye_group))
shrzye_dict = dict(list(shrzye_group))
rzye_dict = dict(szrzye_dict, **shrzye_dict)
rzye_series = pd.Series()
for rdict in rzye_dict:
dictItem = rzye_dict[rdict]
if len(dictItem) >= 1:
headItem = dictItem.head(1)
tailItem = dictItem.tail(1)
headItem.set_index(headItem['mt_code'], inplace=True)
tailItem.set_index(tailItem['mt_code'], inplace=True)
tmpItem = (tailItem['mt_rzye'] - headItem['mt_rzye']) / 10000
rzye_series = rzye_series.append(tmpItem)
rzye_df = rzye_series.to_frame('mt_rzye')
rzye_df['mt_code'] = rzye_df.index
rzye_df.sort_values(by=['mt_rzye'], ascending=False, inplace=True)
rzyeindexlist = range(len(rzye_df))
rzye_df.index = rzyeindexlist
rzye_df.rename(columns={'mt_code': 'hq_code'}, inplace=True)
#获取30,200标的的数据
c = colligation.ZH(sdate, edate)
rzye30_df = c.getRF(30, rzye_df)
rzye200_df = c.getRF(200, rzye_df)
atr_30 = c.getRF(30, atr_all)
atr_200 = c.getRF(200, atr_all)
return rzye200_df, rzye30_df, atr_200, atr_30, rzye_df, atr_all
def indexBigCashflow(self,stime,etime):
import tushare as ts
c=colligation.ZH(stime,etime)
amosql="select hq_code,amo,vol from stocksystem.bigcashflow where datetime>='"+stime+ "'and datetime <='"+etime+"'"
print amosql
amodata=pd.read_sql(amosql,con=self.engine,index_col='hq_code')
#amodata['vol']=(amodata['vol'])/100000000
amodata=amodata.groupby(level=0).sum()
#老代码
#获得股票基本信息
# stockbasics=ts.get_stock_basics()
# stockbasics.reset_index(inplace=True)
# stockbasics['code']=stockbasics['code'].astype(np.int)
# stockbasics.set_index('code',inplace=True)
# amodata['outgb']=stockbasics['outstanding']
# amodata.sort_values('volper',ascending=False,inplace=True)
# amodata['volper']=amodata['vol']/amodata['outgb']
stockdata=ts.get_today_all()
stockdata['code']=stockdata['code'].astype(int)
stockdata.set_index('code',inplace=True)
#得到流通股本
amodata['nmc']=stockdata['nmc']
amodata['amoper']=amodata['amo']/np.abs(amodata['amo']).sum()
amodata['nmcper']=stockdata['nmc']/(stockdata['nmc'].sum())
amodata['amopower']=amodata['amoper']/amodata['nmcper']
amodata['hq_name']=c.getStockNames(amodata,c.stockNames)
amodata=c.getBoard(3000,amodata)
amodata=amodata.sort_values('amopower',ascending=False)
amo_stock=amodata.loc[:,['hq_name','board_name','amo','amopower']]
amo_stock.to_csv(u'E:\\工作\\数据备份\\板块资金流\\stockbig'+stime.replace(':','时')+'至'+etime.replace(':','时')+'.csv',encoding='gbk',float_format='%.2f')
del amodata['hq_name'],amodata['amopower'],amodata['nmc'],amodata['vol']
amodata=amodata.groupby('board_name').sum()
amodata['amopower']=amodata['amoper']/amodata['nmcper']
#amodata['volper']=amodata['vol']/amodata['outgb']
amodata.sort_values('amopower',ascending=False,inplace=True)
amo_index=amodata.loc[:,['amo','amoper','amopower']]
amo_index.reset_index(inplace=True)
amo_index.columns=[u'行业',u'成交量',u'大单占比',u'效能']
amo_stock.columns=[u'股票',u'行业',u'成交量',u'效能']
amo_stock[u'排名']=np.arange(len(amo_stock))
#amo_index.to_csv(u'E:\\工作\\数据备份\\板块资金流\\indexbig'+stime.replace(':','时')+'至'+etime.replace(':','时')+'.csv',encoding='gbk',float_format='%.2f')
# p=plotToExcel.PlotToExcel()
period=stime.replace(':','时')+'至'+etime.replace(':','时')
# p.buildCashflow(amo_stock,amo_index,period)
top=0
left=0
wbk =xlsxwriter.Workbook(u'E:\\工作\\报表\\资金流\\'+period+'flow.xlsx')
zw=wbk.add_format({'align':'center','valign':'vcenter','font_size':10,'num_format':'0.00'})
ZJ_Sheet = wbk.add_worksheet(u'大单效能')
for row in xrange(len(amo_index)):
if row==0:
ZJ_Sheet.write_row(top+row, left,amo_index,zw)
ZJ_Sheet.write_row(top+row+1, left,amo_index.iloc[row],zw)
left+=6
for board in amo_index[u'行业']:
tmpstock=amo_stock[amo_stock[u'行业']==board]
for row in xrange(len(tmpstock)):
if row==0:
ZJ_Sheet.write_row(top+row, left,tmpstock,zw)
ZJ_Sheet.write_row(top+row+1, left,tmpstock.iloc[row],zw)
top+=len(tmpstock)+2
left+=6
top=0
for row in xrange(len(amo_stock)):
if row==0:
ZJ_Sheet.write_row(top+row, left,tmpstock,zw)
ZJ_Sheet.write_row(top+row+1, left,amo_stock.iloc[row],zw)
wbk.close()
return amodata
def bigCashflow(self, stime, etime):
def picBigCashFlow(amo_index, amo_stock, amo_indexnet, amo_stocknet,
period, networth):
top = 0
left = 0
if networth == 1:
wbk = xlsxwriter.Workbook(u'E:\\work\\报表\\资金流\\ ' + period +
' networth.xlsx')
else:
wbk = xlsxwriter.Workbook(u'E:\\work\\报表\\资金流\\ ' + period +
' flow.xlsx')
zw = wbk.add_format({
'align': 'center',
'valign': 'vcenter',
'font_size': 10,
'num_format': '0.00'
})
ZJ_Sheet = wbk.add_worksheet(u'大单效能')
for row in xrange(len(amo_index)):
if row == 0:
ZJ_Sheet.write_row(top + row, left, amo_index, zw)
ZJ_Sheet.write_row(top + row + 1, left, amo_index.iloc[row],
zw)
left += 6
usestock = amo_stock[amo_stock[u'排名'] <= 500]
for board in amo_index[u'行业']:
tmpstock = usestock[usestock[u'行业'] == board]
for row in xrange(len(tmpstock)):
if row == 0:
ZJ_Sheet.write_row(top + row, left, tmpstock, zw)
ZJ_Sheet.write_row(top + row + 1, left, tmpstock.iloc[row],
zw)
top += len(tmpstock) + 2
left += 6
top = 0
for row in xrange(len(amo_stock)):
if row == 0:
ZJ_Sheet.write_row(top + row, left, tmpstock, zw)
ZJ_Sheet.write_row(top + row + 1, left, amo_stock.iloc[row],
zw)
left += 6
for row in xrange(len(amo_index)):
if row == 0:
ZJ_Sheet.write_row(top + row, left, amo_indexnet, zw)
ZJ_Sheet.write_row(top + row + 1, left, amo_indexnet.iloc[row],
zw)
left += 6
usestock = amo_stocknet[amo_stocknet[u'排名'] <= 500]
for board in amo_indexnet[u'行业']:
tmpstock = usestock[usestock[u'行业'] == board]
for row in xrange(len(tmpstock)):
if row == 0:
ZJ_Sheet.write_row(top + row, left, tmpstock, zw)
ZJ_Sheet.write_row(top + row + 1, left, tmpstock.iloc[row],
zw)
top += len(tmpstock) + 2
left += 6
top = 0
for row in xrange(len(amo_stocknet)):
if row == 0:
ZJ_Sheet.write_row(top + row, left, tmpstock, zw)
ZJ_Sheet.write_row(top + row + 1, left, amo_stocknet.iloc[row],
zw)
wbk.close()
amodata, amonet = self.zqAmoMin(stime, etime)
c = colligation.ZH(stime, etime)
amonet['hq_name'] = amodata['hq_name'] = c.getStockNames(
amodata, c.stockNames)
amodata = c.getBoard(3000, amodata)
amonet['board_name'] = amodata['board_name']
amo_stock = amodata.loc[:,
['hq_name', 'board_name', 'amo', 'amopower'
]].dropna()
amo_stocknet = amonet.loc[:,
['hq_name', 'board_name', 'amo', 'amopower'
]].dropna()
#amo_stock.to_csv(u'E:\\work\\数据备份\\板块资金流\\stockbig'+stime.replace(':','时')+'至'+etime.replace(':','时')+'.csv',encoding='gbk',float_format='%.2f')
del amodata['hq_name'], amodata['amopower'], amonet['hq_name'], amonet[
'amopower']
amodata = amodata.groupby('board_name').sum()
amodata['amopower'] = amodata['amoper'] / amodata['nmcper']
amodata.sort_values('amopower', ascending=False, inplace=True)
amo_index = amodata.loc[:, ['amo', 'amoper', 'amopower']].dropna()
amo_index.reset_index(inplace=True)
amonet = amonet.groupby('board_name').sum()
amonet['amopower'] = amonet['amoper'] / amonet['nmcper']
amonet.sort_values('amopower', ascending=False, inplace=True)
amo_indexnet = amonet.loc[:, ['amo', 'amoper', 'amopower']].dropna()
amo_indexnet.reset_index(inplace=True)
amo_indexnet.columns = [u'行业', u'大单净额', u'大单占比', u'效能']
amo_stocknet.columns = [u'股票', u'行业', u'大单净额', u'效能']
amo_index.columns = [u'行业', u'大单总量', u'大单占比', u'效能']
amo_stock.columns = [u'股票', u'行业', u'大单总量', u'效能']
amo_stock[u'排名'] = np.arange(1, len(amo_stock) + 1)
amo_stocknet[u'排名'] = np.arange(1, len(amo_stocknet) + 1)
period = stime.replace(':', '时') + '至' + etime.replace(':', '时')
picBigCashFlow(amo_index,
amo_stock,
amo_indexnet,
amo_stocknet,
period,
networth=0)
if __name__ == '__main__':
z = ZJ()
# update=input('Do you want to update?')
# if update==True:
# z.tickTradeType('20170703','20170714')
sdate = '2017-11-20'
edate = '2017-11-20'
#fdate=sdate.replace('-','')
#z.tickTradeType(sdate.replace('-',''),edate.replace('-',''))
c = colligation.ZH(sdate, edate)
#c.buildMinRankForm(factor=True)
df_period_rank = z.plot_board_zjrank('2017-11-13', '2017-11-20 ')
#
## z.bigCashflow(sdate,sdate)
#z.zqAllAmo('20170710','20170718')
#z.tickCountDay('20170719','20170719')
#amodata=z.zqAmoDay('2017-07-19','2017-07-19')
#data200,data30=z.zqAmo('2017-06-26','2017-06-30')
#生成基础数据，并导出到CSV
#s=z.zqAmoMin('2017-07-06 14:30','2017-07-07 14:59')
#amodata=z.zqAmoMin('2017-07-06 14:30','2017-07-07 14:59')