So I did a MC sim (100x5100 cells) in Excel today and nearly brought the PC to a standstill. I could not save the file either (35MB). Usually I do MCs in R or Stata but had no choice today. My question to the IT guys out there: is there a way to allot more CPU or Ram to excel? Are there any ways to optimize the program load. Note: the problem is not only present on my workplace PC (core 2 duo 3ghz, 4gb ram) but also on my private pc (i7 2.8ghz, 16gb ram). I also changed excel to highest priority in the task manager and closed all other programs.
I don’t know that you need “IT.” Not sure what your model looks like but you should become familiar with how to design models with partial recalculation to avoid redundant recalcs by using Excel’s Manual Calculation mode:
If you know VBA, you can create an algorithm to manage mode switching within the whole process chain automatically.
The model was already in manual calculation mode. It seems Excel is only alloted up to 500MB RAM (that’s the highest number clocked on task manager). I would like to expand its memory access, because I think that’s the thing holding it down…
Have you looked at this: http://msdn.microsoft.com/en-us/library/office/bb687868(v=office.15).aspx
Are you using 32 bit Excel?
32-bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process. A data model’s share of the address space might run up to 500 – 700 megabytes (MB), but could be less if other data models and add-ins are loaded. Do you have the ability to store the data in PowerPivot to reduce the file size? And there are addins that do MC sims in Excel, have you tried any?
Get 64-bit excel, a SSD HD, more ram, and a faster processor. The models I have literally maxed out Excel and will barely run on a top of the line machine, so much so that I had to rebuild the back end in SQL. Excel is really a pretty weak application overall and should only be used as a front end if possible. It constantly crashes and has an unbelievable number of bugs for something that has been out for so long.
But seriously, get 64-bit, it made all the difference for me running large models.
Both systems run on 64bit. As I said, I usually do sims in R which works just fine. However, sometimes I do them in Excel for visualization purposes or to send them to clients. I don’t have active add-ins except Reuters, solver, data-analysis, and a hotkey pack. The model in question has two parts a GBM-random forecaster for the Eurostoxx 50 20 years out and a 100x MC sim based on the forecaster for an insurance product with specific parameters (no downside, cap rate, part. rate). My private PC is really fast, but after pressing F9 I can basically go and get myself a cup of coffee… I could easily do the same in R with no problems.
I run a low latency algo trading strategy on a platform supported by a quantum computer that uses liquid helium for superfluidity, but today I had to do some hedge adjustments on my accounting calculator. I don’t think my Staples 8-digit display calculator can handle well the dynamic smoothing of the kurtosis surface I’ve parametrized in the hyperplane determined by the boundary of my state variables - think it must be the CVS battery pack that it came with is getting weak. Should I juice it up by switching to energizer or duracell? Any hacks appreciated.
Dude – your setup is a joke. Everyone knows that in order to model the kurtosis surface properly, you need to look ahead and map out the 5th and 6th moments of the distribution (as well as to collapse all known time series data and perform spectral analysis vis-a-vis a deterministic chaos approach to visualize all possible paths of each electron governing investment outcomes) you need to be running, MINIMUM, a NUDT Xeon E-5-2692 + Xeon Phi 31S1P with flux capacitor to manage the tachyon ripples emanating from such a system.
When I receive the outputs from my setup mentioned above, I prefer seeing them on my Lotus 1-2-3 spreadsheets. Although, the formatting options really kind of suck and it would be great if I could do stuff like change the font size.
So the point you guys so eloquently try to make is that there is nothing I can do and that I need to accept the limitations of Excel. Thanks for that…
You can write the heavy stuff in c++ and call it within excel
This is also a possibility with R, though I’ve never done it.
I assume, however, that if you posted here, that is probably not a feasible alternative.