def get_contracts(ib):
try:
execs = ib.reqExecutions()
lst = []
for i in range(len(execs)):
contr = execs[i].contract
ib.qualifyContracts(contr)
print('Getting contract ' + str(contr.conId) + ' ' +
str(contr.localSymbol))
lst2 = []
lst2.append(contr.conId) #lst2[0]
lst2.append(contr.secType) #lst2[1]
lst2.append(contr.symbol) #lst2[2]
lst2.append(contr.localSymbol) #lst2[3]
lst2.append(contr.currency) #lst2[4]
lst2.append(contr.exchange) #lst2[5]
lst2.append(contr.tradingClass)
if (contr.secType == 'OPT'):
lst2.append(contr.lastTradeDateOrContractMonth) #lst2[6]
lst2.append(contr.strike) #lst2[7]
lst2.append(contr.right) #lst2[8]
lst2.append(contr.multiplier) #lst2[9]
else:
lst2.extend(
[None, None, None,
1]) # posem el multiplier a 1a per la resta d'instruments
lst.append(lst2)
return (lst)
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, 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, 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])
execute_query(db, sql, commit=True)
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 get_openpositions(ib):
try:
pfl = ib.portfolio()
lst = []
for i in range(len(pfl)):
lst2 = []
lst2.append(pfl[i].account) #lst2[0]
lst2.append(pfl[i].contract.conId) #lst2[1]
lst2.append(pfl[i].position) #lst2[2]
lst2.append(
1
) # indicates Open position #lst2[3]
lst2.append(pfl[i].marketPrice) #lst2[4]
lst2.append(pfl[i].marketValue) #lst2[5]
mult = pfl[i].contract.multiplier #lst2[6]
lst2.append(pfl[i].averageCost /
float(mult)) if mult != '' else lst2.append(
pfl[i].averageCost)
lst2.append(pfl[i].averageCost) #lst2[7]
lst2.append(pfl[i].unrealizedPNL) #lst2[8)
lst2.append(pfl[i].realizedPNL) #lst2[9]
lst.append(lst2)
return (lst)
except Exception as err:
error_handling(err)
raise
def get_executions(ib):
try:
execs = ib.reqExecutions()
lst = []
for i in range(len(execs)):
print('Getting execution ' + str(execs[i].execution.execId))
lst2 = [
] # els valors a inserir a la DDBB aniran a lst2 (1 lst2 per cada execs[i])
lst2.append(execs[i].execution.execId) # lst2[0]
lst2.append(execs[i].execution.acctNumber) # lst2[1]
lst2.append(execs[i].contract.conId) # lst2[2]
lst2.append(execs[i].time) #lst2[3]
if (execs[i].execution.side == 'BOT'): #lst2[4]
lst2.append(execs[i].execution.shares)
else:
s = -execs[i].execution.shares
lst2.append(s)
lst2.append(execs[i].execution.price) #lst2[5]
lst2.append(execs[i].commissionReport.commission) #lst2[6]
if (execs[i].execution.liquidation is None): #lst2[7]
lst2.append(0)
else:
lst2.append(execs[i].execution.liquidation)
# omplim els grecs, IV, etc si és una opció
if execs[i].contract.secType == 'OPT':
ib.qualifyContracts(execs[i].contract)
ib.reqMarketDataType(4)
opt = ib.reqMktData(execs[i].contract, '', False, False)
l = 0
while (
opt.lastGreeks == None
) and l < 5: # mini-bucle per esperar que es rebin els Greeks
opt = ib.reqMktData(execs[i].contract, '', False, False)
ib.sleep(5)
l += 1
opt = ib.reqMktData(execs[i].contract, '100,101,105,106,107',
False, False)
if (opt.lastGreeks is not None):
lst2.append(opt.lastGreeks.optPrice) #lst2[8]
lst2.append(opt.lastGreeks.impliedVol) #lst2[9]
lst2.append(opt.lastGreeks.delta) #lst2[10]
lst2.append(opt.lastGreeks.gamma) #lst2[11]
lst2.append(opt.lastGreeks.vega) #lst2[12]
lst2.append(opt.lastGreeks.theta) #lst2[13]
lst2.append(opt.lastGreeks.pvDividend) #lst2[14]
lst2.append(opt.lastGreeks.undPrice) #lst2[15]
else:
lst2.extend([0, 0, 0, 0, 0, 0, 0, 0])
else:
# si no és una opció, ho deixem amb 0's
lst2.extend([0, 0, 0, 0, 0, 0, 0, 0])
lst2.append(1) # lst2[16]
lst.append(
lst2
) # lst2 (com a list) s'afegeix al final de la llista lst. Aquesta llista (lst) és la que retorna la funció
return (lst)
except Exception as err:
error_handling(err)
raise
def openpositions(ib, db, accid, scan, maxstocks):
try:
scannedstocklist = scanstocks(ib, scan, maxstocks)
scannedstocklist = fillfundamentals(ib, scannedstocklist)
dbfill_contractfundamentals(db, accid, scannedstocklist)
return processpreselectedstocks(ib, db, accid, scannedstocklist)
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 manage_positions(ib, db, accId):
try:
dbfill_contracts(db, get_contracts(
ib)) # inserta tots els diferents contractes, si no hi són a la DB
dbfill_executions(
db,
get_executions(ib)) # inserta les noves (de 1 a 7 dies) executions
dbupdate_executions(db, dbanalyse_executions(
db, accId)) # actualitza les executions per tancar les que toqui
dbfill_positions(db, dbanalyse_positions(
db, accId)) # borra totes les positions i les re-inserta
except Exception as err:
error_handling(err)
raise
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 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 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 allowTrade(pctpostimeelapsed, pctprofitnow, sectype):
try:
allowtrade = 0
if sectype == "OPT":
if pctpostimeelapsed <= 10 and pctprofitnow > ibconfig.myoptprofit10:
allowtrade = 1
if pctpostimeelapsed <= 20 and pctprofitnow > ibconfig.myoptprofit20:
allowtrade = 1
if pctpostimeelapsed <= 50 and pctprofitnow > ibconfig.myoptprofit50:
allowtrade = 1
if pctpostimeelapsed <= 75 and pctprofitnow > ibconfig.myoptprofit75:
allowtrade = 1
if pctprofitnow >= ibconfig.myoptprofit: allowtrade = 1
if pctprofitnow <= ibconfig.myoptloss: allowtrade = 2
elif sectype == "STK":
if pctprofitnow >= ibconfig.mystkprofit: allowtrade = 3
if pctprofitnow <= ibconfig.mystkloss: allowtrade = 4
else:
allowtrade = 0
return allowtrade
except Exception as err:
error_handling(err)
raise
def requestadditionalfundamentals(ib, cnt):
try:
fundamentals = ib.reqFundamentalData(cnt, 'ReportSnapshot')
if fundamentals != []:
doc = xmltodict.parse(fundamentals)
ib.sleep(2)
dictratios = {}
for i in range(len(
doc['ReportSnapshot']['ForecastData']['Ratio'])):
dkey = (doc['ReportSnapshot']['ForecastData']['Ratio'][i]
['@FieldName'])
dvalue = (doc['ReportSnapshot']['ForecastData']['Ratio'][i]
['Value']['#text'])
dvalue = dvalue.split(".")
if len(dvalue) == 1:
dvalue.append(0)
dvalue[1] = "0." + str(dvalue[1])
dvalue = float(dvalue[0]) + float(dvalue[1])
dvalue = round(dvalue, 2)
dictratios[dkey] = dvalue
return (dictratios)
except Exception as err:
error_handling(err)
raise
scanCode=ibconfig.myscancodelist[i],
aboveVolume=200000,
marketCapAbove=10000000000,
averageOptionVolumeAbove=10000)
myorderdict[ibconfig.myscancodelist[i]] = ibopen.openpositions(
myib, mydb, myaccId, myscan, ibconfig.mymaxstocks)
myib.sleep(100)
ibmanagedb.manage_positions(myib, mydb, myaccId)
ibclose.processopenpositions(myib, mydb, myaccId)
ibmanagedb.manage_positions(myib, mydb, myaccId)
ibutil.dbcommit(mydb)
ibutil.dbdisconnect(mydb)
myib.disconnect()
except ConnectionRefusedError as cre:
ibutil.error_handling(cre, "I cannot connect to Interactive Brokers: ")
except Exception as e:
ibutil.error_handling(e)
#def portfolio_to_dict(ib_):
# try:
# pfl = ib_.portfolio()
# # dictionary de lists que contindrà les dades que volem recuperar de l'objecte Contract per cada PortfolioItem del Portfolio
# d_contr = {'secType': [], 'conId': [], 'symbol': [], 'exchange': [], 'primaryExchange': [], 'currency': [], 'localSymbol': []}
# # OrderedDict de lists que contindrà les dades que volem recuperar de la namedtupla PortfolioItem (excepte els detalls del Contract) per cada PortfolioItem del Portfolio del Portfolio
# d_pfl = {'position': [], 'marketPrice': [], 'marketValue': [],'averageCost': [], 'unrealizedPNL': [], 'realizedPNL': [], 'account': []}
# # recorrem tots els PortfoioItema Portfolio
# for i in range(len(pfl)):
# ib_.qualifyContracts(pfl[i].contract)
# for k in d_contr.keys():
# # afegim els valors (cada value de (key,value) és una llista) de cada atribut que recuperem de l'objecte Contract d'aquest PortfolioItem.
def opendefensiveposition(ib, cnt, pos):
try:
print("opendefensiveposition")
# creem objectes tupus contracte
stkcnt = ibsync.Contract() # el underlying de la opció
optcnt1 = ibsync.Contract() # la opció que hi ha al portfolio
optcnt2 = ibsync.Contract() # la potencial nova opció que es crearà
# composem el contracte del underlying de la opció analitzada
# stkcnt = Stock(cnt.symbol, "SMART", cnt.currency)
stkcnt.symbol = cnt.symbol
stkcnt.currency = cnt.currency
stkcnt.secType = "STK"
stkcnt.exchange = "SMART"
ib.qualifyContracts(stkcnt)
print("defensiveposition de: ", stkcnt)
# composem el contracte de la opció analitzada
optcnt1.conId = pos.conId
ib.qualifyContracts(optcnt1)
# composem la data d'expiració que és la mateixa tant per la opció original (optcnt1) com la nova defensiva (optcnt2)
dateexpiration = str(optcnt1.lastTradeDateOrContractMonth)[0:4] + str(
optcnt1.lastTradeDateOrContractMonth)[4:6] + str(
optcnt1.lastTradeDateOrContractMonth)[6:8]
# agafem lastprice del underlying provinent de ticker
tstk = ib.reqTickers(stkcnt)
topt1 = ib.reqTickers(optcnt1)
lastpricestk = tstk[0].marketPrice()
lastpriceopt1 = topt1[0].marketPrice()
ib.sleep(1)
# busquem la cadena d'opcions del underlying
chains = ib.reqSecDefOptParams(stkcnt.symbol, '', stkcnt.secType,
stkcnt.conId)
chain = next(
c for c in chains
if c.tradingClass == stkcnt.symbol and c.exchange == 'SMART')
# separem strikes i expiracions
lexps = []
lstrikes = []
lexps = chain.expirations
lstrikes = chain.strikes
myList = lstrikes
lastpricestk = int(lastpricestk)
# calculem la distància entre el preu del underlying ara i el strike de la opció venuda que estem analitzant
strikedistance = abs(optcnt1.strike - lastpricestk)
# busquem l'strike que més s'acosta al del preu actual del underlying
orderstrike = min(lstrikes,
key=lambda x: int(abs(int(x) - lastpricestk)))
# preparem el nou trade: si era un call ara un put...i al inreves
if optcnt1.right == "C":
opt2right = "P"
else:
opt2right = "C"
# preparem el nou trade: qualifiquem la nova opció compensatoria
optcnt2.symbol = optcnt1.symbol
optcnt2.strike = orderstrike
optcnt2.secType = optcnt1.secType
optcnt2.exchange = "SMART"
optcnt2.currency = optcnt1.currency
optcnt2.right = opt2right
optcnt2.lastTradeDateOrContractMonth = dateexpiration
ib.qualifyContracts(optcnt2)
print("optcon2", optcnt2)
# busquem el preu al que cotitza la nova opció compensatoria
topt2 = ib.reqTickers(optcnt2)
lastpriceopt2bis = (topt2[0].bid + topt2[0].ask) / 2
# lastprice = formatPrice(lastprice, 2)
lastpriceopt2 = topt2[0].marketPrice()
ib.sleep(1)
# executem la ordre
print("opendefensiveposition - ", optcnt2, pos.shares, lastpriceopt2,
pos.conId)
tradelimitorder(ib, optcnt2, pos.shares, lastpriceopt2)
except Exception as err:
error_handling(err)
raise
def fillfundamentals(ib, stklst):
print("\n\t fillfundamentals")
try:
for i in range(len(stklst)):
cnt = stklst[i][1] # contract stklst[i][1]
fr = ib.reqMktData(cnt, "258")
ib.sleep(10)
aux = dict(
t.split('=')
for t in str(fr.fundamentalRatios)[18:-1].split(',') if t)
fratios = {key.lstrip(): value for key, value in aux.items()}
addfunds = requestadditionalfundamentals(ib, cnt)
# we fill the list with fundamental data that we will use to update database + make computations to select
# candidates to open positions
# a vegades requestadditionalfundamentals torna buit, per això el "if df not"
stklst[i].append(0) # stklst[i][2]
stklst[i].append(0) # stklst[i][3]
if fratios != None:
stklst[i].append(fratios.get("AFEEPSNTM", "")) # stklst[i][4]
stklst[i].append(fratios.get(
"Frac52Wk",
"")) # fraction of 52 week high/low - stklst[i][5]
stklst[i].append(fratios.get("BETA", "")) # stklst[i][6]
stklst[i].append(fratios.get(
"APENORM", "")) # annual normalized PE - stklst[i][7]
stklst[i].append(fratios.get(
"QTOTD2EQ", "")) # total debt/total equity - stklst[i][8]
stklst[i].append(fratios.get(
"EV2EBITDA_Cur",
"")) # Enterprise value/ebitda - TTM - stklst[i][9]
stklst[i].append(
fratios.get("TTMPRFCFPS", "")
) # price to free cash flow per share - TTM - stklst[i][10]
stklst[i].append(fratios.get(
"YIELD", "")) # Dividend yield - stklst[i][11]
stklst[i].append(fratios.get(
"TTMROEPCT", "")) # return on equity % - stklst[i][12]
else:
stklst[i].extend([0, 0, 0, 0, 0, 0, 0, 0, 0])
'''
Not used attributes????
vcurrency = fratios.get("CURRENCY", "")
vhigh52wk = fratios.get("NHIG", "") # 52 week high
vlow52wk = fratios.get("NLOW", "") # 53 week low
vpeexclxor = fratios.get("PEEXCLXOR", "") # annual PE excluding extraordinary items
vevcur = fratios.get("EV-Cur", "") # Current enterprise value
'''
if addfunds != None:
stklst[i].append(addfunds["TargetPrice"]) # stklst[i][13]
stklst[i].append(addfunds["ConsRecom"]) # stklst[i][14]
stklst[i].append(addfunds["ProjEPS"]) # stklst[i][15]
stklst[i].append(addfunds["ProjEPSQ"]) # stklst[i][16]
stklst[i].append(addfunds["ProjPE"]) # stklst[i][17]
else:
stklst[i].extend([0, 0, 0, 0, 0])
for j in range(2, len(stklst[i])):
if stklst[i][j] == '': stklst[i][j] = 0
if stklst[i][j] == 'nan': stklst[i][j] = 0
if stklst[i][j] is None: stklst[i][j] = 0
print("fillfundamentals ", stklst[i])
return (stklst)
except Exception as err:
error_handling(err)
raise
def scanstocks(ib, scan, maxstocks):
'''
# atttibutes of the scannerSubscription object, they can be used to filter for some conditions
NumberOfRows[get, set] # int, The number of rows to be returned for the query
Instrument[get, set] # string, The instrument's ty for the scan (STK, FUT, HK, etc.)
LocationCode[get, set] # string, The request's location (STK.US, STK.US.MAJOR, etc.)
ScanCode[get, set] # string, Same as TWS Market Scanner's "parameters" field, i.e. TOP_PERC_GAIN
AbovePrice[get, set] # double, Filters out contracts which price is below this value
BelowPrice[get, set] # double, Filters out contracts which price is above this value
AboveVolume[get, set] # int, Filters out contracts which volume is above this value
AverageOptionVolumeAbove[get, set] # int, Filteres out Cotracts which option volume is above this value
MarketCapAbove[get, set] # double, Filters out Contracts which market cap is above this value.
MarketCapBelow[get, set] # double, Filters out Contracts which market cap is below this value.
MoodyRatingAbove[get, set] # string, Filters out Contracts which Moody 's rating is below this value.
MoodyRatingBelow[get, set] # string, Filters out Contracts which Moody 's rating is above this value.
SpRatingAbove[get, set] # string, Filters out Contracts with a S & P rating below this value.
SpRatingBelow[get, set] # string, Filters out Contracts with a S & P rating below this value.
MaturityDateAbove[get, set] # string, Filter out Contracts with a maturity date earlier than this value.
MaturityDateBelow[get, set] # string, Filter out Contracts with a maturity date older than this value.
CouponRateAbove[get, set] # double, Filter out Contracts with a coupon rate lower than this value.
CouponRateBelow[get, set] # double, Filter out Contracts with a coupon rate higher than this value.
ExcludeConvertible[get, set] # bool, Filters out Convertible bonds.
ScannerSettingPairs[get, set] # string, For example, a pairing "Annual, true" used on the "top Option Implied Vol % Gainers" scan would return annualized volatilities.
StockTypeFilter[get, set] # string
# list of instruments of the scannerSubscription object
"STK",
"STOCK.HK",
"STOCK.EU",
"STK.US",
# list of location codes of scannerSubscription object
"STK.US.MAJOR",
"STK.US.MINOR",
"STK.HK.SEHK",
"STK.HK.ASX",
"STK.EU"
# list of scanCodes of the scannerSubscription object
"LOW_OPT_VOL_PUT_CALL_RATIO",
"HIGH_OPT_IMP_VOLAT_OVER_HIST",
"LOW_OPT_IMP_VOLAT_OVER_HIST",
"HIGH_OPT_IMP_VOLAT",
"TOP_OPT_IMP_VOLAT_GAIN",
"TOP_OPT_IMP_VOLAT_LOSE",
"HIGH_OPT_VOLUME_PUT_CALL_RATIO",
"LOW_OPT_VOLUME_PUT_CALL_RATIO",
"OPT_VOLUME_MOST_ACTIVE",
"HOT_BY_OPT_VOLUME",
"HIGH_OPT_OPEN_INTEREST_PUT_CALL_RATIO",
"LOW_OPT_OPEN_INTEREST_PUT_CALL_RATIO",
"TOP_PERC_GAIN",
"MOST_ACTIVE",
"TOP_PERC_LOSE",
"HOT_BY_VOLUME",
"TOP_PERC_GAIN",
"HOT_BY_PRICE",
"TOP_TRADE_COUNT",
"TOP_TRADE_RATE",
"TOP_PRICE_RANGE",
"HOT_BY_PRICE_RANGE",
"TOP_VOLUME_RATE",
"LOW_OPT_IMP_VOLAT",
"OPT_OPEN_INTEREST_MOST_ACTIVE",
"NOT_OPEN",
"HALTED",
"TOP_OPEN_PERC_GAIN",
"TOP_OPEN_PERC_LOSE",
"HIGH_OPEN_GAP",
"LOW_OPEN_GAP",
"LOW_OPT_IMP_VOLAT",
"TOP_OPT_IMP_VOLAT_GAIN",
"TOP_OPT_IMP_VOLAT_LOSE",
"HIGH_VS_13W_HL",
"LOW_VS_13W_HL",
"HIGH_VS_26W_HL",
"LOW_VS_26W_HL",
"HIGH_VS_52W_HL",
"LOW_VS_52W_HL",
"HIGH_SYNTH_BID_REV_NAT_YIELD",
"LOW_SYNTH_BID_REV_NAT_YIELD"
'''
try:
print("\n\t scanstocks ")
stklst = []
scanner = ib.reqScannerData(scan, [])
for stock in scanner[:
maxstocks]: # loops through stocks in the scanner
contr = stock.contractDetails.contract
ib.qualifyContracts(contr)
stk = []
stk.append(scan.scanCode) #scancode
stk.append(contr) #contract
print("scanstocks :: stock :: ", stk)
stklst.append(stk)
return stklst
except Exception as err:
error_handling(err)
raise
def opennewoption(ib, cnt, opttype, optright, optdaystoexp):
print("\n\t opennewoption")
try:
# agafem lastprice del underlying provinent de ticker
lastpricestk = ib.reqTickers(cnt)[0].marketPrice()
# busquem la cadena d'opcions del underlying
chains = ib.reqSecDefOptParams(cnt.symbol, '', cnt.secType, cnt.conId)
chain = next(c for c in chains
if c.tradingClass == cnt.symbol and c.exchange == 'SMART')
# separem strikes i expiracions (tenir en compte que strikes i expiracions estan en forma de Set, no de List
lstrikes = chain.strikes
# busquem el strike que més s'acosta a lastpricestk
orderstrike = min(lstrikes, key=lambda x: abs(int(x) - lastpricestk))
# busquem la expiration que més s'acosta a desiredexpiration
lexps = []
for e in chain.expirations:
lexps.append(int(e))
desiredexpiration = date.today() + timedelta(days=optdaystoexp)
desiredexpiration = int(
str(desiredexpiration)[0:4] + str(desiredexpiration)[5:7] +
str(desiredexpiration)[8:10])
orderexp = min(lexps, key=lambda x: abs(int(x) - desiredexpiration))
# preparem el nou trade: definim i qualifiquem la nova opció
optcnt = ibsync.Contract()
optcnt.symbol = cnt.symbol
optcnt.strike = orderstrike
optcnt.secType = "OPT"
optcnt.exchange = "SMART"
optcnt.currency = cnt.currency
optcnt.right = optright
optcnt.lastTradeDateOrContractMonth = orderexp
# no tots els strikes possibles (entre ells potser el ja triat) són vàlids.
# si el strike triat no és vàlid en busquem un que sigui vàlid apujant (i baixant) el strike en 0.5
# fins a trobar un que sigui acceptat. Això pot provocar que ens allunyem del ATM, però no hi ha altra solució
ct = 0
while ib.qualifyContracts(optcnt) == [] and ct < 11:
optcnt.strike = orderstrike = int(orderstrike + 0.5 *
(optright == "C") - 0.5 *
(optright == "P"))
ct += 1
# busquem el preu al que cotitza la nova opció de la que obrirem contracte
topt = ib.reqTickers(optcnt)
lastpriceopt = topt[0].marketPrice()
# fem un reqN¡MktData per obtenir (hopefully) els Greeks
opttkr = ib.reqMktData(optcnt, '', False,
False) # això torna un objecte Ticker
l = 0
while (opttkr.lastGreeks == None
) and l < 5: # mini-bucle per esperar que es rebin els Greeks
opttkr = ib.reqMktData(optcnt, '', False, False)
ib.sleep(5)
l += 1
# definim la quantitat = (Capital màxim)/(100*preu acció*Delta)
# en cas que la delta torni buida, usem 0.5 (de moment agafem opcions AtTheMoney igualment)
if (opttkr.lastGreeks.delta is not None):
qty = (1 - 2 * -(opttype == "SELL")) * round(
ibconfig.mymaxposition /
(100 * lastpricestk * abs(opttkr.lastGreeks.delta)))
else:
qty = (1 - 2 *
(opttype == "SELL")) * round(ibconfig.mymaxposition /
(100 * lastpricestk * 0.5))
print("symbol ", optcnt.symbol, "lastpricestk ", lastpricestk,
"desiredstrike", lastpricestk, "orderstrike ", orderstrike,
"desiredexpiration", desiredexpiration, "orderexp ", orderexp,
"quantity", qty, "conId", optcnt.conId, "price", lastpriceopt)
if lastpriceopt == lastpriceopt: #checks if nan
return tradelimitorder(ib, optcnt, qty, lastpriceopt)
else:
return None
except Exception as err:
error_handling(err)
raise
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
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
)
elif acc == "XAVREAL":
rslt = execute_query(
mydb,
"SELECT connHost, connPort, connAccId FROM connections WHERE connName = 'xavreal7496'"
)
else:
sys.exit("Unknown account!!")
myib.connect(rslt[0][0], rslt[0][1], 1)
myaccId = rslt[0][2]
myorderdict = {}
#openpositions(myib, mydb, myaccId)
#cds = myib.reqContractDetails(ibsync.contract.Option('WTTR', '20190719', exchange='SMART'))
#options = [cd.contract for cd in cds]
#tickers = [t for i in range(0, len(options), 100) for t in myib.reqTickers(*options[i:i + 100])]
#import pandas as pd
#ibutil.save_to_excel(pd.DataFrame(tickers))
#opt = ibsync.contract.Option('WTTR', '20190719', 12.5, 'C' , exchange='SMART')
# (intentem) recuperar els greeks
#greeks = ibutil.get_greeks(myib, opt, "lastGreeks").modelGreeks
branco_strategy1(myib, mydb, myaccId)
ibutil.dbdisconnect(mydb)
myib.disconnect()
except Exception as err:
error_handling(err)
raise
