Supplemental Documentation

1 Introduction

1.1 What is the AtlasFive Business REST API?

The AtlasFive Business REST APIs are a set of REST APIs implemented on a serverless architecture. They provide access to AtlasFive curated data and allow external users and app developers to use our data for custom integrations to support their business analytics and reporting needs. The Business APIs allows Eton Solutions clients to integrate AtlasFive into their enterprise ecosystem programmatically without relying on the AtlasFive front end. Our REST APIs let clients access and manipulate our data in various ways.

For example, with our AtlasFive Business Rest APIs, you can:

• Extend capabilities: Build custom integrations to better support your business needs.

• Automate workflows: Automate workflows by programmatically accessing and integrating AtlasFive data into your enterprise ecosystem, without relying on the AtlasFive front end.

• Source the most recent data: Use the APIs as the single access point for the most recently updated data.

• Augment data: Clean, enrich, manipulate and augment data to suit your reporting needs.

• Expand analytics and reporting: Source API data into the tool of your choice, such as Python, R, CL using Curl, Postman, Excel, PowerBI to acquire richer insights and develop custom reporting solutions.

1.2 Getting started with REST API basic elements

1.2.1 Basic Elements 

The abbreviation of REST stands for Representational State Transfer, and API stands for Application Programming Interface.

• The REST API consists of the following elements:

  1. The Endpoint: This is the URL of the resource to which we are going to place the requests. For example: https://api.powerbi.com/v1.0/myorg/

  2. The Method: Although there are multiple methods to call REST APIs, the AtlasFive Business API uses the GET and POST methods.

     • The GET method is used to retrieve resources. A GET request retrieves data from a web server by specifying parameters in the URL portion of the request. You can see the parameters passed by just looking at the URL. For example, in the URL below the parameters are underlined: https://api.familyoperations.com/clientx/v1/GetHoldings?tenantKey=26&portfolioKey=86&entityKey=-12345&dataType=holdings&relationshipKey=126&asOfDate=2021-08-14&stateFilter=%7B%7D

     • The POST method is used to create resources. Unlike the GET request, the POST method does not pass the parameters at the end of the URL. Rather, the POST method contains the list of parameters in the body of the request. POST is normally used to update an account, submit an order, etc.

  3. The Headers: The authentication mode and the content types.

  4. The Data (Body): Textual data in the form of JSON, which needs to be sent to the resource.

• All API access is over HTTPS. All data is sent and received as JSON.

• Blank fields are included as null instead of being omitted.

1.2.2 Authorization, Authentication and Version

All AtlasFive Business API requests require authentication. The current authorization protocol used is OAuth2.

OAuth2 uses Access Tokens. An Access Token is a piece of data that represents the authorization to access resources on behalf of the end user. The JSON Web Token format is used, which enables token issuers to include data in the token itself. For security reasons, Access Tokens have an expiration time and date, and need to be refreshed periodically.

The Version of the AtlasFive API will change periodically as new releases occur. Currently, all AtlasFive APIs are using Version 3.

1.3 How to add and sign-in the AtlasFive API?

1.3.1 Excel Users

Eton Solutions created a custom Excel add-in to facilitate multi-factor authentication for registered Excel users. A registered user has had their account set up by Eton Solutions IT. For the user account to be set up, the user must submit a service desk request. The steps below outline how to add the custom add-in, which, once installed, will load as a side-bar in Excel as shown below:

After the user account has been set up, the user must follow the steps below:

1. Open Windows PowerShell

2. Insert the connection string. To get the connection string, go to the designated storage account, click on connect, and copy the connection string as shown below:

3. Paste the copied the connection into Windows PowerShell and press enter.

4. If the credential was added successfully, the user will see the confirmation message below:

5. In the Excel template, open File → Options → Trust Center → Trusted Add-in Catalogs and in the Catalog Url paste the url from the connection string. The url is located between the double quotes in the #Mount the drive section of the connection string, as shown below:

6. Paste the url in the Catalog Url section in the opened Excel Trust Center as shown below and click on Add Catalog:

7. Check the "Show in Menu" check mark and click OK.

8. Check the "Show in Menu" check mark and click OK. You may get a notification like the one below. Click OK to agree and OK to exit from the Excel options menu:

9. Close all Excel spreadsheets and re-open the template file.

10. Go to Insert → My add ins → See All as shown below:

11. In the Office Add-ins menu, go to the Shared Folder and select the AtlasFive API add-in, and click add.

12. The add-in should appear at the end of the task bar as shown:

13. Once the add-in has loaded, in the first sheet of the template named "Authentication Token", select cell B1, click Sign in in the side bar, and then select your user account. That will generate the bearer token in cell B2, which will then be passed into the API request headers.

You have now successfully authenticated and are ready to use the template!

1.3.2 PowerBI Users

The buildout of MFA for PowerBI Users is currently in progress.

1.4 How to add a new API connection to an existing template?

1.4.1 Excel

Important Considerations

• The current template consists of user input tabs and API data tabs. The user input tabs are the Token1 tab, the Authorization Credentials tab, and the API Query Customization tab.

• The Token1 and Authorization Credentials tabs contains the subscription details required for the user to obtain the necessary authentication token.

• The API Query Customization tab contains API endpoints and body parameters that can be configured by the user directly through this tab. This tab allows the user to insert information in certain fields in the main Excel spreadsheet. That information (such as the API URL) is transformed into parameters in the Advanced Editor and passed on to the API through the API request.

  Note, however, that while many of the API requests have the same body parameters (such as version, entityKey, filters, sort, offset, limit), some API requests have different or additional parameters. The sections below will provide practical examples of how to add a new API request to an existing template when the body of the new request is different.

• The data tabs contain the API response data.

• Make sure that any new rows containing new API URL are inserted at the end of the list with existing API URLs (as shown in Step 1 below).

  The order in which the URLs are represented in the API Query Customization tab is important, because the tool that configures the Excel spreadsheets to source data from APIs (called Microsoft Excel PowerQuery) relies on the order in which data is entered into the API Query Customization sheet. Therefore, changing the order of the elements in the API Query Customization tab by inserting a row in between existing rows, rather than at the end of the table will result in processing errors and prevent the user from being able to connect to the API.

• PowerQuery is a Microsoft tool that simplifies the process of importing data from different source files and sorting them into an Excel sheet. Power Query is a user-friendly business intelligence tool that does not require the user to learn any specific code. When a user creates a data transformation in the power query editor point-and-click UI, Excel is writing the corresponding code for the query. The code is written in a language called M code. M is a functional language which means it is primarily written with functions that are called to evaluate and return results.

• The PowerQuery Advanced Editor stores all the operations executed upon a data object in the order in which they were executed.

• To access the Advanced Editor, go to the Data tab, click on "Get Data" and select "Launch Power Query Editor" as demonstrated below:

• A correctly constructed API request means that the user will succeed in sourcing data from the API.

 
Instructions

To add a new API connection in the Excel spreadsheet, follow the instructions below:

1. In the API Query Customization tab, add a new row with the name of the API and the API URL as demonstrated in the screenshot below.

2. Go to the Data menu, chose the Get Data dropdown, and select the “Launch PowerQuery Editor” option, as shown below.

3. Once the PowerQuery Editor is open, check that the new row got added to the API Query Customization table located under the API Query Customization folder. If the row was not added, click "Refresh Preview", as shown below:

4. Right click the new API URL and click "Add as New Query".

5. The new "Value" will be added as shown below. To maintain clarity and consistency with the rest of the document, rename the "Value" to a more descriptive name, such as "GetRelationships URL".

6. Go to the API Calls folder and select any of the existing tables. In this example, we will be duplicating the GetRptEntities table. Right click the table and select "Duplicate".

7. This will duplicate the existing table and assign it an automatic name with a (2) at the end to denote that there are 2 versions of the same table in the API Calls folder.

8. With the table selected click on the "Advanced Editor" to view the M code. Note that the URL and "Expanded Column" sections are at this stage specific to the details of the table you duplicated. As such, the URL in this example is "GetRptEntities URL".

9. To configure our spreadsheet with the GetRelationships URL, we must:

• Replace the url

  1.  #"GetRptEntities URL"

  replace with the correct URL of the new API, that is:

  1.  #"GetRelationships URL"

• Replace the body of the call with the correct body corresponding to the new API request.
  Note that some requests do not have a body. We will explore an example of that case in Section 1.5. Also note that the elements in the GetRptEntities body are different from the elements in the GetRelationships body and will need to be edited. Currently, GetRptEntities is dynamically pulling the body parameters from the API Query Customization tab of the template. Given that many of the POST API requests in AtlasFive Business API share the same body, we configured the template to allow the user to change the parameters in the API Query Customization tab, which is more convenient than having to manually edit each API query. However, not all POST API requests have the same body. In that case, the user can edit the M code in the Advanced Editor as outlined below.

  GetRptEntities body parameters are dynamically sourced from the API Query Customization tab, as demonstrated by the "&" symbols:

  1.  body = "{
  2.           ""version"":"&version&",
  3.           ""entityKey"":"&entityKey&",
  4.           ""filters"":"&filters&",
  5.           ""sort"":"&sort&",
  6.           ""offset"":"&offset&",
  7.           ""limit"":"&limit&"}",

  The body corresponding to the GetRelationships request can be found in the AtlasFive API Documentation file, and is represented in JSON format:

  1.    {
  2.      "filters": {
  3.        "relationships": [ ],
  4.        "entities": [ ],
  5.        "accounts": [ ],
  6.        "portfolios": [ ],
  7.        "legacyRelationshipIds": [ ]
  8.      }
  9.    }

  Paste the body from the documentation into the PowerQuery Advanced Editor. There are two possible options to edit the body of the API request. Both options will lead to the same API response, and neither option is superior.

  Option #1: Default option using Json.Document() function:

  This is the default option used in the Excel template, because the default Excel spreadsheet is set up to allow the user to modify the elements of the body through the API Query Customization tab.

  The only modification to the JSON body from the documentation is the additional quotations that help M code correctly interpret and pass the body in the API request. The additional quotations are highlighted in red below:

  1.  body = "{
  2.            ""filters"": {
  3.               ""relationships"":""[]"",
  4.               ""entities"":""[]"",
  5.               ""accounts"":""[]"",
  6.               ""portfolios"":""[]"",
  7.               ""legacyRelationshipIds"":""[]""    
  8.             }
  9.          }",

  Option #2: Alternative option using Json.FromValue() function:

  In this option, we are using a PowerBI function called Json.FromValue(), which can read and interpret JSON and does not require the user to add additional quotation marks around the body elements. However, since we are using a different function, we must remove the body, parsed_JSON,and BuildQueryString lines from the M code (see table below, lines 3-11) and modify the content line (see table below, line 16).

  1.  Content = Json.FromValue([
  2.              filters= [
  3.                relationships=[ ],
  4.                entities=[ ],
  5.                accounts=[ ],
  6.                portfolios=[ ],
  7.                legacyRelationshipIds=[ ] 
  8.              ] 
  9.            ] ) ] )),

• Replace the expanded column section with the correct column names that exist in the new API. 
  The correct column names can be found in the API documentation file.

  Note that you do not need to pull in all the available columns in the request, but you should specify at least one column name. 
  The flexibility of the API allows the user to only pull the columns the user needs for the analysis.

  1.    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {
  2.        "RelationshipBK",
  3.        "RelationshipName",
  4.        "RelationshipType",
  5.        "RelationshipStatus",
  6.        "EntityBK",
  7.        "EntityName",
  8.        "EntityShortName",
  9.        "EntityType",
  10.       "EntityDescription",
  11.       "EntityTaxStatus",
  12.       "EntityCurrency",
  13.       "ChartOfAccounts",
  14.       "AccountingMethod",
  15.       "COABK"},
  16.       {
  17.       "RelationshipBK",
  18.       "RelationshipName",
  19.       "RelationshipType",
  20.       "RelationshipStatus",
  21.       "EntityBK",
  22.       "EntityName",
  23.       "EntityShortName",
  24.       "EntityType",
  25.       "EntityDescription",
  26.       "EntityTaxStatus",
  27.       "EntityCurrency",
  28.       "ChartOfAccounts",
  29.       "AccountingMethod",
  30.       "COABK"})
  31. in
  32.   #"Expanded Column1"

 
The side-by-side comparison below demonstrates the differences between the GetRptEntities and GetRelationships M code, as well as the differences between the GetRelationships queries using Option#1 and Option#2 for editing the body of the request.

1.4.2 PowerBI

Important Considerations

PowerBI and Excel have many similarities when it comes to sourcing data from APIs. Like Excel, PowerBI also relies on PowerQuery Advanced Editor. The queries are written in M code. To access the Advanced Editor, go to the "Home" tab and select "Transform data".

Instructions

1. Once in the Power Query Editor, to add a new API connection select "New Source" → "Blank Query" from the "Home" tab:

2. Then select the "Advanced Editor" option, as demonstrated below:

3. From this point forward, you can edit the "xxx" placeholders in the template below to connect to the API.

4. Note that just like with the Excel example above, you do not need to pull in all the available columns in this request. Rather, the flexibility of the API allows the user to only pull the columns the user needs for the analysis.

5. Note that the first time you add a connection, PowerBI will prompt you to specify the privacy level settings. Please select "ignore privacy levels" and click save. Note that the reason this selection must be made is due to a PowerBI limitation related to POST requests.

6. Note that the first time you add a connection, PowerBI will prompt you to specify how to connect. Chose the url at the bottom of the dropdown menu and click "Connect", as demonstrated below.

7. After several data sources have been added to PowerBI, the user can easily visualize the added tables and specify relationships between these tables through the "Model" pane.

8. Adding relationships between the tables is important for reporting purposes. As a rule, PowerBI automatically deducts the connection between the tables in the data model. However, not all relationships can be automatically identified and it's advisable to verify whether the relationships between the tables are correct and complete. To add a relationship, right click on the object, and select "Manage Relationships" and then click on the "New" option, as demonstrated below.

1.5 Adding a GET method API connection example

The GET API Request passes the parameters in the URL, rather than in the Body. While the general process of adding a GET method API is similar to the examples above, the code in the Power Query Editor will not include the body element.

1.5.1 Excel Template for adding a GET method API connection (GetHoldings)

GET/GetHoldings for Excel Template

1.  let
2.     url = #"GetHoldings url",
3.     BuildQueryString = Uri.BuildQueryString,
4.     Source = Json.Document(Web.Contents(url,[Headers = [
5.        #"Content-Type"="application/json",
6.        #"Authorization" = "Bearer "&Token1,
7.        #"Ocp-Apim-Subscription-Key" = #"Ocp-Apim-Subscription-Key"]] )),
8.     Response = Source[Response],
9.     #"Converted to Table" = Table.FromList(Response, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
10.    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
11.    {
12.     "cusipDescription",
13.     "units",
14.     "price",
15.     "marketValue",
16.     "taxCost",
17.     "unrealGainLoss",
18.     "accruedIncome",
19.     "totalMarketValue",
20.     "priceDate",
21.     "entityName",
22.     "accountName",
23.     "sector",
24.     "industry",
25.     "assetLiquidity",
26.     "LiquidityCategory",
27.     "assetVolatility",
28.     "volatilityCategory",
29.     "geography",
30.     "SubAssetClass",
31.     "SuperAssetClass",
32.     "AssetClass",
33.     "AsOfDateKey",
34.     "Custodian",
35.    }, 
36.    {
37.     "cusipDescription",
38.     "units",
39.     "price",
40.     "marketValue",
41.     "taxCost",
42.     "unrealGainLoss",
43.     "accruedIncome",
44.     "totalMarketValue",
45.     "priceDate",
46.     "entityName",
47.     "accountName",
48.     "sector",
49.     "industry",
50.     "assetLiquidity",
51.     "LiquidityCategory",
52.     "assetVolatility",
53.     "volatilityCategory",
54.     "geography",
55.     "SubAssetClass",
56.     "SuperAssetClass",
57.     "AssetClass",
58.     "AsOfDateKey",
59.     "Custodian"
60.   })
61. in
62.   #"Expanded Column1"

1.5.2 PowerBI Template for adding a GET method API connection (GetHoldings)

GET/GetHoldings for PowerBI Template

1.  let
2.     url = "https://api01devtest.azure-api.net/etonapi/v1/GetHoldings?tenantKey=xxx&portfolioKey=xxx&entityKey=xxx&dataType=holdings&relationshipKey=xxx&asOfDate=xxx&stateFilter=xxx",
3.     BuildQueryString = Uri.BuildQueryString,
4.     Source = Json.Document(Web.Contents(url,[Headers = [
5.        #"Content-Type"="application/json",
6.        #"Authorization" = "Bearer "&Token1,
7.        #"Ocp-Apim-Subscription-Key" = "xxx"]] )),
8.     Response = Source[Response],
9.     #"Converted to Table" = Table.FromList(Response, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
10.    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
11.    {
12.     "cusipDescription",
13.     "units",
14.     "price",
15.     "marketValue",
16.     "taxCost",
17.     "unrealGainLoss",
18.     "accruedIncome",
19.     "totalMarketValue",
20.     "priceDate",
21.     "entityName",
22.     "accountName",
23.     "sector",
24.     "industry",
25.     "assetLiquidity",
26.     "LiquidityCategory",
27.     "assetVolatility",
28.     "volatilityCategory",
29.     "geography",
30.     "SubAssetClass",
31.     "SuperAssetClass",
32.     "AssetClass",
33.     "AsOfDateKey",
34.     "Custodian",
35.    }, 
36.    {
37.     "cusipDescription",
38.     "units",
39.     "price",
40.     "marketValue",
41.     "taxCost",
42.     "unrealGainLoss",
43.     "accruedIncome",
44.     "totalMarketValue",
45.     "priceDate",
46.     "entityName",
47.     "accountName",
48.     "sector",
49.     "industry",
50.     "assetLiquidity",
51.     "LiquidityCategory",
52.     "assetVolatility",
53.     "volatilityCategory",
54.     "geography",
55.     "SubAssetClass",
56.     "SuperAssetClass",
57.     "AssetClass",
58.     "AsOfDateKey",
59.     "Custodian"
60.   })
61. in
62.   #"Expanded Column1"

1.6 Adding a POST method API connection example

The POST API Request passes the parameters through a body. Please refer to Section 1.4 for examples of adding a new API connection that uses the POST method.

1.7 Configuring the body of the API request

Many of the AtlasFive Business APIs are POST requests that allow the user to make targeted requests through specifying the elements in the body of the request. The section below provides examples of how to make use of the body in order to make more precise API requests.

As shown in Section 1.4, the body of the request contains the elements outlined below:

¹ Please refer to section 1.4.1 for details on Option #1 and Option #2

 
As you can see, the elements in the Excel and PowerBI version are the same. The only difference is the fact that in Excel the values in the body are pulled through from the values specified by the user in the Query Customization tab (Option #1), whereas in Excel (Option #2) and PowerBI the values are hardcoded into the M code. The section below will discuss each element in the body and provide examples of how to use it.

Version

The AtlasFive business API version. This version is related to the AtlasFive release version you are currently on.

EntityKey

This is the unique identifier of each entity. The default value is -9999, which will return all the entity values available. However, if you want to make a request for a specific entity, you should replace -9999 with the respective entity key value.

Filters

The filters allow the user to make specific requests. For example, if you have 10 financial statement groups in total, but only want the FSG API to return 1 or 2 specific financial groups, you can specify this in the filters as follows:

1. The default value is null: ""filters"" : null,

2. To edit the query, specify the FSG names in the filters row as follows:

   1 FSG Example:

   1.  ""filters"": "" AND [FS Custom Group] IN ( 'xxx Financial Statement Summary' )"",

   2 FSG Example:

   1.  ""filters"": "" AND [FS Custom Group] IN ( 'xxx Financial Statement Summary', 'Estate Planning Financial Statement' )"",

Sort

This element allows you to specify which column you would like the data to be sorted by. For example, if in the GetCOADetails request, you would like to sort by the GLCategoryCode column in the ascending order for a particular entity, all you need to do is specify the entityKey and replace the default value of the “sort” element with the following:

1.       "sort" : "order by GLCategoryCode asc",

Offset and Limit

All APIs contain are configured to have a maximum amount of data that can be transmitted through 1 API request. Please refer to Section 6 for a list of technical specifications and limitations of the AtlasFive Business APIs. Currently the maximum limit is 99,999 or less or a response size limit of 100 MB. If the dataset contains more than the configured limit, the user must make 2 requests and use the "offset" and "limit" elements of the body to obtain the data needed first, and then concatenate the results into one table using PowerBI, if the user requires all data to be present in one table.

1. The offset element defines the first-row number that must be retrieved by the API.

2. The limit element defines the number of elements following the offset element that must be retrieved by the API.

3. For example: If offset is set to 0 and the limit is set to 99,999, the API will retrieve row number 0 through 99,999 elements of the dataset. If offset is set to 99,999 and limit is set to 99,999, the API will retrieve the row numbers 99,999 through 199,998. Both tables will have 99,999 elements, but the data in each table will be different. This is called pagination and will be discussed in more details in Section 6.

Pagination

Request 1 body

Request 2 body

The example above demonstrates how a user can get all the data required in separate tables. After sourcing the data, the user can use PowerBI to concatenate the data into one table.

 
↑ Back to Top