# 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`<br>

   <figure><img src="/files/MU5vTKnI9W7RkvnCMG8V" alt=""><figcaption></figcaption></figure>
2. Insert the URL of the endpoint.\
   In this example, `https://data.api.landytech.net/api/v1/entitylist`, [confirm here](/api-endpoints/entity-list.md).<br>

   <figure><img src="/files/Hg5HOAWGxtkCzJ7AJCiK" alt=""><figcaption></figcaption></figure>
3. In the authentication, select "Basic" and inform your username and password ([read more](/authentication/how-to-authenticate.md)). Use "Connect" at the end to confirm.<br>

   <figure><img src="/files/POM2HWmMMZ5Cugr8FgdD" alt=""><figcaption></figcaption></figure>
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`.

   <figure><img src="/files/K5fUB0YwPuavV4MnDiU3" alt=""><figcaption></figcaption></figure>
5. Use the following options:\
   **Select or enter delimiter**: None\
   **How to handle extra columns**: Show as errors

   <figure><img src="/files/PC7yQEu0BQA2TAbJlFkS" alt=""><figcaption></figcaption></figure>
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`.

   <figure><img src="/files/qYIF77MhfNv3AtxIbl4h" alt=""><figcaption></figcaption></figure>
7. Excel will allow you to review the information, click on `Close & Load`.

   <figure><img src="/files/0g5fDeTvnZYFVHdd0jeE" alt=""><figcaption></figcaption></figure>
8. All set! Your data should be ready.

   <figure><img src="/files/fAnR0NjanHnELU03SHcL" alt=""><figcaption></figcaption></figure>

***

## 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](/api-endpoints/entity-list.md).

   ```javascript
   https://data.api.landytech.net/api/v1/holdings/{entityId}?pageSize=10
   ```
3. Open the editor: `Right-click on the newly created query > Advanced Editor`

<figure><img src="/files/LEy48C3q1291B57neELM" alt=""><figcaption></figcaption></figure>

4. You can use the following statement. \
   :warning: Note, when pasting the below, replace `14422` with your [entityId](/api-endpoints/entity-list.md).

   ```javascript
   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
   ```

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

{% hint style="info" %}
For the '`transactions'` endpoint, use this code:
{% endhint %}

```
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
```

{% hint style="info" %}
For the '`subportfolioholdings'` endpoint, use this code:
{% endhint %}

```
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.&#x20;


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.landytech.com/tutorials-recipes/using-sesame-data-with-excel.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.