-
Notifications
You must be signed in to change notification settings - Fork 10
/
models.py
656 lines (503 loc) · 20.7 KB
/
models.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
import collections
import functools
import operator
from datetime import datetime, timedelta
import uuid64
from flask_login import UserMixin
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.dialects.postgresql import JSON
from sqlalchemy.exc import IntegrityError, InvalidRequestError
from sqlalchemy.ext.indexable import index_property
from finance.exceptions import (AccountNotFoundException,
AssetNotFoundException,
AssetValueUnavailableException,
InvalidTargetAssetException)
from finance.utils import date_range
from typing import Any # noqa
db = SQLAlchemy()
JsonType = db.String().with_variant(JSON(), 'postgresql')
def get_asset_by_fund_code(code: str):
"""Gets an Asset instance mapped to the given fund code.
:param code: A fund code
"""
# NOTE: I know this looks really stupid, but we'll stick with this
# temporary workaround until we figure out how to create an instance of
# Asset model from a raw query result
# (sqlalchemy.engine.result.RowProxy)
query = "SELECT * FROM asset WHERE data->>'code' = :code LIMIT 1"
raw_asset = db.session.execute(query, {'code': code}).first()
if raw_asset is None:
raise AssetNotFoundException(
'Fund code {} is not mapped to any asset'.format(code))
asset_id = raw_asset[0]
return Asset.query.get(asset_id)
class CRUDMixin(object):
"""Copied from https://realpython.com/blog/python/python-web-applications-with-flask-part-ii/
""" # noqa
__table_args__ = {'extend_existing': True} # type: Any
id = db.Column(db.BigInteger, primary_key=True, autoincrement=False,
default=uuid64.issue())
@classmethod
def create(cls, commit=True, ignore_if_exists=False, **kwargs):
if 'id' not in kwargs:
kwargs.update(dict(id=uuid64.issue()))
instance = cls(**kwargs)
if hasattr(instance, 'created_at') \
and getattr(instance, 'created_at') is None:
instance.created_at = datetime.utcnow()
try:
return instance.save(commit=commit)
except (IntegrityError, InvalidRequestError):
if ignore_if_exists:
db.session.rollback()
return cls.find(**kwargs)
else:
raise
@classmethod
def get(cls, id):
return cls.query.get(id)
# We will also proxy Flask-SqlAlchemy's get_or_404
# for symmetry
@classmethod
def get_or_404(cls, id):
return cls.query.get_or_404(id)
@classmethod
def find(cls, **kwargs):
return cls.query.filter_by(**kwargs).first()
@classmethod
def exists(cls, **kwargs):
row = cls.find(**kwargs)
return row is not None
def update(self, commit=True, **kwargs):
for attr, value in kwargs.iteritems():
setattr(self, attr, value)
return commit and self.save() or self
def save(self, commit=True):
db.session.add(self)
if commit:
db.session.commit()
return self
def delete(self, commit=True):
db.session.delete(self)
return commit and db.session.commit()
def __iter__(self):
for column in self.__table__.columns:
yield column.name, str(getattr(self, column.name))
class User(CRUDMixin, UserMixin, db.Model): # type: ignore
given_name = db.Column(db.String)
family_name = db.Column(db.String)
email = db.Column(db.String, unique=True)
#: Arbitrary data
data = db.Column(JsonType)
accounts = db.relationship('Account', backref='user', lazy='dynamic')
def __repr__(self):
return 'User <{}>'.format(self.name)
@property
def name(self):
# TODO: i18n
return u'{}, {}'.format(self.family_name, self.given_name)
# TODO: Need a way to keep track of the value of volatile assets such as stocks
# TODO: Need a way to convert one asset's value to another (e.g., currency
# conversion, stock evaluation, etc.)
class Granularity(object):
sec = '1sec'
min = '1min'
five_min = '5min'
hour = '1hour'
day = '1day'
week = '1week'
month = '1month'
year = '1year'
@classmethod
def is_valid(cls, value):
return value in (
cls.sec, cls.min, cls.five_min, cls.hour, cls.day, cls.week,
cls.month, cls.year)
class AssetValue(CRUDMixin, db.Model): # type: ignore
"""Represents a unit price of an asset at a particular point of time. The
granularity of the 'particular point of time' may range from one second
to a year. See `Granularity` for more details.
"""
__table_args__ = (db.UniqueConstraint(
'asset_id', 'evaluated_at', 'granularity'), {}) # type: Any
asset_id = db.Column(db.BigInteger, db.ForeignKey('asset.id'))
base_asset_id = db.Column(db.BigInteger, db.ForeignKey('asset.id'))
base_asset = db.relationship(
'Asset', uselist=False, foreign_keys=[base_asset_id])
evaluated_at = db.Column(db.DateTime(timezone=False))
source = db.Column(db.Enum(
'yahoo', 'google', 'kofia', 'test', name='asset_value_source'))
granularity = db.Column(db.Enum(
'1sec', '1min', '5min', '1hour', '1day', '1week', '1month', '1year',
name='granularity'))
# NOTE: Should we also store `fetched_at`?
open = db.Column(db.Numeric(precision=20, scale=4))
high = db.Column(db.Numeric(precision=20, scale=4))
low = db.Column(db.Numeric(precision=20, scale=4))
close = db.Column(db.Numeric(precision=20, scale=4))
volume = db.Column(db.Integer)
def __repr__(self):
return 'AssetValue(evaluated_at={0}, open={1}, high={2}, low={3}, ' \
'close={4}, volume={5})'.format(
self.evaluated_at, self.open, self.high, self.low, self.close,
self.volume)
class AssetType(object):
currency = 'currency'
stock = 'stock'
bond = 'bond'
p2p_bond = 'p2p_bond'
security = 'security' # NOTE: Is this necessary?
fund = 'fund'
commodity = 'commodity'
asset_types = (
AssetType.currency, AssetType.stock, AssetType.bond, AssetType.p2p_bond,
AssetType.security, AssetType.fund, AssetType.commodity)
class Asset(CRUDMixin, db.Model): # type: ignore
"""Represents an asset."""
__mapper_args__ = {
'polymorphic_identity': 'asset',
'polymorphic_on': 'type',
}
type = db.Column(db.Enum(*asset_types, name='asset_type'))
name = db.Column(db.String)
# FIXME: Rename this as `symbol` or rename `get_by_symbol` -> `get_by_code`
code = db.Column(db.String, unique=True)
isin = db.Column(db.String)
description = db.Column(db.Text)
#: Arbitrary data
data = db.Column(JsonType)
asset_values = db.relationship(
'AssetValue', backref='asset', foreign_keys=[AssetValue.asset_id],
lazy='dynamic', cascade='all,delete-orphan')
base_asset_values = db.relationship(
'AssetValue', foreign_keys=[AssetValue.base_asset_id],
lazy='dynamic', cascade='all,delete-orphan')
records = db.relationship('Record', backref='asset',
lazy='dynamic', cascade='all,delete-orphan')
def __repr__(self):
name = self.code if self.code is not None else self.name
return 'Asset <{} ({})>'.format(name, self.description)
@property
def unit_price(self):
raise NotImplementedError
@property
def current_value(self):
raise NotImplementedError
@classmethod
def get_by_symbol(cls, symbol):
"""Gets an asset by symbol (e.g., AMZN, NVDA)
NOTE: We may need to rename this method, when we find a more suitable
name (rather than 'symbol').
"""
asset = cls.query.filter(cls.code == symbol).first()
if asset is None:
raise AssetNotFoundException(symbol)
else:
return asset
@classmethod
def get_by_isin(cls, isin):
"""Gets an asset by ISIN
:param isin: International Securities Identification Numbers
"""
asset = cls.query.filter(cls.isin == isin).first()
if asset is None:
raise AssetNotFoundException(isin)
else:
return asset
class BondAsset(Asset):
__tablename__ = 'asset'
__mapper_args__ = {
'polymorphic_identity': 'bond',
}
class CommodityAsset(Asset):
__tablename__ = 'asset'
__mapper_args__ = {
'polymorphic_identity': 'commodity',
}
class CurrencyAsset(Asset):
__tablename__ = 'asset'
__mapper_args__ = {
'polymorphic_identity': 'currency',
}
class FundAsset(Asset):
__tablename__ = 'asset'
__mapper_args__ = {
'polymorphic_identity': 'fund',
}
class P2PBondAsset(Asset):
__tablename__ = 'asset'
__mapper_args__ = {
'polymorphic_identity': 'p2p_bond',
}
def is_delayed(self):
raise NotImplementedError
def is_defaulted(self):
raise NotImplementedError
def last_payment(self):
raise NotImplementedError
def principle(self):
return self.asset_values \
.order_by(AssetValue.evaluated_at).first().close
def returned_principle(self):
now = datetime.now()
return self.asset_values.filter(AssetValue.evaluated_at <= now) \
.order_by(AssetValue.evaluated_at.desc()).first().close
class SecurityAsset(Asset):
__tablename__ = 'asset'
__mapper_args__ = {
'polymorphic_identity': 'security',
}
class StockAsset(Asset):
__tablename__ = 'asset'
__mapper_args__ = {
'polymorphic_identity': 'stock',
}
bps = index_property('data', 'bps')
eps = index_property('data', 'eps')
class AccountType(object):
checking = 'checking'
savings = 'savings'
investment = 'investment'
credit_card = 'credit card'
virtual = 'virtual'
account_types = (
AccountType.checking, AccountType.savings, AccountType.investment,
AccountType.credit_card, AccountType.virtual)
class Account(CRUDMixin, db.Model): # type: ignore
"""Represents an account. An account may contain multiple records based
on different assets. For example, a single bank account may have a balance
in different foreign currencies."""
__table_args__ = (db.UniqueConstraint(
'institution', 'number'), {}) # type: Any
user_id = db.Column(db.BigInteger, db.ForeignKey('user.id'))
portfolio_id = db.Column(db.BigInteger, db.ForeignKey('portfolio.id'))
type = db.Column(db.Enum(*account_types, name='account_type'))
name = db.Column(db.String)
institution = db.Column(db.String) # Could be a routing number (US)
number = db.Column(db.String) # Account number
description = db.Column(db.Text)
#: Arbitrary data
data = db.Column(JsonType)
# NOTE: Transaction-Account relationship is many-to-many
# transactions = db.relationship('Transaction', backref='account',
# lazy='dynamic')
records = db.relationship('Record', backref='account',
lazy='dynamic')
def __repr__(self):
return 'Account <{} ({})>'.format(self.name, self.type)
@classmethod
def get_by_number(cls, institution: str, number: str):
account = cls.query \
.filter(cls.institution == institution) \
.filter(cls.number == number) \
.first()
if account is None:
raise AccountNotFoundException((institution, number))
else:
return account
def assets(self):
"""Returns all assets under this account."""
raise NotImplementedError
def balance(self, evaluated_at=None):
"""Calculates the account balance on a given date."""
if evaluated_at is None:
evaluated_at = datetime.utcnow()
# FIMXE: Consider open transactions
records = Record.query \
.filter(
Record.account == self,
Record.created_at <= evaluated_at) \
.order_by(
Record.created_at)
# Sum all transactions to produce {asset: sum(quantity)} dictionary
bs = {}
rs = [(r.asset, r.quantity, r.type) for r in records]
for asset, quantity, type_ in rs:
bs.setdefault(asset, 0)
if type_ == RecordType.balance_adjustment:
# Previous records will be ignored when 'balance_adjustment'
# is seen.
bs[asset] = quantity
else:
bs[asset] += quantity
return bs
def net_worth(self, evaluated_at=None, granularity=Granularity.day,
approximation=False, base_asset=None):
"""Calculates the net worth of the account on a particular datetime.
If approximation=True and the asset value record is unavailable for the
given date (evaluated_at), try to pull the most recent AssetValue.
"""
if base_asset is None:
raise InvalidTargetAssetException('Base asset cannot be null')
if evaluated_at is None:
evaluated_at = datetime.utcnow()
evaluated_from, evaluated_until = \
self.get_bounds(evaluated_at, granularity)
net_asset_value = 0
for asset, quantity in self.balance(evaluated_until).items():
if asset == base_asset:
net_asset_value += quantity
continue
asset_value = AssetValue.query \
.filter(AssetValue.asset == asset,
AssetValue.granularity == granularity,
AssetValue.base_asset == base_asset)
if approximation:
asset_value = asset_value \
.filter(AssetValue.evaluated_at <= evaluated_until) \
.order_by(AssetValue.evaluated_at.desc())
else:
asset_value = asset_value \
.filter(AssetValue.evaluated_at >= evaluated_from) \
.filter(AssetValue.evaluated_at <= evaluated_until) \
asset_value = asset_value.first()
if asset_value:
worth = asset_value.close * quantity
else:
raise AssetValueUnavailableException()
net_asset_value += worth
return net_asset_value
# FIXME: We probably want to move this function elsewhere
# FIXME: Think of a better name
@classmethod
def get_bounds(cls, evaluated_at=None, granularity=Granularity.day):
if granularity == Granularity.day:
if isinstance(evaluated_at, datetime):
# Truncate by date
lower_bound = evaluated_at.replace(
hour=0, minute=0, second=0, microsecond=0)
# Fast-forward the time to the end of the day, as
# `evaluated_at` is expected to be inclusive on the upper bound
upper_bound = \
lower_bound + timedelta(days=1) - timedelta(microseconds=1)
return lower_bound, upper_bound
else:
raise NotImplementedError
class Portfolio(CRUDMixin, db.Model): # type: ignore
"""A collection of accounts (= a collection of assets)."""
__table_args__ = (
db.ForeignKeyConstraint(['base_asset_id'], ['asset.id']),
)
name = db.Column(db.String)
description = db.Column(db.String)
accounts = db.relationship('Account', backref='portfolio', lazy='dynamic')
base_asset_id = db.Column(db.BigInteger)
base_asset = db.relationship('Asset', uselist=False,
foreign_keys=[base_asset_id])
def add_accounts(self, *accounts, commit=True):
self.accounts.extend(accounts)
if commit:
db.session.commit()
def assets(self):
"""Returns all assets contained by the accounts under this portfolio.
"""
assets = []
for account in self.accounts:
assets.append(account.assets())
return set(assets)
def balance(self, evaluated_at=None):
"""Calculates the sum of all account balances on a given date."""
if evaluated_at is None:
evaluated_at = datetime.utcnow()
# Balances of all accounts under this portfolio
bs = [account.balance(evaluated_at) for account in self.accounts]
return functools.reduce(operator.add, map(collections.Counter, bs))
def net_worth(self, evaluated_at=None, granularity=Granularity.day):
"""Calculates the net worth of the portfolio on a particular datetime.
"""
net = 0
for account in self.accounts:
net += account.net_worth(evaluated_at, granularity, True,
self.base_asset)
return net
def daily_net_worth(self, date_from, date_to, granularity=Granularity.day):
"""NOTE: This probably shouldn't be here, but we'll leave it here for
demonstration purposes.
"""
# FIXME: Calculate the daily net worth incrementally
for date in date_range(date_from, date_to):
yield date, self.net_worth(date)
def __iter__(self):
merged = super(Portfolio, self).__iter__()
# NOTE: Is there any fancier way to do this?
merged['accounts'] = [dict(a) for a in self.accounts.all()]
merged['net_worth'] = self.net_worth(datetime.utcnow())
return merged
class TransactionState(object):
initiated = 'initiated'
closed = 'closed'
pending = 'pending'
invalid = 'invalid'
transaction_states = (
TransactionState.initiated, TransactionState.closed,
TransactionState.pending, TransactionState.invalid)
class Transaction(CRUDMixin, db.Model): # type: ignore
"""A transaction consists of multiple records."""
initiated_at = db.Column(db.DateTime(timezone=False))
closed_at = db.Column(db.DateTime(timezone=False))
state = db.Column(db.Enum(*transaction_states, name='transaction_state'))
#: Individual record
records = db.relationship('Record', backref='transaction',
lazy='dynamic')
def __init__(self, initiated_at=None, *args, **kwargs):
if initiated_at:
self.initiated_at = initiated_at
else:
self.initiated_at = datetime.utcnow()
self.state = TransactionState.initiated
super(self.__class__, self).__init__(*args, **kwargs)
def __enter__(self):
return self
def __exit__(self, type, value, traceback):
"""Implicitly mark the transaction as closed only if the state is
'initiated'."""
if self.state == TransactionState.initiated:
self.close()
def close(self, closed_at=None, commit=True):
"""Explicitly close a transaction.
:param closed_at: Marks a point at which the transaction is close, but
it serves no functionality of scheduled task.
"""
if closed_at:
self.closed_at = closed_at
else:
self.closed_at = datetime.utcnow()
self.state = TransactionState.closed
if commit:
db.session.commit()
class RecordType(object):
deposit = 'deposit'
withdraw = 'withdraw'
balance_adjustment = 'balance_adjustment'
record_types = (RecordType.deposit, RecordType.withdraw,
RecordType.balance_adjustment)
class Record(CRUDMixin, db.Model): # type: ignore
"""A financial transaction consists of one or more records."""
# NOTE: Is this okay to do this?
__table_args__ = (db.UniqueConstraint(
'account_id', 'asset_id', 'created_at', 'quantity'), {}) # type: Any
account_id = db.Column(db.BigInteger, db.ForeignKey('account.id'))
asset_id = db.Column(db.BigInteger, db.ForeignKey('asset.id'))
# asset = db.relationship(Asset, uselist=False)
transaction_id = db.Column(db.BigInteger, db.ForeignKey('transaction.id'))
type = db.Column(db.Enum(*record_types, name='record_type'))
# NOTE: We'll always use the UTC time
created_at = db.Column(db.DateTime(timezone=False))
category = db.Column(db.String)
quantity = db.Column(db.Numeric(precision=20, scale=4))
def __init__(self, *args, **kwargs):
# Record.type could be 'balance_adjustment'
if 'type' not in kwargs and 'quantity' in kwargs:
if kwargs['quantity'] < 0:
kwargs['type'] = RecordType.withdraw
else:
kwargs['type'] = RecordType.deposit
super(self.__class__, self).__init__(*args, **kwargs)
class DartReport(CRUDMixin, db.Model): # type: ignore
"""NOTE: We need a more generic name for this..."""
registered_at = db.Column(db.DateTime(timezone=False))
title = db.Column(db.String)
entity_id = db.Column(db.Integer)
entity = db.Column(db.String)
reporter = db.Column(db.String)
content = db.Column(db.Text)