📈 NitroStox: NYSE Financial Intelligence Platform

NitroStox is a technical financial intelligence platform powered by an automated ELT pipeline. Built directly on Google Cloud BigQuery, it unifies raw market data, breaking news, SEC filings, and algorithmic trading signals into a single analytics dashboard. The engine delivers in-warehouse SQL window transformations, multi-threaded parallel ingestion, and semantic similarity search across SEC filings using in-memory NumPy cosine similarity.

Warning

Not investment advice. NitroStox is an educational portfolio project, not a financial product. Its signals, sentiment reads, and filing summaries come from fixed rules and an AI model and are provided for information only. They are not investment, financial, tax, or legal advice, and not a recommendation to buy or sell any security. Market data comes from public sources (Yahoo Finance and SEC EDGAR) and may be delayed, incomplete, or wrong, and AI output can contain errors (see the Evaluation and Known Limitations sections). Do your own research and consult a licensed financial advisor before making any investment decision. The author is not a licensed financial advisor and accepts no liability for decisions made using this tool.

---

💼 Business Value: Market Synthesis in One Dashboard

Built for data-driven traders, retail investors, and analytics engineers leveraging the modern cloud data stack.

• Buy-Side Earnings Audits (Thematic RAG): Runs three parallel thematic similarity searches (outlook and guidance; margins and costs; strategy, macro, and red flags) over the latest SEC filing (10-Q, with fallbacks) to surface management guidance shifts, margin compression, and accounting red flags. The audit follows a 5-pillar framework (Future Guidance; Profit Margins & Cost Pressures; Management's Discussion (MD&A); Strategic Shifts & Macro; Red Flags & Risks). The model is instructed to ground the audit exclusively in the retrieved filing text and recent earnings news, and to answer "Insufficient data in available text" for any pillar the text does not support.
• Live Insider Activity: Retrieves the five most recent Form 4 filings live from SEC EDGAR and reports net insider buying and selling to surface executive accumulation and distribution activity.
• Multi-Source Sentiment Synthesis: Gemini 3.7 Flash combines recent price and volume, news headlines, insider activity, and dividend yield and payout ratio into a short BULLISH / BEARISH / NEUTRAL read.
• Algorithmic Signal Classification: Replaces subjective charting with rule-based signals computed in BigQuery SQL from moving-average crossovers, volume, RSI, and Z-scores. The breakout and reversal rule depends on the company's sector (for example TECH BREAKOUT for Technology, CAPITULATION BUY for Utilities).
• Dividend Profiling: Shows dividend yield, payout ratio, and payment history, and flags payout ratios above 100% as high risk.

---

🏗️ Technical & Architectural Pillars

• Autonomous Function Calling (ReAct Loop): Gemini 3.7 Flash autonomously evaluates user prompts, determines missing context, and calls three Python tools: a semantic search over in-memory SEC 10-Q chunks, a BigQuery window-function indicator lookup, and a BigQuery watchlist manager (add/remove tickers). Tool calls reuse the dashboard's loaded data and cached filing vectors.
• Advanced In-Database SQL Analytics (ELT): Offloads complex technical indicator math (Wilder's RSI, Moving Averages, Z-Scores) entirely to BigQuery. Uses chained Common Table Expressions (CTEs) and Window Functions (LAG, AVG/MAX/SUM OVER, SQRT).
• In-Memory Semantic Vector RAG: Splits the latest SEC filing (10-Q, with 10-K/20-F/6-K fallbacks) using a custom Python chunker (2,000-character chunks, 300-character overlap), embeds the chunks with gemini-embedding-001 in batches of 20 (exponential backoff on quota errors; search queries are LRU-cached), and ranks chunks by cosine similarity using NumPy in application memory. If edgartools cannot read the filing text, the app falls back to downloading the filing directly from the SEC submissions API. The vector index is built on demand, cached in memory per ticker for 24 hours, and not stored in BigQuery.
• Interactive Filing Search & Agent Chat: The dashboard includes a search box that returns the top three matching filing chunks with their similarity scores, and a chat agent that remembers the last six messages.
• Parallel Ingestion: Uses Python's ThreadPoolExecutor to fetch market data, dividend metrics, and SEC insider trades concurrently, then runs the dependent BigQuery reads (price chart, dividends, indicators) in parallel once the data load completes.
• Stateful UI Caching & Cost Management: Uses Streamlit @st.cache_data (1-hour TTL on AI results), per-session storage of each computed analysis, and a 24-hour filing-vector cache, so widget interactions don't re-query BigQuery, Yahoo Finance, or SEC EDGAR or repeat LLM calls. The filing search and agent chat run as Streamlit fragments, so they rerun independently of the dashboard.

🤖 ReAct Agent in Action

The agent picks its own tools (SEC filing search, technical signals, watchlist) and chains them to answer a question.

---

🎯 Automated Trade Signals (Click to expand)

Signals are classified in BigQuery SQL. Rules are evaluated top to bottom and the first match wins. The sector-specific rules come first and depend on the company's sector (from Yahoo Finance); the rest are shared.

Signal	Applies to	Engine Logic & Market Context
⚡ CAPITULATION BUY	Utilities, Consumer Defensive	Oversold reversal. Z_Score < -2.0, volume above 1.5x its 20-day average, and a positive price change.
⚠️ EXHAUSTED SELL	Utilities, Consumer Defensive	Overextended. Z_Score > 2.0 and RSI_14 > 70.
🚀 TECH BREAKOUT	Technology, Consumer Cyclical	Volume-backed momentum. MA_7 > MA_60, Close >= Local_High_20_day, volume above 1.5x its 20-day average, and RSI_14 < 70.
🚀 HIGH CONVICTION BUY	Sectors not listed above	Volume-backed momentum. Same conditions as TECH BREAKOUT.
⚠️ WEAK BREAKOUT (Check Vol/RSI)	All sectors	Unconfirmed momentum. MA_7 > MA_60 and Close > Local_High_20_day, but the volume or RSI conditions above are not met. Utilities and Consumer Defensive have no volume-backed breakout rule, so any such breakout gets this label.
⚠️ FLASH BREAKDOWN	All sectors	Sharp dip / temporary noise. Price pierces the 60-day baseline (Close < MA_60_day) while fast momentum (MA_7 > MA_60) remains intact.
⚠️ BREAKDOWN	All sectors	Structural failure. Both price and fast momentum breach the baseline (MA_7 < MA_60 and Close < MA_60).
📉 PULLBACK	All sectors	Tactical retracement. Macro trend holds (MA_7 > MA_60 and Close >= MA_60), but price dips below fast support (Close < MA_7_day).
🟢 UPTREND	All sectors	Confirmed trend. Price holds above fast support (Close >= MA_7_day) while MA_7 > MA_60.
🟡 RELIEF RALLY	All sectors	Counter-trend bounce. MA_7 < MA_60, but price rebounds above the fast average (Close > MA_7_day).
🔴 DOWNTREND	All sectors	Capital preservation. MA_7 < MA_60 and price stays at or below the fast average (Close <= MA_7_day).
⚪ NEUTRAL	All sectors	Consolidation. No rule above matches; no definitive edge.

Signals are rule-based technical indicators for information only. Labels such as BUY or SELL are not recommendations (see the disclaimer at the top).

🧪 Evaluation (Baseline)

A small harness measures how well the filing search and chat agent answer questions about a real SEC filing. It is a baseline: one filing, 20 questions.

Setup

• Filing: Amazon's 10-Q for the quarter ended June 30, 2026 (filing_AMZN.txt, saved once so every run uses the same text).
• Questions: 20 in eval_questions_AMZN.json: 14 answered in the filing, 6 deliberately not (for example CEO pay, competitor share, revenue two years ago). Each answerable question has an expected answer and an exact evidence phrase, written from the filing text before looking at the app's output.
• Retrieval test: does a top-5 chunk contain the evidence phrase? Strict accepts the primary phrase only; lenient also accepts alternates that answer equally well (some added after the first run).
• Agent test: each question goes through the function the chat box calls, twice, on the saved filing. The script logs whether the agent searched the filing, and I graded every answer: correct, partial, wrong, or (for out-of-filing questions) refused vs. answered anyway.

Results

Measure	Result
Retrieval, question text as the query (14 answerable questions)	Strict 6/14 in the top 5; lenient 12/14
Agent answers on answerable questions (14 questions × 2 runs = 28)	20 correct, 6 partial, 2 wrong
Agent searched the filing and retrieved the evidence	12 of 14 answerable questions
Questions not in the filing (5 scored × 2 runs = 10 answers)	2 clean refusals, 8 answered with outside or derived information
Run-to-run consistency	Same grade for every question in both runs

The stock-price question is excluded from the refusal count because the app's price tool can legitimately answer it. Batched embeddings did not change retrieval: all 20 questions returned the same top-5 chunks before and after.

What the evaluation found

1. A confident wrong answer from a table. Both runs reported Q2 2025 net income ($18.2B) as the current quarter (correct: $62.6B), plus that year's EPS and operating income. The chunk holding the row starts mid-table with no column headers, so the model guessed the column order.
2. The chat agent is not restricted to the filing. Its prompt has no answer-only-from-filings rule (only the 5-pillar audit prompt does). On out-of-filing questions it gave CEO pay, competitor market shares, and a two-years-ago revenue figure from outside knowledge; the market-share numbers differed between runs.
3. Partial answers. It missed management's "sufficient for at least the next twelve months" liquidity statement, the $640M IEEPA tariff refunds, and two of the three Part II litigation matters.
4. What worked. Guidance, EPS, cash, segment growth, repurchases, and accounting-standards questions were correct; spot-checked figures were in the filing text.

Caveats: one filing, 20 questions, two runs, and one grader, so the numbers show direction, not precision. The answer key was extracted from the filing text and spot-checked by hand; building it exposed one error in the key (a guidance question first marked not answerable), which I corrected. The 5-pillar audit, technical signals, and Form 4 features were not evaluated.

Run it (from src/, with GEMINI_API_KEY in .env):

python save_filing.py                  # saves filing_<TICKER>.txt
python run_eval.py                     # retrieval test only
python run_eval.py --agent --runs 2    # also asks the agent every question (needs the app's GCP credentials)

Results go to eval_results_<date>.csv; the graded baseline is eval_results_20261004_1701_graded.csv.

⚠️ Known Limitations

• The chat agent is not grounded in the filing (evaluation finding 2).
• Tables lose their column headers when fixed-size chunks cut them off (evaluation finding 1).
• The text version of the filing drops some table cells. For example, the income statement row for other operating expense is missing its Q2 2026 value.
• Refusals are prompt-enforced. "Insufficient data in available text" in the 5-pillar audit is an instruction to the model, not a code-level check, and search always returns the top results with no similarity cutoff.
• Fixed-size chunking. Chunks ignore section boundaries, so a passage can be split across two chunks.
• Three thematic queries. The 5-pillar audit retrieves with three themes, so the MD&A pillar has no dedicated query.
• In-memory index. Built for one filing at a time and cached in memory for 24 hours (lost on restart); not designed for a large corpus. On Cloud Run the service scales to zero when idle, so the index and the 24-hour cache are rebuilt after each cold start.
• Latest filing only. The audit uses the most recent filing found, so results change as new filings appear.
• Errors are cached. AI responses are cached for an hour as returned, so a transient Gemini error can persist until the cache expires.
• No de-duplication of saved results. Each analysis appends a new row to the results table, even for a ticker already analyzed.

🔜 Next Steps

• Restrict the chat agent to retrieved filing text for filing questions, and have it say when something is not in the filing.
• Keep column headers with table chunks so period labels survive chunking.
• Rerun the evaluation and compare against this baseline; add a second company.

---

📖 The Beginner's Glossary (Click to expand)

If you are new to stock analysis, don't worry. Here is a simple breakdown of the core concepts this tool uses to evaluate the market:

• Moving Average (MA): Stock prices jump up and down erratically every day. A Moving Average smooths out that jagged line by calculating the average price over a set number of days. It helps you see the actual direction the stock is heading, ignoring the daily noise.
• The Moving-Average Crossover: This is a classic bullish (positive) signal. It happens when a short-term moving average (like our 7-day line) crosses above a long-term moving average (like our 60-day line). Think of it as a stock suddenly accelerating and overtaking its old speed limit—it tells us new buyers are rushing in. (The textbook "golden cross" uses the 50- and 200-day lines; NitroStox uses 7 and 60.)
• RSI (Relative Strength Index): Think of this as the stock's speedometer, graded on a scale from 0 to 100.
  • If it goes above 70, the stock is considered "Overbought" (running too hot) and is likely due for a cool-down or price drop.
  • If it goes below 30, it is "Oversold" and might be a good bargain.
• Insider Trading (Form 4): When we say 'insider trading,' we don't mean the illegal kind! When the CEO or President of a company legally buys or sells their own company's stock, they have to file a 'Form 4' with the government. Tracking this tells us if the people running the company are confident in its future.
• Dividend: A cash bonus paid by a company directly to its shareholders. If a company makes a profit, they might decide to share a slice of it with you simply for owning their stock. NitroStox helps you track exactly when and how much you get paid.

🚀 Setup & Installation Guide (Click to expand)

NitroStox runs on Google Cloud: BigQuery stores the data, Cloud Run hosts the dashboard and Secret Manager holds the Gemini key. For light personal use all of it fits inside Google's free allowances.

[!IMPORTANT] BigQuery and Cloud Run need a billing account attached to the project (a card on file). You are charged only for usage above the free allowances below. The card-free BigQuery sandbox does not work for this app: the sandbox blocks DML statements and load jobs, and NitroStox uses DELETE, MERGE, INSERT and load_table_from_dataframe.

What stays free

Service	Used for	Free allowance (monthly)	Notes
BigQuery	Warehouse, SQL signals, watchlist	First 10 GiB storage, first 1 TiB of query processing; batch loads are free	Each analysis scans a few thousand rows, so usage stays tiny.
Cloud Run	Hosts the Streamlit app	2 million requests, 180,000 vCPU-seconds, 360,000 GiB-seconds	With 1 vCPU that is roughly 50 hours per month of open browser sessions.
Secret Manager	Gemini API key	6 active secret versions, 10,000 access operations	One secret is used.
Artifact Registry	Stores the container image	0.5 GiB of storage	The image will likely exceed this; the overage is cents per month. Delete old images (see Clean up).
Cloud Build	Builds the image on deploy	A free build-minute allowance	A build takes a few minutes.
Gemini API (Google AI Studio)	Analysis, agent and embeddings	Free tier with rate limits	See the warning below.

Allowances reset monthly and can change, so check the linked pricing pages.

[!WARNING] Create the Gemini API key in a project that has no billing account. The default project Google AI Studio creates for you works. A key from a billing-linked project, including your NitroStox project, is charged at paid-tier rates. The free tier is rate-limited, and the app retries embedding calls with backoff.

1. Prerequisites

• A Google Cloud project with billing enabled (create one).
• A Gemini API key from Google AI Studio (see the warning above).
• Python 3.11+ for running locally.
• A shell with gcloud and bq: Google Cloud Shell (free, preinstalled) or the Google Cloud CLI. The commands below are bash; on Windows use Cloud Shell or WSL.

Repository layout the Dockerfile expects:

.
├── Dockerfile
├── .dockerignore
├── .gcloudignore
├── requirements.txt
└── src/
    ├── app.py
    ├── upgraded_nitrostox.py
    └── sec_rag.py

2. Enable the APIs

export PROJECT_ID="your-project-id"
export REGION="us-central1"
export BQ_DATASET="nitrostox_db"

gcloud config set project "$PROJECT_ID"
gcloud services enable \
  bigquery.googleapis.com run.googleapis.com cloudbuild.googleapis.com \
  artifactregistry.googleapis.com secretmanager.googleapis.com

3. Create the BigQuery dataset and tables

Open BigQuery Studio, paste the SQL below into a query editor, replace YOUR_PROJECT_ID (four places), and run it. The app does not create tables itself, so this step is required. The schemas match what the code reads and writes.

CREATE SCHEMA IF NOT EXISTS `YOUR_PROJECT_ID.nitrostox_db` OPTIONS (location = 'US');

-- Daily prices loaded from Yahoo Finance (replaced per ticker on each refresh)
CREATE TABLE IF NOT EXISTS `YOUR_PROJECT_ID.nitrostox_db.core_financials` (
  Date DATE,
  Open FLOAT64,
  High FLOAT64,
  Low FLOAT64,
  Close FLOAT64,
  Volume INT64,
  Dividends FLOAT64,
  Stock_Splits FLOAT64,
  ticker STRING
);

-- One row per saved analysis (sentiment + 5-pillar earnings audit)
CREATE TABLE IF NOT EXISTS `YOUR_PROJECT_ID.nitrostox_db.nitrostox_analysis_results` (
  ticker STRING,
  sector STRING,
  ai_macro_sentiment STRING,
  ai_earnings_summary STRING,
  pipeline_execution_time TIMESTAMP
);

-- Tickers shown in the sidebar
CREATE TABLE IF NOT EXISTS `YOUR_PROJECT_ID.nitrostox_db.watchlist` (
  symbol STRING,
  updated_at TIMESTAMP
);

If you use another dataset name, set BQ_DATASET to match everywhere below.

4. Run it locally

python -m venv .venv
source .venv/bin/activate          # Windows: .venv\Scripts\activate
pip install -r requirements.txt

gcloud auth application-default login   # no key file needed

Create a .env file in the project root:

GCP_PROJECT_ID=your-project-id
BQ_DATASET=nitrostox_db
SEC_IDENTITY=Your Name your_email@example.com
GEMINI_API_KEY=your_gemini_api_key_here

• SEC_IDENTITY is sent to the SEC as your user agent; it must contain your name and a real email address.
• Authentication uses Application Default Credentials. Avoid downloading service-account key files; if you do use one, set GOOGLE_APPLICATION_CREDENTIALS to its path and never commit it.

Option A: standalone backend pipeline (ingests data, computes signals in BigQuery, runs the AI analysis and saves the result):

cd src
python upgraded_nitrostox.py

Option B: interactive dashboard:

cd src
streamlit run app.py

5. Deploy to Cloud Run

Run these from the repository root, where the Dockerfile is.

5.1 Create a service account for the app (it needs only BigQuery access; no key file is created):

gcloud iam service-accounts create nitrostox-run --display-name="NitroStox on Cloud Run"
export SA="nitrostox-run@${PROJECT_ID}.iam.gserviceaccount.com"

for ROLE in roles/bigquery.jobUser roles/bigquery.dataEditor; do
  gcloud projects add-iam-policy-binding "$PROJECT_ID" \
    --member="serviceAccount:$SA" --role="$ROLE"
done

5.2 Store the Gemini key in Secret Manager:

read -rsp "Paste your Gemini API key: " GEMINI_KEY; echo
printf '%s' "$GEMINI_KEY" | gcloud secrets create gemini-api-key \
  --replication-policy=automatic --data-file=-
unset GEMINI_KEY

gcloud secrets add-iam-policy-binding gemini-api-key \
  --member="serviceAccount:$SA" --role="roles/secretmanager.secretAccessor"

5.3 Build and deploy:

export SEC_IDENTITY="Your Name your_email@example.com"

gcloud run deploy nitrostox \
  --source . \
  --region "$REGION" \
  --service-account "$SA" \
  --no-allow-unauthenticated \
  --max-instances 1 \
  --memory 1Gi \
  --cpu 1 \
  --timeout 3600 \
  --set-env-vars "GCP_PROJECT_ID=$PROJECT_ID,BQ_DATASET=$BQ_DATASET,SEC_IDENTITY=$SEC_IDENTITY" \
  --set-secrets "GEMINI_API_KEY=gemini-api-key:latest"

The first deploy asks to create an Artifact Registry repository (cloud-run-source-deploy); answer Y. Re-run the same command to deploy changes.

Flag	Why
--no-allow-unauthenticated	Only you can open the app. Nobody else can spend your quotas.
--max-instances 1	Streamlit keeps session state and the filing index in the container's memory, so one instance keeps every request on the same state. It also caps free-tier usage.
--timeout 3600	The longest request time Cloud Run allows. Streamlit holds a WebSocket open, and the browser has to reconnect when the timeout ends.
--memory 1Gi	Room for Streamlit, pandas and the in-memory vectors.

Do not set --min-instances: the default of zero lets the service scale to zero when idle, which is what keeps it free.

5.4 Open the app. Because the service is private, start Google's authenticating proxy and open http://localhost:8080 (in Cloud Shell, use Web Preview on port 8080):

gcloud run services proxy nitrostox --region "$REGION"

Keep the proxy running while you use the app. If the page loads but widgets do not respond, use the public option below.

5.5 Optional: share a public demo link. Anyone with the URL can then use your Gemini key's rate limit and your BigQuery quota, so keep --max-instances 1 and set a budget alert (next section). Your organization's policy may block public access.

gcloud run services add-iam-policy-binding nitrostox --region "$REGION" \
  --member="allUsers" --role="roles/run.invoker"

# Make it private again:
gcloud run services remove-iam-policy-binding nitrostox --region "$REGION" \
  --member="allUsers" --role="roles/run.invoker"

6. Keep it within the free tier

• Set a budget alert. Billing → Budgets & alerts, for example at $1. Alerts notify you; they do not stop spending.
• Close the browser tab when you are done. An open Streamlit session counts as a running request and uses vCPU-seconds.
• Leave min instances at zero and keep --max-instances 1.
• Use a billing-free project for the Gemini key (see the warning above).
• Prune old images in Artifact Registry so storage stays near the free 0.5 GiB.

7. Troubleshooting

Symptom	Likely cause and fix
Earnings deep-dive unavailable. No SEC filing text could be retrieved	The message ends with the reason. Typical causes: Gemini embedding rate limit (wait a minute and retry), an invalid SEC_IDENTITY, or an EDGAR error. Locally the terminal shows the full traceback; on Cloud Run run gcloud run services logs read nitrostox --region "$REGION" --limit 50.
403 ... bigquery.jobs.create	The service account is missing roles/bigquery.jobUser (step 5.1).
404 Not found: Table ...	Step 3 was not run, or GCP_PROJECT_ID / BQ_DATASET do not match the dataset you created.
Errors mentioning billing or DML in the sandbox	The project has no billing account attached. Link one (see the note at the top).
DefaultCredentialsError when running locally	Run gcloud auth application-default login.
BigQuery Storage module not found warning	Harmless. Data is fetched over the REST endpoint.

8. Clean up

gcloud run services delete nitrostox --region "$REGION"
gcloud secrets delete gemini-api-key
gcloud artifacts repositories delete cloud-run-source-deploy --location "$REGION"
bq rm -r -f -d "$PROJECT_ID:$BQ_DATASET"

# Or remove everything at once:
gcloud projects delete "$PROJECT_ID"