Portfolio analytics: daily price series, split the mega-prompt, ground the analysis in computable evidence

[warlock20]

Problem

The portfolio analytics page (/portfolio/analytics, portfolio_basic_analytics.html) is meant to turn years of the user's own transactions into a judgement about how they invest. Two things stop it from doing that.

1. The Performance vs Cost chart recomputes on almost every view

Root causes, in order of severity:

• The price cache can never hit. cached_provider.py:158-176 walks every calendar day in the range and marks each uncached day as missing. Weekends and holidays have no close price, so missing_dates is never empty and Yahoo is called for the full range on every run regardless of how warm the cache is.
• The cache is files on ephemeral disk. cached_provider.py:62 writes to instance/cache/historical_prices/. Railway wipes this on every deploy, so in production there is effectively no cache at all.
• Cache key is per period. routes.py:347 keys on chart_data:{months}. Switching 1M → 3M → 12M is three separate cold computes.
• 24h TTL on the whole blob. routes.py:360 — expiry recomputes all 12 months including immutable past.
• Month-end granularity only — 12 points for a year.
• No currency conversion. calculate_performance_chart_data sums native-currency prices, so a EUR holding is added straight to a USD one. The app has a full base-currency system (total_cost_base, ExchangeRate per date) that the chart ignores.
• Silent distortion on missing prices. When no price is found the code substitutes cost basis, which flattens that holding's contribution without telling anyone.

2. One 65k-token prompt produces six unrelated sections

portfolio_raw_trade_analysis.yaml is a single call producing health assessment, key findings, behavioral patterns, evolution timeline, repeating mistakes and FOMO analysis together. Any refresh regenerates all of it, including sections the user never opens.

Separately, the output is weak because the prompt asks for things the model cannot know. It requests detection of "social media buzzes, youtube videos released during the time of purchase, influencer endorsements" (line 77). The model has no access to any of that, so it confabulates rather than detects. This is the root of the low-value output, not the prompt structure.

Direction

Daily price series, derived on read

• New shared daily_price(ticker, date, close, currency) table, unique on (ticker, date), append-only — history is immutable. ~75k rows for 30 tickers over 10 years.
• PriceHistoryService syncs incrementally: read MAX(date), fetch only [max+1, today]. Coverage checks become range-based rather than per-calendar-day, which removes the weekend cache-miss permanently.
• Repoint CachedFinancialDataProvider at this table instead of JSON files, so the cache survives restarts.
• Nightly Celery beat sync at 22:00 UTC, alongside the two existing daily jobs in celery_app.py:49.
• PortfolioSeriesBuilder derives the curve on read: replay transactions into a per-ticker quantity/cost step function, build the trading-day calendar from the dates present in daily_price, forward-fill, and convert to the user's base currency per date via ExchangeRate.
• GET /portfolio/api/chart/performance?range=… returns JSON synchronously from the DB with no network access. Target p95 < 150ms at 5y × 30 tickers.
• Deriving on read means it is self-invalidating — editing a back-dated transaction corrects the curve immediately, with no cache to bust.
• Removes portfolio_chart_data_task, start_chart_data_task, _get_cached_chart_data, calculate_performance_chart_data, chart_data:* task rows and CHART_DATA_CACHE_TTL_HOURS.

Nothing blocks page load

The server renders the page shell with zero blocking work; every section hydrates independently. The chart endpoint never touches the network — it reads daily_price only, and when coverage is short it enqueues a one-time backfill and returns immediately with whatever exists. A section still loading must never prevent the rest of the page from being read.

Coverage rule: a ticker only needs price data on dates it was actually held, so the series starts at the earliest date on which every then-held ticker has coverage.

Split the prompt into six independent modules

portfolio_health_assessment, portfolio_behavioral_patterns, portfolio_evolution, portfolio_fomo_analysis, plus the existing sector_momentum_analysis and tax_optimization_analysis.

Each gets its own prompt, portfolio_analysis:<name> task type, cached result with its own timestamp, and its own lifecycle. This extends the pattern already working in analytics_run_module and _get_cached_module_analysis — ALLOWED_ANALYSIS_TEMPLATES grows by four.

The shared analysis instructions (averaging down, redemption arcs, falling-knife vs value investing, value-investor vs momentum-trader) move into a common YAML fragment so the four modules cannot drift apart.

Cadence is monthly, not daily. Behavioural patterns do not change day to day. A module is eligible for refresh when older than 30 days or when transactions changed since it ran. Nothing auto-fires; each analysis displays the date it was produced.

Existing portfolio_raw_trade_analysis results stay readable during migration so no one loses their last analysis on deploy.

Ground the analysis in computable evidence

Once daily_price exists, the behavioural evidence becomes arithmetic instead of guesswork. A deterministic evidence pack (no tokens) computes per transaction:

• trailing 30d/90d return before each buy and distance from the 52-week high on that date — this is literally what "chasing a spike" means
• forward 30d/90d return after each buy — whether it worked
• drawdown from peak at each exit and forward return after — disposition effect, capitulation vs conviction
• position size distribution across entries — averaging-down and "redemption arcs" become exact numbers

The model's job shrinks to interpreting numbers rather than inventing them.

Cache market context per company

New company_market_context(company_id, window_start, window_end, summary, events) table, month granularity, each event carrying {headline, published_at, source_url, event_type}. Fetched via the Google Search grounding already wired at gemini.py:191-193 and already used in tasks_research.py:490.

Only fetched for (company, month) pairs where the user actually transacted — roughly 40–80 windows for a typical portfolio, one time, then cached permanently since news history is immutable. Reusable across research pages, checkpoint analysis, Argos and sector work, which is where most of the value accrues.

Hindsight guard — this decides whether the feature helps or hurts. Retrospective coverage is written by people who already know the outcome, so unfiltered it would label every losing trade FOMO and every winner conviction, with citations. Every event must carry published_at, and anything published after window_end is dropped in code before the model sees it. Prompt instructions alone are not sufficient.

Known limitation: search retrieves news catalysts (earnings, launches, index inclusion, analyst actions) well and retail sentiment poorly, since social buzz is barely indexed retrospectively. The deterministic price run-up is the sentiment proxy; news context explains why the run-up happened.

Deliberately excluded

• Multi-modal chart analysis. A chart image gives the model strictly less than the computed run-up percentages, at higher cost. Worth revisiting only for a different problem, such as reading figures out of filings.
• Per-user precomputed daily snapshots. Deriving on read is fast enough at this scale and avoids invalidation logic for back-dated transaction edits.
• Visual redesign. Separate phase against a working page — see below.

Designer brief

Written as motivation and available data. Representation is the designer's call.

Why the page exists: the user has years of their own transactions in the system. Raw, that history tells them nothing. The page turns it into a judgement about how they invest — not how the portfolio is doing right now, which other pages cover. It answers: am I getting better, what do I keep doing wrong, and is that pattern costing me money.

Who reads it: a single investor reviewing their own record, roughly monthly. Not monitoring — reflecting. Willing to spend a few minutes. The only person who will ever see it.

Data available — exact, always current, free to compute: daily portfolio value and cost basis over any window back to the first trade; win rate, CAGR, average hold time, position count, sector concentration; winners vs losers compared on return, hold time and success rate; the per-transaction evidence pack described above.

Data available — interpretive, dated, costs money to produce: six independent analyses — health assessment, behavioral patterns (each with high/medium/low severity and supporting trade examples), investor evolution across periods with repeating mistakes, FOMO-flagged trades with triggers and outcomes, sector timing, tax optimization.

Data available — contextual: for months where the user traded, what was in the news about that company at the time.

Functional constraints shaping any representation:

• The two kinds of data have genuinely different trust properties. One is arithmetic on the user's own records; the other is a language model's opinion, possibly a month old, possibly wrong. A reader must never have to guess which they are looking at.
• Everything arrives asynchronously and independently. Nothing is guaranteed present, and anything absent must not prevent the rest from being read.
• Each interpretive analysis is in one of four conditions: never generated, being generated, generated and current, generated but outdated by newer trades. Each carries its production date. The user chooses when to regenerate, and each regeneration has a real token cost worth knowing before committing.
• Price history may be incomplete for recently added holdings, so the value series can legitimately cover a shorter window than requested.
• Must sit inside the existing application shell and design language.

Phasing

Each phase independently shippable. Backend first — nothing blocks on the designer.

1. daily_price table, PriceHistoryService, nightly sync, backfill task
2. PortfolioSeriesBuilder + JSON endpoint, base-currency conversion, async hydration; delete the old chart path
3. Deterministic evidence pack
4. Split the prompt into four new modules; monthly cadence; per-analysis dates
5. company_market_context with the code-level hindsight filter
6. Designer handoff, then visual redesign as a separate spec

Verification

• Incremental sync requests only the missing range; syncing twice is idempotent
• A two-ticker, two-currency fixture with known prices and rates produces exactly the expected value and cost arrays
• A back-dated transaction edit changes the curve immediately
• A failing module leaves the other five rendering
• Events published after window_end never reach the model
• Chart endpoint p95 < 150ms at 5y × 30 tickers