Build a live stock-portfolio tracker

Northwind keeps a slice of its cash reserves in a small basket of listed shares, and the finance team likes to know where that basket stands without opening a broker app. The trouble is that a manual update means looking up each ticker, copying the price, and recalculating the gain by hand — so it happens once a month at best, and the numbers in the board pack are always a little stale.

This script turns the holdings sheet into a self-updating tracker. It walks each row, fetches the latest price from a free quote API, and writes both the current price and the gain or loss back into the sheet. Run it on a schedule and the treasury position is always current the moment anyone opens the tab.

What you’ll need

• A Google Sheet with one row per holding and a header in row 1. The script expects columns named ticker, qty, costBasis, currentPrice, and gainLoss — the last two are filled in by the script.
• The spreadsheet’s ID, pasted into the PORTFOLIO_SHEET_ID constant below.
• Nothing else — the Stooq quote endpoint used here needs no API key or sign-up.

The script

// The spreadsheet that holds your portfolio. One row per holding.
const PORTFOLIO_SHEET_ID = '1abcPortfolioId';

// Free quote endpoint — returns a small JSON payload per symbol.
const QUOTE_API = 'https://api.stooq.com/q/?f=sp&h&e=json&s=';

/**
 * Reads every holding, fetches the latest price for each ticker, and
 * writes the current price and gain/loss back into the sheet.
 */
function refreshPortfolio() {
  const sheet = SpreadsheetApp.openById(PORTFOLIO_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and map header names to column indexes.
  const [header, ...rows] = sheet.getDataRange().getValues();
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  if (!rows.length) {
    Logger.log('No holdings to refresh — nothing to do.');
    return;
  }

  // 2. Walk each holding row in turn.
  rows.forEach((row, i) => {
    const ticker = row[col.ticker];
    if (!ticker) return; // skip blank rows

    // 3. Fetch a quote for this ticker. Stooq wants the symbol lower-cased.
    const price = fetchPrice(ticker);
    if (price === null) {
      Logger.log('No price for ' + ticker + ' — leaving the row untouched.');
      return;
    }

    // 4. Write the current price back into its column.
    sheet.getRange(i + 2, col.currentPrice + 1).setValue(price);

    // 5. Gain/loss = (price - what we paid) x how many shares we hold.
    const gainLoss = (price - row[col.costBasis]) * row[col.qty];
    sheet.getRange(i + 2, col.gainLoss + 1).setValue(gainLoss);
  });

  Logger.log('Refreshed ' + rows.length + ' holdings.');
}

/**
 * Fetches the latest price for a single ticker. Returns the price as a
 * number, or null if the symbol is unknown or the API returns nothing.
 */
function fetchPrice(ticker) {
  const res = UrlFetchApp.fetch(QUOTE_API + ticker.toLowerCase(), {
    muteHttpExceptions: true,
  });
  const data = JSON.parse(res.getContentText());
  const symbol = data.symbols && data.symbols[0];
  return symbol && symbol.price ? symbol.price : null;
}

How it works

1. refreshPortfolio opens the portfolio spreadsheet and reads the whole sheet in one call, splitting the header row from the data rows.
2. It builds a col lookup so the rest of the code can say col.ticker instead of remembering that the ticker lives in column 1 — rearrange the sheet and the script still works.
3. If there are no data rows it logs a message and stops, avoiding pointless API calls.
4. For each row it calls fetchPrice, which hits the Stooq quote endpoint with the lower-cased ticker and pulls price out of the JSON.
5. A missing or unknown ticker returns null, and that row is left exactly as it was rather than being overwritten with bad data.
6. When a price comes back, it writes the price into the currentPrice column and the computed gain or loss — (price - costBasis) * qty — into the gainLoss column.

Example run

Before the run, the sheet holds the cost basis and quantity you entered by hand:

ticker	qty	costBasis	currentPrice	gainLoss
AAPL.US	40	165.00
MSFT.US	25	310.00
VOD.UK	800	0.78

After refreshPortfolio runs, the last two columns are filled in:

ticker	qty	costBasis	currentPrice	gainLoss
AAPL.US	40	165.00	189.50	980.00
MSFT.US	25	310.00	421.30	2782.50
VOD.UK	800	0.78	0.71	-56.00

Total the gainLoss column with a =SUM() and you have the treasury position at a glance.

Trigger it

Prices only change while markets are open, so a daily refresh after the close is plenty:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose refreshPortfolio, a Time-driven source, a Day timer, and a slot in the evening such as 6pm–7pm.
4. Save, and approve the authorisation prompt the first time.

Watch out for

• The Stooq endpoint expects exchange-suffixed symbols such as AAPL.US or VOD.UK. A bare AAPL may return nothing — check the symbol format on the Stooq site if a row stays blank.
• Free quote feeds are best-effort. Prices can be delayed by 15 minutes or more and the service has no uptime guarantee, so treat the figures as indicative, not as a trading feed.
• The script does no currency conversion. A UK-listed share priced in pence and a US share priced in dollars will sit side by side — keep each holding’s cost basis in the same currency as its quote, or convert in a separate column.
• Fetching one quote per row means one UrlFetchApp call per holding. That is fine for a few dozen holdings but counts against the daily UrlFetchApp quota, so do not point this at hundreds of tickers.
• A failed fetch leaves the old currentPrice in place. If a price looks stale, check the execution log to see which ticker the API could not resolve.