class CLOSUREOPT(SECID, INVTRAN):
optaction = Column(Enum('EXERCISE', 'ASSIGN', 'EXPIRE', name='optaction'))
units = Column(Numeric(), nullable=False)
shperctrct = Column(Integer, nullable=False)
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'), nullable=False)
relfitid = Column(String(length=255))
gain = Column(Numeric())
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(Numeric(), nullable=False)
marginbalance = Column(Numeric(), nullable=False)
shortbalance = Column(Numeric(), nullable=False)
buypower = Column(Numeric())
class INCOME(SECID, ORIGCURRENCY, INVTRAN):
incometype = Column(Enum(*INCOMETYPES, name='incometype'), nullable=False)
total = Column(Numeric(), nullable=False)
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'), nullable=False)
subacctfund = Column(Enum(*INVSUBACCTS, name='subacctfund'),
nullable=False)
taxexempt = Column(OFXBoolean())
withholding = Column(Numeric())
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
class SPLIT(SECID, INVTRAN):
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'), nullable=False)
oldunits = Column(Numeric(), nullable=False)
newunits = Column(Numeric(), nullable=False)
numerator = Column(Numeric(), nullable=False)
denominator = Column(Numeric(), nullable=False)
fraccash = Column(Numeric())
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(Numeric(), nullable=False)
tferaction = Column(Enum('IN', 'OUT', name='tferaction'), nullable=False)
postype = Column(Enum('SHORT', 'LONG', name='postype'), nullable=False)
avgcostbasis = Column(Numeric())
unitprice = Column(Numeric())
dtpurchase = Column(OFXDateTime)
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
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(Numeric(), nullable=False)
correctfitid = Column(Numeric())
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(Numeric(), 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(Numeric()) # '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(Numeric())
loanid = Column(String(length=32))
loanprincipal = Column(Numeric())
loaninterest = Column(Numeric())
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 = units * (unitprice + markup)
+ (commission + fees + load + taxes)
"""), )
class INVBUYSELL(SECID, ORIGCURRENCY):
""" Synthetic base class of INVBUY/INVSELL - not in OFX spec """
units = Column(Numeric(), nullable=False)
unitprice = Column(Numeric(), nullable=False)
commission = Column(Numeric())
taxes = Column(Numeric())
fees = Column(Numeric())
load = Column(Numeric())
total = Column(Numeric(), nullable=False)
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'))
subacctfund = Column(Enum(*INVSUBACCTS, name='subacctfund'))
inv401ksource = Column(Enum(*INV401KSOURCES, name='inv401ksource'))
class DEBTINFO(SECINFO):
parvalue = Column(Numeric(), nullable=False)
debttype = Column(Enum('COUPON', 'ZERO', name='debttype'), nullable=False)
debtclass = Column(
Enum('TREASURY', 'MUNICIPAL', 'CORPORATE', 'OTHER', name='debtclass'))
couponrt = Column(Numeric())
dtcoupon = Column(OFXDateTime)
couponfreq = Column(
Enum('MONTHLY',
'QUARTERLY',
'SEMIANNUAL',
'ANNUAL',
'OTHER',
name='couponfreq'))
callprice = Column(Numeric())
yieldtocall = Column(Numeric())
dtcall = Column(OFXDateTime)
calltype = Column(
Enum('CALL', 'PUT', 'PREFUND', 'MATURITY', name='calltype'))
ytmat = Column(Numeric())
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(Numeric())
withholding = Column(Numeric())
taxexempt = Column(OFXBoolean())
gain = Column(Numeric())
loanid = Column(String(length=32))
statewithholding = Column(Numeric())
penalty = Column(Numeric())
# 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 = units * (unitprice - markdown)
- (commission + fees + load + taxes + penalty
+ withholding + statewithholding)
"""), )
class INVPOS(Inheritor('invpos'), SECID, CURRENCY, Base):
# Added for SQLAlchemy object model
acctfrom_id = Column(
Integer,
ForeignKey('invacctfrom.id', onupdate='CASCADE', ondelete='CASCADE'))
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(Numeric(), nullable=False)
unitprice = Column(Numeric(), nullable=False)
mktval = Column(Numeric(), 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 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(String(length=255), nullable=False)
#ticker = Column(String(length=32))
ticker = Column(String(length=255))
fiid = Column(String(length=32))
rating = Column(String(length=10))
unitprice = Column(Numeric())
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(Numeric(), nullable=False)
class REINVEST(SECID, ORIGCURRENCY, INVTRAN):
incometype = Column(Enum(*INCOMETYPES, name='incometype'), nullable=False)
total = Column(Numeric(), nullable=False)
subacctsec = Column(Enum(*INVSUBACCTS, name='subacctsec'))
units = Column(Numeric(), nullable=False)
unitprice = Column(Numeric(), nullable=False)
commission = Column(Numeric())
taxes = Column(Numeric())
fees = Column(Numeric())
load = Column(Numeric())
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 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(Numeric(), nullable=False)
class AVAILBAL(Balance, Base):
balamt = Column(Numeric(), nullable=False)
class SELLMF(INVSELL, INVTRAN):
selltype = Column(Enum(*SELLTYPES, name='selltype'), nullable=False)
avgcostbasis = Column(Numeric())
relfitid = Column(String(length=255))
class SELLDEBT(INVSELL, INVTRAN):
sellreason = Column(Enum('CALL', 'SELL', 'MATURITY', name='sellreason'),
nullable=False)
accrdint = Column(Numeric())
class RETOFCAP(SECID, ORIGCURRENCY, INVTRAN):
total = Column(Numeric(), 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 JRNLSEC(SECID, INVTRAN):
subacctto = Column(Enum(*INVSUBACCTS, name='subacctto'), nullable=False)
subacctfrom = Column(Enum(*INVSUBACCTS, name='subacctfrom'),
nullable=False)
units = Column(Numeric(), nullable=False)
class MARGININTEREST(ORIGCURRENCY, INVTRAN):
total = Column(Numeric(), nullable=False)
subacctfund = Column(Enum(*INVSUBACCTS, name='subacctfund'),
nullable=False)
class JRNLFUND(INVTRAN):
subacctto = Column(Enum(*INVSUBACCTS, name='subacctto'), nullable=False)
subacctfrom = Column(Enum(*INVSUBACCTS, name='subacctfrom'),
nullable=False)
total = Column(Numeric(), nullable=False)
class OTHERINFO(SECINFO):
typedesc = Column(String(length=32))
assetclass = Column(Enum(*ASSETCLASSES, name='assetclass'))
fiassetclass = Column(String(length=32))
percent = Column(Numeric())
class MFINFO(SECINFO):
mftype = Column(Enum('OPENEND', 'CLOSEEND', 'OTHER', name='mftype'))
yld = Column(Numeric())
dtyieldasof = Column(OFXDateTime)
class BUYDEBT(INVBUY, INVTRAN):
accrdint = Column(Numeric())
class LEDGERBAL(Balance, Base):
balamt = Column(Numeric(), nullable=False)
class POSSTOCK(INVPOS):
unitsstreet = Column(Numeric())
unitsuser = Column(Numeric())
reinvdiv = Column(OFXBoolean())
class CURRENCY(object):
""" Declarative mixin representing OFX <CURRENCY> aggregate """
cursym = Column(Enum(*CURRENCY_CODES, name='cursym'))
currate = Column(Numeric())
