I have a following table:
<TABLE style="WIDTH: 287pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=382 border=0><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 81pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=108 height=20>Dates</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 55pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=73>Forward</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 48pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>3</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 48pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>6</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 55pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=73>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=20>10.9.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0,8685</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1,1585</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1,449</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=20>15.9.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Interpolate</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=20>22.9.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">Interpolate</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=20>30.9.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Interpolate</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=20>10.10.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>2</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0,9515</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1,198</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1,532</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=20>14.10.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Interpolate</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=20>22.10.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">Interpolate</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=20>10.11.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>3</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>1,0055</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>1,276</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>1,632</TD></TR></TBODY></TABLE>
and I have forward rate quotes for every months 10th day. I would like to get interpolated rates for the cell's that have N/A (because the table couldn't find the quotes)
to get the interpolated rates I have following VBA code:
'Linear Interpolation (e.g. of interest rates or volatilities): x_v = Datevector,y_v = valuevector, X = date
Function inter2(x_v As Object, y_v As Object, X As Variant) As Double
Dim n As Long, ind As Long, i As Long
n = x_v.Rows.Count
If X < x_v(1) Then
inter2 = y_v(1)
ElseIf X > x_v
Then
inter2 = y_v
Else
For i = 1 To n
If x_v(i) <= X Then
ind = ind + 1
Else
ind = ind
End If
Next
Dim X1 As Variant, X2 As Variant, Y1 As Double, Y2 As Double
X1 = x_v(ind)
X2 = x_v(ind + 1)
Y1 = y_v(ind)
Y2 = y_v(ind + 1)
inter2 = (Y1 * (X2 - X) + Y2 * (X - X1)) / (X2 - X1)
End If
End Function
The problem is that the interpolation code needs the argument as follows:
<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=194 border=0><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl615 style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #4f81bd; WIDTH: 74pt; COLOR: white; BORDER-TOP-COLOR: #ece9d8; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=98 height=21>Date</TD><TD style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #4f81bd; WIDTH: 72pt; COLOR: white; BORDER-TOP-COLOR: #ece9d8; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=96>Rate</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl615 style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #b8cce4; COLOR: black; BORDER-TOP-COLOR: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=21>10.8.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #b8cce4; COLOR: black; BORDER-TOP-COLOR: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0,881</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl615 style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #dbe5f1; BORDER-BOTTOM-COLOR: #ece9d8; COLOR: black; BORDER-TOP-COLOR: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 15.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=21>10.9.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #dbe5f1; BORDER-BOTTOM-COLOR: #ece9d8; COLOR: black; BORDER-TOP-COLOR: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>0,8685</TD></TR></TBODY></TABLE>
x_v=Date column
y_v=Rate column
How can I get an object like this??
So if I want to get the interpolated rate at 22.9.2009 for 3 month tenor how can I form the date vector and value vector for my inter2 function.
How can I say to Excel to pick the closest date that is before 22.9.2009 and closest date that is after 22.9.2009 and let those two to form the date vector..?? And the same for the rates..
I know this might a bit tricky to solve (at least it is for me) but any ideas would be appreciated! Thanks!
-Jack <!-- / message -->
<TABLE style="WIDTH: 287pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=382 border=0><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 81pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=108 height=20>Dates</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 55pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=73>Forward</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 48pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>3</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 48pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>6</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 55pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=73>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=20>10.9.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0,8685</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1,1585</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1,449</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=20>15.9.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Interpolate</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=20>22.9.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">Interpolate</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=20>30.9.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Interpolate</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=20>10.10.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>2</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0,9515</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1,198</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1,532</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=20>14.10.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Interpolate</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=20>22.10.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">Interpolate</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl617 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=20>10.11.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>3</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>1,0055</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>1,276</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>1,632</TD></TR></TBODY></TABLE>
and I have forward rate quotes for every months 10th day. I would like to get interpolated rates for the cell's that have N/A (because the table couldn't find the quotes)
to get the interpolated rates I have following VBA code:
'Linear Interpolation (e.g. of interest rates or volatilities): x_v = Datevector,y_v = valuevector, X = date
Function inter2(x_v As Object, y_v As Object, X As Variant) As Double
Dim n As Long, ind As Long, i As Long
n = x_v.Rows.Count
If X < x_v(1) Then
inter2 = y_v(1)
ElseIf X > x_v
Theninter2 = y_v
Else
For i = 1 To n
If x_v(i) <= X Then
ind = ind + 1
Else
ind = ind
End If
Next
Dim X1 As Variant, X2 As Variant, Y1 As Double, Y2 As Double
X1 = x_v(ind)
X2 = x_v(ind + 1)
Y1 = y_v(ind)
Y2 = y_v(ind + 1)
inter2 = (Y1 * (X2 - X) + Y2 * (X - X1)) / (X2 - X1)
End If
End Function
The problem is that the interpolation code needs the argument as follows:
<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=194 border=0><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl615 style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #4f81bd; WIDTH: 74pt; COLOR: white; BORDER-TOP-COLOR: #ece9d8; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=98 height=21>Date</TD><TD style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #4f81bd; WIDTH: 72pt; COLOR: white; BORDER-TOP-COLOR: #ece9d8; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=96>Rate</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl615 style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #b8cce4; COLOR: black; BORDER-TOP-COLOR: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=21>10.8.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #b8cce4; COLOR: black; BORDER-TOP-COLOR: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0,881</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl615 style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #dbe5f1; BORDER-BOTTOM-COLOR: #ece9d8; COLOR: black; BORDER-TOP-COLOR: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 15.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=21>10.9.2009</TD><TD style="BORDER-RIGHT: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: #ece9d8; BACKGROUND: #dbe5f1; BORDER-BOTTOM-COLOR: #ece9d8; COLOR: black; BORDER-TOP-COLOR: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>0,8685</TD></TR></TBODY></TABLE>
x_v=Date column
y_v=Rate column
How can I get an object like this??
So if I want to get the interpolated rate at 22.9.2009 for 3 month tenor how can I form the date vector and value vector for my inter2 function.
How can I say to Excel to pick the closest date that is before 22.9.2009 and closest date that is after 22.9.2009 and let those two to form the date vector..?? And the same for the rates..
I know this might a bit tricky to solve (at least it is for me) but any ideas would be appreciated! Thanks!
-Jack <!-- / message -->
