def get_stock_data(symbol: str, days: int = 300) -> DataFrame:
with session_scope() as ssn:
query = ssn.query(
StockPrice.date,
StockPrice.open_price,
StockPrice.high_price,
StockPrice.low_price,
StockPrice.close_price,
StockPrice.vwa_price,
StockPrice.volume,
StockPrice.trades,
StockPrice.deliverable_volume,
).join(
Stock, StockPrice.stock_id == Stock.id
).filter(
Stock.symbol == symbol, Stock.is_index.is_(False)
).order_by(
StockPrice.date.desc()
).limit(days).statement
df = read_sql(query, ssn.bind, index_col=["date"], parse_dates=["date"])
df = df.sort_index().replace(0, np.nan).bfill()
df["percent_deliverable"] = df.deliverable_volume / df.volume * 100
df["turnover"] = df.vwa_price * df.volume
return df
def save_predictions(predictions: Series, model_name: str, stock_symbol, as_of: date) -> None:
with session_scope(autocommit=True) as session:
stock = session.query(Stock).filter(
Stock.symbol == stock_symbol
).first()
if not stock:
raise Exception("Invalid Stock ID")
stock_id = stock.id
for timestamp, price in predictions.iteritems():
pred = StockPrediction(timestamp.to_pydatetime(), stock_id, price, model_name, as_of)
session.add(pred)
session.flush()
def get_predictions(symbol: str, from_date: date, to_date: date, model: str):
with session_scope() as ssn:
if to_date < from_date:
raise Exception("Invalid Date Range")
stock = ssn.query(Stock).filter(Stock.symbol == symbol).first()
if not stock:
raise Exception("Invalid Symbol")
stock_id = stock.id
preds = ssn.query(StockPrediction).filter(
StockPrediction.stock_id == stock_id,
StockPrediction.date.between(from_date, to_date),
StockPrediction.predictor_model == model,
).order_by(StockPrediction.prediction_as_of).all()
if not preds:
raise Exception("No prediction")
predictions = {p.date: p.predicted_price for p in preds}
return Series(predictions, name="Close")
def store_to_db(df: pd.DataFrame, stock_id: int, name: str):
for (idx, row) in tqdm(
df.iterrows(), desc=f"Storing {name} to db", unit=" rows", total=len(df)
):
stock_price = StockPrice(
date=idx,
stock_id=stock_id,
open_price=row.Open or None,
high_price=row.High or None,
low_price=row.Low or None,
close_price=row.Close or None,
volume=row.Volume or None,
vwa_price=row.get("VWAP"),
trades=row.get("Trades"),
deliverable_volume=row.get("Deliverable_Volume"),
)
try:
with session_scope(autocommit=True) as session:
session.add(stock_price)
session.flush()
except Exception as e:
logger.error(f"Error {e} while adding StockPrice for {name}. Rows {stock_price}")
print("Enter Model: ")
i = int(input()) if ch else 4
m = m_k[i - 1]
model = m_v[i - 1]
print(m)
print()
print("Start Date (YYYY-MM-DD): ")
s_d = (datetime.strptime(input(), "%Y-%m-%d").date() if ch else datetime(
2021, 5, 1).date())
print("End Date (YYYY-MM-DD): ")
e_d = (datetime.strptime(input(), "%Y-%m-%d").date() if ch else datetime(
2021, 6, 1).date())
with session_scope() as ssn:
stock = ssn.query(Stock).filter(Stock.symbol == symbol).first()
if not stock:
raise Exception("Invalid Stock ID")
stock_id = stock.id
preds = (ssn.query(
StockPrediction.date,
StockPrediction.predicted_price,
StockPrice.close_price,
).join(StockPrice,
and_(
StockPrice.stock_id == StockPrediction.stock_id,
StockPrice.date == StockPrediction.date,
def run_service():
trading_calendar = mcal.get_calendar("BSE")
for set_key, (stock_set, is_index) in stocks.items():
n = len(stock_set)
logger.info(f"Fetching {n} {set_key}\n")
for i, (name, symb) in enumerate(stock_set.items(), 1):
logger.info(f"{i}/{n} Fetching {symb}...")
try:
with session_scope(autocommit=True) as session:
if not (stock := session.query(Stock).filter(
Stock.name == name).first()):
stock = Stock(name=name,
symbol=symb,
is_index=is_index)
session.add(stock)
session.flush()
latest_date = stock.latest_date
stock_id = stock.id
except Exception as e:
logger.error(f"Error {e} while adding Stock {name} {symb}")
continue
from_date = latest_date + relativedelta(
days=1) if latest_date else date(1996, 1, 1)
to_date = date.today()
if from_date > to_date or (dates_to_fetch :=
trading_calendar.schedule(
from_date, to_date, tz=tz)).empty:
continue
if dates_to_fetch.market_close.max() + relativedelta(
hours=2) > datetime.now(tz=tz):
to_date -= relativedelta(days=1)
if from_date > to_date or (dates_to_fetch :=
trading_calendar.schedule(
from_date, to_date, tz=tz)).empty:
continue
num_attempts, cooldown_time = 3, 5
for attempt in range(1, num_attempts + 1):
countdown(cooldown_time + (attempt - 1) * 2)
logger.info(
f"\nAttempt {attempt}/{num_attempts}. Fetching {name} data from "
+
f"{dates_to_fetch.index.min().date()} to {dates_to_fetch.index.max().date()}"
)
if (df := fetch_and_store_csv(symb, csv_dirs[set_key],
is_index, set_key, from_date,
to_date)).empty:
continue
logger.info(f"Saved {symb} to file")
store_to_db(df, stock_id, name)
logger.info(f"Saved {name} to DB\n")
break