The Volatility of Volatility for Stocks and Crypto, Part 5
The next step in putting together a stochastic volatility model for predictions is to generate many random price walks from the formulation we demonstrated in the prior articles in this series, and then aggregate them to see how they behave en masse. The aggregation is done by the Excel PERCENTILE function applied many times over a lot of data, which we show momentarily.
The new sheet for this example is here:
Update May 25 2024: This sheet has another faster macro in it called mcFast in addition to the original macro mc:
https://github.com/diffent/excel/blob/main/volofvol5K.xlsm
This we do with an Excel macro. To prep for this, we add 3 additional sheets to our Excel file:
mc
probability
probplot
mc (meaning monte carlo) will store the random walks themselves so we can examine them individually if we would like to (maybe to see if our stochvol model is performing reasonably), probability will store computations of aggregate metrics made from the monte carlo walks and have some plots of constant probability “flowlines” (as we call them) versus time, and probplot will allow us to slice thru the probability data at a user specified number of days forward to analyze price versus probability at that day.
We are still using a theoretical asset starting at 1 dollar, with 1% daily volatility and no mean drift over time. The stochvol model parameters we will adjust to show how the resultant price / probability distribution changes as we change those parameters.
It may not be necessary and in fact it may be better to not store all the individual monte carlo paths on a sheet — for runtime speed purposes, and so we can run larger monte carlo trials — but we start this way for clarity. The random walk paths are stored in columns, so we will be limited in monte carlo run count for now to the max number of Excel columns allowed, which is slightly over 16k. This is a big improvement over old Excel, if anyone remembers old Excel. I think old Excel may have been 255 columns max? There are also far more rows allowed now (> 1 million), so we could transpose the row/column nature of our MC paths… but it may be better to just store the MC paths internally in Visual Basic arrays.
Readers may also chime in and say that we should not even be doing this in Visual Basic at all; but that is another story entirely. Is that FORTRAN, I hear you say? No, probably not. The point here is to make these concepts accessible to non-programmers.
The Monte Carlo macro (mc)
The macro is fairly simple, though it is a VBA coded macro and not a UI captured one. The code comments should be sufficient to describe what is going on. It is hard-coded for now to our 1000 time step example case.
Sub mc()
' generated price path start is in Sheet 1 b2 (this is the last known value = $1 in example)
' max here is about 16k before we run out of columns
Let nmonte = 1000
' to do: clear old monte carlo paths
' shut this stuff off for speed and so we only get 1 random reshuffle per each random walk generate
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For monte = 1 To nmonte
Application.CalculateFull ' this shuffles the random values to the next selection
' a new random walk price path is now generated in the sheet
' maybe can do just from sheet?
' assumes 1000 days forward, rows 2 to 1002
' copy this random walk path to the mc sheet, one column at a time (from the left)
For irow = 2 To 2 + 1000
Sheets("mc").Cells(irow, monte).Value = Sheets("Sheet1").Cells(2 + (irow - 2), 2).Value
Next
' report out to a sheet cell to show progress on cell M2 Sheet1
If monte Mod 10 = 0 Then
Application.ScreenUpdating = True
Sheets("Sheet1").Cells(2, 13).Value = monte
Application.ScreenUpdating = False
End If
Next
' now we have all of our monte carlo paths
' we walk across the columns of the probability sheet left to right
' row 1 is pre-set up for what cumulative probabilities we want to analyze
' we set this up at 0.5% to 95.5% to match our MCarloRisk3D app... every 0.5%
' 0.5% = 0.005 in the sheet
For probColumn = 1 To 199 + 1 ' add 1 more to compute mean
' get the probability value we want to compute for
Let probValue = Sheets("probability").Cells(1, probColumn).Value
' for each time step we will look now across (horizontally)
' all monte carlo paths at that time step
' and compute the price at the given probValue probability
For irow = 2 To 2 + 1000 ' n time steps
Let theRange = Sheets("mc").Range(Sheets("mc").Cells(irow, 1), Sheets("mc").Cells(irow, nmonte))
Let computedPercentile = 0
If (probValue = "mean") Then
computedPercentile = WorksheetFunction.Average(theRange)
Else
' this is a price
computedPercentile = WorksheetFunction.Percentile(theRange, probValue)
End If
' store it
Sheets("probability").Cells(irow, probColumn).Value = computedPercentile
Next
' periodic screen updates so we can see it is working
' again report out to a sheet cell to show progress on cell M2 Sheet1
If probColumn Mod 10 = 0 Then
Application.ScreenUpdating = True
Sheets("Sheet1").Cells(2, 13).Value = probValue
Application.ScreenUpdating = False
End If
Next
' switch back on automatic stuff
' if you hit Esc key to stop the macro, be sure to turn these back on manually
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubExcel hint: If you start the long running macro, to get it to stop before it is done, press the ESC key on your keyboard. It might stop in a state where sheet calculation is manual and screen updating is off, so you may want to make sure those are set back to defaults:
https://www.indeed.com/career-advice/career-development/how-to-turn-on-auto-calculate-in-excel
There may be a menu to force screen updating on also from the UI, but the Excel lightbulb search doesn’t find it:
https://learn.microsoft.com/en-us/office/vba/api/excel.application.screenupdating
For our 1000 trading day forward forecasts and only 1000 MC paths, the macro takes a few minutes to run on a Windows virtual machine in Parallels Desktop. It should run faster on a real Windows machine… we think. “Famous last words.”
After running the macro, you can examine the mc sheet and plot a random walk or few, to see the character of them.
If you don’t know how to run macros, type “view macros” in the lightbulb “Tell me what you want to do” field in Excel. It will bring up the list of macros and there is only one macro in this file called mc, so just highlight it and press Run.
Forward forecast envelope plot
After the macro runs, you can go to the probability sheet, where have an envelope plot constructed for the price/probability forward in time at various notable percentile levels: 5, 25, 50, 75, and 95th percentiles. We overlay one of the random walks on top of it as a check. There is no additional work to construct the envelope plot paths, as the data for these are already in columns. All we have to do is plot the selected columns (aside from the probability level in row 1).
These curves look somewhat like the volatility = square root of time thumb rule curves from ordinary diffusion models at constant volatility, because this new model we are talking about is built upon the same concepts and is a refinement of the basic Brownian motion diffusion process… to try to better match observed reality.
https://breakingdownfinance.com/finance-topics/finance-basics/square-root-of-time-rule/
Note in the macro comments that row 1 of this probability sheet has the percentiles (in bold) that we compute. Hence, we are computing far more data than we are plotting, though these additional constant probability flowlines will be useful later. You can add curves to this plot by ordinary Excel charting methods. For example, you may want to add a 1% curve for the low end “extreme risk” case. This data is in B2:B1002 of the probability sheet.
These plots will be more accurate if you increase the monte carlo run count in the macro (variable nmonte)… at only 1000 trials, the 1% and 99% extreme probability curves are a bit jagged.
Also you will notice that even the curves we plot here at 5% and 95% are not so smooth. This is because we are only running 1000 trials. The professionals might run a study like this at 1M trials or more… however, we will wait on this until we get a faster macro going. 5000 trials should be containable on a real PC with this macro, though you might have enough time to take a coffee break or check out funny memes on Reddit while it runs.
We use 5000 as the default monte carlo count on our MCarloRisk3D apps. We notice that if we go up to a 50k run count in those non-Excel apps (settable by the end user) the 1% and 99% curves are usually pretty smooth. But of course the pros need things computed to fractions of a penny because their investments are so large. Fractions of a penny count there. Here, we are just looking at concepts.
We think that 25% and 75% are interesting levels because they suggest that it’s 50% probable to be between those bounds, and 50% probable to be outside of those bounds according to this model. Note that this model is not yet tuned, however. And actually it isn’t even a model of a real asset. Nonetheless: Coin flip type analysis, 50/50 chance to be in or out of those bounds.
Slicing at a given time step forward
Next we can look at sheet probplot which shows a slice thru the generated aggregate time/price/probability surface at a given number of days forward (you specify the number of days in cell A1!)
We show both the cumulative distribution S-shaped curve in “Excel blue” and the derivative of it (the noisy bell-shaped curve in “Excel red”). This derivative is the price/probability curve (shape) at the given time step. We say “shape,” because we do not normalize the probability values yet (they should add up to 1.0 of course, but they do not here), so you shouldn’t try to pick points off of this red curve. It is typically easier to read values off of the cumulative distribution (S-shaped curve) than off of the probability plot itself anyway. As an accidental bonus, the cumulative curve is smoother than the bell-shaped derivative curve (probability density function), because integration is a strong low pass filter (as we note in one of our other articles). In this example, the bell shaped curve is coarse and jittery because we are only running 1000 monte carlo runs. This noise is smoothed out when we look at the integral version of the same data. E.g. at only 1000 mc runs, the analysis has not really converged to a solution yet.
We have the price axis set to constant max and mins in the above slice-at-a-timestep graphs so you can see how — when you adjust the days forward — the estimated price range expands or contracts. The bell curve stretches out over time horizontally and morphs in shape to be maybe more bullish or bearish (skewed to the left or right in this graph), depending on model parameters.
Example run of same model at nmonte = 5000:
(This took a half hour or more to run on our old virtual machine. We will update this story with better timing metrics and hopefully a faster macro when possible.)
As another example, we can set the stochvol parameters to their neutral settings and we should get ordinary Brownian motion.
Effects of stochvol parameters on forecasts
You can adjust the various stochvol parameters to examine how the price/probability forecast curves changes in shape. You need to re-run the mc macro after you change these parameters. Recall from Part 4 of this article series that these are primarily kappa, xi, and target correl (rho in the official formulation of this method). Theta is just the ordinary daily volatility of the asset, so you can leave that constant when experimenting with these other parameters.
As the following plot demonstrates (generated with different stochvol parameters that the earlier plots), the effects of the stochvol parameters seem to be subtle on the outcome price envelope shape. Though these parameters seem to affect individual price trace shapes strongly, the aggregate effect is not so strong… suggesting that the extremes and volatility bunching behavior that we model day-to-day get averaged out in aggregate studies like these.
Next steps
Before trying to get our conceptual asset model closer to a real asset, it may be worthwhile trying to speed up that mc macro if possible, since model tuning requires many runs of the mc process.
You could also edit the sheets and macros so they don’t generate 1000 days forward. That would also speed things up. E.g. maybe you only want to do a year ahead (365 days for crypto and 252 days for stocks).
How can we adjust the stochvol parameters to best capture the behavior of a real asset? There are notes suggesting that models like this can be tuned to market prices of options (put / call prices). Of course we have to compute those prices first from this model. For European (exercise at expiry) this is not such a problem to compute in code like this. American options which can be exercised at any time before expiry present a much more complicated computation story.
Check out the notes on Calibration in this wiki:
https://en.wikipedia.org/wiki/Heston_model
In our MCarloRisk3D verison of this stochvol method, we try to use the backtest features of the app to allow us to tune stochvol parameters to historical reality. Perhaps this is not as great as tuning to live market options prices… but it can be easier, especially since some assets of interest don’t have much of an options market (e.g. crypto currency). As we also noted in Part 4 of this series, our writeup of this concept of tuning stochvol models to backtests with Bitcoin prices is online at:
https://papers.ssrn.com/sol3/papers.cfm?abstract_id=3693387
Oh yeah, we are still missing the drift term. We might try to add that also. Our empirical based models in MCarloRisk3D don’t have drift terms either, because we just take historical returns data as-is. If those have a tendency to be bullish or bearish, so be it.
If any readers see any issues with the Excel sheet or any other concepts in this article series, please let us know and we will try to fix them… this series represents a lot of concepts stacked up!
Update May 25 2024:
We added a new Excel file with a faster macro called mcFast (link near top of this article). This may not be the ultimate in speed, but it does seem to run about 2x faster than our original mc macro according to our quick tests. This is accomplished by using an internal 2D array to store temp monte carlo path data instead of using the sheet mc for this purpose. It may not be the ultimate in speed, because we copy the rows of this matrix out to perform the PERCENTILE computation using regular For/Next loops. Do Excel functions allow stride constants like LAPACK does, or access to rows and columns of matrices directly without pulling that data out into a separate array? Is there a block copy function to take data from a row of a 2D VBA array to a separate 1D array? We do not know at the moment, so we thought we would ship this faster macro as-is for an incremental gain. There is also the potential of using an array-of-arrays structure for this mc path data — at which point we could then just compute the PERCENTILE on the pre-formed inner arrays without copying the data out — instead of using a 2D array… but the 2D array update was the first one we got working.
This mcFast macro will also allow larger monte carlo runs to be done (nmonte can now be larger than 16k if you want).
Additionally, we have enhanced the macro progress meter a bit on Sheet1 and added a total runtime metric. The cell having the value “mean” shown below in column M (in the following screenshot) will report out the current monte carlo count as it iterates, and then it will report out the percentiles that it is cycling through as it is aggregating the results… finally ending on mean, the last computed average flowline.
For even faster runs of these types of drift/diffusion studies including parallelization across CPU cores, and along with more features than in our Excel examples, look for our macOS, iOS, Windows, and Android apps called MCarloRisk, MCarloRisk3D, and MCarloRisk3DLite.
Further reading
Some of our previous articles, while not involving stochastic volatility, do involve empirical returns distributions instead of assuming normality of returns. The link below discusses our application MCarloRisk3D — which is available on several platforms (free for now!) — and which also computes price/probability distributions like we do in the present article’s Excel file:
https://medium.com/@nttp/backtest-to-the-future-1ea4db87845c
And since it runs compiled code, it is much faster than this Excel macro… in addition to having many backtest and visualization tools built in.
While this article series has not gotten into options price estimates yet, the following more advanced paper compares using ordinary Black-Scholes model versus the Heston model for options pricing. As you can see, the effect of the Heston model adjustments are subtle but notable versus Black-Scholes assumptions of normal distribution of returns, i.i.d., and a mean drift (is there any more than that… let us know if you think of anything!). So in our plots that merely show price/probability, we would not expect to see huge differences in the stochvol generated curves versus setting stochvol parameters to their neutral no- stochvol levels. Because, as you may know, options price estimates can be directly computed from this price-probability analysis.
The graphs here showing the observed option values falling between the two models in the “corner” suggest that some interesting model blends may be possible.
For some reason, the link to the paper pasted directly inline in a scrolling sub window, which is apparently a feature of Medium. If you scroll through it, you will see some pages particularly dense with complicated equations. This seems to represent the much higher effort involved in doing stochastic calculus analytically versus via monte carlo simulation like we do in Excel here. We find papers like this to be useful even if we don’t go through all the equations, for inspiration and ideas. You must admit that our little Excel formulas and the one VBA macro we wrote is nowhere near as complicated as the equations in the below paper. Of course we are not computing options prices yet; but as we will show shortly, this is not such a complicated problem as the paper authors make it out to be, especially exercise-at-expiry European style options. Additionally, with a monte carlo simulated model like we have presented here, we can easily add more tuning parameters to the model to get it to correspond to observed reality better, which would morph the model into a modified Heston model or a Heston-inspired model. Adding more degree of freedom parameters to this model is not so easy to do analytically. For example, we can think about breaking the normality “contract” and use non-normal returns and/or non-normal volatility-of-volatility jitter in the model. Such is not so easy to do in the analytic world of pure Gaussian distributions, but it is very easy to do in the monte carlo simulation world. Of course if we do add parameters, it is best to create and adjust model tuning parameters that correspond to observations, for better generalization. For example, if we observe non-normal returns in the market, it seems fair game to add similar non-normal returns to our models. As a contrast, when this author worked in physical simulation, occasionally an engineer would want to modify the acceleration of gravitation parameter of 9.81 m/s² to get models to behave better. The more experienced engineers would then step in: No no no no… probably shouldn’t change that unless we are building something for Mars or the Moon or something like that.
Part 6 of this series… hot off the presses!
https://medium.com/@nttp/the-volatility-of-volatility-part-6-9ad107678eb0
[Quantstart] Heston Stochastic Volatility Model with Euler Discretisation in C++, https://www.quantstart.com/articles/Heston-Stochastic-Volatility-Model-with-Euler-Discretisation-in-C/
