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'"