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)

1. Open Excel, and navigate: Data > From Web

   
2. Insert the URL of the endpoint. In this example, https://data.api.landytech.net/api/v1/entitylist, confirm here.

   
3. In the authentication, select "Basic" and inform your username and password (read more). Use "Connect" at the end to confirm.

   
4. 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.

   
5. Use the following options: Select or enter delimiter: None How to handle extra columns: Show as errors

   
6. 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.

   
7. Excel will allow you to review the information, click on Close & Load.

   
8. 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:

1. Open Excel, and navigate: Data > From Web

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

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

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

1. 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

2. 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.