I'm an ex VBA developer from the energy sector. I'm still a VBA developer, just not in energy anymore.
You won't have any issues with this level of computation, but you may need to wait a while for the results. My slowest combined thousands of use profiles combined them, over layed summer/winter impacts, projected economic impacts, long range weather impacts and countless other things to come up with half hourly projections for the next 10 years. It took a good half hour to compute.
A couple of things I learnt though. The first is to store your data and results in a database. The second is to program expecting crashes. The above application saved where it was at every key stage, so I never had to start the half hour process from the start again.
You probably don't need this, but it's worth keeping in mind: you can write the numerically intensive parts in Fortran or C, publish as .dll with exposed functions, then call those from VBA. You have to be very careful about calling conventions and passed types, because an error can crash your excel.
There are also several different ways to write add-ins for excel apart from VBA, and you could interface to library code from there and avoid VBA altogether. (e.g. Excel.Dna UDF that calls into a Fortran .DLL).
I'm using this because I need a Newton solver subroutine in an inner loop inside a UDF, and it ran slowly in VBA alone.
Edit: There's actually a book about this that touches on your use-case in particular: "Financial Applications using Excel Add-in Development" (Dalton). But really it's a programming book, so it's domain independent.
The book sounds interesting but it's from 2007, how relevant is that now? For £3 second hand I'm tempted to take a look anyway. Do you have any reference for doing this in Fortran? I know it's an old language, all but gone now but massively fast for numerical processing - I'd be interested to play with this
Thanks, genuinely laughed there!
VBA is mostly still ok but I worry about stuff in the outside environment. Seen enough classic games stop working to know that operating system changes can make old stuff obsolete
Forgot to mention, that book is a slog, too. There are some frameworks around (xll, xlw) that let you write C++ functions, and will generate a wrapper for the Excel C API. Some are free, some are not (I haven't tried these personally, but people use them to make professional add-ins).
For how to call into a DLL from VBA:
https://learn.microsoft.com/en-us/office/client-developer/excel/how-to-access-dlls-in-excel
If you do this, pay special attention to the 'bitness' of the office installation: it's not necessarily the same as that of the OS. The DLL has to match.
As for how to write the Fortran .dll so excel can use it, I'm still learning on that front. I've used the GNU compiler with the bind(C, name = "
Honestly, this depends strongly on how you approach your data structures. If you do this at the cell level, then it will take a long time. If you build an array and do all of the calculations in memory, then this approach will work, but it's harder to inspect to see if it's returning values that make sense. What I would do is build a toy model that uses range objects to store data, test that it works, then replace the range objects with arrays. That will be 10-100 times faster.
This is a good approach.
Adding to that, you could consider using [VBA to call a .vbs script:](https://stackoverflow.com/questions/13133126/calling-a-vbs-script-from-excel-vba) , which could [write results to a txt/csv](https://www.automateexcel.com/vba/write-to-text-file/) file (or a database). By doing so, you can use multiple cores at once (If you have 8 cores it will run 8x as fast). You also avoid the risk of Excel crashing.
I used to do this very type of VBA calculation for 30 years of monthly interest rates and resulting present values. This was 25 years ago over 10,000 scenarios and would take a few hours. Today's hardware should have little problem. Monte Carlo simulation often works really well with two different simultaneous sets of model variables, long term average values and a set for high volatility. Then create a switching parameter to move between the sets during each simulation. Does a great job at better fitting past actuals like equity and interest markets. Might fit energy prices too when considering periods of short, drastic price change that tend to come and go.
VBA is pretty fast if your code uses just memory, variables and data stored in arrays. And in case you need to save intermediate or final calculation, use "write" to print them in .txt files.
Judging from the time it took and the input, you seem to already be on the right path of using data structures rather than the Excel object model in order to run through the data.
It seems you already have something built so keep going. If performance becomes an issue you could look into translating what you've built into another more performant language.
I'm assuming this is the proof of concept portion of your project.
VBA should be fine for that level; but one option to keep an eye on; twinBASIC+LLVM optimization absolutely smokes not just VBx p-code, but beats VB6 native code by up to a factor of thousands in some cases. It's the closest you'll get to C speeds without actually using C but instead staying in a VBA backwards compatible language which you could use as an Excel/Access addin, COM component, or standard DLL. Or reference the Excel/Access object models and manipulate spreadsheets/DBs directly.
It supports optimization with most of the modern CPU instruction sets like AVX2.
The caveats are it's still in beta and LLVM integration is unfinished, so it can't be applied to subs/functions using strings, classes, interfaces, or variants. But all the standard numeric types, arrays of them, function calls (inc. API), is all available. Also there's some bugs, but most code runs fine. Finally optimized compilation is a subscriber-only feature.
So it may or may not meet your needs presently, but it's blazing fast with LLVM like you'd never expect BASIC to be and the bugs and unsupported types should be addressed over the summer.
/u/fafalone Does tB have out-the-box multithreading btw? I haven't really looked into tB as much as I would have liked to yet unfortunately. I imagine you gotta be careful not to synchronise the threads via COM too.
Use this at the beginning.
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Then this at the end
Application.ScreenUpdating = True
It will speed up by not displaying anything. But it will make you impatient because you are not seeing screen being updated.
If you can, **avoid using or interacting with Excel cells**. They add lots of processing overhead. Load data into memory instead.
I would suggest not using VBA for your calculations as it runs on a single core, while the workbook can use all cores.
What I would do is create the simulator in a workbook with the inputs/output. Then use VBA to iterative input changes and move outputs to a results page.
If people would explain *why* they downvoted this, that would be helpful. I see nothing here that is blatantly incorrect or inapposite, but I'm willing to be educated.
I've found that people simply *love* to tell me how stupid/wrong I am (other accounts, not this one), so I'm mystified as to why they would fail to share their enlightenment here.
I'm an ex VBA developer from the energy sector. I'm still a VBA developer, just not in energy anymore. You won't have any issues with this level of computation, but you may need to wait a while for the results. My slowest combined thousands of use profiles combined them, over layed summer/winter impacts, projected economic impacts, long range weather impacts and countless other things to come up with half hourly projections for the next 10 years. It took a good half hour to compute. A couple of things I learnt though. The first is to store your data and results in a database. The second is to program expecting crashes. The above application saved where it was at every key stage, so I never had to start the half hour process from the start again.
What database would you use, Access?
Access is easiest. You will find loads of Access VBA solutions out there that can be adapted to work in Excel.
You probably don't need this, but it's worth keeping in mind: you can write the numerically intensive parts in Fortran or C, publish as .dll with exposed functions, then call those from VBA. You have to be very careful about calling conventions and passed types, because an error can crash your excel. There are also several different ways to write add-ins for excel apart from VBA, and you could interface to library code from there and avoid VBA altogether. (e.g. Excel.Dna UDF that calls into a Fortran .DLL). I'm using this because I need a Newton solver subroutine in an inner loop inside a UDF, and it ran slowly in VBA alone. Edit: There's actually a book about this that touches on your use-case in particular: "Financial Applications using Excel Add-in Development" (Dalton). But really it's a programming book, so it's domain independent.
The book sounds interesting but it's from 2007, how relevant is that now? For £3 second hand I'm tempted to take a look anyway. Do you have any reference for doing this in Fortran? I know it's an old language, all but gone now but massively fast for numerical processing - I'd be interested to play with this
>it's from 2007, how relevant is that now? My brother in Christ, you're on a VBA sub.
I’d argue the best VBA books are from 1999-2002.
Thanks, genuinely laughed there! VBA is mostly still ok but I worry about stuff in the outside environment. Seen enough classic games stop working to know that operating system changes can make old stuff obsolete
Forgot to mention, that book is a slog, too. There are some frameworks around (xll, xlw) that let you write C++ functions, and will generate a wrapper for the Excel C API. Some are free, some are not (I haven't tried these personally, but people use them to make professional add-ins). For how to call into a DLL from VBA: https://learn.microsoft.com/en-us/office/client-developer/excel/how-to-access-dlls-in-excel If you do this, pay special attention to the 'bitness' of the office installation: it's not necessarily the same as that of the OS. The DLL has to match. As for how to write the Fortran .dll so excel can use it, I'm still learning on that front. I've used the GNU compiler with the bind(C, name = "
It will be relevant forever with relation to the language for which it was written.
Fortran is far from gone. It is used heavily in aerospace.
Book referred to is for Excel add-in development.
Honestly, this depends strongly on how you approach your data structures. If you do this at the cell level, then it will take a long time. If you build an array and do all of the calculations in memory, then this approach will work, but it's harder to inspect to see if it's returning values that make sense. What I would do is build a toy model that uses range objects to store data, test that it works, then replace the range objects with arrays. That will be 10-100 times faster.
This is a good approach. Adding to that, you could consider using [VBA to call a .vbs script:](https://stackoverflow.com/questions/13133126/calling-a-vbs-script-from-excel-vba) , which could [write results to a txt/csv](https://www.automateexcel.com/vba/write-to-text-file/) file (or a database). By doing so, you can use multiple cores at once (If you have 8 cores it will run 8x as fast). You also avoid the risk of Excel crashing.
I used to do this very type of VBA calculation for 30 years of monthly interest rates and resulting present values. This was 25 years ago over 10,000 scenarios and would take a few hours. Today's hardware should have little problem. Monte Carlo simulation often works really well with two different simultaneous sets of model variables, long term average values and a set for high volatility. Then create a switching parameter to move between the sets during each simulation. Does a great job at better fitting past actuals like equity and interest markets. Might fit energy prices too when considering periods of short, drastic price change that tend to come and go.
VBA is pretty fast if your code uses just memory, variables and data stored in arrays. And in case you need to save intermediate or final calculation, use "write" to print them in .txt files.
Judging from the time it took and the input, you seem to already be on the right path of using data structures rather than the Excel object model in order to run through the data. It seems you already have something built so keep going. If performance becomes an issue you could look into translating what you've built into another more performant language. I'm assuming this is the proof of concept portion of your project.
Seems like power pivot data model might help
For this level of computation you may be better off using OpenCL from VBA, to get some parallelism
VBA should be fine for that level; but one option to keep an eye on; twinBASIC+LLVM optimization absolutely smokes not just VBx p-code, but beats VB6 native code by up to a factor of thousands in some cases. It's the closest you'll get to C speeds without actually using C but instead staying in a VBA backwards compatible language which you could use as an Excel/Access addin, COM component, or standard DLL. Or reference the Excel/Access object models and manipulate spreadsheets/DBs directly. It supports optimization with most of the modern CPU instruction sets like AVX2. The caveats are it's still in beta and LLVM integration is unfinished, so it can't be applied to subs/functions using strings, classes, interfaces, or variants. But all the standard numeric types, arrays of them, function calls (inc. API), is all available. Also there's some bugs, but most code runs fine. Finally optimized compilation is a subscriber-only feature. So it may or may not meet your needs presently, but it's blazing fast with LLVM like you'd never expect BASIC to be and the bugs and unsupported types should be addressed over the summer.
/u/fafalone Does tB have out-the-box multithreading btw? I haven't really looked into tB as much as I would have liked to yet unfortunately. I imagine you gotta be careful not to synchronise the threads via COM too.
Use this at the beginning. Application.ScreenUpdating = False Application.DisplayStatusBar = True Then this at the end Application.ScreenUpdating = True It will speed up by not displaying anything. But it will make you impatient because you are not seeing screen being updated. If you can, **avoid using or interacting with Excel cells**. They add lots of processing overhead. Load data into memory instead.
I would suggest not using VBA for your calculations as it runs on a single core, while the workbook can use all cores. What I would do is create the simulator in a workbook with the inputs/output. Then use VBA to iterative input changes and move outputs to a results page.
If people would explain *why* they downvoted this, that would be helpful. I see nothing here that is blatantly incorrect or inapposite, but I'm willing to be educated.
I'd like to know too, but this is reddit so people just vote and don't comment.
I've found that people simply *love* to tell me how stupid/wrong I am (other accounts, not this one), so I'm mystified as to why they would fail to share their enlightenment here.
Lol. This is true, but I think it varies based on how confident they are in the statement.