def fix_duplicate_key_value(records):
"""
Detect whether there are duplicate keyvalues for different records,
if there are, modify the keyvalues to make all keys unique.
"""
keys = []
for record in records:
i = 1
temp_key = record['KeyValue']
while temp_key in keys:
temp_key = record['KeyValue'] + '_' + str(i)
i = i + 1
record['KeyValue'] = temp_key
record['UserTranId1'] = temp_key
keys.append(record['KeyValue'])
# check again
keys = []
for record in records:
if record['KeyValue'] in keys:
logger.error(
'fix_duplicate_key_value(): duplicate keys still exists, key={0}, investment={1}'
.format(record['KeyValue'], record['Investment']))
raise DuplicateKeys()
keys.append(record['KeyValue'])
def get_geneva_investment_id(trade_info):
"""
Get the Geneva investment ID for a position.
The function is not complete yet, now we assume it is only used for
HTM portfolios only, otherwise it will throw an error.
"""
if not is_htm_portfolio(trade_info['ACCT_ACNO']):
logger.error('get_geneva_investment_id(): not a HTM portfolio')
raise InvestmentIdNotFound()
if trade_info['SCTYID_ISIN'] != '':
return get_investment_Ids(trade_info['ACCT_ACNO'], 'ISIN',
trade_info['SCTYID_ISIN'])[0]
elif trade_info['SCTYID_SEDOL'] != '':
return get_investment_Ids(trade_info['ACCT_ACNO'], 'SEDOL',
trade_info['SCTYID_SEDOL'])[0]
elif trade_info['SCTYID_CUSIP'] != '':
return get_investment_Ids(trade_info['ACCT_ACNO'], 'CUSIP',
trade_info['SCTYID_CUSIP'])[0]
else:
logger.error(
'get_geneva_investment_id(): no security identifier found for SCTYID_SMSEQ:{0}'
.format(trade_info['SCTYID_SMSEQ']))
raise InvestmentIdNotFound()
def validate_trade_info(trade_info):
logger.debug('validate_trade_info(): trade date={0}, isin={1}'.format(
trade_info['Trd Dt'], trade_info['ISIN']))
# if trade_info['Acct#'] != '12307':
# logger.error('validate_trade_info(): invalid portfolio code: {0}'.format(trade_info['Acct#']))
# raise InvalidTradeInfo
if trade_info['B/S'] == 'B':
settled_amount = trade_info['Units']*trade_info['Unit Price'] + \
(trade_info['Commission'] + trade_info['Tax'] + \
trade_info['Fees'] + trade_info['SEC Fee'])
elif trade_info['B/S'] == 'S':
settled_amount = trade_info['Units']*trade_info['Unit Price'] - \
(trade_info['Commission'] + trade_info['Tax'] + \
trade_info['Fees'] + trade_info['SEC Fee'])
else:
logger.error(
'validate_trade_info(): invalid trade instruction: {0}'.format(
trade_info['B/S']))
raise InvalidTradeInfo
if abs(settled_amount - trade_info['Net Setl']) > 0.1:
logger.error(
'validate_trade_info(): net settlement amount does not match, calculated={0}, read={1}'
.format(settled_amount, trade_info['Net Setl']))
raise InvalidTradeInfo
def get_LocationAccount(portfolio_id):
boc_portfolios = ['12229', '12366', '12528', '12630', '12732', '12733']
jpm_portfolios = ['12548']
if portfolio_id in boc_portfolios:
return 'BOCHK'
elif portfolio_id in jpm_portfolios:
return 'JPM'
else:
logger.error(
'get_LocationAccount(): no LocationAccount found for portfolio id {0}'
.format(portfolio_id))
raise LocationAccountNotFound()
def get_FT_portfolio_currency(portfolio_id):
# FT portfolio's base currency setting. It is not always consistent with
# the correct setting.
FT_usd_portfolio = ['21815']
FT_hkd_portfolio = ['12229', '12366', '12528', '12548', '12630', '12732', \
'12733', '12307', '19437']
if portfolio_id in FT_usd_portfolio:
return 'USD'
elif portfolio_id in FT_hkd_portfolio:
return 'HKD'
else:
logger.error(
'get_FT_portfolio_currency(): no portfolio currency found for {0}'.
format(portfolio_id))
raise PortfolioCurrencyNotFound()
def get_portfolio_currency(portfolio_id):
# A portfolio's base currency
usd_portfolio = ['21815']
hkd_portfolio = [
'12229', '12366', '12528', '12548', '12630', '12732', '12733'
]
if portfolio_id in usd_portfolio:
return 'USD'
elif portfolio_id in hkd_portfolio:
return 'HKD'
else:
logger.error(
'get_portfolio_currency(): no portfolio currency found for {0}'.
format(portfolio_id))
raise PortfolioCurrencyNotFound()
def get_trade_expenses(trade_info):
"""
Extract trade related expenses and group them into 5 categories:
commission, stamp duty, exchange fee, transaction levy, and
miscellaneous fees.
Return trade_expenses, as a list of (expense_code, expense_value) tuples.
For FT historical trades, equity trade expenses are not handled yet.
currently we only handle bond trades, there is no trade expense.
"""
if not is_htm_portfolio(trade_info['ACCT_ACNO']):
logger.error('get_trade_expenses(): trade expense not handled')
raise TradeExpenseNotHandled()
return [] # no explicit trade expense for bond trade
def map_broker_code(broker_code):
"""
Effective 2016-12-13, start using the new broker code.
"""
# print(broker_code)
a_map = {
'BOCI': 'BOCI-EQ',
'CCBS': 'CCB2-EQ',
'CICC': 'CICF-EQ',
'CITI': 'CG-EQ',
'SBSH': 'CG-EQ', # see 12307 Dec 05 trade file
'CLSA': 'CLSA-EQ',
'CMSHK': 'CMS6-EQ',
'DBAB': 'DBG-EQ',
'FBCO': 'CSFB-EQ',
'GSCO': 'GS-EQ',
# note that for 12307 and other ListCo equity portfolios, since
# they only do HK equity, so Guo Tai Jun An securities is mapped
# to its HK arm
'GUO': 'GTHK-EQ',
'HSCL': 'HTIL-EQ',
'JEFF': 'JEF3-EQ',
'JPM': 'JP-EQ',
'MLCO': 'MLAP-EQ',
'MSCO': 'MS-EQ',
'NOMURA': 'INSA-EQ',
'UBS': 'UBSW-EQ',
'CEBSS': 'EBSI-EQ',
'DAIW': 'DAR5-EQ',
'GFS': 'GF01-EQ',
'CGIS': 'CGHK-EQ'
}
try:
return a_map[broker_code]
except KeyError:
logger.error(
'map_broker_code(): broker code {0} does not have a match.'.format(
broker_code))
raise UnknownBrokerCode()
def get_trade_price(trade_info):
"""
Only works for purchase/sale, transfers, calls, tender offer.
If it is transfer, we assume it is always a bond, because only
the HTM bond portfolios have transfers.
"""
if trade_info['TRANTYP'] in ['Purch', 'Sale']:
return trade_info['TRADEPRC']
elif trade_info['TRADEPRC'] > 0:
return trade_info['TRADEPRC'] # use it if it exists
elif trade_info['TRANTYP'] in ['CSA', 'IATSA', 'CALLED', 'TNDRL']:
return abs(trade_info['PRINB'] * trade_info['FXRATE'] /
trade_info['QTY'] * 100)
elif trade_info['TRANTYP'] in ['IATSW', 'CSW']:
return abs(trade_info['TRNBVBAS'] * trade_info['FXRATE'] /
trade_info['QTY'] * 100)
else:
logger.error('get_trade_price(): {0} not handled'.format(
trade_info['TRANTYP']))
raise TradePriceNotFound()
def map_to_isin(security_no):
"""
Map the non-ISIN security code to ISIN.
"""
isin_map = {
'97147884':'HK0000163607',
'BNYHFB12001':'HK0000097490',
'BNYHFN12008':'HK0000109337',
'BNYHFN12017':'HK0000120748',
'BNYHFN13021':'HK0000171949',
'CMU: HSBCFN13002':'HK0000134780',
'EI5766551':'HK0000140217',
'EI7283738':'HK0000083706',
'EI8608990':'HK0000091832',
'EI9135894':'HK0000096856',
'EJ0975098':'HK0000175916'
}
try:
return isin_map[security_no]
except KeyError:
logger.error('map_to_isin(): {0} does not map an ISIN code'.format(security_no))
raise ISINMapFailure(Exception)
def check_field_type(fld, cell_value):
if fld in ['ACCT_ACNO', 'TRANTYP', 'TRANCOD', 'LCLCCY', 'SCTYID_ISIN'] \
and not isinstance(cell_value, str):
logger.error(
'check_field_type(): field {0} should be string, value={1}'.format(
fld, cell_value))
raise InvalidFieldValue()
if fld in ['QTY', 'GROSSBAS', 'PRINB', 'RGLBVBAS', 'RGLCCYCLS', 'ACCRBAS', \
'TRNBVBAS', 'GROSSLCL', 'FXRATE', 'TRADEPRC'] \
and not isinstance(cell_value, float):
logger.error(
'check_field_type(): field {0} should be float, value={1}'.format(
fld, cell_value))
raise InvalidFieldValue()
if fld in ['TRDDATE', 'STLDATE', 'ENTRDATE'
] and not isinstance(cell_value, datetime):
logger.error(
'check_field_type(): field {0} should be datetime, value={1}'.
format(fld, cell_value))
raise InvalidFieldValue()
def validate_line(line_info):
"""
Validate the following:
1. Form serial with trade date (date)
2. Security code is ISIN
"""
if line_info['Trade Date'] - get_date_from_serial(line_info['Form Serial No.']) > timedelta(days=3) \
or get_date_from_serial(line_info['Form Serial No.']) - line_info['Trade Date'] > timedelta(days=3):
logger.error('validate_line(): inconsistent date {0}'.
format(line_info['Trade Date']))
raise InvalidaLineInfo()
if not is_valid_isin(line_info['Security Code']):
logger.error('validate_line(): invalid ISIN {0} on {1}'.
format(line_info['Security Code'], line_info['Trade Date']))
raise InvalidaLineInfo()
if not line_info['Buy/Sell'] in ['Buy', 'Sell']:
logger.error('validate_line(): invalid Buy/Sell action {0}'.
format(line_info['Buy/Sell']))
raise InvalidaLineInfo()
def validate_trade_info(trade_info):
logger.debug(
'validate_trade_info(): trade date={0}, isin={1}, gross amount={2}'.
format(trade_info['TRDDATE'], trade_info['SCTYID_ISIN'],
trade_info['GROSSBAS']))
if trade_info['STLDATE'] < trade_info['TRDDATE'] or \
trade_info['ENTRDATE'] < trade_info['TRDDATE']:
logger.error(
'validate_trade_info(): invalid dates, trade date={0}, settle day={1}, enterday={2}'
.format(trade_info['TRDDATE'], trade_info['STLDATE'],
trade_info['ENTRDATE']))
raise InvalidTradeInfo()
diff = abs(trade_info['GROSSBAS'] * trade_info['FXRATE'] -
trade_info['GROSSLCL'])
if diff > 0.01:
logger.error(
'validate_trade_info(): FX validation failed, diff={0}'.format(
diff))
raise InvalidTradeInfo()
if trade_info['TRANTYP'] in ['Purch', 'Sale']:
# for equity trade
diff2 = abs(
trade_info['PRINB'] *
trade_info['FXRATE']) - trade_info['QTY'] * trade_info['TRADEPRC']
# for bond trade
diff3 = abs(trade_info['PRINB'] * trade_info['FXRATE']
) - trade_info['QTY'] / 100 * trade_info['TRADEPRC']
# print('diff2={0}, diff3={1}'.format(diff2, diff3))
if (abs(diff2) > 0.01 and abs(diff3) > 0.01):
logger.error('validate_trade_info(): price validation failed')
raise InvalidTradeInfo()