Opportunityiq Mvp Scanner

For detailed guidance on specific topics, consult these reference files:

• Data Structure: See data_structure_guide.md for comprehensive guide on handling multi-source data with client identifiers
• Classifications: See expanded_classifications.md for 15+ classification categories with detailed explanations
• Implementation: See implementation_guide.md for step-by-step deployment roadmap
• Product Reference: See product_reference_system.md for maintaining product/carrier reference tables
• Decision Guide: See decision_guide.md for choosing between different approaches
• Starter Scenarios: See starter_scenarios.md for 12 fully-completed scenario examples

Templates

Use these templates for creating new scenarios:

• Excel Template: scenario_template.xlsx - Excel workbook with Scenario Library, Classification Reference, and Example Scenarios
• Markdown Template: scenario_template.md - Markdown version optimized for LLM processing

Process Workflow

Step 1: Load & Validate Data

# Load Google Sheets data
client_master = load_sheet(url, 'Client_Master')
products = load_sheet(url, 'Products')
holdings = load_sheet(url, 'Holdings')

# Validate required columns exist
required_columns = {
    'Client_Master': ['Client_ID', 'Client_Name', 'Total_Portfolio', 'Cash_Balance', 'Cash_Yield'],
    'Products': ['Client_ID', 'Product_Type', 'Current_Value', 'Surrender_End_Date', 'Cap_Rate'],
    'Holdings': ['Client_ID', 'Current_Value', 'Percent_of_Portfolio']
}

# Report any missing data

Step 2: Match Scenarios

Scenario 1: FIA Surrender Ending

Criteria:

• Product_Type = 'FIA'
• Surrender_End_Date within 12 months from today
• Cap_Rate < 5.5%

Revenue Calculation:

revenue = product['Current_Value'] * 0.05  # 5% commission

Output Template:

Client: {client_name}
Type: FIA Replacement Opportunity
Details: {product_name} FIA with ${current_value:,} approaching surrender end in {months} months. Current cap rate of {cap_rate} is below market average of 5.5-6.5%.
Revenue Estimate: ${revenue:,}
Action: Schedule review meeting to discuss current product performance and illustrate replacement options with higher crediting rates.

Scenario 2: Cash Drag

Criteria:

• Cash_Balance > $50,000
• Cash_Yield < 3.0%

Revenue Calculation:

revenue = client['Cash_Balance'] * 0.01  # 1% AUM fee

Output Template:

Client: {client_name}
Type: Cash Repositioning Opportunity
Details: ${cash_balance:,} in cash earning {cash_yield}. Money market funds currently yielding 4.5-5.5%.
Revenue Estimate: ${revenue:,} annually
Action: Quick call to reposition to higher-yielding money market or short-term treasury fund.

Scenario 3: Concentrated Position

Criteria:

• Any single position > 20% of portfolio
• Total_Portfolio > $500,000
• Accredited_Investor = 'Yes'

Revenue Calculation:

# Assume 30% of concentrated position can be repositioned
reposition_amount = position['Current_Value'] * 0.30
# Alternative investment typically 5% upfront
revenue = reposition_amount * 0.05

Output Template:

Client: {client_name}
Type: Concentration Risk / Diversification
Details: {ticker} position of ${position_value:,} represents {percentage}% of portfolio. Significant single-stock concentration risk.
Revenue Estimate: ${revenue:,}
Action: Schedule portfolio review to discuss diversification into alternative investments or sector diversification.

Step 3: Consolidate & Rank

# Collect all opportunities
all_opportunities = []
all_opportunities.extend(fia_opportunities)
all_opportunities.extend(cash_opportunities)
all_opportunities.extend(concentration_opportunities)

# Deduplicate: One opportunity per client (keep highest revenue)
client_best_opp = {}
for opp in all_opportunities:
    client_id = opp['client_id']
    if client_id not in client_best_opp:
        client_best_opp[client_id] = opp
    elif opp['revenue'] > client_best_opp[client_id]['revenue']:
        client_best_opp[client_id] = opp

# Sort by revenue (highest first)
sorted_opportunities = sorted(
    client_best_opp.values(),
    key=lambda x: x['revenue'],
    reverse=True
)

Step 4: Generate Report

Report Format:

═══════════════════════════════════════════════════
  OPPORTUNITYIQ MVP SCAN RESULTS
═══════════════════════════════════════════════════

Generated: {current_date_time}
Clients Scanned: {total_clients}
Opportunities Found: {total_opportunities}
Total Revenue Potential: ${total_revenue:,}

───────────────────────────────────────────────────

OPPORTUNITY #1 - ${revenue:,}

Client: {client_name} ({client_id})
Type: {scenario_name}

Details:
{detailed_description}

Recommended Action:
{next_steps}

───────────────────────────────────────────────────

[Repeat for each opportunity]

───────────────────────────────────────────────────

SUMMARY BY TYPE:
• FIA Replacement: {fia_count} opportunities - ${fia_total:,}
• Cash Repositioning: {cash_count} opportunities - ${cash_total:,}
• Concentration/Diversification: {conc_count} opportunities - ${conc_total:,}

PRIORITY ACTIONS:
1. High Priority (>$5,000): {high_priority_count} opportunities
2. Medium Priority ($2,000-$5,000): {medium_priority_count} opportunities
3. Quick Wins (<$2,000): {quick_win_count} opportunities

NEXT STEPS:
□ Review each opportunity for accuracy
□ Prioritize top 3-5 for immediate outreach
□ Note any false positives to refine criteria
□ Decide: Expand to more scenarios or scale client base?

Error Handling

Handle common data issues gracefully:

# Missing data
if 'Surrender_End_Date' not in products.columns:
    print("⚠️ Warning: Surrender_End_Date not found in Products sheet. Skipping FIA scenario.")

# Invalid dates