Watchlist site — replicate the Google Sheet as a dynamic website

1. Vision / scope ¶

Replace the Google Sheet at 1_xv9pPrxhx9A4OyhrvyTTJuKNXk8rn0m-eAWvnbdXWI with a dynamic website that shows the same data, updated live, with web-native UX (sortable columns, sparklines, color-coded ranks, mobile-friendly, dark mode). The sheet remains the source of truth for the ticker list; the site is a better viewer.

"Replicate it" = same data, same columns, same purpose. Web-native polish where it's free (sortable headers, hover details, responsive layout). No new metrics, no alerts, no notes — those are v2.

Audience: operator first, possibly a small group of viewers later. Not public-internet-anonymous (that'd need rate-limiting and a different threat model).

2. The source sheet ¶

Observed structure from the Sheets fetch:

~95 rows across equities (global / US / DM / EM / China / real estate / dividend / premium-income), fixed income (treasuries / corp / EM-debt), commodities (metals / oil / gas / agri).
13 columns: Ticker, Name, Price, 1D%, 60-Day Trend (sparkline-style), 20R, 60R, 120R (rolling ranks), Rank (0–100 composite), REL5, REL20 (relative strength), From 2025-12-31 (YTD return), Tradetime (last quote).
Multiple tabs (current gid=1224425390 is one of them).
Live data is fetched via GoogleFinance(...) formulas inside the sheet — ~20-minute delay disclaimer.

Data dimensionality: ~95 × 13 ≈ 1.2KB of structured data per snapshot. Trivial.

3. Architecture choice ¶

Three viable shapes. My recommendation up top, the alternatives below for the record.

Option A — Sheet-as-DB SPA RECOMMENDED v1

Google Sheet  ◄── operator edits / GoogleFinance computes
      │
      ▼ (Sheets API v4, every 60s browser-side fetch)
 Static SPA  ◄── deployed on Cloudflare Pages / Vercel
      │
      ▼
   Browser

Stack: a single static HTML/JS file (no build step required) or Vite + Svelte/Alpine if we want components. Reads the sheet via Sheets API v4 with an API key (sheet must be link-shared as Viewer). Renders into a sortable table with sparklines (e.g. tiny-sparkline, ~3KB) and dark-mode-by-default styling matching the design-doc skill aesthetic.

Pros: zero infra. Sheet stays the source of truth — operator edits sheet, site refreshes. Site builds and deploys in < 10 min. Cons: Sheets API has a 60 req/min/user quota — fine for one viewer, not for a viral launch. Latency = browser poll interval. API key is public (the constraint is the sheet being view-only).

Option B — Periodic ingest + DB

Cron job (existing rainier infra) fetches the sheet hourly into Postgres, then a Next.js / Streamlit app reads from Postgres. Better for adding derived columns, alerts, history. Adds a layer of state and a sync job to fail.

Defer to v2 if (a) we want history / charts beyond a 60d sparkline, (b) we add alerting, (c) viewer count grows.

Option C — Replace the sheet entirely

Direct market-data feed (yfinance / Alpaca / IBKR) → custom UI. Best data, biggest rebuild. Operator loses spreadsheet-editing ergonomics for the ticker list.

Out of scope for "replicate it". Re-evaluate if the sheet itself becomes a pain point.

4. Feature parity vs. extensions ¶

Strict replication first; extensions are explicit additions that the operator can opt into.

Feature	v1 (replicate)	v2+ (extensions)
13 columns from the sheet	✓ all 13	—
~95 rows in stable order	✓ default sort = sheet row order	save user's sort preference
60-Day Trend	✓ rendered as inline SVG sparkline	click → expand to candlestick chart
Rank coloring	✓ green ≥80, neutral, red ≤20 cells	—
1D%	✓ color: green/red, sign-aware	—
Sortable headers	✓	—
Filter by asset class	✓ pill row above table (Equities / Fixed Income / Commodities)	—
Search by ticker	✓ instant-filter input	—
Multi-tab support	v1 = single tab; pick which gid	tabs → site sections
Mobile responsive	✓ card layout below 720px	—
Dark mode	✓ auto via `prefers-color-scheme`	manual toggle
Live refresh	✓ 60s browser poll	WebSocket / SSE if migrating to Option B
History / charts	—	Option B prereq
Alerts	—	Option B prereq
Notes per ticker	—	requires write-back; ask first

5. Open questions ¶

Q1 — Auth model

Stake: The sheet is shared as Anyone with the link can view; the site uses a Sheets API key (read-only) and the site itself is public. Simplest possible.

Alternative: Site requires Google sign-in (NextAuth or similar), Sheets accessed via OAuth on the viewer's behalf. ~5× the complexity. Only worth it if the data is genuinely sensitive.

Q2 — Hosting target

Stake: Cloudflare Pages — free, fast CDN, no cold start, custom domain easy. Alternatives: Vercel (same shape), Netlify, GitHub Pages, or self-host on Fly.io.

Operator decides: any existing domain to attach? Otherwise watchlist.pages.dev works.

Q3 — Which tab(s)?

Stake: v1 ships with the current tab (gid=1224425390) only; later tabs can be added as site sections.

Question: Are the other tabs in the sheet also worth surfacing? If yes, do they share the 13-column structure or have different shapes? (Affects whether we ship one renderer or several.)

Q4 — Where does the code live?

Stake: A new repo, ~/projects/watchlist/. Reasons: this is not rainier-specific (covers global ETFs, bonds, commodities), and the deploy target / build pipeline differs from rainier.

Alternative: Sub-directory under rainier (rainier/dashboard/watchlist/) if the operator wants everything in one repo for now.

Q5 — Build stack

Stake: No build step. Single index.html + vanilla JS + AlpineJS (10KB) for reactivity. Sparklines inline-rendered as SVG. Deploys as a static file. Total size < 30KB. Sub-day to v1.

Alternative: Vite + Svelte if we know we want Option B / charts later. Adds build tooling and a package.json — but the code carries forward cleanly.

Tension: "No build step" is fastest to v1. But if v2 (Option B) is likely, starting with Svelte avoids a rewrite. Operator's "ship basic first, then iterate" pattern favors no-build.

6. Risks & non-goals ¶

Risks

Sheets API quota. 60 req/min/user. Browser polls every 60s = 1 req/min per viewer. Safe up to 50 simultaneous viewers; beyond that needs Option B or a CDN-cache layer.
Sheet structure changes. If the operator adds/removes columns in the sheet, the site's column-mapping breaks. Mitigation: column-name detection by header text, not by position.
API key exposure. The Sheets API key is embedded in client JS — anyone can read it. Mitigation: restrict the key to read-only on this specific sheet, restrict by HTTP referrer to the deployed domain. Standard pattern; not a real risk for a read-only public sheet.
20-min quote delay. Inherited from the sheet (GoogleFinance). Anyone treating this as real-time is misusing it. Mitigation: prominent "delayed ~20min" badge in the header.
Sparkline data quality. The "60-Day Trend" column in the sheet renders as a tiny chart; the underlying numeric series may not be exposed in the cell value. Mitigation: pull the 60-day series via a second sheet column or compute from a price feed.

Non-goals

Replacing GoogleFinance as the data source — that's Option C, separate decision.
Multi-user write-back (notes, favorites) — needs auth + backend; v2 if ever.
Real-time push (WebSocket) — overkill for 20-min-delayed data.
Mobile app — responsive web is enough.
Charting beyond inline sparklines — that's Option B's added value.
Alerts / notifications — separate project; rainier's Discord pipeline already exists if needed.

7. Implementation plan ¶

Once §5 questions are resolved, this becomes a single worker task. Tight scope, no design-time decisions left.

Bootstrap. Create repo / dir per Q4. Single index.html, style.css inline, app.js with the polling loop. Vendored AlpineJS or vanilla.
Sheets API plumbing. One fetch call to https://sheets.googleapis.com/v4/spreadsheets/{ID}/values/{RANGE}?key={KEY}. Parse rows into objects keyed by header.
Table renderer. Sortable columns (click header → re-sort), color-coded cells for 1D% / Rank / 20R / 60R / 120R. Asset-class filter pills. Search box.
Sparkline component. Inline SVG, derives the 60d series from a sheet column or from a separate Google Sheet range.
Responsive layout. Table on desktop, card list on mobile. Reuse design-doc skill's CSS conventions.
Live refresh. 60s setInterval repolls. Diff-aware DOM updates so the page doesn't flicker.
Deploy. Cloudflare Pages, custom domain if operator provides one.
Smoke test. Verify all 95 rows render, sort works, filter works, mobile works, dark mode works. Manual gate.

Estimated effort: 4–6 hours of worker time end-to-end.

Date	Decision	Rationale	From
2026-05-19	Replicate the Google Sheet as a dynamic website	Operator directive — better UX than the raw sheet	operator
2026-05-19	Architecture = Option A (Sheet-as-DB SPA)	Lowest infra, fastest v1, sheet stays source of truth	coord proposal

Watchlist site — replicate the Google Sheet