# 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.

# Cross-Posted

• 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. Commented Oct 24, 2023 at 5:06

# Formula for 8 Weeks

• Place in `G2` to calculate `G2:N`
``````=LET(range,TOCOL(A2:A,1), end,TODAY(), start,end-560,
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)))))))
``````