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)