VBA - Finance - MonteCarlo

[Jessie20]

Hi, I need help with my vba code. I know this is a little specific, but if anyone can help it would be amazing.

I need to consider the random market model as a description of the temporal evolution of the price (which would be in log) of a financial asset:

p t = p t-1 + ε t , t = 2, . . . , T,


with p 1 = 0 and where the ε t are N(0, 1) independent variables.

I tried to write a function (Function) that receives as input a value of T and returns a Monte-Carlo estimate of the expected number of times that the price crosses z'ero (i.e. the expected number of times that { p t-1 < 0 and p t > 0 } or { p t-1 > 0 and p t < 0 } ) 'given the value of T. I need to use at least 1000 Monte-Carlo replications. This is the code I wrote, but when I try to test it with a sub procedure, its not working. Can someone help me please?

Function Question3(tTime As Integer)

Dim tTime As Integer
tTime = T
Dim priceStock(1 To T) As Double
priceStock = pS
Dim ePs As Variant
Dim Index As Integer
Dim Succes As Integer

pS(1) = 0
ePs = Application.NormSInv(Rnd)
pS(T) = pS(T - 1) + ePs(t)

For Index = 1 To 10000
    For T = 2 To T
    Randomize
       
    If pS(1) < 0 Then Succes = Succes + 1
    If pS(T - 1) > 0 Then Succes = Succes + 1
       
        Else
            pS(T) = 0
       
        End If
       
        Next T
    Next Index
       

End Function

 

[6StringJazzer]

What does "not working" mean? What do you expect to happen and what happens instead?

 

[Jessie20]

What does "not working" mean? What do you expect to happen and what happens instead?

I explained what I was trying to achieve with my code: I tried to write a function (Function) that receives as input a value of T and returns a Monte-Carlo estimate of the expected number of times that the price crosses z'ero (i.e. the expected number of times that { p t-1 < 0 and p t > 0 } or { p t-1 > 0 and p t < 0 } ) 'given the value of T. I need to use at least 1000 Monte-Carlo replications.

When I try to run my sub to try my function, this is the message I get :
Sub or Function not defined

 

[6StringJazzer]

I understand what you are trying to achieve conceptually but not in concrete terms what a correct result looks like.

I see a couple of issues. They should be addressed but I don't know if they are causing the problem you are reporting. What line of code is highlighted when the error occurs?

Function Question3(tTime As Integer)

Dim tTime As Integer

tTime is a parameter for your function but you are also declaring it again as a local variable. That should cause a compile error that prevents the code from running.

tTime = T

Then you assign T to it. But T is not declared, and it's not defined. If you are using Option Explicit then this will not compile. If you are not, then T will default to be Variant and have a value of 0 at this point.

priceStock = pS

pS is not declared or defined. This line will cause an error. If pS is declared somewhere outside if this procedure it would be a better practice to pass it as an argument rather than depending on global data.

This is a Function so its purpose should be to return a value. However, Question3 is never assigned a value so this function always return 0. If you are not using it to return a value, then it should be declared as a Sub instead of a Function.

 

[Jessie20]

Oh! I think I improved it by following your recommendation if I understood it well. I think I can't call my function. But if you could tell me if what I changed is better?

Thanks!

Function Question3(t As Integer, p As Double, ePs As Variant)



Question3 = p(t)

Dim p(1 To t) As Double
Dim Index As Integer
Dim Succes As Integer

p(1) = 0
ePs = Application.NormSInv(Rnd)
p(t) = p(t - 1) + ePs(t)

For Index = 1 To 10000
    For t = 2 To t
    Randomize
        
    If p(1) < 0 Then Succes = Succes + 1
    If p(t - 1) > 0 Then Succes = Succes + 1
        
        Else
            p(t) = 0
        
        End If
        
        Next t
    Next Index
        

End Function

Sub Question3(t As Integer, p As Double, ePs As Variant)


Msg.Box p(t)

End Sub

 

[6StringJazzer]

I don't understand this revision.

You now have both a Function and a Sub called Question3. This will not compile.

The Function had one parameter, now it has three. I'm not sure why that changed.

The Sub refers to an array p that you declared in the Function. If this compiles, it is a bug.

You originally said that your function

receives as input a value of T and returns a Monte-Carlo estimate

but I don't see where the function returns this. It immediately sets the return value to p(t) then none of the other work affects what is returned.

I am also thinking your logic does not match your description

expected number of times that the price crosses z'ero

that is not what the code does.

I think what I can do to help you is re-read your original description of what you want, and just rewrite your code. That may take a little time.