Google Sheets Financial Data

Prerequisites

Before you start, make sure you have:

A Google account with access to Google Sheets
A stockdata.dev API key (get one free)

The free tier includes 1,000 API calls per month, which is plenty for a personal financial dashboard that refreshes a few times per day.

Setup - Open Apps Script Editor

Google Sheets lets you write custom functions using Apps Script, Google's JavaScript-based scripting platform. Here's how to open it:

Open a new or existing Google Sheet
Click Extensions in the menu bar
Click Apps Script
This opens the script editor in a new tab

Delete any placeholder code in the editor. You'll paste the full script in the next step.

The Code

Paste the following into the Apps Script editor. This creates five custom spreadsheet functions that fetch financial data from the stockdata.dev API.

Apps Script

const API_KEY = "your_api_key_here";
const BASE_URL = "https://api.stockdata.dev/v1";

/**
 * Helper: fetch and cache financial data for a ticker.
 * Uses CacheService to avoid repeated API calls (15 min TTL).
 */
function fetchFinancials_(ticker) {
  ticker = ticker.toString().toUpperCase().trim();
  const cache = CacheService.getScriptCache();
  const cacheKey = "fin_" + ticker;
  const cached = cache.get(cacheKey);

  if (cached) {
    return JSON.parse(cached);
  }

  const url = BASE_URL + "/company/" + ticker + "/financials?period=annual&limit=1";
  const options = { headers: { "X-API-Key": API_KEY } };
  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText());

  if (data.financials && data.financials.length > 0) {
    cache.put(cacheKey, JSON.stringify(data), 900); // 15 min TTL
    return data;
  }
  throw new Error("No financial data for " + ticker);
}

/** Returns the company name for a ticker. */
function COMPANY_NAME(ticker) {
  const data = fetchFinancials_(ticker);
  return data.name;
}

/** Returns the latest annual revenue. */
function REVENUE(ticker) {
  const data = fetchFinancials_(ticker);
  return data.financials[0].revenue;
}

/** Returns the latest annual net income. */
function NET_INCOME(ticker) {
  const data = fetchFinancials_(ticker);
  return data.financials[0].net_income;
}

/** Returns the latest earnings per share. */
function EPS(ticker) {
  const data = fetchFinancials_(ticker);
  return data.financials[0].eps;
}

/** Returns the latest total assets. */
function TOTAL_ASSETS(ticker) {
  const data = fetchFinancials_(ticker);
  return data.financials[0].total_assets;
}

Click the Save button (or press Ctrl+S) in the Apps Script editor. You can name the project anything you like, such as "StockData Formulas".

Replace "your_api_key_here" with your actual stockdata.dev API key. Keep this key private -- anyone with access to the spreadsheet's script can see it.

How the caching works

The fetchFinancials_() helper uses Google's built-in CacheService to store API responses for 15 minutes. This means if you have five formulas in a row all referencing AAPL, only one API call is made. The trailing underscore in the function name is an Apps Script convention that makes it private -- it won't appear as a formula in your sheet.

Using the Formulas

Go back to your Google Sheet. Your custom functions are now available just like any built-in formula. Here's an example layout:

Spreadsheet

     A           B                       C                D              E              F
  ┌──────────┬───────────────────────┬────────────────┬──────────────┬──────────────┬──────────────┐
1 │ Ticker   │ Company               │ Revenue        │ Net Income   │ EPS          │ Total Assets │
  ├──────────┼───────────────────────┼────────────────┼──────────────┼──────────────┼──────────────┤
2 │ AAPL     │ =COMPANY_NAME(A2)     │ =REVENUE(A2)   │ =NET_INCOME( │ =EPS(A2)     │ =TOTAL_ASSET │
  │          │                       │                │   A2)        │              │   S(A2)      │
  ├──────────┼───────────────────────┼────────────────┼──────────────┼──────────────┼──────────────┤
3 │ MSFT     │ =COMPANY_NAME(A3)     │ =REVENUE(A3)   │ =NET_INCOME( │ =EPS(A3)     │ =TOTAL_ASSET │
  │          │                       │                │   A3)        │              │   S(A3)      │
  ├──────────┼───────────────────────┼────────────────┼──────────────┼──────────────┼──────────────┤
4 │ GOOGL    │ =COMPANY_NAME(A4)     │ =REVENUE(A4)   │ =NET_INCOME( │ =EPS(A4)     │ =TOTAL_ASSET │
  │          │                       │                │   A4)        │              │   S(A4)      │
  └──────────┴───────────────────────┴────────────────┴──────────────┴──────────────┴──────────────┘

Once the formulas run, the cells fill in with live data:

Result

     A           B                       C                D              E          F
  ┌──────────┬───────────────────────┬────────────────┬──────────────┬──────────┬──────────────────┐
1 │ Ticker   │ Company               │ Revenue        │ Net Income   │ EPS      │ Total Assets     │
  ├──────────┼───────────────────────┼────────────────┼──────────────┼──────────┼──────────────────┤
2 │ AAPL     │ Apple Inc             │ 383,285,000,000│ 96,995,000,0 │ 6.42     │ 352,583,000,000  │
  ├──────────┼───────────────────────┼────────────────┼──────────────┼──────────┼──────────────────┤
3 │ MSFT     │ Microsoft Corp        │ 245,122,000,000│ 88,136,000,0 │ 11.86    │ 512,163,000,000  │
  ├──────────┼───────────────────────┼────────────────┼──────────────┼──────────┼──────────────────┤
4 │ GOOGL    │ Alphabet Inc          │ 350,018,000,000│ 100,681,000, │ 8.17     │ 432,080,000,000  │
  └──────────┴───────────────────────┴────────────────┴──────────────┴──────────┴──────────────────┘

Custom functions may take a few seconds to load the first time. After that, results are cached for 15 minutes.

Building a Financial Dashboard

With these formulas, you can build a full comparison dashboard. Here are some ideas:

Multi-stock comparison

List 10-20 tickers in column A and use the formulas across columns B through F. Add conditional formatting to highlight the highest revenue or best EPS in each column. Google Sheets' built-in charting can then visualize the data as bar or column charts.

Sector breakdown

Group companies by sector (Tech, Healthcare, Finance) with section headers. Use SUM() and AVERAGE() on the revenue and net income columns to see sector-level totals.

Calculated metrics

Combine the raw data with standard Sheets formulas to compute additional metrics:

Spreadsheet

# Profit margin
=NET_INCOME(A2) / REVENUE(A2)

# Format revenue as billions
=REVENUE(A2) / 1000000000

# Compare two companies
=REVENUE("AAPL") - REVENUE("MSFT")

Auto-Refresh with Triggers

By default, custom functions only recalculate when their inputs change. To refresh the data automatically, you can set up a time-based trigger.

In the Apps Script editor, add this function: