• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Application.WorksheetFunction.Norm.S.Dist

B

bines53

Active Member
Hello friends ,

1)I have VBA code,With Application.WorksheetFunction.NormSDist,Cell G11
I'm trying to do the same code, with Application.WorksheetFunction.Norm.S.Dist Cell H11
NORM.S.DIST Version 2010 and above.

2) I want to change the code, it can be added or subtracted, the argument SDIV ,for example +0.01 or -0.01, That is ,D3:D33+0.01 .

Thank you !
 

Attachments

  • 123.xlsm
    19 KB · Views: 6
Bines

change the lines as per below

Code: 
  N1 = Application.WorksheetFunction.Norm_S_Dist(d1, 0)
  N2 = Application.WorksheetFunction.Norm_S_Dist(d2, 0)

ie: Replace the Dots in the Norm.S.Dist function with Underlines eg: Norm_S_Dist
 
Last edited:
Part 2

Add a new parameter to each Module as required

eg: using XCall7 as an example

Function XCall7(S As Double, Q As Double, r As Double, SDiv As Range, multifly As Double, T As Double, xRange As Range, OptQnt As Range, Optional SDivVar As Double = 0) As Double

Dim d1 As Double
Dim d2 As Double
Dim N1 As Double
Dim N2 As Double
Dim X As Double
Dim Counter1 As Double
Dim XCall_Here As Double

Counter1 = 0
XCall7 = 0
For X = 1 To xRange.Rows.Count

d1 = (Log(S / xRange.Cells(X)) + (r - Q + 0.5 * (SDiv.Cells(X) + SDivVar) * (SDiv.Cells(X) + SDivVar)) * T) / ((SDiv.Cells(X) + SDivVar) * Sqr(T))
d2 = d1 - (SDiv.Cells(X) + SDivVar) * Sqr(T)
N1 = Application.WorksheetFunction.Norm_S_Dist(d1, 0)
N2 = Application.WorksheetFunction.Norm_S_Dist(d2, 0)

Counter1 = Counter1 + 1
XCall_Here = (Exp(-Q * T) * S * N1 - Exp(-r * T) * xRange.Cells(X) * N2) * multifly * OptQnt(Counter1)
XCall7 = XCall7 + XCall_Here
Next
End Function


Then call the function as:
=XCall7(G2,$G$7,$G$6,$D$3:$D$33, $G$1,G5,$B$3:$B$33,$A$3:$A$33, 0.1)
or replace with a cell reference
=XCall7(G2,$G$7,$G$6,$D$3:$D$33, $G$1,G5,$B$3:$B$33,$A$3:$A$33,$G$8)
putting +/- 0.1 in G8

You can leave out the parameter and it will default to 0
=XCall7(G2,$G$7,$G$6,$D$3:$D$33, $G$1,G5,$B$3:$B$33,$A$3:$A$33)
is equivalent to:
=XCall7(G2,$G$7,$G$6,$D$3:$D$33, $G$1,G5,$B$3:$B$33,$A$3:$A$33,0)

you will have to make similar changes in the other Functions

also note, the line for d1 in the above code can be simplified as:
d1 = (Log(S / xRange.Cells(X)) + (r - Q + 0.5 * (SDiv.Cells(X) + SDivVar) ^ 2) * T) / ((SDiv.Cells(X) + SDivVar) * Sqr(T))
 
Last edited:
Hi Hui ,

Should be ,Application.WorksheetFunction.Norm_S_Dist(d1, 1),cumulative distribution.

Thanks for the help !

David
 
Hi Hui ,

Let me ask you one more, if possible, you may have noticed that I attached file, referring to the options market,
Column A call options, and column C, put options,
Call options code,

Function XCall7(S As Double, Q As Double, r As Double, SDiv As Range, multifly As Double, T As Double, xRange As Range, OptQnt As Range, Optional SDivVar As Double = 0) As Double

Dim d1 As Double
Dim d2 As Double
Dim N1 As Double
Dim N2 As Double
Dim X As Double
Dim Counter1 As Double
Dim XCall_Here As Double

Counter1 = 0
XCall7 = 0
For X = 1 To xRange.Rows.Count

d1 = (Log(S / xRange.Cells(X)) + (r - Q + 0.5 * (SDiv.Cells(X) + SDivVar) ^ 2) * T) / ((SDiv.Cells(X) + SDivVar) * Sqr(T))
d2 = d1 - (SDiv.Cells(X) + SDivVar) * Sqr(T)
N1 = WorksheetFunction.Norm_S_Dist(d1, 1)
N2 = WorksheetFunction.Norm_S_Dist(d2, 1)

Counter1 = Counter1 + 1
XCall_Here = (Exp(-Q * T) * S * N1 - Exp(-r * T) * xRange.Cells(X) * N2) * multifly * OptQnt(Counter1)
XCall7 = XCall7 + XCall_Here
Next
End Function

PUT options code,

Function Xput7(S As Double, Q As Double, r As Double, SDiv As Range, multifly As Double, T As Double, xRange As Range, OptQnt As Range, Optional SDivVar As Double = 0) As Double

Dim d1 As Double
Dim d2 As Double
Dim N1 As Double
Dim N2 As Double
Dim X As Double
Dim Counter1 As Double
Dim Xput_Here As Double

Counter1 = 0
Xput7 = 0
For X = 1 To xRange.Rows.Count

d1 = (Log(S / xRange.Cells(X)) + (r - Q + 0.5 * (SDiv.Cells(X) + SDivVar) ^ 2) * T) / ((SDiv.Cells(X) + SDivVar) * Sqr(T))
d2 = d1 - (SDiv.Cells(X) + SDivVar) * Sqr(T)
N1 = WorksheetFunction.Norm_S_Dist(d1, 1)
N2 = WorksheetFunction.Norm_S_Dist(d2, 1)

Counter1 = Counter1 + 1
Xput_Here = (-(S * Exp(-Q * T) * (1 - N1)) + (Exp(-r * T) * xRange.Cells(X) * (1 - N2))) * multifly * OptQnt(Counter1)
Xput7 = Xput7 + Xput_Here
Next
End Function

My request is, can you give me one function (code), which will be included call and put options together.

Thank you !

David
 
Last edited:
Try this:

Code: 
Function XCallPut(S As Double, _
  Q As Double, _
  r As Double, _
  SDiv As Range, _
  multifly As Double, _
  T As Double, _
  xRange As Range, _
  OptQnt As Range, _
  Optional SDivVar As Double = 0, _
  Optional CallPut As String = "Call") As Double

Dim d1 As Double
Dim d2 As Double
Dim N1 As Double
Dim N2 As Double
Dim X As Double
Dim Counter1 As Double
Dim Xput_Here As Double

Counter1 = 0
XCallPut = 0
For X = 1 To xRange.Rows.Count
  If UCase(CallPut) = "CALL" Then
  ' Call Option
  d1 = (Log(S / xRange.Cells(X)) + (r - Q + 0.5 * (SDiv.Cells(X) + SDivVar) ^ 2) * T) / ((SDiv.Cells(X) + SDivVar) * Sqr(T))
  d2 = d1 - (SDiv.Cells(X) + SDivVar) * Sqr(T)
  N1 = WorksheetFunction.Norm_S_Dist(d1, 1)
  N2 = WorksheetFunction.Norm_S_Dist(d2, 1)
  
  Counter1 = Counter1 + 1
  XCall_Here = (Exp(-Q * T) * S * N1 - Exp(-r * T) * xRange.Cells(X) * N2) * multifly * OptQnt(Counter1)
  XCallPut = XCallPut + XCall_Here

  Else
  ' Put Option
  d1 = (Log(S / xRange.Cells(X)) + (r - Q + 0.5 * (SDiv.Cells(X) + SDivVar) ^ 2) * T) / ((SDiv.Cells(X) + SDivVar) * Sqr(T))
  d2 = d1 - (SDiv.Cells(X) + SDivVar) * Sqr(T)
  N1 = WorksheetFunction.Norm_S_Dist(d1, 1)
  N2 = WorksheetFunction.Norm_S_Dist(d2, 1)
  
  Counter1 = Counter1 + 1
  Xput_Here = (-(S * Exp(-Q * T) * (1 - N1)) + (Exp(-r * T) * xRange.Cells(X) * (1 - N2))) * multifly * OptQnt(Counter1)
  XCallPut = XCallPut + Xput_Here
  End If
Next
End Function

To call it use:
=XCallPut(G2,$G$7,$G$6,$D$3:$D$33, $G$1,G5,$B$3:$B$33,$A$3:$A$33,0,"Call" or "Put")

If you leave it out it will default to Call
so
=XCallPut(G2,$G$7,$G$6,$D$3:$D$33, $G$1,G5,$B$3:$B$33,$A$3:$A$33,0)
is the same as

=XCallPut(G2,$G$7,$G$6,$D$3:$D$33, $G$1,G5,$B$3:$B$33,$A$3:$A$33,0,"Call")
 
Hi Hui ,

I mean, added to the A3:A33 and the C3:C33 together,That is, one function calculates, the column of the call(A3:A33), and the column of the put option(C3:C33),Something like that,
=XCallPut(G2,$G$7,$G$6,$D$3:$D$33, $G$1,G5,$B$3:$B$33,$A$3:$A$33,$C$3:$C$33,0)

Thank you !

David
 
Hi Hui ,

I could do that!
Was there something improves code?


Function XCallput(S As Double, Q As Double, r As Double, SDiv As Range, multifly As Double, T As Double, xRange As Range, OptQnt As Range, OptQnt1 As Range, Optional SDivVar As Double = 0) As Double

Dim d1 As Double
Dim d2 As Double
Dim N1 As Double
Dim N2 As Double
Dim X As Double
Dim Counter1 As Double
Dim Counter2 As Double
Dim XCall_Here As Double
Dim Xput_Here As Double

Counter1 = 0
Counter2 = 0
XCallput = 0
For X = 1 To xRange.Rows.Count

d1 = (Log(S / xRange.Cells(X)) + (r - Q + 0.5 * (SDiv.Cells(X) + SDivVar) ^ 2) * T) / ((SDiv.Cells(X) + SDivVar) * Sqr(T))
d2 = d1 - (SDiv.Cells(X) + SDivVar) * Sqr(T)
N1 = WorksheetFunction.Norm_S_Dist(d1, 1)
N2 = WorksheetFunction.Norm_S_Dist(d2, 1)

Counter1 = Counter1 + 1
XCall_Here = (Exp(-Q * T) * S * N1 - Exp(-r * T) * xRange.Cells(X) * N2) * multifly * OptQnt(Counter1)

Counter2 = Counter2 + 1
Xput_Here = (-(S * Exp(-Q * T) * (1 - N1)) + (Exp(-r * T) * xRange.Cells(X) * (1 - N2))) * multifly * OptQnt1(Counter2)

XCallput = XCallput + XCall_Here + Xput_Here
Next
End Function

Thank you !

David
 
You must log in or register to reply here.
Back
Top