def get_geneva_investment_id(trade_info):
"""
As portfolio 12307 is an equity portfolio, the Geneva investment id
is the Bloomberg ticker without the yellow key, e.g., '11 HK'.
So assumptions for this function are:
1. All investment is equity.
2. In the holdings of the portfolio, the ISIN number to ticker mapping
is unique.
"""
# use a function attribute to store the lookup table, as there is only
# one instance of a function, all invocations access the same variable.
# see http://stackoverflow.com/questions/279561/what-is-the-python-equivalent-of-static-variables-inside-a-function
if 'i_lookup' not in get_geneva_investment_id.__dict__:
get_geneva_investment_id.i_lookup = {}
investment_lookup = get_geneva_investment_id.i_lookup
if len(investment_lookup) == 0:
lookup_file = get_current_path() + '\\investmentLookup.xls'
initialize_investment_lookup(investment_lookup, lookup_file)
# return (name, investment_id)
logger.debug('get_geneva_investment_id(): trade date {0}'.format(
trade_info['Trd Dt']))
return investment_lookup[trade_info['ISIN']]
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 read_line(ws, row, fields):
"""
Read the trade information from a line.
"""
line_info = {}
column = 0
for fld in fields:
logger.debug('read_line(): row={0}, column={1}'.format(row, column))
cell_value = ws.cell_value(row, column)
if isinstance(cell_value, str):
cell_value = cell_value.strip()
if fld == 'Item No.':
cell_value = int(cell_value)
if fld == 'Security Code':
if isinstance(cell_value, float):
cell_value = str(int(cell_value))
if not is_valid_isin(cell_value):
cell_value = map_to_isin(cell_value)
if fld in ['Trade Date', 'Value Date']:
cell_value = xldate_as_datetime(cell_value, 0)
line_info[fld] = cell_value
column = column + 1
return line_info
def data_field_begins(ws, row):
logger.debug('in data_field_begins()')
cell_value = ws.cell_value(row, 0)
if isinstance(cell_value, str) and cell_value.strip() == 'Acct#':
return True
else:
return False
def read_line(ws, row, fields):
"""
Read a line, store as trade information. Note, it only read lines whose
transaction type is one of the following:
1. CSA: transferred in (from accounts not under FT)
2. CSW: transferred out (to accounts not under FT)
3. IATSW: transferred out (internal accounts)
4. IATSA: transferred in (internal accounts)
5. CALLED: called by issuer
6. TNDRL: buy back by issuer
If not, then it returns None.
"""
trade_info = {}
column = 0
for fld in fields:
logger.debug('read_line(): row={0}, column={1}'.format(row, column))
cell_value = ws.cell_value(row, column)
if isinstance(cell_value, str):
cell_value = cell_value.strip()
if fld == 'ACCT_ACNO':
cell_value = str(int(cell_value))
if fld in ['SCTYID_SMSEQ', 'SCTYID_SEDOL', 'SCTYID_CUSIP'
] and isinstance(cell_value, float):
cell_value = str(int(cell_value))
if fld in ['TRDDATE', 'STLDATE', 'ENTRDATE']:
# some FT files uses traditional excel date, some uses
# a float number to represent date.
if is_htm_portfolio(trade_info['ACCT_ACNO']):
cell_value = xldate_as_datetime(cell_value, get_datemode())
else:
cell_value = convert_float_to_datetime(cell_value)
if fld in ['QTY', 'GROSSBAS', 'PRINB', 'RGLBVBAS', 'RGLCCYCLS', \
'ACCRBAS', 'TRNBVBAS', 'GROSSLCL', 'FXRATE', 'TRADEPRC'] \
and isinstance(cell_value, str) and cell_value.strip() == '':
cell_value = 0.0
check_field_type(fld, cell_value)
trade_info[fld] = cell_value
column = column + 1
try:
if not trade_info['TRANTYP'] in ['IATSW', 'IATSA', 'CSA', 'CSW', \
'CALLED', 'TNDRL']:
trade_info = None
break
except KeyError:
pass
# end of for loop
return trade_info
def convert12307(files):
"""
Convert the trade files of portfolio 12307 to Geneva format for quick
import.
files: a list of trade files.
"""
logger.debug('in convert12307()')
output = []
for f in files:
read_trade_file(f, output)
records = convert_to_geneva_records(output)
fix_duplicate_key_value(records)
return records
def read_trade_file(trade_file, output):
logger.debug('read_trade_file(): {0}'.format(trade_file))
wb = open_workbook(filename=trade_file)
ws = wb.sheet_by_index(0)
row = 0
while not data_field_begins(ws, row):
row = row + 1
fields = read_data_fields(ws, row)
row = row + 1
while not is_blank_line(ws, row):
trade_info = read_line(ws, row, fields)
validate_trade_info(trade_info)
output.append(trade_info)
row = row + 1
def write_csv(file, records):
with open(file, 'w', newline='') as csvfile:
logger.debug('write_csv(): {0}'.format(file))
file_writer = csv.writer(csvfile)
fields = get_record_fields()
file_writer.writerow(fields[:-1] + [
'TradeExpenses.ExpenseNumber', 'TradeExpenses.ExpenseCode',
'TradeExpenses.ExpenseAmt'
])
for record in records:
trade_expenses = record['trade_expenses']
if trade_expenses == []:
row = []
for fld in fields:
if fld == 'trade_expenses':
row = row + [' ', ' ', ' ']
else:
item = record[fld]
row.append(item)
file_writer.writerow(row)
else:
for expense_number in range(len(trade_expenses)):
row = []
for fld in fields:
if fld == 'trade_expenses':
row = row + [
expense_number + 1,
trade_expenses[expense_number][0],
trade_expenses[expense_number][1]
]
break
if expense_number == 0:
item = record[fld]
else:
item = ''
row.append(item)
file_writer.writerow(row)
def convert12734(files):
"""
Convert the trade files from settlement to Geneva format for quick trade
import.
files: a list of trade files.
"""
output_list = []
error_list = []
for f in files:
logger.debug('convert12734(): read file {0}'.format(f))
read_transaction_file(f, output_list, error_list)
records = convert_to_geneva_records(output_list)
fix_duplicate_key_value(records)
if len(error_list) > 0:
print('There are {0} rows in error, check log file'.format(len(error_list)))
return records
def read_transaction_file(trade_file, isin_list, output):
"""
Note: Read the transaction file from FT, for securities in isin_list only.
"""
logger.debug('read_transaction_file(): {0}'.format(trade_file))
wb = open_workbook(filename=trade_file)
ws = wb.sheet_by_index(0)
fields = read_data_fields(ws, 0)
row = 1
while row < ws.nrows:
if is_blank_line(ws, row):
break
trade_info = read_line(ws, row, fields)
if not trade_info is None and trade_info['SCTYID_ISIN'] in isin_list:
# validate_trade_info(trade_info)
output.append(trade_info)
row = row + 1
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()
def read_line(ws, row, fields):
"""
Read the trade information from a line.
"""
trade_info = {}
column = 0
for fld in fields:
logger.debug('read_line(): row={0}, column={1}'.format(row, column))
cell_value = ws.cell_value(row, column)
if isinstance(cell_value, str):
cell_value = cell_value.strip()
if fld in ['Acct#', 'Trade#'] and isinstance(cell_value, float):
cell_value = str(int(cell_value))
elif fld in ['Trd Dt', 'Setl Dt']:
cell_value = xldate_as_datetime(cell_value, get_datemode())
trade_info[fld] = cell_value
column = column + 1
# end of for loop
return trade_info
def initialize_investment_lookup(investment_lookup, lookup_file):
"""
Initialize the lookup table from a file, mapping isin code to investment_id.
To lookup,
name, investment_id = investment_lookup(security_id_type, security_id)
"""
logger.debug(
'initialize_investment_lookup(): on file {0}'.format(lookup_file))
wb = open_workbook(filename=lookup_file)
ws = wb.sheet_by_name('Sheet1')
row = 1
while (row < ws.nrows):
isin = ws.cell_value(row, 0)
if isin.strip() == '':
break
name = ws.cell_value(row, 1).strip()
investment_id = ws.cell_value(row, 2).strip()
investment_lookup[isin] = (name, investment_id)
row = row + 1
def read_transaction_file(file, output_list, error_list):
logger.debug('read_transaction_file(): read file: {0}'.format(file))
output, row_in_errow = read_file(file, read_line, validate_line, 13)
output_list.extend(output)
error_list.extend(row_in_errow)