Performance Tips

SheetsFinance is designed to efficiently retrieve financial data directly into your Google Sheets. However, due to it's default nature as a Google Sheets extension it must abide by the limitations and behaviours of Google Sheets, which can sometimes lead to suboptimal performance if not managed correctly. This guide outlines best practices and strategies to optimise your usage of SheetsFinance, ensuring faster load times, reduced API quota consumption, and an overall smoother experience.

In this guide we will cover six key strategies:

1. Manage Background Refreshing Strategically

2. Use Batch Functions for Multiple Symbols

3. Chain Metrics Together

4. Request Multiple Years at Once

5. Minimise Dynamic Function Usage

6. Evaluate Third-Party Extensions

All of these strategies work toward the same goal:

Less Function Calls = Better Performance + Lower Quota Usage ✅

1. Manage Background Refreshing Strategically

The Problem

Google Sheets automatically performs background refreshes on all open spreadsheet files, including sheets that aren't currently visible or active. If you have multiple sheets within a single file, each containing SheetsFinance functions, Google will refresh all of them periodically—even if you're only working on one sheet at a time.

Why This Matters

• Quota Consumption: Background refreshes consume your daily Google external request quota unnecessarily, read more about Google Sheets quotas under Limits.

• Performance Impact: Multiple sheets refreshing simultaneously can slow down your entire spreadsheet

• Redundant Calls: You may be making the same API calls multiple times across different sheets

Best Practices

Split analyses across multiple spreadsheet files rather than using multiple sheets within a single file:

• Keep your real-time trading dashboard in one file

• Store historical analysis in a separate file

• Maintain company fundamentals research in another file

Close files when not in use to prevent background refreshing:

• Only keep files open that you're actively working with

• Bookmark frequently-used files for easy access

Audit your functions across all sheets:

• Check for duplicate or redundant SheetsFinance functions

• Remove functions from sheets you're no longer using

• Consider consolidating data calls into a "raw data" sheet that feeds other sheets via cell references. For the full workflow—including sorting and filtering the analysis sheet without reloading formulas—see Sort and filter SheetsFinance data without reloading formulas.

Example Structure:

❌ Bad: One file with multiple sheets
- MyFinanceAnalysis.xlsx
  ├── Real-time Dashboard (refreshing constantly)
  ├── Historical Data (refreshing constantly)
  └── Watchlist (refreshing constantly)

✅ Good: Separate files
- RealTimeDashboard.xlsx (open only during trading hours)
- HistoricalAnalysis.xlsx (open when needed)
- Watchlist.xlsx (open when screening stocks)

2. Use Batch Functions for Multiple Symbols

The Power of Batch Processing

One of SheetsFinance's most powerful features is the ability to retrieve data for hundreds or even thousands of symbols with a single function call. This dramatically reduces API calls and improves performance.

How It Works

Instead of writing individual functions for each ticker symbol:

❌ Inefficient Approach:

=SF("AAPL", "realTime", "price")
=SF("MSFT", "realTime", "price")
=SF("GOOGL", "realTime", "price")
... (1,000 more rows)

This creates 1,003 separate API calls

Write a single function for multiple symbols (listed in cells A1:A1000 in this example):

✅ Efficient Approach:

=SF(A1:A1000, "realTime", "price&volume")

This creates just 1 API call for 1,000 symbols each with 2 data points (2,000 data points total!)

Batch Function Types

SheetsFinance offers batch processing for multiple data types, for example:

Real-time Batch

• Get live prices, volumes, and market data for multiple symbols

• Syntax: =SF(A1:A100, "realTime", "price&volume&marketCap")

Company Info Batch

• Retrieve company fundamentals, descriptions, and static data

• Syntax: =SF(A1:A100, "companyInfo", "sector&industry&description")

Price Change Batch

• Calculate performance metrics across multiple symbols

• Syntax: =SF(A1:A100, "priceChange", "1D&1M&YTD")

Performance Benefits

3. Chain Metrics Together

What is Metric Chaining?

Most SheetsFinance functions allow you to request multiple data points in a single function call using the chaining operator (&). This returns multiple columns of data from one API request.

Syntax

=SF("TICKER", "dataType", "metric1&metric2&metric3")

Practical Examples

Real-time Data:

=SF("AAPL", "realTime", "price&eps&volume")

Returns three columns: current price, earnings per share, and trading volume

Statement Data:

=SF("TSLA", "incomeQ", "date&period&revenue&netIncome&eps")

Returns five rows/line-times from the statement

Combined with Batch Processing:

=SF(A2:A100, "realTime", "price&change&changePercent&volume&marketCap")

Returns data for 99 symbols across 5 metrics = 495 data points in 1 API call !!

Benefits

• Fewer API Calls: Get 10+ metrics with one function instead of 10 functions

• Faster Performance: Single API request is faster than multiple requests

• Easier Maintenance: Update one formula instead of many

• Better Organisation: Related data stays together in adjacent columns

4. Request Multiple Years at Once

The Feature

Rather than creating separate functions for each year of historical data, SheetsFinance allows you to specify a year range that returns multiple years in a single function call.

Syntax

=SF("TICKER", "dataType", "metric", "YYYY-YYYY")

Examples

Income Statement Over Time:

=SF("AAPL", "income", "all", "2000-2023")

Returns all income statement items for 24 years

Specific Metrics:

=SF("MSFT", "income", "revenue&netIncome&eps", "2015-2024")

Returns 10 years of revenue, net income, and EPS data

Supported Functions

This feature works with all functions that support the year parameters, for example:

• Financial Statements - Income Statement: income

• Financial Statements - Balance Sheet: balancesheet

• Financial Statements - Cash Flow: cashflow

• Key Ratios: ratios

• Growth: growth

• (...and more)

5. Minimise Dynamic Function Usage

Understanding the Problem

Google Sheets includes several "dynamic" functions that automatically recalculate frequently:

• TODAY() - Returns the current date

• NOW() - Returns the current date and time

• RAND() - Returns a random number

The Issue: Every time these functions recalculate, they trigger a cascade of recalculations in any formulas that reference them—including your SheetsFinance functions.

Impact on Performance

If you use TODAY() in multiple cells, each connected to SheetsFinance functions:

❌ Inefficient:
Cell A1: =SF_TIMESERIES("AAPL", TODAY()-365, TODAY())
Cell K1: =SF("MSFT", "historical", "close", TODAY()-30)
Cell M1: =SF("GOOGL", "incomeQ", "all", YEAR(TODAY()))

Each time TODAY() recalculates, all three SheetsFinance functions reload, potentially consuming quota unnecessarily.

Best Practice: Call Once, Reference Everywhere

✅ Efficient Approach:

1. Call the dynamic function once in a dedicated cell:

   Cell B1: =TODAY()

2. Reference that cell in all your formulas:

   Cell A1: =SF_TIMESERIES("AAPL", $B$1-365, $B$1)
   Cell K1: =SF("MSFT", "historical", "close", $B$1-30)
   Cell M1: =SF("GOOGL", "incomeQ", "all", YEAR($B$1))

Note the $B$1 absolute reference—this ensures the reference doesn't change when copying the formula.

Benefits

• Reduced Recalculations: Functions only reload when the date actually changes

• Improved Performance: Fewer cascading updates

• Quota Conservation: Prevents redundant API calls

• Easier Updates: Change the date in one place to affect all formulas

6. Evaluate Third-Party Extensions

The Hidden Cost of Multiple Extensions

While Google Sheets supports multiple add-ons and extensions working simultaneously, not all extensions are built with the same level of optimization. Some extensions may:

• Make redundant API calls

• Lack proper caching mechanisms

• Trigger unnecessary recalculations

• Consume shared quota inefficiently

SheetsFinance Optimisation Features

SheetsFinance is specifically designed with quota efficiency in mind:

Intelligent Caching:

• Non-real-time data (company info, historical financials) is cached in multiple stages both locally and server-side

• Subsequent calls to the same data often don't consume additional quota

• Cache automatically refreshes on an appropriate schedule

Request Batching:

• Multiple symbols handled in single API calls

• Automatic grouping of similar requests

• optimised payload sizes

Questions to Ask About Other Extensions

If you're using multiple financial data extensions, evaluate them:

1. Does it cache data? Or does it make a new API call every time?

2. Can it handle batch requests? Or does each symbol require a separate call?

3. Does it run auto-refreshing under the hood? Or only when you explicitly request data?

4. Is it actively maintained? Outdated extensions may have inefficient code

5. Do you actually need it? Can SheetsFinance handle the same tasks?

Audit Your Extensions

Steps to review installed add-ons:

1. Go to Extensions → Add-ons → Manage add-ons

2. Review each financial data extension

3. Remove extensions you're not actively using

4. Test SheetsFinance performance with and without other extensions enabled

Summary

Optimising your SheetsFinance usage comes down to six key principles:

1. Split files to control background refreshing

2. Batch symbols to minimise API calls

3. Chain metrics to get more data per request

4. Request year ranges instead of individual years

5. Call dynamic functions once and reference them

6. Audit extensions to ensure efficient quota usage

By following these guidelines, you'll enjoy faster spreadsheets, conserve your daily quota, and get more value from SheetsFinance.

Need Help?

• Documentation: Full SheetsFinance documentation

• Support: Contact [email protected]

• Community: Join our Discord Server for tips and discussions