Code:
If you can post your code
(with dummy code, if needed, protecting your secret sauces)
in a code window, we may be able to help further.
You can google how to improve the performance of excel vba code, but what I mainly do is:
- Split the problem up (if possible) and run multiple instances of Excel (Windows). No external software needed. Just make copies of the workbook with slightly different names, and start each while pressing the ALT key. Google: do you want to start a new instance of excel
- Disable screen updating as much as possible (rely more on the status bar). Don't forget to re-enable at code end or when code stops with an error.
- To the extent possible/feasible, transfer the sheet into an array (Not one cell at a time!), and perform calculations on the array in VBA rather than in the spreadsheet. (Doing this will get you used to coding as it would more likely be done in a standard language. For maximum speed, the sheet would only hold values, no formulas. All of the calculations would be done in VBA.)
- Via VBA, convert as many formulas as possible to plain values before running looping codes. Reconvert to formulas at end of code.
Good Luck!
Keep us Posted!
Thank you for your suggestions.
I am primarily calculating Black Scholes, each day for 25 years worth of historical price/volume data, For every day, I can calculate option chains using Black Scholes and input variables are IV, T, risk free rate, dividend rate. I can make assumptions on IV (or use HV as a proxy), use historical treasury & dividends for example. VBA is used mainly to loop through the option chains, (and/or butterfly set up) and loop through the 6,600 daily data points. The number of computations can add up quickly: If I compute just 10 option prices for each day (like ~ 3 butterflies), I would have to perform 66,000 Black Scholes computations for each run.
VBA makes it easier to study outcome vs input parameter change. For example, if there is a risk premium, or I have to pay commissions, or slippages, how do they affect my outcome?
I perform all the Black Scholes on Excel because my VBA has very limited function capability - does not have NORMDIST function for example.
The obvious question is why do I need 25 years of data? Because there are 25 years of price/volume data so might as well use all of them.
