fund_xls.columns = ['Port_code','Price_date','Sec_type','Sec_name','Sec_ISIN','Sec_code','Close_price','Quantity','Market_price']
fund_xls['Close_price']=pd.to_numeric(fund_xls.Close_price.values, errors='coerce')
fund_xls['Quantity']=pd.to_numeric(fund_xls.Quantity.values, errors='coerce')
fund_xls['Market_price']=pd.to_numeric(fund_xls.Market_price.values, errors='coerce')
fund_obj = fund_xls.select_dtypes(['object'])
fund_xls[fund_obj.columns] = fund_obj.apply(lambda x: x.str.strip())
df=fund_xls.copy()
df=df[(df.Port_code.isin(dic_om_index.keys()))]
df.loc[:,'Benchmark_code']=df.Port_code.map(lambda x:dic_om_index[x][1])
df['Trade_date']=startDate
df['AssetType1']=df.apply(lambda r: (assetClass(r.Sec_type,r.Sec_code, r.Sec_name,cash_flows_eff)).split(",")[0],axis=1)
df['AssetType2']=df.apply(lambda r: (assetClass(r.Sec_type,r.Sec_code, r.Sec_name,cash_flows_eff)).split(",")[1],axis=1)
df['AssetType3']=df.apply(lambda r: (assetClass(r.Sec_type,r.Sec_code, r.Sec_name,cash_flows_eff)).split(",")[2],axis=1)
df['AssetType4']=df.apply(lambda r: (assetClass(r.Sec_type,r.Sec_code, r.Sec_name,cash_flows_eff)).split(",")[3],axis=1)
df['AssetType5']=df.apply(lambda r: (assetClass(r.Sec_type,r.Sec_code, r.Sec_name,cash_flows_eff)).split(",")[4],axis=1)
df['MarketValue']= np.where(df[['AssetType1']].isin(['B. Futures Exposure','Dividend Exposure']),0, df[['Market_price']])
df['EffExposure']= df[['Market_price']]
df['Close_price']=np.where((df['AssetType2'].isin(['Index Future']))&(df['Quantity'].values!=0),
(df['Market_price'].values/df['Quantity'].values)/10,
df['Close_price'].values)
# Futures insert
if ~fut_flow.empty:
fut_flow = pd.merge(fut_flow[['Port_code','Sec_code']], (df[['Trade_date','AssetType1','AssetType5','AssetType3','Sec_code','Close_price']]).drop_duplicates(['Sec_code']), on=['Sec_code'], how='left').fillna(0)
fut_flow['Quantity']=0
fut_flow['MarketValue']=0
def fut_calc_func(response, orders=False):
#import future
import sys
#sys.path.append('C:\Program Files (x86)\WinPython\python-3.6.5.amd64\lib\site-packages\IPython\extensions')
#sys.path.append('C:\Program Files (x86)\WinPython\settings\.ipython')
#for p in sys.path:
# print(p)
import numpy as np
import pandas as pd
import datetime as dt
from datetime import datetime, timedelta
import glob
import os
#from pydatastream import Datastream
#from business_calendar import Calendar, MO, TU, WE, TH, FR
import pyodbc
from write_excel import excel_fx as exl_rep
from write_excel import input_fx as inp
from write_excel import select_fund as sf
from write_excel import cash_flow_validity_fx as cfvf
from write_excel import assetClassF as assetClass ######
from write_excel import res_indF as res_ind
from write_excel import fx_dtaF as fx_dta
from write_excel import chck_fut as chck_fut
import tkinter
np.seterr(divide='ignore', invalid='ignore')
#DWE = Datastream(username="******", password="******",proxy='172.23.18.187:3128')
#DWE.system_info()
#DWE.sources()
#data_xls = pd.read_excel('\\\\za.investment.int\\dfs\\dbshared\\DFM\\TRADES\\Decalog Valuation\\UFMPosCash20170811.xls', 'Position', index_col=None)
#type(data_xls)
#data_xls.tail()
"""
Set parameters for trading
"""
# Benchmark list
# Check before you run file
# run_prog=inp()
if response == 'yes':
#tkinter.Label(window, text = "Futures report generation in progress!").pack()
#startDate = datetime.today().date()
startDate = datetime.today()
#startDate = datetime.strptime('Sep 15 2017', '%b %d %Y').date()
pd.options.display.max_rows = 200
#testing=True
# Benchmark settings
folder_yr = datetime.strftime(startDate, "%Y")
folder_mth = datetime.strftime(startDate, "%m")
folder_day = datetime.strftime(startDate, "%d")
# Fund settings
#dirtoimport_file='\\\\za.investment.int\\dfs\\dbshared\\DFM\\TRADES\\Decalog Valuation\\'
#dirtoimport_file= 'H:\\Bernisha\\Work\\IndexTrader\\Data\\required_inputs\\'
dirtoimport_file = '\\\\za.investment.int\\DFS\\SSDecalogUmbono\\IndexationPosFile\\'
dirtoimport_cashfile = '\\\\za.investment.int\\dfs\\dbshared\\DFM\\TRADES\\Decalog Valuation\\'
# directory to export report to
#dirtooutput_file='\\\\za.investment.int\\dfs\\dbshared\\DFM\\TRADES\\Futures Trades'
dirtooutput_file = '\\\\za.investment.int\\dfs\\dbshared\\DFM\\TRADES'
#output_folder='\\'.join([dirtooutput_file ,folder_yr, folder_mth])
output_folder = str(
'\\'.join([dirtooutput_file, folder_yr, folder_mth, folder_day]) +
'\\Futures Trades')
if not os.path.exists(output_folder):
os.makedirs(output_folder)
# Map fund and benchmark settings
# dic_om_index = {'DRIEQC':['OM Responsible Equity Fund','CSIESG'],
# 'DSWIXC':['SWIX Index Fund','JSESWIXALSICAPPED'],
# 'CORPEQ':['OMLAC Shareholder Protected Equity Portfolio','JSESWIXALSI'],
# 'MFEQTY':['M&F Protected Equity Portfolio','JSESWIXALSI'],
# 'DSALPC':['SA Listed Property Index Fund','JSESAPY'],
# 'USWIMF':['Momentum SWIX Index Fund','JSESWIXALSI'],
# 'OMRTMF':['RAFI40 Unit Trust','JSERAFI40'],
# 'LEUUSW':['Life Equity UPF','JSESWIXALSICAPPED'],
# 'LEIUSW':['Life Equity IPF','JSESWIXALSICAPPED'],
# 'SASEMF':['SASRIA','JSESWIXALSI'],
# 'BIDLMF':['Bidvest Life CAPI','JSECAPIALSI'],
# 'BIIDMF':['Bidvest Insurance CAPI','JSECAPIALSI'],
# 'ALSCPF':['Assupol CPF','JSESWIXALSI'],
# 'ALSIPF':['Assupol IPF','JSESWIXALSI'],
# 'ALSUPF':['Assupol UPF','JSESWIXALSI'],
# 'UMSMMF':['Samancor Group Provident Fund','JSESWIXALSI'],
# 'OMSI01':['OM CAPPED SWIX FUND','JSESWIXALSICAPPED'],
# 'UMSWMF':['Momentum SWIX 40 Index Fund','JSESWIX40'],
# # 'UMC1MF':['Anglo Corp CW','JSESWIXALSI'],
# 'OMALMF':['Top40 Unit Trust','JSETOP40'],
# 'DALSIC':['All Share Index Fund','JSEALSI'],
# }
# Pull in fund dictionary
fnd_dict = pd.read_csv(
'C:\\IndexTrader\\required_inputs\\fund_dictionary.csv')
dic_om_index = fnd_dict.set_index(['FundCode']).T.to_dict('list')
#dic_users={'blala':['BLL','blala'], 'test':['TST','test'], 'sbisho':['SB','sbisho']}
#dic_users={'blala':['BLL','blala'], 'test':['TST','test'], 'sbisho':['SB','sbisho'], 'tmfelang2':['TM','tmfelang'], 'abalfour':['AB','abalfour'], 'sparker2':['SP','sparker'], 'fsibiya':['FS','fsibiya']}
# Pull in user infomration
user_dict = pd.read_csv(
'C:\\IndexTrader\\required_inputs\\user_dictionary.csv')
dic_users = user_dict.set_index(['username']).T.to_dict('list')
#dic_om_index={
# 'DSALPC':['SA Listed Property Index Fund','JSESAPY',1,5,8,0.0005,0.0022,1,'Option 1 Gross Rate in cents per share'],
# 'CORPEQ':['OMLAC Shareholder Protected Equity Portfolio','JSESWIXALSI',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
# 'DALSIC':['All Share Index Fund','JSEALSI',1,5,8,0.0002,0.0013,3,'Option 1 Gross Rate in cents per share'],
# 'DSWIXC':['SWIX Index Fund','JSESWIXALSICAPPED',1,5,8,0.0002,0.0013,4,'Option 1 Gross Rate in cents per share'],
# 'MFEQTY':['M&F Protected Equity Portfolio','JSESWIXALSI',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
# 'USWIMF':['Momentum SWIX Fund','JSESWIXALSI',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
# 'BIIDMF':['Bidvest Insurance Fund','JSECAPIALSI',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
# 'LEUUSW':['Bidvest Insurance Fund','JSESWIXALSICAPPED',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
## 'OMCC01':['OM Core Conservative','JSESWIXALSICAPPED',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
# }
override = ['SSF Div']
# Public Holidays
pub_holidays = (pd.read_excel(
"C:\\IndexTrader\\required_inputs\\public_holidays.xlsx")
)['pub_holidays'].tolist()
#cal = Calendar(holidays=pub_holidays)
# Determine list of funds to trade
lst_fund = sf()
# Import cash limits
cash_lmt_x = pd.read_csv(
'C:\\IndexTrader\\required_inputs\\cash_limits.csv')
cash_lmt_x = cash_lmt_x[cash_lmt_x.P_Code.isin(lst_fund)]
cash_lmt_dict = cash_lmt_x.set_index(
['P_Code'])[['Min_EffCash', 'Max_EffCash']].T.to_dict()
# Import Flows
#cash_flows_eff = pd.read_csv('H:\\Bernisha\\Work\\IndexTrader\\Data\\required_inputs\\flows.csv')
cash_flows_eff = pd.read_csv(
'C:\\IndexTrader\\required_inputs\\flows.csv', thousands=',')
cash_flows_eff = (
cash_flows_eff[cash_flows_eff.Port_code.isin(lst_fund)]).drop(
'Trade', 1)
# Import futures
fut_flow = pd.merge(cash_lmt_x[['P_Code', 'Future_Code']],
cash_flows_eff[['Port_code', 'fut_sufx']],
how='right',
left_on=['P_Code'],
right_on=['Port_code'])
fut_flow['Sec_code'] = fut_flow[['Future_Code', 'fut_sufx'
]].apply(lambda x: ''.join(x), axis=1)
fut_flow['Sec_code'] = np.where(fut_flow.Future_Code == 'NoFuture',
'NoFuture', fut_flow.Sec_code.values)
# Map Sec type to more descriptive asset classes
# def assetClass(Sec_type, ins_code,sec_nam):
#
# #ssf=['OMLS'+str((cash_flows_eff['fut_sufx'].values)[0]), 'OMAS'+str((cash_flows_eff['fut_sufx'].values)[0])]
# ssf=['S']
# #excp=['OMLF'+str((cash_flows_eff['fut_sufx'].values)[0]),'OMAF'+str((cash_flows_eff['fut_sufx'].values)[0])]
# excp=['F']
# ind_fut=[str((cash_flows_eff['fut_sufx'].values)[0])] # index future suffix
#
# if Sec_type == 'CASH : CALL ACC':
# return "Total cash,Settled cash,Cash on call,Total cash"
# elif Sec_type=='CASH : SAFEX AC':
# return "Total cash,Settled cash,Futures margin,Total cash"
# elif Sec_type == "CURRENCY" and sec_nam=='VAL':
# return "Total cash,Settled cash,Val cash,Total cash"
# elif Sec_type=="PAYABLE" and sec_nam=='DIF':
# return "Total cash,Unsettled cash,Dif cash,Total cash"
# elif Sec_type=='FUTRE STCK INDX':
# return str("Futures Exposure,"+"Index Future,"+str(ins_code[0:4]+ind_fut[0])+",Futures Exposure")
# # elif Sec_type=='FUTURE : EQUITY' and ins_code in(ssf) :
# elif Sec_type=='FUTURE : EQUITY' and ins_code[3:4] in(ssf):
# # return str("Futures Exposure,"+"SSF,"+str(ssf[0]))
# return str("Futures Exposure,"+"SSF,null"+",Futures Exposure")
# elif Sec_type=='EQ : ORDINARY':
# return "Equity Exposure,Equity,null,Equity Exposure"
# elif Sec_type=='EQ : RIGHTS':
# return "Equity Exposure,Equity Rights,null,Equity Exposure"
# elif Sec_type=='EQ : FOREIGN':
# return "Equity Exposure,Equity Foreign,null,Equity Exposure"
# elif ins_code[3:4] in(excp):
# # return str("Dividend Exposure,"+"SSF Div,"+str(excp[0]))
# return str("Dividend Exposure,"+"SSF Div,null,Dividend Exposure")
# elif Sec_type=="FUND : LOC EQ":
# return str("Equity Exposure,"+"Equity Fund,"+str(ins_code)+",Equity Exposure")
# else:
# return "Other,null,null,Other"
#
#
# def res_ind(dat,des,ind=['Trade_date','Port_code','AssetType1','AssetType2','AssetType3','AssetType4','Quantity','EffExposure','MarketValue','FundValue','Close_price']):
# dat=dat.reset_index()
# dat['AssetType1']=des
# dat['AssetType2']='null'
# dat['AssetType3']='null'
# dat['AssetType4']='null'
# dat=dat[ind]
# return dat
"""
Fund, Benchmark, Corporate Action data import
"""
#newest = max(glob.iglob(dirtoimport_file+'fund_data/*.xls'), key=os.path.getmtime)
newest = max(glob.iglob(dirtoimport_file + '*.xls'),
key=os.path.getmtime)
#newest_cash=max(glob.iglob(dirtoimport_cashfile+'*.xls'), key=os.path.getmtime)
newest_cash = max(glob.iglob(dirtoimport_file + '*.xls'),
key=os.path.getmtime)
#str(dirtoimport_file+newest)
#newest
#fund_xls = pd.read_excel(newest,converters={'Portfolio code':str, 'Price date': pd.to_datetime,
#'Security type (name)':str,
#'Security name':str,
#'Security ISIN code':str,
#'Security acronym':str,
#'Close price':float,
#'Quantity held':float,
#'Market price value':float},
#)
fund_xls = pd.read_excel(
newest,
sheet_name=0,
converters={
'Portfolio': str,
'Price Date': pd.to_datetime,
'Inst Type': str,
'Inst Name': str,
'ISIN': str,
'Instrument': str,
'Quote Close': float,
'Qty': float,
'Market Val': float,
'Delta': float,
'Origin': str
},
)
fund_xls = fund_xls.drop(['Delta'], axis=1)
if orders:
pass
else:
fund_xls = fund_xls[fund_xls.Origin == 'POSITION']
fund_xls = fund_xls.drop(['Origin'], axis=1)
#fund_xls.dtypes
fund_xls.columns = [
'Port_code', 'Price_date', 'Sec_type', 'Sec_name', 'Sec_ISIN',
'Sec_code', 'Close_price', 'Quantity', 'Market_price'
]
fund_xls['Close_price'] = pd.to_numeric(fund_xls.Close_price.values,
errors='coerce')
fund_xls['Quantity'] = pd.to_numeric(fund_xls.Quantity.values,
errors='coerce')
fund_xls['Market_price'] = pd.to_numeric(fund_xls.Market_price.values,
errors='coerce')
fund_obj = fund_xls.select_dtypes(['object'])
fund_xls[fund_obj.columns] = fund_obj.apply(lambda x: x.str.strip())
df = fund_xls.copy()
df = df[(df.Port_code.isin(dic_om_index.keys()))]
df.loc[:, 'Benchmark_code'] = df.Port_code.map(
lambda x: dic_om_index[x][1])
df.loc[:, 'TypeFund'] = df.Port_code.map(lambda x: dic_om_index[x][2])
df['Trade_date'] = startDate
df['AssetType1'] = df.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.
TypeFund, cash_flows_eff)).split(",")[0],
axis=1)
df['AssetType2'] = df.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.
TypeFund, cash_flows_eff)).split(",")[1],
axis=1)
df['AssetType3'] = df.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.
TypeFund, cash_flows_eff)).split(",")[2],
axis=1)
df['AssetType4'] = df.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.
TypeFund, cash_flows_eff)).split(",")[3],
axis=1)
df['MarketValue'] = np.where(
df[['AssetType1']].isin(['Futures Exposure', 'Dividend Exposure']),
0, df[['Market_price']])
df['EffExposure'] = df[['Market_price']]
df['Close_price'] = np.where(
(df['AssetType2'].isin(['Index Future'])) &
(df['Quantity'].values != 0),
(df['Market_price'].values / df['Quantity'].values) / 10,
df['Close_price'].values)
# Futures insert
if ~fut_flow.empty:
#ind_fut=[str((cash_flows_eff['fut_sufx'].values)[0])]
fut_flow = pd.merge(fut_flow[['Port_code', 'Sec_code']],
(df[[
'Trade_date', 'AssetType1', 'AssetType2',
'AssetType3', 'Sec_code', 'Close_price'
]]).drop_duplicates(['Sec_code']),
on=['Sec_code'],
how='left').fillna(0)
fut_flow['Quantity'] = 0
fut_flow['MarketValue'] = 0
fut_flow['EffExposure'] = 0
fut_flow['Trade_date'] = startDate
fut_flow['AssetType1'] = 'Futures Exposure'
fut_flow['AssetType2'] = 'Index Future'
fut_flow['AssetType3'] = fut_flow.Sec_code
fut_flow = (fut_flow[[
'Trade_date', 'Port_code', 'AssetType1', 'AssetType2',
'AssetType3', 'Sec_code', 'Close_price', 'Quantity',
'MarketValue', 'EffExposure'
]]).copy()
fut_flow = fut_flow[~(fut_flow.Sec_code == 'NoFuture')]
df = df[df.Port_code.isin(lst_fund)]
dfprt = (df[[
'Trade_date', 'Port_code', 'AssetType1', 'AssetType2',
'AssetType3', 'Sec_code', 'Close_price', 'Quantity', 'MarketValue',
'EffExposure'
]]).copy()
# Remove cash for multi-asset class
dfprt['MarketValue'] = np.where(
(dfprt.Port_code.map(lambda x: dic_om_index[x][2]) == 'M') &
(dfprt.AssetType1 != 'Equity Exposure'), 0,
dfprt.MarketValue.values)
dfprt['EffExposure'] = np.where(
(dfprt.Port_code.map(lambda x: dic_om_index[x][2]) == 'M') &
(dfprt.AssetType1 != 'Equity Exposure'), 0,
dfprt.EffExposure.values)
#Remove SSF Dividend Exposure
dfprt.loc[:, 'EffExposure'] = np.where(
dfprt[['AssetType2']].isin(override), 0, dfprt[['EffExposure']])
dfprt = dfprt[~(dfprt.Port_code.isnull())]
dfprt = dfprt[~(dfprt.Quantity.isnull())]
dfprt_preflow = dfprt.copy()
# Add futures structureback
dfprt_preflow = dfprt_preflow.append(fut_flow, sort=True)
if ~cash_flows_eff.empty:
# add cash flow check
xx = cfvf(cash_flows_eff,
newest_cash,
startDate,
lst_fund,
bf=0.005)
cash_flows_eff = cash_flows_eff.merge(
(xx[1])[['Port_code', 'Inflow_use']],
on=['Port_code'],
how='left')
cash_flows_eff = cash_flows_eff[[
'Port_code', 'Inflow_use', 'Eff_cash', 'fut_sufx'
]]
cash_flows_eff.columns = [
'Port_code', 'Inflow', 'Eff_cash', 'fut_sufx'
]
cash_flows_eff['Trade_date'] = startDate
cash_flows_eff['AssetType1'] = 'Total cash'
cash_flows_eff['AssetType2'] = 'Settled cash'
cash_flows_eff['AssetType3'] = 'Cash flow'
cash_flows_eff['AssetType4'] = 'Cash flow'
cash_flows_eff['Sec_code'] = 'ZAR'
cash_flows_eff['Close_price'] = 1
cash_flows_eff['Quantity'] = cash_flows_eff[['Inflow']]
cash_flows_eff['MarketValue'] = cash_flows_eff[['Inflow']]
cash_flows_eff['EffExposure'] = cash_flows_eff[['Inflow']]
cash_flows = cash_flows_eff[[
'Trade_date', 'Port_code', 'AssetType1', 'AssetType2',
'AssetType3', 'AssetType4', 'Sec_code', 'Close_price',
'Quantity', 'MarketValue', 'EffExposure'
]]
dfprt = dfprt_preflow.append(cash_flows, sort=True)
'''Generate cash calc
- Consolidate the current holdings of each fund
1) Calculate the cash holdings
2) Calculate the futures exposure (both Index and SSF)
3) Calculate the equity market value
4) Calculate the special fund (cash and equity exposure)
'''
# def fx_dta(dfprt_x=dfprt):
# dfprt_1=dfprt_x.groupby(['Trade_date','Port_code','AssetType1','AssetType2','AssetType3']).agg({'EffExposure':'sum','MarketValue':'sum','Quantity':'sum','Close_price':'max'})
# dfprt_1=dfprt_1.reset_index()
# dfprt_2= (dfprt_1.groupby(['Trade_date','Port_code']).agg({'MarketValue':'sum'})).reset_index()
# dfprt_1=pd.merge( dfprt_1,dfprt_2, on=['Trade_date','Port_code'])
# dfprt_1.rename(columns={'MarketValue_x':'MarketValue', 'MarketValue_y':'FundValue'}, inplace=True)
# dfprt_1=dfprt_1[['Trade_date','Port_code','AssetType1','AssetType2','AssetType3','MarketValue','EffExposure','Quantity','FundValue','Close_price']]
# dfprt_1=dfprt_1.groupby(['Trade_date','Port_code','AssetType1','AssetType2','AssetType3']).agg({'EffExposure':'sum','MarketValue':'sum','FundValue':'max','Quantity':'max','Close_price':'max'})
#
# req_sum={'EffExposure':'sum','MarketValue':'sum','FundValue':'max','Quantity':'max','Close_price':'max'}
# total_cash= (dfprt_1[(dfprt_1.index.get_level_values('AssetType1').isin(['Total cash']))]).reset_index().groupby(['Trade_date','Port_code']).agg(req_sum)
#
# effective_cash=((total_cash-(dfprt_1[(dfprt_1.index.get_level_values('AssetType1').isin(['Futures Exposure']))]).reset_index().groupby(['Trade_date','Port_code']).agg(req_sum)).fillna(0))
# effective_cash['MarketValue']=0
# effective_cash['FundValue']=total_cash[['FundValue']].values
# effective_cash['EffExposure']=np.where(effective_cash[['EffExposure']].values==0,total_cash[['EffExposure']].values, effective_cash[['EffExposure']].values)
#
#
# cash_dat=res_ind(effective_cash,'Effective cash').reset_index()
# cash_dat['Trade_date']=startDate
# cash_dat=(cash_dat[['Trade_date', 'Port_code','AssetType1','AssetType2','AssetType3', 'Quantity','EffExposure','MarketValue','FundValue','Close_price']])
# new_dat=((pd.concat([dfprt_1.reset_index(),cash_dat],axis=0,sort=True).reset_index().drop('index',axis=1)).sort_values(['Port_code','AssetType1','AssetType2','AssetType3'])).set_index(['Trade_date','Port_code','AssetType1','AssetType2','AssetType3'])
# new_dat['EffWgt']=new_dat[['EffExposure']].values/new_dat[['FundValue']].values
# new_dat['MktWgt']=new_dat[['MarketValue']].values/new_dat[['FundValue']].values
# n_1 = new_dat.reset_index()
# n_1=n_1.groupby(['Port_code','AssetType1']).agg({'EffExposure':'sum','EffWgt':'sum'})
# n_1=n_1[~(n_1.index.get_level_values('AssetType1').isin(['Dividend Exposure']))]
# n_2=n_1.reset_index()
# fnd_value=(total_cash[['FundValue']].reset_index().set_index('Port_code')[['FundValue']]).reset_index()
# fnd_value['AssetType1']='Fund Value'
# fnd_value['EffWgt']=1
# fnd_value.columns= ['Port_code','EffExposure','AssetType1','EffWgt']
# fnd_value=fnd_value[n_2.columns]
# n_3=n_2.append(fnd_value)
# n_3=n_3.reset_index().pivot(index='Port_code', columns='AssetType1',values='EffExposure')
# n_4=n_2.reset_index().pivot(index='Port_code', columns='AssetType1',values='EffWgt')
#
# n_3.columns=[sym.replace(" ", "")+'_R' for sym in n_3.columns]
# n_4.columns=[sym.replace(" ", "")+'_p' for sym in n_4.columns]
#
# n_comb=n_3.merge(n_4, left_index=True, right_index=True)
# n_comb[['FuturesExposure_R']]=(n_comb[['FuturesExposure_R']]).fillna(0)
# n_comb[['FuturesExposure_p']]=(n_comb[['FuturesExposure_p']]).fillna(0)
# lst = [new_dat, n_comb]
# return lst
# Pre flow
new_dat_preflow = fx_dta(dfprt_preflow, startDate)
new_dat_pf = new_dat_preflow[0]
n_comb_pf = new_dat_preflow[1]
# Post flow
new_dat_x = fx_dta(dfprt, startDate)
new_dat = new_dat_x[0]
n_comb = new_dat_x[1]
#fut_price=((new_dat[new_dat.index.get_level_values('AssetType2')=='Index Future']['Close_price']).reset_index())[['Port_code','AssetType3','Close_price']]
no_fut = ((new_dat[new_dat.index.get_level_values('AssetType2') ==
'Index Future'][['Quantity']]).reset_index())[[
'Port_code', 'AssetType3', 'Quantity'
]]
fut_code1 = (cash_lmt_x[['P_Code', 'Future_Code']]).copy()
fut_code1.loc[:, 'Future_Code'] = np.where(
fut_code1['Future_Code'] == 'NoFuture', 'NoFuture',
fut_code1['Future_Code'] + str(
(cash_flows_eff['fut_sufx'].values)[0]))
no_fut = no_fut.merge(fut_code1,
how='right',
left_on=['Port_code'],
right_on=['P_Code'])
no_fut['AssetType3'] = np.where(no_fut.AssetType3.isnull(),
no_fut.Future_Code.values,
no_fut.AssetType3.values)
no_fut = (no_fut[['P_Code', 'AssetType3', 'Quantity']]).merge(
(((new_dat[new_dat.index.get_level_values('AssetType2') ==
'Index Future'][['Close_price']]).reset_index())[[
'AssetType3', 'Close_price'
]]).drop_duplicates(['AssetType3']),
how='left',
left_on=['AssetType3'],
right_on=['AssetType3'])
no_fut = no_fut.fillna(0)
no_fut.columns = ['Port_code', 'AssetType3', 'Quantity', 'Close_price']
n_comb = (n_comb.reset_index()).merge(no_fut,
how='left',
left_on=['Port_code'],
right_on=['Port_code'])
# Get Inflow information & override effective cash if applicable
cash_lmt = pd.merge(cash_lmt_x,
cash_flows_eff[['Port_code', 'Eff_cash',
'Inflow']],
how='left',
left_on=['P_Code'],
right_on=['Port_code'])
cash_lmt.pop('Port_code')
cash_lmt = cash_lmt.rename(columns={'Eff_cash': 'Ovd_Effcash'})
cash_lmt['Tgt_EffCash1'] = np.where(cash_lmt[['Ovd_Effcash']].isnull(),
cash_lmt[['Tgt_EffCash']].values,
cash_lmt[['Ovd_Effcash']])
# Get Futures codes
get_Futurecodes = fut_code1
cash_lmt = cash_lmt.merge(get_Futurecodes,
how='left',
left_on=['P_Code'],
right_on=['P_Code'])
#cash_lmt=cash_lmt.drop(['Port_code'], axis=1)
n_comb = pd.merge(n_comb,
cash_lmt,
how='left',
left_on=['Port_code'],
right_on=['P_Code'])
n_comb.loc[:, 'FundValue_p'] = 1
n_comb.loc[:, 'Trade'] = np.where(
n_comb.Future_Code_y == "NoFuture", "No Trade",
np.where((n_comb['Effectivecash_p'].values >=
n_comb['Max_EffCash'].values), 'Buy',
np.where((n_comb['Effectivecash_p'].values <=
n_comb['Min_EffCash'].values), 'Sell',
'No Trade')))
n_comb.loc[:, 'Trade'] = np.where(
(n_comb.Trade == "No Trade") & (~n_comb.Ovd_Effcash.isnull()) &
(~(n_comb.Future_Code_y == "NoFuture")),
np.where((n_comb['Effectivecash_p'].values >=
n_comb['Tgt_EffCash1'].values), 'Buy',
np.where((n_comb['Effectivecash_p'].values <=
n_comb['Tgt_EffCash1'].values), 'Sell',
'No Trade')), n_comb.Trade)
#n_comb.loc[:,'No. Futures']=np.where(n_comb[['Trade']].isin(['Buy','Sell']), np.rint(((n_comb[['Effectivecash_p']].values-n_comb[['Tgt_EffCash1']].values)*n_comb[['FundValue_R']].values)/(n_comb[['Close_price']].values*10)), 0)
n_comb.loc[:, 'NoFutures'] = np.where(
n_comb[['Trade']].isin(['Buy', 'Sell']),
np.rint(((n_comb[['Effectivecash_p']].values -
n_comb[['Tgt_EffCash1']].values) *
n_comb[['FundValue_R']].values) /
(n_comb[['Close_price']].values * 10)), 0)
n_comb.loc[:, 'Fut_price'] = n_comb[['Close_price']].fillna(0).values
# x=chck_fut(-2,(n_comb.tail(1))[['Effectivecash_p']].values,(n_comb.tail(1))[['Max_EffCash']].values, (n_comb.tail(1))[['Min_EffCash']].values,
# (n_comb.tail(1))[['Tgt_EffCash1']].values,(n_comb.tail(1))[['Fut_price']].values, (n_comb.tail(1))[['FundValue_R']].values )
#Check for breach of eff cash limits
n_comb['No. Futures'] = n_comb.apply(lambda r: (chck_fut(
r.NoFutures, r.Effectivecash_p, r.Max_EffCash, r.Min_EffCash, r.
Tgt_EffCash1, r.Fut_price, r.FundValue_R)),
axis=1)
n_comb = n_comb.drop(['Fut_price', 'NoFutures'], axis=1)
# Check for negative effective cash
n_comb.loc[:, 'Trade'] = np.where(
((-(n_comb[['No. Futures']].values *
n_comb[['Close_price']].fillna(0).values * 10) /
n_comb[['FundValue_R']].values) +
n_comb[['Effectivecash_p']].values) < 0, 0, n_comb[['Trade']])
n_comb.loc[:, 'No. Futures'] = np.where(
((-(n_comb[['No. Futures']].values *
n_comb[['Close_price']].fillna(0).values * 10) /
n_comb[['FundValue_R']].values) +
n_comb[['Effectivecash_p']].values) < 0, 0,
n_comb[['No. Futures']])
n_comb.loc[:, 'Trade'] = np.where(n_comb['No. Futures'] == 0,
'No Trade', n_comb.Trade.values)
n_comb.loc[:, 'Effectivecash_T'] = (
-(n_comb[['No. Futures']].values *
n_comb[['Close_price']].fillna(0).values * 10) /
n_comb[['FundValue_R']].values) + n_comb[['Effectivecash_p'
]].values
n_comb.loc[:, 'Effectivecash_TR'] = n_comb[[
'Effectivecash_T'
]].values * n_comb[['FundValue_R']].values
n_comb.loc[:, 'EquityExposure_T'] = n_comb[['EquityExposure_p']].values
n_comb.loc[:,
'EquityExposure_TR'] = n_comb[['EquityExposure_R']].values
n_comb.loc[:, 'FundValue_T'] = 1
n_comb.loc[:, 'FundValue_TR'] = n_comb[['FundValue_R']].values
n_comb.loc[:, 'FuturesExposure_T'] = n_comb[[
'FuturesExposure_p'
]].fillna(0).values + (
(n_comb[['No. Futures']].values *
n_comb[['Close_price']].fillna(0).values * 10) /
n_comb[['FundValue_R']].values)
n_comb.loc[:, 'FuturesExposure_TR'] = n_comb[[
'FuturesExposure_R'
]].fillna(0).values + ((n_comb[['No. Futures']].values *
n_comb[['Close_price']].fillna(0).values * 10))
n_comb.loc[:, 'Totalcash_T'] = n_comb[['Totalcash_p']].values
n_comb.loc[:, 'Totalcash_TR'] = n_comb[['Totalcash_R']].values
n_comb.loc[:, 'Check cash'] = np.where(
(n_comb['Totalcash_T'].values >= n_comb['Max_TotalCash'].values),
'Reduce cash',
np.where((n_comb['Totalcash_T'].values <=
n_comb['Min_TotalCash'].values), 'Increase cash', ''))
n_comb.loc[:,
'Inflow_p'] = n_comb['Inflow'] / n_comb['FundValue_R'].values
# Add Pre-Flow
n_comb_pf.columns = [str(col) + '_pf' for col in n_comb_pf.columns]
n_comb = pd.merge(n_comb,
n_comb_pf.reset_index(),
how='left',
left_on=['Port_code'],
right_on=['Port_code'])
n_comb.loc[:, 'FundValue_p_pf'] = 1
n_comb.loc[:, 'FuturesTraded_R'] = (n_comb['Close_price'].values *
n_comb['No. Futures'].values * 10)
n_comb.loc[:,
'FuturesTraded_p'] = n_comb.loc[:,
'FuturesTraded_R'] / n_comb[
'FundValue_R'].values
n_comb_eff_n = n_comb[[
'Port_code', 'FundValue_R_pf', 'EquityExposure_R_pf',
'Totalcash_R_pf', 'FuturesExposure_R_pf', 'Effectivecash_R_pf',
'FundValue_R', 'EquityExposure_R', 'Totalcash_R',
'FuturesExposure_R', 'Effectivecash_R', 'Tgt_EffCash1',
'No. Futures', 'AssetType3', 'Trade', 'FundValue_TR',
'EquityExposure_TR', 'Totalcash_TR', 'FuturesExposure_TR',
'Effectivecash_TR', 'Check cash', 'Min_EffCash', 'Max_EffCash',
'Min_TotalCash', 'Max_TotalCash', 'Tgt_EffCash', 'Inflow',
'FuturesTraded_R'
]]
n_comb_eff = n_comb_eff_n.copy()
n_comb_eff.loc[:, 'ExposureType'] = ''
n_comb_eff.loc[:, 'Tgt_EffCash1'] = np.nan
n_comb_eff.loc[:, 'No. Futures'] = n_comb[['Close_price']].values
n_comb_eff.loc[:, 'Trade'] = np.nan
n_comb_eff.loc[:, 'Check cash'] = np.nan
n_comb_eff.loc[:, 'Min_EffCash'] = np.nan
n_comb_eff.loc[:, 'Max_EffCash'] = np.nan
n_comb_eff.loc[:, 'Min_TotalCash'] = np.nan
n_comb_eff.loc[:, 'Max_TotalCash'] = np.nan
n_comb_eff.loc[:, 'Tgt_EffCash'] = np.nan
n_comb_eff.loc[:, 'AssetType3'] = np.nan
n_comb_eff_n = n_comb[[
'Port_code', 'FundValue_p_pf', 'EquityExposure_p_pf',
'Totalcash_p_pf', 'FuturesExposure_p_pf', 'Effectivecash_p_pf',
'FundValue_p', 'EquityExposure_p', 'Totalcash_p',
'FuturesExposure_p', 'Effectivecash_p', 'Tgt_EffCash1',
'No. Futures', 'AssetType3', 'Trade', 'FundValue_T',
'EquityExposure_T', 'Totalcash_T', 'FuturesExposure_T',
'Effectivecash_T', 'Check cash', 'Min_EffCash', 'Max_EffCash',
'Min_TotalCash', 'Max_TotalCash', 'Tgt_EffCash', 'Inflow_p',
'FuturesTraded_p'
]]
n_comb_effp = n_comb_eff_n.copy()
n_comb_effp.loc[:, 'ExposureType'] = '(%)'
n_comb_effp.columns = n_comb_eff.columns
n_comb_eff = n_comb_eff.append(n_comb_effp)
n_comb_eff.loc[:, 'Checked'] = ''
#n_comb_eff.loc[:,'Authorised']=''
#n_comb_eff.sort(['Port_code','ExposureType'], ascending=False).set_index(['Port_code','ExposureType']).to_csv('c:\data\check.csv')
n_comb_eff_1 = n_comb_eff.sort_values(['Port_code', 'ExposureType'],
ascending=True).set_index([
'Port_code', 'ExposureType'
])
n_comb_eff_1['Trade_YN'] = ''
n_comb_eff_1['Comment'] = ''
n_comb_eff_1 = n_comb_eff_1[~n_comb_eff_1.index.duplicated(
keep='first')]
# n_comb_eff_1['Trade_p'=]
# write excel report
exl_rep(output_folder, dic_users, n_comb_eff_1, startDate, newest)
#excel_fx(output_folder,dic_users,n_comb_eff_1,startDate)
#exl_rep('c:\\data\\',dic_users,n_comb_eff_1,startDate)
print("\nReport Complete")
#n_comb_eff.sort(['Port_code','ExposureType'], ascending=False).set_index(['Port_code','ExposureType']).to_html(open('c:\data\check.html','w'),formatters='{:,.0f}'.format)
#grp=n_comb_eff.groupby(['Port_code','ExposureType']).agg({'FundValue_R':'sum','EquityExposure_R':'sum'})
#n_comb_eff=n_comb_eff.pivot(index='Port_code', columns='ExposureType')
#n_comb.to_csv('c:\data\check.csv')
else:
#tkinter.Label(window, text = "Please Update Flows").pack()
print("Exit")
def pre_flow_calcFx(response, automatic=True, orders=False, testing=False):
#import future
import sys
#sys.path.append('C:\Program Files (x86)\WinPython\python-3.6.5.amd64\lib\site-packages\IPython\extensions')
#sys.path.append('C:\Program Files (x86)\WinPython\settings\.ipython')
#for p in sys.path:
# print(p)
import numpy as np
import pandas as pd
import datetime as dt
from datetime import datetime, timedelta
import glob
import os
#from pydatastream import Datastream
#from business_calendar import Calendar, MO, TU, WE, TH, FR
import pyodbc
#from write_excel import excel_fx as exl_rep
#from write_excel import input_fx as inp
from write_excel import select_fund as sf
from write_excel import CashFlowFlag as cff
from write_excel import trade_calc as t_c
from write_excel import trade_calc_automatic as t_c_a
from write_excel import bulk_cash_excel_report as bcer
from write_excel import cash_flow_validity_fx as cfvf
from write_excel import assetClassB as assetClass
from write_excel import res_indB as res_ind
from write_excel import fx_dtaB as fx_dta
if testing:
response = 'yes'
automatic = True
orders = False
if response:
start_time = datetime.now()
np.seterr(divide='ignore', invalid='ignore')
#DWE = Datastream(username="******", password="******",proxy='172.23.18.187:3128')
#DWE.system_info()
#DWE.sources()
#data_xls = pd.read_excel('\\\\za.investment.int\\dfs\\dbshared\\DFM\\TRADES\\Decalog Valuation\\UFMPosCash20170811.xls', 'Position', index_col=None)
#type(data_xls)
#data_xls.tail()
"""
Set parameters for trading
"""
# Benchmark list
# Check before you run file
#run_prog=inp()
#if run_prog[0]=='N':
# quit()
#else:
#startDate = datetime.today().date()
startDate = datetime.today()
#startDate = datetime.strptime('Sep 15 2017', '%b %d %Y').datetime()
#startDate = datetime.today()- timedelta(days=1)
pd.options.display.max_rows = 200
#testing=True
# Benchmark settings
folder_yr = datetime.strftime(startDate, "%Y")
folder_mth = datetime.strftime(startDate, "%m")
folder_day = datetime.strftime(startDate, "%d")
# Fund settings
# dirtoimport_file='\\\\za.investment.int\\dfs\\dbshared\\DFM\\TRADES\\Decalog Valuation\\'
#dirtoimport_file= 'H:\\Bernisha\\Work\\IndexTrader\\Data\\required_inputs\\'
dirtoimport_file = '\\\\za.investment.int\\DFS\\SSDecalogUmbono\\IndexationPosFile\\'
dirtoimport_cashfile = '\\\\za.investment.int\\dfs\\dbshared\\DFM\\TRADES\\Decalog Valuation\\'
# directory to export report to
#dirtooutput_file='\\\\za.investment.int\\dfs\\dbshared\\DFM\\TRADES\\Futures Trades'
dirtooutput_file = '\\\\za.investment.int\\dfs\\dbshared\\DFM\\TRADES'
#output_folder='\\'.join([dirtooutput_file ,folder_yr, folder_mth])
output_folder = str(
'\\'.join([dirtooutput_file, folder_yr, folder_mth, folder_day]) +
'\\BatchTrades')
if not os.path.exists(output_folder):
os.makedirs(output_folder)
# Map fund and benchmark settings
# Pull in fund dictionary
fnd_dict = pd.read_csv(
'C:\\IndexTrader\\required_inputs\\fund_dictionary.csv')
dic_om_index = fnd_dict.set_index(['FundCode']).T.to_dict('list')
user_dict = pd.read_csv(
'C:\\IndexTrader\\required_inputs\\user_dictionary.csv')
dic_users = user_dict.set_index(['username']).T.to_dict('list')
fnd_excp = [
'DSALPC', 'OMCC01', 'OMCD01', 'OMCD02', 'OMCM01', 'OMCM02',
'PPSBTA', 'PPSBTB'
]
#dic_om_index = {'DRIEQC':['OM Responsible Equity Fund','CSIESG'],
#'DSWIXC':['SWIX Index Fund','JSESWIXALSICAPPED'],
#'CORPEQ':['OMLAC Shareholder Protected Equity Portfolio','JSESWIXALSI'],
#'MFEQTY':['M&F Protected Equity Portfolio','JSESWIXALSI'],
#'DSALPC':['SA Listed Property Index Fund','JSESAPY'],
#'USWIMF':['Momentum SWIX Index Fund','JSESWIXALSI'],
#'OMRTMF':['RAFI40 Unit Trust','JSERAFI40'],
#'LEUUSW':['Life Equity UPF','JSESWIXALSICAPPED'],
#'LEIUSW':['Life Equity IPF','JSESWIXALSICAPPED'],
#'SASEMF':['SASRIA','JSESWIXALSI'],
#'BIDLMF':['Bidvest Life CAPI','JSECAPIALSI'],
#'BIIDMF':['Bidvest Insurance CAPI','JSECAPIALSI'],
#'ALSCPF':['Assupol CPF','JSESWIXALSI'],
#'ALSIPF':['Assupol IPF','JSESWIXALSI'],
#'ALSUPF':['Assupol UPF','JSESWIXALSI'],
#'UMSMMF':['Samancor Group Provident Fund','JSESWIXALSI'],
#'OMSI01':['OM CAPPED SWIX FUND','JSESWIXALSICAPPED'],
#'UMSWMF':['Momentum SWIX 40 Index Fund','JSESWIX40'],
# # 'UMC1MF':['Anglo Corp CW','JSESWIXALSI'],
#'OMALMF':['Top40 Unit Trust','JSETOP40'],
#'DALSIC':['All Share Index Fund','JSEALSI'],
#}
#
##dic_users={'blala':['BLL','blala'], 'test':['TST','test'], 'sbisho':['SB','sbisho']}
#dic_users={'blala':['BLL','blala'], 'test':['TST','test'], 'sbisho':['SB','sbisho'], 'tmfelang2':['TM','tmfelang'], 'abalfour':['AB','abalfour'], 'sparker2':['SP','sparker'], 'fsibiya':['FS','fsibiya']}
#dic_om_index={
# 'DSALPC':['SA Listed Property Index Fund','JSESAPY',1,5,8,0.0005,0.0022,1,'Option 1 Gross Rate in cents per share'],
# 'CORPEQ':['OMLAC Shareholder Protected Equity Portfolio','JSESWIXALSI',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
# 'DALSIC':['All Share Index Fund','JSEALSI',1,5,8,0.0002,0.0013,3,'Option 1 Gross Rate in cents per share'],
# 'DSWIXC':['SWIX Index Fund','JSESWIXALSICAPPED',1,5,8,0.0002,0.0013,4,'Option 1 Gross Rate in cents per share'],
# 'MFEQTY':['M&F Protected Equity Portfolio','JSESWIXALSI',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
# 'USWIMF':['Momentum SWIX Fund','JSESWIXALSI',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
# 'BIIDMF':['Bidvest Insurance Fund','JSECAPIALSI',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
# 'LEUUSW':['Bidvest Insurance Fund','JSESWIXALSICAPPED',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
## 'OMCC01':['OM Core Conservative','JSESWIXALSICAPPED',1,5,8,0.0002,0.0013,2,'Option 1 Gross Rate in cents per share'],
# }
override = ['SSF DIV']
# Public Holidays
pub_holidays = (pd.read_excel(
"C:\\IndexTrader\\required_inputs\\public_holidays.xlsx")
)['pub_holidays'].tolist()
#cal = Calendar(holidays=pub_holidays)
# Determine list of funds to trade
lst_fund = sf()
# Import cash limits
cash_lmt_x = pd.read_csv(
'C:\\IndexTrader\\required_inputs\\cash_limits.csv')
cash_lmt_x = cash_lmt_x[cash_lmt_x.P_Code.isin(lst_fund)]
cash_lmt_dict = cash_lmt_x.set_index(
['P_Code'])[['Min_EffCash', 'Max_EffCash']].T.to_dict()
# Import Flows
#cash_flows_eff = pd.read_csv('H:\\Bernisha\\Work\\IndexTrader\\Data\\required_inputs\\flows.csv')
cash_flows_eff = pd.read_csv(
'C:\\IndexTrader\\required_inputs\\flows.csv', thousands=',')
cash_flows_act_name = cash_flows_eff[cash_flows_eff.Trade ==
1].Port_code
cash_flows_eff = (
cash_flows_eff[cash_flows_eff.Port_code.isin(lst_fund)]).drop(
'Trade', 1)
# Import futures
fut_flow = pd.merge(cash_lmt_x[['P_Code', 'Future_Code']],
cash_flows_eff[['Port_code', 'fut_sufx']],
how='right',
left_on=['P_Code'],
right_on=['Port_code'])
fut_flow['Sec_code'] = fut_flow[['Future_Code', 'fut_sufx'
]].apply(lambda x: ''.join(x), axis=1)
fut_flow['Sec_code'] = np.where(fut_flow.Future_Code == 'NoFuture',
'NoFuture', fut_flow.Sec_code.values)
# Map Sec type to more descriptive asset classes
#def assetClassB(Sec_type, ins_code,sec_nam,cash_flows_eff):
#
# #ssf=['OMLS'+str((cash_flows_eff['fut_sufx'].values)[0]), 'OMAS'+str((cash_flows_eff['fut_sufx'].values)[0])]
# ssf=['S']
# #excp=['OMLF'+str((cash_flows_eff['fut_sufx'].values)[0]),'OMAF'+str((cash_flows_eff['fut_sufx'].values)[0])]
# excp=['F']
# ind_fut=[str((cash_flows_eff['fut_sufx'].values)[0])] # index future suffix
#
# if Sec_type == 'CASH : CALL ACC':
# return "A. Total cash,Settled cash,Cash on call,Total cash,C. CALL"
# elif Sec_type=='CASH : SAFEX AC':
# return "A. Total cash,Settled cash,Futures margin,Total cash,D. SAFEX"
# elif Sec_type == "CURRENCY" and sec_nam=='VAL':
# return "A. Total cash,Settled cash,Val cash,Total cash,A. VAL"
# elif Sec_type=="PAYABLE" and sec_nam=='DIF':
# return "A. Total cash,Unsettled cash,Dif cash,Total cash,B. DIF"
# elif Sec_type=='FUTRE STCK INDX':
# return str("B. Futures Exposure,"+"Index Future,"+str(ins_code[0:4]+ind_fut[0])+",Futures Exposure,A. INDEX FUTURES")
#
#
## elif Sec_type=='FUTURE : EQUITY' and ins_code in(ssf) :
# elif Sec_type=='FUTURE : EQUITY' and ins_code[3:4] in(ssf):
## return str("Futures Exposure,"+"SSF,"+str(ssf[0]))
# return str("B. Futures Exposure,"+"SSF,null"+",Futures Exposure"+",B. SSF")
# elif Sec_type=='EQ : ORDINARY':
# return "Equity Exposure,Equity,null,Equity Exposure,EQUITY"
# elif Sec_type=='EQ : RIGHTS':
# return "Equity Exposure,Equity Rights,null,Equity Exposure,EQUITY"
# elif Sec_type=='EQ : FOREIGN':
# return "Equity Exposure,Equity Foreign,null,Equity Exposure,EQUITY"
# elif ins_code[3:4] in(excp):
## return str("Dividend Exposure,"+"SSF Div,"+str(excp[0]))
# return str("Dividend Exposure,"+"SSF Div,null,Dividend Exposure,SSF DIV")
# elif Sec_type=="FUND : LOC EQ":
# return str("Equity Exposure,"+"Equity Fund,"+str(ins_code)+",Equity Exposure,EQUITY")
# else:
# return "Other,null,null,Other,OTHER"
#
#
#def res_ind(dat,des,ind=['Trade_date','Port_code','AssetType1','AssetType2','AssetType3','AssetType4','AssetType5','Quantity','EffExposure','MarketValue','FundValue','Close_price']):
# dat=dat.reset_index()
# dat['AssetType1']=des
# dat['AssetType2']='null'
# dat['AssetType3']='null'
# dat['AssetType4']='null'
# dat['AssetType5']='null'
# dat=dat[ind]
# return dat
"""
Fund, Benchmark, Corporate Action data import
"""
#newest = max(glob.iglob(dirtoimport_file+'fund_data/*.xls'), key=os.path.getmtime)
newest = max(glob.iglob(dirtoimport_file + '*.xls'),
key=os.path.getmtime)
newest_cash = max(glob.iglob(dirtoimport_file + '*.xls'),
key=os.path.getmtime)
#str(dirtoimport_file+newest)
#newest
fund_xls = pd.read_excel(
newest,
sheet_name=0,
converters={
'Portfolio': str,
'Price Date': pd.to_datetime,
'Inst Type': str,
'Inst Name': str,
'ISIN': str,
'Instrument': str,
'Quote Close': float,
'Qty': float,
'Market Val': float,
'Delta': float,
'Origin': str
},
)
fund_xls = fund_xls.drop(['Delta'], axis=1)
if orders:
pass
else:
fund_xls = fund_xls[fund_xls.Origin == 'POSITION']
fund_xls = fund_xls.drop(['Origin'], axis=1)
#fund_xls.dtypes
fund_xls.columns = [
'Port_code', 'Price_date', 'Sec_type', 'Sec_name', 'Sec_ISIN',
'Sec_code', 'Close_price', 'Quantity', 'Market_price'
]
fund_xls['Close_price'] = pd.to_numeric(fund_xls.Close_price.values,
errors='coerce')
fund_xls['Quantity'] = pd.to_numeric(fund_xls.Quantity.values,
errors='coerce')
fund_xls['Market_price'] = pd.to_numeric(fund_xls.Market_price.values,
errors='coerce')
fund_obj = fund_xls.select_dtypes(['object'])
fund_xls[fund_obj.columns] = fund_obj.apply(lambda x: x.str.strip())
df = fund_xls.copy()
df = df[(df.Port_code.isin(dic_om_index.keys()))]
df.loc[:, 'Benchmark_code'] = df.Port_code.map(
lambda x: dic_om_index[x][1])
df.loc[:, 'TypeFund'] = df.Port_code.map(lambda x: dic_om_index[x][2])
df['Trade_date'] = startDate
df['AssetType1'] = df.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.
TypeFund, cash_flows_eff)).split(",")[0],
axis=1)
df['AssetType2'] = df.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.
TypeFund, cash_flows_eff)).split(",")[1],
axis=1)
df['AssetType3'] = df.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.
TypeFund, cash_flows_eff)).split(",")[2],
axis=1)
df['AssetType4'] = df.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.
TypeFund, cash_flows_eff)).split(",")[3],
axis=1)
df['AssetType5'] = df.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.
TypeFund, cash_flows_eff)).split(",")[4],
axis=1)
df['MarketValue'] = np.where(
df[['AssetType1'
]].isin(['B. Futures Exposure', 'Dividend Exposure']), 0,
df[['Market_price']])
df['EffExposure'] = df[['Market_price']]
df['Close_price'] = np.where(
(df['AssetType2'].isin(['Index Future'])) &
(df['Quantity'].values != 0),
(df['Market_price'].values / df['Quantity'].values) / 10,
df['Close_price'].values)
# Futures insert
if ~fut_flow.empty:
fut_flow = pd.merge(fut_flow[['Port_code', 'Sec_code']],
(df[[
'Trade_date', 'AssetType1', 'AssetType5',
'AssetType3', 'Sec_code', 'Sec_type',
'Close_price'
]]).drop_duplicates(['Sec_code']),
on=['Sec_code'],
how='left').fillna(0)
fut_flow['Quantity'] = 0
fut_flow['MarketValue'] = 0
fut_flow['EffExposure'] = 0
fut_flow['Trade_date'] = startDate
fut_flow['AssetType1'] = 'B. Futures Exposure'
fut_flow['AssetType5'] = 'A. INDEX FUTURES'
fut_flow['AssetType3'] = fut_flow.Sec_code
fut_flow = (fut_flow[[
'Trade_date', 'Port_code', 'AssetType1', 'AssetType5',
'AssetType3', 'Sec_code', 'Sec_type', 'Close_price',
'Quantity', 'MarketValue', 'EffExposure'
]]).copy()
fut_flow = fut_flow[~(fut_flow.Sec_code == 'NoFuture')]
df = df[df.Port_code.isin(lst_fund)]
dfprt = (df[[
'Trade_date', 'Port_code', 'AssetType1', 'AssetType5',
'AssetType3', 'Sec_code', 'Sec_type', 'Close_price', 'Quantity',
'MarketValue', 'EffExposure'
]]).copy()
# Remove cash and other non-equity asset classes for multi-asset class
dfprt['MarketValue'] = np.where(
(dfprt.Port_code.map(lambda x: dic_om_index[x][2]) == 'M') &
(dfprt.AssetType1 != 'Equity Exposure'), 0,
dfprt.MarketValue.values)
dfprt['EffExposure'] = np.where(
(dfprt.Port_code.map(lambda x: dic_om_index[x][2]) == 'M') &
(dfprt.AssetType1 != 'Equity Exposure'), 0,
dfprt.EffExposure.values)
dfprt = dfprt[~(
(dfprt.AssetType1 == 'Other') &
(dfprt.Port_code.map(lambda x: dic_om_index[x][2]) == 'M'))]
#Remove SSF Dividend Exposure
dfprt.loc[:, 'EffExposure'] = np.where(
dfprt[['AssetType5']].isin(override), 0, dfprt[['EffExposure']])
dfprt = dfprt[~(dfprt.Port_code.isnull())]
dfprt = dfprt[~(dfprt.Quantity.isnull())]
dfprt_preflow = dfprt.copy()
# Add futures structureback
dfprt_preflow = dfprt_preflow.append(fut_flow, sort=True)
if ~cash_flows_eff.empty:
xx = cfvf(cash_flows_eff,
newest_cash,
startDate,
lst_fund,
bf=0.01)
cash_flows_eff = cash_flows_eff.merge(
(xx[1])[['Port_code', 'Inflow_use']],
on=['Port_code'],
how='left')
cash_flows_eff = cash_flows_eff[[
'Port_code', 'Inflow_use', 'Eff_cash', 'fut_sufx'
]]
cash_flows_eff.columns = [
'Port_code', 'Inflow', 'Eff_cash', 'fut_sufx'
]
cash_flows_eff['Trade_date'] = startDate
cash_flows_eff['AssetType1'] = 'A. Total cash'
cash_flows_eff['AssetType2'] = 'Settled cash'
cash_flows_eff['AssetType3'] = 'Cash flow'
cash_flows_eff['AssetType4'] = 'Cash flow'
cash_flows_eff['AssetType5'] = 'Cash flow'
cash_flows_eff['Sec_code'] = 'ZAR'
cash_flows_eff['Sec_type'] = 'VAL'
cash_flows_eff['Close_price'] = 1
cash_flows_eff['Quantity'] = cash_flows_eff[['Inflow']]
cash_flows_eff['MarketValue'] = cash_flows_eff[['Inflow']]
cash_flows_eff['EffExposure'] = cash_flows_eff[['Inflow']]
cash_flows = cash_flows_eff[[
'Trade_date', 'Port_code', 'AssetType1', 'AssetType5',
'AssetType3', 'Sec_code', 'Sec_type', 'Close_price',
'Quantity', 'MarketValue', 'EffExposure'
]]
chx_flw = xx[1]
chx_flw = chx_flw[chx_flw.Port_code.isin(
cash_flows_act_name.tolist())]
else:
chx_flw = pd.DataFrame(
columns=['Port_code', 'Inflow_use', 'ActFlow'])
dfprt1 = dfprt_preflow.append(cash_flows, sort=True)
dfprt = dfprt1.drop(['Sec_type'], axis=1)
'''Generate cash calc
- Consolidate the current holdings of each fund
1) Calculate the cash holdings
2) Calculate the futures exposure (both Index and SSF)
3) Calculate the equity market value
4) Calculate the special fund (cash and equity exposure)
'''
#def fx_dta(dfprt_x=dfprt, res_ind):
# dfprt_1=dfprt_x.groupby(['Trade_date','Port_code','AssetType1','AssetType5','AssetType3']).agg({'EffExposure':'sum','MarketValue':'sum','Quantity':'sum','Close_price':'max'})
# dfprt_1=dfprt_1.reset_index()
# dfprt_2= (dfprt_1.groupby(['Trade_date','Port_code']).agg({'MarketValue':'sum'})).reset_index()
# dfprt_1=pd.merge( dfprt_1,dfprt_2, on=['Trade_date','Port_code'])
# dfprt_1.rename(columns={'MarketValue_x':'MarketValue', 'MarketValue_y':'FundValue'}, inplace=True)
# dfprt_1=dfprt_1[['Trade_date','Port_code','AssetType1','AssetType5','AssetType3','MarketValue','EffExposure','Quantity','FundValue','Close_price']]
# dfprt_1=dfprt_1.groupby(['Trade_date','Port_code','AssetType1','AssetType5','AssetType3']).agg({'EffExposure':'sum','MarketValue':'sum','FundValue':'max','Quantity':'max','Close_price':'max'})
#
# req_sum={'EffExposure':'sum','MarketValue':'sum','FundValue':'max','Quantity':'max','Close_price':'max'}
# total_cash= (dfprt_1[(dfprt_1.index.get_level_values('AssetType1').isin(['A. Total cash']))]).reset_index().groupby(['Trade_date','Port_code']).agg(req_sum)
#
# effective_cash=((total_cash-(dfprt_1[(dfprt_1.index.get_level_values('AssetType1').isin(['B. Futures Exposure']))]).reset_index().groupby(['Trade_date','Port_code']).agg(req_sum)).fillna(0))
# effective_cash['MarketValue']=0
# effective_cash['FundValue']=total_cash[['FundValue']].values
# effective_cash['EffExposure']=np.where(effective_cash[['EffExposure']].values==0,total_cash[['EffExposure']].values, effective_cash[['EffExposure']].values)
#
#
# cash_dat=res_ind(effective_cash,'Effective cash').reset_index()
# cash_dat['Trade_date']=startDate
# cash_dat=(cash_dat[['Trade_date', 'Port_code','AssetType1','AssetType5','AssetType3', 'Quantity','EffExposure','MarketValue','FundValue','Close_price']])
# new_dat=((pd.concat([dfprt_1.reset_index(),cash_dat],axis=0, sort=True).reset_index().drop('index',axis=1)).sort_values(['Port_code','AssetType1','AssetType5','AssetType3'])).set_index(['Trade_date','Port_code','AssetType1','AssetType5','AssetType3'])
# new_dat['EffWgt']=new_dat[['EffExposure']].values/new_dat[['FundValue']].values
# new_dat['MktWgt']=new_dat[['MarketValue']].values/new_dat[['FundValue']].values
# n_1 = new_dat.reset_index()
# n_1=n_1.groupby(['Port_code','AssetType1']).agg({'EffExposure':'sum','EffWgt':'sum'})
# n_1=n_1[~(n_1.index.get_level_values('AssetType1').isin(['Dividend Exposure']))]
# n_2=n_1.reset_index()
# fnd_value=(total_cash[['FundValue']].reset_index().set_index('Port_code')[['FundValue']]).reset_index()
# fnd_value['AssetType1']='Fund Value'
# fnd_value['EffWgt']=1
# fnd_value.columns= ['Port_code','EffExposure','AssetType1','EffWgt']
# fnd_value=fnd_value[n_2.columns]
# n_3=n_2.append(fnd_value)
# n_3=n_3.reset_index().pivot(index='Port_code', columns='AssetType1',values='EffExposure')
# n_4=n_2.reset_index().pivot(index='Port_code', columns='AssetType1',values='EffWgt')
#
# n_3.columns=[sym.replace(" ", "")+'_R' for sym in n_3.columns]
# n_4.columns=[sym.replace(" ", "")+'_p' for sym in n_4.columns]
#
# n_comb=n_3.merge(n_4, left_index=True, right_index=True)
# n_comb[['B.FuturesExposure_R']]=(n_comb[['B.FuturesExposure_R']]).fillna(0)
# n_comb[['B.FuturesExposure_p']]=(n_comb[['B.FuturesExposure_p']]).fillna(0)
# lst = [new_dat, n_comb]
# return lst
# Pre flow
new_dat_preflow = fx_dta(dfprt_preflow, startDate)
new_dat_pf = new_dat_preflow[0]
n_comb_pf = new_dat_preflow[1]
time_elapsed = datetime.now() - start_time
print('Time elapsed (hh:mm:ss.ms) {}'.format(time_elapsed))
# Post flow
new_dat_x = fx_dta(dfprt, startDate)
new_dat = new_dat_x[0]
n_comb = new_dat_x[1]
# Determing the number of futures
#fut_price=((new_dat[new_dat.index.get_level_values('AssetType2')=='Index Future']['Close_price']).reset_index())[['Port_code','AssetType3','Close_price']]
no_fut = ((new_dat[new_dat.index.get_level_values('AssetType5') ==
'A. INDEX FUTURES'][['Quantity']]).reset_index())[[
'Port_code', 'AssetType5', 'Quantity'
]]
fut_code1 = (cash_lmt_x[['P_Code', 'Future_Code']]).copy()
fut_code1.loc[:, 'Future_Code'] = np.where(
fut_code1['Future_Code'] == 'NoFuture', 'NoFuture',
fut_code1['Future_Code'] + str(
(cash_flows_eff['fut_sufx'].values)[0]))
no_fut = no_fut.merge(fut_code1,
how='right',
left_on=['Port_code'],
right_on=['P_Code'])
no_fut['AssetType5'] = np.where(no_fut.AssetType5.isnull(),
no_fut.Future_Code.values,
no_fut.AssetType5.values)
no_fut = (no_fut[['P_Code', 'AssetType5', 'Quantity']]).merge(
(((new_dat[new_dat.index.get_level_values('AssetType5') ==
'A. INDEX FUTURES'][['Close_price']]).reset_index())[[
'Port_code', 'AssetType5', 'Close_price'
]]).drop_duplicates(['Port_code', 'AssetType5']),
how='left',
left_on=['P_Code', 'AssetType5'],
right_on=['Port_code', 'AssetType5'])
no_fut = no_fut.fillna(0)
no_fut = no_fut[['Port_code', 'AssetType5', 'Quantity', 'Close_price']]
#no_fut.columns= ['Port_code', 'AssetType5', 'Quantity', 'Close_price']
n_comb = (n_comb.reset_index()).merge(no_fut,
how='left',
left_on=['Port_code'],
right_on=['Port_code'])
# Get Inflow information & override effective cash if applicable
cash_lmt = pd.merge(cash_lmt_x,
cash_flows_eff[['Port_code', 'Eff_cash',
'Inflow']],
how='left',
left_on=['P_Code'],
right_on=['Port_code'])
cash_lmt.pop('Port_code')
cash_lmt = cash_lmt.rename(columns={'Eff_cash': 'Ovd_Effcash'})
cash_lmt['Tgt_EffCash1'] = np.where(cash_lmt[['Ovd_Effcash']].isnull(),
cash_lmt[['Tgt_EffCash']].values,
cash_lmt[['Ovd_Effcash']])
# Get Futures codes
get_Futurecodes = fut_code1
cash_lmt = cash_lmt.merge(get_Futurecodes,
how='left',
left_on=['P_Code'],
right_on=['P_Code'])
#cash_lmt=cash_lmt.drop(['Port_code'], axis=1)
n_comb = pd.merge(n_comb,
cash_lmt,
how='left',
left_on=['Port_code'],
right_on=['P_Code'])
n_comb.loc[:, 'FundValue_p'] = 1
n_comb.loc[:, 'Inflow_p'] = np.where(
n_comb.Inflow.isnull().values, 0,
n_comb.Inflow.values / n_comb.FundValue_R.values)
n_comb.loc[:, 'Mid_Totalcash_p'] = n_comb[[
'Max_TotalCash', 'Min_TotalCash'
]].mean(axis=1) # placeholder
"' Create Breach Flag '"
# Inflow
# Var1 test1 Var2 test2 Action
# Eff cash up (breach upper bound) Total cash up (breach upper bound) Trade equity + futures
# Eff cash up (breach upper bound) Total cash within bounds Trade futures only
# Eff cash up (breach upper bound) Total cash down (breach lower bound) Trade equity + futures
# Eff cash within bounds Total cash up (breach upper bound) Trade equity
# Eff cash within bounds Total cash within bounds No action
# Eff cash within bounds Total cash down (breach lower bound) Trade equity
#Outflow
# Var1 test1 Var2 test2 Action
# Eff cash down (breach lower bound) Total cash down (breach lower bound) Trade equity + futures
# Eff cash down (breach lower bound) Total cash within bounds Trade futures only
# Eff cash down (breach lower bound) Total cash down (breach upper bound) Trade equity + futures
# Eff cash within bounds Total cash down (breach lower bound) Trade equity
# Eff cash within bounds Total cash within bounds No action
# Eff cash within bounds Total cash down (breach upper bound) Trade equity
#Else Override
n_comb.columns = [
'Port_code', 'Totalcash_R', 'FuturesExposure_R', 'Effectivecash_R',
'EquityExposure_R', 'FundValue_R', 'Totalcash_p',
'FuturesExposure_p', 'Effectivecash_p', 'EquityExposure_p',
'AssetType5', 'Quantity', 'Close_price', 'P_Code', 'Future_Code_x',
'Min_EffCash', 'Max_EffCash', 'Min_TotalCash', 'Max_TotalCash',
'Tgt_EffCash', 'Tgt_TotalCash', 'Ovd_Effcash', 'Inflow',
'Tgt_EffCash1', 'Future_Code_y', 'FundValue_p', 'Inflow_p',
'Mid_Totalcash_p'
]
n_comb = n_comb.merge((xx[1])[['Port_code', 'ActFlow']],
how='left',
on=['Port_code'])
n_comb['ActFlow_p'] = n_comb.ActFlow / n_comb.FundValue_R
n_comb['CashFlowFlag'] = n_comb.apply(
lambda r: (cff(r.Effectivecash_p, r.Totalcash_p, r.Max_TotalCash, r
.Min_TotalCash, r.Max_EffCash, r.Min_EffCash, r.
Mid_Totalcash_p, r.ActFlow_p, r.FuturesExposure_p)),
axis=1)
n_comb['fin_teff_cash'] = n_comb.apply(
lambda r:
(t_c(r.CashFlowFlag, r.Tgt_EffCash1, r.Tgt_TotalCash, r.
Future_Code_y, r.Max_EffCash, r.Min_EffCash, r.Ovd_Effcash, r.
Effectivecash_p, r.Totalcash_p, r.FundValue_R, r.Close_price,
r.FuturesExposure_p, r.ActFlow_p)[0]),
axis=1)
n_comb['fin_tot_cash'] = n_comb.apply(
lambda r:
(t_c(r.CashFlowFlag, r.Tgt_EffCash1, r.Tgt_TotalCash, r.
Future_Code_y, r.Max_EffCash, r.Min_EffCash, r.Ovd_Effcash, r.
Effectivecash_p, r.Totalcash_p, r.FundValue_R, r.Close_price,
r.FuturesExposure_p, r.ActFlow_p)[1]),
axis=1)
n_comb['InvType'] = np.where(
n_comb.Inflow.values > 0, 'Investment',
np.where(n_comb.Inflow.values < 0, 'Withdrawal Pay(t)',
'No cash flow'))
if automatic:
n_comb['trd_fut'] = n_comb.apply(lambda r: (t_c_a(
r.Port_code, r.CashFlowFlag, r.Tgt_EffCash1, r.Tgt_TotalCash, r
.Future_Code_y, r.Max_EffCash, r.Min_EffCash, r.Ovd_Effcash, r.
Effectivecash_p, r.Totalcash_p, r.FundValue_R, r.Close_price, r
.FuturesExposure_p, r.ActFlow_p, r.Quantity)[2]),
axis=1).astype(float)
n_comb['tot_fut'] = n_comb.apply(lambda r: (t_c_a(
r.Port_code, r.CashFlowFlag, r.Tgt_EffCash1, r.Tgt_TotalCash, r
.Future_Code_y, r.Max_EffCash, r.Min_EffCash, r.Ovd_Effcash, r.
Effectivecash_p, r.Totalcash_p, r.FundValue_R, r.Close_price, r
.FuturesExposure_p, r.ActFlow_p, r.Quantity)[3]),
axis=1).astype(float)
n_comb['cash_bpm'] = n_comb.apply(lambda r: (t_c_a(
r.Port_code, r.CashFlowFlag, r.Tgt_EffCash1, r.Tgt_TotalCash, r
.Future_Code_y, r.Max_EffCash, r.Min_EffCash, r.Ovd_Effcash, r.
Effectivecash_p, r.Totalcash_p, r.FundValue_R, r.Close_price, r
.FuturesExposure_p, r.ActFlow_p, r.Quantity)[4]),
axis=1)
n_comb['eq_trade'] = n_comb.apply(lambda r: (t_c_a(
r.Port_code, r.CashFlowFlag, r.Tgt_EffCash1, r.Tgt_TotalCash, r
.Future_Code_y, r.Max_EffCash, r.Min_EffCash, r.Ovd_Effcash, r.
Effectivecash_p, r.Totalcash_p, r.FundValue_R, r.Close_price, r
.FuturesExposure_p, r.ActFlow_p, r.Quantity)[5]),
axis=1)
# n_comb.to_hdf(str('c:/data/n_comb_'+str(startDate.date())+'.hdf'),'w', data_columns=True, format='table')
# dfprt.to_hdf(str('c:/data/df_'+ str(startDate.date())+'.hdf'),'w', data_columns=True,format='table')
run_btch = bcer(startDate, new_dat_pf, new_dat, n_comb, dic_users,
dic_om_index, newest, output_folder, fnd_excp, chx_flw,
automatic)
# print("\nReport Complete")
return run_btch
else:
# print("Exit")
return "Error in \n creating \n batch"
def look_through_fx(dfprt1, n_comb, fund_xls, dic_om_index, cash_flows_eff,
lst_fund):
import pandas as pd
import numpy as np
from write_excel import assetClassB as assetClass
from tkinter import filedialog
from tkinter import Tk
dfprt1.loc[:,
'BmkCode'] = dfprt1.Port_code.map(lambda x: dic_om_index[x][3])
#dfprt1.loc[:,'TypeFund']=dfprt1.Port_code.map(lambda x:dic_om_index[x][2])
dfprt_eq = dfprt1[dfprt1.Sec_type.isin([
'EQ : ORDINARY', 'EQ : RIGHTS', 'FUTURE : EQUITY', 'FUND : LOC EQ',
"EQ : PROPERTY", "EQ : PREFERED", "FUND : LOC EQ S",
'EQ : ORDINARY SHARE', 'EQ : STANDARD RIGHTS ISSUE', 'FUTURE : EQUITY',
'FUND : LOCAL EQUITY', 'EQ : PROPERTY', 'EQ : PREFERED SHARE',
'FUND : LOCAL EQUITY SMALL CAP'
])]
# Add Futures and cash inforamtion
fut_dtaX = n_comb[['Port_code', 'tot_fut', 'Close_price', 'Future_Code_y']]
fut_dta = fut_dtaX.copy()
fut_dta.loc[:,
'EffExposure'] = fut_dta[['tot_fut']].values * np.nan_to_num(
fut_dta[['Close_price']].values) * 10
fut_dta.loc[:, 'MarketValue'] = 0
fut_dta.loc[:, 'Quantity'] = fut_dta['tot_fut']
fut_dta.loc[:, 'Sec_type'] = 'FUTRE STCK INDX'
fut_dta.loc[:, 'Sec_code'] = fut_dta['Future_Code_y'].values
fut_dta.loc[:, 'Sec_name'] = fut_dta['Future_Code_y'].values
fut_dta.loc[:,
'Trade_date'] = dfprt1['Trade_date'].head(len(fut_dta)).values
fut_dta = fut_dta[[
'Close_price', 'EffExposure', 'MarketValue', 'Port_code', 'Quantity',
'Sec_code', 'Sec_name', 'Sec_type', 'Trade_date'
]]
fut_dta = fut_dta[~(fut_dta.Sec_code == 'NoFuture')]
del fut_dtaX
cash_dtaX = n_comb[['Port_code', 'cash_bpm']]
cash_dta = cash_dtaX.copy()
cash_dta.loc[:, 'Close_price'] = 1
cash_dta.loc[:, 'EffExposure'] = cash_dta['cash_bpm']
cash_dta.loc[:, 'MarketValue'] = cash_dta['cash_bpm']
cash_dta.loc[:, 'Quantity'] = cash_dta['cash_bpm']
cash_dta.loc[:, 'Sec_type'] = 'CURRENCY'
cash_dta.loc[:, 'Sec_code'] = 'ZAR'
cash_dta.loc[:, 'Trade_date'] = dfprt1['Trade_date'].head(
len(cash_dta)).values
cash_dta.loc[:, 'Sec_name'] = 'VAL'
cash_dta = cash_dta[[
'Close_price', 'EffExposure', 'MarketValue', 'Port_code', 'Quantity',
'Sec_code', 'Sec_name', 'Sec_type', 'Trade_date'
]]
del cash_dtaX
tot_dta = fut_dta.append(cash_dta, sort=False)
tot_dta.loc[:, 'TypeFund'] = tot_dta.Port_code.map(
lambda x: dic_om_index[x][2])
tot_dta['AssetType1'] = tot_dta.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.TypeFund,
cash_flows_eff)).split(",")[0],
axis=1)
tot_dta['AssetType3'] = tot_dta.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.TypeFund,
cash_flows_eff)).split(",")[2],
axis=1)
tot_dta['AssetType5'] = tot_dta.apply(
lambda r: (assetClass(r.Sec_type, r.Sec_code, r.Sec_name, r.TypeFund,
cash_flows_eff)).split(",")[4],
axis=1)
tot_dta.loc[:, 'BmkCode'] = tot_dta.Port_code.map(
lambda x: dic_om_index[x][3])
tot_dta = tot_dta[[
'AssetType1', 'AssetType3', 'AssetType5', 'Close_price', 'EffExposure',
'MarketValue', 'Port_code', 'Quantity', 'Sec_code', 'Sec_type',
'Trade_date', 'BmkCode'
]]
# Append to equity holdings, cash and futures
dfprt_all = dfprt_eq.append(tot_dta, sort=False)
# Pul in exceptions
# Pull in n_comb (data - cash_bpm, tot_fut)
lst_bmks = dfprt1['BmkCode'].unique().tolist()
bmk_dta = fund_xls[fund_xls.Port_code.isin(lst_bmks)][[
'Port_code', 'Sec_code', 'Close_price', 'Quantity', 'Market_price'
]]
bmk_dta.columns = [
'BmkCode', 'Sec_code', 'Bmk_price', 'Bmk_quantity', 'BmkValue'
]
all_bmk_data = pd.DataFrame()
for key, val in dic_om_index.items():
if key in lst_fund:
print(key)
print(val[3])
df = bmk_dta[bmk_dta.BmkCode == val[3]]
if df.empty:
print("No Data")
else:
df1 = df.copy()
df1.loc[:, 'Port_code'] = key
df1.loc[:,
'bmk_wgt'] = df['BmkValue'] / np.nansum(df['BmkValue'])
all_bmk_data = all_bmk_data.append([df1])
del df1
del df
# Composites Data
mapp_comp = pd.read_csv(
'C:\\IndexTrader\\required_inputs\\comp_mappings.csv')
mapp_comp['InstName'] = np.where(
mapp_comp.Type.isin(['FUTURE : EQUITY', 'FUTRE STCK INDX']),
mapp_comp.InstName + cash_flows_eff.fut_sufx.unique()[0],
mapp_comp.InstName)
dic_om_composites = mapp_comp.set_index(['InstName']).T.to_dict('list')
all_dta = pd.DataFrame()
for key, val in dic_om_composites.items():
key_1 = key
val_1 = val[1]
val_2 = val[0]
print(val_1)
if val_2 in ['FUND : LOC EQ', 'FUTRE STCK INDX']:
comp_dta = fund_xls[fund_xls.Port_code == val_1][[
'Port_code', 'Sec_code', 'Close_price', 'Quantity',
'Market_price'
]]
comp_dta['Comp_Code'] = key_1
comp_dta['Comp_wght'] = comp_dta['Market_price'] / comp_dta[
'Market_price'].sum()
else:
comp_dta = fund_xls[fund_xls.Sec_code == key_1][[
'Port_code', 'Sec_code', 'Close_price', 'Quantity',
'Market_price'
]]
comp_dta['Comp_Code'] = key_1
comp_dta['Port_code'] = val_1
comp_dta['Close_price'] = (fund_xls[fund_xls.Sec_code == val_1]
['Close_price']).unique()[0]
comp_dta = comp_dta.drop_duplicates(['Port_code', 'Sec_code'])
comp_dta['Comp_wght'] = 1
all_dta = all_dta.append([comp_dta])
print(key_1)
all_dta.columns = [
'BComCode', 'BSec_code', 'Comp_price', 'comp_quantity', 'CompValue',
'Comp_Code', 'Comp_wgt'
]
dfprt_comp = pd.merge(dfprt_all,
all_dta,
left_on=['Sec_code'],
right_on=['Comp_Code'],
how='outer')
dfprt_comp = dfprt_comp[~dfprt_comp.Sec_code.isnull()]
dfprt_comp.loc[:, 'Cmb_code'] = np.where(
dfprt_comp.Sec_type.isin(['FUTURE : EQUITY', 'EQ : RIGHTS']),
dfprt_comp.BComCode,
np.where(dfprt_comp.BSec_code.isnull(), dfprt_comp.Sec_code.values,
dfprt_comp.BSec_code.values))
dfprt_comp.loc[:, 'Cmb_effexp'] = np.where(
dfprt_comp.Comp_wgt.isnull(), dfprt_comp.EffExposure.values,
dfprt_comp.Comp_wgt.values * dfprt_comp.EffExposure.values)
dfprt_comp.loc[:, 'Cmb_effexp'] = np.where(
dfprt_comp.Sec_type.isin(['EQ : RIGHTS']),
dfprt_comp.Quantity.values * dfprt_comp.Comp_price.values,
dfprt_comp.Cmb_effexp.values)
#dfprt_comp[dfprt_comp.Sec_code.isin(['RBP','RBPN','ALSIJ19','DSWIXCCH','DRIEQCCH','OMUSJ19','NXDSJ19'])].to_csv('c:\\data\\right_x.csv')
dfprt_comp_agg = dfprt_comp.groupby(
['Trade_date', 'Port_code', 'BmkCode',
'Cmb_code']).agg({'Cmb_effexp': 'sum'})
dfprt_comp_fund = dfprt_comp.groupby(
['Trade_date', 'Port_code', 'BmkCode']).agg({'Cmb_effexp': 'sum'})
dfprt_comp_fund.columns = ['Fund_EffExp']
#dfprt_comp_fund.reindex(dfprt_comp_agg.index, level=1).ffill()
dfprt_comp_fund = dfprt_comp_fund.reset_index()
dfprt_comp_agg = dfprt_comp_agg.reset_index()
#dfprt_comp_fund.set_index(['Trade_date','Port_code','BmkCode','Cmb_code'],inplace = True)
dfprt_comp_agg = dfprt_comp_agg.merge(
dfprt_comp_fund, on=['Trade_date', 'Port_code', 'BmkCode'], how='left')
dfprt_comp_agg.loc[:,
'Cmp_wgt'] = dfprt_comp_agg.Cmb_effexp.values / dfprt_comp_agg.Fund_EffExp.values
dfprt_comp_agg = dfprt_comp_agg.groupby(
['Trade_date', 'Port_code', 'BmkCode', 'Cmb_code']).agg({
'Cmb_effexp':
'sum',
'Cmp_wgt':
'sum',
'Fund_EffExp':
'max'
})
dfprt_comp_agg_R = dfprt_comp_agg.reset_index()
dfprt_comp_agg_R.columns = [
'Trade_date', 'Port_code', 'BmkCode', 'Sec_code', 'fnd_val', 'fnd_wgt',
'tot_fnd_val'
]
dfprt_comp_agg_R_B = dfprt_comp_agg_R.merge(
all_bmk_data, on=['Port_code', 'BmkCode', 'Sec_code'], how='outer')
dfprt_comp_agg_R_B.loc[:, 'fnd_wgt'] = dfprt_comp_agg_R_B.fnd_wgt.fillna(0)
dfprt_comp_agg_R_B.loc[:, 'bmk_wgt'] = dfprt_comp_agg_R_B.bmk_wgt.fillna(0)
dfprt_comp_agg_R_B.loc[:, 'fnd_val'] = dfprt_comp_agg_R_B.fnd_val.fillna(0)
dfprt_comp_agg_R_B.loc[:,
'BmkValue'] = dfprt_comp_agg_R_B.BmkValue.fillna(0)
dfprt_comp.loc[:, 'a_Sec_code'] = np.where(
(dfprt_comp.Sec_code != dfprt_comp.Cmb_code) &
(dfprt_comp.Sec_type == 'FUTURE : EQUITY'), dfprt_comp.Cmb_code,
dfprt_comp.Sec_code)
dfprt_comp.loc[:, 'a_Price'] = np.where(
(dfprt_comp.Sec_code != dfprt_comp.Cmb_code) &
(dfprt_comp.Sec_type == 'FUTURE : EQUITY'), dfprt_comp.Comp_price,
dfprt_comp.Close_price)
dfprt_comp_agg_R_B_q = dfprt_comp_agg_R_B.merge(
dfprt_comp[['Port_code', 'Quantity', 'a_Sec_code', 'Cmb_code']],
left_on=['Port_code', 'Sec_code'],
right_on=['Port_code', 'a_Sec_code'],
how='left')
dfprt_comp_agg_R_B_q = dfprt_comp_agg_R_B_q.merge(
dfprt_comp[['Port_code', 'a_Sec_code', 'a_Price']],
left_on=['Port_code', 'Sec_code'],
right_on=['Port_code', 'a_Sec_code'],
how='left')
dfprt_comp_agg_R_B_q = dfprt_comp_agg_R_B_q[[
'Trade_date', 'Port_code', 'Sec_code', 'BmkCode', 'fnd_val', 'fnd_wgt',
'tot_fnd_val', 'Bmk_price', 'Bmk_quantity', 'BmkValue', 'bmk_wgt',
'Quantity', 'a_Price'
]]
dfprt_comp_agg_R_B_q.loc[:, 'Quantity'] = dfprt_comp_agg_R_B_q.fillna(0)
dfprt_comp_agg_R_B_q.loc[:, 'U_Price'] = np.where(
dfprt_comp_agg_R_B_q.a_Price.isnull(),
np.where(dfprt_comp_agg_R_B_q.Bmk_price.isnull(), np.nan,
dfprt_comp_agg_R_B_q.Bmk_price.values),
dfprt_comp_agg_R_B_q.a_Price.values)
dfprt_comp_agg_R_B_q.loc[:,
'act_bet'] = dfprt_comp_agg_R_B_q.fnd_wgt - dfprt_comp_agg_R_B_q.bmk_wgt
root = Tk()
root.filename = filedialog.askopenfilename(
initialdir=
'\\\\za.investment.int\\dfs\\dbshared\\DFM\\Benchmarks\\BlockList\\',
title="Please import Block list",
filetypes=[("all files", "*.*")])
# print (root.filename)
root.withdraw()
hd = pd.read_excel(root.filename)
hd = hd.drop(['_type'], axis=1)
excep_xls = (hd[hd.iloc[:, 1] == 'P'].iloc[:, 0]).tolist()
excl_xls = (hd[hd.iloc[:, 1] != 'P'].iloc[:]).set_index(
['!ID']).T.to_dict('list')
x_list = (
dfprt_comp_agg_R_B_q[~dfprt_comp_agg_R_B_q.Sec_code.
isin(list(excl_xls.keys()))].drop_duplicates([
'Sec_code'
]))[['Sec_code', 'fnd_wgt'
]].set_index(['Sec_code']).T.to_dict('list')
zxclusion = {**excl_xls, **x_list}
return [excep_xls, excl_xls, dfprt_comp_agg_R_B_q]