# Stock Research Assistant MCP Server

A comprehensive stock research assistant built with FastMCP for Databricks AI/BI integration. Enables natural-language interaction with real market data, watchlist management, company analysis, and research note tracking.

## Features

### 🎯 Watchlist Management
- Track personal watchlists of tickers
- Add/remove stocks with custom notes
- View watchlist with live prices and performance

### 📊 Market Data
- Real-time quotes from Massive Stocks API
- Historical price data and performance metrics
- Company fundamentals and profile information
- Multi-ticker comparisons

### 📰 News & Research
- Fetch and summarize recent company news
- Save research notes with investment thesis
- Set price targets and confidence levels
- Flag notable price moves automatically

### 🗄️ Data Persistence
- Lakebase Postgres backend for all user data
- Complete schema for users, watchlists, prices, news, and research
- Optimized for future semantic search capabilities

## Architecture

```
┌─────────────────┐
│  AI/BI Agent    │  ← Natural language queries
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│  FastMCP Server │  ← stocks_mcp_server.py
└────────┬────────┘
         │
    ┌────┴────┐
    ▼         ▼
┌─────────┐ ┌──────────────┐
│Lakebase │ │Massive Stocks│  
│Postgres │ │     API      │
└─────────┘ └──────────────┘
```

## MCP Tools

The server exposes 10 agent tools:

### Watchlist Tools
- `add_to_watchlist(ticker, watchlist_name, notes)` - Add a stock to your watchlist
- `remove_from_watchlist(ticker, watchlist_name)` - Remove a stock
- `get_watchlist(watchlist_name)` - View all stocks with live prices

### Market Data Tools
- `get_price_data(ticker, days)` - Current and historical prices with performance summary
- `get_company_info(ticker)` - Company fundamentals and profile
- `compare_tickers(tickers, metric)` - Compare multiple stocks

### Research Tools
- `get_recent_news(ticker, limit)` - Fetch and summarize recent news
- `save_research_note(ticker, title, content, thesis, target_price, confidence)` - Log analysis
- `flag_notable_moves(threshold_percent)` - Alert on significant price changes

### Utility
- `get_current_user()` - Get authenticated user email

## Setup Instructions

### 1. Set Up Databricks Secrets

You need two secrets:

#### Lakebase Connection URL
```bash
databricks secrets create-scope database
databricks secrets put-secret database lakebase-url
```

The value should be a standard Postgres connection URL:
```
postgresql://role:password@host:5432/databricks_postgres?sslmode=require
```

#### Massive Stocks API Key
```bash
databricks secrets create-scope massive
databricks secrets put-secret massive api-key
```

Get your API key from [Massive Stocks API](https://polygon.io/) or similar provider.

### 2. Initialize Database Schema

Run the SQL schema against your Lakebase instance:
```bash
psql <lakebase-url> < schema_stocks.sql
```

This creates all required tables:
- `users` - User profiles
- `watchlists` - Named watchlists per user
- `watchlist_tickers` - Stocks in watchlists
- `companies` - Company profiles and fundamentals
- `price_snapshots` - Historical price data
- `news_articles` - News articles per ticker
- `research_notes` - User research and theses
- `analysis_reports` - Agent-generated reports

### 3. Deploy as Databricks App

```bash
cd stocks_research_assistant
databricks apps create stock-research-assistant \
  --description "Stock Research Assistant with MCP" \
  --source-code-path .

databricks apps deploy stock-research-assistant
```

Or use the UI:
1. Go to **Databricks Apps** → **Create App**
2. Set source path to `/Workspace/Users/<your-email>/databricks-lakebase-app-day-3-hw/stocks_research_assistant`
3. Click **Deploy**

### 4. Connect to AI Playground or Agent Bricks

Once deployed, your app URL will be: `https://stock-research-assistant-<id>.aws.databricksapps.com`

#### Option A: AI Playground
- The Playground auto-discovers workspace MCP servers
- Your tools should appear automatically

#### Option B: Agent Bricks
1. Go to **Machine Learning** → **Agents**
2. Create a new agent
3. Add External Tool → MCP Server
4. URL: `https://stock-research-assistant-<id>.aws.databricksapps.com`

## Example Usage

### Natural Language Queries

**Managing Watchlists:**
```
"Add AAPL and MSFT to my watchlist"
"Show me my watchlist"
"Remove TSLA from my default watchlist"
```

**Market Research:**
```
"What's the price of NVDA over the last 30 days?"
"Compare GOOGL and META on performance"
"Show me recent news for AAPL"
```

**Investment Analysis:**
```
"Save a research note for TSLA: Bullish on FSD progress, target $350"
"Which stocks in my watchlist moved more than 5% today?"
"Get company fundamentals for AMD"
```

### Direct Tool Calls

You can also call tools directly via the MCP protocol:

```python
# Add to watchlist
{
  "tool": "add_to_watchlist",
  "arguments": {
    "ticker": "AAPL",
    "watchlist_name": "tech_giants",
    "notes": "Strong services revenue, AI potential"
  }
}

# Get price data
{
  "tool": "get_price_data",
  "arguments": {
    "ticker": "NVDA",
    "days": 90
  }
}

# Save research note
{
  "tool": "save_research_note",
  "arguments": {
    "ticker": "TSLA",
    "title": "Q4 2025 Analysis",
    "content": "Strong deliveries, margin expansion expected...",
    "thesis": "Bullish on FSD monetization and energy storage growth",
    "target_price": 350.0,
    "confidence": "high"
  }
}
```

## Database Schema

Key tables and relationships:

```sql
users
  ├── watchlists
  │     └── watchlist_tickers
  ├── research_notes
  └── analysis_reports

companies (ticker lookup)
price_snapshots (time series)
news_articles (ticker, published_at)
```

## API Rate Limits

Massive Stocks API (Polygon.io) free tier:
- 5 API calls per minute
- Delayed data (15-minute delay for stocks)

For production use, upgrade to a paid plan for:
- Real-time data
- Higher rate limits
- WebSocket streaming

## Future Enhancements

### Context Engineering (Semantic Search)
The schema includes `VECTOR` columns for embeddings:
- `companies.profile_embedding`
- `news_articles.content_embedding`
- `research_notes.content_embedding`

Enables queries like:
- "Find companies exposed to rising interest rates in regional banking"
- "Show me research notes about AI chip manufacturers"
- "Surface news about EV battery supply chain issues"

Implement with:
- Databricks sentence-transformers models
- pgvector extension in Lakebase
- Semantic similarity search

### Additional Features
- Real-time price alerts via WebSocket
- Technical analysis indicators (RSI, MACD, etc.)
- Portfolio tracking and P&L
- Earnings calendar integration
- Options data and Greeks
- Sector rotation analysis

## Troubleshooting

### "API request failed"
- Check that your Massive Stocks API key is valid
- Verify the secret is properly configured
- Check API rate limits

### "Could not retrieve Lakebase connection"
- Verify Lakebase secret is set correctly
- Test connection string manually: `psql <connection-url>`
- Check that Lakebase endpoint is running

### "No results returned for {ticker}"
- Ticker may be invalid or delisted
- Use `search_companies` tool to find correct symbol
- Some tickers may not be available on free tier

### App deployment failed
- Check logs: `databricks apps logs stock-research-assistant --tail 100`
- Verify all dependencies in requirements.txt
- Ensure app.yaml env vars match your secret scopes

## Files

```
stocks_research_assistant/
├── README.md                      # This file
├── app.yaml                       # Databricks App configuration
├── requirements.txt               # Python dependencies
├── schema_stocks.sql              # Lakebase schema
├── lakebase.py                    # Postgres connection helper
├── massive_stocks_broker.py       # Massive API client
└── stocks_mcp_server.py           # FastMCP server with tools
```

## License

MIT

## Support

For issues or questions:
1. Check the troubleshooting section
2. Review Databricks Apps documentation
3. Check FastMCP docs at https://gofastmcp.com
4. Review Massive Stocks API docs at https://polygon.io/docs