I am looking for a workaround to the problem of real-time Bloomberg data requests "taking over" 100% of my CPU usage when an Excel spreadsheet is set to automatically recalculate.We have an Excel spreadsheet using a Bloomberg class in VBA to manage real-time data requests via the Bloomberg API for securities prices, which feed into functions that calculate derivatives values, which are then returned to and displayed on a spreadsheet for the user.Here are the issues:- if calculation is set to "manual", there is no problem with CPU usage, but the user has to manually recalculate to display fresh prices- if we set calculation to "automatic", we immediately lose control of the applicaion with 100% CPU usage reported by Windows- if we recalculate specific ranges only, we also immediately lose control of the application with 100% CPU usage- our next step is to test whether we should a) set recalc to manual, and also b) use a timer to recalculate the spreadsheet every X second intervalHas anyone else addressed a similar problem and have some insight to share?

When do you want it to recalculate? Every N seconds? When a user changes a user-defined value?

QuoteOriginally posted by: ZmeiGorynychWhen do you want it to recalculate? Every N seconds? When a user changes a user-defined value?Ideally, it would calculate in real-time or as close to real-time as possible as it is used for trading desk decision-making support...but without the disadvantage of locking up Excel or the the CPU...

...also a user may change a user-defined calculation for which a recalc would be needed, but this is infrequent. The change in real-time securities prices is the primary issue...

QuoteOriginally posted by: dcQuoteOriginally posted by: ZmeiGorynychWhen do you want it to recalculate? Every N seconds? When a user changes a user-defined value?Ideally, it would calculate in real-time or as close to real-time as possible as it is used for trading desk decision-making support...but without the disadvantage of locking up Excel or the the CPU...But "trading desk decision-making support" means "human real-time" not "computer real-time". Is it not enough to have the spreadsheet recalculate itself every half a second, as well as on any cell change (both should be straightforard in VBA, no?)?

QuoteIs it not enough to have the spreadsheet recalculate itself every half a second, as well as on any cell change (both should be straightforard in VBA, no?)? Hi ZmeiGorynych, yes, it is human real-time (i.e. some small delay is tolerable). I should not that in my original post I suggested that coding a timer to recalculate the spreadsheet every X second interval was a possible solution...I was looking to see if others agreed or suggested some better alternative...

...re: "on any cell change"...this locks it up in the same way as auto recalc of sheet or auto recalc of some (sub-) range...

One way to do this is to move some of the calculation out of Excel. We would take the Bloomberg data into a separate application (in our case Cascade DataHub), perform complex calculation with in-line scripting or through a custom app written in C++ or C#, feed the results back to the DataHub, and feed Excel from there. That way we still use Excel for presentation and any user-specific calculations, but use a more efficient engine for the CPU-intensive operations. As a nice bonus, we can put the calculations on a dedicated monster machine and send the results to every Excel on the network, all in real-time.Andrew

Hi Andrew, Thank you for the thoughtful response. The challenge at hand is a real time application to support a 3 person team of traders/portfolio managers. Despite the merits of using a data hub, I am not yet ready to abandon the simple implimentation of a single Excel file running on the trader's workstation...at least until I have exhausted all possibilities...which is a point I may arrive at soon if my latest attempt fails......after much back and forth with Bloomberg, their conclusion is that Automation is causing the bottleneck..."Automation is being used to not only place the data entering the Data event handler into the cells of the worksheet, but also by the Data event handler itself...and that therefore, whether I use ActiveX, or .NET or the C API to send data to Excel worksheets, I will still encounter the same bottleneck...being Automation". Instead, they recommended RTD, which we are currently testing...

You can set Bloomberg to not be live, and then refresh the data only when you want it.

Hi gjlipman, We tried using an on/off switch for the asynchronous real-time request via the ActiveX data control in VBA. We liked this because structure because we could reuse all the code we were using for historical requests of the same. It worked fine when it was OFF - naturally. But, would lock up the CPU at 100% when ON. We also tried a synchronous request on a timer. In this case, we requested the data, received it, waited, than did the same again. It took 40 seconds to receive the data, so then we waited 2 minutes on the timer, requested again, received in 30 seconds, waited 2 mins, requested again, then crashed...that's when we nixed both the asynchronous and synchronous requests using ActiveX...we sent the samples to Bloomberg, and afetr a bit of back and forth with their programming team, they responded by recommending RTD for real-time...which we are now testing...

QuoteOriginally posted by: dcHi Andrew, Thank you for the thoughtful response. The challenge at hand is a real time application to support a 3 person team of traders/portfolio managers. Despite the merits of using a data hub, I am not yet ready to abandon the simple implimentation of a single Excel file running on the trader's workstation...at least until I have exhausted all possibilities...which is a point I may arrive at soon if my latest attempt fails...A datahub would do that for you. The scenario is trivial to set up:Bloomberg (machine 1) --> datahub (machine 1) --- network --> datahub (machines 2 through N) --> Excel Every trader has his own individual, customizable, Excel spreadsheet but all traders can see the same data. If you have a common calculation that you all need, then it can be done on machine 1, and the individual traders do not have to recompute it themselves. This is a massive CPU savings. I have seen people passing around spreadsheets with 4000 calculated values (currency trading) to 5 workstations on a network, 5 times per second. The network bandwidth requirement is trivial. The bottleneck is in Excel's ability to take data at that rate, but it is mitigated by sending data as Excel ranges, not as individual values. They were not exhausting their CPU in that scenario.Your problem is easily solved, without a single line of programming.Andrew

There is a way to do this using ActiveX scripting. I remember having this problem several months ago and did a little digging to find out the work around. Bloomberg offers free training on how to write programs with ActiveX to download realtime data and perform calculations on it. If you're in NYC, I believe there is one in mid-october