def attribution_one_fund(self):
""" 测试单个基金归因 """
# fund_name = '建行中国人寿多策略管理计划'
# beg_date = '20190101'
# end_date = '20190312'
# period_name = "2019年至今（20190312）"
fund_name = '泰达宏利启富'
beg_date = '20180625'
end_date = '20190411'
period_name = "20180625至20190411"
param = MfcData().get_mfc_fund_info()
param.index = param.Name
param = param.dropna(subset=['Index'])
index_code_ratio = param.loc[fund_name, "Index_Ratio"]
fund_code = param.loc[fund_name, "Code"]
index_code = param.loc[fund_name, "Index"]
type = param.loc[fund_name, "Type"]
trust_fee = param.loc[fund_name, "TrusteeShipFeeRatio"]
mg_fee = param.loc[fund_name, "MgFeeRatio"]
mg_fee_ratio = trust_fee + mg_fee
self.get_info(index_code_ratio, index_code, fund_code, fund_name, type,
beg_date, end_date, period_name, mg_fee_ratio)
self.attribution_fund()
def attribution_all_fund(self, date_list):
""" 归因所有基准不为空的基金 给定所有时间段 """
param = MfcData().get_mfc_fund_info()
param.index = param.Name
param = param.dropna(subset=['Index'])
print(param.index)
for i_date in range(len(date_list)):
beg_date = date_list[i_date][0]
end_date = date_list[i_date][1]
period_name = date_list[i_date][2]
for i_fund in range(0, len(param)):
fund_name = param.index[i_fund]
index_code_ratio = param.loc[fund_name, "Index_Ratio"]
fund_code = param.loc[fund_name, "Code"]
index_code = param.loc[fund_name, "Index"]
type = param.loc[fund_name, "Type"]
trust_fee = param.loc[fund_name, "TrusteeShipFeeRatio"]
mg_fee = param.loc[fund_name, "MgFeeRatio"]
mg_fee_ratio = trust_fee + mg_fee
self.get_info(index_code_ratio, index_code, fund_code,
fund_name, type, beg_date, end_date, period_name,
mg_fee_ratio)
self.attribution_fund()
def get_fund_pct(self):
""" 产品某一段时间内的涨跌幅，专户公募分开 """
if self.type == "专户":
fund_pct = MfcData().get_mfc_private_fund_nav(self.fund_name)
fund_pct['基金涨跌幅'] = fund_pct['累计复权净值'].pct_change()
fund_pct = fund_pct[['累计复权净值', '基金涨跌幅']]
fund_pct = fund_pct.dropna()
fund_pct = fund_pct.loc[self.beg_date_pre:self.end_date, :]
else:
fund_pct = MfcData().get_mfc_public_fund_nav(self.fund_code)
fund_pct = fund_pct[['NAV_ADJ', 'NAV_ADJ_RETURN1']]
fund_pct['NAV_ADJ_RETURN1'] = fund_pct['NAV_ADJ'].pct_change()
fund_pct.columns = ['累计复权净值', '基金涨跌幅']
fund_pct = fund_pct.dropna()
fund_pct = fund_pct.loc[self.beg_date_pre:self.end_date, :]
return fund_pct
def cal_ipo_sell():
out_path = 'E:\\3_Data\\7_other_data\\4_cal_ipo_sell\\'
new_days = 60
today = datetime.today().strftime("%Y%m%d")
Date().load_trade_date_series("D")
# MfcData().load_ftp_daily(date=today)
# MfcData().change_ftp_file(date=today)
before_trade_data = Date().get_trade_date_offset(today, -1)
data = MfcData().get_fund_security(before_trade_data)
data = data.dropna(subset=['基金名称'])
data = data[['基金名称', '证券代码', '持仓', '证券类别']]
data.columns = ['FundName', 'StockCode', 'Holding', 'Type']
data = data[data.Type == '股票']
data.StockCode = data.StockCode.map(stock_code_add_postfix)
data["Market"] = data.StockCode.map(get_stcok_market)
Stock().load_all_stock_code_now()
Stock().load_ipo_date()
stock = Stock().get_ipo_date()
stock.columns = ['IpoDate', 'DelistDate']
stock['StockCode'] = stock.index
stock['IpoDate'] = stock['IpoDate'].map(lambda x: str(int(x)))
new_stock_date = datetime.today() - timedelta(days=new_days)
new_stock_date = new_stock_date.strftime("%Y%m%d")
all_data = pd.merge(data, stock, on=['StockCode'], how="left")
all_data = all_data[all_data.IpoDate > new_stock_date]
code_list = list(set(all_data.StockCode.values))
code_str = ','.join(code_list)
pct = w.wsq(code_str, "rt_pct_chg,rt_vol")
pct = pd.DataFrame(pct.Data, columns=pct.Codes, index=['Pct', 'Vol']).T
pct['StockCode'] = pct.index
new_data = pd.merge(all_data, pct, on=['StockCode'], how="left")
new_data = new_data[new_data['Vol'] > 0]
new_data = new_data[new_data['Pct'] < 0.09]
fund_list = list(set(data['FundName']))
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
fund_data = new_data[new_data.FundName == fund_name]
out_sub_path = os.path.join(out_path, today)
if not os.path.exists(out_sub_path):
os.mkdir(out_sub_path)
print(" Make Folder At ", today)
if len(fund_data) > 0:
warnings.filterwarnings("ignore")
fund_data_out = fund_data[['StockCode', 'Holding', 'Market']]
fund_data_out.columns = ['Ticker', 'Shares', 'Market']
fund_data_out['Direction'] = 2
fund_data_out['Price'] = 0
fund_data_out['Market Code'] = fund_data_out['Market'].map(
lambda x: 1 if x == 'SH' else 2)
fund_data_out['Price Model'] = 4
fund_data_out['Ticker'] = fund_data_out['Ticker'].map(
lambda x: x[0:6])
fund_data_out = fund_data_out[[
'Ticker', 'Direction', 'Shares', 'Price', 'Price Model',
'Market Code'
]]
file = fund_name + '.xls'
out_file = os.path.join(out_sub_path, file)
print(out_file)
fund_data_out.to_excel(out_file, index=None)
ftp = MyFtp()
ftp.connect()
ftp_file = os.path.join("\\ipo_stock\\", today, file)
ftp.upload_file(ftp_file, out_file)
ftp.close()
def cal_reverse_5days(self):
"""
每天开盘前计算 前5个交易日 各个基金的股票成交情况
不能自反向 ：若每只基金自身 前五天有卖出（买入）情况某只股票的情况 那么基金当天不能买入（卖出）
"""
day_period = 5
end_date = Date().get_trade_date_offset(self.today, -1)
beg_date = Date().get_trade_date_offset(end_date, -(day_period - 1))
trade_series = Date().get_trade_date_series(beg_date, end_date)
manager_info = MfcData().get_mfc_manage_info()
for i_col in range(len(manager_info.columns)):
person_name = manager_info.columns[i_col]
manager_fund = manager_info[person_name]
manager_fund = manager_fund.dropna()
fund_list = list(manager_fund.values)
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
print(" Cal Fund Reverse 5 days ", fund_name, person_name)
result = pd.DataFrame([])
for i_date in range(len(trade_series)):
cur_date = trade_series[i_date]
data = MfcData().get_trade_statement(cur_date)
data = data.dropna(subset=['基金名称'])
data = data[['基金名称', '证券代码', '委托方向', '成交数量', '资产类别']]
data.columns = [
'FundName', 'StockCode', 'Direction', 'TradeNumber',
'Type'
]
data = data[data.FundName == fund_name]
data = data[data.Type == '股票资产']
data.StockCode = data.StockCode.map(
CodeFormat().stock_code_add_postfix)
data.Direction = data.Direction.map(lambda x: 2
if x == '卖出' else 1)
result_date = pd.DataFrame(data.Direction.values,
index=data.StockCode.values,
columns=[cur_date])
try:
result = pd.concat([result, result_date], axis=1)
except Exception as e:
pass
result = result.fillna(0)
result.index.name = 'CODE'
result = result.astype(np.int)
my_result = []
for i_row in range(len(result)):
vals_list = list(result.ix[i_row, trade_series])
vals_set = list(set(result.ix[i_row, trade_series]))
if 2 in vals_set:
append_row = [result.index[i_row], 2]
append_row.extend(vals_list)
my_result.append(append_row)
if 1 in vals_set:
append_row = [result.index[i_row], 1]
append_row.extend(vals_list)
my_result.append(append_row)
col = ['CODE', 'FLAG']
col.extend(trade_series)
my_result = pd.DataFrame(my_result, columns=col)
out_sub_path = os.path.join(self.data_path, person_name,
self.today, 'reverse_5days')
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
out_file = os.path.join(out_sub_path, fund_name + '.csv')
my_result.to_csv(out_file, index=None)
print(fund_name, len(result), len(my_result))
def cal_ipo_mkt_monitor(self):
"""
每天开盘前计算 前20个交易日 公募基金 两市分别的股票市值
现在打新的下限 是在某个市场上 股票市值20个交易日的平均值 大于6000万
计算前20个交易日基金股票市值的平均值 利用20*6000万-前19个交易日的股票市值=昨天能够打新的市值下限
这样计算出的结果和昨天真实持仓的差就是 今天应该增加多少股票市值
所需要的文件是所有基金的证券持仓 和 当日的收盘价
"""
thread_value = 60000000
day_period = 20
person_list = ['liuxin', 'liuyang']
end_date = Date().get_trade_date_offset(self.today, -1)
beg_date = Date().get_trade_date_offset(end_date, -(day_period - 1))
trade_series = Date().get_trade_date_series(beg_date, end_date)
# 基金经理管理的基金
fund = MfcData().get_mfc_manage_info()
# 所需要的持仓文件
holding_data = pd.DataFrame([])
for i_date in range(len(trade_series)):
date = trade_series[i_date]
data = MfcData().get_group_security(date)
data = data.dropna(subset=['基金名称'])
data = data[['基金名称', '证券代码', '持仓', '证券类别', '最新价']]
data.columns = [
'FundName', 'StockCode', 'Holding', 'Type', 'Price'
]
data['Date'] = date
data = data[data.Type == '股票']
data.StockCode = data.StockCode.map(
CodeFormat().stock_code_add_postfix)
data["Market"] = data.StockCode.map(CodeFormat().get_stcok_market)
data['Holding'] = data['Holding'].astype(np.float)
data['Price'] = data['Price'].astype(np.float)
data['StockMarketValue'] = data['Holding'] * data['Price']
holding_data = pd.concat([holding_data, data], axis=0)
holding_data = holding_data.reset_index(drop=True)
# 开始计算每只基金的股票平均市值
for i_col in range(len(person_list)):
person_name = person_list[i_col]
fund_val = fund.loc[:, person_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
columns = ['沪市平均', '沪市最新', '沪市目标', '深市平均', '深市最新', '深市目标']
result = pd.DataFrame([], index=fund_list, columns=columns)
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
print(" Cal Fund IPO Stock MarketValue 20 days ", fund_name)
holding_data_fund = holding_data[holding_data.FundName ==
fund_name]
fund_gb = holding_data_fund.groupby(
by=['Date', 'Market'])['StockMarketValue'].sum()
fund_gb = fund_gb.unstack()
result.loc[fund_name, "沪市平均"] = fund_gb.loc[:, "SH"].mean()
result.loc[fund_name, "沪市最新"] = fund_gb.loc[fund_gb.index[-1],
"SH"]
result.loc[fund_name,
"沪市目标"] = thread_value * day_period - fund_gb.ix[
0:-1, "SH"].sum()
result.loc[fund_name, "深市平均"] = fund_gb.loc[:, "SZ"].mean()
result.loc[fund_name, "深市最新"] = fund_gb.loc[fund_gb.index[-1],
"SZ"]
result.loc[fund_name,
"深市目标"] = thread_value * day_period - fund_gb.ix[
0:-1, "SZ"].sum()
result = result[(result["沪市平均"] < thread_value) |
(result["深市平均"] < thread_value)]
result /= 10000
out_sub_path = os.path.join(self.data_path, person_name,
self.today, 'ipo_mkt_monitor')
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
out_file = os.path.join(out_sub_path, '新股市值监控.csv')
result.to_csv(out_file)
def ipo_buy_online(self, today=datetime.today().strftime("%Y%m%d")):
""" 线上打新 （股票总市值不够1000万 或者不够单只股票打新下限 或者 专户产品）"""
fund_info = MfcData().get_mfc_fund_info()
fund_info.index = fund_info.ShortName
fund_info = fund_info[['Name', 'StockGroupId', 'Id']]
fund_info = fund_info.dropna()
print("开始计算网上打新############################################")
# New FTP Folder
ftp = MyFtp()
ftp.connect()
ftp_folder = os.path.join(self.ftp_path, today)
ftp.upload_folder(ftp_folder)
ftp.close()
manager_info = MfcData().get_manager_fund()
for i_manager in range(len(manager_info.columns)):
manager_name = manager_info.columns[i_manager]
manager_fund = pd.DataFrame(manager_info[manager_name])
manager_fund = manager_fund.dropna()
manager_fund.index = manager_fund[manager_name]
# 新思路和集利债 固收负责 在刘洋产品中去掉
if manager_name == "liuyang":
manager_fund = manager_fund.drop("泰达新思路")
manager_fund = manager_fund.drop("泰达宏利集利债券")
if manager_name == "caolongjie":
manager_fund = manager_fund.drop("建行中国人寿固收组合管理计划")
manager_fund_info = fund_info[fund_info['Name'].map(
lambda x: x in manager_fund.values)]
local_path = os.path.join(self.data_path, today, "新股原始文件")
dir_list = os.listdir(local_path)
result = pd.DataFrame([])
for i_file in range(len(dir_list)):
file = dir_list[i_file]
local_file = os.path.join(local_path, file)
try:
data = pd.read_excel(local_file,
index_col=[0],
sheetname='2网上')
except Exception as e:
print(e)
data = pd.read_excel(local_file, index_col=[0])
if data.index.name == "泰达宏利基金管理有限公司":
print(file, "有网上新股")
""" 一种是只有网上发行的 一种是网下发行完成后再网上发行的"""
try:
stock_code = CodeFormat(
).change_normal_to_ipo_apply_code(data.iloc[2, 9])[0:6]
stock_price = data.iloc[2, 11]
print("之前问询过网下，现在网上新股")
""" 当日拿不出来当日申购上限数据 就用Excel中的数据 """
stock_code_normal = CodeFormat(
).change_ipo_apply_code_to_normal(data.iloc[2, 9])[0:6]
stock_code_normal = CodeFormat(
).stock_code_add_postfix(stock_code_normal)
num = w.wss(stock_code_normal, "ipo_SShares_uplimit",
"unit=1")
stock_number_up = num.Data[0][0]
if np.isnan(stock_number_up) or stock_number_up == 0:
stock_number_up = data.iloc[2, 13]
print(stock_code, stock_number_up, stock_price)
fund1 = data.iloc[4:6, :].T
fund1.index = fund1['基金名称']
fund1 = fund1.dropna()
fund1 = fund1[fund1.iloc[:, 1] == "是"]
fund2 = data.iloc[10:12, :].T
fund2.index = fund2['基金名称']
fund2 = fund2.dropna()
fund2 = fund2[fund2.iloc[:, 1] == "是"]
fund3 = data.iloc[17:19, :].T
fund3.index = fund3['基金名称']
fund3 = fund3.dropna()
fund3 = fund3[fund3.iloc[:, 1] == "是"]
except Exception as e:
print(e)
stock_code = CodeFormat(
).change_normal_to_ipo_apply_code(data.iloc[4, 0])[0:6]
stock_price = data.iloc[4, 6]
print("只有网上新股")
""" 当日拿不出来当日申购上限数据 就用Excel中的数据 """
stock_code_normal = CodeFormat(
).change_ipo_apply_code_to_normal(data.iloc[4, 0])[0:6]
stock_code_normal = CodeFormat(
).stock_code_add_postfix(stock_code_normal)
num = w.wss(stock_code_normal, "ipo_SShares_uplimit",
"unit=1")
stock_number_up = num.Data[0][0]
if np.isnan(stock_number_up) or stock_number_up == 0:
stock_number_up = data.iloc[4, 7]
print(stock_code, stock_number_up, stock_price)
fund1 = data.iloc[7:9, :].T
fund1.index = fund1['基金名称']
fund1 = fund1.dropna()
fund1 = fund1[fund1.iloc[:, 1] == "是"]
fund2 = data.iloc[13:15, :].T
fund2.index = fund2['基金名称']
fund2 = fund2.dropna()
fund2 = fund2[fund2.iloc[:, 1] == "是"]
fund3 = data.iloc[19:21, :].T
fund3.index = fund3['基金名称']
fund3 = fund3.dropna()
fund3 = fund3[fund3.iloc[:, 1] == "是"]
fund = pd.concat([fund1, fund2, fund3], axis=0)
online_fund = pd.concat([fund, manager_fund_info], axis=1)
online_fund = online_fund.dropna()
online_fund['证券代码'] = stock_code
online_fund['委托方向'] = "C"
online_fund['指令数量'] = stock_number_up
online_fund['指令价格'] = stock_price
online_fund['价格模式'] = ""
online_fund['交易市场内部编号'] = CodeFormat().get_stcok_market(
stock_code)
online_fund['交易市场内部编号'] = online_fund['交易市场内部编号'].map(
lambda x: 1 if x == 'SH' else 2)
online_fund['当前指令市值/净值（%）'] = ""
online_fund['目标市值/净值（%）'] = ""
online_fund['基金编号（序号）'] = online_fund['Id']
online_fund['基金名称'] = online_fund['Name']
online_fund["组合编号"] = online_fund['StockGroupId']
result = pd.concat([online_fund, result], axis=0)
else:
print(file, "没有网上新股")
continue
if len(result):
local_path = os.path.join(self.data_path, today, "申购单")
if not os.path.exists(local_path):
os.makedirs(local_path)
file = '网上_%s.xls' % manager_name
out_file = os.path.join(local_path, file)
col = [
'证券代码', '委托方向', '指令数量', '指令价格', '价格模式', '交易市场内部编号',
'当前指令市值/净值（%）', '目标市值/净值（%）', '基金编号（序号）', '基金名称', '组合编号'
]
result = result[col]
print("今日有网上新股%s %s %s" % (manager_name, today, len(result)))
result.to_excel(out_file, index=None)
ftp = MyFtp()
ftp.connect()
ftp_file = os.path.join(self.ftp_path, today, file)
ftp.upload_file(ftp_file, out_file)
ftp.close()
else:
print("今日无网上新股%s %s %s" % (manager_name, today, len(result)))
def ipo_buy_outline(self, today=datetime.today().strftime("%Y%m%d")):
""" 线下打新 （股票总市值大于600万， 公募）"""
print("开始计算网下打新############################################")
fund_info = MfcData().get_mfc_fund_info()
fund_info.index = fund_info.ShortName
fund_info = fund_info[['Name', 'StockGroupId', 'Id', 'FundId']]
fund_info = fund_info.dropna()
fund_info['Id'] = fund_info['Id']
fund_info['FundId'] = fund_info['FundId'].map(
CodeFormat.stock_code_add_postfix)
fund_info['FundId'] = fund_info['FundId'].map(lambda x: x[0:6])
# New FTP Folder
ftp = MyFtp()
ftp.connect()
ftp_folder = os.path.join(self.ftp_path, today)
ftp.upload_folder(ftp_folder)
ftp.close()
manager_info = MfcData().get_manager_fund()
manager_list = ['liuxin', 'liuyang']
for i_manager in range(len(manager_list)):
manager_name = manager_list[i_manager]
manager_fund = pd.DataFrame(manager_info[manager_name])
manager_fund = manager_fund.dropna()
manager_fund.index = manager_fund[manager_name]
# 新思路和集利债 固收负责 在刘洋产品中去掉
if manager_name == "liuyang":
manager_fund = manager_fund.drop("泰达新思路")
manager_fund = manager_fund.drop("泰达宏利集利债券")
if manager_name == "caolongjie":
manager_fund = manager_fund.drop("建行中国人寿固收组合管理计划")
manager_fund_info = fund_info[fund_info['Name'].map(
lambda x: x in manager_fund.values)]
local_path = os.path.join(self.data_path, today, "新股原始文件")
dir_list = os.listdir(local_path)
result = pd.DataFrame([])
for i_file in range(len(dir_list)):
file = dir_list[i_file]
local_file = os.path.join(local_path, file)
data = pd.read_excel(local_file, index_col=[0])
if data.index.name == "新股发行网下申购审批表":
stock_code = CodeFormat().change_normal_to_ipo_apply_code(
data.iloc[1, 3])[0:6]
stock_price = data.iloc[6, 1]
""" 网下问询时间和当日不一致的话 那么说明之前已经网下申购完成 当日应该是网上问询 """
ask_date = Date().change_to_str(data.iloc[3, 0])
if ask_date == today:
print("有网下新股", file)
fund = data.iloc[34:, 0:3]
fund.columns = ['Number', 'Blank', 'Vol']
fund = fund[['Number', 'Vol']]
fund = fund.dropna()
online_fund = pd.concat([fund, manager_fund_info],
axis=1)
online_fund = online_fund.dropna()
online_fund['基金代码'] = online_fund['FundId']
online_fund["组合编号"] = online_fund['StockGroupId']
online_fund['交易市场内部编号'] = CodeFormat(
).get_stcok_market(stock_code)
online_fund['交易市场内部编号'] = online_fund['交易市场内部编号'].map(
lambda x: 1 if x == 'SH' else 2)
online_fund['证券代码'] = stock_code
online_fund['申购价格1'] = stock_price
online_fund['申购数量1'] = online_fund['Number'] * 10000.0
online_fund['申购价格2'] = ""
online_fund['申购数量2'] = ""
online_fund['申购价格3'] = ""
online_fund['申购数量3'] = ""
online_fund['其他要求'] = ""
online_fund['基金名称'] = online_fund['Name']
online_fund['组合名称'] = ""
result = pd.concat([online_fund, result], axis=0)
else:
print("网下问询日期和当日不匹配，之前已经问询过", file)
continue
else:
print("没有网下", file)
continue
if len(result):
local_path = os.path.join(self.data_path, today, "申购单")
if not os.path.exists(local_path):
os.makedirs(local_path)
file = '网下_%s.xls' % manager_name
out_file = os.path.join(local_path, file)
col = [
'基金代码', '组合编号', '交易市场内部编号', '证券代码', '申购价格1', '申购数量1',
'申购价格2', '申购数量2', '申购价格3', '申购数量3', "其他要求", '基金名称', '组合名称'
]
result = result[col]
print("今日有网下新股%s %s %s" % (manager_name, today, len(result)))
result.to_excel(out_file, index=None)
ftp = MyFtp()
ftp.connect()
ftp_file = os.path.join(self.ftp_path, today, file)
ftp.upload_file(ftp_file, out_file)
ftp.close()
else:
print("今日无网下新股%s %s %s" % (manager_name, today, len(result)))
def cal_ipo_mkt_monitor(today, project_path, out_path):
# 参数
###########################################################################
thread_value = 60000000
day_period = 20
person_list = ['liuxin', 'yangchao', 'liuyang']
before_trade_data = Date().get_trade_date_offset(today, -1)
trade_series = Date().get_trade_date_series(end_date=before_trade_data)
trade_series = pd.DataFrame(trade_series, index=trade_series)
trade_series = list(trade_series.index[-day_period:])
# 基金经理管理的基金
###########################################################################
fund = pd.read_excel(project_path + 'Manage_Fund_Name.xlsx',
encoding='gbk')
# 所需要的持仓文件
#######################################################################################################
holding_data = pd.DataFrame([])
for i_date in range(len(trade_series)):
date = trade_series[i_date]
data = MfcData().get_group_security(date)
data = data.dropna(subset=['基金名称'])
data = data[['基金名称', '证券代码', '持仓', '证券类别', '最新价']]
data.columns = ['FundName', 'StockCode', 'Holding', 'Type', 'Price']
data['Date'] = date
data = data[data.Type == '股票']
data.StockCode = data.StockCode.map(stock_code_add_postfix)
data["Market"] = data.StockCode.map(get_stcok_market)
data['Holding'] = data['Holding'].astype(np.float)
data['Price'] = data['Price'].astype(np.float)
data['StockMarketValue'] = data['Holding'] * data['Price']
holding_data = pd.concat([holding_data, data], axis=0)
holding_data = holding_data.reset_index(drop=True)
# 开始计算每只基金的股票平均市值
#######################################################################################################
for i_col in range(len(person_list)):
person_name = person_list[i_col]
fund_val = fund.ix[:, person_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
columns = ['沪市平均', '沪市最新', '沪市目标', '深市平均', '深市最新', '深市目标']
result = pd.DataFrame([], index=fund_list, columns=columns)
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
print(" Cal Fund IPO Stock MarketValue 20 days ", fund_name)
holding_data_fund = holding_data[holding_data.FundName ==
fund_name]
fund_gb = holding_data_fund.groupby(
by=['Date', 'Market'])['StockMarketValue'].sum()
fund_gb = fund_gb.unstack()
result.ix[fund_name, "沪市平均"] = fund_gb.ix[:, "SH"].mean()
result.ix[fund_name, "沪市最新"] = fund_gb.ix[-1, "SH"]
result.ix[fund_name,
"沪市目标"] = thread_value * day_period - fund_gb.ix[
0:-1, "SH"].sum()
result.ix[fund_name, "深市平均"] = fund_gb.ix[:, "SZ"].mean()
result.ix[fund_name, "深市最新"] = fund_gb.ix[-1, "SZ"]
result.ix[fund_name,
"深市目标"] = thread_value * day_period - fund_gb.ix[
0:-1, "SZ"].sum()
result = result[(result["沪市平均"] < thread_value) |
(result["深市平均"] < thread_value)]
result /= 10000
out_sub_path = os.path.join(out_path, person_name, today,
'ipo_mkt_monitor')
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
out_file = os.path.join(out_sub_path, '新股市值监控.csv')
result.to_csv(out_file)
def ipo_sell(self,
today=datetime.today().strftime("%Y%m%d")):
""" 计算所有基金新股是否打开 """
before_trade_data = Date().get_trade_date_offset(today, -1)
# get holding data
data = MfcData().get_group_security(before_trade_data)
data = data.dropna(subset=['基金名称'])
data = data[['基金名称', '基金编号', '组合名称', '组合编号', '证券代码', '持仓', '证券类别']]
data.columns = [['基金名称', '基金编号（序号）', '组合名称', '组合编号', '证券代码', '指令数量', '证券类别']]
data = data[data['证券类别'] == '股票']
data['证券代码'] = data['证券代码'].map(CodeFormat().stock_code_add_postfix)
data["交易市场内部编号"] = data['证券代码'].map(CodeFormat().get_stcok_market)
fund_info = MfcData().get_mfc_fund_info()
fund_info = fund_info.dropna(subset=['FundId'])
fund_info['FundId'] = fund_info['FundId'].map(CodeFormat.stock_code_add_postfix)
fund_info['FundId'] = fund_info['FundId'].map(lambda x: x[0:6])
# get ipo data
Stock().load_all_stock_code_now()
Stock().load_ipo_date()
stock = Stock().get_ipo_date()
stock.columns = ['IpoDate', 'DelistDate']
stock['证券代码'] = stock.index
stock['IpoDate'] = stock['IpoDate'].map(lambda x: str(int(x)))
# get New Stock
new_stock_date = datetime.today() - timedelta(days=self.new_days)
new_stock_date = new_stock_date.strftime("%Y%m%d")
all_data = pd.merge(data, stock, on=['证券代码'], how="left")
all_data = all_data[all_data.IpoDate > new_stock_date]
# get Vol and Pct of New Stock
code_list = list(set(all_data['证券代码'].values))
code_str = ','.join(code_list)
pct = w.wsq(code_str, "rt_pct_chg,rt_vol")
pct = pd.DataFrame(pct.Data, columns=pct.Codes, index=['Pct', 'Vol']).T
pct['证券代码'] = pct.index
# filter multi_factor
new_data = pd.merge(all_data, pct, on=['证券代码'], how="left")
new_data = new_data[new_data['Vol'] > 0]
new_data = new_data[new_data['Pct'] < 0.09]
# New Local Folder
out_sub_path = os.path.join(self.data_path, today)
if not os.path.exists(out_sub_path):
os.mkdir(out_sub_path)
# New FTP Folder
ftp = MyFtp()
ftp.connect()
ftp_folder = os.path.join(self.ftp_path, today)
ftp.upload_folder(ftp_folder)
ftp.close()
# manager data
manager_data = MfcData().get_manager_fund()
for i_manager in range(len(manager_data.columns)):
manager_name = manager_data.columns[i_manager]
manager_fund = manager_data[manager_name]
manager_fund = manager_fund.dropna()
fund_data = new_data[new_data['基金名称'].map(lambda x: x in manager_fund.values)]
if len(fund_data) > 0:
warnings.filterwarnings("ignore")
fund_data_out = fund_data[['证券代码', '指令数量', '交易市场内部编号',
'基金编号（序号）', '基金名称', '组合编号']]
fund_data_out['委托方向'] = 2
fund_data_out['指令价格'] = 0
fund_data_out['交易市场内部编号'] = fund_data_out['交易市场内部编号'].map(lambda x: 1 if x == 'SH' else 2)
fund_data_out['价格模式'] = ""
fund_data_out['当前指令市值/净值（%）'] = ""
fund_data_out['目标市值/净值（%）'] = ""
fund_data_out['基金名称'] = ""
fund_data_out['证券代码'] = fund_data_out['证券代码'].map(lambda x: x[0:6])
fund_data_out = fund_data_out[['证券代码', '委托方向', '指令数量', '指令价格',
'价格模式', '交易市场内部编号', '当前指令市值/净值（%）', '目标市值/净值（%）',
'基金编号（序号）', '基金名称', '组合编号']]
file = manager_name + '.xls'
out_file = os.path.join(out_sub_path, file)
print(out_file)
fund_data_out.to_excel(out_file, index=None)
ftp = MyFtp()
ftp.connect()
ftp_file = os.path.join(self.ftp_path, today, file)
ftp.upload_file(ftp_file, out_file)
ftp.close()
def cal_reverse_5days(today, project_path, out_path):
# 参数
###########################################################################
person_list = ['liuxin', 'yangchao', 'caolongjie', 'liuyang']
day_period = 5
# 日期参数
###########################################################################
before_trade_data = Date().get_trade_date_offset(today, -1)
trade_series = Date().get_trade_date_series(end_date=before_trade_data)
trade_series = pd.DataFrame(trade_series, index=trade_series)
trade_series = list(trade_series.index[-day_period:])
# 基金经理管理的基金
###########################################################################
fund = pd.read_excel(project_path + 'Manage_Fund_Name.xlsx',
encoding='gbk')
result = pd.DataFrame([])
# 开始计算每只基金的5日五日反向
#######################################################################################################
for i_col in range(len(person_list)):
person_name = person_list[i_col]
fund_val = fund.ix[:, person_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
print(" Cal Fund Reverse 5 days ", fund_name)
for i_date in range(len(trade_series)):
cur_date = trade_series[i_date]
data = MfcData().get_trade_statement(cur_date)
data = data.dropna(subset=['基金名称'])
data = data[['基金名称', '证券代码', '委托方向', '成交数量', '资产类别']]
data.columns = [
'FundName', 'StockCode', 'Direction', 'TradeNumber', 'Type'
]
data = data[data.FundName == fund_name]
data = data[data.Type == '股票资产']
data.StockCode = data.StockCode.map(stock_code_add_postfix)
data.Direction = data.Direction.map(lambda x: 2
if x == '卖出' else 1)
result_date = pd.DataFrame(data.Direction.values,
index=data.StockCode.values,
columns=[cur_date])
if i_date == 0:
result = result_date
else:
try:
result = pd.concat([result, result_date], axis=1)
except:
pass
result = result.fillna(0)
result.index.name = 'CODE'
result = result.astype(np.int)
my_result = []
for i_row in range(len(result)):
vals_list = list(result.ix[i_row, trade_series])
vals_set = list(set(result.ix[i_row, trade_series]))
if 2 in vals_set:
append_row = [result.index[i_row], 2]
append_row.extend(vals_list)
my_result.append(append_row)
if 1 in vals_set:
append_row = [result.index[i_row], 1]
append_row.extend(vals_list)
my_result.append(append_row)
col = ['CODE', 'FLAG']
col.extend(trade_series)
my_result = pd.DataFrame(my_result, columns=col)
out_sub_path = os.path.join(out_path, person_name, today,
'reverse_5days')
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
out_file = os.path.join(out_sub_path, fund_name + '.csv')
my_result.to_csv(out_file, index=None)
print(fund_name, len(result), len(my_result))