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_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_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 test_read_transaction(self):
filename = join(get_current_directory(), 'samples', 'new_nav_sample2.xls')
wb = open_workbook(filename=filename)
ws = wb.sheet_by_index(0)
port_values = {}
read_transaction(ws, port_values)
transactions = port_values['bond_transactions']
self.assertEqual(len(transactions), 3)
self.verify_transaction1(transactions[0])
self.verify_transaction2(transactions[1])
self.verify_transaction3(transactions[2])
def test_read_bond_section(self):
filename = join(get_current_directory(), 'samples', 'new_nav_sample2.xls')
wb = open_workbook(filename=filename)
ws = wb.sheet_by_index(0)
port_values = {}
port_values['portfolio_id'] = '12229'
row = read_bond_section(ws, 26, 'buy', port_values)
self.assertEqual(row, 41) # row# of the equities section begins
transactions = port_values['bond_transactions']
self.assertEqual(len(transactions), 2)
self.verify_transaction1(transactions[0])
self.verify_transaction2(transactions[1])
def test_read_holding(self):
filename = join(get_current_directory(), 'samples', 'nav_sample1.xls')
wb = open_workbook(filename=filename)
ws = wb.sheet_by_name('Portfolio Val.')
port_values = {}
read_holding(ws, port_values)
bond_holding = port_values['bond']
self.assertEqual(port_values['portfolio_id'], '12229')
self.assertEqual(len(bond_holding), 37)
self.verify_bond_position1(bond_holding[0])
self.verify_bond_position2(bond_holding[16])
self.verify_bond_position3(bond_holding[24])
self.verify_bond_position4(bond_holding[36])
def test_read_section(self):
filename = join(get_current_directory(), 'samples', 'nav_sample1.xls')
wb = open_workbook(filename=filename)
ws = wb.sheet_by_name('Portfolio Val.')
row = 61 # the bond section starts at A62
port_values = {}
currency = 'USD'
fields = [
'par_amount', 'is_listed', 'listed_location', 'fx_on_trade_day',
'coupon_rate', 'coupon_start_date', 'maturity_date',
'average_cost', 'amortized_cost', 'book_cost', 'interest_bought',
'amortized_value', 'accrued_interest', 'amortized_gain_loss',
'fx_gain_loss', 'fund_percentage'
]
row = read_section(ws, row, fields, 'bond', currency, port_values)
self.assertEqual(row, 133) # reading stops at A134
bond_holding = port_values['bond']
self.assertEqual(len(bond_holding), 25)
self.verify_bond_position1(bond_holding[0])
self.verify_bond_position2(bond_holding[16])
self.verify_bond_position3(bond_holding[24])
def test_read_sub_section(self):
filename = join(get_current_directory(), 'samples', 'nav_sample1.xls')
wb = open_workbook(filename=filename)
ws = wb.sheet_by_name('Portfolio Val.')
row = 70 # the bond sub section starts at A71
accounting_treatment = 'HTM'
fields = [
'par_amount', 'is_listed', 'listed_location', 'fx_on_trade_day',
'coupon_rate', 'coupon_start_date', 'maturity_date',
'average_cost', 'amortized_cost', 'book_cost', 'interest_bought',
'amortized_value', 'accrued_interest', 'amortized_gain_loss',
'fx_gain_loss', 'fund_percentage'
]
asset_class = 'bond'
currency = 'USD'
bond_holding = []
row = read_sub_section(ws, row, accounting_treatment, fields,
asset_class, currency, bond_holding)
self.assertEqual(row, 102)
self.assertEqual(len(bond_holding), 17)
self.verify_bond_position1(bond_holding[0])
self.verify_bond_position2(bond_holding[16])
])
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)
# the upload file path.
#
import time, os
from os.path import join
from subprocess import run, TimeoutExpired, CalledProcessError
from trustee.utility import get_current_directory
import logging
logger = logging.getLogger(__name__)
# initialized only once when this module is first imported by others
if not 'config' in globals():
import configparser
config = configparser.ConfigParser()
config.read(join(get_current_directory(), 'sftp.config'))
def upload(file_list):
"""
Call winscp.com to execute the sftp upload job.
"""
winscp_script, winscp_log = create_winscp_files(file_list)
try:
args = [get_winscp_path(), '/script={0}'.format(winscp_script), \
'/log={0}'.format(winscp_log)]
result = run(args, timeout=get_timeout(), check=True)
except TimeoutExpired:
logger.error('upload(): timeout {0} expired'.format(get_timeout()))
except CalledProcessError:
def get_winscp_script_directory():
return join(get_current_directory(), 'winscp_script')
def test_get_portfolio_id(self):
filename = join(get_current_directory(), 'samples', 'new_nav_sample2.xls')
wb = open_workbook(filename=filename)
ws = wb.sheet_by_index(0)
self.assertEqual(get_portfolio_id(ws), ('12229', 6))
def test_get_report_name(self):
filename = join(get_current_directory(), 'samples', 'new_nav_sample2.xls')
wb = open_workbook(filename=filename)
ws = wb.sheet_by_index(0)
self.assertEqual(get_report_name(ws), 'SECURITIES TRANSACTION IMPLEMENTED')
def get_winscp_log_directory():
return join(get_current_directory(), 'winscp_log')
