1

Problem

In Google Sheets I have huge GOOGLEFINANCE formulas that return accurate results however the sheet is often unresponsive, and can take a few hours to recalculate when reloaded.

Is there a way to shorten the calculation and make the page load faster?

Current Formula

Accurately performs the calculation for a single stock over a single 52-week period:

=(((((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 2, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 2, 1))))/((((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 2, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 2, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 3, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 3, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 4, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 4, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 5, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 5, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 6, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 6, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 7, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 7, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 8, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 8, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 9, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 9, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 10, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 10, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 11, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 11, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 12, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 12, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 13, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 13, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 14, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 14, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 15, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 15, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 16, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 16, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 17, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 17, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 18, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 18, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 19, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 19, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 20, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 20, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 21, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 21, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 22, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 22, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 23, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 23, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 24, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 24, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 25, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 25, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 26, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 26, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 27, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 27, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 28, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 28, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 29, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 29, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 30, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 30, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 31, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 31, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 32, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 32, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 33, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 33, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 34, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 34, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 35, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 35, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 36, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 36, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 37, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 37, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 38, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 38, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 39, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 39, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 40, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 40, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 41, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 41, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 42, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 42, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 43, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 43, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 44, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 44, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 45, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 45, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 46, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 46, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 47, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 47, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 48, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 48, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 49, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 49, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 50, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 50, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 51, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 51, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 52, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 52, 1)))+((INDEX(QUERY(GoogleFinance("NSE:"&$A3, "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 53, 1))/(INDEX(QUERY(GoogleFinance("INDEXNSE:NIFTY_50", "price", TODAY()-560, TODAY(), "weekly"), "select Col2 order by Col1 desc limit 100"), 53, 1))))/52)-1)*10)

Overview

  1. Column A contains roughly 2,000 NSE-listed stocks.
  2. For each stock, I need to return a comparison of its weekly pricing over 52 weeks to the NIFTY_50 benchmark index's pricing for the same period.
  3. For each stock, I would like to perform the calculation 8 times, once in each of Columns G through N with the only difference being that each calculation's 52-week period is offset 1 week in the past from the previous column's:
    • Column G: week   0 through Week -51
    • Column H: week -1 through week -52
      . . . continues
    • Column N: week -7 through week -58
  4. Visualizing the Calculation:
    =((cur-wk)/AVERAGE(all-wks)-1)*10
    
    • cur-wk
      A single value calculated from the current week's stock price divided by the current week's benchmark index price.
    • all-wks
      A 1x52 array of values returned by calculating, for each of the 52 weeks in the period, that week's stock price divided by that week's benchmark index price.

Link to editable spreadsheet showing formula and required results

Cross-Posted

1
  • FYI, your original wasn't accurate because it assumed 52 weeks of data and 8% of your stocks don't have 52 weeks of history, and 9% don't have 59 weeks (required to offset the 52-week period back 7 weeks). Use AVERAGE to avoid this error. ie. AVERAGE(SEQUENCE(52,1,1,0))=1 and SUM(SEQUENCE(52,1,1,0))/52=1 and AVERAGE(SEQUENCE(40,1,1,0))=1 but SUM(SEQUENCE(40,1,1,0))/52<>1 See Calculate weeks of available data per stock in the demo sheet.
    – Blindspots
    Oct 24, 2023 at 5:06

1 Answer 1

0

Formula for 8 Weeks

  • Place in G2 to calculate G2:N
=LET(range,TOCOL(A2:A,1), end,TODAY(), start,end-560,
   calc,LAMBDA(c, INDEX(SORT(GOOGLEFINANCE(
   c, "price", start, end, "weekly"),1,0),,2)),
   idx,calc("INDEXNSE:NIFTY_50"),
   BYROW(range, LAMBDA(s, LET(
     avs,TOCOL(ARRAYFORMULA(calc("NSE:"& s)/idx),2),
     rw,SEQUENCE(ROWS(avs)),
     MAP(SEQUENCE(1,8,0), LAMBDA(s, LET(
       col,FILTER(avs, rw<53+s, rw>s),
       (INDEX(col,1)/AVERAGE(col)-1)*10)))))))

Demo Spreadsheet

Link to editable spreadsheet

Demo sheet includes added formula modifiers that illustrate approaches to help reduce recalculation times:

  1. G6 (checkbox)
    Enable/Disable calculation
  2. L6 (dropdown)
    Set the number of 52-week periods
  3. G7 (checkbox)
    Limit stocks by column B checkbox
  4. K7 & L7 (checkbox & #)
    Limit max # stocks returned (works with or without G7)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.