+ Reply to Thread
Results 1 to 23 of 23

Need for Speed in Excel, hardware question

  1. #1
    Registered User
    Join Date
    02-05-2019
    Location
    Jyllinge, Denmark
    MS-Off Ver
    Professionel 2010 64 bit
    Posts
    13

    Need for Speed in Excel, hardware question

    Hi there, questions in bold below

    I'm 58, started in Lotus 1-2-3, then Lotus Symphony, and then Excel. I had a company that developed Excel sheets for people, happy customers but not enough of them :-). Currently employed, and making some money on the side in the stock market. To do the latter, I create and test models in Excel. And they have become quite complex and demanding, I work with 5-8 variables.

    My question here is about speed. My latest sheet takes about 1 minute to calculate, and to optimize on best combinations of the variables I use 2 dimensional tables with around 10x10 size, meaning about 100 minutes of calculation time.

    Yes, there are many ways to speed up an Excel sheet, and while any advice is welcome, I've been through most of that in the last months. So my question here is about hardware. Currently I calculate on a standalone Dell Optiplex:
    HDS66B2, G456023.50 GHz Ram 20GB 64-bit operating system, x64 processor, W10, and I run Excel 2010 64-bit

    My PC knowledgeable brother in law recommends the following HW to speed up:
    OptiPlex with Intel Core i7-8700 (not i7-8700T), it has slower 3.20 processor but 6 cores and 12MB cache, 12 threads

    Most of these specs are not something I understand. I trust him that my calculation time would be reduced. But how much? The PC is over $1000, I would be willing to buy it if I got a 80%+ reduction in calculation time, but if it's only a 10-30% reduction, I would save my money. So how much reduction in calculation time can I expect? Anybody got some experience on that? In the 1980s, when I got a new PC at work, everything was so much faster, every time. But I think that the improvements are smaller these days?

    That was the main question. Here are two additional questions:
    -Excel 2010 is as fast or faster than 2013 and 2016, right?
    -Is there anywhere on the web one could (maybe for a fee) place the sheet and have faster calculation? I've tried the possibility Microsoft offers, but it was slower. The sheet is 27MB

    Thanks for reading this far!

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Need for Speed in Excel, hardware question

    The best performance usually comes from removing calculations from the worksheet....

    It's impossible to advise any further on how to optimise, without understanding your workbook.

    It's very unlikely you'll achieve the performance gains you wish for from a hardware upgrade alone.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    02-05-2019
    Location
    Jyllinge, Denmark
    MS-Off Ver
    Professionel 2010 64 bit
    Posts
    13

    Re: Need for Speed in Excel, hardware question

    Thanks Olly. Not any formulas to remove, need them all. The sheet is 95% optimized, I'm sure.

    Since you're a Forum Guru, can I ask your view on these as well? :-)

    -Excel 2010 is as fast or faster than 2013 and 2016, right?
    -Is there anywhere on the web one could (maybe for a fee) place the sheet and have faster calculation?

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Need for Speed in Excel, hardware question

    I very much doubt it's 95% optimized, if it still relies on lots of worksheet formulae.

    There's little performance difference for traditional calculation between 2010 / 2016... and there are currently no online Excel services which will process traditional excel calculation more efficiently on server, than client.

    I bet we could rewrite your workbook to use PowerPivot, instead of traditional formulas, and get a massive performance increase, though.

  5. #5
    Registered User
    Join Date
    02-05-2019
    Location
    Jyllinge, Denmark
    MS-Off Ver
    Professionel 2010 64 bit
    Posts
    13

    Re: Need for Speed in Excel, hardware question

    What is Power Pivot? Is it useful for me? " Anyone who regularly uses PivotTables and/or VLOOKUP is very much the “target audience” for Power Pivot", I have no pivots or Lookups, I do have some Sumifs and the occasional Offset formula. And the interdependenices are plentyful. And plenty of I
    f formulas

    But...has nobody lately bought a new not cheapo PC and noticed a difference in speed. Most apparent I would guess if you use 2 dimensional tables?

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Need for Speed in Excel, hardware question

    I hope you don’t have offset formulas in your interdependencies...

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Need for Speed in Excel, hardware question

    Do you have complex formulae? Array Formulae? Full column references in any of the formulae you have? Do you have formulae that return a null value in preparation for future data input? Do you have Conditional Formatting?

    How big is the workbook? How many sheets are there in it? How many columns and rows are there in each sheet? Does Excel recognise the "true" last cell on each sheet, or does it think it is further across/down the sheet?

    So many questions ...
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  8. #8
    Registered User
    Join Date
    02-05-2019
    Location
    Jyllinge, Denmark
    MS-Off Ver
    Professionel 2010 64 bit
    Posts
    13

    Re: Need for Speed in Excel, hardware question

    Quote Originally Posted by TMS View Post
    Do you have complex formulae? Array Formulae? Full column references in any of the formulae you have? Do you have formulae that return a null value in preparation for future data input? Do you have Conditional Formatting?

    How big is the workbook? How many sheets are there in it? How many columns and rows are there in each sheet? Does Excel recognise the "true" last cell on each sheet, or does it think it is further across/down the sheet?

    So many questions ...
    Those sheet improvement ideas, been through them all, thanks. I can make my sheet faster, maybe by 30%, but then I lose functionalities, don't want to do that. I want to decide to either live with it, or spend money to get it considerably faster. Thanks

  9. #9
    Registered User
    Join Date
    02-05-2019
    Location
    Jyllinge, Denmark
    MS-Off Ver
    Professionel 2010 64 bit
    Posts
    13

    Re: Need for Speed in Excel, hardware question

    But as a fyi:
    My sheet has 10 months of data per 10 minutes Dow Jones, 26000 lines, and per line the decision is to buy or sell or if already bought or sold when to close the trade. Add to that, per line, a Stop Loss that can move based on previous movements (trailing). Variables are direction based on what time period to look at (so two variables), what time period to trade in (two variables), Stop Loss distance, Stop Loss trailing or not and at what trigger if yes (two variables). SL can also be based on ATR from the day before, multiplied by a variable. Also bottom and top ATR, you don't trade outside that range (two variables). At one point I had a SL based on 3 dimensional formula with ATR, but didn't gain anything
    All this on 5 time frames per 24 hour, multiplies everything by 5. Add to that: direction in period 2-5 is depending on points won or lost in period 1 with a limit, so period 2-5 have an extra variable.
    Etc. etc.

    I've spent month developing it, and weeks optimizing it, every time it got too slow for me. I sincerely doubt that this programming can be transferred to another tool without months of work.

    So, back to the hardware please?

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Need for Speed in Excel, hardware question

    I doubt anybody can give you a definitive answer without actually testing your workbook.

    Here is a similar type of question
    https://www.reddit.com/r/excel/comme...ultiple_cores/

    Maybe the real question is what do you gain by speeding up the calculation? Will faster results make you more money?
    Cheers
    Andy
    www.andypope.info

  11. #11
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Need for Speed in Excel, hardware question

    Quote Originally Posted by No Quarter View Post
    So, back to the hardware please?
    So far the consensus (from some, it has to be said, pretty knowledegable responders) has been the hardware will make a negligible difference - it's all in the formulae. So there you have the answer to your "hardware question".

    You therefore need to decide whether another platform is the way to go, or whether to stick with the model you have spent countless hours building and refining.

    Try reading a few of Charles Wiliams' articles here : he has a thing about making Excel work faster and has written about Excel's calculation secrets, how to optimise formulae etc as well as a blog on Excel performance.

    He also sells software he has developed (FastExcel) that claims to show bottlenecks in complex workbooks (I have not used this software, so cannot vouch for how well it works - it's just something for you to consider, but he is a Microsoft accredited MVP, so he does know what he's talking about).

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  12. #12
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Need for Speed in Excel, hardware question

    Quote Originally Posted by No Quarter View Post
    But as a fyi:
    My sheet has 10 months of data per 10 minutes Dow Jones, 26000 lines, and per line the decision is to buy or sell or if already bought or sold when to close the trade. Add to that, per line, a Stop Loss that can move based on previous movements (trailing). Variables are direction based on what time period to look at (so two variables), what time period to trade in (two variables), Stop Loss distance, Stop Loss trailing or not and at what trigger if yes (two variables). SL can also be based on ATR from the day before, multiplied by a variable. Also bottom and top ATR, you don't trade outside that range (two variables). At one point I had a SL based on 3 dimensional formula with ATR, but didn't gain anything
    All this on 5 time frames per 24 hour, multiplies everything by 5. Add to that: direction in period 2-5 is depending on points won or lost in period 1 with a limit, so period 2-5 have an extra variable.
    Etc. etc.

    I've spent month developing it, and weeks optimizing it, every time it got too slow for me. I sincerely doubt that this programming can be transferred to another tool without months of work.

    So, back to the hardware please?
    This all indicates that you have a solid understanding of your business logic - so a rewrite would be merely reconstructing existing logic.

    Transferring that from Excel formulae to DAX in a PowerPivot model would be straightforward, and would deliver huge performance gains. Really - 26k rows is nothing.

    If you can share an example workbook (with data suitably anonymised), I'd love to take a look...

  13. #13
    Registered User
    Join Date
    02-05-2019
    Location
    Jyllinge, Denmark
    MS-Off Ver
    Professionel 2010 64 bit
    Posts
    13

    Re: Need for Speed in Excel, hardware question

    Quote Originally Posted by Olly View Post
    would deliver huge performance gains..

    If you can share an example workbook (with data suitably anonymised), I'd love to take a look...
    Again, I seriously doubt it, but I'm always willing to learn new stuff. And I appreciate the offer. How do I get the 27MB file to you the best?
    Last edited by No Quarter; 02-06-2019 at 07:03 AM.

  14. #14
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Need for Speed in Excel, hardware question

    OneDrive works well. I'll PM you an email address to share it with.

  15. #15
    Registered User
    Join Date
    02-05-2019
    Location
    Jyllinge, Denmark
    MS-Off Ver
    Professionel 2010 64 bit
    Posts
    13

    Re: Need for Speed in Excel, hardware question

    Learned something interesting right now, I was sitting preparing the file to be sent to Olly, doing a brief explanation. It reminded me that I have a set of formulas per data point (10 minute interval) per day, I have 134 calculation lines per day. I have this so I can change the timing of the day I exit, but only the formulas at the time of the day where I exit are used in further calculations. So if I do not change the timing of the day for exit, and I haven't for months, I can remove 133 out of 134 lines of formulas. Thereby decreasing the amount of formulas in my sheet by maybe 99%.

    But guess what. After I tried it and calculated a few times, it took exactly the same time as before. I guess that means that Excel is very good at determining which formulas need to be calculated and which formulas can be ignored because they won't affect other formulas/cells.

    The file is only 14MB now (from 23MB), but no faster!

  16. #16
    Registered User
    Join Date
    02-05-2019
    Location
    Jyllinge, Denmark
    MS-Off Ver
    Professionel 2010 64 bit
    Posts
    13

    Re: Need for Speed in Excel, hardware question

    Quote Originally Posted by Andy Pope View Post
    Will faster results make you more money?
    I test many ideas. 1 of 10 increases my result, 9 of 10 are just a waste of time. Having more speed decreases time spent on each idea, and it allows more variables to be in play at the same time. During a test of an idea, I probably calculate 50 times, and all of them trying to only calculate one variable, thereby maybe losing some info from varying two at a time. I run 2 way tables maybe 10 times, and have to delete the one I just made before calculating the new one. All of this takes my time and stops me from seeing a lot of combinations of the variables. Ideally I could do a 7 dimension table with 10 values for each variable, that would take the rest of my life to calculate. (I've tried Goal Seek, it seems to be overwhelmed by my complexity, it never gets close to the optimum in a reasonable time)

    So the short answer? Yes
    Last edited by No Quarter; 02-06-2019 at 10:20 AM.

  17. #17
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Need for Speed in Excel, hardware question

    Sounds like an opportunity for a VBA routine to process sets of variables. Clear the table, drop the values in, process the data, store the results, go again, and again, ... Maybe run overnight, come down to a raft of output. Just a thought.

  18. #18
    Registered User
    Join Date
    02-05-2019
    Location
    Jyllinge, Denmark
    MS-Off Ver
    Professionel 2010 64 bit
    Posts
    13

    Re: Need for Speed in Excel, hardware question

    OK, let me answer my own question. I bought a Dell Precision Workstation, i7-8700 with 6 cores, 12MB Cache, 12 threads etc etc. What took 9 seconds on the Optiplex (449 values tables), now takes just under 3 seconds. Worth the $$$? Not sure...

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Need for Speed in Excel, hardware question

    So it's cost you $1000+ for a 6 second improvement ... is that close enough to your hoped for 80% ? If my calculations are right, that's about 2/3 less, 66%.

    To late to get your money back?

  20. #20
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Need for Speed in Excel, hardware question

    sometimes the only way to be sure if it is a waste of money, would be to spend the money.. And it is also a matter of perspective

    It is 66% decrease.. on every calculation.. , wished (dreamed?) target was 80%, but it is also more then double of the low-end treshold of 30% which would be considered a waste of money.
    So all in all I think a good improvement, just not as high as you were hoping for. But then again on what was the 80% based? was it based on reasonable expections or more a gut-feeling, something that felt good for the amount of money to be spent?

  21. #21
    Registered User
    Join Date
    02-05-2019
    Location
    Jyllinge, Denmark
    MS-Off Ver
    Professionel 2010 64 bit
    Posts
    13

    Re: Need for Speed in Excel, hardware question

    The 80% was just a wish, not an estimate. The 9 second test file, is just for testing, most of my files are from 30 seconds to 5 minutes, on those a 2/3 reduction will matter. Thinking about it now, I may spend most of this weekend testing 4 ideas I've had, and the purchase will save me hours in total and frustrations from waiting, and may even produce better results. So now that I thought about it, for me, where doing these models are correlated to what I earn in the stock market, yes it was worth it. Thanks for input.

  22. #22
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Need for Speed in Excel, hardware question

    Still think automating the process with VBA is worth considering. Think how many tests you could run overnight and wake up to sets of results ... and even discard the poor ones.

  23. #23
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Need for Speed in Excel, hardware question

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. Does Excel have its own limits, regardless of hardware?
    By jonnyyyl in forum Excel General
    Replies: 18
    Last Post: 07-31-2014, 11:13 PM
  2. Optimal Hardware for Increased Calculation Speed
    By Celex005 in forum Excel General
    Replies: 6
    Last Post: 01-23-2013, 11:50 AM
  3. How to get the most out of EXCEL? (Hardware-Wise)
    By zealot in forum Excel General
    Replies: 12
    Last Post: 07-08-2012, 10:34 AM
  4. Hardware to maximize macro run speed
    By DaveF in forum Excel General
    Replies: 4
    Last Post: 05-25-2012, 07:39 PM
  5. Replies: 6
    Last Post: 07-05-2006, 10:20 AM
  6. Hardware effect on Chart creation speed
    By Denis Boucher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2006, 03:45 PM
  7. Hardware Question USB & NT4.0
    By Christmas May in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-02-2005, 04:06 PM

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