fewmath

Fetch prices from Yahoo data

Nov 4th, 2025 (edited)
1,972
0
Never
21
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. // var url = 'https://query2.finance.yahoo.com/v8/finance/chart/0P0001BM48.F?symbol=0P0001BM48.F&period1=1733961600&period2=1734393600&interval=5d'
  2. /**
  3.  * Fetches the closing price for a Yahoo Finance symbol on or immediately before a target date.
  4.  * Includes caching, browser headers, and flexible date parsing.
  5.  *
  6.  * @param {string} symbol The Yahoo Finance ticker symbol (e.g., "0P00008W4T.F", "AAPL").
  7.  * @param {Date|string} date The target date (cell reference or date string).
  8.  * @return {number|string} The closing price or an error message.
  9.  * @customfunction
  10.  */
  11. function YAHOO_PRICE_ON_DATE(symbol, date) {
  12.   if (!symbol) return "ERROR: Missing symbol.";
  13.  
  14.   // 1. Normalize Date input
  15.   const d = date instanceof Date ? date : new Date(date);
  16.   if (isNaN(d.getTime())) return "ERROR: Invalid date.";
  17.  
  18.   const targetTimestamp = Date.UTC(d.getFullYear(), d.getMonth(), d.getDate()) / 1000;
  19.   const cacheKey = `YF_${symbol.toUpperCase()}_${targetTimestamp}`;
  20.  
  21.   // 2. Check Cache first (reduces quota usage)
  22.   const cache = CacheService.getScriptCache();
  23.   const cachedPrice = cache.get(cacheKey);
  24.   if (cachedPrice !== null) {
  25.     return Number(cachedPrice);
  26.   }
  27.  
  28.   try {
  29.     const oneDay = 86400;
  30.     const period1 = targetTimestamp - 100 * oneDay;
  31.     const period2 = targetTimestamp + 1 * oneDay;
  32.  
  33.     const encodedSymbol = encodeURIComponent(symbol.trim());
  34.     const url = `https://query2.finance.yahoo.com/v8/finance/chart/${encodedSymbol}?symbol=${encodedSymbol}&period1=${period1}&period2=${period2}&interval=1d`;
  35.  
  36.     // 3. Fetch with custom User-Agent to avoid 403 Forbidden errors
  37.     const options = {
  38.       muteHttpExceptions: true,
  39.       headers: {
  40.         'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36'
  41.       }
  42.     };
  43.  
  44.     const response = UrlFetchApp.fetch(url, options);
  45.     if (response.getResponseCode() !== 200) {
  46.       return `ERROR: Yahoo Finance returned HTTP ${response.getResponseCode()}`;
  47.     }
  48.  
  49.     const json = JSON.parse(response.getContentText());
  50.     const result = json?.chart?.result?.[0];
  51.     const timestamps = result?.timestamp;
  52.     const closePrices = result?.indicators?.quote?.[0]?.close;
  53.  
  54.     if (!timestamps?.length || !closePrices?.length) {
  55.       return "ERROR: No price data returned for this symbol.";
  56.     }
  57.  
  58.     // 4. Reverse search for the most recent valid close price on or before target date
  59.     for (let i = timestamps.length - 1; i >= 0; i--) {
  60.       if (timestamps[i] <= targetTimestamp && closePrices[i] != null) {
  61.         const price = closePrices[i];
  62.        
  63.         // Cache historical prices for 30 minutes (1800 seconds)
  64.         cache.put(cacheKey, price.toString(), 1800);
  65.         return price;
  66.       }
  67.     }
  68.  
  69.     return "Price not found for date.";
  70.   } catch (e) {
  71.     return `ERROR: ${e.message || e.toString()}`;
  72.   }
  73. }
Advertisement
Comments
  • fewmath
    1 year
    # text 0.28 KB | 0 0
    1. You must fetch the Yahoo Finance code for each stock to use it in this function. It very often coincides with the standard ticker, but it can sometimes be different. There is a daily query limit for yahoo finance, so use this function wisely.
    2.  
    3. Usage: =YAHOO_PRICE_ON_DATE('GOOG', TODAY())
    • fewmath
      61 days
      # text 0.15 KB | 0 0
      1. I added a 30 minutes Cache functionality to prevent too many queries (20,000/day is the limit). This number can be reduced if your query needs are small.
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
Add Comment
Please, Sign In to add comment