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.

Market Analysis

A deep-dive into the full investable universe. Shows every stock in the top 1,500 by market cap with all JCN factor scores side-by-side in a single searchable, sortable table. Use this page to screen across the entire market simultaneously — no individual stock lookups needed. See the Sector Analysis Module Guide section for a full breakdown of all the analytical tables on the related Sector Analysis page.

Sector Analysis

The top-down macro analysis hub. Contains 9 modules covering every dimension of sector rotation analysis: fundamental score history over 8 quarters, price momentum via ETF heatmaps and Relative Rotation Graphs, convergence signals that pair fundamental trends with price leadership, and granular breakdowns by GICS industry group. See the Sector Analysis Module Guide section below for a plain-English explanation of every module.

Sector Analysis — Module Guide

The Sector Analysis page is designed around one core question: where are the best risk-adjusted opportunities in the market right now? Each module answers a different dimension of that question. Together they give you a complete top-down view — from broad market health, to which sectors have the best fundamentals, to which sectors have the strongest price momentum, to where fundamentals and price are moving in the same direction.

1. Market Overview Cards

Eight headline numbers summarising the entire investable universe at a glance. These are computed from all ~3,000 stocks in the JCN universe, not just the ones in your portfolio.

• Total Stocks — how many stocks are currently in the investable universe.
• Sectors — number of distinct GICS sectors represented.
• Median Composite — the midpoint JCN composite score across the whole universe. 50 = exactly average. Above 50 = above-average universe quality.
• Median Quality / Momentum / Growth — same median concept per factor.
• Median YTD% — the midpoint year-to-date return across all stocks. Positive = more than half the universe is up for the year.
• % Near 52-Week High — what fraction of stocks are within 10% of their 52-week high. A high reading signals broad market strength.

2. Sector Fundamental Score History — 8 Quarters

Shows how the median JCN scores for each sector have changed over the past two years, broken into calendar quarters. This is the single most important table for identifying whether a sector's fundamentals are getting better or worse over time — not just where they stand today.

How to read it: Each row is a sector. Each column is a quarter (Q2’24 through Q1’26). The cell shows the median score for that quarter. Green cells = strong scores. Red cells = weak scores. Use the score selector buttons at the top right to switch between Composite, Quality, Growth, Momentum, Value, and Financial Strength.

• Trend arrow — computed from a linear regression across all 8 quarters. ↑↑ means the score is rising consistently, ↓↓ means consistently falling, → means flat. The number next to it (e.g. +0.4/Q) is how many points the score is moving per quarter on average.
• 8Q Delta — simple difference between today's score and the score from 8 quarters ago. A large positive number means the sector has meaningfully improved over two years.
• Click to expand — clicking any row opens an ECharts line chart showing all 6 factor scores for that sector across all 8 quarters. This lets you see which specific factors are driving the trend.

What to look for: Sectors showing consistently rising composite scores (↑ or ↑↑) with improving quality and growth are accumulating fundamental strength. Sectors showing ↓↓ trends, especially in quality or financial strength, are deteriorating fundamentally regardless of where the stock prices are.

3. Fundamental + Price Momentum Convergence Matrix

This is the highest-value analytical table on the page. It takes the 8-quarter fundamental trend from the history table and pairs it with the price momentum signal from the Relative Rotation Graph (RRG) to generate a single sector-level signal. The idea: the best opportunities come when both fundamentals and price are moving in the same direction.

The four signals:

• CONFIRM — Fundamentals improving AND price is in the Leading or Improving RRG quadrant. Both signals agree. This is the highest-conviction setup — the sector has the wind at its back from both a business quality and market momentum perspective.
• EARLY — Fundamentals improving but price momentum is still lagging or weakening. The business is getting better but the market has not yet priced it in. This is a potential early entry signal — watch for the RRG quadrant to start improving.
• DIVERGE — Fundamentals are deteriorating but price is still strong (Leading or Weakening RRG). The stock prices are running ahead of the business reality. This is a late-cycle risk warning. The price strength may not be sustainable if fundamentals continue to weaken.
• AVOID — Both fundamentals and price momentum are negative. No edge. The sector is in a confirmed downtrend on both dimensions — either reduce exposure or avoid entirely.

The vs Prior Q column shows whether the latest composite score improved or deteriorated vs the previous quarter — useful for spotting turning points in real time. The RS-Ratio column (from the RRG) shows how far above or below 100 the sector is — above 100 = outperforming SPY, below 100 = underperforming SPY.

4. Sector Score Summary

A snapshot of today's median JCN scores for all 11 GICS sectors plus a MARKET row showing the universe median. Each score is a percentile rank from 0–100 against the full ~3,000 stock investable universe. A score of 75 means the median stock in that sector is in the 75th percentile of the universe for that factor. The MARKET row is always pinned at the bottom regardless of sort order.

Click any column header to sort sectors by that score. The table shows: Composite (overall), QARP (Quality at a Reasonable Price), GARP (Growth at a Reasonable Price), Quality×Momentum, Fortress (balance sheet strength), plus the five individual factor scores.

5. Quintile Distribution

Goes deeper than the median by showing the full spread of scores within each sector. A sector might have a high median but also have extremely wide dispersion — meaning some stocks are exceptional while others are very weak. This table shows Q1 (bottom 20%), Q2, Median, Q4, Q5 (top 80%), Mean, and Standard Deviation.

Why this matters: A tight spread (low Std Dev) means most stocks in the sector score similarly — the sector is homogeneous and a sector-level call is reliable. A wide spread means there is high stock-picking opportunity within the sector — the sector average does not tell the full story.

Use the Metric selector dropdown to switch the table between Composite, Quality, Momentum, Growth, Value, Financial Strength, ROIC, Gross Margin, Net Debt/EBITDA, Quality×Momentum, and Fortress.

6. Sector Momentum Dashboard

A detailed breakdown of price momentum strength per sector, going well beyond a simple YTD return. Each column measures a different dimension of how prices are trending.

• Momentum Score — the JCN composite momentum score (percentile vs universe).
• AF Composite — Absolute/FIP momentum composite. Measures raw return strength across multiple lookback periods (3M, 6M, 9M, 12M).
• FIP 12M — Frog-in-the-Pan score. Measures the quality of the price path, not just the end return. A high FIP means returns were earned smoothly in small increments (strong trend). A low FIP means gains came in a few large jumps (weaker trend quality).
• System Score — trend linearity. How straight and consistent the price trend has been over 5 years.
• % Pos YTD — what fraction of stocks in the sector are up year-to-date. Above 50% = broad sector advance, not just a few large caps pulling up the average.
• % Above 50% Range — what fraction of stocks are in the upper half of their 52-week trading range.
• % Near 52-Week High — what fraction of stocks are within 10% of their 52-week high. A high reading = sector is broadly strong.
• Median YTD% / Median 1Yr% — actual return figures for context.

7. Industry Group Tables

Three tables mirroring the sector Score Summary, Quintile Distribution, and Momentum Dashboard — but broken down to the GICS industry group level rather than the broader sector. Industries with fewer than 5 stocks are excluded. This is useful for identifying pockets of strength or weakness within a sector. For example, within Financials: Banks may be Leading while Insurance is Lagging.

All three tables have a Sector column so you can quickly see which parent sector each industry belongs to, and sort across the full industry universe by any score or momentum metric.

8. ETF Rotation Heatmaps

Three side-by-side heatmap tables showing actual ETF price returns for sector, industry, and factor ETFs. Unlike the JCN scores which measure fundamental quality, these tables measure what the market is doing with its money right now. Columns are return periods: 1W, 1M, 3M, 6M, YTD, 1Y, 3Y, 5Y.

Color coding: Deep green = strong positive return. Deep red = strong negative return. The intensity of the color scales with the magnitude — you can immediately see at a glance which sectors and time periods are hot or cold.

Absolute vs vs SPY toggle: Switch between viewing raw returns (e.g. XLK up +12% YTD) and relative returns vs the S&P 500 (e.g. XLK up +5% vs SPY YTD). The vs SPY view shows you which sectors are actually beating the benchmark — important because in a rising market everything looks green.

Acceleration/Deceleration badge: Each ETF row shows either ↑ Accel or ↓ Decel. This compares the average of short-term returns (1W + 1M) against the average of long-term returns (3M + 6M). If recent returns are faster than the longer-term average, momentum is accelerating. If recent returns are slower, momentum is decelerating. The number next to the badge (e.g. +3.9pp) shows by how much in percentage points.

• Sector ETF Rotation — the 11 SPDR XL sector ETFs (XLK, XLV, XLF, XLY, XLP, XLE, XLI, XLB, XLU, XLRE, XLC) plus SPY, QQQ, and IWM benchmarks pinned at the bottom.
• Industry ETF Deep Dive — 35 sub-sector ETFs including SOXX (Semiconductors), IBB (Biotech), KBE (Banks), OIH (Oil Services), GLD (Gold), JETS (Airlines), and many more, each tagged with its parent sector.
• Factor ETF Rotation — 30 ETFs grouped by factor style: Momentum (MTUM, QMOM), Quality (QUAL, SPHQ), Value (VLUE, IWD, VTV), Growth (IWF, VUG), Low Volatility (USMV, SPLV), Dividend (SCHD, VYM, HDV), Small Cap (IWM, IJR, VBR), Multi-Factor (GSLC, JPUS). This table tells you which investment style the market is currently rewarding.

RS Rank — each ETF is ranked 1 (strongest) to last within its group by YTD return. In vs SPY mode the rank switches to SPY-relative YTD. The top 3 ranks are highlighted in green.

9. Relative Rotation Graph (RRG)

Created by Julius de Kempenaer, the RRG is a scatter plot that shows all 11 GICS sector ETFs in a single view, making sector rotation immediately visible. Each dot represents a sector. The dot's position tells you two things at once: how it's performing relative to SPY (x-axis) and whether that relative performance is getting stronger or weaker (y-axis). The trail behind each dot shows the path it traveled over the selected time period.

The two axes:

• X-axis — RS-Ratio: Is this sector beating SPY right now? Above 100 = outperforming SPY. Below 100 = underperforming. The further right, the stronger the outperformance.
• Y-axis — RS-Momentum: Is the outperformance getting bigger or smaller? Above 100 = the relative performance is accelerating. Below 100 = it's fading. The further up, the stronger the acceleration.

The four quadrants:

• Leading (top-right, green) — beating SPY and the outperformance is still accelerating. This is where you want to be overweight. The sector has both a strong trend AND strong momentum behind that trend.
• Weakening (bottom-right, yellow) — still beating SPY but the outperformance is starting to fade. The trend is intact but momentum is turning. Watch closely — it may rotate to Lagging soon.
• Lagging (bottom-left, red) — underperforming SPY and the gap is getting worse. Avoid or underweight. Unless you have a specific contrarian thesis, there is no edge here.
• Improving (top-left, blue) — still underperforming SPY but momentum is turning positive. This is the early recovery signal. The sector is still in a relative downtrend but the rate of deterioration is slowing and reversing. Watch for a cross into Leading.

The clockwise rotation: Sectors naturally move through quadrants in a clockwise direction over a full cycle: Leading → Weakening → Lagging → Improving → Leading. The trail length shows how far a sector has traveled. A long trail = high velocity (the sector is rotating quickly). A short trail = low velocity (stable position).

Trail length toggle: 3M (13 weekly points), 6M (26 points), or 1Y (52 points). All three are pre-loaded — switching is instant with no additional data fetch. The 6M view is usually the most useful for identifying current rotation trends without too much noise.

How it's calculated: Uses weekly adjusted-close prices for all 11 XL sector ETFs vs SPY. RS-Ratio is computed as the normalized ratio of a 10-week EMA to a 40-week EMA of the price-relative (ETF/SPY). RS-Momentum is the rate of change of RS-Ratio over 4 weeks, also normalized. Both axes are scaled so that 100 = exactly in line with SPY and the standard deviation is approximately 10 points, making sectors comparable on the same scale.

Using RRG with the Convergence Matrix: The RRG quadrant is one of the two inputs to the Convergence Matrix signal. When you see a sector moving from Improving into Leading on the RRG while the Fundamental History table shows a rising composite score trend, the Convergence Matrix will generate a CONFIRM signal — both dimensions aligned.

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