Google Sheets stock price formulas, without GOOGLEFINANCE

const HOST = 'yahoo-finance-turbo.p.rapidapi.com';

function turbo_(path, params) {
  const key = PropertiesService.getScriptProperties().getProperty('RAPIDAPI_KEY');
  if (!key) throw new Error('Add RAPIDAPI_KEY under Project Settings > Script properties');
  const qs = Object.keys(params).map((k) => k + '=' + encodeURIComponent(params[k])).join('&');
  const url = 'https://' + HOST + path + '?' + qs;
  const cache = CacheService.getScriptCache();
  const hit = cache.get(url);
  if (hit) return JSON.parse(hit);
  const res = UrlFetchApp.fetch(url, {
    headers: { 'X-RapidAPI-Key': key, 'X-RapidAPI-Host': HOST },
    muteHttpExceptions: true,
  });
  if (res.getResponseCode() !== 200) throw new Error('HTTP ' + res.getResponseCode() + ': ' + res.getContentText().slice(0, 150));
  try { cache.put(url, res.getContentText(), 300); } catch (e) {} // 5 min; values over 100 KB are not cached
  return JSON.parse(res.getContentText());
}

/**
 * Last price for one ticker.
 * @param {string} symbol Yahoo symbol, e.g. "AAPL" or "VOD.L"
 * @customfunction
 */
function STOCKPRICE(symbol) {
  const q = turbo_('/market/v2/get-quotes', { region: 'US', symbols: String(symbol).trim().toUpperCase() }).quoteResponse.result[0];
  if (!q) throw new Error('Unknown symbol ' + symbol);
  return q.regularMarketPrice;
}

/**
 * Price, change % and volume for up to 20 tickers in one request.
 * @param {A2:A20} symbols A column of Yahoo symbols
 * @customfunction
 */
function STOCKQUOTES(symbols) {
  const list = [].concat(symbols).flat().map((s) => String(s).trim().toUpperCase()).filter(Boolean);
  if (list.length > 20) throw new Error('Max 20 symbols per call');
  const rows = turbo_('/market/v2/get-quotes', { region: 'US', symbols: list.join(',') }).quoteResponse.result;
  const by = Object.fromEntries(rows.map((q) => [q.symbol, q]));
  return list.map((s) => (by[s] ? [by[s].regularMarketPrice, by[s].regularMarketChangePercent / 100, by[s].regularMarketVolume] : ['', '', '']));
}

/**
 * Closing price on a date (the last trading day on or before it).
 * @param {string} symbol Yahoo symbol
 * @param {date} date A date cell or DATE(2026,1,2)
 * @customfunction
 */
function STOCKCLOSE(symbol, date) {
  const tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  const want = Utilities.formatDate(new Date(date), tz, 'yyyy-MM-dd');
  const days = (Date.now() - new Date(date)) / 864e5;
  const range = days < 25 ? '1mo' : days < 175 ? '6mo' : days < 360 ? '1y' : days < 1820 ? '5y' : 'max';
  const r = turbo_('/stock/v3/get-chart', { symbol: String(symbol).trim().toUpperCase(), interval: '1d', range: range, region: 'US' }).chart.result[0];
  const ex = r.meta.exchangeTimezoneName;
  const close = r.indicators.quote[0].close;
  let best = '';
  r.timestamp.forEach((t, i) => {
    if (close[i] != null && Utilities.formatDate(new Date(t * 1000), ex, 'yyyy-MM-dd') <= want) best = close[i];
  });
  return best;
}