def read_line_position(ws, row, fields):
"""
Read a line in the unmatched position file, return a position record.
"""
line_info = {}
column = 0
for fld in fields:
cell_value = ws.cell_value(row, column)
if isinstance(cell_value, str):
cell_value = cell_value.strip()
if fld == 'Portfolio' and isinstance(cell_value, float):
cell_value = str(int(cell_value))
if fld in ['Interest Start Day', 'Maturity']:
cell_value = convert_datetime_to_string(
xldate_as_datetime(cell_value, 0))
line_info[fld] = cell_value
column = column + 1
# end of for loop
return line_info
def create_fx_record(fx_buy, fx_sell, record_fields):
known_fields = {
'RecordType': 'SpotFX',
'RecordAction': 'InsertUpdate',
'KeyValue.KeyName': 'UserTranId1',
'Strategy': 'Default',
'FundStructure': 'CALC',
'ContractFxRate': 'CALC'
}
new_record = {}
for record_field in record_fields:
if record_field in known_fields:
new_record[record_field] = known_fields[record_field]
elif record_field == 'Portfolio':
new_record[record_field] = fx_buy['ACCT_ACNO']
elif record_field == 'LocationAccount':
new_record[record_field] = get_LocationAccount(fx_buy['ACCT_ACNO'])
elif record_field == 'Broker':
new_record[record_field] = new_record['LocationAccount']
elif record_field == 'EventDate':
new_record[record_field] = convert_datetime_to_string(
fx_buy['TRDDATE'])
elif record_field == 'SettleDate':
new_record[record_field] = convert_datetime_to_string(
fx_buy['STLDATE'])
elif record_field == 'ActualSettleDate':
new_record[record_field] = new_record['SettleDate']
elif record_field == 'Investment':
new_record[record_field] = fx_buy['LCLCCY']
elif record_field == 'CounterInvestment':
new_record[record_field] = fx_sell['LCLCCY']
elif record_field == 'Quantity':
new_record[record_field] = fx_buy['GROSSLCL']
elif record_field == 'NetCounterAmount':
new_record[record_field] = abs(fx_sell['GROSSLCL'])
elif record_field == 'ContractFxRateNumerator':
new_record[record_field] = new_record['Investment']
elif record_field == 'ContractFxRateDenominator':
new_record[record_field] = new_record['CounterInvestment']
# end of for loop
create_fx_record_key_value(new_record)
return new_record
def create_cash_record(cash_transaction):
known_fields = {
'RecordAction': 'InsertUpdate',
'KeyValue.KeyName': 'UserTranId1',
'Strategy': 'Default',
'FundStructure': 'CALC',
'OEAccount': '',
'CounterTDateFx': '',
'CounterSDateFx': '',
'CounterFXDenomination': ''
}
type_map = {
'IATCA': 'Deposit',
'IATCW': 'Withdraw',
'CashAdd': 'Deposit',
'CashWth': 'Withdraw'
}
new_record = {}
for fld in known_fields:
new_record[fld] = known_fields[fld]
new_record['RecordType'] = type_map[cash_transaction['TRANTYP']]
new_record['Portfolio'] = cash_transaction['ACCT_ACNO']
new_record['LocationAccount'] = get_LocationAccount(
cash_transaction['ACCT_ACNO'])
new_record['EventDate'] = convert_datetime_to_string(
cash_transaction['TRDDATE'])
new_record['SettleDate'] = convert_datetime_to_string(
cash_transaction['STLDATE'])
new_record['ActualSettleDate'] = new_record['SettleDate']
new_record['NetCounterAmount'] = abs(cash_transaction['GROSSLCL'])
new_record['PriceDenomination'] = cash_transaction['LCLCCY']
new_record['CounterInvestment'] = new_record['PriceDenomination']
create_cash_record_key_value(new_record)
return new_record
def write_csv(file, record_fields, records):
with open(file, 'w', newline='') as csvfile:
file_writer = csv.writer(csvfile)
file_writer.writerow(record_fields)
for record in records:
row = []
for fld in record_fields:
if fld in ['EventDate', 'SettleDate', 'ActualSettleDate']:
row.append(convert_datetime_to_string(record[fld]))
else:
row.append(record[fld])
file_writer.writerow(row)
def create_paydown_record(paydown_transaction):
known_fields = {
'RecordType':'Paydown',
'RecordAction':'InsertUpdate',
'KeyValue.KeyName':'UserTranId1',
'Comments':'',
'Payment':'AlwaysInCash',
'PaydownLossType':'No Loss'
}
new_record = {}
for fld in known_fields:
new_record[fld] = known_fields[fld]
new_record['Portfolio'] = paydown_transaction['ACCT_ACNO']
new_record['Investment'] = get_geneva_investment_id(paydown_transaction)
new_record['EventDate'] = convert_datetime_to_string(paydown_transaction['TRDDATE'])
new_record['SettleDate'] = convert_datetime_to_string(paydown_transaction['STLDATE'])
new_record['ActualSettleDate'] = new_record['SettleDate']
new_record['PerShareAmount'] = find_paydown_factor(paydown_transaction)
create_paydown_record_key_value(new_record)
return new_record
def create_tax_lot_date(trustee_records, trade_records):
"""
Match tax lots in trustee records to buy trade records, so that we can
populate the date of the matched tax lots to the settlement date of
the trade record.
"""
matched, unmatched = match_repeat(trustee_records, trade_records,
map_trade_tax_lot)
for (trustee_record, trade_record) in matched:
trustee_record['date'] = convert_datetime_to_string(
trade_record['SettleDate'])
for trustee_record in unmatched:
trustee_record['date'] = ''
return trustee_records, unmatched
def read_line_trustee(ws, row, fields):
"""
Read a line in the trustee NAV records file, return a tax lot record.
"""
line_info = {}
column = 0
for fld in fields:
if fld == '':
continue
cell_value = ws.cell_value(row, column)
if isinstance(cell_value, str):
cell_value = cell_value.strip()
if fld in ['Interest Start Day', 'Maturity']:
cell_value = convert_datetime_to_string(
xldate_as_datetime(cell_value, 0))
line_info[fld] = cell_value
column = column + 1
# end of for loop
return line_info