def get_delta_records(self,
equity_symbol,
equity_records,
days_to_current_date=10):
conn = BaseDAO.get_connection()
cursor = conn.cursor()
delta_list = []
# hard code here, because the option was ingested from 20170724, and some vxx data may wrong before (or on) 20170824
filtered_equity_records = filter(
lambda x: x[0] >= datetime.date(2017, 8, 24), equity_records)
all_unexpired_dates = self.option_dao.get_all_unexpired_dates(
equity_symbol, filtered_equity_records[0][0], cursor=cursor)
for date_price in filtered_equity_records:
expiration_date = self.find_following_expiration_date(
all_unexpired_dates, date_price[0])
# print equity_symbol, date_price, expiration_date, days_to_current_date
# expiration_date = self.option_dao.get_following_expirationDate(equity_symbol, date_price[0])
option_symbol = self.option_dao.find_symbol(
equity_symbol,
expiration_date,
date_price[1],
imp_only=True,
current_date=date_price[0],
days_to_current_date=days_to_current_date,
cursor=cursor)
delta = self.option_dao.get_delta_by_symbol_and_date(
option_symbol, date_price[0], cursor)
#print [date_price[0], delta]
if delta is not None:
delta_list.append([date_price[0], delta])
conn.commit()
conn.close()
return delta_list
def update_delta(risk_free_interest_rate=0.005):
date_price_records = VIXDAO().get_vix_price_by_symbol('VIY00')
date_hv_lst = OptionCalculater.get_year_history_volatility_list(
date_price_records)
date_price_dic = list_to_hash(date_price_records)
date_hv_dic = list_to_hash(date_hv_lst)
option_dao = OptionDAO()
vix_option_records = option_dao.get_vix_options()
conn = BaseDAO.get_connection()
cursor = conn.cursor()
count = 0
for (symbol, trade_date, left_days, strike_price,
option_type) in vix_option_records:
underlying_price = date_price_dic.get(trade_date)
sigma = date_hv_dic.get(trade_date)
if underlying_price is not None and sigma is not None:
delta = OptionCalculater.get_delta(underlying_price,
strike_price, left_days,
risk_free_interest_rate,
sigma,
option_type[0:1].lower())
option_dao.update_delta_for_vix_options(
symbol, trade_date, delta, cursor)
count += 1
if count == 1000:
conn.commit()
count = 0
conn.commit()
conn.close()
def get_spy_price_list(date_str_list):
price_list = []
dao = YahooEquityDAO()
conn = BaseDAO.get_connection()
cursor = conn.cursor()
for date_str in date_str_list:
price_list.append(
dao.get_equity_price_by_date('SPY', date_str, cursor=cursor))
conn.close
return price_list
def insert(self, records):
query_template = """insert into equity_min (symbol,tradeTime,openPrice,highPrice,lowPrice,closePrice,volume) values ('{}','{}',{},{},{},{},{})"""
conn = BaseDAO.get_connection()
cursor = conn.cursor()
for equity in records:
query = BaseDAO.mysql_format(query_template, equity.symbol,
equity.tradeTime, equity.openPrice,
equity.highPrice, equity.lowPrice,
equity.lastPrice, equity.volume)
self.execute_query(query, cursor)
conn.commit()
conn.close()
def insert(self, records):
query_template = """insert into option_data (underlingSymbol, tradeTime, symbol, expirationDate,the_date,daysToExpiration,optionType,strikePrice,askPrice,bidDate,bidPrice,openPrice,highPrice,lowPrice,lastPrice,priceChange,volatility,theoretical,delta,gamma,rho,theta,vega,openInterest,volume) values ('{}','{}','{}','{}','{}',{},'{}',{},{},'{}',{},{},{},{},{},{},{},{},{},{},{},{},{},{},{})"""
conn = BaseDAO.get_connection()
cursor = conn.cursor()
count = 0
for option in records:
query = BaseDAO.mysql_format(query_template, option.underlingSymbol, option.tradeTime, option.symbol,option.expirationDate,option.date,option.daysToExpiration,option.optionType,option.strikePrice,option.askPrice,option.bidDate,option.bidPrice,option.openPrice,option.highPrice,option.lowPrice,option.lastPrice,option.priceChange,option.volatility,option.theoretical,option.delta,option.gamma,option.rho,option.theta,option.vega,option.openInterest,option.volume)
self.execute_query(query, cursor)
count += 1
if count == 1000:
conn.commit()
count = 0
conn.commit()
conn.close()
def insert(self, symbol, df):
query_template = """insert into yahoo_equity (symbol,tradeDate,openPrice,highPrice,lowPrice,closePrice,adjClosePrice,volume) values ('{}','{}',{},{},{},{},{},{})"""
conn = BaseDAO.get_connection()
cursor = conn.cursor()
for index, row in df.iterrows():
if TradeTime.is_trade_day(
datetime.datetime.strptime(row['Date'], '%Y-%m-%d')):
query = BaseDAO.mysql_format(query_template, symbol,
row['Date'], row['Open'],
row['High'], row['Low'],
row['Close'], row['Adj Close'],
row['Volume'])
self.execute_query(query, cursor)
conn.commit()
conn.close()
def save(self, credits):
query_template = """insert into nyse_credit (lastDate,the_year,the_month,margin_debt,cash_accounts,credit_balance) values
(str_to_date('{}', '%Y-%m-%d'),{},{},{},{},{})
on duplicate key update margin_debt = {}, cash_accounts = {}, credit_balance = {}"""
conn = BaseDAO.get_connection()
cursor = conn.cursor()
for credit in credits:
query = BaseDAO.mysql_format(
query_template, credit.date_str, credit.year, credit.month,
credit.margin_debt, credit.cash_accounts,
credit.credit_balance, credit.margin_debt,
credit.cash_accounts, credit.credit_balance)
# print query
self.execute_query(query, cursor)
conn.commit()
conn.close()
def save_from_equities(self, equities):
query_template = """insert into yahoo_equity (symbol,tradeDate,openPrice,highPrice,lowPrice,closePrice,adjClosePrice,volume) values
('{}','{}',{},{},{},{},{},{}) on duplicate key update
openPrice={},highPrice={},lowPrice={},closePrice={},adjClosePrice={},volume={}"""
conn = BaseDAO.get_connection()
cursor = conn.cursor()
for equity in equities:
query = BaseDAO.mysql_format(
query_template, equity.symbol, equity.tradeTime,
equity.openPrice, equity.highPrice, equity.lowPrice,
equity.lastPrice, equity.lastPrice, equity.volume,
equity.openPrice, equity.highPrice, equity.lowPrice,
equity.lastPrice, equity.lastPrice, equity.volume)
self.execute_query(query, cursor)
conn.commit()
conn.close()
def insert(self, records):
query_template = """insert into vix (symbol,lastPrice,priceChange,openPrice,highPrice,lowPrice,previousPrice,volume,tradeTime,dailyLastPrice,dailyPriceChange,dailyOpenPrice,dailyHighPrice,dailyLowPrice,dailyPreviousPrice,dailyVolume,dailyDate1dAgo) values ('{}',{},{},{},{},{},{},{},'{}',{},{},{},{},{},{},{},'{}')"""
conn = BaseDAO.get_connection()
cursor = conn.cursor()
count = 0
for vix in records:
query = BaseDAO.mysql_format(
query_template, vix.symbol, vix.lastPrice, vix.priceChange,
vix.openPrice, vix.highPrice, vix.lowPrice, vix.previousPrice,
vix.volume, vix.tradeTime, vix.dailyLastPrice,
vix.dailyPriceChange, vix.dailyOpenPrice, vix.dailyHighPrice,
vix.dailyLowPrice, vix.dailyPreviousPrice, vix.dailyVolume,
vix.dailyDate1dAgo)
self.execute_query(query, cursor)
count += 1
if count == 1000:
conn.commit()
count = 0
conn.commit()
conn.close()