def getKey(cls, api, keyonly=True):
ModelBase.connect(new_session=True)
session = ModelBase.session
q = session.query(ApiKey).filter_by(api=api).one_or_none()
if keyonly:
return q.key if q else None
return q
def getChartUserData(self):
if self.chartInitialized is False:
self.chartInitialized = True
return
ModelBase.connect(new_session=True)
self.query = ModelBase.session.query(TradeSum).order_by(
TradeSum.date.asc(), TradeSum.start.asc())
self.runFilters()
# self.query = self.query.all()
# self.query = self.query.limit(self.limit)
accounts = self.cud['accounts'] if self.cud['accounts'] else 'All'
if self.cud['inNumSets'] > 0:
pnls, dates = self.getProfitInNumGroups(self.query.all(),
self.cud['inNumSets'])
self.title = f'Trades in groups of {self.cud["inNumSets"]} trades in {accounts} accounts'
elif self.cud['inTimeGroups'] is not None:
pnls, dates = self.groupByTime(self.query.all(),
self.cud['inTimeGroups'])
self.title = f'Trades: {self.cud["titleBit"]} in {accounts} accounts'
self.data = pnls
self.getFormatGraphArray()
if self.cud['inNumSets'] >= 1:
self.labels = [x.strftime("%m/%d/%y %H:%M") for x in dates]
else:
self.labels = [x.strftime("%b %d, %Y") for x in dates]
assert len(self.labels) == len(self.neg)
def test_figureBAPL(self):
'''
figureBAPL is called by openIBStatement and is give the trade tables and
position tables from an IB statement. figureBAPL fills in Balance, Average
and pnl. Every Balance entry should be filled. There could be blanks for
Average. Here we can test all balance entries made it to the db
'''
ibs = IbStatement()
ibdb = StatementDB()
ibdb.reinitializeTradeTables()
# These two files are a multiday flex and an activity.
# Find a way to find recent files to test
# trades1, meta1 = ibs.openIBStatement(self.testfile1)
trades2, meta2 = ibs.openIBStatement(self.testfile2)
len2 = len(trades2['TRNT']) if 'TRNT' in trades2.keys() else len(
trades2['Trades'])
ModelBase.connect(new_session=True)
session = ModelBase.session
q = session.query(Trade).all()
q2 = session.query(Trade).filter(Trade.balance is None).all()
self.assertEqual(len2, len(q))
self.assertEqual(len(q2), 0)
bu = Backup()
bu.restore()
def getTradesBySumId(self, tsid):
ModelBase.connect(new_session=True)
session = ModelBase.session
q = session.query(
Trade, TradeSum).filter(Trade.trade_sum_id == TradeSum.id).filter(
TradeSum.id == tsid).with_entities(Trade.id).all()
return q
def getDuplicateTradesnew(self, account):
'''
Cold not figure the ORM equiivalent of a bunch of stuff here. Going to get
a more basic select/join and then process in python
'''
import math
ModelBase.connect(new_session=True)
session = ModelBase.session
t1 = aliased(Trade)
t2 = aliased(Trade)
pairs = session.query(t1, t2).filter(
and_(
t1.symb == t2.symb,
# (((t1.price - t2.price) < .00001) or ((t2.price - t1.price) < .00001)),
# math.isclose(t1.price, t2.price, abs_tol=.00001),
t1.datetime < t2.datetime,
t1.account == account,
t2.account == account,
t1.id != t2.id,
# (t2.das is None) and (t1.ib == "IB")
# (t2.das is None or t2.das == "") and (t1.ib is None or t1.ib == "")
)).all()
# q = session.query(Trade).join(t2).filter( and_(
# substr(Trade.datetime, 1, 8) == substr(t2.datetime, 1, 8),
# ((Trade.das is Null or Trade.das == "") and (t2.ib is Null or t2.ib == "")) or (
# (Trade.ib is Null or Trade.ib == "") and (t2.das is Null and t2.das == ""))
# )).order_by(Trade.datetime).all()
print(q)
print()
def insertTrade(self, row, source, new_session=False):
'''
:params row: pd.Series
:params oc: The Open/Close code
:params source: Currently DAS or IB
'''
if new_session:
ModelBase.connect(new_session=True)
session = ModelBase.session
das, ib = ('DAS', None) if source == 'DAS' else (None, 'IB')
oc = row['OC'] if 'OC' in row.keys() else ''
trade = Trade(
symb=row['Symb'],
datetime=row['DateTime'],
qty=row['Qty'],
balance=row['Balance'],
price=row['Price'],
average=row['Average'],
pnl=row['PnL'],
commission=row['Commission'],
oc=oc,
das=das,
ib=ib,
account=row['Account']
# trade_sum_id=Column(Integer, ForeignKey('trade_sum.id'))
)
session.add(trade)
if new_session:
session.commit()
session.close()
return True
def getDuplicateTrades(self, account):
'''
Have been unable to write a usable query using the ORM. Here it is
in SQL
'''
from sqlalchemy.sql import text
ModelBase.connect(new_session=True)
conn = ModelBase.engine.connect()
statement = f'''
SELECT t.id as t1_id, t2.id as t2_id,
t.balance as t1_bal, t2.balance as t2_bal,
t.datetime as t1_dt, t2.datetime as t2_dt,
t.DAS as t1_das, t2.das as t2_das,
t.ib as t1_ib, t2.ib as t2_ib,
t.trade_sum_id as t1_tsid, t2.trade_sum_id as t2_tsid,
t.price as t1_price, t2.price as t2_price
FROM ib_trades AS t
INNER JOIN ib_trades as t2
WHERE t.Symb = t2.symb
AND t.Qty = t2.Qty
AND (t.Price - t2.Price < .00001 or t2.Price - t.price < .00001)
AND t.datetime < t2.datetime
AND t.Account = "{account}"
AND t.Account = t2.Account
AND substr(t.datetime,1, 8) = substr(t2.datetime,1, 8)
AND (((t.DAS is NULL or t.DAS = "") and (t2.IB is NULL or t2.IB = ""))
or ((t.IB is NULL or t.IB = "") and (t2.DAS is NULL or t2.DAS = "")))
ORDER BY t.datetime'''
statement = text(statement)
result = ModelBase.engine.execute(statement)
result = [x for x in result]
return result
print()
def getStatementQuery(cls, begin, end, account='all'):
ModelBase.connect(new_session=True)
session = ModelBase.session
q = session.query(Trade).filter(Trade.datetime > begin).filter(
Trade.datetime < end)
if account != 'all':
q = q.filter_by(account=account)
return q
def getTableNames(self):
if not ModelBase.engine:
ModelBase.connect(new_session=True)
ModelBase.createAll()
inspector = inspect(ModelBase.engine)
tns = inspector.get_table_names()
return tns
def removeKey(cls, api):
ModelBase.connect(new_session=True)
session = ModelBase.session
q = session.query(ApiKey).filter_by(api=api).one_or_none()
if q:
ModelBase.session.delete(q)
ModelBase.session.commit()
ModelBase.session.close()
def getDistinctStratsQuery(cls):
ModelBase.connect(new_session=True)
qq = ModelBase.session.query(TradeSum.strategy,
func.count(TradeSum.strategy)).group_by(
TradeSum.strategy).order_by(
desc(func.count(
TradeSum.strategy)))
return qq
def getAccounts(cls):
'''
Retrieve a list of all accounts that have trades. The entries in the table are actual account numbers
'''
ModelBase.connect(new_session=True)
q = ModelBase.session.query(Trade.account).distinct().all()
print()
return [x[0] for x in q]
def getTags(cls, tsum_id):
'''
:params tsum_id: int. numpy types may fail(e.g. numpy.int64 fails in filter_by)
'''
ModelBase.connect(new_session=True)
q = ModelBase.session.query(TradeSum).filter_by(id=tsum_id).first()
return q.tags if q else []
def getId(cls, name):
'''
Get the id of the strategy named name or return None if not found
'''
ModelBase.connect(new_session=True, db="structjourDb")
session = ModelBase.session
q = session.query(Strategy.id).filter_by(name=name).one_or_none()
if not q: return None
return q.id
def isHoliday(self, d, new_session=False):
d = pd.Timestamp(d)
d = d.strftime("%Y%m%d")
if new_session == True:
ModelBase.connect(new_session)
session = ModelBase.session
q = session.query(Holidays).filter_by(day=d).one_or_none()
return True if q else False
def test_popHol(self):
ModelBase.connect(new_session=True)
statement = text('delete from holidays')
ModelBase.engine.execute(statement)
ibdb = StatementDB()
for holiday in ibdb.holidays:
for day in holiday[1:]:
if day:
self.assertTrue(ibdb.tcrud.isHoliday(day))
def deleteById(cls, tsid):
ModelBase.connect(new_session=True)
session = ModelBase.session
q = session.query(TradeSum).filter_by(id=tsid).one_or_none()
if not q:
return 0
session.delete(q)
session.close()
return 1
def setUp(self):
'''Remove schema'''
print(Inspire.__table__)
ModelBase.connect(new_session=True, con_str=self.con_str)
ModelBase.metadata.drop_all(bind=ModelBase.engine,
tables=[Inspire.__table__])
ModelBase.session.commit()
ModelBase.session.close()
def getStrategy(cls, name=None, sid=None):
ModelBase.connect(new_session=True, db="structjourDb")
session = ModelBase.session
if name:
q = session.query(Strategy).filter_by(name=name).one_or_none()
return q
return session.query(Strategy).filter_by(id=sid).one_or_none()
def updateTSID(cls, tid, tsid):
ModelBase.connect(new_session=True)
session = ModelBase.session
q = session.query(Trade).filter_by(id=tid).one_or_none()
if q:
if not isNumeric(q.trade_sum_id) or tsid != q.trade_sum_id:
q.trade_sum_id = tsid
session.add(q)
session.commit()
session.close()
def exercisegetListsOfTradesForStrategies():
ModelBase.connect(new_session=True)
q = ModelBase.session.query(TradeSum).filter(
TradeSum.date > "20200101").filter(TradeSum.date < "20200301")
trades = TradeSum.getListsOfTradesForStrategies(
q, ['ORB', "VWAP MA trend", "Swing", "Momentum"])
print(len(trades))
for t in trades:
print(t[0], len(t[1]))
print()
def addSource(cls, name, id=None):
ModelBase.connect(new_session=True, db="structjourDb")
session = ModelBase.session
if id:
source = Source(datasource=name, id=id)
else:
source = Source(datasource=name)
session.add(source)
session.commit()
session.close()
def createTables(self):
ModelBase.connect(new_session=True, db='structjourDb')
ModelBase.createAll()
try:
Source.addSource('default', 1)
Source.addSource('user', 2)
Source.addSource('contrib', 3)
logging.info('Creating standard Strategy Source entries')
except Exception:
logging.info('Standard Strategy Source entries have already been created')
def updateMstkVals(cls, tsid, val, note):
if not tsid: return
ModelBase.connect(new_session=True)
session = ModelBase.session
q = session.query(TradeSum).filter_by(id=tsid).one_or_none()
if q:
q.mstkval = val
q.mstknote = note
session.add(q)
session.commit()
session.close()
def insertHoliday(cls, day, name, commit=False):
if commit:
ModelBase.connect(new_session=True)
session = ModelBase.session
q = session.query(Holidays).filter_by(day=day).filter_by(name=name).one_or_none()
if not q:
h = Holidays(day=day, name=name)
session.add(h)
if commit:
session.commit()
session.close()
def getImage(cls, strat, widget):
'''
:return a list of [<Images>, <Strategy>]
'''
# Implicit join retrives a list[image, strategy]
ModelBase.connect(new_session=True, db="structjourDb")
session = ModelBase.session
q = session.query(Images, Strategy).filter(
and_(Strategy.id == Images.strategy_id, Strategy.name == strat,
Images.widget == widget)).one_or_none()
return q
def insertCovered(cls, account, d, covered_b='true', new_session=False):
if new_session == True:
ModelBase.connect(new_session=True)
session = ModelBase.session
d = pd.Timestamp(d)
d = d.strftime("%Y%m%d")
if not Covered.isDateCovered(account, d):
cov = Covered(day=d, account=account, covered=covered_b)
session.add(cov)
if new_session:
session.commit()
session.close()
def clearTables(self):
statements = ['''delete from chart''',
'''delete from holidays''',
'''delete from ib_covered''',
'''delete from ib_trades''',
'''delete from ib_positions''',
'''delete from trade_sum'''
]
ModelBase.connect(new_session=True)
for statement in statements:
s = text(statement)
result = ModelBase.engine.execute(s)
def appendTags():
'''local proof of concept stuff'''
ModelBase.connect(new_session=True)
trades = ModelBase.session.query(TradeSum).filter(
TradeSum.date > "20200131").all()
tags = ModelBase.session.query(Tags).all()
print(len(trades), len(tags))
for i, trade in enumerate(trades):
# print(f'({trade.id}, {tags[i%11].id}), ', end='')
TradeSum.append_tag(trade_sum_id=trade.id, tag_id=tags[i % 11].id)
TradeSum.append_tag(trade_sum_id=trade.id,
tag_id=tags[(i + 7) % 11].id)
def addStrategy(cls, name, preferred=True):
if Strategy.getStrategy(name):
logging.info(f'Strategy {name} already exists. No strategy added')
return
ModelBase.connect(new_session=True, db="structjourDb")
session = ModelBase.session
strat = Strategy(name=name, preferred=preferred)
session.add(strat)
session.commit()
session.close()
return strat