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 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 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_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 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()
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)