PC upgrade advice

grantx

Senior member
2,331 223
Excel performance is really sluggish on some sheets due, presuambly, to size and number of calculations. To improve this I could split the sheets but this is not ideal.
Please refer to the specs of my pc below, and suggest compatible options for upgrade(s) which would give a significant boost.

I suspect there are tweaking/overclocking possibilities but I'm wary (don't know what I'm doing).

Whether relevant, I've also attached a shot of Task Manager showing the number of processes running. Note that Ace-WINscreen.exe is not usually running – it was used to capture the image.

Thank you.

Grant.

AMD Athlon 64 X2 Dual Core 4800 processor, 2.41 Ghz
Asus A8N-SLI Deluxe motherboard
Sapphire Radeon X800 GTO 512 MB DDR2 graphics card
2 x 1GB Kingston KVR400X64 DDR RAM
Enermax Liberty 500wv2.2 psu
Value Antec P180 Super Midi case
Samsung SP2504C hard drive
Running on Windows XP Pro x64, version3, SP 1

I also have a spare (new) Western Digital Caviar SE16 SATA 2 which may be used to form an array.
 

Attachments

  • y.doc
    40 KB · Views: 352

A Dashing Blade

Experienced member
1,373 170
Excel performance is really sluggish on some sheets due, presuambly, to size and number of calculations. To improve this I could split the sheets but this is not ideal.
Please refer to the specs of my pc below, and suggest compatible options for upgrade(s) which would give a significant boost.

Can't see you should have any probs with a dual core 4800 (I use one as the back-bone of a hard-core gaming rig) + you're running 32bit excel which, iirc is multi-threaded + it's only using 42,072k of mem (heavy, but certainly NOT excessive, i got one using 126k as we speak)

Strongly recommends you recheck the spreaddy particuarly wrt the use of volatile functions
 

rossored

Senior member
2,103 56
agreed, that shouldn't have any problems with the CPU and the RAM you've got plumbed in there, so I'd look to Excel as the culprit. Can you run the sheet on another machine and see if it gives similar crappy performance?
 

Hoggums

Senior member
2,176 878
Grant

Any iteration used in your sheets ?

Good advice so far - they only thing I can add is that if you do have a lot of data in your spreadsheets then perhaps adding more memory would speed things up a little. But I would look at the source of the problem before spending money as I'm not sure how excel works. I believe XP places limits on the amount of memory a program can allocate so adding more memory might not help at all....
 

grantx

Senior member
2,331 223
Gentlemen,

Performance is probably worse on Excel 07 compared to Excel 02.

A sheet is 8,135k, consisting of 13,500 rows x 40 columns containing tick data of DOM to 10 levels on bid/ask – bid and ask price, bid and ask size.

From this I construct 1-minute data of all the fields producing 845 rows. From the 1-minute data I use basic calcs - change from previous, total bid/ask size, etc. I’ll also construct various frequency/distribution tables consisting of array formulas, containing around 8 columns.

Functions used are =COUNTIF and =SUMIF.

I’ve actually separated the 1-minute into an separate file out of necessity rather than choice and will use this for initial analysis.

I know there are various freeware prog’s that can examine various aspects of a pc. But is there any that can identify specific areas of weakness.

I don’t know if this relevant but from System Properties, Advanced, Performance, Settings, Advanced, Virtual Memory, Memory usage (best performance) is Program (as opposed to System cache); Total paging file equals 4093 mb (Visual Effects is selected for Adjust for best performance).

I’ve also attached a shot of Performance from Task Manager.

PT,

Could you be more specific re iteration?

Grant.
 

Profitaker

Established member
773 70
Grant

Any function that is using For / Next / loop as part of it's code, in other words any continuous counting process. I have a DDE Option price feed into Excel and use an iteration process to solve for implied Vol. It uses an awful lot of CPU, such that I have to severely limit the number of strikes I can evaluate.

In Excel go to > Tools > Options > Calculation > and uncheck the Iteration box. See if that makes any difference to your speed ? If it does then try reducing the Iteration maxima.
 

blackcab

Established member
523 51
Have you got any RAND() functions in the spreadsheet?

I run a 25MB, 30,000-row model on a PC far, far lower spec than that and it's fine. I've noticed though that large numbers of RAND()s causes Excel to slow right down beyond a certain point.
 

grantx

Senior member
2,331 223
Black,

There’s nothing like rand(). The calcs are basic maths (addition, subtraction division) plus =COUNTIF and =SUMIF, some contained within arrays.

This is an example of a cell:

=IF($AZ6<MIN($B$4:$B$20001),"",VLOOKUP($AZ6,$B$4:$AP$20000,22))

In this particular case this is from a table 800 rows x 40 columns (32,000 cells).

The following are example cells from a Price, volume distribution table (1000 rows):

number of trades {=FREQUENCY(AQ4:AQ20000,BO5:BO10000} (this is part of an array)

total volume =SUMIF(AQ4:AQ20000,BO5, AR4:AR20000

buys =IF(BT5=0,0,IF(BT5<0,ABS(BT5),IF(BT5>0,BQ5+BS5)))

sales =IF(BT5=0,0,IF(BT5<0,BR5-BQ5,IF(BT5>0,-((BQ5/2)-BT5))))

net =SUMIF(AQ4:AQ20000,BO5,AT4:AT20000)

buy/sell ratio =IF(BT5=0,0,IF(BS5=0,0,BR5/ABS(BS5)))

buys % =BR5/BO$3

sales % =BS5/BO$3.

If it is the case the sheet is over-burdened, I suppose we have three options: translate all formulas into a vba module; upgrade; do both.

Grant.
 

Atilla

Legendary member
19,909 3,160
Black,

There’s nothing like rand(). The calcs are basic maths (addition, subtraction division) plus =COUNTIF and =SUMIF, some contained within arrays.

This is an example of a cell:

=IF($AZ6<MIN($B$4:$B$20001),"",VLOOKUP($AZ6,$B$4:$AP$20000,22))

In this particular case this is from a table 800 rows x 40 columns (32,000 cells).

The following are example cells from a Price, volume distribution table (1000 rows):

number of trades {=FREQUENCY(AQ4:AQ20000,BO5:BO10000} (this is part of an array)

total volume =SUMIF(AQ4:AQ20000,BO5, AR4:AR20000

buys =IF(BT5=0,0,IF(BT5<0,ABS(BT5),IF(BT5>0,BQ5+BS5)))

sales =IF(BT5=0,0,IF(BT5<0,BR5-BQ5,IF(BT5>0,-((BQ5/2)-BT5))))

net =SUMIF(AQ4:AQ20000,BO5,AT4:AT20000)

buy/sell ratio =IF(BT5=0,0,IF(BS5=0,0,BR5/ABS(BS5)))

buys % =BR5/BO$3

sales % =BS5/BO$3.

If it is the case the sheet is over-burdened, I suppose we have three options: translate all formulas into a vba module; upgrade; do both.

Grant.

Hi Grantx,

Go to TOOLS / OPTIONS / CALCULATIONS tab and disable auto calculations.

You can press F9 to carry out manual calculations.

See if your CPU and ram usage goes up then. Should do.

Then you'll know if it's your formulas that's the prob.:idea:
 

Crap Buddist

Senior member
2,458 289
Actually, maybe ,as it happens.....

Start -run -type in msconfig select start up, click disable all.

reboot. manually open programs you want running... and lets face it, stay off them porn sites...... :LOL:





note, check it out, ive no idea whats required to be enabled but ive clicked mine off and its ok, and you can always click enable all......... if you dont like it...... :)

ohh, ohh look at me daddy I'm an I.T. engineer, yeeee haaaaa....
 
Last edited:

grantx

Senior member
2,331 223
Hello Atilla,

I’ve already disabled the auto calc.

Did as you suggested and the CPU indicator on Task Manager went to around 50-52%. So why doesn’t it use more of the resources? CPU indicator returns to 0% so I presume the draw from other processes is negligible.

Grant.
 

Atilla

Legendary member
19,909 3,160
Hello Atilla,

I’ve already disabled the auto calc.

Did as you suggested and the CPU indicator on Task Manager went to around 50-52%. So why doesn’t it use more of the resources? CPU indicator returns to 0% so I presume the draw from other processes is negligible.

Grant.

Could be an interrupt request. Even clicking your mouse repeatedly will slow CPU down.

However in Task Manager if you click on the CPU column header it will list your services in order of top users.

Do you have many devices connected to your PC like printer/scanner/fax cameras mp3 players etc etc?

It is normal for CPU to peak at 100% and maybe stay there for a few seconds.

The problem is if it stays there constantly.

Try disabling Automatic Updates.

For some reason on my machine I realise it really puts an over head and slows internet and market maker down. Without it I can run mulitple tasks and listen to mp3 or watch movies all at the same time.

Having said that once a month I enable it get the updates whilst doing nothing else and then disable it again.
 
 
AdBlock Detected

We get it, advertisements are annoying!

But it's thanks to our sponsors that access to Trade2Win remains free for all. By viewing our ads you help us pay our bills, so please support the site and disable your AdBlocker.

I've Disabled AdBlock