PDA

View Full Version : [SOLVED] Linear interpolation



demian
09-20-2017, 08:27 PM
Hi, VBA Experts!

I'd like to ask for help:
I have to code the linear interpretation for missing data.
The way I did is pretty simple, but I got stuck somepoint..

As attached file, I have to find the missing data for the year 4 and 7 from the table(A1:B8) to be able to make the new data array(D1:E10)
I was bale to fill the missing year in the new table, but for the year to be interpolated with Zero value.
I need an advice regarding the logic for finding the next Filled Rate to interpolate with and the logic to be applied for the interpolation formula.

Any help is greatly appreciated.


Function Interpolate(oldRates As Variant) As Variant
Dim i As Integer, j As Integer, oldCount As Integer, Maturity As Integer
' (8,1) in old rates data array highlighted in yellow, so maturity became 10
Maturity = oldRates(oldRates.Rows.Count, 1)
Dim Mat As Variant
ReDim Mat(1 To Maturity, 1 To 2) ' 1 to 10, 1 to 2: the new array set up.
'Fills in the years
For i = 1 To Maturity
Mat(i, 1) = i
Mat(i, 2) = 0
Next i
'copies over known Rates.
For i = 1 To Maturity ' 1 to 10: i
For j = 1 To oldRates.Rows.Count ' 1 to 8 : J
If oldRates(j, 1) = Mat(i, 1) Then
Mat(i, 2) = oldRates(j, 2)
End If
Next j
Next i
For i = 1 To Maturity
If Mat(i, 2) = 0 Then
'find the next Filled Rate to interpolate with
For j = i + 1 To Maturity
'if statement
Next j
' Mat(i,2) = linear interpolation code here.
Mat(i, 2) = Mat(i - 1, 2) + (Mat(i + 1, 2) - Mat(i - 1, 2)) / (Mat(i + 1, 1) - Mat(i - 1, 1)) * (Mat(i, 1) - Mat(i - 1, 1))
End If
Next i
Interpolate = Mat
End Function

YasserKhalil
09-20-2017, 11:09 PM
Hello
Have a look at this link (May be helpful for you)
From Here (https://wellsr.com/vba/2016/excel/powerful-excel-linear-interpolation-function-vba/#Example)

Paul_Hossler
09-21-2017, 06:42 AM
2 Things

1. I added CODE tags for you -- you can use the [#] icon to insert them and paste your macro(s) between

2. The macro should go on a Standard Module (Insert | Standard Module) not on a worksheet module

20420
3. You need to 'array enter' the formula: select the output range of cells and use control+shift+enter.
Don't enter the { } around your formula, the CSE will add that

20421


Seems to work, or at least return numbers

(OK, 3 things)


Simple overview of array formulas

https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

demian
09-21-2017, 11:26 AM
Paul,
I was not aware of the code tag rule for uploading the coding part. Thanks for correcting me, also thanks for the useful link for overview of the array formula:)
Yes, it is working for the given data set with missing at t=4 and 7.
But what if we have missing data on t=4 and 5 consecutively, which I attached "VBAExpress2.xls"
It doesn't seem working in that case.

The logic to be used here quite challenging for a VBA newbie like me with double loop and if statement simultaneously.:bug:
I need the general 'if statements' to find the next filled rate to and 'linear interpolation code' as below. Could you please help me?


For i = 1 To Maturity
If Mat(i, 2) = 0 Then
'find the next Filled Rate to interpolate with
For j = i + 1 To Maturity
'if statement

Next j
' Mat(i,2) = linear interpolation code here. not sure abt the below.

Mat(i, 2) = Mat(i - 1, 2) + (Mat(i + 1, 2) - Mat(i - 1, 2)) / (Mat(i + 1, 1) - Mat(i - 1, 1)) * (Mat(i, 1) - Mat(i - 1, 1))

End If


I'm new enough to VBA so not sure where i am going wrong, any help is greatly appreciated.

Regards,
Demian

SamT
09-21-2017, 11:56 AM
But what if we have missing data on t=4 and 5 consecutively, which I attached "VBAExpress2.xls"
It doesn't seem working in that case.
You have several "Bends" in that data curve, If a "Bend" occurs at a missing data Point, the interpolation will be off

Paul_Hossler
09-21-2017, 12:48 PM
If it's not necessary to 'roll your own' why not use the built in Slope() and Intercept() functions?

They seem to handle your gap case better. I also did a trivial straight line case

20428




Option Explicit

Function Interpolate(oldYears As Range, oldRates As Range) As Variant
Dim i As Long
Dim A() As Variant
Dim X As Variant, Y As Variant
Dim m As Double, b As Double

With Application.WorksheetFunction
X = .Transpose(oldYears)
Y = .Transpose(oldRates)

ReDim A(1 To X(UBound(X)), 1 To 2)

m = .Slope(Y, X)
b = .Intercept(Y, X)
End With

For i = LBound(A, 1) To UBound(A, 1)
A(i, 1) = I
A(i, 2) = m * i + b
Next i
Interpolate = A

End Function

demian
09-26-2017, 08:43 AM
I appreciate your sheet. It seems the perfect worksheet for my case.
However, unfortunately, I have to work per the comments here as this is a school project.
At least, there has been some progress in my file, but it still doesn't generate the consecutive missing rates...
Can you take a look at it again?


Option Explicit
Function Interpolate(oldRates As Variant) As Variant
Dim i As Integer, j As Integer, oldCount As Integer, Maturity As Integer
' (8,1) in old rates data array highlighted in yellow, so maturity became 10
Maturity = oldRates(oldRates.Rows.Count, 1)
Dim Mat As Variant
ReDim Mat(1 To Maturity, 1 To 2) ' 1 to 10, 1 to 2: the new array set up.
'Fills in the years
For i = 1 To Maturity
Mat(i, 1) = i
Mat(i, 2) = 0
Next i
'copies over known Rates.
For i = 1 To Maturity ' 1 to 10: i
For j = 1 To oldRates.Rows.Count ' 1 to 8 : J
If oldRates(j, 1) = Mat(i, 1) Then
Mat(i, 2) = oldRates(j, 2)
End If
Next j
Next i
For i = 1 To Maturity
If Mat(i, 2) = 0 Then
For j = i + 1 To Maturity 'find the next Filled Rate to interpolate with
If Mat(j, 2) = 0 Then 'if statement
Next j
Else
' Mat(i,2) = linear interpolation code here
Mat(i, 2) = Mat(i - 1, 2) + (Mat(j, 2) - Mat(i - 1, 2)) / (Mat(i + 1, 1) - Mat(i - 1, 1)) * (Mat(i, 1) - Mat(i - 1, 1))
End If
Next i
Interpolate = Mat
End Function

Paul_Hossler
09-26-2017, 10:36 AM
I think you were wiping out your data

I added the Exit For to get out of your j loop once the gap is calculated and now everything looks reasonable

(although my math is a little rusty)



Option Explicit

Function Interpolate(oldRates As Variant) As Variant
Dim i As Long, j As Long, Maturity As Integer
' (8,1) in old rates data array highlighted in yellow, so maturity became 10
Maturity = oldRates(oldRates.Rows.Count, 1)
Dim Mat() As Double
ReDim Mat(1 To Maturity, 1 To 2) ' 1 to 10, 1 to 2: the new array set up.
'Fills in the years
For i = 1 To Maturity
Mat(i, 1) = i
Mat(i, 2) = 0#
Next i
'copies over known Rates.
For i = 1 To oldRates.Rows.Count ' 1 to 8
j = oldRates(i, 1).Value
Mat(j, 2) = oldRates(i, 2)
Next i
For i = 2 To Maturity
'find if missing
If Mat(i, 2) = 0# Then
For j = i + 1 To Maturity 'find the next Filled Rate to interpolate with
If Mat(j, 2) <> 0 Then 'if statement
Mat(i, 2) = Mat(i - 1, 2) + (Mat(j, 2) - Mat(i - 1, 2)) / (Mat(j, 1) - Mat(i - 1, 1))
Exit For ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End If
Next j
End If
Next i
Interpolate = Mat
End Function

demian
09-27-2017, 08:22 AM
Thank you everyone and Big thanks to Paul!!:hi:
I feel more comfortable with the if stmt now:)
This will get me moving forward on learning VBA.

Stupid question - can you please explain the details of why you need to get out of J loop for (not sure) adding the exit?

Paul_Hossler
09-27-2017, 09:38 AM
Stupid question - can you please explain the details of why you need to get out of J loop

Once you find the next X(j) with a Y(j) <> 0 and use it to interpolate the X(I) with the Y(I) = 0, you don't want to continue looking since that could overwrite the Y(I) you just calculated