+ Reply to Thread
Results 1 to 7 of 7

Huge calculations in a cell, Google sheets not loading

  1. #1
    Registered User
    Join Date
    10-08-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2000
    Posts
    4

    Huge calculations in a cell, Google sheets not loading

    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

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,053

    Re: Huge calculations in a cell, Google sheets not loading

    I am not even going to try and decipher that monster!!

    Not sure of your excel level, but I have often seen relatively new members build huge long formulas, trying to impress themselves and others, of their excel prowess, when the reality is that a bunch of smaller formulas in helper columns are much simpler to trouble shoot/modify/edit etc.

    Take a look at your formula and see if there are any duplicate parts, then try and break those out and put them into a helper column once.
    For instance, you have 18 identical parts that only differ in the column they reference - if removing all those TODAY functions doesnt speed things up for you, try putting each of those in their own column (which you can hide if needed), then running the calc based on those cell refs.
    Remove all those TODAY() references, put TODAY() in it's own cell, and then reference that. (you use that volatile function almost 40 times)
    To just make that whole thing shorter, text like "select Col2 order by Col1 desc limit 100" could be put in it's own cell and then also referenced. (you could even give that cell a range name so that, in the formula, it makes it easier to read.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-08-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2000
    Posts
    4

    Re: Huge calculations in a cell, Google sheets not loading

    Dear FDibbins


    I can understand

    Actually I did not post the complete formula here, the whole formula is 5 times of this truncated version.

    I am new to google sheets and don't know how to shorten it and need assistance in the same.

    I have a link for my editable google sheet, but unable to post that due to forum rules. You can view the link in the .txt file attached

    docs.google.com/spreadsheets/d/ 1gK_gRSHaAygcI1BR9TcTyvKG-jlcdJL9B8Kevfws9K4/edit#gid=0

    In the meantime, I shall try to understand your answer.


    Thanks

    Varun
    Attached Files Attached Files
    Last edited by rafale_777; 10-18-2023 at 03:15 AM. Reason: Attachment

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2508 Win 11
    Posts
    24,959

    Re: Huge calculations in a cell, Google sheets not loading

    Rule 7: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this.

    I have added the crosspost reference for you today. Please comply with this and all our rules in the future

    However, if you continue to crosspost, you can expect to have your thread BLOCKED until you update it yourself.

    https://support.google.com/docs/thread/239659672

    https://webapps.stackexchange.com/qu...ts-not-loading

    If you have posted to other sites, please indicate the links so that no one duplicates the efforts of others.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Registered User
    Join Date
    10-08-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2000
    Posts
    4

    Re: Huge calculations in a cell, Google sheets not loading

    Dear Alan


    My mistake, will be careful in future. Thank you


    Varun

  6. #6
    Registered User
    Join Date
    10-08-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2000
    Posts
    4

    Re: Huge calculations in a cell, Google sheets not loading

    Anyone who is looking for an answer to this query can follow the Google Support page, I am unable to post links.
    link shared by Alan above

    This question has been answered in full at Google Support, the whole conversation thread is there


    Varun

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,924

    Re: Huge calculations in a cell, Google sheets not loading

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Google Sheets. Can I connect offline data from excel to google sheets?
    By drlemur39 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 11-15-2021, 10:40 AM
  2. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  3. Which Formulas to use for Huge Calculations
    By toci in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-12-2016, 10:07 AM
  4. email row contents based on cell values (google sheets populated by google forms)
    By reedg in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-13-2016, 02:55 PM
  5. Run VBA code before huge calculations
    By lookingforhelp1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2015, 11:55 AM
  6. Replies: 6
    Last Post: 03-18-2013, 01:11 PM
  7. Loading calculations to a cell
    By jartzh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-06-2009, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1