Geometric Brownian Motion Help

[hype_hoosier]

TIA! I have seen a few old posts on similar topics but still trying to get some help on this as my situation is different than other posters.

I am very new to VBA and trying to simulate stock prices using the geometric brownian motion formula, and I'm trying to make the code dynamic and reference cells in the worksheet so if I run the code again, I do not need to update the code itself, just the values within each cell. I am also trying to run a certain number of iterations (I would also like the number of iterations to be dynamic and reference a cell in the worksheet in case I wanted to change the number of iterations in the future, but typically I'd want to run 1000+ iterations, so I am trying to make the code efficient). Using 64-bit Office 365 Excel.

The starting point for each iteration would be S, which is the stock price and would be located in Cell B1 in my example. Then every subsequent day would follow the formula below.

The formula is as follows:
X(n) = X(n-1) + u*X(n-1)*Delta_t + Sigma*X(n-1)*En*sqrt(Delta_t)
Where:
u = Drift = Cell B2 in a worksheet (a percentage)
Sigma = Annual Volatility = Cell B3 in a worksheet (a percentage)
Delta_t = Time Interval Between Steps = Cell B5 in a worksheet (calculated as 1 divided by the total number of steps).
En = A standard Normal Random Variable = NORMSINV(Rand())

The total number of steps I would need in each iteration would be calculated when I run the code given it is based on the number of days the NYSE is open for the next 5 or so years from the time of running the code. So I am trying to have the total number of steps (i.e. total number of trading days for the next 5 years) in cell B4 and have the code update how many steps to do each time I run it.

I'm trying to have the output data hardcoded (to reduce memory usage) and so I would only need to update my input cells and then re-run the code for the old output data to be erased and overwrote by the new output data.

Finally, I am trying to get the data to go horizontally (i.e. my first iteration would be housed in row 10 for example, and iteration 2 would be in row 11, etc.)

TLDR: geometric brownian motion that is as adaptive as possible based on input cells in a worksheet.

Appreciate any and all help!!

[p45cal]

Attached has a button at cell I3 which calls the macro blah.
Double and triple check it.
A few things I'm not sure about:
How often En should be recalculated; at the moment I'm recalculating it the max no. of times, that is, each day.
How Annual volatility is used bearing in mind there is no annual anything in the code or your formula.

On the sheet is a formula in cell B4:
=NETWORKDAYS(TODAY(),B6,G2:G8)
where G2:G8 is a list of holidays so that those dates are excluded from the count of trading days. In reality there are more holidays (about 9 per year) which you need to add (about 45 holidays in total) which I'll leave you to supply, but where I've put that list of holidays is probably inconvenient, so you can relocate it (to another sheet?) and change the reference in the formula.

You can set the destination of the results in line 7 of the code. Bear in mind the clearing of old results uses Currentregion so take care to leave at least 1 completely empty row above and below the results so other stuff doesn't also get deleted.

[hype_hoosier]

p45cal -

Wow. I am speechless. I cannot say thank you for this enough! I was in the middle of trying to figure out how to incorporate the usage of arrays to speed up the calc (as you can imagine mine was a "bit" slower than your file).

To you questions, yes the input En should be re-calc'd each interval (or day in this case). And #2, yes, my mistake. I referred to it as annual volatility but I just back-solve the daily volatility and drift based on annual amounts.

My only follow up question would be, what do you think is the most efficient way to add a row at the top that shows that column's time step(i.e. starting at 0 and ending at 1191 in the file provided)?

I'm still trying to sort out the other steps/analysis I need to perform based on the output data, so I may have a few additional questions. Do you prefer if I PM or post them?

But thank you again, I truly cannot tell you how helpful this is.

-Hoosier

[p45cal]

Change to:
Sub blah()
S = Range("B1").Value
u = Range("B2").Value
Sigma = Range("B3").Value
TotalSteps = Range("B4").Value
Delta_t = Range("B5").Value
Iterations = Range("B7").Value
Set Destn = Range("A12")

SqrtDelta_t = Sqr(Delta_t)

Destn.CurrentRegion.Clear

For j = 1 To Iterations + 1
ReDim Results(1 To 1, 1 To TotalSteps)
If j = 1 Then
For i = 1 To UBound(Results, 2)
Results(1, i) = i - 1
Next i
Else
Results(1, 1) = S
For i = 2 To UBound(Results, 2)
Randomize
Results(1, i) = Results(1, i - 1) + u * Results(1, i - 1) * Delta_t + Sigma * Results(1, i - 1) * Application.Norm_S_Inv(Rnd()) * SqrtDelta_t
Next i
End If
Destn.Resize(, UBound(Results, 2)).Value = Results
Set Destn = Destn.Offset(1)
Next j
End Sub

[hype_hoosier]

Incredible!

Thank you so much again, this is incredibly helpful and greatly appreciated! Once I presumably hit a road block on the other steps, I will reach out.

-Hoosier

[hype_hoosier]

Okay, I give up. I made a few very minor tweaks to the code (truly nothing important).

I am now trying to address part 2 and 3 of my analysis but cannot figure it out. I added columns in blue, and this is what I am trying to replicate via VBA. I am trying to perform 2 "rolling counts" for a 30 day period (so day 1 to 30, day 2 to 31, etc.) and counting how many times the stock price is above 12.50 or 15.00, if at all (Column IS to RD is the 12.50 test and RE to ZP is the 15.00 test). A few issues that I am having are:
a) making these tests start on day 30 (since that is the first day that it can start)
b) making these tests dynamic so that as the Total Steps increases, the test continues being performed from day 30 to the last day in the iteration

Finally, I am trying to then pull the day # that the 30 day rolling count exceeds 20, if at all (this is performed in column A and B in blue). So in the attached file, on day 115 for the second iteration the rolling count in the 12.50 test is 21 (Cell LZ14). This "20 day" count is also done for the 15.00 threshold.

How do I go about incorporating these into the VBA? I made the number of steps and number of iterations smaller to make the manual nature easier, but in the end, it will be a significantly larger lift for Excel, hence the use of VBA.

Thank you again so much! Really appreciate it!!

[p45cal]

See attached.
Note:

a) making these tests start on day 30 (since that is the first day that it can start)!So if the price exceeds 12.5 for 21 days from the very start you don't want to know about it? The attached will report >20 days >12.5 if it happens before 30 days.

[hype_hoosier]

Sorry for the confusion, I should've wrote it clearer. I am trying to create a 30 day minimum holding period or lock up. So even if the price is above 12.50 for 21 days in a row starting at time point 1, I would not want it to say "21" I'd want it to say "30" (since on the 30th day when you look back to timepoint 1, the count would definitely be above 20). It looks like you're saying the file provided reports it as the 21st day is the first time the price breaks 12.50/15 for a 20 day period in this scenario, but in my simulation I am trying to get the rolling count to start on Day 30 and the lowest number that could show up in column A/B is 30.

Thank you as always!! You are a life saver!

[p45cal]

See attached

[hype_hoosier]

Thank you so much!!

Going to try and actually do some additional items on my own (like finding the min., max. and mean of each iteration) which probably seems rudimentary to you haha.

Thank you so much for all the help, this is incredible.

Hoosier

[p45cal]

Hint:
Application.Max(Results)
Application.Min(Results)
Application.Average(Results)

[hype_hoosier]

Hahaha I tried that but then was coming across the issue of having the starting price being potentially the min or the max. I added this under the BreakDay calcs (probably inefficient in terms of computational power):

29818

[p45cal]

but then was coming across the issue of having the starting price being potentially the min or the max.I don't see a problem with that, but if you really want to miss the first value then you'd get away with:
Destn.Offset(, -2).Resize(, 2).Value = Array(BreakDay1, BreakDay2)
Results(1, 1) = Empty
MsgBox "max = " & Application.Max(Results)
MsgBox "min = " & Application.Min(Results)
MsgBox "avg = " & Application.Average(Results)
Set Destn = Destn.Offset(1)
New code in red.
This first writes the full results to the sheet, then removes the first value, then carries out max, min & average calculations.

[hype_hoosier]

Oh okay I see! That makes sense! Just implemented that.

One question - right before the actual math calculation, there is a line that just says "Randomize". What does this accomplish/what is the intention with this?

Thanks for everything! Seriously cannot say it enough.
Hoosier

[p45cal]

Best way to answer that is to refer you to microsoft:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/randomize-statement
The subsequent line contains a generated random number with Rnd()

[hype_hoosier]

Sorry for the delayed response. Yes, that definitely helps explain it! Figured it was related to the Rnd function.

Thank you again for all the help, I cannot put into words how helpful this was! If there are any other questions, I will probably reach out. Stay safe!!

-Hoosier

[hype_hoosier]

Hi!

I am back with an issue! The Rnd function doesnt seem to be working as intended!

I thought including the Randomize function in the line prior to the "Rnd" would help make it more random, but it seems that there is a pattern starting around day 258. When backsolving for what the Norm.S.Inv(Rnd()) is in the formula, Day 1 is equal to day 258, Day 2 is equal to day 259 and so on.

It doesn't seem that each number is truly random. How do I go about ensuring that there is more randomness and that the Rnd function does not repeat, and doesn't repeat in a predictable way?

Thank you so much in advance!!!

[p45cal]

Could you save me some time? Tell me what I should do (click) what settings I should use and where should I look to see this repeatability. Then I can try to eliminate it.
Edit post posting : OK I've seen the yellow highlight. I'm running a full monte and it's taking some time - should it take very long?
Edit again: Well it didn't take too long but I don't know how to see the repeatability, it's quite different from your Sheet1.

[hype_hoosier]

Apologies!!

In the Monte Carlo tab of the new file, in Cell P21 and to the right, you will see some calculations. Row 24 is the main focus. I solved for what the VBA is outputting for the Norm.S.Inv(Rnd()) function in the code. If you see, the corresponding cells are very similar (P24 is almost identical to JL24 and so on). This seems to be occurring a bunch. And if you re-run Module2, you will see that this is consistent, where Day 2 and Day 258 are almost the same.

If you run Module 2, it is a slimmed down version of Module 1.

You only need one iteration to see this occur, but Module 2 will run 1000 iterations (takes my computer 9 seconds to run that many).

TIA!!!!

[p45cal]

Try moving the Randomize line out of the loop and putting it before the loop starts. The same applies to everywhere Randomize is within a loop.

[Paul_Hossler]

They are not true random numbers, they are pseudo-random numbers (PRN) and I guess the 'pseudo' depends on many things

I have two functions that I got somewhere (but can't find a reference) that generates an array of better PRN, including a normal distribution version




Function RandomNumberArray(Optional n As Long = 1000, Optional LowerLimit As Double = 0#, Optional UpperLimit As Double = 1#, Optional RoundDecimals As Long = 15) As Variant


Function RandomNormalArray(Optional n As Long = 1000, Optional SampleMean As Double = 0#, Optional SampleSigma As Double = 1#, Optional RoundDecimals As Long = 15) As Variant



29956


The Green are linear, and the yellow are normal distributed. I sorted to get the nice curve

Maybe you could generate an array of PRN and use an index into the array for your simulation

[hype_hoosier]

Ah okay thank you! Didn't realize it was so sensitive but it seems that including it multiple times ends up restricting how random it is.

Thank you so much for all the help!!!