Can file size alone (even when data is hardcoded) contribute to slow performance?

[d0rian]

I've spent a lot of time trying to optimize my workbook (which streams in a high-volume of real-time stock quotes) for speed, e.g.: (i) getting rid of resource-hogging dynamic functions, (ii) and particularly hardcoding values wherever possible (with macros that calculate formulas and then paste their values) so that I don't have unnecessary formulas constantly calculating in the background. Performance still not great, and wondering whether the mere fact that my file size is still large (7.5MB) can contribute to the lag. A few worksheets still have ~15,000 rows of data, and even though it's nearly entirely hardcoded data (and there aren't any active INDEX or LOOKUP formulas that are referencing those large data tables), can the mere fact that it's there and the file size is large contribute to slower performance?

 

[Kyle123]

If most things are hard coded, what specifically are you finding slow?

If it’s macros, absolutely the amount of data can make a difference, but this is typically most noticeable when the macros are poorly written. To give you some feedback we’d really need to see your code.

If it’s not the macros, you need to give us more to go on

 

[offthelip]

One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.

(with macros that calculate formulas and then paste their values)

So what i hope you are doing for maximum performance with these macros is reading all the values you need from your stock download into one single variant array, then defining a single array for the output, then doing all calculations in the VBA macros and then writing all the results out. i.e One single read and one single write.
If you are doing each calculation by accessing the cells individually and writing the result back individually it wil be slow.

 

[d0rian]

If most things are hard coded, what specifically are you finding slow?
If it’s macros, absolutely the amount of data can make a difference, but this is typically most noticeable when the macros are poorly written. To give you some feedback we’d really need to see your code.
If it’s not the macros, you need to give us more to go on

So what i hope you are doing for maximum performance with these macros is reading all the values you need from your stock download into one single variant array, then defining a single array for the output, then doing all calculations in the VBA macros and then writing all the results out. i.e One single read and one single write. If you are doing each calculation by accessing the cells individually and writing the result back individually it wil be slow.

It's not the VBA / macros that are slow (well, they aren't great, but I'm only running them from time to time when I want to update some hardcoded values). Rather, I'm using this workbook to stream in real-time tick-by-tick stock quotes using my brokerage's API (DDE), and then doing a bunch of calculations on the quotes (and using conditional formatting to indicate changes in stock prices.)

The problem is a lag of 5-10 seconds between when I see quotes change in the brokerage software and when they change in my workbook. If I open up a completely BLANK workbook and ONLY paste the API formulas to go get the streaming quotes (i.e. the 'cleanest' file possible), the lag is only 0.5 - 1.5 seconds. But in the workbook where I have all of my formulas and conditional formatting working away in real-time, the lag is 5-10s. So that's the performance that I'm trying to improve. The more I learn, the more I come to the conclusion that I'm simply asking Excel to do something, resource-wise, that it wasn't really designed to do (i.e. stream in several hundred quotes in real-time and do a bunch of calc's on them)...but that hasn't stopped me from trying to get the best performance possible. Hope that helps.

 

[offthelip]

Can I suggest tyrying it without the conditional formatting!!

 

[Anthony47]

We know nothing about your code, finding the bottleneck is job for a sorcerer …

Are you using SetLinkOnData to track each of the data changes; or a single OnTime procedure to monitor all of them; or what? How many DDE formulas do you have in your sheet? For how many different stock shares?
And, as already stated by other experts above, the way you access the cells content also make a difference, if we talk about thousands of cells.

Bye

 

[d0rian]

Can I suggest tyrying it without the conditional formatting!!

Yes, I did a deep dive into CF, and learned that Excel essentially treats CF rules like dynamic formulas (aka resource-intensive / performance drawback), so I set up a checkbox to toggle CF on/off in order to troubleshoot. I thought I'd notice a significant performance improvement, but it was negligible. (To achieve the 'toggle', I created an additional CF Rule and moved it to the top of the list of Rules and checked 'stop if true' so that none of the Rules below it will get applied when the checkbox is ticked.)

We know nothing about your code, finding the bottleneck is job for a sorcerer …

Are you using SetLinkOnData to track each of the data changes; or a single OnTime procedure to monitor all of them; or what? How many DDE formulas do you have in your sheet? For how many different stock shares?
And, as already stated by other experts above, the way you access the cells content also make a difference, if we talk about thousands of cells.

Yes, that's why I didn't post a one line "waaaaaaah, my code is slow HALP!!" OP. I asked a specific Q about whether there was any reason a lot of hardcoded data / large file size could in and of itself cause performance issues. I only provided the follow-up info because other users asked. I'm streaming in 500+ different symbols using the brokerage's DDE API (with ~12 variables for each ticker, i.e. bid, ask, size, volume, etc). So a lot of data and a lot of calculation. And I just have a lot of formulas running that manipulate those values being streamed in. So yes, as I wrote above, I may simply be asking Excel to do much heavier lifting than it's designed to do, but I still want to optimize to whatever extent I'm able. I have wondered, btw, whether maybe I could alleviate some of the resource crunch by setting Calc to manual and then running a macro to calculate everything every, say, 1 second...so basically 'faking' auto-calc that way. Not sure if that would use fewer resources compared to just streaming in the data and letting all of my formulas run in real-time.

 

[Anthony47]

7 MB of text do not require calculation and Cpu time; 7 MB of macros could run in a bliss; 100 DDE formulas could require seconds to be calculated; a single macro could take seconds to complete, if it involves handling external events (like dde links).
I understand you have 6000 DDE formulas (~500 * 12), that is a lot. If you used SetLinkOnData (do you use it?) to associate a macro to each DDE change you will have thousands of events, probably arriving in "waves" from the provider's server.
You mention that there are lot of formulas, in addition to the results of the macros: the way they refer to data impact their performace, especially if the are in array formulas or address long list of data; and hopefully you do not address entire columns of data.

My first test would be processing every X seconds the data you collect in realtime; that implies isolating the real time data from the formulas, and link the formulas to "a copy" of your data that you refresh in one block every X seconds.

But I think you need to benchmark each of the various operations that you workbook perform to identify which is /which are those that consume most of your cpu.

Bye

 

[Michael M]

It sounds much like your downloading is the issue.....Does the streaming rate vary much ??
Is there a lag in the stock quote supplier. With large amounts of streaming there can be a choke point, where data has to wait to be processed?
Also, depending on the calcs, excel may stop the stream until it has finished processing current data? could also be a lack of useable memory / storage !!

 

[offthelip]

es, I did a deep dive into CF, and learned that Excel essentially treats CF rules like dynamic formulas (aka resource-intensive / performance drawback), so I set up a checkbox to toggle CF on/off in order to troubleshoot. I thought I'd notice a significant performance improvement, but it was negligible. (To achieve the 'toggle', I created an additional CF Rule and moved it to the top of the list of Rules and checked 'stop if true' so that none of the Rules below it will get applied when the checkbox is ticked.)

Why don't really try it without the conditional formatting on a copy of the workbook, because it is so easy to delete it all and try it? How do you know that EXCEL isn't still parsing all that CF every recalc