Skip to main content

Performance & Speed Optimization

Processing thousands of financial transactions in Google Sheets™ can lead to slow execution times or script timeouts. To ensure a "snappy" experience, tradeGIST uses two distinct caching mechanisms designed to bypass the latency of repetitive spreadsheet reads during complex script executions.

1. Trades Caching​

Instead of rebuilding your entire history every time you update your portfolio, the engine utilizes "Delta Processing" to minimize resource usage:

  • Incremental Scanning: The script stores the last scanned row index in the Cache Service. On subsequent runs, it only reads the Transaction sheet from that specific index to the end.
  • Differential Updates: Rather than a full "dump and replace" of the Trades sheet, the logic identifies only the specific rows that require changes and updates them individually.
  • Data Integrity: This system preserves the complex logic required for LIFO/FIFO matching and accurately calculates P&L, ROI, and FX impact across stocks, options, and crypto.

Enabling Trades Cache​

  1. Open the tradeGIST menu and click ⚙️ Settings.
  2. Go to the Caching / performance section.
  3. Enable Trades cache and save.

The "Any Change" Rule for Trades​

Because the engine relies on a "last known state," it will not automatically detect modifications made to "previous" transactions (rows located before the last scanned index).

You must manually clear the trades cache if ANY changes are made to an existing transaction row. This includes:

  • Editing Volume, Price, Account, Category, Fees, or Currency.
  • Changing the Date or Ticker symbol.
  • Deleting or Inserting a row anywhere in the middle of the sheet.

2. Configuration Caching​

Fetching settings directly from spreadsheet cells is one of the slowest operations in Google Apps Script. When the "Generate Trades" script runs, it frequently needs to reference the data in your Configuration sheet — your asset and option contract specifications, accounts and categories, account tax-group mappings, stock splits, and tax rates. Caching these settings in memory significantly reduces execution time by eliminating redundant "Read" calls to the Configuration sheet.

This cache covers the data read from your Configuration sheet:

  • Assets & Options: Asset ticker-specific settings (currency, sector, price source…) and option contract specifications.
  • Accounts & Categories: Accounts and categories.
  • Tax: Tax rates, account tax groups, and the wash sale detection configuration (your identical-assets groups).
  • Events: Stock splits.

Enabling Configuration Cache​

Once you have finalized configuring all your settings and your workflow has shifted to only adding new records to the Transactions sheet, you can activate the configuration cache to further optimize performance. To enable it:

  1. Open the tradeGIST menu and click ⚙️ Settings.
  2. Go to the Caching / performance section.
  3. Enable Configuration cache and save.
Script Efficiency

Enabling this feature prevents the add-on from having to "look up" your configurations in the Configuration sheet during the trade generation process, leading to a much faster execution.

Handling Config Updates​

If you change any value in the Configuration sheet (e.g., editing an asset, adding an account, or recording a new Stock Split), the script will continue to use the "old" values stored in the cache.

You must manually clear the configuration cache after making ANY settings change.

Cache Management Tools​

The top menu provides granular control over your data state. Access these via tradeGIST > Cache:

Menu OptionWhen to use it
Clear trades cacheUse after editing, inserting, or deleting any historical transaction row.
Clear configuration cacheUse after changing assets, accounts, tax rates, splits, or any other Configuration-sheet setting.
Clear all cachesThe "nuclear option." Use this if you have made extensive changes to both data and settings to ensure a perfectly clean rebuild.
Important

After clearing a cache, always click Generate Trades to initiate a full scan and sync your sheets with the most recent data and settings.