PDA

View Full Version : Running macro periodically



mark.miller9
05-30-2008, 01:40 AM
I have a pice of macro that opens a file and runs a few models on the data. The problem is that the data is received via an RTD, and while the macro is running, the RTD will not update. I can load the data and then run the macro, but as I have about 40 of these to do and the models take a good 5 minutes to run. It would be a lot easier and a less time consuming if I could find a way to call these macros one after the other without the need for manual intervention. Any ideas would be great, thanks in advance.

Bob Phillips
05-30-2008, 01:50 AM
Just create a macro that does nested calls



Call Macro1

Call Macro2

Call Macro3

'etc.

mark.miller9
05-30-2008, 01:59 AM
For this, will a macro not be running in the background? If so, the RTD will not update and the data needed will not be available for the models to run off the back of.

Bob Phillips
05-30-2008, 02:06 AM
Sorry, are you asking whether the RTD might fail because all of these macros are running. If so, whilst I have no idea what RTD is or does, potentially yes. But you need to find a window to run these macros, and if RTD could fire in at any time, you are goiung to struggle as VBA is single-threaded.

Bob Phillips
05-30-2008, 02:07 AM
You could splatter DoEvents around in the code to give other apps a processor share, but I don't know how RTD works as I said.

mark.miller9
05-30-2008, 03:49 AM
RTD is just a real-time-data link that is bringing in data from BB in this case, but the problem with the above is that (like the sleep and application.wait), there is still a macro running in the background so will not load data. I guess what I am trying to find is a way to put the entire macro to sleep for like a couple of minutes (as though it was closed and then refired).

Bob Phillips
05-30-2008, 03:58 AM
But how will you know when to put the macro on sleep?

Try adding DoEvents in the macro as I suggested and see if that helps.

gwkenny
05-30-2008, 06:29 AM
Does BB stand for bloomberg? If so, you should have their add-in API loaded and you can make calls and get data directly from VB.

I don't have current access to bloomberg so I can not give you more help :(

I do have another thought though. Sounds like you are making a data request from your real time feed, then running your model on the resulting data. Are you requesting something like historic prices for 40 different securities? If so, instead of making 40 requests to your real time data feed, you should make one request asking for the 40 companies at once. Dump this data into an intermediary spreadsheet, then run your models from the intermediary sheet.

That's just faster from an execution standpoint.

You say your model takes a good 5-minutes to run. I don't know what your model is supposed to do, but I believe you need to look at optimizing the speed of your model. 5-minutes is an incredible amount of computing time. The only thing I wrote that took a significant amount of time was an asset backed refinancing model that did an exhaustive search of all the structuring permutations (a couple of million permutations). Other than that, most everything calculates with tenths of a second if less and the odd workbook at 2 or 3 seconds, and these are workbooks in the 10 to 60 mb range.

Last thought. You could use application.ontime to delay your events. I believe, if you give it enough time, that the real time data feed will update while things are in the queue with ontime events.

Good luck to you!

mark.miller9
05-30-2008, 07:01 AM
I do mean Bloomberg when I say BB and the file is about 12MB, but runs through 9 yrs of available data for five separate volalitlity models for 4 lots of day separations on sectors consisting of 30-300 stocks. I am sure it is not 100% efficient, however is a "work in progres" at the moment and unfortunately not an expert in the field of programming. Once I have completed them once, will just be updating on a daily basis which takes a few seconds instead of minutes.

I initially do make the call for all the stocks to be brought in at once, but this is where my problem is at the moment. I have a global spreadsheet that I wish to run overnight to run through all of the indicies I am looking at without the need for me to be here to make the call for each index, wait for the historic data to load, then run the macro. Have tried the DoEvents and sleep functions, but sleep just seems to be equivilent to the Application.Wait in that the macro still runs in the background and DoEvents is just skipped over as though there are no processes waiting to be run.

mark.miller9
05-30-2008, 07:06 AM
Also the data takes about 1-2 mins to load for the largets of indicies, so that could also be quite significant when looking as the DoEvents

mark.miller9
05-30-2008, 07:36 AM
qwkeeny - Thanks very much, tried the ontime event in a different sheet and for a longer period of time and it seems that that will work 100% for me, thanks very much

xld - Thanks so much for your help on this one ...

Have a great weekend guys

gwkenny
05-30-2008, 08:29 AM
Mark:

Volatility models? Reminds me of option pricing and swap models which brings emotions of DANGER DANGER and the instinct to RUN RUN, RUN AWAY QUICKLY!!!!

lol

Mark, I'm glad you got your answer.

If you are going to do a significant amount of data mining (with other models etc...) from bloomberg, it is really worth your time learning the API model because you can do queries direct to bloomberg in code that get's dumped into an array which you can manipulate and then write to a sheet.

I also would like to bring to your attention the board http://www.wilmott.com/index.cfm?NoCookies=Yes&forumid=1

Wilmott forums is a board specifically for the Financial Quant industry and it is pretty technical (though most of those guys program in C++ or in .net) and the readership is literate with both programming and seriously advanced applied quants. Just don't give away your ideas, heh.

(hope no one takes offense me citing another board here, but it's content is really different and it's not even close to being a competitor for this boards target audience)