Extra RAM to speed up simulation using Excel?
Discussion
A query for the hardware experts on here.
I'm running MonteCarlo simulations using Excel on two Windows10 laptops. One is 2016 i5 with 12Gb. The other is 2019 i7 with 8Gb. Neither can be upgraded/added to according to Crucial. I think the RAM might be soldered in. Both run 64-bit Windows, with Desktop version of Excel (64-bit). I tried O365 on the 8Gb machine but it didn't seem to complete the simulation any faster.
The 8Gb takes c.8-12 minutes to complete each simulation exercise (number of simulations vary). The 12Gb one seems to work a little faster, more like 5-10mins. I'm often running these 6-8 times per day, as part of a process which takes c.5-15mins excluding the simulation., so c3-4 hours in total. With less time calculating, I can run more simulations.
There is no immediate need to change either laptop yet, but I'm wondering if buying faster hardware would lead to a discernible increase in speed. As an example Dell outlet has refurbished 11th gen i7 XPS with 32Gb or 64Gb. I suspect that processing time is not linear, so quadrupling RAM will not lead to 75% time saving, but don't know. I also have no idea if changing processor to AMD Rayzen would produce quicker simulation than Intel 10th/11th gen i5/i7.
Its quite a costly undertaking to buy just to see, and I'm not sure if the time savings will be sufficient to justify the investment, but thanks in advance for thoughts / suggestions.
I'm running MonteCarlo simulations using Excel on two Windows10 laptops. One is 2016 i5 with 12Gb. The other is 2019 i7 with 8Gb. Neither can be upgraded/added to according to Crucial. I think the RAM might be soldered in. Both run 64-bit Windows, with Desktop version of Excel (64-bit). I tried O365 on the 8Gb machine but it didn't seem to complete the simulation any faster.
The 8Gb takes c.8-12 minutes to complete each simulation exercise (number of simulations vary). The 12Gb one seems to work a little faster, more like 5-10mins. I'm often running these 6-8 times per day, as part of a process which takes c.5-15mins excluding the simulation., so c3-4 hours in total. With less time calculating, I can run more simulations.
There is no immediate need to change either laptop yet, but I'm wondering if buying faster hardware would lead to a discernible increase in speed. As an example Dell outlet has refurbished 11th gen i7 XPS with 32Gb or 64Gb. I suspect that processing time is not linear, so quadrupling RAM will not lead to 75% time saving, but don't know. I also have no idea if changing processor to AMD Rayzen would produce quicker simulation than Intel 10th/11th gen i5/i7.
Its quite a costly undertaking to buy just to see, and I'm not sure if the time savings will be sufficient to justify the investment, but thanks in advance for thoughts / suggestions.
HappyMidget said:
Do you have access to cloud computing on Azure/AWS/GCS? If so, I would suggest looking into running this in Databricks and scale out to more cores. Helpful to know a bit of python for that though.
Thanks. I'll check out running Excel via AWS or similar. My Python skills are almost non-existent, so will check the consumer-end of their offering.Although I am not an expert in Monte Carlo simulations, I do have a decent background in optimising the performance of intensive tasks.
Without knowing more it's hard to tell what your best course of action is, but as a general rule "buy more hardware" is your last and least effective option. The first question to answer is - why are things taking a long time? In general there are three hardware things to look at, but the most important, and most effective, is to look at the way you have written the simulation. Are you sure it is efficient? Particularly with something like Monte Carlo, which be definition runs many iterations with varying inputs, small optimisations to your algorithm can have major impacts on your overall runtime. And particularly with something like Excel, which likes to do all kinds of things in the background without you asking for it, you need to be aware of what is going on behind the scenes. Like I say I'm no Monte Carlo expert but this https://stackoverflow.com/questions/65961042/speed... looks like a decent place to start.
If you're sure that your algorithm and implementation are efficient then yes you can start to look at updating the hardware. This means understanding where your bottlenecks are, so open up Task Manager and take a look a what's going on when the simulation is executing.
Is your CPU locked at 100% (or, worse, do you have a single-threaded implementation and therefore you are not using all your CPU resources)?
Is all of your RAM in use?
Is the hard drive going mad?
You need to find out which resource is being exhausted and then you can plan out how to increase that resource. There is no point in buying more RAM if CPU is your problem, for instance.
But I can't emphasis enough that you need to optimise your algorithm and implementation first. Throwing hardware at an implementation problem is a waste of time and money.
Without knowing more it's hard to tell what your best course of action is, but as a general rule "buy more hardware" is your last and least effective option. The first question to answer is - why are things taking a long time? In general there are three hardware things to look at, but the most important, and most effective, is to look at the way you have written the simulation. Are you sure it is efficient? Particularly with something like Monte Carlo, which be definition runs many iterations with varying inputs, small optimisations to your algorithm can have major impacts on your overall runtime. And particularly with something like Excel, which likes to do all kinds of things in the background without you asking for it, you need to be aware of what is going on behind the scenes. Like I say I'm no Monte Carlo expert but this https://stackoverflow.com/questions/65961042/speed... looks like a decent place to start.
If you're sure that your algorithm and implementation are efficient then yes you can start to look at updating the hardware. This means understanding where your bottlenecks are, so open up Task Manager and take a look a what's going on when the simulation is executing.
Is your CPU locked at 100% (or, worse, do you have a single-threaded implementation and therefore you are not using all your CPU resources)?
Is all of your RAM in use?
Is the hard drive going mad?
You need to find out which resource is being exhausted and then you can plan out how to increase that resource. There is no point in buying more RAM if CPU is your problem, for instance.
But I can't emphasis enough that you need to optimise your algorithm and implementation first. Throwing hardware at an implementation problem is a waste of time and money.
otolith said:
Have you examined the performance monitor to see what is maxing out?
TBH, I'd rewrite it in something faster if performance is really important.
Thanks. I'll check the monitor later this afternoon and share it on here.TBH, I'd rewrite it in something faster if performance is really important.
I would be keen to explore a faster tool, but am nervous of a time-consuming learning curve, given that I've got a good grasp of Excel and can rely on some fairly complex stuff going on within it.
What would you suggest? Python?
It's almost certainly CPU generation, and model positioning (i5, i7 etc) within that range. RAM is unlikely to make much difference once there's "enough", and not enough would probably give you bigger differences than you're reporting.
If you want more speed and can't migrate to better software, look at getting a 12th Gen i7 will probably get you 15-25%. It would help to know exact CPU models.
A desktop will probably be faster for the same price if that is an option.
Obviously make certain the machines are plugged in and well cooled (lifted off a hard surface not plonked on a blanket) for best performance.
If you want more speed and can't migrate to better software, look at getting a 12th Gen i7 will probably get you 15-25%. It would help to know exact CPU models.
A desktop will probably be faster for the same price if that is an option.
Obviously make certain the machines are plugged in and well cooled (lifted off a hard surface not plonked on a blanket) for best performance.
deckster said:
Although I am not an expert in Monte Carlo simulations, I do have a decent background in optimising the performance of intensive tasks.
Without knowing more it's hard to tell what your best course of action is, but as a general rule "buy more hardware" is your last and least effective option. The first question to answer is - why are things taking a long time? In general there are three hardware things to look at, but the most important, and most effective, is to look at the way you have written the simulation. Are you sure it is efficient? Particularly with something like Monte Carlo, which be definition runs many iterations with varying inputs, small optimisations to your algorithm can have major impacts on your overall runtime. And particularly with something like Excel, which likes to do all kinds of things in the background without you asking for it, you need to be aware of what is going on behind the scenes. Like I say I'm no Monte Carlo expert but this https://stackoverflow.com/questions/65961042/speed... looks like a decent place to start.
If you're sure that your algorithm and implementation are efficient then yes you can start to look at updating the hardware. This means understanding where your bottlenecks are, so open up Task Manager and take a look a what's going on when the simulation is executing.
Is your CPU locked at 100% (or, worse, do you have a single-threaded implementation and therefore you are not using all your CPU resources)?
Is all of your RAM in use?
Is the hard drive going mad?
You need to find out which resource is being exhausted and then you can plan out how to increase that resource. There is no point in buying more RAM if CPU is your problem, for instance.
But I can't emphasis enough that you need to optimise your algorithm and implementation first. Throwing hardware at an implementation problem is a waste of time and money.
Thanks - lots of wisdom there.Without knowing more it's hard to tell what your best course of action is, but as a general rule "buy more hardware" is your last and least effective option. The first question to answer is - why are things taking a long time? In general there are three hardware things to look at, but the most important, and most effective, is to look at the way you have written the simulation. Are you sure it is efficient? Particularly with something like Monte Carlo, which be definition runs many iterations with varying inputs, small optimisations to your algorithm can have major impacts on your overall runtime. And particularly with something like Excel, which likes to do all kinds of things in the background without you asking for it, you need to be aware of what is going on behind the scenes. Like I say I'm no Monte Carlo expert but this https://stackoverflow.com/questions/65961042/speed... looks like a decent place to start.
If you're sure that your algorithm and implementation are efficient then yes you can start to look at updating the hardware. This means understanding where your bottlenecks are, so open up Task Manager and take a look a what's going on when the simulation is executing.
Is your CPU locked at 100% (or, worse, do you have a single-threaded implementation and therefore you are not using all your CPU resources)?
Is all of your RAM in use?
Is the hard drive going mad?
You need to find out which resource is being exhausted and then you can plan out how to increase that resource. There is no point in buying more RAM if CPU is your problem, for instance.
But I can't emphasis enough that you need to optimise your algorithm and implementation first. Throwing hardware at an implementation problem is a waste of time and money.
I agree that in general throwing money at this is the last option. I've got Excel pretty much optimised (I think) and the MC Sim is run with a paid-for Add-In.
I'll check that link - thanks again.
sideways sid said:
otolith said:
Have you examined the performance monitor to see what is maxing out?
TBH, I'd rewrite it in something faster if performance is really important.
Thanks. I'll check the monitor later this afternoon and share it on here.TBH, I'd rewrite it in something faster if performance is really important.
I would be keen to explore a faster tool, but am nervous of a time-consuming learning curve, given that I've got a good grasp of Excel and can rely on some fairly complex stuff going on within it.
What would you suggest? Python?
https://myfinancialmarkets.wordpress.com/2017/12/1...
xeny said:
It's almost certainly CPU generation, and model positioning (i5, i7 etc) within that range. RAM is unlikely to make much difference once there's "enough", and not enough would probably give you bigger differences than you're reporting.
If you want more speed and can't migrate to better software, look at getting a 12th Gen i7 will probably get you 15-25%. It would help to know exact CPU models.
A desktop will probably be faster for the same price if that is an option.
Obviously make certain the machines are plugged in and well cooled (lifted off a hard surface not plonked on a blanket) for best performance.
Thanks.If you want more speed and can't migrate to better software, look at getting a 12th Gen i7 will probably get you 15-25%. It would help to know exact CPU models.
A desktop will probably be faster for the same price if that is an option.
Obviously make certain the machines are plugged in and well cooled (lifted off a hard surface not plonked on a blanket) for best performance.
Desktop is not an option unfortunately. The older CPU is: Intel(R) Core(TM) i5-6300HQ CPU @ 2.30GHz
That has 12Gb RAM
Both are used almost exclusively on desks in different offices, running mains via PSU or brand new dock.
This might be worth a read:
https://www.oraylis.de/blog/2016/monte-carlo-simul...
You can do your simulation in DAX and/or with Power Query in Excel. If that code is decent it should be faster than doing the same thing in a Workbook.
https://www.oraylis.de/blog/2016/monte-carlo-simul...
You can do your simulation in DAX and/or with Power Query in Excel. If that code is decent it should be faster than doing the same thing in a Workbook.
If you're using a paid-for Excel add-in then it's not going to be as simple as "using VLOOKUP".
Task manager should show you whether your CPU is maxed out or not, and how much RAM is being used (very roughly). If it's using less than 100% of either, what happens if you run 2 of them concurrently in different Excel instances? Sometimes this can have surprising results.
You say a desktop can't be used - but why can't you put the spreadsheet on a desktop and RDP into it? Or even an AWS/Google desktop computer, these can be incredibly speedy too. A cloud desktop is quick to try out, if your organisation already has a relationship and the security covered. If you're running financial MC then it probably does.
Have you tried asking the software provider for tips?
Task manager should show you whether your CPU is maxed out or not, and how much RAM is being used (very roughly). If it's using less than 100% of either, what happens if you run 2 of them concurrently in different Excel instances? Sometimes this can have surprising results.
You say a desktop can't be used - but why can't you put the spreadsheet on a desktop and RDP into it? Or even an AWS/Google desktop computer, these can be incredibly speedy too. A cloud desktop is quick to try out, if your organisation already has a relationship and the security covered. If you're running financial MC then it probably does.
Have you tried asking the software provider for tips?
Learn Python - it's no more difficult than Excel and will has more libraries that you can imagine. In fact I would be surprised if someone hasn't already written a library to do exactly what you want.
Jupyter Notebooks are a great way to learn and free to use here
https://colab.research.google.com/
Jupyter Notebooks are a great way to learn and free to use here
https://colab.research.google.com/
otolith said:
Here's some actual benchmarking of programming languages running this task
https://myfinancialmarkets.wordpress.com/2017/12/1...
Thanks. That link is interesting and relevant.https://myfinancialmarkets.wordpress.com/2017/12/1...
paulrockliffe said:
This might be worth a read:
https://www.oraylis.de/blog/2016/monte-carlo-simul...
You can do your simulation in DAX and/or with Power Query in Excel. If that code is decent it should be faster than doing the same thing in a Workbook.
Thanks. I skimmed over that whilst on a call but will read it properly over the weekend to see if I can apply DAX, as I thought DAX was more applicable to visualising data for BI etc, but every day is a school day and all that,https://www.oraylis.de/blog/2016/monte-carlo-simul...
You can do your simulation in DAX and/or with Power Query in Excel. If that code is decent it should be faster than doing the same thing in a Workbook.
biggiles said:
If you're using a paid-for Excel add-in then it's not going to be as simple as "using VLOOKUP".
Task manager should show you whether your CPU is maxed out or not, and how much RAM is being used (very roughly). If it's using less than 100% of either, what happens if you run 2 of them concurrently in different Excel instances? Sometimes this can have surprising results.
You say a desktop can't be used - but why can't you put the spreadsheet on a desktop and RDP into it? Or even an AWS/Google desktop computer, these can be incredibly speedy too. A cloud desktop is quick to try out, if your organisation already has a relationship and the security covered. If you're running financial MC then it probably does.
Have you tried asking the software provider for tips?
Thanks. I've implemented all the stuff that the software provider recommends, and am going to investigate AWS or similar over the weekend. BtW, this is not being done for an organisation, its a personal project that I'm trying to run around client work.Task manager should show you whether your CPU is maxed out or not, and how much RAM is being used (very roughly). If it's using less than 100% of either, what happens if you run 2 of them concurrently in different Excel instances? Sometimes this can have surprising results.
You say a desktop can't be used - but why can't you put the spreadsheet on a desktop and RDP into it? Or even an AWS/Google desktop computer, these can be incredibly speedy too. A cloud desktop is quick to try out, if your organisation already has a relationship and the security covered. If you're running financial MC then it probably does.
Have you tried asking the software provider for tips?
sideways sid said:
otolith said:
Here's some actual benchmarking of programming languages running this task
https://myfinancialmarkets.wordpress.com/2017/12/1...
Thanks. That link is interesting and relevant.https://myfinancialmarkets.wordpress.com/2017/12/1...
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff