Overview

What is JCN Financial?

JCN Financial is a production-grade investment dashboard that provides real-time portfolio tracking, deep fundamental stock analysis, and institutional-quality data infrastructure. Built for serious investors who need survivorship-bias-free data, point-in-time accuracy, and comprehensive fundamental screening across the top 1,500 US equities by market capitalization.

The platform manages three distinct investment portfolios (Persistent Value, Olivia Growth, Pure Alpha), each with unique strategies, and offers a full Stock Analysis screener with 10 analytical modules covering price performance, per-share data, quality metrics, financial statements, growth rates, valuation, and composite quality scoring.

Technology Stack

Architecture

The application follows a serverless architecture deployed on Vercel. The Next.js frontend communicates with Python FastAPI serverless functions via /api/* routes. All market data lives in MotherDuck (cloud DuckDB), organized into production tables under thePROD_EODHD database.

Browser (React)
  └─ Next.js SSR / Client
       └─ /api/* routes (Vercel Serverless)
            └─ FastAPI (Python)
                 ├─ Cache: /tmp (Vercel ephemeral)
                 └─ MotherDuck (DuckDB Cloud)
                      ├─ PROD_EODHD (production)
                      └─ DEV_EODHD_DATA (staging)

Dashboard Pages

Dashboard (Heatmap + Charts)

The main dashboard page features a full-width TradingView stock market heatmap showing real-time sector and stock performance with color-coded daily percentage changes. Above the heatmap, three equally spaced TradingView Advanced Chart widgets display daily candle charts for SPY (S&P 500), QQQ (Nasdaq 100), and ACWI (All Country World Index) — providing instant macro context. Charts use white backgrounds with volume bars hidden for a clean presentation.

Persistent Value Portfolio

A 21-holding portfolio focused on high-quality, durable compounders with strong competitive moats, consistent free cash flow generation, and long-term capital appreciation. Holdings include ASML, COST, AVGO, MA, FICO, SPGI, V, AAPL, NFLX, and others. Uses SPMO as the benchmark ETF.

Features: real-time performance table with YTD/1Y/3Y/5Y returns, market-cap-weighted allocation pie chart, SPY benchmark comparison with alpha calculation, historical price charts, JCN 5-factor score grid, and quality radar charts. All data loads from MotherDuck with 4-layer cache.

Olivia Growth Portfolio

A 20-holding growth-oriented portfolio emphasizing high-momentum technology and infrastructure names. Holdings include GOOG, AMZN, META, NVDA, AMD, CRWD, PLTR, SHOP, and others. Uses QGRW as the benchmark ETF. Identical architecture to Persistent Value.

Pure Alpha Portfolio

A concentrated 10-holding high-conviction portfolio designed for maximum alpha generation. Holdings: GEV, GOOG, NVDA, TSLA, PWR, AXON, LRCX, MELI, MU, NFLX. Benchmarked directly against SPY.

Stock Analysis Screener

Deep single-stock fundamental analysis page with 10 analytical modules. Limited to the top 1,500 US stocks by current market capitalization. Features smart search with autocomplete, recent search history pills (localStorage, max 10), and animated progress bar.

Default symbol: NVDA (loaded automatically). All modules always visible. Data fetched in a single API call and cached 30 minutes in both browser and server.

Stock Screener

A FinViz-style stock screener that filters the ~3,000 stock investable universe using preset dropdown filters — no manual value input required. Organized into 7 filter tabs: Descriptive (market cap, sector), JCN Scores (5 factor composites + 8 blend composites), Valuation (P/E, PEG, P/B, P/S, EV/EBITDA, dividend yield), Growth (revenue and earnings growth), Profitability (margins, ROE, ROA), Momentum (daily/YTD/YoY changes, beta, and all AF/FIP/System momentum sub-components), and Fundamentals (debt/equity, current ratio, interest coverage).

Results display in a TanStack Table v8 with full sorting on any column, a column picker to show/hide fields, and CSV export. Right-clicking any cell opens a context menu with three options: Analysis (opens the stock in Stock Analysis in a new tab), Add to Watchlist (saves to localStorage), and Grok (placeholder for future AI analysis). Filter and table state are persisted in sessionStorage so navigating away and back preserves the screener exactly as the user left it.

The screener API (POST /api/screener) uses a dynamic SQL query builder with a whitelisted field map to prevent SQL injection. It JOINs across 8 MotherDuck tables using inline subqueries (not CTEs, which fail on MotherDuck). Results are cached in /tmp with a 5-minute TTL. The API is 100% read-only and never writes to any production table.

Watchlist

A personal watchlist page where users can track stocks of interest. Symbols can be added manually via a ticker input field or from the Screener context menu. The watchlist is stored in localStorage (no database writes required) and syncs across components via a custom watchlist-change event.

The watchlist table displays enriched data fetched from the screener API: company name, sector, market cap, price, daily/YTD/YoY percentage changes, and five JCN factor scores (Value, Quality, Growth, Momentum, JCN Full Composite). Users can sort by any column, remove individual symbols, clear all with a confirmation dialog, export to CSV, or open any symbol in Stock Analysis via a new-tab link. An empty state guides new users to add symbols from the input field or the Screener page.

Data Sync Pipeline

A 4-stage automated data pipeline that ingests daily EOD prices from EODHD, validates data quality, promotes to production, and rebuilds composite scores. Each stage is idempotent and safe to re-run. See the Sync Pipeline section for details.

Portfolio Metrics

Performance Table

Each portfolio page displays a performance table showing every holding with current price, daily change, and period returns. Prices come from EODHD real-time API (15-min delayed) or MotherDuck historical data.

Allocation

Portfolio allocation is displayed as a pie chart, categorized by market cap tier: Mega Cap (above $200B), Large Cap ($10B-$200B), Mid Cap ($2B-$10B), Small Cap (below $2B), and ETF. Allocation weights are equal-weight by default.

Benchmarks and Alpha

Each portfolio is benchmarked against SPY (S&P 500 ETF) and a portfolio-specific ETF. Alpha is calculated as the excess return of the portfolio over SPY for each time period.

Alpha = Portfolio Return - SPY Return

JCN Factor Scores

Each stock in a portfolio receives five composite factor scores (0-100), computed monthly from fundamental data and stored in dedicated score tables in MotherDuck. These scores power the radar charts and fundamentals grid on each portfolio page.

score = round((clamped_value - low) / (high - low) * 100)

Stock Analysis Metrics

Every metric displayed in the Stock Analysis screener is computed server-side from PROD_EOD_Fundamentals quarterly data, aggregated into annual figures. P&L and Cash Flow items are summed across quarters; Balance Sheet items use the latest quarter in each calendar year.

Per Share Data (Module 3)

Annual per-share metrics calculated by dividing aggregate financials by shares outstanding. Shares use bs_commonStockSharesOutstanding (quarterly historical), falling back to shares_outstanding (snapshot) if unavailable.

Quality Metrics (Module 4)

Annual quality ratios measuring profitability, returns on capital, leverage, and operational efficiency. All computed from aggregated annual data.

Financial Statements (Modules 5-7)

Income Statement, Balance Sheet, and Cash Flows are displayed as hierarchical tables with expandable parent-child rows. All values are shown in millions ($M). Quarterly data is aggregated into annual: P&L and Cash Flow items are summed, Balance Sheet items use the latest quarter.

Revenue
  └ Cost of Revenue
Gross Profit
Operating Expenses
  ├ R&D
  ├ SG&A
  └ D&A
Operating Income
Interest Expense
Income Before Tax
Income Tax
EBITDA
Net Income

Total Assets
  ├ Current Assets
  │   ├ Cash & Equivalents
  │   ├ Short-Term Investments
  │   ├ Net Receivables
  │   └ Inventory
  └ Non-Current Assets
      ├ PP&E
      ├ Goodwill
      ├ Intangibles
      └ Long-Term Investments
Total Liabilities
  ├ Current Liabilities
  │   ├ Accounts Payable
  │   └ Short-Term Debt
  └ Non-Current Liabilities
      └ Long-Term Debt
Stockholder Equity
  ├ Common Stock
  ├ Retained Earnings
  └ Treasury Stock
Net Debt
Shares Outstanding

Operating Cash Flow
  ├ Net Income
  ├ D&A
  ├ Stock-Based Comp
  └ Working Capital Changes
Investing Cash Flow
  ├ Capital Expenditures
  └ Investments
Financing Cash Flow
  ├ Dividends Paid
  ├ Share Buybacks/Issuance
  └ Net Borrowings
Free Cash Flow
Net Change in Cash

Growth Rates (Module 8)

Year-over-year growth rates for 12 key financial metrics. Computed as percentage change from the prior year. Displayed with heatmap coloring: deep green for high growth, red for contraction, gray for null.

growth_pct = (current_year - prior_year) / abs(prior_year) * 100

Tracked metrics: Revenue, Gross Profit, Operating Income, EBITDA, Net Income, EPS, Free Cash Flow, Operating Cash Flow, Total Assets, Stockholder Equity, Long-Term Debt, Dividends Paid.

Valuation Ratios (Module 9)

Current snapshot valuation multiples from EODHD. These are point-in-time values (not historical per quarter) and represent the latest available data for each stock.

JCN Factor Scores (Module 10)

Five precomputed composite factor scores fetched from PROD score tables (computed monthly). Each score is 0-100. The JCN Composite is the simple average of all five factor scores. Displayed as score cards and a 5-axis radar chart.

jcn_composite = (value + quality + financial_strength + growth + momentum) / 5

JCN Score Methodology

The JCN scoring system evaluates every stock in the investable universe across five fundamental dimensions: Value, Quality, Growth, Financial Strength, and Momentum. Each factor is computed monthly using a proprietary 3-way scoring system that combines cross-sectional, sector-relative, and historical self-relative rankings. The five individual scores are then blended into eight composite presets for different investment strategies.

Investable Universe

Scores are computed only for stocks in the JCN Investable Universe — the top 3,000 US equities by market capitalization, reconstituted annually on the last trading day of May. This mirrors the Russell 3000 reconstitution methodology used by FTSE Russell.

3-Way Scoring System

Each of the five factor scores uses three independent scoring dimensions, combined into a single composite. This multi-lens approach prevents a stock from scoring well simply because its entire sector is cheap or because it happens to be in a historically low-quality industry.

composite = Universe Score (40%) + Sector Score (40%) + History Score (20%)

Fallback rules: if a sector has fewer than 3 peers, weights shift to Universe 60% + History 40%. If historical data has fewer than 8 quarters, weights shift to Universe 50% + Sector 50%.

Value Score

Measures how attractively priced a stock is relative to its fundamentals. Lower valuation ratios produce higher scores (inverted ranking). The weighting emphasizes cash-flow-based measures over accounting earnings.

P/FCF receives the highest weight because free cash flow is the purest measure of economic value available to shareholders. Unlike earnings, FCF is difficult to inflate through accounting choices. EV/EBITDA is capital-structure-neutral, allowing fair comparison between levered and unlevered firms — the standard metric in M&A valuation. P/E is included for universality but downweighted due to susceptibility to one-time charges and accounting distortions. The value factor draws directly from the HML (High Minus Low) factor identified by Fama & French (1992).

Quality Score

Measures business quality through profitability, capital efficiency, and earnings reliability. Higher values indicate better quality. The weighting emphasizes asset-level profitability measures that academic research has shown to be the most predictive of future returns.

The Earnings Quality metric is a Sloan (1996) accruals-based signal: companies where cash flow consistently trails reported earnings tend to have lower future returns, as the accrual component eventually reverses. GPA at 20% weight follows Novy-Marx (2013), who showed gross profitability scaled by assets has strong return-predictive power and is negatively correlated with value — making it an excellent complement in a multi-factor model.

Growth Score

Measures multi-period earnings, revenue, and cash flow growth using blended compound annual growth rates (CAGRs). Equal-weighted across four per-share growth metrics, each blending three lookback periods to balance recent acceleration with durable compounding.

Blended = 1Y CAGR (40%) + 3Y CAGR (35%) + 5Y CAGR (25%)

All growth metrics are computed on a per-share basis to penalize companies that grow revenue by diluting shareholders through stock issuance. The per-share normalization ensures that only genuine organic growth (or efficient capital deployment) is rewarded.

Financial Strength Score

Measures balance sheet health, debt serviceability, and liquidity. Equal-weighted across six metrics covering both stock measures (balance sheet ratios) and flow measures (coverage ratios). Draws from Altman Z-score principles (Altman, 1968) and Piotroski F-score methodology (Piotroski, 2000).

Equal weighting ensures no single dimension (leverage, liquidity, or coverage) dominates the score. The mix captures both point-in-time balance sheet health and ongoing cash flow adequacy. Companies with zero debt naturally score high on debt-related metrics but still need strong liquidity and working capital to score well overall.

Momentum Score

Measures price trend strength and persistence from weekly return data. Unlike the other four factor scores which use monthly fundamental data, Momentum is computed weekly from adjusted closing prices.

SystemScore = (5yr_avg_annual_return) * (R_squared ^ 3)

Weights are conditional: if a component is unavailable for a stock (e.g., less than 5 years of data for SystemScore), remaining component weights are re-normalized. The momentum score also uses the 3-way scoring system (Universe 40% + Sector 40% + History 20%) for the final composite.

Composite Blends

Eight pre-configured factor blend presets are stored in PROD_JCN_Composite_Scores. Each is a weighted average of the five individual factor composite scores. When a factor is unavailable for a given stock, the remaining factor weights are re-normalized so they still sum to 100%.

blend_score = SUM(weight_i * factor_i) / SUM(weight_i for non-null factors)

References

The JCN scoring methodology draws from decades of peer-reviewed financial economics research. Key references:

Database & Schema

MotherDuck

MotherDuck is a cloud-hosted DuckDB service that provides the analytical warehouse for all JCN data. Single-writer architecture (concurrent writes fail silently). Connection via MOTHERDUCK_TOKEN environment variable. All symbols stored in TICKER.US format (e.g., AAPL.US).

-- Connection pattern
conn = duckdb.connect(f'md:?motherduck_token={token}')

-- Database layout
PROD_EODHD.main.*        -- Production tables (read by dashboard)
DEV_EODHD_DATA.main.*    -- Staging tables (written by sync pipeline)

PROD_EOD_survivorship

The primary price table. Contains daily OHLC + adjusted_close for all US common stocks and ADRs, including delisted symbols (survivorship-bias free). Over 121 million rows. Zone-map optimized by (date, symbol).

PROD_EOD_ETFs

Daily OHLC + adjusted_close for ETFs. SPY.US is the primary benchmark. Same structure as survivorship but without survivorship metadata columns.

PROD_EOD_Fundamentals

Quarterly fundamental data for all US stocks. 196 columns covering income statement (is_*), balance sheet (bs_*), cash flow (cf_*), valuation snapshots, analyst estimates, and company metadata. Over 160 quarters of history for major stocks.

Important: Valuation fields (market_cap, pe_ratio, etc.) are SNAPSHOT values — identical across all quarters for a given symbol. They represent the CURRENT latest value, not historical. Only financial statement columns (is_*, bs_*, cf_*) contain true historical quarterly data.

Score Tables

Five separate score tables, each containing monthly composite scores per symbol. Rebuilt during Stage 3 of the sync pipeline. Used by portfolio pages for the JCN 5-factor score grid and radar charts.

SYNC_STATE & SYNC_LOG

SYNC_STATE stores the current state of each sync stage (last run timestamp, status, cursor position for resumable operations). SYNC_LOG records every sync run with timing, row counts, and error details. Both tables enable the Prime Directive PD-08 (cursor persistence) and provide audit trail.

EODHD API

API Overview

EODHD (End of Day Historical Data) is the primary external data provider for JCN Financial. It supplies daily end-of-day prices, quarterly fundamentals, and company metadata for all US-listed securities including common stocks, ADRs, and ETFs. Data is point-in-time accurate using SEC filing dates.

Endpoints Used

Rate Limits

EODHD enforces rate limits of 1,000 API calls per minute and 100,000 calls per day. The sync pipeline uses bulk endpoints to minimize call count. Real-time price fetches for portfolios are throttled to one refresh per 15 minutes (client-side localStorage cache) and only trigger on manual refresh button click or TTL expiry.

delay = max(0.06s per call, 60ms between requests)

Data Sync Pipeline

The sync pipeline is a 4-stage process that maintains the production database. Each stage is independently runnable, idempotent, and designed to operate within Vercel Pro 300-second function timeout (280s budget with 20s safety buffer).

Stage 0: Health Check (diagnostics)
    ↓
Stage 1: Ingest (EODHD → DEV)
    ↓
Stage 2: Validate & Promote (DEV → PROD)
    ↓
Stage 3: Score Rebuild (recompute composites)

Stage 0: Health Check

Runs 8 diagnostic checks before any sync operation: (1) MotherDuck connectivity, (2) EODHD API key validation, (3) Required tables existence with estimated row counts, (4) Last sync timestamp from SYNC_STATE, (5) Symbol format consistency (PD-03 — all must be TICKER.US), (6) Fundamentals coverage ratio, (7) Gap analysis (latest date vs today), (8) Duplicate detection in last 7 days. Uses sampled queries and LIMIT 1 patterns for speed. Results cached 5 minutes.

Stage 1: Ingest

Downloads daily EOD prices for all US symbols via EODHD bulk endpoint (/api/eod-bulk-last-day/US). Separates stocks from ETFs based on exchange code. Validates inline (filters out mutual funds, OTC, warrants per PD-06). Writes to DEV_EODHD_DATA staging tables. Idempotent — skips dates already ingested. Tracks cursor in SYNC_STATE for resumability.

Stage 2: Validate & Promote

Four phases: (Phase 1) 6-point data quality audit on DEV — NULL checks on critical columns, duplicate detection, row count validation, date continuity, price reasonableness, symbol format compliance. (Phase 2) DEV to PROD promotion via ANTI JOIN (only new rows inserted, never overwrites). (Phase 3) Incremental Weekly OHLC rebuild. (Phase 3.5) Dashboard snapshot rebuild. (Phase 4) Post-validation + SYNC_LOG entry.

1. NULL check on critical columns (symbol, date, adjusted_close)
2. Duplicate detection (same symbol+date in last 7 days)
3. Row count validation (≥95% of expected rows)
4. Date continuity (no gaps > 3 business days)
5. Price reasonableness (no negative prices, no > 1000% daily moves)
6. Symbol format compliance (all must end in .US)

Stage 3: Score Rebuild

Recomputes all 5 JCN composite factor scores (Value, Quality, Growth, Financial Strength, Momentum) for 8 different presets. Reads from PROD tables, applies scoring algorithms, and writes results to the 5 PROD_OBQ_*_Scores tables. Approximately 4.48 million score rows generated.

Prime Directive v1.0

Eight immutable rules governing all data operations in the sync pipeline. These rules cannot be overridden and are enforced at every stage.

Caching Architecture

4-Layer Cache

JCN uses a multi-layer caching strategy to minimize latency and API calls. Each layer has specific TTLs and invalidation rules.

TTL Strategy

Cache TTLs are designed around data freshness requirements. Portfolio performance data (24hr TTL) only needs daily refresh since it uses EOD prices. Live prices (15min TTL) balance freshness with API rate limits. Stock analysis data (30min TTL) represents quarterly fundamentals that change infrequently.

// Cache hierarchy — check in order, return first hit:
1. localStorage (instant)  → if valid TTL, return
2. /tmp file cache (instant if warm)  → if same day, return
3. PROD_DASHBOARD_SNAPSHOT (~200ms)  → pre-computed row, return
4. Legacy 5-CTE query (2-4s)  → full DB query, return + cache
5. EODHD API (500ms)  → live prices only, on manual refresh