Excel & Power Query Integration

You can integrate the IS Macro API directly into Excel using two primary methods: our custom Office Add-in for simple functions, or the powerful Power Query tool for more complex data workflows.

Excel Add-in

Our custom add-in provides the most seamless experience for using our financial functions directly in your spreadsheets.

1. Download the manifest file: IS Macro Add-in Manifest.
2. Open Excel and go to File > Options > Trust Center > Trust Center Settings... > Trusted Add-in Catalogs.
3. In the "Catalog Url" box, enter the path to a folder on your local machine (e.g., `C:\addin`) and click "Add catalog". Make sure to check the "Show in Menu" box and press OK.
4. Place the downloaded `addin-manifest.xml` file into that folder.
5. Now, in Excel, go to Insert > My Add-ins > Shared Folder.
6. Select the "IS Macro" add-in to load it. It will now be available in your Excel ribbon.

Power Query Integration

Power Query is the ideal tool for building robust, refreshable reports by pulling data directly from the API into your workbook.

Simple Example: Calling a Single Endpoint

This method is best for pulling a simple list, like the current yield curves.

1. In Excel, go to the Data tab and click From Web.
2. In the dialog box, enter the URL of the API endpoint (e.g., `https://www.lce.is/api/fixed-income/yield-curves-graph-data`) and click "OK".
3. The Power Query Editor will open. You will see a record containing the API response.
4. Click on the `List` or `Record` links to drill down into the data, and use the "To Table" and "Expand Columns" tools in the ribbon to structure the data into a usable table.
5. Once you are happy with the format, click "Close & Load".

Advanced Workflow: Enriching Local Data with the API

A powerful pattern is to use Power Query to get a list of securities from your own database and then send that list to our API for valuation and risk calculations in a single batch. This is highly efficient.

The following example demonstrates how to value a portfolio of bonds fetched from a local SQL Server database using the batch bond calculator endpoint.

Power Query M Code Example:

let
    // Step 1: Execute the initial SQL Query to get the base bond data from a local database.
    Source = Sql.Database("YourServerName", "YourDatabaseName", 
    [Query="
        -- Your SQL query to get a list of tickers and nominal amounts goes here.
        -- Example:
        SELECT
            BOND.[Ticker] AS 'Auðkenni',
            SUM(BOND.[Nafnverð]) * -1 AS 'Nafnverð'
        FROM [YourDB$Bond] AS BOND
        WHERE --... your conditions
        GROUP BY BOND.[Ticker]
    "]),
    
    // Step 2: Filter out any rows that have a zero or null nominal value.
    FilteredSource = Table.SelectRows(Source, each [Nafnverð] <> 0 and [Nafnverð] <> null),

    // Step 3: Prepare the data payload for the API. This involves renaming columns
    // to match the API's expected format ('ticker', 'nominal') and adding a default yield.
    PreparedPayload = let
        SelectedForPayload = Table.SelectColumns(FilteredSource, {"Auðkenni", "Nafnverð"}),
        RenamedForPayload = Table.RenameColumns(SelectedForPayload, {{"Auðkenni", "ticker"}, {"Nafnverð", "nominal"}}),
        AddYieldRate = Table.AddColumn(RenamedForPayload, "yield_rate", each 0.03, type number),
        ListOfRecords = Table.ToRecords(AddYieldRate)
    in
        ListOfRecords,

    // Step 4: Make the single batch API call to the calculator endpoint.
    // The body of the request is the list of records created in the previous step.
    ApiResponse = let
        baseUrl = "https://www.lce.is/api/fixed-income/bond-calculator/batch",
        valueDate = Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd"),
        fullUrl = baseUrl & "?value_date=" & valueDate & "&adjust_for_holidays=true",
        jsonBody = Json.FromValue(PreparedPayload),
        response = Web.Contents(fullUrl,
            [
                Headers = [#"Content-Type"="application/json"],
                Content = jsonBody,
                Timeout = #duration(0, 0, 5, 0) // 5 minute timeout for long calculations
            ]
        )
    in
        response,

    // Step 5: Process the API response by parsing the JSON and converting it into a table.
    ParsedResponse = Json.Document(ApiResponse),
    ResultsTable = Table.FromRecords(ParsedResponse),

    // Step 6: Join the original data from your SQL query with the new results from the API.
    // An index is added to both tables to ensure a correct row-by-row join.
    SourceWithIndex = Table.AddIndexColumn(FilteredSource, "Index", 0, 1),
    ResultsWithIndex = Table.AddIndexColumn(ResultsTable, "Index", 0, 1),
    JoinedTables = Table.NestedJoin(SourceWithIndex, {"Index"}, ResultsWithIndex, {"Index"}, "APIResult", JoinKind.LeftOuter),

    // Step 7: Expand the nested columns to flatten the data into a final table.
    // First, expand the top-level results.
    ExpandedData = Table.ExpandTableColumn(JoinedTables, "APIResult", {"terms", "valuation", "risk"}, {"terms", "valuation", "risk"}),
    
    // Step 8: Now, expand the nested 'valuation' record to get specific metrics.
    ExpandedValuation = Table.ExpandRecordColumn(ExpandedData, "valuation", 
        {"indexed_remaining_principal", "indexed_accrued_interest", "indexed_total", "npv", "next_coupon_date"},
        {"Indexed Principal", "Indexed Accrued", "Indexed Total", "NPV", "Next Coupon Date"}
    ),

    // Step 9: Clean up the final table by setting data types and removing intermediate columns.
    SetTypes = Table.TransformColumnTypes(ExpandedValuation,{
        {"Next Coupon Date", type nullable date},
        {"Indexed Principal", type number},
        {"Indexed Accrued", type number},
        {"Indexed Total", type number},
        {"NPV", type number}
    }),
    FinalTable = Table.RemoveColumns(SetTypes,{"Index", "terms", "risk"})

in
    FinalTable