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 optionTo 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 saysExpand
, see image below, point1
. This will show the list of columns that should be pulled, which you may change at your best convenience. Uncheck the optionUse 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.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.Confirm and
Close & Load
. Your data should be available!
For the 'transactions'
endpoint, use this code:
For the 'subportfolioholdings'
endpoint, use this code:
The second way of using pagination with Power Query is demonstrated in our tutorial of PowerBi.
Last updated