powerquerylinear-interpolation

Can I add a new column with Linear Interpolation in Power Query M?

I am working on extracting an Interest Rate curve from futures market prices and create a table (Table 1) inside power query with the following columns:

- BusinessDays: Represents the nr o business days from today to the expiry of each future contract

- InterestRate: Represents the rate from today until the expiry of the futures contract

Table 1

The second table (table 2) refers to the ID of internal financial products that expire in different business days.

- InstrumentID: Unique internal ID a financial product selled by a financial institution

- BusinessDays: Represents the nr o business days from today to the expiry of each financial product

Table 2

I am having some trouble with M language, and unfortunately this specific calculation must be executed in Excel, so i am restricted to Power Query M.

The specific step i am not able to do is:

The end result i am looking for would look like this

End result

Solution

  • There are several ways to approach this but one way or another, you'll need to do some kind of lookup to determine which bracket to match your BusinessDays value with, so you can calculate the interpolated value.

    I think it's simpler to just generate an all inclusive list of days vs interest rates, and then do a Join to pull out the matches.

    I Name'd this first query intRates and expanded the Interest Rate table:

    let

//Get the interest rate/business day table
    Source = Excel.CurrentWorkbook(){[Name="intRates"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BusinessDays", Int64.Type}, {"InterestRate", Percentage.Type}}),

    //Add two columns which are the interest rate and business day columns offset by one
    //It is faster to subtract this way than by adding an Index column
    offset= 
        Table.FromColumns(
            Table.ToColumns(#"Changed Type") 
                & {List.RemoveFirstN(#"Changed Type"[BusinessDays]) & {null}}
                & {(List.RemoveFirstN(#"Changed Type"[InterestRate])) & {null}},
            type table[BusinessDays=Int64.Type, InterestRate=Percentage.Type, shifted BusDays=Int64.Type, shifted IntRate=Percentage.Type]),

//Add a column with a list of the interest rates for each data interpolated between the segments
    #"Added Custom" = Table.AddColumn(offset, "IntList", each let
            sbd=[shifted BusDays], 
            intRateIncrement = ([shifted IntRate]-[InterestRate])/([shifted BusDays]-[BusinessDays]),
            Lists= List.Generate(
                ()=>[d=[BusinessDays],i=[InterestRate]],
                each [d]< sbd,
                each [d=[d]+1, i = [i]+intRateIncrement],
                each [i])
        in Lists),

//add another column with a list of days corresponding to the interest rates
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "dayList", each {[BusinessDays]..[shifted BusDays]-1}),

//remove the last row as it will have an error
    remErrRow = Table.RemoveLastN(#"Added Custom1",1),

//create the new table which has the rates for every duration
    intRateTable = Table.FromColumns(
                        {List.Combine(remErrRow[dayList]),List.Combine(remErrRow[IntList])},
                        type table[Days=Int64.Type, Interest=Percentage.Type])
in
    intRateTable

    This results in a table that has every day (from 39 to , with its corresponding interest rate.

    Then read in the "Instruments" table and Join it with the intRates, using a JoinKind.LeftOuter

    let
    Source = Excel.CurrentWorkbook(){[Name="Instruments"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InstrumentID", type text}, {"BusinessDays", Int64.Type}}),

//add the rate column
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"BusinessDays"}, intRates, {"Days"}, "intRates", JoinKind.LeftOuter),
    #"Expanded intRates" = Table.ExpandTableColumn(#"Merged Queries", "intRates", {"Interest"}, {"Interest"})
in
    #"Expanded intRates"

    enter image description here

    Some of the results in the middle part of the table differ from what you've posted, but seem to be consistent with the linear interpolation formula for between two values, so I'm not sure how the discrepancy arises