Hi
I have designed a google sheet in which huge formulae are calculated to arrive at desired values. I am sharing the calculation below.
=(((((INDEX(QUERY(GoogleFinance(""NSE:INFY", "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:INFY", "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:INFY", "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:INFY", "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:INFY", "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:INFY", "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:INFY", "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:INFY", "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:INFY", "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:INFY", "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))))
This single calculation has to be made for the past 52 weeks for about 2000 stocks. The results of the calculation are accurate, but the google sheet literally takes a few hours every time to recalculate the whole sheet whenever this sheet is loaded. It shows a loading message and most of the time becomes unresponsive.
My work is fully on hold till I am able to sort this out.
Is there a way to shorten the calculation and load the google sheets faster ?
Varun
Bookmarks