Best solution VBA/C++

[Charles]

Hello

I am seeking some advice on the best solution for a small program I
have to build. I need to design a program which can be launched in
Excel, would acquire some data from Excel, then run some cash flow
calculations (so mostly working on multiple small arrays with a lot of
conditions and simple calculations) and would run some monte carlo
simulations.

So basically I need a program which would be fast enough so that I can
run my calculations 1,000s of times in a reasonable time, that would
have access to a proper random number generator (I guess I have to go
for a commercial product for that, any suggestion?), and which could
easily communicate with Excel.

For the moment I think I can run that in VBA. C++ is quite unflexible
and would probably require a little more time to code my stuff. In top
I would have to manage the interface between VBA and C++. On the other
side, I don't know how much faster the program would run under C++
compared to VBA. Can C++ really save some time on functions like if,
loops, while, and simple array calculations? (there will be no access
to excel between the begining and the end of the program).

If anyone has an opinion or a suggestion, I am happy to take it!
Best regards
Charles

 

[Mike Middleton]

Charles -

If you can build the cash flow model in Excel, you could perform the Monte
Carlo simulation using industrial-strength software like Crystal Ball
(www.crystalball.com) or @RISK (www.palisade.com).

Or, you could use less expensive simulation software like my RiskSim
(www.treeplan.com) or Sam Savage's XLSIM package (www.analycorp.com).

Or, you can reinvent the wheel and write VBA or C++ code, where compiled C++
will undoubtedly be faster than interpreted VBA.

- Mike
www.mikemiddleton.com

 

[Charles]

Thanks for your answer

For the first solution, I think that Crystal Ball just uses the results
from the calculations of the spreadsheet? My problem is that we are
talking about a significantly complex cash flow model, which is to be
honest already a bit slow to process in Excel. (Not slow enough to be a
problem in Excel, but slow enough to be worried if it had to be ran
100,000 times).That's why I can hardly see a way to do it without VBA
and/or C++.

My question is rather: will I win a significant amount of runtime by
using C++ than by using properly (declaring all the variables, etc)
VBA?

You look like you know these software quite well. I think I would
mostly use their random number generation capabilities. Do you know how
they compare for that purpose?

Charles

 

[Mike Middleton]

Charles -

I do not have direct experience comparing VBA and C++. For some anecdotal
evidence regarding VBA vs. C++, see

http://groups.google.com/group/micr...9479f?lnk=st&q=&rnum=4&hl=en#9c62051cc639479f

or search Google Groups for "excel ian smith" (without the quotes) to find
the above message and other relevant information. Ian Smith can provide
numerous relevant VBA functions for Monte Carlo simulation, e.g.,

http://groups.google.com/group/micr...1b42f?lnk=st&q=&rnum=7&hl=en#3c4626d90261b42f

Another important resource regarding Excel calculation speed is Charles
Williams' web site

http://www.decisionmodels.com/

- Mike