def precompute(underlying):
print "precompute: " + str(underlying)
print
db = MySQLdb.connect(host="localhost", user=settings.db_username, passwd=settings.db_password, db="optiondata")
cur2 = db.cursor()
query = "SELECT id, quote_date, underlying_bid_1545, underlying_ask_1545, bid_1545, ask_1545, expiration, strike, option_type FROM optiondata WHERE underlying_symbol = '" + underlying + "' AND iv IS NULL ORDER BY id asc"
cur2.execute(query)
for row in cur2:
rowid = row[0]
quote_date = row[1]
underlying_bid_1545 = row[2]
underlying_ask_1545 = row[3]
bid_1545 = row[4]
ask_1545 = row[5]
expiration = row[6]
strike = float(row[7])
option_type = row[8].lower()
current_quote = float((underlying_bid_1545 + underlying_ask_1545) / 2)
midprice = float((bid_1545 + ask_1545) / 2)
expiration_time = datetime.combine(expiration, time(16, 00))
remaining_time_in_years = util.remaining_time(quote_date, expiration_time)
iv = 0.001
delta = 0.001
theta = 0.001
if remaining_time_in_years > 0:
try:
iv = implied_volatility.implied_volatility(midprice, current_quote, int(strike), remaining_time_in_years, util.interest, option_type)
except:
iv = 0.001
delta = analytical.delta(option_type, current_quote, strike, remaining_time_in_years, util.interest, iv) * 100
theta = analytical.theta(option_type, current_quote, strike, remaining_time_in_years, util.interest, iv) * 100
updateQuery = "UPDATE optiondata SET iv=%s, delta=%s, theta=%s WHERE id=%s" % (iv, delta, theta, rowid)
try:
cur2.execute(updateQuery)
db.commit()
except:
print rowid
print current_quote
print midprice
print iv
print delta
print theta
print updateQuery
db.close()
print("before query")
query = "SELECT DISTINCT fullday_call.underlying_symbol, fullday_call.underlying_mid_1545, fullday_call.strike, fullday_call.expiration, (fullday_call.mid_1545 - fullday_put.mid_1545) AS credit FROM fullday as fullday_call JOIN fullday as fullday_put ON fullday_call.underlying_symbol = fullday_put.underlying_symbol AND fullday_call.strike = fullday_put.strike AND fullday_call.expiration = fullday_put.expiration WHERE fullday_call.underlying_mid_1545 > 100 AND fullday_call.expiration > (fullday_call.quote_date + INTERVAL '60 day') AND fullday_call.option_type = 'c' AND fullday_put.option_type = 'p' AND ABS(fullday_call.bid_1545 - fullday_call.ask_1545) < 0.5 AND fullday_call.bid_1545 != 0 AND fullday_call.ask_1545 != 0 AND ABS(fullday_put.bid_1545 - fullday_put.ask_1545) < 0.5 AND fullday_put.bid_1545 != 0 AND fullday_put.ask_1545 != 0 AND (fullday_call.mid_1545 > fullday_put.mid_1545) ORDER BY fullday_call.underlying_symbol, fullday_call.strike, fullday_call.expiration ASC;"
cur1.execute(query)
print("after query")
for row in cur1.fetchall():
underlying = row[0]
underlying_price = row[1]
strike = row[2]
expiration = row[3]
credit = row[4]
difference = (strike - underlying_price)
percentage = float(((difference + credit) / underlying_price) * 100)
remaining_time = util.remaining_time(
datetime.strptime(str(date), "%Y-%m-%d").date(),
datetime.strptime(str(expiration), "%Y-%m-%d"))
per_annum = round((percentage / remaining_time), 2)
if per_annum > 1:
counter += 1
print("counter: " + str(counter))
print("underlying: " + str(underlying))
print("price: " + str(float(underlying_price)))
print("strike: " + str(strike))
print("expiration: " + str(expiration))
print("credit -c+p: " + str(credit))
print("percentage: " + str(round(percentage, 2)))
print("remaining time in years: " + str(remaining_time))
print("percentage / remaining time: " + str(per_annum))
print()
def precompute(table, computedate, underlying, include_riskfree):
db = psycopg2.connect(host="localhost",
user=settings.db_username,
password=settings.db_password,
database="optiondata")
cur2 = db.cursor()
underlying_fragment = ""
if (underlying != "*"):
underlying_fragment = "underlying_symbol = '" + underlying + "' AND "
date_fragment = ""
if (computedate != "*"):
date_fragment = "quote_date = '" + str(computedate) + "' AND "
query = "SELECT id, quote_date, underlying_mid_1545, mid_1545, expiration, strike, option_type FROM " + \
table + " WHERE " + underlying_fragment + date_fragment + "iv IS NULL"
cur2.execute(query)
result = cur2.fetchall()
print(
str(computedate) + " " + str(underlying) + ": " + str(len(result)) +
" results")
bulkrows = []
if (len(result) > 0):
for row in result:
rowid = row[0]
quote_date = row[1]
underlying_mid_1545 = float(row[2])
mid_1545 = float(row[3])
expiration = row[4]
strike = float(row[5])
option_type = row[6]
expiration_time = datetime.datetime.combine(
expiration, datetime.time(16, 0))
remaining_time_in_years = util.remaining_time(
quote_date, expiration_time)
rf = util.interest
if include_riskfree:
rf = util.get_riskfree_libor(quote_date,
remaining_time_in_years)
try:
iv = implied_volatility.implied_volatility(
mid_1545, underlying_mid_1545, int(strike),
remaining_time_in_years, rf, option_type)
except:
iv = 0.001
try:
bs_price_bid_ask = black_scholes(option_type,
underlying_mid_1545, strike,
remaining_time_in_years, rf,
iv)
except:
bs_price_bid_ask = 0.001
try:
delta = analytical.delta(option_type, underlying_mid_1545,
strike, remaining_time_in_years, rf,
iv) * 100
except:
delta = 0.001
try:
theta = analytical.theta(option_type, underlying_mid_1545,
strike, remaining_time_in_years, rf,
iv) * 100
except:
theta = 0.001
try:
vega = analytical.vega(option_type, underlying_mid_1545,
strike, remaining_time_in_years, rf,
iv) * 100
except:
vega = 0.001
bulkrows.append({
'iv': iv,
'bs_price_bid_ask': bs_price_bid_ask,
'delta': delta,
'theta': theta,
'vega': vega,
'rowid': rowid
})
psycopg2.extras.execute_batch(
cur2,
"""UPDATE """ + table +
""" SET iv=%(iv)s, bs_price_bid_ask=%(bs_price_bid_ask)s, delta=%(delta)s, theta=%(theta)s, vega=%(vega)s WHERE id=%(rowid)s""",
bulkrows,
page_size=100)
db.commit()
db.close()
def getExpectedValue(connector, underlying, combo, current_date, expiration):
current_quote = connector.query_midprice_underlying(
underlying, current_date)
expiration_time = datetime.combine(expiration, time(16))
remaining_time_in_years = util.remaining_time(current_date,
expiration_time)
ul_for_ew = []
sum_legs = []
prob_touch = []
if (current_quote % 10) < 5:
atm_strike = int(current_quote / 10) * 10
else:
atm_strike = int((current_quote + 10) / 10) * 10
try:
atm_option = util.Option(connector, current_date, underlying,
atm_strike, expiration, "p")
except ValueError:
return "missing data"
midprice = connector.query_midprice(current_date, atm_option)
try:
atm_iv = implied_volatility.implied_volatility(
midprice, current_quote, atm_strike, remaining_time_in_years,
util.interest, atm_option.type)
except pyex.BelowIntrinsicException:
atm_iv = 0.01
if (atm_iv == 0): atm_iv = 0.01
# print atm_iv
one_sd = (atm_iv / math.sqrt(
util.yeartradingdays /
(remaining_time_in_years * util.yeartradingdays))) * current_quote
lower_ul = current_quote - e_spanne * one_sd
upper_ul = current_quote + e_spanne * one_sd
step = (upper_ul - lower_ul) / 24 # war 1000
for i in range(25): # war 1001
ul_for_ew.insert(i, lower_ul + (i * step))
sum_legs_i = 0
positions = combo.getPositions()
for position in positions:
# param sigma: annualized standard deviation, or volatility
# https://www.etfreplay.com/etf/iwm.aspx
value = black_scholes.black_scholes(position.option.type,
ul_for_ew[i],
position.option.strike,
remaining_time_in_years,
util.interest, 0)
guv = (value - position.entry_price) * ratio * position.amount
sum_legs_i += guv
sum_legs.insert(i, sum_legs_i)
prob = util.prob_hit(current_quote, ul_for_ew[i],
remaining_time_in_years, 0, atm_iv)
prob_touch.insert(i, prob)
sumproduct = sum([a * b for a, b in zip(sum_legs, prob_touch)])
expected_value = round((sumproduct / sum(prob_touch)), 2)
return expected_value
def getExpectedValue(underlying,
combo,
current_date,
expiration,
use_precomputed=True,
include_riskfree=True):
current_quote_original = util.connector.query_midprice_underlying(
underlying, current_date)
if (current_quote_original is None):
return None
current_quote = float(current_quote_original)
if (current_quote == 0.0):
return None
expiration_time = datetime.combine(expiration, time(16))
remaining_time_in_years = util.remaining_time(current_date,
expiration_time)
ul_for_ew = []
sum_legs = []
prob_touch = []
if (current_quote % 10) < 5:
atm_strike = int(current_quote / 10) * 10
else:
atm_strike = int((current_quote + 10) / 10) * 10
if use_precomputed:
try:
atm_iv = float(
util.connector.select_iv(current_date, underlying, expiration,
"p", atm_strike))
except:
atm_iv = 0.01
else:
try:
atm_option = util.Option(current_date, underlying, atm_strike,
expiration, "p")
except ValueError:
return None
midprice = util.connector.query_midprice(current_date, atm_option)
rf = util.interest
if include_riskfree:
rf = util.get_riskfree_libor(current_date, remaining_time_in_years)
try:
atm_iv = float(
implied_volatility.implied_volatility(midprice, current_quote,
atm_strike,
remaining_time_in_years,
rf, atm_option.type))
except:
atm_iv = 0.01
if (atm_iv == 0):
atm_iv = 0.01
one_sd = (atm_iv / math.sqrt(
util.yeartradingdays /
(remaining_time_in_years * util.yeartradingdays))) * current_quote
lower_ul = current_quote - e_spanne * one_sd
upper_ul = current_quote + e_spanne * one_sd
step = (upper_ul - lower_ul) / 24 # war 1000
for i in range(25): # war 1001
ul_for_ew.insert(i, lower_ul + (i * step))
sum_legs_i = 0
positions = combo.getPositions()
for position in positions:
# param sigma: annualized standard deviation, or volatility
# https://www.etfreplay.com/etf/iwm.aspx
rf = util.interest
if include_riskfree:
rf = util.get_riskfree_libor(current_date,
remaining_time_in_years)
value = black_scholes.black_scholes(position.option.type,
ul_for_ew[i],
position.option.strike,
remaining_time_in_years, rf, 0)
guv = (value - position.entry_price) * ratio * position.amount
sum_legs_i += guv
sum_legs.insert(i, sum_legs_i)
prob = util.prob_hit(current_quote, ul_for_ew[i],
remaining_time_in_years, 0, atm_iv)
prob_touch.insert(i, prob)
sumproduct = sum([a * b for a, b in zip(sum_legs, prob_touch)])
expected_value = round((sumproduct / sum(prob_touch)), 2)
return expected_value
def precompute(underlying, include_riskfree):
done = False
bulksize = 100000
counter = 0
print("precompute: " + str(underlying))
print()
while not done:
db = psycopg2.connect(host="localhost",
user=settings.db_username,
password=settings.db_password,
database="optiondata")
cur2 = db.cursor()
print("Query for next " + str(bulksize) + " items to precompute ")
query = "SELECT id, quote_date, underlying_mid_1545, mid_1545, expiration, strike, option_type FROM optiondata WHERE underlying_symbol = '" + underlying + "' AND iv IS NULL LIMIT " + str(
bulksize)
cur2.execute(query)
result = cur2.fetchall()
print(str(len(result)) + " items to precompute")
if len(result) == 0:
done = True
print("Done precomputing")
print()
bulkrows = []
for row in result:
rowid = row[0]
quote_date = row[1]
underlying_mid_1545 = float(row[2])
mid_1545 = float(row[3])
expiration = row[4]
strike = float(row[5])
option_type = row[6]
expiration_time = datetime.datetime.combine(
expiration, datetime.time(16, 0))
remaining_time_in_years = util.remaining_time(
quote_date, expiration_time)
rf = util.interest
if include_riskfree:
rf = util.get_riskfree_libor(quote_date,
remaining_time_in_years)
try:
iv = implied_volatility.implied_volatility(
mid_1545, underlying_mid_1545, int(strike),
remaining_time_in_years, rf, option_type)
delta = analytical.delta(option_type, underlying_mid_1545,
strike, remaining_time_in_years, rf,
iv) * 100
theta = analytical.theta(option_type, underlying_mid_1545,
strike, remaining_time_in_years, rf,
iv) * 100
vega = analytical.vega(option_type, underlying_mid_1545,
strike, remaining_time_in_years, rf,
iv) * 100
except:
iv = 0.001
delta = 0.001
theta = 0.001
vega = 0.001
bulkrows.append({
'iv': iv,
'delta': delta,
'theta': theta,
'vega': vega,
'rowid': rowid
})
counter += 1
if (((counter % 1000) == 0) or ((len(result) < bulksize) and
(counter == len(result)))):
try:
cur2.executemany(
"""UPDATE optiondata SET iv=%(iv)s, delta=%(delta)s, theta=%(theta)s, vega=%(vega)s WHERE id=%(rowid)s""",
bulkrows)
db.commit()
print("inserted: " + str(counter))
bulkrows = []
time.sleep(1)
except Exception as e:
print("an exception occurred")
print(e)
if (len(result) < bulksize) and (counter == len(result)):
done = True
print("Done precomputing")
print()
db.close()
def precompute(table, computedate, underlying, include_riskfree):
# start = time.time()
db = psycopg2.connect(host="localhost", user=settings.db_username, password=settings.db_password, database="optiondata")
cur2 = db.cursor()
underlying_fragment = ""
if (underlying != "*"):
underlying_fragment = "underlying_symbol = '" + underlying + "' AND "
date_fragment = ""
if (computedate != "*"):
date_fragment = "quote_date = '" + str(computedate) + "' AND "
query = "SELECT id, quote_date, underlying_mid_1545, mid_1545, expiration, strike, option_type FROM " + table + " WHERE " + underlying_fragment + date_fragment + "bs_price_bid_ask IS NULL"
cur2.execute(query)
result = cur2.fetchall()
print (str(computedate) + " " + str(underlying) + ": " + str(len(result)) + " results")
bulkrows = []
if (len(result) > 0):
for row in result:
rowid = row[0]
quote_date = row[1]
underlying_mid_1545 = float(row[2])
mid_1545 = float(row[3])
expiration = row[4]
strike = float(row[5])
option_type = row[6]
expiration_time = datetime.datetime.combine(expiration, datetime.time(16, 0))
remaining_time_in_years = util.remaining_time(quote_date, expiration_time)
rf = util.interest
if include_riskfree:
rf = util.get_riskfree_libor(quote_date, remaining_time_in_years)
try: iv = implied_volatility.implied_volatility(mid_1545, underlying_mid_1545, int(strike), remaining_time_in_years, rf, option_type)
except: iv = 0.001
if underlying_mid_1545 == 0: underlying_mid_1545 = 0.01
bs_price_bid_ask = black_scholes(option_type, underlying_mid_1545, strike, remaining_time_in_years, rf, iv)
# print (bs_price_bid_ask)
bulkrows.append({'bs_price_bid_ask': bs_price_bid_ask, 'rowid': rowid})
try:
psycopg2.extras.execute_batch(cur2, """UPDATE """ + table + """ SET bs_price_bid_ask=%(bs_price_bid_ask)s WHERE id=%(rowid)s""", bulkrows, page_size=100)
db.commit()
except Exception as e:
print("an exception occurred")
print(e)
print (query)
# end = time.time()
# print (end - start)
print ()
db.close()