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 G4560@3.50GHz 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 G4560@3.50GHz 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