dividend-tracking

Dividend Tracking

Purpose

Import Fidelity dividend CSV data into the Dividends sheet input area, then trigger the Apps Script to process records into the historical log.

Workflow Routing

When executing this workflow, output this notification:

Running the **SyncDividends** workflow from the **dividend-tracking** skill...

Dividends Sheet Architecture

The Dividends tab has TWO SECTIONS:

Left Side: INPUT AREA (Columns A-D, Rows 2-43)

This is where YOU write dividend records.

RULES:

• ✅ Write to rows 2-43 ONLY (row 1 is header)
• ✅ Maximum 42 records per batch
• ❌ NEVER write past row 43
• After writing, click "Add Dividend" button to process

Right Side: HISTORICAL LOG (Columns G-U, Rows 4+)

This is populated by the Apps Script - DO NOT WRITE HERE.

The Apps Script reads from the input area (A-D) and appends to the historical log (G onwards).

Input Source: SnapTrade activities (preferred) — CSV is fallback

As of SnapTrade Phase 2 (#72), the preferred input is live normalized activities, not dividend.csv:

uv run python -m src.integrations.snaptrade.cli activities --output json

Filter the returned records to type == "DIVIDEND". Each record carries date, symbol, amount, description, and account. Null-symbol dividends already have a ticker resolved — the CLI parses it from the description (e.g. ... ETF (SCHD)), mirroring the positions/options symbol fallback — so symbol is safe to write straight to Column A.

Dedupe is unchanged: Google Sheets stays the single source of truth. The historical log (cols A-F / SUMIFS in G-U) is the ledger; only write input rows that are not already represented, and only for pay dates that have passed. No local cache or state file is introduced.

Fallback: the dividend.csv path below still works and remains the fallback until the human reconciliation gate (#72) confirms parity. CSV ingestion is not removed in this phase (deletion is Phase 3 / #73).

Core Workflow

1. Read Dividend CSV

File Location: notebooks/updates/dividend.csv

Key CSV Columns:

2. Calculate Dividends Received

Dividends Received = Quantity × Amount per share

Aggregation Rules:

• Sum quantities for same ticker (Margin + Cash accounts)
• Use single row per ticker
• Skip rows with -- in Amount per share (non-dividend payers)
• Only include pay dates that have PASSED (already received)

3. Check Input Area Status

Read current input area:

mcp__gdrive__sheets(
    operation: "readSheet",
    params: {
        spreadsheetId: "{spreadsheet_id}",
        range: "Dividends!A2:D43"
    }
)

Determine:

• First empty row (where to start writing)
• Available slots (max 45 - current entries)
• If full, STOP and alert user to click button first

4. Write to Input Area

Write starting at first empty row:

mcp__gdrive__sheets(
    operation: "updateCells",
    params: {
        spreadsheetId: "{spreadsheet_id}",
        range: "Dividends!A2:D13",  // Adjust range based on record count
        values: [
            ["JEPI", "$51.63", "01/05/2026", "TRUE"],
            ["JEPQ", "$78.62", "01/05/2026", "TRUE"],
            // ... more records
        ]
    }
)

5. Trigger addDividendFast() via apps-script-run web app

After writing records, invoke the dispatcher (replaces the old "click Add Dividend button" UI step):

curl -sL "${APPS_SCRIPT_RUN_URL}?fn=addDividendFast"

The function appends each input row to the historical raw data (cols A-F at the bottom of the sheet) and clears A2:D44. SUMIFS formulas in the historical log (cols G-U) auto-aggregate by ticker × month. Returns JSON {"ok": true, ...} on success. See the apps-script-run skill for the full dispatcher reference.

Fallback (if web app is unreachable): Replicate addDividendFast directly via the gdrive Sheets API:

1. Read input area A2:D44, skip blank rows
2. For each row, compute year = new Date(date).getFullYear() and month = getMonth() + 1
3. Append [ticker, amount, date, drip, year, month] to the next empty row in cols A-F (typically row getLastRow() + 1)
4. Clear A2:D44

6. Verify Processing

After clicking button:

• Input area (A2:D43) should be cleared
• Historical log should have new entries
• Monthly totals should update

Data Flow Diagram

┌─────────────────────────────────┐
│  dividend.csv (Fidelity export) │
│  - Symbol, Quantity             │
│  - Amount per share, Pay date   │
└──────────────┬──────────────────┘
               │
               ▼
┌─────────────────────────────────┐
│  Calculate Dividends Received   │
│  Qty × Amount = Total Dividend  │
│  Aggregate by ticker            │
│  Filter: only PAST pay dates    │
└──────────────┬──────────────────┘
               │
               ▼
┌─────────────────────────────────┐
│  INPUT AREA (A2:D43)            │
│  Write calculated dividends     │
│  Max 42 records per batch       │
└──────────────┬──────────────────┘
               │
               ▼
┌─────────────────────────────────┐
│  CLICK "Add Dividend" BUTTON    │
│  (Browser automation or manual) │
└──────────────┬──────────────────┘
               │
               ▼
┌─────────────────────────────────┐
│  HISTORICAL LOG (G4+)           │
│  Apps Script processes input    │
│  Appends to monthly columns     │
└─────────────────────────────────┘

Apps Script Integration

The Dividends sheet has Apps Script automation that:

• Reads records from input area (A2:D43)
• Parses ticker, amount, date, DRIP status
• Appends to historical log with proper date formatting
• Updates monthly income columns (I-T)
• Clears input area after processing

Script Location: scripts/google-sheets/portfolio-optimizer/Dividend.js

Custom Menu: "Portfolio Optimizer" → (dividend-related options)

Critical Rules

WRITABLE Area

• ✅ Columns A-D, Rows 2-43 (input area)
• ✅ Maximum 42 records per batch
• ✅ Must click "Add Dividend" button after writing

DO NOT MODIFY

• ❌ Row 1 (header)
• ❌ Rows 44+ in columns A-D
• ❌ Columns G-U (historical log - Apps Script managed)
• ❌ Any formulas

Date Format

• Use MM/DD/YYYY (e.g., "01/05/2026")
• Match existing entries in the sheet

DRIP Status

• TRUE = dividend was reinvested (shares increased)
• FALSE = dividend paid as cash
• Default TRUE for accumulation phase

Pre-Flight Checklist

Before syncing dividends:

• dividend.csv exists in notebooks/updates/
• CSV is recent (check "Date downloaded" at bottom)
• Input area (A2:D43) has available slots
• If input area has data, click button first to clear it
• Browser automation available for button click

Example Scenario

User: "sync dividends"

Agent workflow:

1. ✅ Read CSV - found 40 rows
2. ✅ Filter - 12 tickers with dividend data for past pay dates
3. ✅ Aggregate - combined Margin/Cash positions
4. ✅ Calculate - total dividends: $786.86
5. ✅ Check input area - rows 2-43 empty, 42 slots available
6. ✅ Write records - added 12 rows to A2:D13
7. ✅ Open browser - navigate to Dividends sheet
8. ✅ Click button - trigger "Add Dividend" Apps Script
9. ✅ Verify - input area cleared, historical log updated
10. ✅ LOG: "Synced 12 dividend records totaling $786.86"

Google Sheets Integration

Spreadsheet ID: {spreadsheet_id} Dividends Sheet ID: 2068577140 Direct URL: https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit#gid=2068577140

Reference Files

• Dividend CSV: notebooks/updates/dividend.csv
• Apps Script: scripts/google-sheets/portfolio-optimizer/Dividend.js
• Spreadsheet: Finance Guru Portfolio Tracker (Dividends tab)

---

Skill Type: Domain (workflow guidance) Enforcement: SUGGEST (high priority advisory) Priority: High