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)