def test_refine_price(self):
file = join(get_current_path(), 'samples',
'csa_upload_without_12528.xlsx')
records, row_in_error = read_file(file, read_line)
self.assertEqual(len(row_in_error), 0)
self.assertEqual(len(filter_transfer(records)), 287)
file = join(get_current_path(), 'samples', 'transfer transactions.xls')
ft_records, row_in_error = read_file(file, read_line_ft)
self.assertEqual(len(row_in_error), 0)
records = refine_price(records, ft_records)
self.assertEqual(len(records), 296)
r = filter_record(records, 'CSW', 'US46625HGY09', '12229',
datetime(2009, 12, 29), 3000000)
self.assertAlmostEqual(r['Price'], 107.8825)
r = filter_record(records, 'CSA', 'US268317AB08', '12366',
datetime(2012, 8, 23), 100000)
self.assertAlmostEqual(r['Price'], 104.784)
r = filter_record(records, 'IATSA', 'XS1189103382', '12366',
datetime(2015, 3, 10), 3200000)
self.assertAlmostEqual(r['Price'], 98.523)
r = filter_record(records, 'CSA', 'XS0368899695', '12366',
datetime(2009, 12, 29), 600000)
self.assertAlmostEqual(r['Price'], 103.48)
def read_both_files(self):
trustee_file = join(get_current_path(), 'samples',
'12229_match_sample.xlsx')
trustee_records, row_in_error = read_file(trustee_file,
read_line_trustee)
trade_file = join(get_current_path(), 'samples',
'trade_upload_match_sample.xlsx')
trade_records, row_in_error = read_file(trade_file, read_line_trade)
return trustee_records, trade_records
def test_update_position(self):
input_file = join(get_current_directory(), 'samples',
'Jones Holding 2017.12.20.xlsx')
jones_holding, row_in_error = read_file(input_file, read_line_jones)
input_file = join(get_current_directory(), 'samples',
'12229 local appraisal 20180103.xlsx')
geneva_holding, row_in_error = read_file(input_file, read_line)
self.assertEqual(len(geneva_holding), 88)
update_position(geneva_holding, jones_holding)
self.verify_geneva_position1(geneva_holding[1])
self.verify_geneva_position2(geneva_holding[4])
self.verify_geneva_position3(geneva_holding[87])
def test_update_amortized_cost(self):
input_file = join(get_current_directory(), 'samples', 'new_12229.xlsx')
trustee_holding, row_in_error = read_file(input_file,
read_line_trustee,
starting_row=2)
input_file = join(get_current_directory(), 'samples',
'12229_local_appraisal_sample5.xlsx')
geneva_holding, row_in_error = read_file(input_file, read_line)
self.assertEqual(len(geneva_holding), 88)
update_amortized_cost(geneva_holding, trustee_holding)
self.verify_geneva_position1(geneva_holding[1])
self.verify_geneva_position2(geneva_holding[6])
self.verify_geneva_position3(geneva_holding[87])
def test_read_line_bond(self):
file1 = join(get_current_path(), 'samples', 'bond_issue.xlsx')
records, row_in_error = read_file(file1, read_line_bond)
self.assertEqual(len(records), 51)
self.assertEqual(len(row_in_error), 0)
self.verify_bond_record1(records[0])
self.verify_bond_record2(records[47])
def test_read_line_position(self):
file = join(get_current_path(), 'samples', 'unmatched_position.xlsx')
records, row_in_error = read_file(file, read_line_position)
self.assertEqual(len(records), 151)
self.assertEqual(len(row_in_error), 0)
self.verify_position_record1(records[0])
self.verify_position_record2(records[150])
def test_read_file4(self):
file = join(get_current_directory(), 'samples',
'12732_local_appraisal_sample4.xlsx')
holding, row_in_error = read_file(file, read_line)
self.assertEqual(len(row_in_error), 0)
self.assertEqual(len(holding), 3)
self.assertEqual(len(filter_maturity(holding)), 1)
def test_read_line_trustee(self):
trustee_file = join(get_current_path(), 'samples', '12229_sample.xlsx')
records, row_in_error = read_file(trustee_file, read_line_trustee)
self.assertEqual(len(records), 7)
self.assertEqual(len(row_in_error), 0)
self.verify_trustee_record1(records[0])
self.verify_trustee_record2(records[6])
def test_read_line_trade(self):
trade_file = join(get_current_path(), 'samples',
'trade_upload_sample.xlsx')
records, row_in_error = read_file(trade_file, read_line_trade)
self.assertEqual(len(records), 5)
self.assertEqual(len(row_in_error), 0)
self.verify_trade_record1(records[0])
self.verify_trade_record2(records[4])
def test_read_file3(self):
file = join(get_current_directory(), 'samples',
'12229_local_appraisal_sample3.xlsx')
holding, row_in_error = read_file(file, read_line)
self.assertEqual(len(row_in_error), 0)
self.assertEqual(len(holding), 6)
self.assertEqual(len(filter_maturity(holding)), 2)
self.verify_position5(filter_maturity(holding))
def test_read_line_jones(self):
input_file = join(get_current_directory(), 'samples',
'Jones Holding 2017.12.20.xlsx')
holding, row_in_error = read_file(input_file, read_line_jones)
self.assertEqual(len(holding), 105)
self.verify_jones_position1(holding[0])
self.verify_jones_position2(holding[4])
self.verify_jones_position3(holding[104])
def test_read_line_trustee(self):
input_file = join(get_current_directory(), 'samples', 'new_12229.xlsx')
holding, row_in_error = read_file(input_file,
read_line_trustee,
starting_row=2)
self.assertEqual(len(holding), 85)
self.verify_trustee_position1(holding[0])
self.verify_trustee_position2(holding[3])
self.verify_trustee_position3(holding[84])
def get_holding_from_files(input_dir=get_geneva_input_directory()):
"""
Based on all local position appraisal files in a directory, then
read Geneva holdings from them.
"""
file_list = [join(input_dir, f) for f in listdir(input_dir) \
if isfile(join(input_dir, f)) and f.split('.')[-1] == 'xlsx']
geneva_holding = []
for file in file_list:
holding, row_in_error = read_file(file, read_line)
geneva_holding = geneva_holding + holding
return geneva_holding
if __name__ == '__main__':
import argparse
parser = argparse.ArgumentParser(
description=
'Match the trustee records to FT buy records to determine the tax lot dates.'
)
parser.add_argument('ft_trade_file')
args = parser.parse_args()
import os, sys
if not os.path.exists(args.ft_trade_file):
print('File does not exist: {0} or {1}'.format(args.trustee_file,
args.ft_trade_file))
sys.exit(1)
trade_records, row_in_error = read_file(args.ft_trade_file,
read_line_trade)
if len(row_in_error) > 0:
print('Some rows in error when reading trade record file.')
portfolios = [
'12229', '12366', '12528', '12548', '12630', '12732', '12733', '12734'
]
unmatched_isin = []
for p in portfolios:
trustee_records, row_in_error = read_file(
'trustee\\{0}.xlsx'.format(p), read_line_trustee)
if len(row_in_error) > 0:
print(
'Some rows in error when reading trustee record {0}'.format(p))
trustee_records, unmatched = create_tax_lot_date(
])
for position in holding:
if is_cash_position(position):
continue
# print(position['InvestID'])
row = [
'CD023', '4',
get_ISIN_from_investID(position['InvestID']), '',
position['Maturity to Last Year End'],
position['Maturity to Last Year End']
]
file_writer.writerow(row)
if __name__ == '__main__':
import logging.config
logging.config.fileConfig('logging.config', disable_existing_loggers=False)
input_file = join(get_current_directory(), 'samples',
'Jones Holding 2017.12.20.xlsx')
jones_holding, row_in_error = read_file(input_file, read_line_jones)
holding = get_holding_from_files()
update_position(holding, jones_holding)
write_upload_csv(holding)
consolidated_holding = consolidate_security(holding)
write_upload_csv_maturity(consolidated_holding)
file_writer.writerow(row)
if __name__ == '__main__':
import argparse, sys, glob, csv
from os.path import join, isdir, exists
import logging.config
logging.config.fileConfig('logging.config', disable_existing_loggers=False)
parser = argparse.ArgumentParser(
description='Create custom amortized cost \
upload file based on Geneva local position \
and trustee file.')
parser.add_argument('--trustee', help='trustee file', required=True)
parser.add_argument('--geneva',
help='geneva local position appraisal file',
required=True)
args = parser.parse_args()
input_file = join(get_input_directory(), args.trustee)
trustee_holding, row_in_error = read_file(input_file,
read_line_trustee,
starting_row=2)
input_file = join(get_input_directory(), args.geneva)
geneva_holding, row_in_error = read_file(input_file, read_line)
update_amortized_cost(geneva_holding, trustee_holding)
write_upload_csv(geneva_holding)
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)
import argparse
parser = argparse.ArgumentParser(
description=
'Match the unmatched position to bond issue records to determine the tax lot dates.'
)
parser.add_argument('unmatched_position')
parser.add_argument('bond_issue')
args = parser.parse_args()
import os, sys
if not os.path.exists(args.unmatched_position) or not os.path.exists(
args.bond_issue):
print('File does not exist: {0} or {1}'.format(args.unmatched_position,
args.bond_issue))
sys.exit(1)
positions, row_in_error = read_file(args.unmatched_position,
read_line_position)
if len(row_in_error) > 0:
print('Some rows in error when reading unmatched record file.')
bond_issues, row_in_error = read_file(args.bond_issue, read_line_bond)
if len(row_in_error) > 0:
print('Some rows in error when reading bond issue file.')
positions, unmatched = create_position_date(positions, bond_issues)
write_csv('unmatched_position_refined.csv', get_record_fields(), positions)
write_csv('unmatched_isin_2.csv', ['isin'], [{
'isin': x
} for x in get_isin_list(unmatched)])
return False
if __name__ == '__main__':
import argparse
parser = argparse.ArgumentParser(
description=
'Match the transfer records to FT transactions file and correct some of their prices.'
)
parser.add_argument('record_file')
parser.add_argument('ft_transfer_file')
args = parser.parse_args()
import os, sys
if not os.path.exists(args.record_file):
print('File does not exist: {0}'.format(args.record_file))
sys.exit(1)
records, row_in_error = read_file(args.record_file, read_line)
if len(row_in_error) > 0:
print(
'some rows in error reading original CSA/CSW/IATSA/IATSW record file.'
)
ft_records, row_in_error = read_file(args.ft_transfer_file, read_line_ft)
if len(row_in_error) > 0:
print('some rows in error for ft transfer file.')
write_csv('refined_records.csv', get_record_fields(),
refine_price(records, ft_records))
if not args.file is None:
file = join(get_input_directory(), args.file)
if not exists(file):
print('{0} does not exist'.format(file))
sys.exit(1)
files = [file]
elif not args.folder is None:
folder = join(get_input_directory(), args.folder)
if not exists(folder) or not isdir(folder):
print('{0} is not a valid directory'.format(folder))
sys.exit(1)
files = glob.glob(folder + '\\*.xls*')
else:
print('Please provide either --file or --folder input')
sys.exit(1)
for input_file in files:
holding, row_in_error = read_file(input_file, read_line)
filename = input_file.split('\\')[-1] # the file name without path
print('read file {0}'.format(filename))
if len(row_in_error) > 0:
print('some rows in error')
write_bond_holding_csv(filter_maturity(holding),
filename.split('.')[0])
