PDA

View Full Version : Three Dimensional Linear Interpolation UDF



Red Salerno
10-17-2013, 02:07 PM
Hello Friends

I've found online UDF for linear interpolation....my project is a little for complex and I could use some help; perhaps someone has some code.

I have several historical yield curve all with bond maturities and associated yields. I'm looking for a UDF that has two inputs:
Input 1 - Is the historical yield curve date on which to interpolate
Input 2 - Is the date to interpolate to along the historical yield curve

The result is a interpolated yield output to a specified date for a specified curve.

As a kicker there are blanks along the curve as new bonds are added into the bond universe.

Perhaps a spreadsheet will help...as attached.

Can anyone help ...please

p45cal
10-18-2013, 06:28 AM
There is no 31 Sep 2014, so calculating for 30th Sep 2014 instead, are you expecting an answer of 1.046278689?
If not, what is the expected answer, so that I can try to fathom how you want it worked out.

Red Salerno
10-20-2013, 03:44 PM
My Bad.....yes the date is SEPT 30th, 2014....and YES the expected anwser is 1.046278689. Are you able to help

Red Salerno
10-20-2013, 03:54 PM
10731

p45cal
10-21-2013, 01:41 PM
see attached.

Red Salerno
10-21-2013, 04:51 PM
Hello Pascal / p45cal ...thanks for the input so far. And the answer is correct. I'm trying to put those calculations into a VBA User Defined Formula and if possible without using the excel add-in Trend.

Is that possible?

p45cal
10-21-2013, 05:31 PM
I'm 98% sure that TREND is not an add-in, neither for excel worksheets, nor for VBA, so why avoid using it?

However because these calculations use linear interpolatation between only 2 points, plain arithmetic will do the job; either of these will give the new value of Y (aka NewY using the same notation as below):
=((NewX-knownX1)*(knownY2-knownY1))/(knownX2-knownX1)+knownY1

=(knownY2-knownY1)/(knownX2-knownX1)*(NewX-knownX1)+knownY1


Regarding creating a udf, what arguments would you want it to take? At the moment, my formula-only solution uses several helper cells, taking/working out several values from the sheet, including taking care of gaps in the data, how much of that do you want to be done by the udf. In essence, we need a clear specification.

Red Salerno
10-22-2013, 06:31 AM
I would like the UDF to have three auguments/inputs:
1) the date of the historical yield curve - ie in this example Oct 4th, 2013
Range(F7:F24)
2) the date to interpolate to along the historical yield curve ie in this example Sept 30th, 2014
Range(G6:Q6) known Xs
Range(G20:Q20) known Ys ....based on augument Range(f7:F24)

The UDF gets tricky because of the gaps in the matrix, there are is no data for the Nov 1st, 2014 bond after historical yield curve Oct 10th, 2013 so the UDF will have to jump to the historical data of the Dec 1, 2014 bond to calculate the interpolation.

I am new to VBA programing myself ....but I have been trying to learn how to figure this one out. I thought if I could see the coding using plain arithmetic calculations I could then add another augument for exponential and cubic spline interpolation; hence my desire to stay away from Trend()

p45cal
10-22-2013, 03:00 PM
See attached where formula is in H1.
Check it gives the right answers, I haven't.
It's not very elegant - I wrote it before I planned it.
I've stuck with TREND in the code but there's also a commented-out line, just before the last line, which calculates the value using arithmetic, BUT, I think it will still be easier to use TREND because in Help:
"You can use TREND for polynomial curve fitting by regressing against the same variable raised to different powers. For example, suppose column A contains y-values and column B contains x-values. You can enter x^2 in column C, x^3 in column D, and so on, and then regress columns B through D against column A."

The function is used:
=interpol(HYCDate,NewDate,TableTLC)
where:
HYCDate means the cell containing the Historical Yield Curve Date
NewDate means the interpolation date
TableTLC means the Top Left Corner of the Table (F6 on the sample sheet).

eg.:
=interpol(G2,G3,F6)

Red Salerno
10-23-2013, 11:41 AM
Pascal

The formula works as required; but it is seems rather finicky. It does not work properly if the inputs for HYCDate and NewDate are on a different worksheet than the TableTLC ...why would that be?? - The inputs after F9ing the formula look correct.

Is it possible to extrapolate using the function?

I am truly indebted for the input you've provided Pascal. This has been a really big problem and a great learning experience for me. My goal is to learn VBA to YOUR LEVEL and in the future pay it forward.

p45cal
10-23-2013, 01:18 PM
It does not work properly if the inputs for HYCDate and NewDate are on a different worksheet than the TableTLC ...why would that be?? - Because I was in a hurry.
Try adding the bits in red including the three red dots:

With TableTLC.Parent
For Each cll In HYCYields.Cells
If IsNumeric(cll.Value) And Len(cll.Value) > 0 Then
Select Case .Cells(TableTLC.Row, cll.Column)
Case Is = NewDate
interpol = cll.Value
Exit Function
Case Is < NewDate
KnownY1 = cll.Value
KnownX1 = .Cells(TableTLC.Row, cll.Column).Value
Case Is > NewDate
KnownY2 = cll.Value
KnownX2 = .Cells(TableTLC.Row, cll.Column).Value
Exit For
End Select
End If
Next cll
End With
(Can't use code tags as they don't show the colours)




Is it possible to extrapolate using the function?Yes.

Red Salerno
10-28-2013, 05:35 AM
P45cal

Thanks for the help with this coding. Your help is GREATLY appreciated.

Red

Red Salerno
11-23-2013, 06:00 PM
I've used this function to get thru some large calculation and it has been GREAT.

However,

It was indicated that you could extrapolate with the trend function. I.E. so on the attached spreadsheet I thought you could put in a date for 06/15/2015 on yield curve date 10/04/2013 and trend would extrapolate to the 06/15/2015 date. It does not work.

Anyone have any thoughts????

Red10862

p45cal
11-24-2013, 06:45 PM
Using the TREND function on the sheet would be difficult; I haven't tried.
Using a UDF however, the following needs to be tested ; it was the first bit of code cobbled together which seems to give the right results with some cursory testing. I've not made any attempt to make the code elegant/robust. It uses only the first 2 values before/after the date to extrapolate to:
Function interpol(HYCDate, NewDate, TableTLC As Range)
Application.Volatile
Dim HYCDateCell As Range, OFST As Long, matdates As Range, YCDates As Range, HYCYields As Range, cll As Range
Dim KnownY1, KnownY2, KnownX1, KnownX2, OldY1, NextY2, OldX1, NextX2, FirstLaterDateFound As Boolean
OFST = 1
Do Until Len(TableTLC.Offset(, OFST).Value) = 0
OFST = OFST + 1
Loop
Set matdates = TableTLC.Offset(, 1).Resize(, OFST - 1)
OFST = 1
Do Until Len(TableTLC.Offset(OFST).Value) = 0
If TableTLC.Offset(OFST).Value = HYCDate Then
Set HYCDateCell = TableTLC.Offset(OFST)
End If
OFST = OFST + 1
Loop
Set YCDates = TableTLC.Offset(1).Resize(OFST - 1)
Set HYCYields = HYCDateCell.Offset(, 1).Resize(, matdates.Cells.Count)
With TableTLC.Parent
For Each cll In HYCYields.Cells
If IsNumeric(cll.Value) And Len(cll.Value) > 0 Then
Select Case .Cells(TableTLC.Row, cll.Column)
Case Is = NewDate
interpol = cll.Value
Exit Function
Case Is < NewDate
OldY1 = KnownY1: OldX1 = KnownX1
KnownY1 = cll.Value
KnownX1 = .Cells(TableTLC.Row, cll.Column).Value
Case Is > NewDate
If FirstLaterDateFound Then
NextY2 = cll.Value
NextX2 = .Cells(TableTLC.Row, cll.Column).Value
Else
KnownY2 = cll.Value
KnownX2 = .Cells(TableTLC.Row, cll.Column).Value
End If
If FirstLaterDateFound Then Exit For
FirstLaterDateFound = True
End Select
End If
Next cll
End With
If IsEmpty(KnownY2) Then
KnownY2 = OldY1
KnownX2 = OldX1
End If
If IsEmpty(KnownY1) Then
KnownY1 = NextY2
KnownX1 = NextX2
End If
interpol = Application.WorksheetFunction.Trend(Array(KnownY1, KnownY2), Array(CLng(KnownX1), CLng(KnownX2)), CLng(NewDate.Value))
End FunctionIf you have a lot of interpol formulae on the sheet the Application.Volatile line may slow things up.

p45cal
01-27-2014, 02:28 PM
I respond to your private message here because my response there was rejected because it was too long:

I think the first code only did interpolation, the subsequent code did extrapolation too.
Also I'm not sure whether I had Application.Volatile in the first code or not. This might slow it up.

See comments in the code below.


Function interpol(HYCDate, NewDate, TableTLC As Range)
Application.Volatile 'I think this is the line that slows things down, but your values won't update properly without it (Normally, cells are recalculated automatically if the values in their formula change, however, this function uses cell values which are not in the formula itself; Application.volatile means the formula gets recalculated more frequently).
Dim HYCDateCell As Range, OFST As Long, matdates As Range, YCDates As Range, HYCYields As Range, cll As Range
Dim KnownY1, KnownY2, KnownX1, KnownX2, OldY1, NextY2, OldX1, NextX2, FirstLaterDateFound As Boolean

'First determine where things are so we know where to get values from:
'the following block sets matdates to a range being the top row of dates at the top of the table:
OFST = 1
Do Until Len(TableTLC.Offset(, OFST).Value) = 0
OFST = OFST + 1
Loop
Set matdates = TableTLC.Offset(, 1).Resize(, OFST - 1)
'the following block sets YCDates to a range being the left column of date headers at the left of the table:
OFST = 1
Do Until Len(TableTLC.Offset(OFST).Value) = 0
'If statement to determine row of data to use for extrapolation (instead of doing this in a separate loop):
If TableTLC.Offset(OFST).Value = HYCDate Then
Set HYCDateCell = TableTLC.Offset(OFST)
End If
OFST = OFST + 1
Loop
Set YCDates = TableTLC.Offset(1).Resize(OFST - 1)
'next line sets HYCYields to be the row of data (without row header) from HYCDateCell:
Set HYCYields = HYCDateCell.Offset(, 1).Resize(, matdates.Cells.Count)

'Second, look for values to use in the TREND formula at the end:
With TableTLC.Parent 'Parent identifies the sheet on which the table is.
For Each cll In HYCYields.Cells 'this loop runs through the cells on that one row
If IsNumeric(cll.Value) And Len(cll.Value) > 0 Then 'if the cell has a number in it process it, otherwise move on to the next cll.
Select Case .Cells(TableTLC.Row, cll.Column) 'the date in the header row above the cells being looped through.
Case Is = NewDate '(if the value has the same value as he supplied 2nd argument NewDate then..)
interpol = cll.Value 'no interpolation needed, the value already exists
Exit Function 'no more processing required at all.
Case Is < NewDate '(if the date is earlier than the supplied 2nd argument NewDate then.. (we're looking for the date with a valid value just before the interpolation date))
OldY1 = KnownY1: OldX1 = KnownX1 'this keeps a note of the previous values of KnownY1 and KnownX1 in case later knownY2 is empty.
KnownY1 = cll.Value 'the value in the cell. This is the only line in this section where KnownY1 is assigned a value; it might never be executed if there is no valid value for a date BEFORE the interpolation date (NewDate).
KnownX1 = .Cells(TableTLC.Row, cll.Column).Value 'the date in the header row of dates above the cell cll.
Case Is > NewDate 'if the date is after the interpolation date
'We want the next If statement block to be executed no more than twice.
If FirstLaterDateFound Then
'we want the next 2 lines to be executed no more than once.
NextY2 = cll.Value
NextX2 = .Cells(TableTLC.Row, cll.Column).Value
Else
'we want the next 2 lines to be executed no more than once.
KnownY2 = cll.Value ' this is the only line in this section where KnownY2 is assigned a value; it might never be executed if there is no valid value for a date AFTER the interpolation date (NewDate).
KnownX2 = .Cells(TableTLC.Row, cll.Column).Value
End If
If FirstLaterDateFound Then Exit For ' we've finished getting all the values so quit looking.
FirstLaterDateFound = True 'we've found one date with a valid value after the interpolation date, we'll grab the next pair in case we need to do a backward extrapolation.
End Select
End If
Next cll
End With

'Now to examine what we've got:
If IsEmpty(KnownY2) Then 'KnownY2 can be empty if it has never been assigned a value
KnownY2 = OldY1
KnownX2 = OldX1
End If
If IsEmpty(KnownY1) Then 'KnownY1 can be empty if it has never been assigned a value
KnownY1 = NextY2
KnownX1 = NextX2
End If
'The function hinges on having values for these 5 arguments supplied to the TREND function:
'NewDate, supplied as an argument being the interpolation date
'KnownX1, the date just before the interpolation date with a valid value.
'KnownX2. the date just after the interpolation date with a valid value.
'KnownY1, the value in the row under the date in KnownX1.
'KnownY2, the value in the row under the date in KnownX2.

'We have to cater for several scenarios:
'1. The normal interpolation where we have valid values (neither blank cells nor cells with text in them) and dates either side of the sought date's value.
'2. The case where there is no value BEFORE the sought date's value so we have to extrapolate BACK from the NEXT pair of of valid dates and values.
'3. The case where there is no value AFTER the sought date's value so we need to extrapolate FORWARDS from the PREVIOUS pair of valid dates and values.
'4. Only one date with a valid value in which case no inter/extrapolation can be done.
'5. No date with a valid value in which case no inter/extrapolation can be done.

'Cases 2 and 3 above is what FirstLaterDateFound, NextY2 and NextX2 is all about.
interpol = Application.WorksheetFunction.Trend(Array(KnownY1, KnownY2), Array(CLng(KnownX1), CLng(KnownX2)), CLng(NewDate.Value))
End Function