0

Goodmorning. I'm new here. Before posting I've searched similar questions, but I found something similar only with different programming language, and however the question found were incomplete.

Ok, my problem is to find a VBA code which, once recalled in Excel, it requires from me only the Geometric Brownian Motions (GBM) parameters (initial stock value s, maturity t, volatility z, risk-free rate r, dividend q, number of steps n), the number m of trajectories I need to reproduce. As a output I don't want to see no numbers, only one plot of the superimposed m trajectories.

The problem is that, I can't write it directly cell by cell (then carrying) because in this way it would be too complex from a computational power point of view; it would allow to make something like 15 trajectories, not much more. Thus the solution is to work in VBA.

My initial step was

Function GBMSimulation(s As Double, t As Double, z As Double, r As Double, q As Double, n As Double) As Variant
Dim dt, e, dlns, SimVar() As Double
ReDim SimVar(n + 1)
dt = t / n
SimVar(0) = s
For i = 1 To n
Randomize
    e = WorksheetFunction.NormSInv(Rnd())
    dlns = (r - q - z ^ 2 / 2) * dt + z * e * dt ^ 0.5
        SimVar(i) = SimVar(i - 1) * Exp(dlns)
Next i
GBMSimulation = SimVar(n)
End Function

This code works; I mean, it gives a number, which is the n-th step of ONE gbm.

Changing

GBMSimulation = SimVar(n)

with

GBMSimulation = SimVar(i)

I would obtain the i-th step of that particular gbm.

Thus, all the steps are stored in memory and this is good.

My problem is: how can I create m different realizations of a GBM (i.e. to repeat my previous step m times, m given as an input) and for each one of them create automatically a plot?

I think some for cycle should be taken into account, but I'm a beginner and I don't know how to do this.

I thought I could create an n-dimensional array in which store the GBM steps, plotting them, and repeat this m times; the problem is that I miss the language, I'm learning it, but it require time, and some help could be useful.

ONLY FROM GRAPHICAL POINT OF VIEW, the final result should be like this (I specified the graphical pov because in the link there are numbers, and all the work is made cell by cell, and as I said before I need to avoid this).

Many thanks.

UPDATE: my problem was partially solved thank to Tehscript. The code he/she kindly provided, allows to store the n steps of each of the m GBMs in an array of arrays. Thus remains one last step in order to solve my problem: how can I plot these datas?

Community
  • 1
  • 1
Joe
  • 121
  • 2
  • 6
  • You can have the function return an array and use this function to have array of arrays. – Tehscript May 03 '17 at 15:37
  • @Tehscript: thanks for the hint; however I'm really a beginner, can I ask you gently to tell me a little more please? Thanks – Joe May 03 '17 at 15:57
  • I don't really know what numbers to use so I used 2 for all of them – Tehscript May 03 '17 at 16:00
  • If you want those s, t, z, r, q to vary, you can do it the same way n and m vary. – Tehscript May 03 '17 at 16:07
  • I want the parameters I've listed in the post to be inserted in Excel by a user, not to be fixed in the code; I'm confused because in your code you fix n and m, but I need to insert them in excel (as the other parameters: the final output should be only the plot of the trajectories, once inserted the parameters). – Joe May 03 '17 at 16:21
  • Do you want to print this output in excel cells? or do you want to get m and n value from excel cells? – Tehscript May 03 '17 at 16:23
  • I want to recall my VBA function in Excel, giving it all the parameters listed above (s,t,z,r,q,n,m) and the result should be one plot, of the m GBM trajectories. For example, you wrote "I don't really know what numbers to use so I used 2 for all of them", but those numbers are to be inserted in excel AFTER the function is defined. I don't need at all to print in excel the relative datas. Thanks again! – Joe May 03 '17 at 16:27
  • But once this is done, it seems the result is an array of array in which all my n steps of each of the m GBM is stored. And this is great. The final step will be to plot them correctly – Joe May 03 '17 at 16:34

1 Answers1

2

Inside your function at the end, define your function as array

GBMSimulation = SimVar()

Then use the following code and have your function to create another array.

Edit: You can input your variables from Cells A1, B1, C1, .. and if you uncomment below, you will have these results in your excel, just like your excel. You can make a chart as well. Also if you want to loop 0 to 1 by 0.01 (100(101?) times total) you can use step 0.01 For example: for i = 0 To 1 step 0.01 (i should be double - ps:try declaring it as long and see what happens:)

Sub ArrayofArrays()
Dim i As Long, j As Long, m As Long, n As Double, s As Double, t As Double, z As Double, r As Double, q As Double
With Sheets("Sheet1")
    m = .Range("A1").Value
    n = .Range("B1").Value
    s = .Range("C1").Value
    t = .Range("D1").Value
    z = .Range("E1").Value
    r = .Range("F1").Value
    q = .Range("G1").Value
End With
ReDim Arr(m)
For i = 0 To m
    Arr(i) = GBMSimulation(s, t, z, r, q, n)
Next i
For i = 0 To m
    For j = 0 To n
        Debug.Print Arr(i)(j)
        'Cells(i + 2, j + 1) = Arr(i)(j)
    Next j
Next i
End Sub
Tehscript
  • 2,556
  • 2
  • 13
  • 23
  • Ok I did this. As I told you, I'm a beginner, so I ask you gently to be patient. It seems that, the final result of this code is to store (via the Debug.Print command) all the GBM steps, for each trajectory. From this, I read that I can recover these datas by using the Immediate Window, but opening it, nothing appears. So at this point, how can I use my data to get a plot? – Joe May 03 '17 at 17:39
  • Debug.print is the command to display the output. The final result is stored in Arr() 2 dimensinonal array. Like this: Arr((a1, a2, a3....),(b1, b2, b3....),and so on...). If you want to display a1, it is Arr(0)(0). I you want to display b3, it is Arr(1)(2). Notice that counting starts from 0, not 1. Using 2 nested for loops you can display all outputs by Arr(i)(j). By using this output, you can display them in cells, plot them in chart, whatever you want. – Tehscript May 04 '17 at 09:34
  • Ok (and 1thousand thanks again) but if I go to Excel and i write in a cell =Arr(0)(0) it doesn't give nothing. How can this be possibile? – Joe May 04 '17 at 15:56
  • You can use `Cells(1,1) = Arr(0)(0)` to have output to A1 which is first row and first column. I.e., `Cells(2,3)` is second row, third column which is C2. If you uncomment the comment in above code like this `Cells(i + 2, j + 1) = Arr(i)(j)` (by deleting apostrophe ->') you will see the output in excel cells, and then you can figure out how. – Tehscript May 04 '17 at 16:11
  • You will write it in the code inside the editor, not in cell. Do you need this to work as a function in excel? – Tehscript May 04 '17 at 16:14