Big Excel model, need speed

[No Quarter]

My trading technique is to predict direction based on history, which involves variables like historic stock prices, time interval to test, optimum SL, trailing Sl etc. I'm running my model on DOW on 10 minutes data since April, 23000 lines. That makes for a quite complicated excel sheet. Currently it takes med 2-4 minutes to calculate tables on 2 variables, so I waste a lot of time waiting. Any PC experts here? My desktop is about 3.9 GHz and stuffed with RAM. Can I buy anything that siginificantly speeds this up? Or is there a solution in a cloud or similar where calculation is a lot faster.

Thanks

 

[malaguti]

you've gone through all the basics presumably..
checked for multi-threading? checked your language settings? disabled addons or any macros? your referencing named ranges rather than copying each cell etc etc, structured it as a table, rather than individual rows
very difficult to help without knowing how you've structured your workbook. google it there's very common suggestions
23000 lines is not that much and should in be in the region of a minute to reprocess

 

[Atilla]

From PC hardware perspective need more info. Fwiw these are the areas I'd look into. Sorry if its not well constructed but it is a brain storm from years gone by. 🙂

CPU / RAM / DISK / NETWORK can all effect performance. They would be key areas to check. You can do this using Open Resource Meter and see what are the top processes utlising these key elements. .

There then is the CPU type and amount of cache it has available for it. Higher the CPU chipset/type and Cache better the performance.

Graphics not likely to be factor unless you are drawing fancy fast charts.

There are solid-hard drives which can also improve performance if you are doing read/writes. In my time large DBs based on weather apps was Read or Write intensive could benefit from mirrored or striped RAID configuration but I don't remember now. I think striping was better fro disk writes. However, that's more server side and I suspect with solid drives may not be better performance. I'm going to assume you simply have a standalone system wiht no input output dependencies, otherwise bandwidth and network speeds will become a factor too.

You would need to monitor your PC from opening file, running your XLS to closing it to read counters to tell you what's happening under the bonnet so to speak. You say you have lots of RAM but any dirty pages may mean you need more ram or faster disk.

There is the obvious things to check too like make sure you are using a 64 bit HW with a 64-bit OS and 64-bit Excel. If any of those are running 32 bit then obviously upgrade wd help also.

With CPU's interrupt requests are always heavy. If you have lots of stuff connected to your PC like fancy mice and keyboards and other stuff, they can slow CPU.

Quality of motherboard is also likely to be a factor but haven't looked at them for a while now.

I also agree with Malaguti but I'm not so much as a developer. Peeps writing VB scripts or macros can help with that. I don't understand why you need 23000 lines for two variables to be calculated.

I'm going to assume you have deep pockets and money is no object. Good luck.


Going off track but I hope you realise TA doesn't work. History pretty irrelevant on future price imo. Either way wishing you all the best and good luck. 👍

 

[No Quarter]

Thanks for input.

First, I believe the sheet itself is optimized, and I run it on a PC that does nothing else. And I'm not looking for saving maybe 10% or 20% waiting time, I need a huge increase in speed to invest in it. "money is no object"? Not exactly true, but this is how I make my living, so I would be willing to invest.

"TA doesn't work", well some people do live off it. I get better every week, but could save time and try more stuff if I didn't have to wait a couple of minutes per idea and when testing 2-way tables on the 9 variables I analyse on.

" I don't understand why you need 23000 lines for two variables to be calculated. ", the 23000 lines are 8 mth of data with 134 data points per day (10 minute)

So, back to my question, those that remember when PCs entered the working place, if you had an Excel sheet that took a minute to calculate, and you then got a new PC at work, the 1 minute was reduced to 1-2 seconds. That's the kind of improvement I look for, but it seems we're at diminising returns now on PC speed? Should I get a workstation? A server?

 

[malaguti]

I would argue, do you really need a spreadsheet at all as 23000 lines is absolutely nothing
you are looking at historic stock prices..ok any software product can do that, back test periods..ok got that, and trailing stop loss.
nothing that any standard TA software couldn't do for you and yet you are using excel?
why, what is it about your spreadsheet that somebody couldnt do in amibroker?
and TA does work..just putting it out there
amibroker is just an example by the way, but it has one of the fastest calculation engines

 

[No Quarter]

With all due respect, I don't need advice on how to trade or what SW to use. I need answers on how to if possible to speed up my Excel. Thanks

 

[malaguti]

im not advising you, im just trying to better understand your issue as it was help i thought you wanted
help me to help you as it were..but no worries hope you find the answer you're looking for

 

[Atilla]

You haven't said anything about your PC, OS or software speck.

You haven't said what your network connection or dependencies are. If you are pulling data from another source into Excel have you considered latency, bandwidth?

Also, your 8 month of data... is that in one file or spread across multiple small files. Having 10,000 files with 1K of data will take considerably more time to read and process than one file wiht 10K of data. Opening and closing files is not desirable if it can be avoided.

I agree with Malaguti again. Need quality input in to get quality out 🙂



All the best, 👍

 

[No Quarter]

All data is in one file on a stand alone PC tht runs nothing else. Dell Optiplex 3.9 GHz, Windows 7

 

[Atilla]

All data is in one file on a stand alone PC tht runs nothing else. Dell Optiplex 3.9 GHz, Windows 7

From command bar line can you type SYSINFO and see what comes back please?

Windows 7 is end of life and is on extended support. After next year it will no longer be supported.

I suspect you maybe running Office 2010 and possibly 32-bit Excel.

It's using i3-7100 chipset which is also not ideal or fast for heavy number crunching. https://www.amazon.co.uk/Dell-OptiPlex-3050-Tower-Desktop/dp/B06W9HTGM2


You should be looking at i7 with 16Gb RAM running W10 OS and Office 16 or Excel 16 ensuring you are running 64-bit versions of software. As it stands that's hardly a high performance PC speck.

If you need CPU performance do not underestimate the value of CPU cache. Higher the better. Does take price up but performance comes at a cost.

 

[No Quarter]

Atilla, here's som of what I could find, hope that makes sense 🙂

Dell Desktop-HDS66B2
Intel Pentium [email protected] 3.50GHz (stated twice?)
RAM 20GB (19.9GB usable)
64-BIT operating system, x64 processor
Windows 10 Pro, version 1803, OS build 17134.472 (I said W7, not correct)
BIOS mode UEFI

--
Excel 2010 Home and Student, 14.0.4760.1000. (32-bit)

 

[Atilla]

Atilla, here's som of what I could find, hope that makes sense 🙂

Dell Desktop-HDS66B2
Intel Pentium [email protected] 3.50GHz (stated twice?)
RAM 20GB (19.9GB usable)
64-BIT operating system, x64 processor
Windows 10 Pro, version 1803, OS build 17134.472 (I said W7, not correct)
BIOS mode UEFI

--
Excel 2010 Home and Student, 14.0.4760.1000. (32-bit)

I think there lies your problem NQ.

32-bit has limited RAM access, google says 4Gb. Your 16Gb of RAM can't be used by 32-bit Excel.

32-bit Office is recommended but if you are after performance and just running dedicated Excel then you should definitely upgrade to 64-bit excel and should see performance improvement. You may wish to test this on a friend or colleagues excel before proceeding.

https://ark.intel.com/products/97143/Intel-Pentium-Processor-G4560-3M-Cache-3-50-GHz- That chips set is rather old.

https://ark.intel.com/products/series/186673/9th-Generation-Intel-Core-i9-Processors Here is an i9 chipset. Didn't know these were out. Notice as well as being a better CPU it has 16Mb Cache compared to 3Mb on yours.

You can read about CPU's here https://ark.intel.com/#@PanelLabel122139.

Personally, I'd spend < £1K and no more to buy a new laptop with an i7 CPU and 16Gb RAM.

You can add extra monitors. Ensure it also has a dedicated graphics card.

I used to standardise on Dell's but have now moved on to Lenovo's which are the Old IBM's but bought out by the Chinese. Don't buy HP as I did and it broke under a year. Good value and performance imo. i9s are over 2K. Not sure if it'll be worth it.


In summary, first try your file on an excel 64-bit as with 20Gb RAM you should definitely see performance improvement.

Secondly, upgrade PC/Laptop to a more recent chipset i7 or above based on your budget ensuring 64-bit across OS and SW.



Best of luck in your endeavours and jolly xmas 🙂

 

[No Quarter]

Thanks Atilla, will test when xmas things are over...

 

[No Quarter]

Strange. Exchanged my Office 2010 32 bit for the 2010 64 bit, because I had the disc on the shelf. Didn't speed up at all...
While calculating it showed:
CPU around 26%, stable
Ram 2.3-2.5 GB of the 20 available (?), dead stable
Committed memroy 3.1/22.9 GB (?)
Disc C: mostly around 30%, but also 97% some times
Cached read 1-2 GB
Seems it does not take advantage of 64 bit?
--
Googled the issue a bit, could be problem with early BIOS so updated from 1.7.9 to 1.11, no visible change

 

[Atilla]

Strange. Exchanged my Office 2010 32 bit for the 2010 64 bit, because I had the disc on the shelf. Didn't speed up at all...
While calculating it showed:
CPU around 26%, stable
Ram 2.3-2.5 GB of the 20 available (?), dead stable
Committed memroy 3.1/22.9 GB (?)
Disc C: mostly around 30%, but also 97% some times
Cached read 1-2 GB
Seems it does not take advantage of 64 bit?
--
Googled the issue a bit, could be problem with early BIOS so updated from 1.7.9 to 1.11, no visible change

Does it say what kind of motherboard you have and whether it supports 64-bit?

Resource meter is good to run to see which apps using up resources. Gives more detailed information. If you click on header bars when running it'll sort in terms of top resource users.

Either your sheet doesn't use much above 2Gb or some element of your setup is limiting RAM utilisation above 4Gb which points to a 32-bit issue.

Found this on google. Maybe a better place to ask your question as more techies there.
https://www.bleepingcomputer.com/forums/t/598578/advice-on-32-or-64-bit-on-old-computer/

GameGuru makes the following point.
Posted 06 December 2015 - 09:45 PM

Honestly you won't get much benefit (from my experience) going from 32-bit to 64-bit on this old of hardware. I actually found on older hardware the 32-bit ran better (especially with Ubuntu). 64-bit does give you access to more RAM (if your motherboard allows it) but on a single core old Pentium 4 you won't be doing much other than Word or browsing the internet. I say just leave it as is and enjoy it.



You mentioned W7 and then discovered you are running W10. I always prefer a clean install to an upgrade. Often once you start upgrading then a whole bunch of other issues rears their head especially if the PC is old. Pentium 4 chip set is quite old now. Apparently it was released in 2006. As you rightly point out, one then ends up upgrading drivers, bios and firmware etc., which in itself can be risky beware. Make sure you have good backups.

When running excel if you click on File/Help/About it should tell you version and what type you are running? Worth double checking. Did you re-install whole of MS Office or just Excel. For compatibility reasons they suggest 32-bit so maybe using shared DLL's and something just not right.

Keep digging. Performance trouble shooting never easy but can be rewarding and will help with future purchase - hopefully 🙂

 

[No Quarter]

thanks Atilla, will look into it more. It should be said though, that I bought this Optiplex and a 4k screen for this purpose in spring, and since it had on it the 64 bit W10, I assume the motherboard can handle 64. I've only added the Office 2010, first in 32 bit version not knowing any better, but now in 64 bit. So this should be a 64 bit machine, hw, windows and Excel.
Could it be the graphics card, I have the 4k 43" screen that's HD quality, might require some RAM? Only guessing.

Motherboard, called baseboard:
Manufacturer: Dell
Model: Not Available (!)
Name: Baseboard
Useless!

 

[Atilla]

thanks Atilla, will look into it more. It should be said though, that I bought this Optiplex and a 4k screen for this purpose in spring, and since it had on it the 64 bit W10, I assume the motherboard can handle 64. I've only added the Office 2010, first in 32 bit version not knowing any better, but now in 64 bit. So this should be a 64 bit machine, hw, windows and Excel.
Could it be the graphics card, I have the 4k 43" screen that's HD quality, might require some RAM? Only guessing.

Motherboard, called baseboard:
Manufacturer: Dell
Model: Not Available (!)
Name: Baseboard
Useless!

Dell Desktop-HDS66B2 ? Nothing comes up on google search for this model. I've checked dell.co.uk as well.

Re: CPU - Intel Pentium [email protected] 3.50GHz - I've checked again and it may be this CPU https://www.intel.com/content/www/us/en/products/processors/pentium/g4560.html
Looking at the specification it's not great and quite a few reviews worth reading in that link. Says it struggles under heavy loads. It only has 3mb cache. Not for heavy computing. Some say it's equivalent to i3 chipsets. https://www.hardwaresecrets.com/pentium-g4560-cpu-by-intel-review/6/

If it is then my previous comment about it being old and released in 2006 may have been wrong as it says release date was Q1 17. Although the original Pentium 4 was released in 2006 seems Intel may have just released an old chipset for the low end of the market. That list for the new chipsets is what I would go by. The price of G4560 starting at $60 doesn't sound very brand spanking new? The new i9's are priced at $500+


In general if you are looking for performance, the HW, OS and SW needs to be looked at in detail and obviously, all configured for 64-bit. Is Excel / Office, definitely verified as running 64-bit. Worth checking.

Bit like when buying diamonds, so many facets and characteristics it's difficult to know what one is looking at.


You said on earlier post CPU is at 23% most of the time. Is that the case when your XLS is computing when waiting 2-4mins?

Go to TASK MANAGER/PERFORMANCE/RESOURCE METER and then whilst watching it run through your excel sheet. Hopefully you may be able to identify where the delay or heavy utilisation of resources are. Pretty intuitive I'd say.

 

[No Quarter]

I experimented with using the cloud. Had to find a smaller file (<5MB) to do for free, it took 45 seconds on my PC. Uploaded to Excel Online onedrive.live.com it calculated consistently in about 35 seconds. So not a major difference worth going to the cloud for. Now, I assume (?) that in the cloud you have all the power you can have, so does that mean that even if I spent a fortune on hw and sw, I would never get a 70-90% reduction in calculation time? Or can I not conclude like that?

Just for the Excel nerd to understand why it takes so long: calculating my sheet functions takes a split second. But I run 15x15 tables to constantly optimize, so I do in fact calculate the sheet 225 times in that 45 seconds. Some of my models have 4 tabels, equal to calculating the sheet 900 times. That's why I need power...

BTW, the programmers do have a sense of humour. First the screen say "Working on it...". Later it says "Still working on it..." 🙂

 

[Atilla]

I'm sure just as you have file size limit, when using the cloud there is likely to be throttling limits set by cloud operator on how much CPU and RAM power is allocated to any one user.

Why not stick your file on a USB key and take it to Curry's and ask if you can Try-Before-You-Buy with one of their assistants.

I'm sure they'll let you test out your one file on a laptop. They may not based on security concerns but worth a quick shot. They probably will not understand what your file does or how to use it either.

I would say if you get better performance in cloud, then your local setup is the issue. 🤔

 

[No Quarter]

Curry's? I'm in Denmark. I may find locally. And you're right, the file is so complex and not user friendly, nobody will benefit from it.

Just remembered I have a friend selling servers at Dell locally, I'll talk to him. Thanks