def dbfill_contracts(db, cntrlst):
try:
sql = "INSERT INTO contracts (kConId, kType, kSymbol, kLocalSymbol, kCurrency, kExchange, kTradingClass, kExpiry, kStrike, kRight, kMultiplier, kEarningsDate) "
sql = sql + "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s, %s)"
for i in range(len(cntrlst)):
c = cntrlst[i] # contract
check = execute_query(
db, "SELECT * FROM contracts WHERE kConId = " + str(c.conId))
if (not check):
earningsdate = inputearningsdate(c.conId,
c.symbol) # earnings date
val = [
c.conId, c.secType, c.symbol, c.localSymbol, c.currency,
c.exchange, c.tradingClass, None, None, None, 1,
earningsdate
]
# Si és una opció, reemplacem els valors específics d'opcions
if (c.secType == 'OPT'):
val[-5:-1] = [
c.lastTradeDateOrContractMonth, c.strike, c.right,
c.multiplier
]
execute_query(db, sql, values=tuple(val), commit=True)
except Exception as err:
#error_handling(err)
raise
def dbupdate_executions(db, execs):
# execs[i] conté [tId, tExecId, tConId, tShares, tPrice, tActive]
try:
for i in range(len(execs)):
print('Updating execution ' + str(execs[i][0]))
if execs[i][5] == 0:
sql = "UPDATE trades SET tActive = 0 where tId = " + str(
execs[i][0])
execute_query(db, sql, commit=True)
elif execs[i][5] == 'M':
sql = "UPDATE trades SET tShares = tShares - " + str(
execs[i][3]) + " ,tActive = 0 where tId = " + str(
execs[i][0])
execute_query(db, sql, commit=True)
sql = "INSERT INTO trades (tExecid, tAccId, tClientId, tOrderId, tConId, tTime, tShares, tPrice, tCommission, tLiquidation, "
sql = sql + "toptPrice, toptIV, toptDelta, toptGamma, toptVega, toptTheta, toptPVDividend, toptPriceOfUnderlying, tActive) "
# al nou registre, modifiquem l'Execid afegin-hi una C a davant, tActive=1 i tShares = execs[i][5]
new_execid = 'C' + execs[i][1]
sql = sql + "SELECT '" + new_execid + "',tAccId, tClientId, tOrderId, tConId, tTime," + str(
execs[i][3]) + ", tPrice, tCommission, tLiquidation, "
sql = sql + "toptPrice, toptIV, toptDelta, toptGamma, toptVega, toptTheta, "
sql = sql + "toptPVDividend, toptPriceOfUnderlying, 1 " # active = 1
sql = sql + "FROM trades WHERE tId = " + str(execs[i][0])
dbfill_positions(execute_query(db, sql, commit=True))
except Exception as err:
#error_handling(err)
raise
def dbfill_fundamentals(db, accid, stklst):
try:
clst = [a[0] for a in stklst]
dbfill_contracts(db, clst)
for i in range(len(stklst)):
c = stklst[i][0]
check = execute_query(
db, "SELECT * FROM contractfundamentals WHERE fConId = " +
str(c.conId) + " AND fAccId = '" + str(accid) +
"' AND fDate = DATE(NOW())")
val = stklst[i][1:15]
if (not check):
datetoday = datetime.now().strftime("%Y%m%d")
sql = "INSERT INTO contractfundamentals (fAccId, fConId, fDate, fEpsNext, fFrac52wk, fBeta, fPE0, " \
"fDebtEquity, fEVEbitda, fPricetoFCFShare, fYield, fROE, fTargetPrice, fConsRecom, fProjEPS, " \
"fProjEPSQ, fProjPE) " \
" VALUES ('" + str(accid) + "', '" + str(c.conId) + "', " + datetoday + ", %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
else:
sql = "UPDATE contractfundamentals set fEpsNext = %s, fFrac52wk = %s, fBeta = %s, fPE0 = %s, fDebtEquity = %s, " \
" fEVEbitda = %s, fPricetoFCFShare = %s, fYield = %s, fROE = %s, fTargetPrice = %s, fConsRecom = %s, fProjEPS = %s, " \
" fProjEPSQ = %s, fProjPE = %s " \
" WHERE fConId = " + str(c.conId) + " AND fAccId = '" + str(accid) + "' AND fDate = DATE(NOW())"
execute_query(db, sql, values=tuple(val), commit=True)
except Exception as err:
#error_handling(err)
raise
def dbupdate_contractfundamentals(db, accid, stk):
try:
cnt = stk[1] # contract
sql = "UPDATE contractfundamentals set fScanCode= %s, fRating = %s, fTradeType = %s, fEpsNext = %s, fFrac52wk = %s, fBeta = %s, fPE0 = %s, fDebtEquity = %s, " \
" fEVEbitda = %s, fPricetoFCFShare = %s, fYield = %s, fROE = %s, fTargetPrice = %s, fConsRecom = %s, fProjEPS = %s, fProjEPSQ = %s, fProjPE = %s " \
" WHERE fConId = " + str(cnt.conId) + " AND fAccId = '" + str(accid) + "' "
val = [stk[0]] + stk[2::]
execute_query(db, sql, values=tuple(val), commit=True)
except Exception as err:
error_handling(err)
raise
def dbfill_contracts(db, contr):
try:
sql = "INSERT INTO contracts (kConId, kType, kSymbol, kLocalSymbol, kCurrency, kExchange, kTradingClass, kExpiry, kStrike, kRight, kMultiplier) "
sql = sql + "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s)"
for i in range(len(contr)):
check = execute_query(
db,
"SELECT * FROM contracts WHERE kConId = " + str(contr[i][0]))
if (not check):
execute_query(db, sql, values=tuple(contr[i]), commit=True)
except Exception as err:
error_handling(err)
raise
def dbanalyse_executions(db, accId):
sql = "SELECT DISTINCT(tConId), min(tShares), max(tShares), COUNT(*) FROM activetrades WHERE tAccId = '" + str(accId) + "' " \
+ "GROUP BY tConId HAVING COUNT(tConId)>1 AND min(tShares) < 0 AND max(tShares)> 0 ORDER BY tConId, tTime"
try:
lst = execute_query(
db, sql
) # distinct executions for the same contract + number of executions
final_list = []
for i in range(len(lst)):
j = k = last = stop = 0
sql = "SELECT tId, tExecId, tConId, tShares, tPrice, tActive FROM activetrades " \
+ "WHERE tAccId = '" + str(accId) + "' AND tConId = " + str(lst[i][0]) + " ORDER BY SIGN(tShares), tTime"
execs = execute_query(db, sql)
execs[0] = list(execs[0]) # convertim la tupla en una list
# trobar l'índex(k)a partir del qual els valors són positius
for h in range(1, len(execs)):
execs[h] = list(execs[h]) # convertim la tupla en una list
if (sign(execs[h][3]) != sign(execs[h - 1][3])):
stop = h
k = h
while j < stop:
if k < len(execs):
if abs(execs[j][3]) < abs(
execs[k][3]): # Comparació de les +/- shares
execs[j][5] = 0 # posarem el registre a tActive = 0
execs[k][3] = execs[j][3] + execs[k][3]
j += 1
last = k
elif abs(execs[j][3]) == abs(execs[k][3]):
execs[j][5] = 0
execs[k][5] = 0
last = len(execs)
j += 1
k += 1
else:
execs[k][5] = 0 # posarem el registre a tActive = 0
execs[j][3] = execs[j][3] + execs[k][3]
last = j
k += 1
else:
break
if (last != len(execs)
): # si last = len(execs), compres i vendes s'han quadrat
execs[last][
5] = 'M' # marca que s'ha canviat (ho posem a tActive)
for j in range(0, len(execs)):
final_list.append(execs[j])
return final_list
except Exception as err:
error_handling(err)
raise
def dbfill_orders(db, order, trade, scode, ttype):
try:
sql = "INSERT INTO orders (oOrderId, oClientId, oConId, oQuantity, oStatus, oScanCode, oTradeType) " \
" VALUES (%s, %s, %s, %s, %s, %s, %s)"
check = execute_query(
db, "SELECT * FROM orders WHERE oOrderId = " + str(order.orderId) +
" AND oClientId = " + str(order.clientId))
if (not check):
val = (order.orderId, order.clientId, trade.contract.conId,
order.totalQuantity, trade.orderStatus.status, scode, ttype)
execute_query(db, sql, val, True)
else:
print("Aquesta ordre ja existeix a la Base de Dades: ", order)
except Exception as err:
#error_handling(err)
raise
def dbfillall_greeks(ib, db, accid):
print("dbfillall_greeks")
lst = execute_query(
db, "SELECT DISTINCT(pConId) FROM positions WHERE pAccId = '" +
str(accid) + "' AND pType = 'OPT' AND pActive = 1 ")
for i in range(len(lst)):
conid = lst[i][0]
opt = ibsync.contract.Option(conId=str(conid))
ib.qualifyContracts(opt)
opttkr = ibutil.get_greeks(ib, opt)
lg = opttkr.lastGreeks
if lg is not None:
val = [
lg.impliedVol, lg.optPrice, lg.delta, lg.gamma, lg.theta,
lg.vega, lg.pvDividend, lg.undPrice
]
else:
val = [0] * 8
mg = opttkr.modelGreeks
if mg is not None:
val.extend([
mg.impliedVol, mg.optPrice, mg.delta, mg.gamma, mg.theta,
mg.vega, mg.pvDividend, mg.undPrice
])
else:
val.extend([0] * 8)
val.extend([opttkr.ask, opttkr.bid, opttkr.askSize, opttkr.bidSize])
check = execute_query(
db, "SELECT * FROM contractgreeks WHERE cgConId = " + str(conid) +
" AND cgAccId = '" + str(accid) + "' AND cgDate = DATE(NOW())")
if (not check):
datetoday = datetime.now().strftime("%Y%m%d")
sql = "INSERT INTO contractgreeks (cgAccId, cgConId, cgDate, cglastIV, cglastOptPrice, cglastdelta, " \
"cglastgamma, cglasttheta, cglastvega, cglastDividend, cglastUndPrice, cgmodelIV, cgmodelOptPrice, " \
"cgmodeldelta, cgmodelgamma, cgmodeltheta, cgmodelvega, cgmodeldividend, cgmodelUndPrice, " \
"cgask, cgbid, cgasksize, cgbidsize) " \
" VALUES ('" + str(accid) + "', '" + str(conid) + "', " + datetoday + ", %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
"%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
else:
sql = "UPDATE contractgreeks set cglastIV = %s, cglastOptPrice = %s, cglastdelta = %s, cglastgamma = %s, cglasttheta = %s, " \
" cglastvega = %s, cglastDividend = %s, cglastUndPrice = %s, cgmodelIV = %s, cgmodelOptPrice = %s, cgmodeldelta = %s, " \
" cgmodelgamma = %s, cgmodeltheta = %s, cgmodelvega = %s, cgmodeldividend = %s, cgmodelUndPrice = %s, " \
" cgask = %s, cgbid = %s, cgasksize = %s, cgbidsize = %s " \
" WHERE cgConId = " + str(conid) + " AND cgAccId = '" + str(accid) + "' AND cgDate = DATE(NOW())"
execute_query(db, sql, values=tuple(val), commit=True)
def dbfill_executions(db, execs):
sql = "INSERT INTO trades (tExecid, tAccId, tConId, tTime, tShares, tPrice, tCommission, tLiquidation, "
sql = sql + "toptPrice, toptIV, toptDelta, toptGamma, toptVega, toptTheta, toptPVDividend, toptPriceOfUnderlying, tActive)"
sql = sql + "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
for i in range(len(execs)):
try:
check = execute_query(
db, "SELECT * FROM trades WHERE tExecId = '" +
str(execs[i][0]) + "'")
if (not check):
execute_query(db, sql, values=tuple(execs[i]), commit=True)
except Exception as err:
if err.errno == sqlconn.errorcode.ER_DUP_ENTRY:
continue
else:
error_handling(err)
raise
def dbget_lastfundamentals(db, conid):
try:
sql = "SELECT * FROM contractfundamentals WHERE fConId = " + str(
conid) + " ORDER BY fDate DESC LIMIT 1"
result = execute_query(db, sql)
if result == []: return [0] * 17
else: return list(result[0])
except Exception as err:
# error_handling(err)
raise
def getprevioustargetprice(db, conid, accid):
try:
sql = "SELECT cfs.fTargetPrice FROM contracts c RIGHT JOIN contractfundamentals cfs on c.kConId = cfs.fConId " + \
" WHERE cfs.fConId = '" + str(conid) + "' AND cfs.fAccId = '" + str(accid) + "' AND cfs.fDate < DATE(NOW()) " + \
" ORDER BY cfs.fDate DESC LIMIT 1 "
rst = execute_query(db, sql)
if rst != []: return rst[0][0]
else: return 0
except Exception as err:
#error_handling(err)
raise
def dbfill_contractfundamentals(db, accid, stklst):
try:
for i in range(len(stklst)):
cnt = stklst[i][1]
check = execute_query(
db, "SELECT * FROM contracts WHERE kConId = " + str(cnt.conId))
if (not check):
sql = "INSERT INTO contracts (kConId, kType, kSymbol, kLocalSymbol, kCurrency, kExchange, kTradingClass, kExpiry, kStrike, kRight, kMultiplier) " \
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s)"
val = (cnt.conId, cnt.secType, cnt.symbol, cnt.localSymbol,
cnt.currency, cnt.exchange, cnt.tradingClass)
if (cnt.secType == 'OPT'):
val = val + (cnt.lastTradeDateOrContractMonth, cnt.strike,
cnt.right, cnt.multiplier)
else:
val = val + (
None, None, None, 1
) # posem el multiplier a 1a per la resta d'instruments
execute_query(db, sql, values=val, commit=True)
check = execute_query(
db, "SELECT fConId FROM contractfundamentals WHERE fConId = " +
str(cnt.conId))
if (not check):
sql = "INSERT INTO contractfundamentals (fAccId, fConId, fScanCode, fRating, fTradeType, fEpsNext, fFrac52wk, fBeta, fPE0, fDebtEquity, fEVEbitda, fPricetoFCFShare, fYield, fROE, fTargetPrice, fConsRecom, fProjEPS, fProjEPSQ, fProjPE) " \
" VALUES ('" + str(accid) + "', '" + str(cnt.conId) + "', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
val = [stklst[i][0]] + stklst[i][2::]
execute_query(db, sql, values=tuple(val), commit=True)
except Exception as err:
error_handling(err)
raise
def dbfill_positions(db, execs):
# execs[i] conté [ctId, ctAccId, ctExecId, ctConId, ctType, ctMultiplier, ctShares, ctPrice, ctDate, ctCommission, ctLiquidation, ctoptPrice, ctoptIV, ctoptDelta, ctoptGamma,
# ctoptTheta, ctoptVega, ctoptPVDividend, ctoptPriceOfUnderlying, ctActive
# Si ctActive='C', llavors execs[i][19]=[execs[j]], on execs[j] és l'execució que tanca execs[i]
try:
# borrem la taula de positions (per aquest Account) i després farem inserts del què tenim a execs. La taula positions_optiondetails es borra també (DELETE CASCADE)
if execs != []:
sql = "DELETE FROM positions WHERE pAccId = '" + str(
execs[0][1]) + "'"
count = execute_query(db, sql, commit=True)
for i in range(len(execs)):
if execs[i][19] != 'D':
print('Inserting position ' + str(execs[i][0]))
sql = "INSERT INTO positions (pId, pExecid, pAccId, pConId, pDate, pType, pMultiplier, pShares, pInitialPrice, pInitialValue, pCommission, pLiquidation, pActive) " \
+ "SELECT ctId, ctExecId, ctAccId, ctConId, ctDate, ctType, ctMultiplier, ctShares, ctPrice, ctPrice*abs(ctShares)*ctMultiplier, ctCommission, ctLiquidation, ctActive " \
+ "FROM combinedtrades WHERE ctID = " + str(execs[i][0])
execute_query(db, sql, commit=True)
if execs[i][4] == 'OPT':
sql = "INSERT INTO positions_optiondetails (podId, podInitialModelPrice, podInitialIV, podInitialDelta, podInitialGamma, podInitialVega, " \
+ "podInitialTheta, podInitialPVDividend, podInitialPriceOfUnderlying) " \
+ "SELECT ctId, ctoptPrice, ctoptIV, ctoptDelta, ctoptGamma, ctoptVega, ctoptTheta, ctoptPVDividend, ctoptPriceOfUnderlying " \
+ "FROM combinedtrades WHERE ctID = " + str(execs[i][0])
execute_query(db, sql)
if execs[i][19] == 'C':
clist = execs[i][20]
sql = "UPDATE positions set pActive = %s, pClosingPrice = %s, pClosingValue = %s, pClosingDate = %s, pClosingId = %s, pPNL = %s, pCommission = %s, pLiquidation = %s " \
+ "WHERE pId = " + str(execs[i][0])
pnl = -(execs[i][6] * execs[i][7] +
clist[6] * clist[7]) * execs[i][5]
val = (0, clist[7], clist[5] * abs(clist[6]) * clist[7],
clist[8], clist[0], pnl, execs[i][9] + clist[9],
clist[10])
execute_query(db, sql, values=val, commit=True)
if execs[i][4] == 'OPT':
sql = "UPDATE positions_optiondetails set podFinalModelPrice = %s, podFinalIV = %s, podFinalDelta = %s, podFinalGamma = %s, podFinalTheta = %s, " \
+ "podFinalVega = %s, podFinalPVDividend = %s, podFinalPriceOfUnderlying = %s " \
+ "WHERE podId = " + str(execs[i][0])
val = (clist[11], clist[12], clist[13], clist[14],
clist[15], clist[16], clist[17], clist[18])
execute_query(db, sql, values=val, commit=True)
except Exception as err:
error_handling(err)
raise
def dbfill_earningsdate(db):
try:
# opció d'entrar-ne una o totes les buides
q = input(
"Vols entrar totes les que estan buides o una d'específica? \n 'Press Enter' - Totes \n 'StockCode' - una de concreta \n\n"
)
sql = "SELECT kConId, kSymbol FROM contracts "
if q != "":
sql = sql + " WHERE kSymbol = '" + q + "' "
else:
sql = sql + " WHERE kEarningsDate IS NULL ORDER BY kSymbol "
execs = execute_query(db, sql)
if not execs:
print(" No hi ha cap contracte que compleixi el criteri ")
else:
print("Aquesta és la llista: ", execs)
for i in range(len(execs)):
earningsdate = inputearningsdate(execs[i][0], execs[i][1])
sql = "UPDATE contracts SET kEarningsDate = " + str(earningsdate) + \
" WHERE kSymbol= '" + str(execs[i][1]) + "' "
execute_query(db, sql)
except Exception as err:
# error_handling(err)
raise
def scanselection(db):
sql = "SELECT scode, ' - ', sdescription, ' - Tipus ', stype FROM scancodes WHERE sonoff = 1 ORDER BY scode"
rslt = execute_query(db, sql)
text = str(rslt).strip('[]').replace("'", '').replace(",", '').replace(
'(', '').replace(')', '\n')
scancode = input("triar els scans desitjats - exit per sortir: \n " + text)
while scancode != "exit":
if scancode in (item[0] for item in rslt):
print("\nScan escollit: ", scancode, "\n")
break
else:
print("Scan desconegut!")
scancode = input("")
if scancode == "exit": sys.exit("Exit requested!")
scanselection = [[scancode, item[2], item[4]] for item in rslt
if item[0] == scancode]
return scanselection[0] # [code, description, type]
def get_positiondate(db, accid, conid):
try:
sql = "SELECT DATE_FORMAT(pdate, '%Y%m%d') FROM positions " \
" WHERE pAccId = '" + str(accid) + "' AND pConId = " + str(conid) + " AND pActive = 1 " \
" ORDER by pDate LIMIT 1"
execs = execute_query(db, sql)
if not execs:
#raise Exception (" No hi ha cap posició a la base de dades pel contracte ", conid )
print(" No hi ha cap posició a la base de dades pel contracte ",
conid)
elif len(execs) > 1:
raise Exception(" Hi ha més d'una posició oberta del contracte ",
conid)
else:
return execs[0][0]
except Exception as err:
#error_handling(err)
raise
def processpreselectedstocks(ib, db, accid, stklst):
print("\n\t processpreselectedstocks")
try:
listorders = []
for i in range(len(stklst)):
cnt = stklst[i][1] # contract
targetprice = stklst[i][13] # target price
frac52w = stklst[i][5] # distància a la que està del high/low
sql = "SELECT fTargetPrice FROM contractfundamentals WHERE fConId = '" + str(cnt.conId) + "' " \
+ " AND fAccId = '" + str(accid) + "' "
rst = execute_query(db, sql)
# si scancode = HIGH_VS_52W_HL i la distància al hign és <= que un 1% i TargetPrice > el que està guardat a la base de dades
if stklst[i][0] == 'HIGH_VS_52W_HL' and float(
frac52w
) >= ibconfig.my52whighfrac and targetprice > rst[0][0]:
print("Open new LOW_VS_52W_HL - Put ", cnt.symbol)
listorders.append(
opennewoption(ib, cnt, "SELL", "P",
ibconfig.myoptdaystoexp))
elif stklst[i][0] == 'LOW_VS_52W_HL' and float(
frac52w
) <= ibconfig.my52wlowfrac and targetprice < rst[0][0]:
print("Open new LOW_VS_52W_HL - Call ", cnt.symbol)
listorders.append(
opennewoption(ib, cnt, "SELL", "C",
ibconfig.myoptdaystoexp))
elif stklst[i][0] == 'HOT_BY_VOLUME':
print("ProcessPreselectedStocks HOT_BY_VOLUME ")
else:
print("I’m sorry Besuga, I’m afraid I can’t do that: \n ",
cnt.conId, ' ', cnt.symbol, "Scan Code: ", stklst[i][0],
"frac52w: ", frac52w, " Target Price: ", targetprice,
"\n")
# actualitzem els fundamentals a la base de dades
dbupdate_contractfundamentals(db, accid, stklst[i])
return listorders
except Exception as err:
error_handling(err)
raise
def getearningsdate(db, conid, symbol):
try:
sql = "SELECT DATE_FORMAT(kEarningsDate, '%Y%m%d') FROM contracts " \
"WHERE kConId = '" + str(conid) + "' "
execs = execute_query(db, sql)
if not execs:
print(" El contracte ", conid, " no existeix a la base de dades ")
return None
elif execs[0][0] == None:
print(" La Earnings Date no existeix pel contracte ", str(conid),
"-", str(symbol))
earningsdate = inputearningsdate(conid, symbol)
# ULL!!!!!! Si la data no està entrada, posem un valor suficientment allunyat per no sortir de la posició
if earningsdate != None:
return datetime.strptime(earningsdate, "%Y%m%d").date()
else:
return (
datetime.now() +
timedelta(days=cf.mydaystoearnings + 1)).strftime("%Y%m%d")
else:
return datetime.strptime(execs[0][0], "%Y%m%d").date()
except Exception as err:
#error_handling(err)
raise
def dbfill_accounthistory(ib, db, accid):
print("Filling Account History ")
try:
dacs = ib.accountSummary()
check = execute_query(
db, "SELECT * FROM accounthistory WHERE achId = '" + str(accid) +
"' AND achDate = DATE(NOW()) ")
val = [accid]
for i in range(1, 22):
val.append(dacs[i].value)
# pnl és una llista [PnL, num open Calls, open calls PNL, open Puts, open Puts PNL, ope Stocks, open Stocks PNL]
realizedPNL = execute_query(
db, "SELECT sum(pPNL) FROM positions WHERE pActive = 0")
pnl = ibutil.get_pnl(ib, accid)
val.extend([
pnl[0].dailyPnL, pnl[0].unrealizedPnL + float(realizedPNL[0][0]),
pnl[0].unrealizedPnL,
float(realizedPNL[0][0])
])
val.extend(ibutil.dbget_pnlbyright(ib))
val = tuple(val)
if (not check):
sql = "INSERT INTO accounthistory (achId, achDate, achTime, achCushion, achDayTradesRemaining,achLookAheadNextChange,achAccruedCash,achAvailableFunds,achBuyingPower, " \
"achEquityWithLoanValue,achExcessLiquidity,achFullAvailableFunds,achFullExcessLiquidity,achFullInitMarginReq,achFullMaintMarginReq,achGrossPositionValue,achInitMarginReq, " \
"achLookAheadAvailableFunds,achLookAheadExcessLiquidity,achLookAheadInitMarginReq,achLookAheadMaintMarginReq,achMaintMarginReq,achNetLiquidation,achTotalCashValue, " \
"achDailyPNL, achTotalPNL, achUnrealizedPNL, achRealizedPNL, achOpenCalls, achOpenCallsPNL, achOpenPuts, achOpenPutsPNL, achOpenStocks, achOpenStocksPNL) " \
"VALUES (%s, DATE(NOW()), CURTIME(), %s,%s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s ) "
execute_query(db, sql, values=val, commit=True)
else:
sql = "UPDATE accounthistory SET achTime = CURTIME(), achCushion = %s, achDayTradesRemaining = %s,achLookAheadNextChange = %s, achAccruedCash = %s,achAvailableFunds = %s,achBuyingPower = %s, " \
"achEquityWithLoanValue = %s,achExcessLiquidity = %s,achFullAvailableFunds = %s,achFullExcessLiquidity = %s,achFullInitMarginReq = %s,achFullMaintMarginReq = %s,achGrossPositionValue = %s,achInitMarginReq = %s, " \
"achLookAheadAvailableFunds = %s,achLookAheadExcessLiquidity = %s,achLookAheadInitMarginReq = %s,achLookAheadMaintMarginReq = %s,achMaintMarginReq = %s,achNetLiquidation = %s, achTotalCashValue = %s, " \
"achDailyPNL = %s, achTotalPNL = %s, achUnrealizedPNL = %s, achRealizedPNL = %s, achOpenCalls = %s, achOpenCallsPNL = %s, achOpenPuts = %s, achOpenPutsPNL = %s, achOpenStocks = %s, achOpenStocksPNL = %s " \
" WHERE achId = '" + str(accid) + "' AND achDate = DATE(NOW()) "
val = val[1::]
execute_query(db, sql, values=val, commit=True)
except Exception as err:
#error_handling(err)
raise
def processopenpositions(ib, db, vAccId):
print("\nprocessopenpositions")
try:
# llegim posicions obertes de la base de dades
query = "SELECT pId, pExecId, pAccId, pConId, pDate, pType, pMultiplier, pShares, pInitialPrice,pInitialValue, pClosingPrice, pClosingValue," \
" pClosingDate, pClosingId, pPNL, pCommission, pLiquidation, pActive" \
" FROM positions LEFT JOIN contracts ON positions.pConId = contracts.kConId" \
" WHERE pAccId = '" + vAccId + "' AND pActive = 1"
rst = execute_query(db, query, values=None)
# definim namedtuple "positions" per a processar posicions obertes
positions = namedtuple(
"positions", "Id execId accId conId \
date type multiplier shares initialPrice initialValue closingPrice \
closingValue closingDate closingId PNL commission liquidation \
active")
# passem les execucions obertes en forma de namedtuple a la llista "openpos"
openpos = []
for i in range(len(rst)):
position = positions(Id=rst[i][0],
execId=rst[i][1],
accId=rst[i][2],
conId=rst[i][3],
date=rst[i][4],
type=rst[i][5],
multiplier=rst[i][6],
shares=rst[i][7],
initialPrice=rst[i][8],
initialValue=rst[i][9],
closingPrice=rst[i][10],
closingValue=rst[i][11],
closingDate=rst[i][12],
closingId=rst[i][13],
PNL=rst[i][14],
commission=rst[i][15],
liquidation=rst[i][16],
active=rst[i][17])
openpos.append(position)
# llegim "openpos" en forma de loop per a decidir què fer amb cada execució oberta
pctProfitList = []
for pos in openpos:
# creem un objecte Contract
cnt = ibsync.Contract()
# fem una instancia de contract amb el contracte llegit del query de trades oberts de la db trades
cnt.conId = pos.conId
ib.qualifyContracts(cnt)
pfl = ib.portfolio()
# obtenim i formategem data expiració
dateexpiration = str(cnt.lastTradeDateOrContractMonth)[0:4] + str(
cnt.lastTradeDateOrContractMonth)[4:6] + str(
cnt.lastTradeDateOrContractMonth)[6:8]
# agafem lastprice provinent de portfolio
lastprice = 0
for f in pfl:
if pos.conId == f.contract.conId:
lastprice = f.marketPrice
# lastprice = f.marketValue
# demanem dades a traves de reqMktData
# m_data = ib.reqMktData(cnt)
# while m_data.last != m_data.last: ib.sleep(0.01) # Wait until data is in.
# ib.cancelMktData(cnt)
# print("m_data ",m_data.last)
avgcost = float(pos.initialPrice)
vshares = pos.shares
# calculem pctprofitnow (el pnl de la posició)
if vshares < 0:
pctprofitnow = (1 - (lastprice / avgcost)) * 100
else:
pctprofitnow = ((lastprice / avgcost) - 1) * 100
print(cnt.symbol, " ", vshares, "lastprice ", lastprice,
"avgcost", avgcost, "pctprofitnow ", pctprofitnow)
# calculem percentatge temps passat entre apertura posició i expiració per a posicions d'opcions
pctpostimeelapsed = 0
if cnt.secType == "OPT":
dateentry = str(pos.date)[0:4] + str(pos.date)[5:7] + str(
pos.date)[8:10]
datetoday = datetime.datetime.now().strftime("%Y%m%d")
datedifffromentry = diffdays
datedifffromentry = diffdays(dateentry, dateexpiration)
datedifffromtoday = diffdays(datetoday, dateexpiration)
pctpostimeelapsed = int(
(1 - datedifffromtoday / datedifffromentry) * 100)
# d'acord amb els paràmetres calculats decidim si es fa un trade o no a la funció "allowtrade"
# allowtrade = allowTrade(pctpostimeelapsed, pctprofitnow)
allowtrade = allowTrade(pctpostimeelapsed, pctprofitnow,
cnt.secType)
# allowtrade = 0
pctProfitList.append([
cnt.symbol, pos.shares, cnt.right, cnt.strike,
pos.initialPrice, lastprice,
int(pctprofitnow), pctpostimeelapsed, allowtrade
])
# allowtrade = 1 tancar posició per recollida de beneficis, allowtrade = 2 fem un trade defensio de la posició
price = 0
if allowtrade == 1:
if pos.shares < 0:
ordertype = 'BUY'
else:
ordertype = 'SELL'
# Configurem preu operació
if ordertype == "BUY" and cnt.secType == "OPT":
# price = ((avgcost * ((100 - pctprofitnow)) / 100)) / 100
price = avgcost * (1 - pctprofitnow / 100)
elif ordertype == "SELL" and cnt.secType == "OPT":
# price = (avgcost * (1 + (pctprofitnow / 100))) / 100
price = avgcost * (1 + pctprofitnow / 100)
fmtprice = formatPrice(price, 2)
print("Close Position: \t", cnt, "\t", ordertype, "\t",
fmtprice)
tradelimitorder(ib, cnt, -vshares, fmtprice)
elif allowtrade == 2:
# obrim posició defensiva
opendefensiveposition(ib, cnt, pos)
elif allowtrade == 3:
if pos.shares < 0:
ordertype = 'BUY'
else:
ordertype = 'SELL'
# Configurem preu operació
if ordertype == "BUY":
price = lastprice
# price = avgcost * (1 - pctprofitnow / 100)
elif ordertype == "SELL":
price = lastprice
# price = avgcost * (1 + pctprofitnow / 100)
fmtprice = formatPrice(price, 2)
print("Close Position: \t", cnt, "\t", ordertype, "\t",
fmtprice)
tradelimitorder(ib, cnt, -vshares, fmtprice)
elif allowtrade == 4:
if pos.shares < 0:
ordertype = 'BUY'
else:
ordertype = 'SELL'
# Configurem preu operació
if ordertype == "BUY":
price = lastprice
# price = avgcost * (1 - pctprofitnow / 100)
elif ordertype == "SELL":
price = lastprice
# price = avgcost * (1 + pctprofitnow / 100)
fmtprice = formatPrice(price, 2)
print("Close Position: \t", cnt, "\t", ordertype, "\t",
fmtprice)
tradelimitorder(ib, cnt, -vshares, fmtprice)
elif allowtrade == "8888":
pass
else:
pass
except Exception as err:
error_handling(err)
raise
def positionisopen(db, accid, symbol):
sql = "SELECT * FROM openpositions WHERE pAccId = '" + str(
accid) + "' AND kSymbol = '" + str(symbol) + "' "
execs = execute_query(db, sql)
if execs != []: return True
else: return False
def dbanalyse_positions(db, accId):
sql = "SELECT DISTINCT(ctConId) FROM combinedtrades WHERE ctAccId = '" + str(
accId) + "' ORDER BY ctTime"
try:
lst = execute_query(
db, sql) # llista els diferents contractes a 'combinedtrades'
final_list = []
for i in range(len(lst)):
sql = "SELECT ctId, ctAccId, ctExecId, ctConId, ctType, ctMultiplier, ctShares, ctPrice, ctDate, ctCommission, ctLiquidation, ctoptPrice, ctoptIV, ctoptDelta, ctoptGamma, " \
+ "ctoptTheta, ctoptVega, ctoptPVDividend, ctoptPriceOfUnderlying, ctActive FROM combinedtrades " \
+ "WHERE ctAccId = '" + str(accId) + "' AND ctConId = " + str(lst[i][0]) + " ORDER BY ctActive, ctTime"
execs = execute_query(db, sql)
# mirem si l'últim registre està actiu (com a molt pot ser l'últim), si està actiu no cal fer-li res
stop = len(execs)
if execs[len(execs) - 1][19] == 1: stop = len(execs) - 1
for h in range(0, len(execs)):
execs[h] = list(execs[h]) # convertim la tupla en una list
j, new_k, new_j = 0, stop, stop
for h in (y for y in range(j + 1, stop)
if sign(execs[y][6]) != sign(execs[j][6])):
new_k = h
break
k = min(new_k, stop)
while j < stop:
if abs(execs[j][6]) < abs(
execs[k][6]): # Comparació de les +/- shares
execs[j].append(
execs[k]
) # append a la llista de j tota la llista de k com exec[j][20]
execs[k][6] = execs[j][6] + execs[k][
6] # recalculem el número de shares de k per la següent iteració
elif abs(execs[j][6]) == abs(execs[k][6]):
execs[k][19] = 'D' # D for delete
execs[j].append(
execs[k]
) # append a la llista de j tota la llista de k com exec[j][20]
# en aquest cas (k<j), tanquem execs[j]iinserim un nou element a la lliata amb la resta de shares de exec[j]
# ajustem la variable stop adequadament
else:
execs[k][19] = 'D' # D for delete
aux = execs[j].copy() # aux és una llista auxiliar
aux[0] = execs[k][
0] # utilitzem l'id de K (doncs sabem que execs[k] tanc auna posició, l'id no s'usarà
aux[6] = execs[j][6] + execs[k][
6] # el número de shares que quedaran al nou element
execs[j][6] = -execs[k][
6] # ajustem el número de shares a execs[j] - posició que tanca
execs[j].append(
execs[k]
) # append a la llista de j tota la llista de k com exec[j][20]
execs.insert(
j + 1, aux) # insertem el nou element a la posició j+1
stop += 1 # stop augmenta en un doncs afegim un element a la execs
execs[j][
19] = 'C' # posarem el registre a tActive = (C)losed - SEMPRE
new_j = stop
for h in (x for x in range(j + 1, stop)
if execs[x][19] != 'D'):
new_j = h
for l in (y for y in range(new_j + 1, stop)
if sign(execs[y][6]) != sign(execs[new_j][6])):
k = l
break
break
j = min(new_j, stop)
for h in range(0, len(execs)):
final_list.append(execs[h])
return final_list
except Exception as err:
error_handling(err)
raise
except Exception as e:
# error_handling(e)
raise
if __name__ == '__main__':
try:
myib = ibsync.IB()
mydb = ibutil.dbconnect(cf.dbhost, cf.dbname, cf.dbuser, cf.dbpassword)
rslt = []
q = input("Triar entre 'besugapaper', 'xavpaper', 'mavpaper1', 'mavpaper2' - exit to quit ")
while q != "exit":
sql = "SELECT connHost, connPort, connAccId, connClientId FROM connections WHERE "
if q.lower() == "besugapaper":
rslt = ibutil.execute_query(mydb, sql + " connName = 'besugapaper7498'")
break
elif q.lower() == "xavpaper":
rslt = ibutil.execute_query(mydb, sql + " connName = 'xavpaper7497'")
break
elif q.lower() == "mavpaper1":
rslt = ibutil.execute_query(mydb, sql + " connName = 'mavpaper1'")
break
elif q.lower() == "mavpaper2":
rslt = ibutil.execute_query(mydb, sql + " connName = 'mavpaper2'")
break
elif q.lower() == "exit":
sys.exit("Exit requested! ")
else:
q = input ("Unknown account! ")
dbfill_positions(db, dbanalyse_positions(
db, accId)) # borra totes les positions i les re-inserta
except Exception as err:
error_handling(err)
raise
if __name__ == '__main__':
myib = IB()
mydb = ibutil.dbconnect("localhost", "besuga", "xarnaus", "Besuga8888")
acc = input(
"triar entre 'besugapaper', 'xavpaper', 'mavpaper1', 'mavpaper2'")
if acc == "besugapaper":
rslt = execute_query(
mydb,
"SELECT connHost, connPort, connAccId FROM connections WHERE connName = 'besugapaper7498'"
)
elif acc == "xavpaper":
rslt = execute_query(
mydb,
"SELECT connHost, connPort, connAccId FROM connections WHERE connName = 'xavpaper7497'"
)
elif acc == "mavpaper1":
rslt = execute_query(
mydb,
"SELECT connHost, connPort, connAccId FROM connections WHERE connName = 'mavpaper1'"
)
elif acc == "mavpaper2":
rslt = execute_query(
mydb,
"SELECT connHost, connPort, connAccId FROM connections WHERE connName = 'mavpaper2'"
