Binomial Tree

E

ExcelMonkey

Does anybody know what the best way to build a binomial tree in VBA is?
I can build it in excel using cells that branch out, but I am wonderin
if there is an efficient way to built it in code as well. Using a VB
array seems difficult as not all the elements would be used due to th
tree branching structure. Any ideas
 
P

Peter Atherton

Hi Monkey

Sorry about the delay. Is this what you are after?
Sub Binomial()
Dim n As Integer, Comb As Integer
Dim i As Integer, j As Integer, nc As Integer
n = 10
With ActiveSheet
.Cells.Clear
End With
Application.ScreenUpdating = False
On Error Resume Next
For i = 1 To n
For j = 1 To n
Comb = Application.WorksheetFunction.Combin(i, j)
Cells(i, j) = Comb
Next j
Next i

' get rid of unwanted "1s"
For i = 1 To n - 1
Range(Cells(i, i + 1), Cells(i, n)).ClearContents
Next i
' add the first column
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range(Cells(1, 1), Cells(n, 1)) = 1
Range(Cells(1, 1), Cells(n, n)).ColumnWidth = 5
Range("A1").Select
Range("B1").ClearContents
Application.ScreenUpdating = True
End Sub

Regards
Peter
 
K

KJTFS

I have a binomial tree program on my website. www.kjtfs.com

You just have to think of the looping theory to make it branch out
correctly, think of the math of it, it depends on how you want to show
it, all together or in a fancy little pyramid scheme.

Keith
www.kjtfs.com
 
E

ExcelMonkey

Yes I actually downloaded your software and tried it. Nice job!
ultimately want to do it in code and dump to a spreadsheet as well.
Just haven't figured out how to do it yet
 
K

KJTFS

Here is some of the code for the program might help you figure out th
looping logic. You might be able to rip something out of there, I a
pretty sure it will look crappy due to the formating the forum does.

intStartRow = intNumPeriods + 2

For i = 2 To (intNumPeriods + 1)
For j = (intStartRow - (i - 1)) To (intStartRow + (i - 1)) Ste
2
FutureS.Cells(j, i).Interior.ColorIndex = YellowCI
FutureS.Cells(j, i).Font.Bold = True
OptionS.Cells(j, i).Interior.ColorIndex = YellowCI
OptionS.Cells(j, i).Font.Bold = True
''Make Formula for cells
''Futures
If (j = (intStartRow - (i - 1))) Then
FutureS.Cells(j, i).Formula = "=round((R[1]C[-1] * u_
, 4)"
Else
FutureS.Cells(j, i).Formula = "=round((R[-1]C[-1] * d_
, 4)"
End If
''End Futures
''Options
If (i = (intNumPeriods + 1)) Then
If (MainS.Range("OptionType").Value = "Call") Then
OptionS.Cells(j, i).Formula = "=max(0, '"
Trim(FutureS.Name) & "'!RC - K_)"
Else
OptionS.Cells(j, i).Formula = "=max(0, K_ - '"
Trim(FutureS.Name) & "'!RC)"
End If
Else
OptionS.Cells(j, i).Formula = "= round(((p_
R[-1]C[1]) + ( (1 - p_) * R[1]C[1]))/rp_ , 4)"
End If
''End Options
''End Make Formula for cells



Next j
Next
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top