Using Sesame Data with Excel

In this tutorial, the combination of Sesame Data and the power of Excel will be demonstrated. To achieve this it will be used Power Query, the user-friendly technology from Microsoft, will be utilized for data transformation.

Get Entity List (no code)

Open Excel, and navigate: Data > From Web
Insert the URL of the endpoint. In this example, https://data.api.landytech.net/api/v1/entitylist, confirm here.
In the authentication, select "Basic" and inform your username and password (read more). Use "Connect" at the end to confirm.
Upon a successful connection, Excel should show a page similar to the following. Right-click above the column title List, and use the option To Table.
Use the following options: Select or enter delimiter: None How to handle extra columns: Show as errors
The output should be a Column1 with many record lines. Click on the little icon that says Expand, see image below, point 1. This will show the list of columns that should be pulled, which you may change at your best convenience. Uncheck the option Use Original column name as prefix.
Excel will allow you to review the information, click on Close & Load.
All set! Your data should be ready.

Get Holdings (using Advanced Editor)

Dealing with pagination slightly increases the complexity of Power Query. There are two ways of doing it. The first requires to adding a few lines of code, nothing too complex:

Open Excel, and navigate: Data > From Web

Use the following URL Please replace {entityId} with your entityId.

https://data.api.landytech.net/api/v1/holdings/{entityId}?pageSize=10

Open the editor: Right-click on the newly created query > Advanced Editor

You can use the following statement. ⚠️ Note, when pasting the below, replace 14422 with your entityId.

let
    // Define the base URL
    baseUrl = "https://data.api.landytech.net/api/v1/holdings/14422?pageSize=10",
 
    // Get data from the first page
    data = Json.Document(Web.Contents(baseUrl)),
    totalPages = data[totalPages],

    // Extract data from all pages
    allData =  List.Combine(List.Accumulate(
                    {0..totalPages-1},
                    {},
                    (accumulator, pageNumber) => accumulator & {Json.Document(Web.Contents(baseUrl & "&pageNumber=" &  Number.ToText(pageNumber))) [content]}
                )),

    // Parse the content into a table and expand it to columns
    table = Table.FromList(allData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expanded = Table.ExpandRecordColumn(table, "Column1", {"entityId", "date", "entityName", "assetName", "assetId", "assetType", "quantity", "localCurrency", "unitPrice", "marketValueLc", "riskCountry"}, {"entityId", "date", "entityName", "assetName", "assetId", "assetType", "quantity", "localCurrency", "unitPrice", "marketValueLc", "riskCountry"})
in
    // Return the table with all records
    expanded

Confirm and Close & Load. Your data should be available!

For the 'transactions' endpoint, use this code:

let
    // Define the base URL
    baseUrl = "https://data.api.landytech.net/api/v1/transactions/14422?pageSize=100",
 
    // Get data from the first page
    data = Json.Document(Web.Contents(baseUrl)),
    totalPages = data[totalPages],

    // Extract data from all pages
    allData =  List.Combine(List.Accumulate(
                    {0..totalPages-1},
                    {},
                    (accumulator, pageNumber) => accumulator & {Json.Document(Web.Contents(baseUrl & "&pageNumber=" &  Number.ToText(pageNumber))) [content]}
                )),

    // Parse the content into a table and expand it to columns
    table = Table.FromList(allData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expanded = Table.ExpandRecordColumn(table, "Column1", {"entityId", "entityName", "assetId", "assetName", "isin","valueDate", "tradeDate", "settlementDate", "transactionSubtype", "description", "quantity", "unitPrice", "localCurrency", "localCurrency2", "amount", "grossMovementLc", "movementRc", "grossMovementRc",  "transactionId", "commissionsLc", "stampDutyLc", "withholdingTaxLc", "collectionChargeLc", "taxLc", "accruedInterestLc", "capitalOrIncome", "subPortfolioCode", "subPortfolioCode2", "reversal"}, {"entityId", "entityName", "assetId", "assetName", "isin","valueDate", "tradeDate", "settlementDate", "transactionSubtype", "description", "quantity", "unitPrice", "localCurrency", "localCurrency2", "amount", "grossMovementLc", "movementRc", "grossMovementRc",  "transactionId", "commissionsLc", "stampDutyLc", "withholdingTaxLc", "collectionChargeLc", "taxLc", "accruedInterestLc", "capitalOrIncome", "subPortfolioCode", "subPortfolioCode2", "reversal"})
in
    // Return the table with all records
    expanded

For the 'subportfolioholdings' endpoint, use this code:

let
    // Define the base URL
    baseUrl = "https://data.api.landytech.net/api/v1/subportfolioholdings/14422?pageSize=100",
 
    // Get data from the first page
    data = Json.Document(Web.Contents(baseUrl)),
    totalPages = data[totalPages],

    // Extract data from all pages
    allData =  List.Combine(List.Accumulate(
                    {0..totalPages-1},
                    {},
                    (accumulator, pageNumber) => accumulator & {Json.Document(Web.Contents(baseUrl & "&pageNumber=" &  Number.ToText(pageNumber))) [content]}
                )),

    // Parse the content into a table and expand it to columns
    table = Table.FromList(allData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expanded = Table.ExpandRecordColumn(table, "Column1", {"entityId", "entityName", "date", "localCurrency", "marketValueLc", "subPortfolioCode"}, {"entityId", "entityName", "date", "localCurrency", "marketValueLc", "subPortfolioCode"})
in
    // Return the table with all records
    expanded

The second way of using pagination with Power Query is demonstrated in our tutorial of PowerBi.

PreviousUsing Sesame Data with Postman NextUsing Sesame Data with PowerBi

Last updated 1 year ago

Was this helpful?