class INVBAL(Base):
"""
We deviate from the OFX spec by storing the STMT.dtasof in INVBAL.dtasof
in order to uniquely link the balance with the statement without persisting
an INVSTMT object. We make INVBAL.dtasof mandatory and use it as part of
the primary key.
"""
# Added for SQLAlchemy object model
acctfrom_id = Column(Integer,
ForeignKey('invacctfrom.id',
onupdate='CASCADE',
ondelete='CASCADE'),
primary_key=True)
acctfrom = relationship('INVACCTFROM',
backref=backref(
'invbals',
cascade='all, delete-orphan',
passive_deletes=True,
))
# Extra attribute definitions not from OFX spec
dtasof = Column(OFXDateTime, primary_key=True)
# Elements from OFX spec
availcash = Column(OFXNumeric(), nullable=False)
marginbalance = Column(OFXNumeric(), nullable=False)
shortbalance = Column(OFXNumeric(), nullable=False)
buypower = Column(OFXNumeric())
class INVPOS(Inheritor('invpos'), SECID, CURRENCY, Base):
# Added for SQLAlchemy object model
acctfrom_id = Column(Integer,
ForeignKey('invacctfrom.id',
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
acctfrom = relationship('INVACCTFROM',
backref=backref(
'invposs',
cascade='all, delete-orphan',
passive_deletes=True,
))
dtasof = Column(OFXDateTime)
# Elements from OFX spec
heldinacct = Column(Enum(*INVSUBACCTS, name='heldinacct'), nullable=False)
postype = Column(Enum('SHORT', 'LONG', name='postype'), nullable=False)
units = Column(OFXNumeric(), nullable=False)
unitprice = Column(OFXNumeric(), nullable=False)
mktval = Column(OFXNumeric(), nullable=False)
dtpriceasof = Column(OFXDateTime, nullable=False)
memo = Column(String(length=255))
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
pks = ['acctfrom_id', 'secinfo_id', 'dtasof']
class INCOME(SECID, ORIGCURRENCY, INVTRAN):
incometype = Column(Enum(*INCOMETYPES, name='incometype'), nullable=False)
total = Column(OFXNumeric(), nullable=False)
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'), nullable=False)
subacctfund = Column(Enum(*INVSUBACCTS, name='subacctfund'),
nullable=False)
taxexempt = Column(OFXBoolean())
withholding = Column(OFXNumeric(), CheckConstraint('withholding >= 0'))
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
class SPLIT(SECID, INVTRAN):
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'), nullable=False)
oldunits = Column(OFXNumeric(), nullable=False)
newunits = Column(OFXNumeric(), nullable=False)
numerator = Column(OFXNumeric(), nullable=False)
denominator = Column(OFXNumeric(), nullable=False)
fraccash = Column(OFXNumeric())
subacctfund = Column(Enum(*INVSUBACCTS, name='subacctfund'))
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
class TRANSFER(SECID, INVTRAN):
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'), nullable=False)
units = Column(OFXNumeric(), nullable=False)
tferaction = Column(Enum('IN', 'OUT', name='tferaction'), nullable=False)
postype = Column(Enum('SHORT', 'LONG', name='postype'), nullable=False)
avgcostbasis = Column(OFXNumeric())
unitprice = Column(OFXNumeric())
dtpurchase = Column(OFXDateTime)
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
class CLOSUREOPT(SECID, INVTRAN):
optaction = Column(
Enum('EXERCISE', 'ASSIGN', 'EXPIRE', name='optaction'),
nullable=False,
)
units = Column(OFXNumeric(), nullable=False)
shperctrct = Column(Integer, nullable=False)
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'), nullable=False)
relfitid = Column(String(length=255))
gain = Column(OFXNumeric())
class BANKTRAN(ORIGCURRENCY):
"""
Synthetic mixin for common elements of STMTTRN/INVBANKTRAN - not in OFX spec
"""
# Added for SQLAlchemy object model
@declared_attr
def acctto_id(cls):
return Column(
Integer,
ForeignKey('acctto.id', onupdate='CASCADE', ondelete='CASCADE'))
@declared_attr
def payee_name(cls):
return Column(
String(32),
ForeignKey('payee.name', onupdate='CASCADE', ondelete='CASCADE'))
# Elements from OFX spec
fitid = Column(String(length=255), primary_key=True)
srvrtid = Column(String(length=10))
trntype = Column(Enum('CREDIT',
'DEBIT',
'INT',
'DIV',
'FEE',
'SRVCHG',
'DEP',
'ATM',
'POS',
'XFER',
'CHECK',
'PAYMENT',
'CASH',
'DIRECTDEP',
'DIRECTDEBIT',
'REPEATPMT',
'OTHER',
name='trntype'),
nullable=False)
dtposted = Column(OFXDateTime, nullable=False)
dtuser = Column(OFXDateTime)
dtavail = Column(OFXDateTime)
trnamt = Column(OFXNumeric(), nullable=False)
correctfitid = Column(OFXNumeric())
correctaction = Column(Enum('REPLACE', 'DELETE', name='correctaction'))
checknum = Column(String(length=12))
refnum = Column(String(length=32))
sic = Column(Integer())
payeeid = Column(String(length=12))
name = Column(String(length=32))
memo = Column(String(length=255))
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
class OPTINFO(SECINFO):
opttype = Column(Enum('CALL', 'PUT', name='opttype'), nullable=False)
strikeprice = Column(OFXNumeric(), nullable=False)
dtexpire = Column(OFXDateTime, nullable=False)
shperctrct = Column(Integer, nullable=False)
assetclass = Column(Enum(*ASSETCLASSES, name='assetclass'))
fiassetclass = Column(String(length=32))
class STOCKINFO(SECINFO):
typedesc = Column(String(length=32))
stocktype = Column(
Enum('COMMON', 'PREFERRED', 'CONVERTIBLE', 'OTHER', name='stocktype'))
yld = Column(OFXNumeric()) # 'yield' is a reserved word in Python
dtyieldasof = Column(OFXDateTime)
typedesc = Column(String(length=32))
assetclass = Column(Enum(*ASSETCLASSES, name='assetclass'))
fiassetclass = Column(String(length=32))
class INVBUY(INVBUYSELL):
""" Declarative mixin for OFX INVBUY aggregate """
markup = Column(OFXNumeric(), CheckConstraint('markup >= 0'))
loanid = Column(String(length=32))
loanprincipal = Column(OFXNumeric())
loaninterest = Column(OFXNumeric())
dtpayroll = Column(OFXDateTime)
prioryearcontrib = Column(OFXBoolean())
# Be careful about multiple inheritance. Subclasses of INV{BUY,SELL}
# also inherit from INVTRAN, which also uses __table_args__ to define
# define uniqueness constraint for the natural PKs (acctfrom_id, fitid).
# This is OK because the polymorphic inheritance scheme for INVTRAN
# subclasses only requires the uniqueness constraint on the base table
# (i.e. INVTRAN) which holds these PKs, so INVTRAN subclasses are free
# to clobber __table_args__ by inheriting it from INV{BUY,SELL}...
# ...but be careful.
__table_args__ = (CheckConstraint("""
total = -1 * units * (unitprice + markup)
- (commission + fees + load + taxes)
"""), )
class DEBTINFO(SECINFO):
parvalue = Column(OFXNumeric(), nullable=False)
debttype = Column(Enum('COUPON', 'ZERO', name='debttype'), nullable=False)
debtclass = Column(
Enum('TREASURY', 'MUNICIPAL', 'CORPORATE', 'OTHER', name='debtclass'))
couponrt = Column(OFXNumeric())
dtcoupon = Column(OFXDateTime)
couponfreq = Column(
Enum('MONTHLY',
'QUARTERLY',
'SEMIANNUAL',
'ANNUAL',
'OTHER',
name='couponfreq'))
callprice = Column(OFXNumeric())
yieldtocall = Column(OFXNumeric())
dtcall = Column(OFXDateTime)
calltype = Column(
Enum('CALL', 'PUT', 'PREFUND', 'MATURITY', name='calltype'))
ytmat = Column(OFXNumeric())
dtmat = Column(OFXDateTime)
assetclass = Column(Enum(*ASSETCLASSES, name='assetclass'))
fiassetclass = Column(String(length=32))
class INVSELL(INVBUYSELL):
""" Declarative mixin for OFX INVSELL aggregate """
markdown = Column(OFXNumeric(), CheckConstraint('markdown >= 0'))
withholding = Column(OFXNumeric(), CheckConstraint('withholding >= 0'))
taxexempt = Column(OFXBoolean())
gain = Column(OFXNumeric())
loanid = Column(String(length=32))
statewithholding = Column(OFXNumeric(),
CheckConstraint('statewithholding >= 0'))
penalty = Column(OFXNumeric())
# Be careful about multiple inheritance. Subclasses of INV{BUY,SELL}
# also inherit from INVTRAN, which also uses __table_args__ to define
# define uniqueness constraint for the natural PKs (acctfrom_id, fitid).
# This is OK because the polymorphic inheritance scheme for INVTRAN
# subclasses only requires the uniqueness constraint on the base table
# (i.e. INVTRAN) which holds these PKs, so INVTRAN subclasses are free
# to clobber __table_args__ by inheriting it from INV{BUY,SELL}...
# ...but be careful.
__table_args__ = (CheckConstraint("""
total = -1 * units * (unitprice - markdown)
- (commission + fees + load + taxes + penalty
+ withholding + statewithholding)
"""), )
class SECINFO(Inheritor('secinfo'), CURRENCY, Base):
uniqueidtype = Column(String(length=10), nullable=False)
uniqueid = Column(String(length=32), nullable=False)
# FIs *cough* IBKR *cough* abuse SECNAME/TICKER with too much information
# Relaxing the length constraints from the OFX spec does little harm
#secname = Column(String(length=120), nullable=False)
secname = Column(NagString(length=120), nullable=False)
#ticker = Column(String(length=32))
ticker = Column(NagString(length=32))
fiid = Column(String(length=32))
rating = Column(String(length=10))
unitprice = Column(OFXNumeric())
dtasof = Column(OFXDateTime)
memo = Column(String(length=255))
pks = ['uniqueid', 'uniqueidtype']
class FIPORTION(Base):
# Added for SQLAlchemy object model
mfinfo_id = Column(Integer,
ForeignKey('mfinfo.id',
onupdate='CASCADE',
ondelete='CASCADE'),
primary_key=True)
mfinfo = relationship('MFINFO',
backref=backref(
'fimfassetclasses',
cascade='all, delete-orphan',
passive_deletes=True,
))
# Elements from OFX spec
fiassetclass = Column(Enum(*ASSETCLASSES, name='assetclass'),
primary_key=True)
percent = Column(OFXNumeric(), nullable=False)
class INVBUYSELL(SECID, ORIGCURRENCY):
""" Synthetic base class of INVBUY/INVSELL - not in OFX spec """
units = Column(OFXNumeric(), nullable=False)
unitprice = Column(OFXNumeric(),
CheckConstraint('unitprice >= 0'),
nullable=False)
# Alas, some FIs (*cough* IBKR) violate the OFX spec by booking trade
# reversals that include negative commissions
#commission = Column(OFXNumeric(), CheckConstraint('commission >= 0'))
commission = Column(OFXNumeric(), )
taxes = Column(OFXNumeric(), CheckConstraint('taxes >= 0'))
fees = Column(OFXNumeric(), CheckConstraint('fees >= 0'))
load = Column(OFXNumeric(), CheckConstraint('load >= 0'))
total = Column(OFXNumeric(), nullable=False)
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'), nullable=False)
subacctfund = Column(Enum(*INVSUBACCTS, name='subacctfund'),
nullable=False)
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
class REINVEST(SECID, ORIGCURRENCY, INVTRAN):
incometype = Column(Enum(*INCOMETYPES, name='incometype'), nullable=False)
total = Column(OFXNumeric(), nullable=False)
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'), nullable=False)
units = Column(OFXNumeric(), nullable=False)
unitprice = Column(OFXNumeric(), nullable=False)
commission = Column(OFXNumeric(), CheckConstraint('commission >= 0'))
taxes = Column(OFXNumeric(), CheckConstraint('taxes >= 0'))
fees = Column(OFXNumeric(), CheckConstraint('fees >= 0'))
load = Column(OFXNumeric(), CheckConstraint('load >= 0'))
taxexempt = Column(OFXBoolean())
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
# Be careful about multiple inheritance. REINVEST also inherits from
# INVTRAN, which also uses __table_args__ to define uniqueness constraint
# for the natural PKs (acctfrom_id, fitid). This is OK because the
# polymorphic inheritance scheme for INVTRAN subclasses only requires the
# uniqueness constraint on the base table (i.e. INVTRAN)
# which holds these PKs, so REINVEST is free to clobber __table_args__ ...
# ...but be careful.
__table_args__ = (CheckConstraint("""
total = units * (unitprice) + (commission + fees + load + taxes)
"""), )
class SELLMF(INVSELL, INVTRAN):
selltype = Column(Enum(*SELLTYPES, name='selltype'), nullable=False)
avgcostbasis = Column(OFXNumeric())
relfitid = Column(String(length=255))
class SELLDEBT(INVSELL, INVTRAN):
sellreason = Column(Enum('CALL', 'SELL', 'MATURITY', name='sellreason'),
nullable=False)
accrdint = Column(OFXNumeric())
class RETOFCAP(SECID, ORIGCURRENCY, INVTRAN):
total = Column(OFXNumeric(), nullable=False)
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'), nullable=False)
subacctfund = Column(Enum(*INVSUBACCTS, name='subacctfund'),
nullable=False)
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
class MARGININTEREST(ORIGCURRENCY, INVTRAN):
total = Column(OFXNumeric(), nullable=False)
subacctfund = Column(Enum(*INVSUBACCTS, name='subacctfund'),
nullable=False)
class JRNLSEC(SECID, INVTRAN):
subacctto = Column(Enum(*INVSUBACCTS, name='subacctto'), nullable=False)
subacctfrom = Column(Enum(*INVSUBACCTS, name='subacctfrom'),
nullable=False)
units = Column(OFXNumeric(), nullable=False)
class BAL(Balance, CURRENCY, Base):
name = Column(String(length=32), primary_key=True)
desc = Column(String(length=80), nullable=False)
baltype = Column(Enum('DOLLAR', 'PERCENT', 'NUMBER', name='baltype'),
nullable=False)
value = Column(OFXNumeric(), nullable=False)
class BUYDEBT(INVBUY, INVTRAN):
accrdint = Column(OFXNumeric())
class LEDGERBAL(Balance, Base):
balamt = Column(OFXNumeric(), nullable=False)
class MFINFO(SECINFO):
mftype = Column(Enum('OPENEND', 'CLOSEEND', 'OTHER', name='mftype'))
yld = Column(OFXNumeric())
dtyieldasof = Column(OFXDateTime)
class AVAILBAL(Balance, Base):
balamt = Column(OFXNumeric(), nullable=False)
class JRNLFUND(INVTRAN):
subacctto = Column(Enum(*INVSUBACCTS, name='subacctto'), nullable=False)
subacctfrom = Column(Enum(*INVSUBACCTS, name='subacctfrom'),
nullable=False)
total = Column(OFXNumeric(), nullable=False)
class OTHERINFO(SECINFO):
typedesc = Column(String(length=32))
assetclass = Column(Enum(*ASSETCLASSES, name='assetclass'))
fiassetclass = Column(String(length=32))
percent = Column(OFXNumeric())
class POSSTOCK(INVPOS):
unitsstreet = Column(OFXNumeric())
unitsuser = Column(OFXNumeric())
reinvdiv = Column(OFXBoolean())
class CURRENCY(object):
""" Declarative mixin representing OFX <CURRENCY> aggregate """
cursym = Column(Enum(*CURRENCY_CODES, name='cursym'))
currate = Column(OFXNumeric())