zenith-finance-sync

name	zenith-finance-sync
description	Auto-sync Zenith Finance data (transactions, ledger) to Google Sheets. Creates/updates a spreadsheet with Dashboard and History sheets.
version	1.0.0
author	Hermes Agent
license	MIT
metadata	{"hermes":{"tags":["Finance","Accounting","Google Sheets","Automation","Zenith"]}}

Zenith Finance to Google Sheets Auto-Sync Skill

Purpose

Automatically synchronizes Zenith Finance transaction data and ledger balances to a Google Sheets spreadsheet with:

• Dashboard sheet: Summary metrics, charts-ready data with correct formulas
• History sheet: Complete transaction log with timestamps

Enhanced Features

• Correctly references Category column (D) for income/expense calculations
• Account balances calculated as Income - Expense per account
• Robust transaction handling with duplicate prevention
• Extended dashboard with monthly summaries and detailed accounting
• Preserves custom formulas in Dashboard cells (especially B2 and B3 for Income/Expense SUMIFs) by using user-specified column references in the sync script
• Supports enhanced pipe-separated Transactions.md format: [timestamp] | description | amount | Income/Expense | Category | Source | Balance
• ENHANCED: Now includes actual Category field (Food/Transport/etc.) in Google Sheets Column E for detailed tracking
• Updated to sync 7 columns: Timestamp, Description, Amount, Income/Expense, Category, Source, Balance

Prerequisites

1. Google Workspace skill must be authenticated with scopes including:
   • https://www.googleapis.com/auth/spreadsheets
   • https://www.googleapis.com/auth/drive
2. Zenith Finance data must exist in ~/Documents/Obsidian/Notes/Finance/

Configuration

The skill stores the target spreadsheet ID in:

• ~/.hermes/zenith_spreadsheet_id.txt
• Can be overridden by setting ZENITH_SPREADSHEET_ID environment variable

Workflow

1. Check if Google authentication is valid
2. Load Zenith Finance data from Ledger.md and Transactions.md
3. If no target spreadsheet exists, create one with Dashboard and History sheets
4. Append new transactions to History sheet (since last sync)
5. Update Dashboard summary metrics
6. Record sync timestamp for next run

Troubleshooting\n\n### Regex Pattern Issues\nIf encountering "bad character range" errors in parse_transactions_file:\n1. Timestamp regex (around line 91): Should be r'$$\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}$$' to match [2026-04-07 21:32]\n2. Transaction parser (around line 109): Should be r'$$(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2})$$\\s*\\|\\s*(.+?)\\s*\\|\\s*(.+?)\\s*\\|\\s*(.+?)\\s*\\|\\s*(.+)'\n3. Amount extraction (around line 117): Should be r'-\\s*Rp([\\d,.]+)$' to extract amounts like "- Rp12345.67"\n4. Amount/Balance cleaning (around lines 126, 134): Should be re.sub(r'[Rp,\\s]', '', string) to remove Rp, commas, and spaces\n\n### String Escaping Issues\nIf encountering "unexpected character after line continuation character" errors:\n- Check print statements for incorrectly escaped quotes\n- Should be print(f\"Warning: message {variable}\") not print(f\\\"Warning: message {variable}\\\")\n\n### PATH Construction Issues\nIf seeing "google_token.json not found" errors despite file existing:\n- Check TOKEN_PATH assignment - should be Path(HERMES_HOME) / \"google_token.json\" not malformed concatenation\n\n### Sync State Issues\nIf load_sync_state() errors occur:\n- Ensure the sync state file (~/.hermes/zenith_sync_state.json) contains valid JSON\n- Is absent for first run (will be created after successful sync)\n\n### Google Auth Problems\nIf API calls fail with authentication errors:\n- Re-authenticate using the google-workspace-setup skill\n- Verify token file exists at ~/.hermes/google_token.json\n- Ensure required scopes are granted: spreadsheets and drive

Trigger this skill after any Zenith Finance transaction is recorded, or run manually to sync all data.

Data Mapping

History Sheet Columns:

• Timestamp: ISO format from transaction
• Description: Transaction description/item
• Amount: Numeric amount (positive for income, negative for expense)
• Category: Transaction category (Income, Expense)
• Source: Account involved (BCA, Gopay, Dompet, etc.)
• Balance: Running net worth after transaction

Dashboard Sheet:

• Metrics: Total Income, Total Expenses, Net Savings, Last Updated
• Formulas that auto-calculate from History data

Implementation

This skill uses the scripts/sync.py script to perform the actual synchronization. The script handles robust parsing of Transactions.md format in two versions:

Legacy Format (still supported):

• Multiple transactions may appear on the same line without separators
• Transaction descriptions follow the format "Description - RpAmount" where the amount needs extraction
• Special formats like "-RpAmount" (without description dash) are handled for consistency

New Pipe-Separated Format (current):

• Each transaction on its own line: [timestamp] | description | amount | Income/Expense | Category | Source | Balance

• Amount is a numeric value (may include commas)

• Income/Expense field indicates transaction type

• Category provides detailed categorization (Food, Transport, etc.)

• Source indicates the account involved

• Balance shows running net worth after transaction (prefixed with "Rp")

• Opening balance transactions are identified and processed correctly

• Sync state is tracked using timestamps to only process new transactions since last sync

• The parser handles malformed lines gracefully with warnings rather than failures

• Dashboard summary formulas are preserved per user specification, specifically ensuring B2 and B3 use column D (Category) for SUMIF calculations rather than column E (Source)