Multiple large GOOGLEFINANCE formulas make spreadsheet unresponsive

Question

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

• Excel Forums

• Google Support (Successfully answered)

Answer 1

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)