class Test(unittest.TestCase):
def setUp(self):
test_db_path = 'test-kew-pe.db'
if os.path.isfile(test_db_path):
os.remove(test_db_path)
self.control = Control(db_url="sqlite:///" + test_db_path,echo=False)
def tearDown(self):
self.control._dispose_()
def testName(self):
date1 = datetime.date(2009, 5, 23)
date2 = datetime.date(2010, 5, 23)
date3 = datetime.date(2011, 5, 23)
date4 = datetime.date(2013, 5, 23)
date5 = datetime.date(2014, 5, 23)
with self.control._session_context as session:
deal = model.Deal()
session.add(deal)
deal.cashflows.append(model.Cashflow(amount=Decimal(15), settlement_date = date1))
deal.cashflows.append(model.Cashflow(amount=Decimal(15), settlement_date = date1))
deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date1))
deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date2))
deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date4))
deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal(25), settlement_date = date4))
deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal(75), settlement_date = date5))
deal.holdings.append(model.Holding(current_value=Decimal(10)))
deal.holdings.append(model.Holding(current_value=Decimal(-5)))
session.commit()
s = session.query(func.sum(model.Cashflow.amount), func.avg(model.Cashflow.amount))
s = s.filter(model.Cashflow.amount == Decimal(15))
print s
#s.filter(model.Cashflow.amount == Decimal(15))
#print s
r = session.execute(s)
id = deal.id
cash_flows = []
#for row in r:
#print row
back = session.query(model.Cashflow.settlement_date.label("settlement_date"),model.Cashflow.amount.label("amount")).\
filter(model.Cashflow.deal_id==deal.id)
forward = session.query(model.ForwardCashflow.settlement_date.label("settlement_date"), model.ForwardCashflow.amount.label("amount")).\
filter(model.ForwardCashflow.deal_id==deal.id)
cash = back.union_all(forward).order_by(literal_column("settlement_date").asc(), literal_column("amount").asc())
for c in cash:
print c
cash_flows.append(c)
irr = IRRCalculator(Decimal('0.10'), cash_flows)
irr2 = IRRCalculator(Decimal('0.10'), [])
#print irr.theIRR
#print irr2.theIRR
mm = MMCalculator(cash_flows)
print mm.theMM
print mm.errors
class Test(unittest.TestCase):
def setUp(self):
test_db_path = 'shop.db'
if os.path.isfile(test_db_path):
os.remove(test_db_path)
test_db_path = '../shop.db'
db_url = "sqlite:///" + test_db_path
# db_url = 'mysql://*****:*****@localhost/kew_pe?charset=utf8'
self.control = Control(db_url=db_url,echo=True, drop_all=True)
def tearDown(self):
self.control._dispose_()
def testName(self):
with self.control._session_context as session:
deal = model.Deal()
deal2 = model.Deal()
session.add_all([deal,deal2])
deal.cashflows.append(model.Cashflow(amount=Decimal(10)))
deal.cashflows.append(model.Cashflow(amount=Decimal("10.1333")))
deal.cashflows.append(model.Cashflow(amount=Decimal(10)))
deal.cashflows.append(model.Cashflow(amount=Decimal(10)))
deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal(-5)))
deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal("-5")))
deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal(-5)))
deal.holdings.append(model.Holding(current_value=Decimal(10),pnl=30,fx_pnl=300))
deal.holdings.append(model.Holding(current_value=Decimal(-5),current_invested_capital=Decimal(1000000)))
deal2.cashflows.append(model.Cashflow(amount=Decimal(10)))
deal2.cashflows.append(model.Cashflow(amount=Decimal(10)))
deal2.forward_cashflows.append(model.ForwardCashflow(amount=-Decimal(5)))
deal2.holdings.append(model.Holding(current_value=Decimal(10),pnl=30,fx_pnl=2))
session.commit()
self.assertEquals(deal.current_value, Decimal(5))
self.assertEquals(deal.current_invested_capital, Decimal(1000000))
self.assertEquals(deal.current_pnl, Decimal(30))
self.assertEquals(deal.current_fx_pnl, Decimal(300))
self.assertEquals(deal.cash_total + deal.forward_total, Decimal("25.1333"))
self.assertEquals(deal2.cash_total + deal2.forward_total, Decimal(15))
self.assertEquals(deal.cash_pnl, Decimal("25.1333"))
self.assertEquals(deal2.cash_pnl, Decimal(15))
def _testName(self):
with self.control._session_context as session:
deal = session.query(model.Deal).get(1)
# print deal.current_value
print deal.cash_pnl
def migrate_surrogate(session, surrogate, crm_path, path_sep="/"):
logging.debug('reading files for %s:%s from %s' % (surrogate.get("_type"),surrogate.get("label"), crm_path) )
file_count = 0
for info in Control._walk_path_(crm_path, path_sep):
surrogate_path = info.get("surrogate_path")
source_path = info.get("source_path")
fileinfo = info.get("file_info")
try:
db_file = model.File(**fileinfo)
db_file.pending = False
session.add(db_file)
from genoa.core.model import JsonMixin
db_object = JsonMixin.from_surrogate(session, surrogate)
if surrogate.get("_type") == "Deal":
db_rel = model.F2D(deal=db_object, surrogate_path=surrogate_path)
db_file.deals.append(db_rel)
elif surrogate.get("_type") == "Entity":
db_rel = model.F2E(entity=db_object, surrogate_path=surrogate_path)
db_file.entities.append(db_rel)
elif surrogate.get("_type") == "Person":
db_rel = model.F2P(person=db_object, surrogate_path=surrogate_path)
db_file.contacts.append(db_rel)
else:
db_file.pending = True
logging.error("Unsupported file relationship to %s " % surrogate.get("_type"))
session.flush()
file_count += 1
except Exception, e:
logging.exception("Error importing file db_file.name: %s" % e)
finally:
def setUp(self):
test_db_path = 'shop.db'
if os.path.isfile(test_db_path):
os.remove(test_db_path)
test_db_path = '../shop.db'
db_url = "sqlite:///" + test_db_path
# db_url = 'mysql://*****:*****@localhost/kew_pe?charset=utf8'
self.control = Control(db_url=db_url,echo=True, drop_all=True)
def setUp(self):
test_db_path = 'deal_test.db'
if os.path.isfile(test_db_path):
os.remove(test_db_path)
test_db_path = '../deal_test.db'
# db_url = "sqlite:///" + test_db_path
db_url = 'mysql://*****:*****@localhost/kew_pe?charset=utf8'
self.control = Control(file_archive='',db_url=db_url)
class Test(unittest.TestCase):
def setUp(self):
db_url = 'mysql://*****:*****@localhost/kew_pe?charset=utf8'
self.control = Control(file_archive="", db_url=db_url)
def tearDown(self):
self.control._dispose_()
def testPowerPointFeed(self):
with self.control._session_context as session:
deal_name="12 Eaton Place"
result = self.control.update_structure_chart_image(deal_name)
print result
class Test(unittest.TestCase):
def setUp(self):
db_url = 'mysql://*****:*****@localhost/kew_pe?charset=utf8'
self.control = Control(file_archive='',db_url=db_url)
def tearDown(self):
self.control._dispose_()
def testSave(self):
with self.control._session_context as session:
deal = session.query(model.Deal).filter(model.Deal.name=="12 Eaton Place").first()
if deal:
data = deal.to_json()
collections = []
self.control.genoa_save('Deal', data, collections, surrogates=[])
class Test(unittest.TestCase):
def setUp(self):
test_db_path = 'deal_test.db'
if os.path.isfile(test_db_path):
os.remove(test_db_path)
test_db_path = '../deal_test.db'
# db_url = "sqlite:///" + test_db_path
db_url = 'mysql://*****:*****@localhost/kew_pe?charset=utf8'
self.control = Control(file_archive='',db_url=db_url)
def tearDown(self):
self.control._dispose_()
def testHoldings(self):
with self.control._session_context as session:
entity = session.query(model.Entity).filter(model.Entity.name=='').first()
if entity:
for i, h in enumerate(entity.key_contacts):
print i, h.id, h.name
def setUp(self):
test_db_path = 'test-kew-pe.db'
if os.path.isfile(test_db_path):
os.remove(test_db_path)
self.control = Control(db_url="sqlite:///" + test_db_path,echo=False)
def run(self):
self.tmpl = resource_filename("kew.pe.utils","cashbalances.html")
args = {
'cols' : ['deal', 'entity', 'bank', 'account_type', 'account', 'iban', 'currency', 'balance', 'collateral', 'fx', 'bal_usd', 'subtotal', 'total', 'balance_date', 'age'],
'headers' : {
'deal' : {'name' : 'Deal',
'class':'text',
'_fmt' : fmtdata1},
'entity' : {'name' : 'Entity',
'class':'text',
'_fmt' : fmtdata1},
'bank' : {'name' : 'Bank',
'class':'text',
'_fmt' : fmtdata1},
'account' : {'name' : 'Account#',
'class':'text',
'_fmt' : fmtdata1},
'account_type' : {'name' : 'Account Type',
'class':'text',
'_fmt' : fmtdata1},
'iban' : {'name' : 'IBAN',
'class':'text',
'_fmt' : fmtdata1},
'currency' : {'name' : 'CCY',
'class':'text',
'_fmt' : fmtdata1},
'collateral' : {'name' : 'Collateral',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0)},
'balance' : {'name' : 'Balance',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0)},
'fx' : {'name' : 'FX Rate',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 4)},
'bal_usd' : {'name' : 'USD Equiv. Total',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0, '$')},
'subtotal' : {'name' : 'Sub Total',
'class':'number',
'_fmt' : lambda a : fmtdata1(a, 0, '$')},
'total' : {'name' : 'Total',
'class':'number',
'_fmt' : lambda a : fmtdata1(a, 0, '$')},
'balance_date' : {'name' : 'Balance Date',
'class':'text',
'_fmt' : fmtdata1},
'age' : {'name' : 'Aged',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0)},
},
'asof' : self._asof.strftime('%A, %d %B %Y') ,
'rows' : [],
}
control = Control(file_archive=None,db_url=self._dburl)
args['rows'] = control.fetch_cash_report_data()
self._gen_report_(args)
# Now convert it to a pdf file
pdf_output = self.outputfile.replace('.html', '.pdf')
pdfargs = ['--username','corp\\reportingdb', '--password','&AdRekAd']
htmltopdf([os.path.abspath(self.outputfile)], os.path.abspath(pdf_output), orientation='landscape', pdfargs=pdfargs)
def run(self):
self.tmpl = resource_filename("kew.pe.utils","exceptions.html")
args = {
'cols' : ['deal', 'entity', 'bank', 'account_type', 'account', 'iban', 'currency', 'balance', 'collateral', 'fx', 'bal_usd', 'subtotal', 'total', 'balance_date', 'age'],
'headers' : {
'deal' : {'name' : 'Deal',
'class':'text',
'_fmt' : fmtdata1},
'entity' : {'name' : 'Entity',
'class':'text',
'_fmt' : fmtdata1},
'bank' : {'name' : 'Bank',
'class':'text',
'_fmt' : fmtdata1},
'account' : {'name' : 'Account#',
'class':'text',
'_fmt' : fmtdata1},
'account_type' : {'name' : 'Account Type',
'class':'text',
'_fmt' : fmtdata1},
'iban' : {'name' : 'IBAN',
'class':'text',
'_fmt' : fmtdata1},
'currency' : {'name' : 'CCY',
'class':'text',
'_fmt' : fmtdata1},
'balance' : {'name' : 'Balance',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0)},
'collateral' : {'name' : 'Collateral',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0)},
'fx' : {'name' : 'FX Rate',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 4)},
'bal_usd' : {'name' : 'USD Equiv.',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0, '$')},
'subtotal' : {'name' : 'Sub Total',
'class':'number',
'_fmt' : lambda a : fmtdata1(a, 0, '$')},
'total' : {'name' : 'Total',
'class':'number',
'_fmt' : lambda a : fmtdata1(a, 0, '$')},
'balance_date' : {'name' : 'Balance Date',
'class':'text',
'_fmt' : fmtdata1},
'age' : {'name' : 'Aged',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0)},
},
'asof' : self._asof.strftime('%A, %d %B %Y') ,
'acc_info': []
}
# Note - CRM url is hard coded!
control = Control(file_archive=None, db_url=self._dburl)
aged, no_data, dropped, age_limit = control.fetch_cash_exception_data()
args['acc_info'] = ((aged, "Aged Accounts (Balances older than %s days)" % age_limit, "No old balances to report"),
(no_data, "Accounts that have never had a balance", "All accounts have a balance."),
(dropped, "Automatic accounts not loading automatically (Balance >1 day old)", "No old balances to report")
)
self._gen_report_(args)
# Now convert it to a pdf file
pdf_output = self.outputfile.replace('.html', '.pdf')
pdfargs = ['--username','corp\\reportingdb', '--password','&AdRekAd']
htmltopdf([os.path.abspath(self.outputfile)], os.path.abspath(pdf_output), orientation='landscape', pdfargs=pdfargs)
def setUp(self):
db_url = 'mysql://*****:*****@localhost/kew_pe?charset=utf8'
self.control = Control(file_archive='',db_url=db_url)
"""
Author: Scott Jorgensen
Date: 29th February 2012
Purpose: Tests the loading of the CS Call account into the dev DB
"""
import os
import sys
sys.path.append(r"c:\src\trunk\src")
import datetime
from kew.pe.control import Control
from pkg_resources import resource_filename
if __name__ == "__main__":
dburl = "mysql://*****:*****@localhost/kew_pe?charset=utf8"
date = datetime.datetime.now().date()
dt = date.strftime("%Y%m%d")
base = r"S:\Kew Shared\Operations\Vollin Position & Cash Rec\Cash And Position Files"
sql = "select account.id as acc_id, account.account_category as deal_name, deal.name as deal, entity.name as company,account.bank as bank,account.acc_number,account.acc_type,account.margin,account.iban, account.currency, ab3.amount as amount, ab3.dt as bal_date, account.note, account.collateral, account.automatic from account inner join (select account_balance.amount, account_balance.account_id, account_balance.as_of_date as dt from account_balance inner join (select max(account_balance.as_of_date) as max_date, account_balance.account_id from account_balance group by account_balance.account_id) ab1 on account_balance.as_of_date = ab1.max_date and account_balance.account_id = ab1.account_id) ab3 on account.id = ab3.account_id inner join entity on account.entity_id = entity.id left join d2_e on account.entity_id = d2_e.entity_id left join deal on d2_e.deal_id = deal.id where account.live = 1"
c = Control(file_archive=None, db_url=dburl)
# c.fetch_report_data()
c.fetch_exception_data()
class Test(unittest.TestCase):
def setUp(self):
test_db_path = 'deal_test.db'
if os.path.isfile(test_db_path):
os.remove(test_db_path)
test_db_path = '../deal_test.db'
# db_url = "sqlite:///" + test_db_path
db_url = 'mysql://*****:*****@localhost/kew_pe?charset=utf8'
self.control = Control(file_archive='',db_url=db_url, echo=False)
def tearDown(self):
self.control._dispose_()
def _testHoldings(self):
with self.control._session_context as session:
deal = session.query(model.Deal).get(1)
if deal:
for i, h in enumerate(deal.holdings):
print i, h.id, h.name, h.deal_id
def _testEntity(self):
with self.control._session_context as session:
obj = session.query(model.Entity).get(99)
if obj:
for i, o in enumerate(obj.accounts):
print i, o
def _testVersionInc(self):
with self.control._session_context as session:
deals = [34]
version_date = datetime.date.today()
self.control.roll_version(deals, version_date=version_date, version_label="New Report")
def testColProps(self):
with self.control._session_context as session:
print "*"*20, datetime.datetime.now()
deal_27 = session.query(model.Deal).get(27)
deal_34 = session.query(model.Deal).get(34)
print deal_27.version_id, deal_34.version_id
print deal_27.current_value, deal_34.current_value
print deal_27.current_invested_capital, deal_34.current_invested_capital
print deal_27.current_pnl, deal_34.current_pnl
print deal_27.cash_pnl, deal_34.cash_pnl
#print '*'*20, deal.current_invested_capital
#json = deal.to_json()
#print json
print "*"*20, datetime.datetime.now()
print "genoa_item"
#list = self.control.genoa_list(model='Cashflow', criteria={u'deal_id': 27, u'version_id': 27}, offset=None, limit=None, summary=None, order_by=None)
data = self.control.genoa_item(model='Deal', pk=27, collections=['cashflows'], surrogates=[], version_id=None)
print "*"*20, datetime.datetime.now()
print "genoa_save"
self.control.genoa_save('Deal', data, collections=[['cashflows', 'Cashflow', 'ONETOMANY']], surrogates=[])
print "*"*20, datetime.datetime.now()
print "genoa_list"
self.control.genoa_list('Cashflow', criteria={'deal_id':27, 'version_id':59})
print "*"*20, datetime.datetime.now()
