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.
Generally, VBA has every function the worksheet has. You could use: Code: WorksheetFunction.Norm_Dist I appreciate you giving a deeper explanation of your code , but I'd prefer seeing your code (and possibly, the sheet--secret sauces protected--as explained earlier) before I can offer the best possible help.
Let's go for the second question right from the start, yes you can really save on that, PC are doing just the same function and you are saving big by doing it on it and not on MAC, as for first question - you really can know the answer only by testing it
Sorry for the late reply. This helps! I managed to get the computation time down to something reasonable. Thank you all for your helpful suggestions.
Depends on the code ... as your cite concurs in the second sentence: "But it also depends on how your code and functions are written." Some stuff has to occur sequentially; especially with financial time-series code. Anyway, as I and others have already stated, you can split the problem up yourself and run multiple instances of Excel in order to utilized otherwise unused CPU potential--then stitch everything back together once all the code completes. Even this process can be automated via code.
This link has many suggestions on how to speed up the computations. I will have to study them carefully. Thank you.
I ran my excel VBA on a PC with a Quad core i7, about 1.5x the speed of my dual core MacBook Pro i7. Surprised, it was actually slower!!! So, indeed Excel VBA is single thread. Two other differences: 1. Mac has copy of 2011 Excel, PC has 2019 Excel. 2. RAM: Mac 16G and PC 8G? Must be the RAM.