Hello!
First, please excuse the thread length as I tried to be as concise as possible.
Second, please assume I've optimized the "financial model workbook" and the financial model (FM) macro (not saying that more couldn't be done but that is not the issue here).
I have a financial model workbook (FM wb) that runs 160,000 simulations. For the desktop it was written for and with Excel 2010 and 4 cores, it takes about 4 hours. The FM macro in that FM wb opens a text file with the simulation information (160,000 lines in the text file), creates an output text file, then cycles through the simulations (load a given simulation information, recalc the wb, then write simulation output). The text files are then closed as well as the financial model workbook.
FWIW, threading is on for the financial model workbook.
I now have a much better desktop available with 20 cores (and apparently 40 threads) available. Some extensive testing suggests that using 16 excel instances with 16 copies of the FM wb, each FM wb cycling through 10,000 simulations, can get the work done in about 40 minutes.
So, in a "master workbook" I wrote a macro that opens the input text file, splits it into 16 parts (with similar names; e.g., input1.txt, input2.txt, etc.). Then by looping 16 times, 16 instances of excel are opened, and the original FM wb is opened and saved 16 times with similar names (e.g., FMwb1.xlsb, FMwb2.xlsb, etc.) and each copy has a cell that gets modified to create the appropriate input file (and so 16 output files similarly named will be created).
At this point I have 17 instances of excel with 17 open workbooks -- the master wb with the master macro, and 16 other similar wbs, each one with a copy of the FM macro, and the control is in the master macro.
I can readily set up a loop in the master macro to launch the macro in each of the 16 copies, but only one FM macro can be executed as control doesn't return to the master macro until the selected FM wb and its macro are done. What I've been doing until now (and how I now 40 minutes is about optimal) is that I toggle manually through the FM wbs and hit the macro button that's in each. So now they are running simulaneously but with manual intervention.
So is there a way to have the master macro launch all of the 16 FM macros so they all run simultaneously? If not in an excel module, how about using a C+ shell or some other shell? My preference would be to stay in excel VBA, but I'm open to any ideas.
(And, last, originally the single wb on the 4 core desktop took about 16 hours, so I've spent some effort on the wb optimization as it's now down to 4 hours. This includes running local to a desktop and staying off the LAN, screen updating off, manual recalc, all wb formatting removed, only using volatile functions where absolutely necessary, choosing INDEX or LOOKUP or whatever prudently, calculation dependencies are left to right, top to bottom, and everything's been valued out that can be. Hey, if you have other optimizing ideas, I'll use them but I'm very skeptical that you can get me from 4 hours to 40 minutes with those ideas. Essentially for a given FM wb I need to set up a simulation -- so read a record, recalc the wb which will thread during the recalc, then write an output record -- there's an unavoidable bottleneck.)
First, please excuse the thread length as I tried to be as concise as possible.
Second, please assume I've optimized the "financial model workbook" and the financial model (FM) macro (not saying that more couldn't be done but that is not the issue here).
I have a financial model workbook (FM wb) that runs 160,000 simulations. For the desktop it was written for and with Excel 2010 and 4 cores, it takes about 4 hours. The FM macro in that FM wb opens a text file with the simulation information (160,000 lines in the text file), creates an output text file, then cycles through the simulations (load a given simulation information, recalc the wb, then write simulation output). The text files are then closed as well as the financial model workbook.
FWIW, threading is on for the financial model workbook.
I now have a much better desktop available with 20 cores (and apparently 40 threads) available. Some extensive testing suggests that using 16 excel instances with 16 copies of the FM wb, each FM wb cycling through 10,000 simulations, can get the work done in about 40 minutes.
So, in a "master workbook" I wrote a macro that opens the input text file, splits it into 16 parts (with similar names; e.g., input1.txt, input2.txt, etc.). Then by looping 16 times, 16 instances of excel are opened, and the original FM wb is opened and saved 16 times with similar names (e.g., FMwb1.xlsb, FMwb2.xlsb, etc.) and each copy has a cell that gets modified to create the appropriate input file (and so 16 output files similarly named will be created).
At this point I have 17 instances of excel with 17 open workbooks -- the master wb with the master macro, and 16 other similar wbs, each one with a copy of the FM macro, and the control is in the master macro.
I can readily set up a loop in the master macro to launch the macro in each of the 16 copies, but only one FM macro can be executed as control doesn't return to the master macro until the selected FM wb and its macro are done. What I've been doing until now (and how I now 40 minutes is about optimal) is that I toggle manually through the FM wbs and hit the macro button that's in each. So now they are running simulaneously but with manual intervention.
So is there a way to have the master macro launch all of the 16 FM macros so they all run simultaneously? If not in an excel module, how about using a C+ shell or some other shell? My preference would be to stay in excel VBA, but I'm open to any ideas.
(And, last, originally the single wb on the 4 core desktop took about 16 hours, so I've spent some effort on the wb optimization as it's now down to 4 hours. This includes running local to a desktop and staying off the LAN, screen updating off, manual recalc, all wb formatting removed, only using volatile functions where absolutely necessary, choosing INDEX or LOOKUP or whatever prudently, calculation dependencies are left to right, top to bottom, and everything's been valued out that can be. Hey, if you have other optimizing ideas, I'll use them but I'm very skeptical that you can get me from 4 hours to 40 minutes with those ideas. Essentially for a given FM wb I need to set up a simulation -- so read a record, recalc the wb which will thread during the recalc, then write an output record -- there's an unavoidable bottleneck.)
