def cal_factor_exposure(self, beg_date, end_date):
""" 计算因子暴露 """
new_stock_days = 60
data = Stock().get_ipo_date()
data.columns = ['ipo', 'delist']
data = data.astype(str)
date_series = Date().get_trade_date_series(beg_date, end_date)
res = pd.DataFrame()
for i_date in range(len(date_series)):
date = date_series[i_date]
print('Calculating Barra Risk factor %s at date %s' %
(self.raw_factor_name, date))
new_stock_date = Date().get_trade_date_offset(
date, -new_stock_days)
data_date = data[(data['ipo'] < new_stock_date)
& (data['delist'] > date)]
data_date['GEM'] = data_date.index.map(CodeFormat().get_gem_stock)
res_date = pd.DataFrame(data_date['GEM'])
res_date.columns = [date]
res = pd.concat([res, res_date], axis=1)
self.save_risk_factor_exposure(res, self.raw_factor_name)
def get_input_data(self, fund_code, fund_name, inside_fund_name,
fund_strategy, asset_allocation_strategy,
comparsion_bench_list, date, fund_manager):
"""
输入基金名称 基金代码 当日日期
得到最近的交易日、季报日、半年报年报日
得到基金净值数据、季报和半年报持仓数据
"""
self.fund_code = fund_code
self.fund_name = fund_name
self.date = date
self.fund_strategy = fund_strategy
self.inside_fund_name = inside_fund_name
self.asset_allocation_strategy = asset_allocation_strategy
self.last_trade_date = Date().get_trade_date_offset(date, 0)
print("参数时间", self.last_trade_date)
if fund_code == fund_name:
self.fund_nav = MfcData().get_mfc_private_fund_nav(fund_name)
self.fund_nav = pd.DataFrame(self.fund_nav['累计复权净值'])
self.fund_nav.columns = ['NAV_ADJ']
else:
self.fund_nav = MfcData().get_mfc_public_fund_nav(self.fund_code)
fund_hold_stock = MfcData().get_fund_security(self.last_trade_date)
fund_hold_stock = fund_hold_stock[fund_hold_stock['基金名称'] ==
self.inside_fund_name]
fund_hold_stock = pd.DataFrame(fund_hold_stock['市值比净值(%)'].values,
index=fund_hold_stock['证券代码'],
columns=['Weight'])
fund_hold_stock.index = fund_hold_stock.index.map(
CodeFormat().stock_code_add_postfix)
fund_hold_stock['Weight'] /= 100.0
self.fund_hold_stock = fund_hold_stock
self.comparsion_bench_list = comparsion_bench_list
self.fund_manager = fund_manager
def get_stock_trading(self, date):
""" 读取股票交易数据 """
try:
trade = MfcData().get_trade_statement(date)
trade_data = trade[trade['基金名称'] == self.fund_name]
trade_data = trade_data[trade_data['资产类别'] == '股票资产']
trade_data = trade_data[[
'基金名称', '证券名称', '成交数量', '市场成交均价', '委托方向', '证券代码'
]]
trade_data['证券代码'] = trade_data['证券代码'].map(
CodeFormat().stock_code_add_postfix)
trade_data.index = trade_data['证券代码'].values
trade_data = trade_data[['成交数量', '市场成交均价', '委托方向']]
trade_data.columns = ['TradeVol', 'TradePrice', 'SellOrBuy']
trade_data = trade_data[~trade_data.index.duplicated()]
sell_code_list = list(
trade_data[trade_data['SellOrBuy'] == "卖出"].index)
trade_data.loc[sell_code_list,
'TradeVol'] = -trade_data.loc[sell_code_list,
'TradeVol']
return trade_data
except Exception as e:
print(e)
return None
def cal_mfc_holding_alpha_exposure_date(fund_name, factor_name_list, date):
""" 计算泰达持仓对alpha因子的暴露（满仓）"""
from quant.project.multi_factor.alpha_model.exposure.alpha_factor import AlphaFactor
date = Date().get_trade_date_offset(date, 0)
try:
holding_data = MfcGetData().get_fund_security(date)
holding_data = holding_data[["基金名称", "证券代码", "市值", '证券类别']]
holding_data = holding_data[holding_data["基金名称"] == fund_name]
holding_data = holding_data[holding_data['证券类别'] == "股票"]
holding_data.columns = ["FundName", "StockCode", "Weight", 'Type']
holding_data['Weight'] = holding_data['Weight'] / holding_data[
'Weight'].sum()
holding_data.StockCode = holding_data.StockCode.map(
CodeFormat().stock_code_add_postfix)
holding_data.index = holding_data.StockCode
weight = holding_data
exposure = pd.DataFrame()
for factor_name in factor_name_list:
alpha = AlphaFactor().get_alpha_factor_exposure(factor_name)
exposure_add = pd.DataFrame(alpha[date])
exposure_add.columns = [factor_name]
exposure = pd.concat([exposure, exposure_add], axis=1)
data = pd.concat([weight, exposure], axis=1)
data = data.sort_values(by=['Weight'], ascending=True)
data = data.dropna(subset=["Weight"])
res = pd.DataFrame([], columns=factor_name_list, index=[date])
if data['Weight'].sum() > 0.0:
for i_col in range(len(exposure.columns)):
factor_name = exposure.columns[i_col]
data_factor = data[["Weight", factor_name]]
data_factor = data_factor.dropna()
exposure_sum = (data_factor["Weight"] *
data_factor[factor_name]).sum()
res.loc[date,
factor_name] = exposure_sum / data['Weight'].sum()
print(" Calculate Mfcteda Fund %s Alpha Exposure at %s" %
(fund_name, date))
else:
print(
" Calculate Mfcteda Fund %s At %s of Weight Stock is Zero"
% (fund_name, date))
return res
except Exception as e:
print(" Calculate Mfcteda Fund %s Alpha Exposure at %s is Null " %
(fund_name, date))
res = pd.DataFrame([], columns=factor_name_list, index=[date])
return res
def get_fund_stock_weight(self, fund_name, date):
""" 某天某只基金的股票持仓 """
data = self.get_fund_security(date)
data = data[data['基金名称'] == fund_name]
data.index = data['证券代码'].map(CodeFormat().stock_code_add_postfix)
weight = pd.DataFrame(data['市值比净值(%)'])
weight.columns = ['Weight']
weight = weight.sort_values(by=['Weight'], ascending=False)
weight.index.name = "Code"
print(len(weight))
return weight
def cal_mfc_holding_barra_exposure_date(fund_name, date):
""" 计算某只基金在某天的暴露 """
date = Date().get_trade_date_offset(date, 0)
type_list = ["STYLE", "COUNTRY", "INDUSTRY"]
try:
holding_data = MfcGetData().get_fund_security(date)
holding_data = holding_data[["基金名称", "证券代码", "市值", '证券类别']]
holding_data = holding_data[holding_data["基金名称"] == fund_name]
holding_data = holding_data[holding_data['证券类别'] == "股票"]
holding_data.columns = ["FundName", "StockCode", "Weight", 'Type']
exposure = Barra().get_factor_exposure_date(date,
type_list=type_list)
holding_data['Weight'] = holding_data['Weight'] / holding_data[
'Weight'].sum()
holding_data.StockCode = holding_data.StockCode.map(
CodeFormat().stock_code_add_postfix)
holding_data.index = holding_data.StockCode
weight = holding_data
data = pd.concat([weight, exposure], axis=1)
data = data.sort_values(by=['Weight'], ascending=True)
data = data.dropna(subset=["Weight"])
res = pd.DataFrame([], columns=exposure.columns, index=[date])
if data['Weight'].sum() > 0.0:
for i_col in range(len(exposure.columns)):
risk_factor_name = exposure.columns[i_col]
exposure_sum = (data["Weight"] *
data[risk_factor_name]).sum()
res.ix[date, risk_factor_name] = exposure_sum / data[
'Weight'].sum()
print(" Calculate Mfcteda Fund %s Barra Exposure at %s" %
(fund_name, date))
else:
print(
" Calculate Mfcteda Fund %s At %s of Weight Stock is Zero"
% (fund_name, date))
return res
except Exception as e:
print(" Calculate Mfcteda Fund %s Barra Exposure at %s is Null " %
(fund_name, date))
name = Barra().get_factor_name(type_list=type_list)
res = pd.DataFrame([],
columns=list(name.NAME_EN.values),
index=[date])
return res
def get_stock_holding(self, date):
""" 读取股票持仓数据"""
try:
security = MfcData().get_fund_security(date)
security_data = security[security['基金名称'] == self.fund_name]
security_data = security_data[security_data['证券类别'] == '股票']
security_data = security_data[['基金名称', '证券名称', '持仓', '证券代码']]
security_data['证券代码'] = security_data['证券代码'].map(
CodeFormat().stock_code_add_postfix)
security_data.index = security_data['证券代码'].values
security_data = security_data[['证券名称', '持仓']]
return security_data
except Exception as e:
print(e)
return None
def get_history_wind_portfolio():
project_path = r'E:\Data\portfolio\other_portfolio\exceed_expectation'
wind_port_path = WindPortUpLoad().path
name = "超预期30"
stock_ratio = 0.95
file = os.path.join(project_path, "超预期30组合历史持仓.xlsx")
data = pd.read_excel(file)
data.columns = ["Date", "Code", "Weight", "Industry"]
data["Code"] = data["Code"].map(CodeFormat().stock_code_add_postfix)
data["Date"] = data["Date"].map(Date().change_to_str)
data["Price"] = 0.0
data["Direction"] = "Long"
data["CreditTrading"] = "No"
date_list = list(set(data["Date"]))
date_list.sort()
out_sub_path = os.path.join(wind_port_path, name)
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
for i_date in range(len(date_list)):
date = date_list[i_date]
data_date = data[data["Date"] == date]
data_date.index = data_date['Code']
del data_date['Code']
data_date['Weight'] *= stock_ratio
data_date.loc['Cash', 'Weight'] = 1 - stock_ratio
data_date["Price"] = 0.0
data_date["Direction"] = "Long"
data_date["CreditTrading"] = "No"
data_date['Date'] = date
out_file = os.path.join(out_sub_path, name + "_" + date + '.csv')
data_date.to_csv(out_file)
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 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()