D
deanop
table in excel file is as follows:
col b col c col d
Black-Scholes Directly in a Excel Sheet
Stock price S 61
Strike price X 65
Years to maturity T 0.25
Risk-free rate r 0.08
Volatility v 0.3
d1 -0.215
d2 -0.365
European call value 2.527
European put value 5.240
here is code to write this table to a txt file:
Sub writetabletotxtfile()
Dim ExpRng As Range
Dim ff As Integer
Set ExpRng = Range("worksheet_to_text")
'Set ExpRng = ActiveCell.CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count
FirstRow = ExpRng.Rows(1).Row
LastRow = FirstRow + ExpRng.Rows.Count
ff = FreeFile()
Open "C:\Documents and Settings\xyz\Desktop\tabletotextfile.txt" For
Output As ff
Print #ff, ExpRng.AddressLocal()
Print #ff, ExpRng.AddressLocal(RowAbsolute:=False,
columnabsolute:=False)
For r = FirstRow To LastRow
For c = FirstCol To LastCol
vdata = ExpRng.Cells(r, c).Text
If c <> LastCol Then
Print #ff, vdata; Chr(44);
Else
Print #ff, vdata
End If
Next c
Next r
Close ff
End Sub
here is txt file produced when running this code:
$B$2:$E$18
B2:E18
,,,,
,,,,
S,61,,,
X,65,,,
T,0.25,,,
r,0.08,,,
v,0.3,,,
,,,,
d1,-0.215,,,
d2,-0.365,,,
,,,,
,2.527,,,
,5.240,,,
,,,,
,,,,
,,,,
,,,,
,,,,
could u help me write this table retaining table spacing and
formatting.
thanks folks, deano
col b col c col d
Black-Scholes Directly in a Excel Sheet
Stock price S 61
Strike price X 65
Years to maturity T 0.25
Risk-free rate r 0.08
Volatility v 0.3
d1 -0.215
d2 -0.365
European call value 2.527
European put value 5.240
here is code to write this table to a txt file:
Sub writetabletotxtfile()
Dim ExpRng As Range
Dim ff As Integer
Set ExpRng = Range("worksheet_to_text")
'Set ExpRng = ActiveCell.CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count
FirstRow = ExpRng.Rows(1).Row
LastRow = FirstRow + ExpRng.Rows.Count
ff = FreeFile()
Open "C:\Documents and Settings\xyz\Desktop\tabletotextfile.txt" For
Output As ff
Print #ff, ExpRng.AddressLocal()
Print #ff, ExpRng.AddressLocal(RowAbsolute:=False,
columnabsolute:=False)
For r = FirstRow To LastRow
For c = FirstCol To LastCol
vdata = ExpRng.Cells(r, c).Text
If c <> LastCol Then
Print #ff, vdata; Chr(44);
Else
Print #ff, vdata
End If
Next c
Next r
Close ff
End Sub
here is txt file produced when running this code:
$B$2:$E$18
B2:E18
,,,,
,,,,
S,61,,,
X,65,,,
T,0.25,,,
r,0.08,,,
v,0.3,,,
,,,,
d1,-0.215,,,
d2,-0.365,,,
,,,,
,2.527,,,
,5.240,,,
,,,,
,,,,
,,,,
,,,,
,,,,
could u help me write this table retaining table spacing and
formatting.
thanks folks, deano