formula-protection（配方保护）

Formula Protection

Purpose

GUARDRAIL SKILL - Prevents accidental modification or deletion of critical formulas that maintain spreadsheet integrity. Ensures financial data accuracy by protecting auto-calculated columns.

When to Use (Auto-Blocks)

This skill automatically blocks when detecting:

• Attempts to "update formula", "modify formula", "change formula"
• Editing Column C (GOOGLEFINANCE price formulas)
• Modifying Columns D-F, H-S (calculated formulas)
• Fixing formula errors (#N/A, #DIV/0!, #REF!) without proper protocol
• User mentions: "fix formula", "edit cell", "update column C/D/E"

This is a BLOCKING skill - You MUST use this skill before proceeding with any formula-related edits.

Sacred Formulas (NEVER TOUCH)

DataHub Tab

Column C: Last Price

=GOOGLEFINANCE(A2, "price")

• Auto-updates stock prices in real-time
• ❌ NEVER modify - prices must come from Google Finance
• ❌ NEVER replace with static values
• ✅ ONLY wrap with IFERROR if showing #N/A for delisted stocks

Columns D-E: $ Change, % Change

=C2 - G2  ($ Change)
=D2 / G2  (% Change)

• Calculated from Last Price (C) and Avg Cost Basis (G)
• ❌ NEVER touch - let formulas calculate automatically

Columns H-M: Gains/Losses

=L2 - M2  (Total G/L $)
=K2 / M2  (Total G/L %)
=B2 * C2  (Current Value)
=B2 * G2  (Cost Basis Total)

• Core portfolio performance metrics
• ❌ NEVER modify - accuracy depends on these formulas
• ✅ ONLY add IFERROR if #DIV/0! errors appear

Columns N-S: Advanced Metrics

• Contains ranges, dividend data, layer classifications
• Mix of formulas and manual classifications
• ⚠️ Consult spreadsheet-architecture.md before editing

Dividend Tracker Tab

Column F: Total Dividend $

=D2 * E2  (Shares × Dividend Per Share)

• Calculates expected dividend income per fund
• ❌ NEVER modify - must remain formula-driven

Total Row Formula

=SUM(F2:F50)  (TOTAL EXPECTED DIVIDENDS)

• Sums all dividend income
• ❌ NEVER delete or modify
• ✅ ONLY expand range if data grows beyond row 50

Margin Dashboard Tab

Coverage Ratio

=IFERROR(B10 / B11, 0)  (Dividends ÷ Interest Cost)

• Critical safety metric for margin strategy
• ❌ NEVER remove IFERROR wrapper
• ✅ ONLY update if adding new safety thresholds

Allowed Operations

✅ SAFE: Add IFERROR() Wrappers

Purpose: Prevent error display without changing logic

Example:

Before: =GOOGLEFINANCE(A2, "price")
After: =IFERROR(GOOGLEFINANCE(A2, "price"), "N/A")

Before: =B10 / B11
After: =IFERROR(B10 / B11, 0)

When to use:

• #N/A errors from delisted stocks (GOOGLEFINANCE failures)
• #DIV/0! errors when margin balance = $0
• #REF! errors from deleted rows (use IFERROR as temporary fix)

✅ SAFE: Fix Broken Sheet References

Purpose: Correct renamed or moved sheet names

Example:

Before: =Sheet1!A1
After: ='DataHub'!A1

Before: ='Dividend Tracker OLD'!B10
After: ='Dividend Tracker'!B10

When to use:

• Sheet was renamed (Sheet1 → DataHub)
• Sheet was duplicated and old reference remains
• Tab moved to different position

✅ SAFE: Expand Formula Ranges

Purpose: Include new data rows without changing logic

Example:

Before: =SUM(F2:F50)
After: =SUM(F2:F100)

Before: =AVERAGE(B2:B30)
After: =AVERAGE(B2:B50)

When to use:

• New portfolio positions added beyond row 50
• Dividend tracker grows beyond expected size
• Margin dashboard accumulates monthly entries

✅ SAFE: Fix Cell Reference Typos

Purpose: Correct obvious mistakes in formula construction

Example:

Before: =B100 * C100  (B100 doesn't exist)
After: =B10 * C10

Before: =A2 + A2  (duplicate cell reference)
After: =A2 + B2  (correct cells)

When to use:

• Formula references non-existent row
• Clear typo in cell reference
• Formula clearly broken due to manual error

Forbidden Operations

❌ NEVER: Change Formula Logic

Example of what NOT to do:

❌ =SUM(F2:F50) → =AVERAGE(F2:F50)  (changes meaning)
❌ =B2 * C2 → =B2 + C2  (changes calculation)
❌ =GOOGLEFINANCE(A2, "price") → =GOOGLEFINANCE(A2, "volume")

Why: Changes the meaning of calculated data, breaks dashboard integrity

❌ NEVER: Replace Formulas with Static Values

Example of what NOT to do:

❌ =GOOGLEFINANCE("TSLA", "price") → 445.47  (hardcoded)
❌ =B2 * C2 → 32964.78  (static value)
❌ =SUM(F2:F50) → 2847.32  (loses dynamic calculation)

Why: Data becomes stale, no longer updates automatically

❌ NEVER: Delete Formulas

Example of what NOT to do:

❌ Deleting Column C (Last Price formulas) to "clean up"
❌ Removing total row formulas to "simplify"
❌ Clearing formula cells to "start fresh"

Why: Destroys data pipeline, breaks all dependent calculations

❌ NEVER: Modify GOOGLEFINANCE Parameters

Example of what NOT to do:

❌ =GOOGLEFINANCE(A2, "price") → =GOOGLEFINANCE(A2, "closeyest")
❌ =GOOGLEFINANCE("TSLA", "price") → =GOOGLEFINANCE("NASDAQ:TSLA", "price")

Why: May break price lookups, change data source unexpectedly

Smart Formula Repair Workflow

Step 1: Identify Error Type

Scan spreadsheet for:

• #N/A (not available - usually GOOGLEFINANCE or VLOOKUP failures)
• #DIV/0! (division by zero - usually margin calculations when balance = $0)
• #REF! (reference error - deleted rows/columns)
• #VALUE! (wrong data type - rare in financial sheets)

Step 2: Classify Repair Strategy

For #N/A Errors:

GOOGLEFINANCE failures (Column C):

Cause: Stock delisted, ticker invalid, or Google Finance API issue
Solution: Wrap with IFERROR()
=IFERROR(GOOGLEFINANCE(A2, "price"), "DELISTED")

VLOOKUP failures (if used):

Cause: Lookup value doesn't exist in source data
Solution: Check source data exists, expand range, or add IFERROR()
=IFERROR(VLOOKUP(A2, Data!A:B, 2, FALSE), "NOT FOUND")

For #DIV/0! Errors:

Margin coverage ratio (when margin = $0):

Before: =B10 / B11
After: =IFERROR(B10 / B11, 0)

Percentage calculations (when denominator = 0):

Before: =K2 / M2
After: =IFERROR(K2 / M2, 0)

For #REF! Errors:

Deleted rows/columns:

Cause: Formula references Sheet1!A10 but row 10 was deleted
Solution: If temporary, wrap with IFERROR(). If permanent, reconstruct formula.
Temporary: =IFERROR(Sheet1!A10, 0)
Permanent: Identify correct new reference and update

Step 3: Test Repair on Single Cell

Before applying broadly:

1. Copy original formula to notes (for rollback)
2. Apply repair to ONE cell
3. Verify result looks correct
4. Check no new errors introduced
5. If successful, apply to other similar errors

Step 4: Validate No New Errors

After repair:

• Scan entire sheet for new #N/A, #DIV/0!, #REF!
• Verify totals still sum correctly
• Check formulas still reference correct cells
• Confirm no unexpected data changes

Step 5: Document Repair

Log all changes:

✅ Fixed #DIV/0! in Margin Dashboard C10 by adding IFERROR()
Formula change: =B10/B11 → =IFERROR(B10/B11, 0)
Reason: Margin balance was $0 causing division by zero
Date: 2025-11-11

Pre-Flight Checklist (Before Editing Formulas)

Ask yourself:

• Am I trying to modify a formula-based column? (C, D-F, H-S)
• Is this a GOOGLEFINANCE formula? (STOP - don't touch)
• Is this a total/summary formula? (STOP - don't touch)
• Am I changing formula logic? (STOP - forbidden)
• Am I replacing formula with static value? (STOP - forbidden)
• Am I adding IFERROR to existing formula? (✅ Allowed)
• Am I fixing a broken sheet reference? (✅ Allowed)
• Am I expanding a range for new data? (✅ Allowed)
• Have I consulted spreadsheet-architecture.md? (Required)

If ANY stop condition triggered: Use this skill, review rules, proceed with caution

Emergency Rollback Procedure

If formula modification breaks the spreadsheet:

1. STOP immediately - Do not attempt additional fixes
2. Alert user - Clearly explain what went wrong
3. Document the error:

- Which cell/column was modified - What formula change was made - What error occurred (new #N/A, wrong calculations, etc.)

1. Restore from Google Sheets version history:

- File → Version History → See Version History - Find last working version (before modification) - Click "Restore this version"

1. Learn from it - Update this skill if new edge case discovered

Example Scenarios

❌ WRONG: Modifying Last Price Formula

User request: "The price for TSLA isn't updating, let me fix the formula"

Agent response:

🛑 BLOCKED by formula-protection skill

You're attempting to modify Column C (Last Price) which contains GOOGLEFINANCE formulas.
These are sacred formulas and must NEVER be modified.

If TSLA price isn't updating, the issue is likely:
1. Google Finance API delay (wait 5-10 minutes)
2. Market is closed (prices only update during trading hours)
3. Ticker symbol incorrect (verify "TSLA" is correct)

❌ Do NOT modify =GOOGLEFINANCE(A2, "price")
✅ If truly broken, wrap with: =IFERROR(GOOGLEFINANCE(A2, "price"), "N/A")

✅ RIGHT: Adding IFERROR Wrapper

User request: "Column C shows #N/A for delisted stock, can you fix?"

Agent response:

✅ Using formula-protection skill

I'll safely wrap the GOOGLEFINANCE formula with IFERROR() to handle the delisted stock.

Before: =GOOGLEFINANCE(A45, "price")
After: =IFERROR(GOOGLEFINANCE(A45, "price"), "DELISTED")

This preserves the formula logic while displaying "DELISTED" instead of #N/A.
The formula will still work if the stock relists in the future.

✅ Safe operation - formula logic unchanged

✅ RIGHT: Expanding Summary Range

User request: "Total dividends formula only goes to row 50, but I have 55 funds now"

Agent response:

✅ Using formula-protection skill

I'll safely expand the SUM range to include all 55 funds.

Before: =SUM(F2:F50)
After: =SUM(F2:F100)

This expands the range to accommodate current data (55 funds) plus room for growth.
Formula logic unchanged - still summing Total Dividend $ column.

✅ Safe operation - range expanded, logic preserved

Google Sheets Integration

Spreadsheet ID: Read from fin-guru/data/user-profile.yaml → google_sheets.portfolio_tracker.spreadsheet_id

Use the mcp__gdrive__sheets tool:

Read-only operations (to detect formula errors):

// STEP 1: Read Spreadsheet ID from user profile
// Load fin-guru/data/user-profile.yaml
// Extract: google_sheets.portfolio_tracker.spreadsheet_id

// STEP 2: Scan for errors
mcp__gdrive__sheets(
    operation: "spreadsheets.values.get",
    params: {
        spreadsheetId: SPREADSHEET_ID,  // from user-profile.yaml
        range: "DataHub!A1:Z100"
    }
)
// Check for #N/A, #DIV/0!, #REF! in returned values

Write operations (only for safe repairs):

// Add IFERROR wrapper to fix formula errors
mcp__gdrive__sheets(
    operation: "spreadsheets.values.update",
    params: {
        spreadsheetId: SPREADSHEET_ID,  // from user-profile.yaml
        range: "DataHub!C2:C2",
        valueInputOption: "USER_ENTERED",
        requestBody: {
            values: [["=IFERROR(GOOGLEFINANCE(A2, \"price\"), \"N/A\")"]]
        }
    }
)

Agent Permissions

Builder (Write-enabled with formula-protection):

• Can add IFERROR wrappers
• Can fix broken sheet references
• Can expand formula ranges
• Can fix cell reference typos
• MUST follow this skill's rules

All Other Agents (Strictly Read-only):

• Market Researcher, Quant Analyst, Strategy Advisor, Margin Specialist, Dividend Specialist
• Can read all data including formulas
• CANNOT modify any formulas
• Must defer to Builder for any formula repairs
• Should alert Builder if formula errors detected

Reference Files

For complete details, see:

• Spreadsheet Architecture: fin-guru/data/spreadsheet-architecture.md (lines 380-440)
• Quick Reference: fin-guru/data/spreadsheet-quick-ref.md
• Agent Permissions: fin-guru/data/spreadsheet-architecture.md (lines 91-136)

Key Takeaways

Remember:

• 🛑 Formulas are sacred - Default assumption is DON'T TOUCH
• ✅ IFERROR is your friend - Safe way to handle errors
• 📖 Consult docs first - Read spreadsheet-architecture.md before any edit
• 🤝 Ask user if unsure - Better to ask than break financial data
• 🔄 Google Sheets has version history - Mistakes can be rolled back

When in doubt: READ-ONLY and ASK USER for guidance.

Skill Type: Guardrail (safety mechanism) Enforcement: BLOCK (prevents formula modifications) Priority: Critical Line Count: < 500 (following 500-line rule) ✅