Schwab Portfolio Drift Alerter

Pulls your Schwab positions daily, applies the Bogleheads 5/25 rebalancing rule, and logs a color-coded drift report to Google Sheets — so you always know when (and how much) to rebalance without checking your brokerage every day.

Date: 2026-07-08 Type: Integration Theme: Investing Status: Idea

What it is

A scheduled integration that connects Charles Schwab's Trader API (individual developer tier, free access at developer.schwab.com) to a Google Sheet. Once a day it reads your portfolio positions, groups them by asset class, computes each class's actual weight as a share of total portfolio value, and compares that weight against your personal target allocation using the Bogleheads 5/25 threshold rule. Each run appends one row per asset class to a DriftLog tab — TRIGGER rows turn red via a conditional format rule, OK rows stay neutral. No email, no dashboard, no brokerage login needed after setup.

Who it serves

A passive index-fund investor (3-fund portfolio, target-date investor, or simple equity/bond split) who wants to maintain their intended asset allocation without checking their brokerage daily. Especially useful during sustained bull runs, when equity positions silently drift far above target — adding unintended risk the investor may not notice until a correction.

The money problem it addresses

Portfolio drift is the quiet risk of buy-and-hold investing: a 60/40 portfolio left unattended during a multi-year bull market becomes 75/25 or worse — with the investor carrying far more equity risk than they originally chose. The Bogleheads 5/25 rule (rebalance when any asset class drifts ≥5 percentage points absolute, OR ≥25% relative to its target, whichever triggers first) is the most commonly recommended threshold-based approach in the passive-investing community. The pain: implementing this check manually requires opening your brokerage, pulling up percentages, doing the math, and deciding — most investors forget or procrastinate. This integration automates the math and surfaces the answer inside a tool most people already have open (Google Sheets).

How it works

1. Token check: The integration loads stored OAuth2 tokens. If the Schwab access token is within 5 minutes of expiry (TTL: 30 min), it refreshes using the stored refresh token (TTL: 7 days) before proceeding.
2. Position fetch: GET /trader/v1/accounts?fields=positions returns all positions and balances for the configured account. Total portfolio value is summed from marketValue across all positions.
3. Drift computation: Each position is mapped to a user-defined asset class (e.g., VTI → US Equity, VXUS → International Equity, BND → Bonds) using a local config file. Actual weight = marketValue / totalValue × 100. Drift = actual% − target%. The 5/25 rule fires when |drift_pp| ≥ 5.0 (absolute band) OR |drift_pp / target%| × 100 ≥ 25.0 (relative band) — whichever triggers first. An action hint (BUY $X / SELL $X) is computed to show exactly how many dollars would restore the target weight.
4. Sheet append: One row per asset class is appended to the DriftLog tab via the Google Sheets values:append endpoint. All rows for a given run are written atomically (all or none).
5. No-op on success: If no asset class triggers, all rows say "OK – within bands" and the Sheet serves as a record that the check ran cleanly that day.

What's in this folder

• README.md — this file
• cover-image.png — cover card image for the web app
• metadata.md — workflow metadata sidecar
• integration-spec.md — the full integration contract: systems, auth, endpoints, payload shapes, error model, sequencing, and operational expectations
• sequence.md — textual sequence diagram covering the happy path (daily drift check), four failure cases (expired access token, Schwab 429, expired refresh token, all-clear run)
• sample-request.json — example POST body for the Google Sheets values:append call (a run with 2 TRIGGER events + 1 OK, 3-fund portfolio, $80,000 total)
• sample-response.json — matching Google Sheets API v4 response confirming 3 rows written to DriftLog!A5:H7

How to run / read it

Read integration-spec.md first for the complete contract. Then sequence.md for the step-by-step call chain across the happy path and four failure scenarios. Finally review sample-request.json and sample-response.json together — the request shows the exact payload shape sent to Google Sheets after a drift-check run, and the response shows what a successful append confirmation looks like.

To implement this integration:

1. Register a free Individual Developer app at developer.schwab.com (requires an existing Schwab brokerage account).
2. Complete the OAuth2 authorization code flow once to obtain an initial access + refresh token pair.
3. Enable the Google Sheets API in a Google Cloud project; create a service account or run the personal OAuth2 device flow to obtain a Sheets access token.
4. Create the target Google Sheet; add a DriftLog tab with header row: Date | Symbol | Actual% | Target% | Drift_pp | Trigger | Market_Value | Action.
5. Define a local config.json with your accountNumber, spreadsheetId, targetWeights per symbol, and symbolToAssetClass map.
6. Schedule the integration script to run daily at 09:15 ET (or a time convenient for your market). See integration-spec.md §8 for rate-limit and quota details.

Estimated impact

A passive investor with a $80,000 3-fund portfolio who rebalances using the 5/25 rule spends approximately 5–10 minutes per year making actual rebalancing trades — versus the ~2 hours per year (15–20 min/month) that manual portfolio-check habits often consume. The financial benefit comes from maintaining the intended risk level: research by Vanguard and the Bogleheads community consistently shows that threshold-based rebalancing reduces maximum drawdown by keeping equity exposure from silently inflating 10–15 percentage points above target during bull markets. The integration doesn't eliminate the rebalancing trade; it eliminates the cognitive load of deciding whether one is needed.

Good to know

This integration is an organizational and monitoring tool, not personalized financial or investment advice. The 5/25 rule is a widely cited guideline from the passive-investing community, not a guarantee of optimal returns — rebalancing frequency, tax implications (taxable accounts vs. tax-advantaged), and individual risk tolerance all affect the right approach for any specific person. Before implementing, verify current Schwab API terms at developer.schwab.com and Google Sheets API quotas at console.cloud.google.com. Rates, access terms, and API capabilities change over time; always check the current documentation before building on third-party APIs.

Sources

• https://developer.schwab.com/user-guides/individual-developer/about-individual-developer-role — Schwab Individual Developer role overview and setup
• https://developer.schwab.com/user-guides/get-started/authenticate-with-oauth — Schwab OAuth2 authorization code flow, access/refresh token TTLs
• https://medium.com/@carstensavage/the-unofficial-guide-to-charles-schwabs-trader-apis-14c1f5bc1d57 — Schwab Trader API endpoints and positions response structure
• https://www.bogleheads.org/forum/viewtopic.php?t=463265 — Bogleheads 5/25 rebalancing rule, community discussion and examples
• https://www.bogleheads.org/forum/viewtopic.php?t=460315 — Bogleheads 5/25 rule applied to 90/10 and other allocations; absolute vs. relative band interaction
• https://www.bogleheads.org/forum/viewtopic.php?t=185596 — The Swedroe 5/25 rebalancing bands: origin and interpretation
• https://www.alphaexcapital.com/stocks/stock-portfolio-management/rebalancing-stock-portfolios/threshold-based-rebalancing-rules — Threshold-based rebalancing rules: 5pp absolute and 25% relative explained
• https://portfoliogenius.ai/blog/automated-portfolio-rebalancing-tools — 2026 landscape of portfolio drift monitoring tools; pain-point framing
• https://api.ynab.com/ — YNAB API reference (considered as alternative integration target)
• https://cran.r-project.org/web/packages/schwabr/schwabr.pdf — schwabr R package (community reference for Schwab API endpoint shapes)