def __init__(self, batchDate = None):
'''
Constructor
'''
self.productionStartDate = Date(day=25,month=7,year=2013)
self.logFile = settings.BATCH_LOGS + 'batchLog.txt'
self.perfFile = settings.BATCH_LOGS + 'perf.txt'
self.mtmFile = settings.BATCH_LOGS + 'mtm.txt'
self.allocationFile = settings.BATCH_LOGS + 'allocation.txt'
#Specify your email address to get notifications sent to
self.email = 'XXX'
self.marketId = 'EOD'
self.maxBatchDate = Date()
queryResult = Batch.objects.all().aggregate(Max('batchDate'))
self.maxBatchDate.fromPythonDate(queryResult['batchDate__max'])
if batchDate == None:
today = datetime.date.today()
self.batchDate = Date(month=today.month, day=today.day, year=today.year)
else:
self.batchDate = batchDate
#prepare the batch so that we can save it if the batch finishes succesfully
self.batch = Batch()
self.batch.batchDate = self.batchDate
print 'Running batch as of %s' % self.batchDate
def main():
parser = make_parser()
args = parser.parse_args()
if args.setup_db:
create_tables()
return 0
logging.info("Starting database session")
session = create_session()
try:
logging.info("Starting scraping")
ents = scrape()
except Exception:
logging.exception("Scraping failed")
return 1
logging.info("Scraped %s inmates", len(ents))
try:
previous_batch, *_ = session.query(
func.coalesce(func.max(Batch.id), 0)
).one()
current_batch = previous_batch + 1
batch = Batch(id=current_batch)
session.add(batch)
session.flush()
for ent in ents:
inmate = Inmate(
batch_id=batch.id,
booking_id=ent["booking_id"],
name=ent["name"],
race=ent["race"],
sex=ent["sex"],
height=ent["height"],
weight=ent["weight"],
hair=ent["hair"],
eyes=ent["eyes"],
booking_date=ent["date_booked"],
next_court_date=ent["court_date"],
bail=ent["bail"],
)
inmate.charges = [
Charge(**charge, batch_id=batch.id)
for charge in ent["charges"]
]
session.add(inmate)
except Exception:
logging.exception("Persisting data failed")
return 1
session.commit()
logging.info("Persisted scraped data")
return 0
def dataForProductionStart(self):
#Setup initial data for Tim's portfolios as of 7/24/13
productionStartDate = Date(month=7,day=25,year=2013)
if not Batch.objects.filter(batchDate=productionStartDate):
batch = Batch()
batch.batchDate = productionStartDate
batch.save()
if not Location.objects.filter(name='Manhasset').exists():
location = Location()
location.name = 'Manhasset'
location.pricingDate = date(month=7,day=25,year=2013)
location.save()
if not User.objects.filter(username='******').exists():
#Enter email password
User.objects.create_user(username='******',email='*****@*****.**',\
password='******')
cmt = User.objects.get(username='******')
if not UserProfile.objects.filter(user=cmt).exists():
up2 = UserProfile()
up2.user = cmt
up2.location = Location.objects.get(name='Manhasset')
up2.marketId = 'EOD'
up2.save()
#if it exists then make sure Location is Manhasset
else:
profile = UserProfile.objects.get(user=cmt)
profile.location = Location.objects.get(name='Manhasset')
profile.marketId = 'EOD'
profile.save()
#Setup portfolios
portfolioData = (['401K','cmt'],['ChaseIRA','cmt'],
['TDIRA','cmt'],['TDPostTaxIRA','cmt'],
['Just2Trade','cmt'],['TDEmergency','cmt'])
for p in portfolioData:
if not Portfolio.objects.filter(name=p[0],user=p[1]):
portfolio =Portfolio()
portfolio.name = p[0]
portfolio.user = p[1]
portfolio.save()
#Setup Equities and Prices as of productionStartDate
dataSet = (('NEIAX',32.57),('PTTDX',10.78),('EFA',61.06),
('GSG',32.67),('SAN-E',26.7301),('VWO',40.19),
('VNQ', 71.17))
for data in dataSet:
if not Equity.objects.filter(ticker=data[0]).exists():
equity = Equity()
equity.ticker = data[0]
equity.assetType = Enum.AssetType('EQUITYUS')
equity.save()
equity = Equity.objects.get(ticker=data[0])
stockPrice = StockPrice()
stockPrice.equity = equity
stockPrice.pricingDate = productionStartDate
stockPrice.marketId = 'EOD'
stockPrice.mid = data[1]
stockPrice.save()
#Setup Bond identifiers and TCBonds as of productionStartDate
dataSet = (('PortAuth_4.00_JAN42','73358WGG3',Date(month=1,day=15,year=2012),Date(month=1,day=15,year=2042),0.04,),)
for data in dataSet:
if not Identifier.objects.filter(name=data[1],type=BondIdentifierType('CUSIP')):
identifier = Identifier()
identifier.name = data[1]
identifier.type = BondIdentifierType('CUSIP')
identifier.save()
for data in dataSet:
if not TCBond.objects.filter(name=data[0]):
tcBond = TCBond()
tcBond.name = data[0]
tcBond.identifiers = Identifier.objects.get(name=data[1],type=BondIdentifierType('CUSIP'))
tcBond.startDate = data[2].toPythonDate()
tcBond.endDate = data[3].toPythonDate()
tcBond.coupon = data[4]
tcBond.assetType = Enum.AssetType('NYMUNIBOND')
tcBond.save()
#Setup OAS for production start date
if not BondOAS.objects.filter(tCBond=TCBond.objects.get(name='PortAuth_4.00_JAN42'), marketId='EOD',pricingDate=productionStartDate):
bondOAS = BondOAS(tCBond=TCBond.objects.get(name='PortAuth_4.00_JAN42'), marketId='EOD',pricingDate=productionStartDate,mid=0.014)
bondOAS.save()
#Setup Rates
fredLoader = FREDLoader.FREDLoader()
fredLoader.loadLiborCurvesForSpecificDates(marketId='EOD', datesToLoadFor=[productionStartDate])
#Setup transactions
dataSet = (('401K','INIT','EQUITY','NEIAX',8758.407),
('401K','INIT','EQUITY','PTTDX',10746.441),
('ChaseIRA','INIT','EQUITY','EFA',633.37038),
('TDIRA','INIT','EQUITY','EFA',47),
('TDIRA','INIT','EQUITY','GSG',610),
('TDIRA','INIT','EQUITY','VWO',1050),
('TDIRA','INIT','CASH','Cash',11151.23),
('TDPostTaxIRA','INIT','EQUITY','EFA',560),
('TDPostTaxIRA','INIT','EQUITY','VNQ',170),
('TDPostTaxIRA','INIT','CASH','Cash',951.33),
('Just2Trade','INIT','EQUITY','GSG',300),
('Just2Trade','INIT','EQUITY','VWO',430),
('TDEmergency','INIT','BOND','PortAuth_4.00_JAN42',100),
('TDEmergency','INIT','CASH','Cash',201.01))
for data in dataSet:
if not Transaction.objects.filter(portfolio=Portfolio.objects.get(name=data[0]),
transactionType=TransactionType(data[1]),
positionType=PositionType(data[2]),
ticker=data[3],
amount=data[4],
transactionDate = productionStartDate,
effectiveDate = productionStartDate):
transaction = Transaction()
transaction.portfolio =Portfolio.objects.get(name=data[0])
transaction.transactionType = TransactionType(data[1])
transaction.positionType = PositionType(data[2])
transaction.ticker = data[3]
transaction.amount = data[4]
transaction.transactionDate = productionStartDate
transaction.effectiveDate = productionStartDate
transaction.reflectedInPosition = False
transaction.save()
class BatchUtility(object):
'''
Daily Batch that should be executed in windows scheduler
'''
def __init__(self, batchDate = None):
'''
Constructor
'''
self.productionStartDate = Date(day=25,month=7,year=2013)
self.logFile = settings.BATCH_LOGS + 'batchLog.txt'
self.perfFile = settings.BATCH_LOGS + 'perf.txt'
self.mtmFile = settings.BATCH_LOGS + 'mtm.txt'
self.allocationFile = settings.BATCH_LOGS + 'allocation.txt'
#Specify your email address to get notifications sent to
self.email = 'XXX'
self.marketId = 'EOD'
self.maxBatchDate = Date()
queryResult = Batch.objects.all().aggregate(Max('batchDate'))
self.maxBatchDate.fromPythonDate(queryResult['batchDate__max'])
if batchDate == None:
today = datetime.date.today()
self.batchDate = Date(month=today.month, day=today.day, year=today.year)
else:
self.batchDate = batchDate
#prepare the batch so that we can save it if the batch finishes succesfully
self.batch = Batch()
self.batch.batchDate = self.batchDate
print 'Running batch as of %s' % self.batchDate
def run(self):
#Log
if os.path.isfile(self.logFile):
os.remove(self.logFile)
logFileHandle = open(self.logFile,'w')
logFileHandle.write('Running batch as of %s\n' % self.batchDate)
logFileHandle.write('Max batch is %s\n' % self.maxBatchDate)
# if QuantLib.TARGET().isBusinessDay(self.batchDate.ql()) == False:
# logFileHandle.write('Not a good business date based on QuantLib TARGET calendar. Exiting ...')
# print('Not a good business date based on QuantLib TARGET calendar. Exiting ...')
#Update locations with date
logFileHandle.write('\nUpdating all locations except TEST1 with new date\n')
locations = Location.objects.filter(~Q(name='TEST1'))
for location in locations:
logFileHandle.write('Updating location %s\n' % location.name)
location.pricingDate = self.batchDate.toPythonDate()
location.save()
#Load Equity prices
logFileHandle.write('\nLoading equity prices\n')
equities = Equity.objects.all()
equityLoader = EquityPriceLoader()
for equity in equities:
#skip TEST1 and TEST2 as they are only used for testing
if equity.ticker in ['TEST1', 'TEST2']:
continue
if StockPrice.objects.filter(equity=equity, pricingDate=self.batchDate, marketId=self.marketId).exists() == True:
logFileHandle.write('%s price exists\n' % equity.ticker)
continue
else:
logFileHandle.write('Loading prices for equity %s\n' % equity.ticker)
try:
equityLoader.loadCurrentPriceFromYahoo(secId=equity.ticker,
today=self.batchDate,
marketId=self.marketId)
except MarketDataMissing:
logFileHandle.write('No market data loaded for %s as of %s\n' % (equity.ticker, self.batchDate))
logFileHandle.write('As default loading price from last batch date\n')
lastPrice = StockPrice.objects.get(equity=equity, pricingDate=self.maxBatchDate, marketId=self.marketId)
lastPrice.pricingDate = self.batchDate
lastPrice.pk = None
lastPrice.save()
#Load Interest Rates
logFileHandle.write('\nLoading Interest Rates\n')
if self.maxBatchDate != self.batchDate:
logFileHandle.write('For now just copy rates from max batch date to current batch date\n')
curve = InterestRateCurve.objects.get(ccy=Currency('USD'), index=Index('LIBOR'),
term=TimePeriod('M'), numTerms=3,
pricingDate=self.maxBatchDate,
marketId='EOD')
curve.loadRates()
newCurve = InterestRateCurve(ccy=Currency('USD'), index=Index('LIBOR'),
term=TimePeriod('M'), numTerms=3,
pricingDate=self.batchDate,
marketId='EOD')
for rate in curve.getRates():
newRate = InterestRate(type=rate.type, term=rate.term, numTerms=rate.numTerms, mid=rate.mid, curve=newCurve)
logFileHandle.write('Rate: %s/%d/%0.2f\n' % (rate.term, rate.numTerms, rate.mid*100))
newCurve.addRate(newRate)
newCurve.save()
#Copy OAS forward and keep constant
logFileHandle.write('\nCopying OAS from max batch date to batch date\n')
if self.maxBatchDate != self.batchDate:
bondOAS = BondOAS.objects.get(tCBond=TCBond.objects.get(name='PortAuth_4.00_JAN42'),
marketId='EOD', pricingDate=self.maxBatchDate)
bondOAS.pk = None
bondOAS.pricingDate = self.batchDate
bondOAS.save()
#Process Positions
logFileHandle.write('\nProcessing positions\n')
#load all positions with the max date
#if the batchDate greater than maxbatchdate then copy position to batchdate (roll position forward)
if self.maxBatchDate < self.batchDate:
positions = ModelPosition.objects.filter(asOf=self.maxBatchDate)
for position in positions:
logFileHandle.write('Copying position %s from max batch date to batch date\n' % position)
position.pk = None
position.asOf = self.batchDate
position.save()
#Update Positions based on Transactions
logFileHandle.write('\nUpdating of positions based on transaction\n')
transactions = Transaction.objects.filter(reflectedInPosition=False)
if len(transactions) == 0:
logFileHandle.write('No transactions to process\n')
else:
for transaction in transactions:
logFileHandle.write('Processing transaction %s\n' % str(transaction))
positions = transaction.relatedPositions()
for position in positions:
logFileHandle.write('Processing position %s\n' % str(transaction))
transaction.updatePositionAmount(position=position)
position.save()
transaction.reflectedInPosition = 'True'
transaction.save()
#Performance
logFileHandle.write('\nRunning Performance Report\n')
if os.path.isfile(self.perfFile):
os.remove(self.perfFile)
perfFileHandle = open(self.perfFile,'w')
portfolios = Portfolio.objects.filter(user='******')
startValue = 0
endValue = 0
for portfolio in portfolios:
performanceCalculator = PerformanceCalculator(start=self.productionStartDate,
end=self.batchDate,
portfolio=portfolio,
marketId=self.marketId)
perfFileHandle.write(performanceCalculator.report()+'\n')
startValue += performanceCalculator.startValue
endValue += performanceCalculator.endValue
endValue += performanceCalculator.transactionValue
if startValue == 0 or (self.batchDate.ql() - self.productionStartDate.ql()) == 0:
overallPerformance = 0
else:
overallPerformance = ((endValue - startValue) / startValue) * 365.0 / (self.batchDate.ql() - self.productionStartDate.ql())
perfFileHandle.write('Overall Period Performance = %.2f%%\n' % (overallPerformance*100.0/365.0*(self.batchDate.ql() - self.productionStartDate.ql())))
perfFileHandle.write('Overall Annual Performance = %.2f%%' % (overallPerformance*100.0))
perfFileHandle.close()
#MTM and Positions
logFileHandle.write('\nRunning MTM and Positions Report\n')
totalValue = 0
if os.path.isfile(self.mtmFile):
os.remove(self.mtmFile)
mtmFileHandle = open(self.mtmFile,'w')
portfolios =Portfolio.objects.filter(user='******')
mtmFileHandle.write('MTM Report as of %s\n' % self.batchDate)
for portfolio in portfolios:
mtmFileHandle.write('Portfolio=%s\n' % portfolio.name)
modelPositions = ModelPosition.objects.filter(portfolio=portfolio, asOf=self.batchDate)
for modelPosition in modelPositions:
position = CreatePosition(modelPosition=modelPosition)
portfolio.addPosition(position)
marketDataContainer = MarketDataContainer()
for position in portfolio.positions:
marketDataContainer.add(position.marketData(pricingDate=self.batchDate, marketId=self.marketId))
for position in portfolio.positions:
position.marketDataContainer = marketDataContainer
for position in portfolio.positions:
positionValue = position.NPV(pricingDate=self.batchDate,marketId=self.marketId)
mtmFileHandle.write('ModelPosition=%s with %.0f shares, asset type %s and value $%s\n' % (position.secId,
position.amount,
position.getAssetType(),
fToDC(positionValue)))
portfolioValue = portfolio.NPV(pricingDate=self.batchDate,marketId=self.marketId)
mtmFileHandle.write('Portfolio value=$%s\n\n' % fToDC(portfolioValue))
totalValue += portfolioValue
mtmFileHandle.write('Total value=%s\n\n' % fToDC(totalValue))
#Asset Allocation
#allocations is associative array with value
allocations = {}
totalValue = 0
for item in AssetType.choices:
allocations[item[0]] = 0
logFileHandle.write('\nRunning Allocation Report\n\n')
if os.path.isfile(self.allocationFile):
os.remove(self.allocationFile)
allocationFileHandle = open(self.allocationFile,'w')
portfolios =Portfolio.objects.filter(user='******')
allocationFileHandle.write('Allocation Report as of %s\n\n' % self.batchDate)
for portfolio in portfolios:
if portfolio.name == 'TradeAug2013':
continue
modelPositions = ModelPosition.objects.filter(portfolio=portfolio, asOf=self.batchDate)
for modelPosition in modelPositions:
position = CreatePosition(modelPosition)
portfolio.addPosition(position)
marketDataContainer = MarketDataContainer()
for position in portfolio.positions:
marketDataContainer.add(position.marketData(pricingDate=self.batchDate, marketId=self.marketId))
for position in portfolio.positions:
position.marketDataContainer = marketDataContainer
for position in portfolio.positions:
npv = position.NPV(pricingDate=self.batchDate, marketId=self.marketId)
allocations[str(position.getAssetType())] += npv
totalValue += npv
orderedAllocations = OrderedDict(sorted(allocations.items(),key=lambda t: t[1],reverse=True))
for key in orderedAllocations.keys():
try:
allocationPercent = Allocation.objects.get(assetType=AssetType(key)).percent
except:
allocationPercent = 0.0
try:
actualAllocation = 100*orderedAllocations[key]/totalValue
except:
actualAllocation = 0.0
allocationFileHandle.write('%s=%.0f%%\t\t\t\t\t\twith target %.0f%%\t and value $%s\n' % (key,
actualAllocation,
100*allocationPercent,
fToD(orderedAllocations[key])))
logFileHandle.write('Batch completed\n')
logFileHandle.close()
#Every time I run the batch and it succeeds we need to save the batch info
self.batch.save()
def sendEmail(self):
print "Sending email"
mtmFileHandle = open(self.mtmFile,'r')
send_mail('CMT MTM and Positions as of %s' % self.batchDate, mtmFileHandle.read(), self.email,[self.email], fail_silently=False)
mtmFileHandle.close()
perfFileHandle = open(self.perfFile,'r')
send_mail('CMT Performance as of %s' % self.batchDate, perfFileHandle.read(), self.email,[self.email], fail_silently=False)
perfFileHandle.close()
allocationFileHandle = open(self.allocationFile,'r')
send_mail('CMT Allocations as of %s' % self.batchDate, allocationFileHandle.read(), self.email,[self.email], fail_silently=False)
allocationFileHandle.close()
logFileHandle = open(self.logFile,'r')
send_mail('CMT Log as of %s' % self.batchDate, logFileHandle.read(), self.email,[self.email], fail_silently=False)
logFileHandle.close()
