BILINEARINTERP

Updated 2024-02-13 20:43:07.520000

Syntax

SELECT [westclintech].[wct].[BILINEARINTERP](
  <@Grid, nvarchar(max),>
 ,<@New_x, float,>
 ,<@New_y, float,>)

Description

Use the scalar function BILINEARINTERP to interpolate on a regular 2-dimensional grid. BILINEARINTERP accepts the 2-dimensional grid, a new x-value, and a new y-value as input, where the x-values are stored in the first column of data and the y-values are stored in the first row of data and the data are contained in the table.

The bilinear interpolation calculation can be thought of as a series of linear interpolation calculations using the closest combination of x- and y-values on the 2-dimensional grid. For example, let's look at the following table which shows the number of days in the future as the x-values, the strike price of an option as the y-values, and which contains the volatility associated with the date and the strike price.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell","width":64},{"field":"column 2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell","width":64},{"field":"column 3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell","width":64},{"field":"column 4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell","width":64},{"field":"column 5","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell","width":64},{"field":"column 6","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell","width":64},{"field":"column 7","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell","width":64},{"field":"column 8","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell","width":64}],"rows":[{"column 2":"70","column 3":"80","column 4":"90","column 5":"100","column 6":"110","column 7":"120","column 8":"130"},{"column 1":"30","column 2":"0.22","column 3":"0.2","column 4":"0.18","column 5":"0.16","column 6":"0.175","column 7":"0.19","column 8":"0.205"},{"column 1":"90","column 2":"0.23","column 3":"0.21","column 4":"0.19","column 5":"0.17","column 6":"0.185","column 7":"0.2","column 8":"0.215"},{"column 1":"182","column 2":"0.26","column 3":"0.24","column 4":"0.22","column 5":"0.2","column 6":"0.215","column 7":"0.23","column 8":"0.245"},{"column 1":"270","column 2":"0.25","column 3":"0.23","column 4":"0.21","column 5":"0.19","column 6":"0.205","column 7":"0.22","column 8":"0.235"},{"column 1":"365","column 2":"0.245","column 3":"0.225","column 4":"0.205","column 5":"0.185","column 6":"0.2","column 7":"0.215","column 8":"0.23"}]}

If we want to interpolate the volatility for 63 days with a strike price of 87, we can quickly see that the surrounding points would look like this.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"column 2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"column 3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 2":"80","column 3":"90"},{"column 1":"30","column 2":"0.20","column 3":"0.18"},{"column 1":"90","column 2":"0.21","column 3":"0.19"}]}

It is then a very straightforward to see that the desired result can be thought of as three linear interpolation calculations, as demonstrated by the following SQL.

SELECT wct.INTERP(x, y, 87) as BiLinear

FROM

(

    SELECT 80 as x,

           wct.INTERP(x, y, 63) as y

    FROM

    (

        VALUES

            (30, .20),

            (90, 0.21)

    ) n (x, y)

    UNION ALL

    SELECT 90,

           wct.INTERP(x, y, 63)

    FROM

    (

        VALUES

            (30, .18),

            (90, 0.19)

    ) n (x, y)

) i;

Producing the result of 0.1915

Arguments

@New_y

the new y-value. @New_y must be a type float or of a type that implicitly converts to float.

@New_x

the new x-value. @New_x must be a type float or of a type that implicitly converts to float.

@Grid

a T-SQL statement, as text, which when executed returns a resultant table containing the x-values in the first column, the y-values in the first row and the values (z) to be interpolated. In other words the table should look something like this:

{"columns":[{"field":"column 1","width":64},{"field":"column 2","width":64},{"field":"column 3","width":64},{"field":"column 4","width":64},{"field":"column 5","width":64},{"field":"column 6","width":64}],"rows":[{"column 1":"NULL","column 2":"y 1","column 3":"y 2","column 4":"y 3","column 5":"…","column 6":"y n"},{"column 1":"x 1","column 2":"z x1y1","column 3":"z x1y2","column 4":"z x1y3","column 5":"…","column 6":"z x1yn"},{"column 1":"x 2","column 2":"z x2y1","column 3":"z x2y2","column 4":"z x2y3","column 5":"…","column 6":"z x2yn"},{"column 1":"x 3","column 2":"z x3y1","column 3":"z x3y2","column 4":"z x3y3","column 5":"…","column 6":"z x3yn"},{"column 1":"…","column 2":"…","column 3":"…","column 4":"…","column 5":"…"},{"column 1":"x m","column 2":"z xmy1","column 3":"z xmy2","column 4":"z xmy3","column 5":"…","column 6":"z xmyn"}]}

All values in the resultant table must be of a type float or of a type the implicitly converts to float.

Return Type

float

Remarks

If @New_x < MIN(X) or @New_x > MAX(X) then a NULL will be returned.

If @New_y < MIN(Y) or @New_y > MAX(y) then a NULL will be returned.

If the @New_x and @New_y are bound by a grid containing a NULL then a NULL will be returned.

Examples

SELECT wct.BILINEARINTERP(

                             'SELECT *

FROM (VALUES

(NULL,-45,-44,-43,-42,-41),

(44,67,17,67,7,66),

(45,53,65,34,3,90),

(46,25,40,21,29,24),

(47,29,69,17,63,51),

(48,71,90,90,57,54),

(49,92,15,71,0,10),

(50,33,73,21,19,99)

)n(x,y1,y2,y3,y4,y5)',

                             47.25,

                             -43.5

                         ) as BILINEARINTERP;

This produces the following result.

{"columns":[{"field":"BILINEARINTERP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"BILINEARINTERP":"54.75"}]}