大家好!
基金定投大家肯定不陌生,要么按周定投、要么按月定投,但是不知道大家有没有想过到底哪一天进行扣款会相对合理呢?
今天来分享一下如何用Python工具来帮助我们判断指数基金定投的最佳扣款时间(数据口径:2008-2020年每日大盘数据)。
案例中数据:
2008年-2020年2月每天的上证指数数据
代码及演示:
import numpy as np
import pandas as pd
#读取2008年-2020年2月每天的上证指数数据
df1=pd.read_excel("D:\\01\\会Excel的隔壁老王\\Excel报表\\20200319-Python实例:大数据帮你判断指数基金定投的每周最佳扣款时间\\000001_2008-01-01_2020-02-29_history_A_stock_k_data.xlsx")
#选择我们需要的列
df_shares = df1[["date","code","open","high","low","close","pctChg"]]
df_shares
#添加rise_fall列,标志当天大盘涨跌情况
#pctChg列,if值>0的 = rise,else = fall
def function(x):
if x > 0:
return "rise"
else:
return "fall"
df_shares["rise_fall"]=df_shares.apply(lambda x: function(x.pctChg), axis=1)
df_shares
#添加date列的星期weekday列
df_shares["weekday"]=pd.DatetimeIndex(df_shares["date"]).weekday+1
df_shares
#数据透视,查看2008年-2020年2月的大盘涨跌情况
df_shares_pivot_table_1=pd.pivot_table(df_shares,index=["code","rise_fall","weekday"],values=["close"], aggfunc=np.count_nonzero, margins=all)
df_shares_pivot_table_1
#合并单元格拆分
df_shares_pivot_table_reset_1 = df_shares_pivot_table_1.reset_index()
df_shares_pivot_table_reset_1
#筛选出大盘下帖的数据
df_shares_pivot_table_reset_1_fall = df_shares_pivot_table_reset_1[ df_shares_pivot_table_reset_1.rise_fall == "fall" ]
df_shares_pivot_table_reset_1_fall
#显示1:"周一",2:"周二",3:"周三",4:"周四",5:"周五"
df_shares_pivot_table_reset_1_fall_replace = df_shares_pivot_table_reset_1_fall.replace({1:"周一",2:"周二",3:"周三",4:"周四",5:"周五",})
df_shares_pivot_table_reset_1_fall_replace
#画柱状图
from pyecharts import options as opts
from pyecharts.charts import *
from pyecharts.commons.utils import JsCode
from pyecharts.globals import ThemeType
values = []
for i in df_shares_pivot_table_reset_1_fall_replace.values:
dic = {}
dic["value"] = i[3]
values.append(dic)
c = (
Bar(init_opts=opts.InitOpts(width="800px", height="350px",theme = ThemeType.DARK ))
.add_xaxis( list( df_shares_pivot_table_reset_1_fall_replace["weekday"].values ) )
.add_yaxis("2018-2020年工作日大盘下跌次数", values, category_gap="60%")
.set_series_opts(
itemstyle_opts={
"normal": {
"color": JsCode(
"""new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
offset: 0,
color: 'rgba(22, 77, 167, 1)'
}, {
offset: 1,
color: 'rgba(77, 160, 321, 1)'
}], false)"""
),
"barBorderRadius": [30, 30, 30, 30],
"shadowColor": "rgb(77, 160, 321)",
}
}
)
.set_global_opts(title_opts=opts.TitleOpts(subtitle = "结论:按照2008年-2020年2月的历史数据统计,一般周四定投的平均成本更低!" ,title="Python:大数据帮你判断指数基金定投的每周最佳扣款时间(微信公众号:会Excel的隔壁老王)", pos_top = "1%", pos_right = "0%"),legend_opts=opts.LegendOpts( pos_top = "93%"))
#.render("bar_border_radius.html")
)
c.render_notebook()
完整python代码参考:
import numpy as np
import pandas as pd
#读取2008年-2020年2月每天的上证指数数据
df1=pd.read_excel("D:\\01\\会Excel的隔壁老王\\Excel报表\\20200319-Python实例:大数据帮你判断指数基金定投的每周最佳扣款时间\\000001_2008-01-01_2020-02-29_history_A_stock_k_data.xlsx")
#选择我们需要的列
df_shares = df1[["date","code","open","high","low","close","pctChg"]]
#添加rise_fall列,标志当天大盘涨跌情况
#pctChg列,if值>0的 = rise,else = fall
def function(x):
if x > 0:
return "rise"
else:
return "fall"
df_shares["rise_fall"]=df_shares.apply(lambda x: function(x.pctChg), axis=1)
#添加date列的星期weekday列
df_shares["weekday"]=pd.DatetimeIndex(df_shares["date"]).weekday+1
#数据透视,查看2008年-2020年2月的大盘涨跌情况
df_shares_pivot_table_1=pd.pivot_table(df_shares,index=["code","rise_fall","weekday"],values=["close"], aggfunc=np.count_nonzero, margins=all)
#合并单元格拆分
df_shares_pivot_table_reset_1 = df_shares_pivot_table_1.reset_index()
#筛选出大盘下帖的数据
df_shares_pivot_table_reset_1_fall = df_shares_pivot_table_reset_1[ df_shares_pivot_table_reset_1.rise_fall == "fall" ]
#显示1:"周一",2:"周二",3:"周三",4:"周四",5:"周五"
df_shares_pivot_table_reset_1_fall_replace = df_shares_pivot_table_reset_1_fall.replace({1:"周一",2:"周二",3:"周三",4:"周四",5:"周五",})
#画柱状图
from pyecharts import options as opts
from pyecharts.charts import *
from pyecharts.commons.utils import JsCode
from pyecharts.globals import ThemeType
values = []
for i in df_shares_pivot_table_reset_1_fall_replace.values:
dic = {}
dic["value"] = i[3]
values.append(dic)
c = (
Bar(init_opts=opts.InitOpts(width="800px", height="350px",theme = ThemeType.DARK ))
.add_xaxis( list( df_shares_pivot_table_reset_1_fall_replace["weekday"].values ) )
.add_yaxis("2018-2020年工作日大盘下跌次数", values, category_gap="60%")
.set_series_opts(
itemstyle_opts={
"normal": {
"color": JsCode(
"""new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
offset: 0,
color: 'rgba(22, 77, 167, 1)'
}, {
offset: 1,
color: 'rgba(77, 160, 321, 1)'
}], false)"""
),
"barBorderRadius": [30, 30, 30, 30],
"shadowColor": "rgb(77, 160, 321)",
}
}
)
.set_global_opts(title_opts=opts.TitleOpts(subtitle = "结论:按照2008年-2020年2月的历史数据统计,一般周四定投的平均成本更低!" ,title="Python:大数据帮你判断指数基金定投的每周最佳扣款时间(微信公众号:会Excel的隔壁老王)", pos_top = "1%", pos_right = "0%"),legend_opts=opts.LegendOpts( pos_top = "93%"))
#.render("bar_border_radius.html")
)
c.render_notebook()