def getstockindicator(ts_codes, st_date, ed_date):
if isinstance(ts_codes, str):
ts_codes = [ts_codes]
return_mean, return_volatility = [], []
return_mean_2, return_volatility_2 = pd.Series(dtype=float), pd.Series(dtype=float)
stock_price = kongo.Kongo().getstockdailyfrommongo(st_date, ed_date, ts_codes, 'qfq')
# stock_price = stock_price[["trade_date", "close"]].copy()
stock_price.set_index('trade_date')
stock_pivot = stock_price[["trade_date", "ts_code", "close"]].pivot_table(index='trade_date', columns='ts_code', values='close').copy()
for i, ts_code in enumerate(ts_codes):
stock_pivot.loc[:, ts_code+"_return"] = np.log(stock_pivot.loc[:, ts_code] / stock_pivot.loc[:, ts_code].shift(1))
stock_pivot.dropna(inplace=True)
return_mean.append(round(pd.Series(stock_pivot[ts_code+"_return"]).mean() * 252, 6)) # 计算股票的平均年化收益率
return_volatility.append(pd.Series(stock_pivot[ts_code+"_return"]).std()*np.sqrt(252)) # 计算股票的年化收益率波动率
return_mean_2 = pd.Series(return_mean)
return_volatility_2 = pd.Series(return_volatility)
stock_pivot.drop(columns=ts_codes, inplace=True)
return stock_pivot, return_mean_2, return_volatility_2
def get_index_smb_hml(start_date, end_date):
con = kongo.Kongo()
df_bm_mv = con.getstockdailybasicfrommongo(start_date, end_date)
df_bm_mv.loc[:,
"bm"] = df_bm_mv.loc[:, "pb"]**-1 * df_bm_mv.loc[:,
"total_mv"]
df_bm_mv = df_bm_mv[["ts_code", "bm", "total_mv"]]
df_bm_mv.rename(columns={"total_mv": "mv"}, inplace=True)
# df.drop_duplicates(subset=["ts_code"], inplace=True)
size_bm = df_bm_mv["bm"].quantile([0.3, 0.7]).tolist()
size_mv = df_bm_mv["mv"].quantile(.5)
print("市值和账面价值进行分类...")
df_bm_mv = df_bm_mv.apply(lambda x: category_bm_mv(x, size_bm, size_mv),
axis=1)
print("市值和账面价值分类完成...")
# 获取股票期间收益
stock_list = df_bm_mv['ts_code'].tolist()
print("获取所有股票的期间收益...")
return_sum = formula_1.getstockreturnsum(start_date, end_date, stock_list)
# 期间收益表和bm mv表两表合并
print("合并股票分类和期间收益...")
df_bm_mv_return = pd.merge(df_bm_mv, return_sum, on='ts_code')
df_index = pd.DataFrame()
# 合并后的表按组编码来分组
df_grouped = df_bm_mv_return.groupby("group")
df_index_groups = df_grouped.size().index
print(df_bm_mv_return)
def is_tradeday(date):
db = kg.Kongo()
_df_calendar = db.gettradecalfrommongo(date, date)
df_cal = _df_calendar
if df_cal.loc[0, 'is_open']:
return date
else:
return df_cal.loc[0, 'pretrade_date']
def get_pe_3years(period):
years = get_recent_3years_pe(period)
db = kg.Kongo()
_df_list = db.getStockAllFromMongo(fields='ts_code,symbol,name')
df_pe_ttm = _df_list
_df_pe_ttm_year1 = db.getDailyBasicFromMongo(start_date=years[0],
end_date=years[0],
fields='pe_ttm')
_df_pe_ttm_year2 = db.getDailyBasicFromMongo(start_date=years[1],
end_date=years[1],
fields='pe_ttm')
_df_pe_ttm_year3 = db.getDailyBasicFromMongo(start_date=years[2],
end_date=years[2],
fields='pe_ttm')
df_pe_ttm = pd.merge(df_pe_ttm['ts_code'],
_df_pe_ttm_year1[['ts_code',
'pe_ttm']].drop_duplicates(),
left_on='ts_code',
right_on='ts_code',
how='left')
df_pe_ttm.rename(columns={'pe_ttm': 'pe_ttm_year1'}, inplace=True)
df_pe_ttm = pd.merge(df_pe_ttm,
_df_pe_ttm_year2[['ts_code',
'pe_ttm']].drop_duplicates(),
left_on='ts_code',
right_on='ts_code',
how='left')
df_pe_ttm.rename(columns={'pe_ttm': 'pe_ttm_year2'}, inplace=True)
df_pe_ttm = pd.merge(df_pe_ttm,
_df_pe_ttm_year3[['ts_code',
'pe_ttm']].drop_duplicates(),
left_on='ts_code',
right_on='ts_code',
how='left')
df_pe_ttm.rename(columns={'pe_ttm': 'pe_ttm_year3'}, inplace=True)
df_pe_ttm.fillna(value=0, inplace=True)
return df_pe_ttm
def get_roic_3years(period):
years = get_recent_3years_roic(period)
db = kg.Kongo()
_df_list = db.getStockAllFromMongo(fields='ts_code,symbol,name')
df_roic = _df_list
_df_roic_year1 = db.getStockFinIndicatorFromMongo(start_date=years[0],
end_date=years[0],
fields="roic")
_df_roic_year2 = db.getStockFinIndicatorFromMongo(start_date=years[1],
end_date=years[1],
fields="roic")
_df_roic_year3 = db.getStockFinIndicatorFromMongo(start_date=years[2],
end_date=years[2],
fields="roic")
df_roic = pd.merge(df_roic[['ts_code', 'symbol', 'name']],
_df_roic_year1[['ts_code', 'roic'
]].drop_duplicates(subset=['ts_code']),
left_on='ts_code',
right_on='ts_code',
how='left')
df_roic.rename(columns={'roic': 'roic_year1'}, inplace=True)
df_roic = pd.merge(df_roic,
_df_roic_year2[['ts_code', 'roic'
]].drop_duplicates(subset=['ts_code']),
left_on='ts_code',
right_on='ts_code',
how='left')
df_roic.rename(columns={'roic': 'roic_year2'}, inplace=True)
df_roic = pd.merge(df_roic,
_df_roic_year3[['ts_code', 'roic'
]].drop_duplicates(subset=['ts_code']),
left_on='ts_code',
right_on='ts_code',
how='left')
df_roic.rename(columns={'roic': 'roic_year3'}, inplace=True)
df_roic.fillna({'roic': np.nan}, inplace=True)
return df_roic
def getformuladata(st_date, ed_date, ts_codes, index_codes="", freq="D"):
if isinstance(ts_codes, str):
ts_codes = [ts_codes]
if isinstance(index_codes, str):
index_codes = [index_codes]
if index_codes != [""]:
total_codes = ts_codes + index_codes
else:
total_codes = ts_codes
return_mean, return_volatility = [], []
return_mean_2, return_volatility_2 = pd.Series(dtype=float), pd.Series(
dtype=float)
stock_price = kongo.Kongo().getstockdailyfrommongo(st_date,
ed_date,
ts_codes,
method='stock',
adj='qfq')
index_price = kongo.Kongo().getstockdailyfrommongo(st_date,
ed_date,
index_codes,
method='index')
if not index_price.empty:
new_price = pd.concat([
stock_price[["trade_date", "ts_code", "close"]],
index_price[["trade_date", "ts_code", "close"]]
],
ignore_index=True)
else:
new_price = stock_price
new_price.set_index('trade_date', inplace=True)
new_price.index = pd.DatetimeIndex(new_price.index)
stock_pivot = new_price[["ts_code",
"close"]].pivot_table(index='trade_date',
columns='ts_code',
values='close').copy()
for i, ts_code in enumerate(total_codes):
stock_pivot.loc[:, ts_code] = np.log(
stock_pivot.loc[:, ts_code] / stock_pivot.loc[:, ts_code].shift(1))
return_mean.append(
round(pd.Series(stock_pivot[ts_code]).mean() * 250,
6)) # 计算股票的平均年化收益率
return_volatility.append(
pd.Series(stock_pivot[ts_code]).std() *
np.sqrt(250)) # 计算股票的年化收益率波动率
return_mean_2 = pd.Series(return_mean)
return_volatility_2 = pd.Series(return_volatility)
# stock_pivot.drop(columns=total_codes, inplace=True)
stock_pivot.dropna(inplace=True)
if freq == "W":
stock_pivot = pd.DataFrame(stock_pivot.resample('W-FRI').sum())
return stock_pivot, return_mean_2, return_volatility_2
Rf = 0.03
w = np.array(w)
Rp = np.sum(w * return_mean)
Vp = np.sqrt(np.dot(w, np.dot(return_cov, w.T)))
SR = (Rp - Rf) / Vp
print(SR)
return np.array([Rp, Vp, SR])
def SRmin_F(w):
return -F(w)[2]
if __name__ == '__main__':
df = kongo.Kongo().getstockbasicallfrommongo()
x = np.random.randint(0, len(df), 10)
df2 = df.iloc[x]
stock_list = pd.Series(df2['ts_code']).to_list()
stock_return, return_mean, return_volatility = getformuladata(
"20190701", "20210630", stock_list)
print(return_mean)
cons = ({'type': 'eq', 'fun': lambda x: np.sum(x) - 1})
bnds = tuple((0, 1) for x in range(len(return_mean)))
w0 = np.ones_like(return_mean) / len(return_mean.index)
result = sco.minimize(SRmin_F,
w0,
import numpy as np
import pandas as pd
import statsmodels.api as sm
import formula_1
import kongo
if __name__ == '__main__':
kon = kongo.Kongo()
start_date, end_date = '20200701', '20210630'
report_date = '20210331'
df_sk = kon.getstockbasicallfrommongo()
df_sk_grouped = df_sk.groupby('market')
bank_list = list(df_sk_grouped.groups.keys())
bank_list.pop(0)
index_list = ['399001.SZ', '000001.SH', '399006.SZ', '000688.SH']
bank_dict = dict(zip(bank_list, index_list))
with pd.ExcelWriter('pvgo_' + start_date + '_' + end_date + '.xlsx',
mode='w',
engine="openpyxl") as writer:
df_result_sum = pd.DataFrame()
for bank in bank_list:
df_bank = df_sk_grouped.get_group(bank)
list_zb = df_bank['ts_code'].tolist()
def drawpic():
db = kongo.Kongo()
df_stock = db.getstocktscodebyname(["交通银行"])
df_index = db.getindextscodebyname(["上证指数", "上证50", "上证180", "沪深300"])
stock_list = df_stock['ts_code'].tolist()
index_list = df_index['ts_code'].tolist()
stock_return, return_mean, return_volatility = getstockdata("20110630",
"20210630",
stock_list,
index_list,
method='W')
plt.Figure(figsize=(10, 10))
plt.subplot(2, 2, 1)
plt.scatter(x=stock_return['000001.SH_return'],
y=stock_return['601328.SH_return'],
c='b',
marker='o')
plt.xticks(fontsize=13)
plt.xlabel(u'上证指数', fontsize=13)
plt.yticks(fontsize=13)
plt.ylabel(u'交通银行', fontsize=13, rotation=90)
plt.grid()
plt.subplot(2, 2, 2)
plt.scatter(x=stock_return['000010.SH_return'],
y=stock_return['601328.SH_return'],
c='c',
marker='o')
plt.xticks(fontsize=13)
plt.xlabel(u'上证50', fontsize=13)
plt.yticks(fontsize=13)
plt.ylabel(u'交通银行', fontsize=13, rotation=90)
plt.grid()
plt.subplot(2, 2, 3)
plt.scatter(x=stock_return['000016.SH_return'],
y=stock_return['601328.SH_return'],
c='m',
marker='o')
plt.xticks(fontsize=13)
plt.xlabel(u'上证180', fontsize=13)
plt.yticks(fontsize=13)
plt.ylabel(u'交通银行', fontsize=13, rotation=90)
plt.grid()
plt.subplot(2, 2, 4)
plt.scatter(x=stock_return['399300.SZ_return'],
y=stock_return['601328.SH_return'],
c='y',
marker='o')
plt.xticks(fontsize=13)
plt.xlabel(u'沪深300', fontsize=13)
plt.yticks(fontsize=13)
plt.ylabel(u'交通银行', fontsize=13, rotation=90)
plt.grid()
plt.show()
plt.subplot(2, 2, 4)
plt.scatter(x=stock_return['399300.SZ_return'],
y=stock_return['601328.SH_return'],
c='y',
marker='o')
plt.xticks(fontsize=13)
plt.xlabel(u'沪深300', fontsize=13)
plt.yticks(fontsize=13)
plt.ylabel(u'交通银行', fontsize=13, rotation=90)
plt.grid()
plt.show()
if __name__ == '__main__':
db = kongo.Kongo()
df_stock = db.getstocktscodebyname(["赛升药业"])
df_index = db.getindextscodebyname(
["上证指数", "上证50", "上证180", "沪深300", "创业板指"])
stock_list = df_stock['ts_code'].tolist()
index_list = df_index['ts_code'].tolist()
stock_return, return_mean, return_volatility = getstockdata("20150630",
"20210630",
stock_list,
index_list,
freq='W')
print(stock_return)
SZ_index = stock_return['000001.SH']
