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 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 get_vix_price_by_symbol(self, symbol, remove_invalid_date=True): query_template = """select dailyDate1dAgo, dailyLastPrice from vix where symbol = '{}' order by dailyDate1dAgo""" query = BaseDAO.mysql_format(query_template, symbol) rows = self.select(query) if remove_invalid_date: rows = filter(lambda x: TradeTime.is_trade_day(x[0]), rows) return rows
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 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 get_all_margin_debt(self, start_date_str='1993-01-01'): query_template = """select {} from nyse_credit where lastDate >= str_to_date('{}', '%Y-%m-%d') order by lastDate""" columns = ['lastDate', 'margin_debt'] query = BaseDAO.mysql_format(query_template, ', '.join(columns), start_date_str) rows = self.select(query) df = pd.DataFrame(rows) df.columns = columns return df
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 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()
def get_vix_price_by_symbol_and_date(self, symbol, from_date=datetime.datetime( 1993, 1, 1), to_date=None, remove_invalid_date=True): to_date = to_date or TradeTime.get_latest_trade_date() query_template = """select dailyDate1dAgo, dailyLastPrice from vix where symbol = '{}' and dailyDate1dAgo >= str_to_date('{}', '%Y-%m-%d') and dailyDate1dAgo <= str_to_date('{}', '%Y-%m-%d') order by dailyDate1dAgo""" query = BaseDAO.mysql_format(query_template, symbol, from_date.strftime('%Y-%m-%d'), to_date.strftime('%Y-%m-%d')) rows = self.select(query) if remove_invalid_date: rows = filter(lambda x: TradeTime.is_trade_day(x[0]), rows) return rows
def __init__(self): BaseDAO.__init__(self)
def insert(self, symbol, trade_time, price): query_template = """insert into equity_realtime (symbol,tradeTime,price) values ('{}','{}',{})""" query = BaseDAO.mysql_format(query_template, symbol, trade_time, price) self.execute_query(query)