数据研究
import pandas as pd
df=pd.read_csv('passwd.txt',dtype={'port':int},sep='|')
(host,port,user,passwd)=df.iloc[0]
import sqlalchemy as sa
import os
import pandas as pd
import MySQLdb
conn=MySQLdb.connect(host=host,user=user,passwd=passwd,db='test',port=port,charset="utf8")
近年北上净流入情况¶
2015年股灾期间，北上资金持续抄底，在指数上涨后有一波减持。2016年之后北上资金开始持续净流入
hsgt_stat_cumsum_df = pd.read_sql('select * from hsgt_stat_cumsum_df;', con=conn)
#hsgt_stat_cumsum_df.tail(4)
import matplotlib
from pylab import mpl
matplotlib.rcParams['axes.unicode_minus']=False
myfont = matplotlib.font_manager.FontProperties(fname='C:/Windows/Fonts/msyh.ttf')
mpl.rcParams['axes.unicode_minus'] = False
mpl.rcParams['font.sans-serif'] = ['SimHei']
import matplotlib.pyplot as plt
fig = plt.figure(figsize =(12,6))
ax1 = fig.add_subplot(111)
plt.xticks(rotation=25)
ax1.plot( pd.to_datetime(hsgt_stat_cumsum_df['date'].tolist()), hsgt_stat_cumsum_df['close'].tolist(),color="red",label=u'sz' ) #linestyle="--",
ax1.set_title( u"HS300&北上净资金" ,fontsize =12)
ax2 = ax1.twinx()
ax2.plot( pd.to_datetime( hsgt_stat_cumsum_df['date'].tolist()),hsgt_stat_cumsum_df['north_cumsum'].tolist(),color="blue",label=u'北上资金累计资金')# color='red' , label=u'质押数量'
ax1.set_ylabel(u'HS300指数',fontsize =12)
ax2.set_ylabel(u'北上累积资金')
ax1.legend( loc="upper left",fontsize =12)
ax2.legend( loc="upper center",fontsize =12)
北上资金HS300成本线 及 HS300对应的持股数¶
close 为hs300 指数走势，meanPrice为北上资金对应的HS300指数的持仓成本 持仓成本计算为 累积投入资金/当前持有的股数 ; 每日股数变动为= 当日北上净流入流出值/当日指数 ; 当前持股=累加历史上每天的股票变动
import copy
tmp_df=copy.deepcopy( hsgt_stat_cumsum_df )
tmp_df.index=tmp_df['date'] #data[['indexPrice','meanPrice']].plot(figsize=(14,5),linewidth=2)
tmp_df[['close','meanPrice']].plot(figsize=(14,8),linewidth=2,legend=True,title=u'hs300股价 & 北上资金持仓成本') #[tmp_df['date']>'2015-01-01']
plt.figure(2)
tmp_df['accumShare'].plot(figsize=(14,3),color='red',title=u'累计份额(参考 百万)',linewidth=2,legend=True)
近期资金流入流出¶
资金行业流入情况：¶
inustry_in_df = pd.read_sql('select * from inustry_in_df;', con=conn)
inustry_in_df
北上资金行业流出情况：¶
inustry_out_df = pd.read_sql('select * from inustry_out_df;', con=conn)
inustry_out_df
近期北上资金净买入净卖出个股(按净买入倒排)¶
单位： 亿
recent_stks_trad_top_df = pd.read_sql('select * from recent_stks_trad_top_df;', con=conn)
recent_stks_trad_top_df['JME']=recent_stks_trad_top_df['JME']/100000000
recent_stks_trad_top_df['MRJE']=recent_stks_trad_top_df['MRJE']/100000000
recent_stks_trad_top_df['MCJE']=recent_stks_trad_top_df['MCJE']/100000000
recent_stks_trad_top_df['CJJE']=recent_stks_trad_top_df['CJJE']/100000000
recent_stks_trad_top_df[['date','stk','stkname','JME','MRJE','MCJE','CJJE']].sort(['date','JME'],ascending=False).rename(columns={'JME':u'净买入额','MRJE':u'买入金额','MCJE':u'卖出金额','CJJE':u'成交金额'}).head(20)
conn.close()
from IPython.display import display
from IPython.display import HTML
import IPython.core.display as di
# This line will hide code by default when the notebook is exported as HTML
di.display_html('''<script>
$(function() {
String.prototype.startWith=function(str){
var reg=new RegExp("^"+str);
return reg.test(this);
}
String.prototype.endWith=function(str){
var reg=new RegExp(str+"$");
return reg.test(this);
}
if ($("body.notebook_app").length == 0) {
$(".input_area").empty().toggle();
$(".prompt").empty().toggle();
$(".output_stderr").empty().toggle();
$("pre").each(function(idx, val){
if(this.innerHTML.startWith('&')){
$(this).empty().toggle();
}
})
$(".code_cell").css("padding", '0px');
$(".code_cell").css("border", '0px');
$("#notebook-container").css("padding", '0px');
$("div.output_subarea").css("min-width", '100%');
}
});
</script>''', raw=True)
