streamlit_app.py

import streamlit as st
from snowflake.snowpark.context import get_active_session
import altair as alt
import pandas as pd

st.set_page_config(layout="wide", page_title="Stock Price Forecast Dashboard")
st.title("Multi-Stock Price Forecast Dashboard")
st.markdown(
    "Historical prices for AAPL, MSFT, GOOGL, AMZN and NVDA, with a 30-day "
    "Snowflake Cortex ML forecast that factors in upcoming FOMC meeting dates."
)

session = get_active_session()


@st.cache_data
def load_data():
    hist_query = """
        SELECT
            asset_symbol,
            trade_date,
            close_price,
            'Historical' AS data_type,
            NULL AS lower_bound,
            NULL AS upper_bound
        FROM quant_db.gold.asset_metrics
        WHERE trade_date >= DATEADD(day, -365, CURRENT_DATE())
    """
    hist_df = session.sql(hist_query).to_pandas()

    pred_query = """
        SELECT
            series::STRING AS asset_symbol,
            ts::DATE AS trade_date,
            forecast AS close_price,
            'Forecast' AS data_type,
            lower_bound,
            upper_bound
        FROM quant_db.gold.predicted_prices
    """
    pred_df = session.sql(pred_query).to_pandas()

    combined_df = pd.concat([hist_df, pred_df])
    combined_df.columns = combined_df.columns.str.upper()
    combined_df["TRADE_DATE"] = pd.to_datetime(combined_df["TRADE_DATE"])
    return combined_df


@st.cache_data
def load_fomc_dates():
    df = session.sql("SELECT decision_date FROM quant_db.bronze.fomc_meeting_dates").to_pandas()
    df.columns = df.columns.str.upper()
    df["DECISION_DATE"] = pd.to_datetime(df["DECISION_DATE"])
    return df


df = load_data()
fomc_df = load_fomc_dates()
assets = sorted(df["ASSET_SYMBOL"].dropna().unique().tolist())

st.sidebar.header("View")
view = st.sidebar.radio("Choose a view", ["Single stock", "Compare all stocks"])


def fomc_rules_for(date_min, date_max):
    visible = fomc_df[(fomc_df["DECISION_DATE"] >= date_min) & (fomc_df["DECISION_DATE"] <= date_max)]
    return alt.Chart(visible).mark_rule(strokeDash=[4, 4], color="gray").encode(
        x="DECISION_DATE:T",
        tooltip=alt.Tooltip("DECISION_DATE:T", title="FOMC decision"),
    )


if view == "Single stock":
    st.sidebar.subheader("Stock selector")
    selected_asset = st.sidebar.selectbox("Select a stock", assets)

    asset_df = df[df["ASSET_SYMBOL"] == selected_asset].sort_values("TRADE_DATE")

    st.subheader(selected_asset)
    col1, col2 = st.columns(2)

    historical_data = asset_df[asset_df["DATA_TYPE"] == "Historical"]
    if not historical_data.empty:
        col1.metric("Latest close price", f"${historical_data.iloc[-1]['CLOSE_PRICE']:.2f}")
    else:
        col1.metric("Latest close price", "Data unavailable")

    forecast_data = asset_df[asset_df["DATA_TYPE"] == "Forecast"]
    if not forecast_data.empty:
        col2.metric("30-day Cortex ML price target", f"${forecast_data.iloc[-1]['CLOSE_PRICE']:.2f}")
    else:
        col2.metric("30-day Cortex ML price target", "No forecast generated")

    chart_range = asset_df[asset_df["TRADE_DATE"] >= pd.Timestamp.today() - pd.Timedelta(days=120)]

    base = alt.Chart(chart_range).encode(x=alt.X("TRADE_DATE:T", title="Date"))

    line = base.mark_line().encode(
        y=alt.Y("CLOSE_PRICE:Q", title="Price (USD)", scale=alt.Scale(zero=False)),
        color=alt.Color(
            "DATA_TYPE:N",
            title="",
            scale=alt.Scale(domain=["Historical", "Forecast"], range=["#1f77b4", "#ff7f0e"]),
        ),
    )

    band = base.mark_area(opacity=0.2, color="#ff7f0e").encode(
        y="LOWER_BOUND:Q", y2="UPPER_BOUND:Q"
    ).transform_filter(alt.datum.DATA_TYPE == "Forecast")

    if not chart_range.empty:
        rules = fomc_rules_for(chart_range["TRADE_DATE"].min(), chart_range["TRADE_DATE"].max())
        st.altair_chart(rules + band + line, use_container_width=True)
    else:
        st.altair_chart(band + line, use_container_width=True)

    st.caption(
        "Dashed lines mark FOMC rate-decision dates - the model was trained to treat "
        "these as a feature, not a coincidence."
    )

else:
    st.subheader("All stocks, indexed to 100 at the start of the window")
    st.caption("Prices are rebased so stocks on very different price scales can be compared on one chart.")

    compare_df = df[df["TRADE_DATE"] >= pd.Timestamp.today() - pd.Timedelta(days=120)].copy()

    baseline = (
        compare_df[compare_df["DATA_TYPE"] == "Historical"]
        .sort_values("TRADE_DATE")
        .groupby("ASSET_SYMBOL", as_index=False)
        .first()[["ASSET_SYMBOL", "CLOSE_PRICE"]]
        .rename(columns={"CLOSE_PRICE": "BASELINE"})
    )
    compare_df = compare_df.merge(baseline, on="ASSET_SYMBOL", how="left")
    compare_df["INDEXED"] = compare_df["CLOSE_PRICE"] / compare_df["BASELINE"] * 100

    chart = alt.Chart(compare_df).mark_line().encode(
        x=alt.X("TRADE_DATE:T", title="Date"),
        y=alt.Y("INDEXED:Q", title="Indexed price (start = 100)", scale=alt.Scale(zero=False)),
        color=alt.Color("ASSET_SYMBOL:N", title="Stock"),
        strokeDash=alt.StrokeDash(
            "DATA_TYPE:N",
            scale=alt.Scale(domain=["Historical", "Forecast"], range=[[1, 0], [1, 4]]),
            legend=None,
        ),
    )

    if not compare_df.empty:
        rules = fomc_rules_for(compare_df["TRADE_DATE"].min(), compare_df["TRADE_DATE"].max())
        st.altair_chart(rules + chart, use_container_width=True)
    else:
        st.altair_chart(chart, use_container_width=True)
    st.caption("Solid = historical · Dotted = 30-day forecast, same color per stock.")

    st.markdown("**30-day snapshot per stock**")
    summary_rows = []
    for ticker in assets:
        tdf = df[df["ASSET_SYMBOL"] == ticker]
        hist = tdf[tdf["DATA_TYPE"] == "Historical"].sort_values("TRADE_DATE")
        fcst = tdf[tdf["DATA_TYPE"] == "Forecast"].sort_values("TRADE_DATE")
        if hist.empty or fcst.empty:
            continue
        latest = hist.iloc[-1]["CLOSE_PRICE"]
        target = fcst.iloc[-1]["CLOSE_PRICE"]
        pct_change = (target / latest - 1) * 100
        summary_rows.append(
            {
                "Stock": ticker,
                "Latest close": f"${latest:.2f}",
                "30-day Cortex ML target": f"${target:.2f}",
                "Projected change": f"{pct_change:+.1f}%",
            }
        )
    st.dataframe(pd.DataFrame(summary_rows), use_container_width=True, hide_index=True)