Part 3: Extend your reach

In Part 3 of the tutorial you use a Robots script with the Python Requests library to connect to the HighBond API. You also connect to two different third-party APIs – one that returns the dates of public holidays, and another that returns currency exchange rates.

The Requests library is a Python component that allows you to use Python to send API requests and receive responses. You can use it to interact with any REST API.

Note

You must have created the robot in Part 1, and the script in Part 2, before you can do the initial portion of Part 3. The latter portion of Part 3 (using Python with a third-party API) does not have any prerequisites.

What will I learn?

You'll learn how to:

  • use the Python Requests library as an alternate way for making HighBond API requests

  • use the Python Requests library to interact with a third-party API and bring real-world data into a Robots script

Use the Python Requests library with the HighBond API

We'll begin by using the Python Requests library to replicate part of what you have already achieved using HCL HighBond API methods.

Generally, we recommend using HCL methods to interact with the HighBond API because the required code is simpler. We'll use the Requests library in this case for two reasons:

  • You can begin to learn about the Requests library in a now familiar context before moving on to using Requests with a third-party API.

  • If you're experienced with Python and Requests, you may prefer to continuing using a familiar approach.

HCL methods can only be used with the HighBond API. You must use Requests for connecting from a Python/HCL script to a third-party API.

Retrieve your HighBond API token and update the password variable

Sign in to Diligent One

If you are currently signed out, sign in to Diligent One.

  1. Go to www.highbond.com.
  2. Enter your Diligent One credentials (email and password).
  3. Click Sign in.
  4. If your company uses more than one instance in Diligent One, make sure the appropriate instance is active.

Retrieve your Diligent One API token

  1. To use an existing token, access the token you saved in a secure place for future use.

  2. Optional. You can also create a new token if the token you previously used is no longer available. For more information, see Create a HighBond API token in Launchpad.

Update the password variable

  1. Open the Robots app in a separate tab:

    1. Click Switch Apps Switch Apps icon.

    2. Right-click Robots and select Open link in new tab.

  2. Switch to the browser tab where Robots is open.

  3. Open the robot that you created in Part 1 of the tutorial.

  4. In the upper-right corner of the robot, click Development to switch to development mode.

  5. In the Script versions tab, select the most recent script version, and in the Version details panel click Edit script.

    The Robots script editor begins the start-up process.

  6. From the Robots script editor, click Manage variables .

  7. In the Variables window, paste your HighBond API token into the Value field for the v_hb_token variable.

  8. Click Save and close.

    Result The variable is updated and you are returned to the script editor.

    Note

    As a security measure, any time you exit or refresh the script editor your HighBond API token is automatically deleted from the password variable. You must re-paste a valid token into the variable each time you reopen or refresh the script editor if you want to run a script that uses the HighBond API.

    If you forget this step, you get an error message when you run the script:

    HTTPError: 401 Client Error: Unauthorized for url: <url>

Update the existing Robots script to use Requests

You don't need to change much in the existing script in order to use Requests. We'll continue to use HCL methods in the script cells that find or create a Results collection and an analysis. We'll use Requests methods in the cell that finds or creates a Results table.

In the script, make all the changes that are listed in the table below.

Note

Did you retrieve your HighBond API token and update the password variable?

If you get a Python error (a red error block) at any point, see Troubleshooting.

Cell # Instructions
Cell 1

Import required components

In cell 1, update the code to import two additional Python libraries:

  • Requests

  • JSON

  1. Replace the code in cell 1 with the code block below.

  2. Run the cell.

There is no visible output when you run this cell.

# CELL 1

# Import the HighBond Command Language (HCL) custom Python library
import hcl

# Import the date, datetime, and timedelta classes from the Python datetime module
from datetime import date, datetime, timedelta

# Import the Python Requests library
import requests

# Import the Python JSON library
import json
Cell 2

Define the variables used in the script

  1. In cell 2, update Test table 2 (line 16) to Test table 3 :

    v_table_name = f"Test table 3 ({current_month})"

  2. Add two new lines after v_collection_id = None (line 23), and define new variables for your HighBond API URL and your org ID. Put each variable definition on a separate line.

    HighBond API URL 

    v_api_url = "your HighBond API URL"

    Replace your HighBond API URL with your actual HighBond API URL. For example:

    v_api_url = "https://apis-us.highbond.com"

    Note

    HighBond API URLs differ by Diligent One region. To find the URL that matches your region, see API regions in the HighBond API Reference.

    Org ID 

    v_org_id = "your org ID"

    Replace your org ID with your actual org ID. For example:

    v_org_id = "11594"

    Tip

    In the URL in the address bar at the top of the script editor, your actual org ID is the first number in the URL, after /orgs/ .

  3. Run the cell.
Cell 3

Find or create a collection in Results

  1. Do not change anything.

  2. Run the cell.
Cell 4

Find or create an analysis in Results

  1. Do not change anything.

  2. Run the cell.
Cell 5

Create a table in Results

In cell 5, replace the code with the code block below.

Two Python Requests methods make the API requests:

  • requests.get – gets a list of all tables in the analysis

  • requests.post – if the table name you specified does not exist in the analysis, creates a table with that name

Read the comments in the code for a detailed explanation of how we're using the Python Requests library.

  1. Replace the code in cell 5 with the code block below.

  2. Run the cell.

# CELL 5

# Find a table with the name specified in v_table_name. If the table doesn't exist, create it.

# Define the variables to use with the Python Requests methods

# Assign the HighBond API request URL to a variable
url = f"{v_api_url}/v1/orgs/{v_org_id}/analyses/{v_analysis_id}/tables"

# Assign a Python dictionary containing the request payload to a variable
# The payload specifies the information needed to create a Results table
payload = {
  "data": {
    "type": "tables",
    "attributes": {
      "name": v_table_name,
      "description": f"Description of {v_table_name}",
      "table_type": "data_analytic",
    }
  }
}

# Convert the Python dictionary to a JSON string, the required format for the payload in the Python Requests POST method
payload = json.dumps(payload)

# Assign your HighBond API token to a Python variable
token = hcl.secret["v_hb_token"].unmask()

# Assign the request header values to a variable
headers = {
  'Content-Type': 'application/vnd.api+json', # requires this custom header rather than application/json
  'Authorization': 'Bearer ' + token
  }

# Use the Python Requests GET method to get a list of all tables in v_analysis_id
tables = requests.get(url, headers=headers)

# Convert the list from a JSON string to a Python dictionary
tables_dict = tables.json()

# Loop through the tables in the tables dictionary to search for v_table_name
# If the name exists, assign its unique ID to v_table_id
for table in tables_dict["data"]:
    if table['attributes']['name'] == v_table_name:
        v_table_id = table['id']
        print(f"The ID of the existing table is {v_table_id}")

# If v_table_name does not exist, use the Python Requests POST method to create a table with that name in Results
# Use the Python Requests GET method to refresh the tables dictionary, and assign the unique ID of the newly created table to v_table_id
if v_table_id == None:
    requests.post(url, headers=headers, data=payload)
    tables = requests.get(url, headers=headers)
    tables_dict = tables.json()
    for table in tables_dict["data"]:
        if table['attributes']['name'] == v_table_name:
            v_table_id = table['id']
            print(f"The ID of the newly created table is {v_table_id}")
Cell 6

Perform some data analysis

  1. Do not change anything.

  2. Run the cell.
Cell 7

Save the output of the data analysis to the Results table

  1. Do not change anything.

  2. Run the cell.

Check Results for the new table

Let's check the Results app to confirm that using Python Requests to create a new table was successful.

  1. Open the Results app in a separate tab:

    1. Click Switch Apps Switch Apps icon.

    2. Right-click Results and select Open link in new tab.

  2. Switch to the browser tab where Results is open.

  3. Open the collection that you created in Part 2 of the tutorial.

    Result The table that you just created using the Requests library and the HighBond API is present, nested inside the analysis that you created in Part 2 of the tutorial. The table contains the output results that you just saved.

    Note

    Depending on the current usage levels of Results there may be a delay in the output results populating the table.

  4. Enable Show table IDs, if it is not already enabled.

    The table ID displays to the left of the table name. It should be the same ID that was created and displayed when you ran the script cell to create the table.

  5. Click the table name to open the table.

    The output results that you saved from Robots to Results are displayed.

  6. Switch back to the browser tab where Robots is open.

Restore the previous table name

If you're still running the scheduled task that you created in Part 2 of the tutorial, you may want to restore the previous table name in the script.

In cell 2, line 16, update the name to Test table 2 , or whatever name you previously used:

v_table_name = f"Test table 2 ({current_month})"

Save the script and exit the script editor

  1. In Robots, on the script editor tool bar, click Save and commit.

  2. Enter a commit message such as Updated script to use Python Requests library.

  3. Click Commit to save and commit the script.

    The message Your script has been committed successfully appears.

  4. On the page header, click the robot name (Tutorial robot <your initials>).

    Result You are returned to the Script versions tab in the robot. Every time that you commit and save a script, the saved version is added to this tab.

Use the Python Requests library with a third-party API

Okay, you've seen the Python Requests library in action. You're now ready to take the final step in the tutorial: use Robots, Python, and Python Requests to interact with a third-party API and bring some real-world data into a Robots script.

The APIs we'll connect to

We'll use two different publicly available APIs to practice connecting:

  • Nager.Date – a repository containing the dates of public holidays, by year, in over 100 countries

  • Open Exchange Rates – a currency data API that provides exchange rates in USD for 170 currencies

Authentication

Like the HighBond API, most public APIs require that you authenticate yourself using credentials whenever you send a request to the API. Nager.Date does not require authentication, so we'll use that first as the simplest option. Open Exchange Rates does require authentication. The process to create a free account and acquire an authentication token is straightforward.

Create a new Robots script to use Requests

Let's create a new robot with a new script to contain your practice with Python Requests and third-party APIs.

Sign in to Diligent One

Note

If you're already signed in, and in the Robots app, skip to Create a new robot.

  1. Go to www.highbond.com.
  2. Enter your Diligent One credentials (email and password).
  3. Click Sign in.
  4. If your company uses more than one instance in Diligent One, make sure the appropriate instance is active.
  5. Open the Robots app.

Create a new robot

  1. In the Dashboard in Robots, select HighBond Robots and click Create a HighBond robot.

  2. Enter a name for the robot such as API practice robot <your initials> and click Create a HighBond robot.

    Result The Robots script editor in the new robot begins the start-up process. You cannot run a script, or a script cell, until the start-up process is complete.

Connect to Nager.Date

We'll connect to the Nager.Date API and return the dates and other associated information for all the public holidays for a specified year and country. You sometimes need the dates of public holidays when performing analysis on transactional data.

Conversion to a Pandas dataframe

The data is returned in JSON format. To make the data easier to visually scan, we'll convert it from JSON to a Pandas dataframe. Be aware, however, that using the returned data in a script does not require converting it to a dataframe. Depending on the logic of a script, using a dataframe may or may not be the best approach.

Add code blocks to the new script

Copy and paste each code block in the table below into a separate cell in the script editor.

Note

If you get a Python error (a red error block) at any point, see Troubleshooting.

Cell # Instructions
Cell 1

Import required components

The code in cell 1 imports two Python libraries:

  • Requests

  • Pandas

Copy and paste this code block into cell 1 in the script editor, and then run the cell.

There is no visible output when you run this cell.

# CELL 1

# Import the Python Requests library
import requests

# Import the Pandas library
import pandas as pd
Cell 2

Make an API request

In cell 2, connect to the Nager.Date API and return public holidays for the specified year and country.

  1. Create a second cell in the script editor, and copy and paste the code block below into the cell.

  2. Run the cell.

# CELL 2

# Connect to the Nager.Date API and return the public holidays for the specified year and country
response = requests.get("https://date.nager.at/api/v3/publicholidays/2021/US")

# Convert the list of holidays from a JSON string to a Pandas dataframe
public_holidays = pd.read_json(response.text)

# Display the public holidays dataframe
public_holidays

Example of the Nager.Date API response

If you didn't update the GET request, all the public holidays in the United States for 2021 are returned. In addition to the actual date on which the holiday falls, other useful information is returned, such as which jurisdictions observe holidays that are not observed in the entire country.

Learn more

Update the GET request (cell 2, line 4) to return public holidays for a different year or country. In addition to the current year, you can return data for either previous or future years.

For example, this GET request returns holidays in Brazil for 2020:

response = requests.get("https://date.nager.at/api/v3/publicholidays/2020/BR")

Tip

Consult the Nager.Date web site for the supported country codes.

Connect to Open Exchange Rates

Next we'll connect to the Open Exchange Rates API and return the latest exchange rates for all the currencies supported by the API. You may require exchange rates when performing analysis on financial data.

Open Exchange Rates returns point-in-time exchange rates, with the source updated hourly.

Conversion to a Pandas dataframe

The data is returned in JSON format. To make the data easier to visually scan, we'll convert it from JSON to a Pandas dataframe. Be aware, however, that using the returned data in a script does not require converting it to a dataframe. Depending on the logic of a script, using a dataframe may or may not be the best approach.

Create a free Open Exchange Rates account

First you need to create a free Open Exchange Rates account and retrieve your authentication token.

  1. Go to the Open Exchange Rates sign up page for a free account:

    https://openexchangerates.org/signup/free

  2. Follow the on-screen instructions to create your account.

  3. Once you have created your account, go to the App IDs page and copy your App ID (authentication token).

Create a password variable for the Open Exchange Rates token

Before you begin building the script, create a password variable for the Open Exchange Rates token.

  1. From the Robots script editor, click Manage variables .

  2. In the Variables window, specify the following values to create the password variable:

    Field/option Value
    Name

    Specify v_oxr_token.

    Unlike v_hb_token, you can specify any variable name that you want. However, you need to specify v_oxr_token to work with the supplied code blocks in the tutorial.
    Type

    Select Password.
    Value

    Paste your Open Exchange Rates token from the clipboard into the field.
    Task input Automatically enabled when you select Password.
    Task input label

    Specify Enter your Open Exchange Rates token:
    User input required Leave selected.
    Task input description Optional. You can leave the description blank.
  3. Click Save and close.

    Result The variable is saved and you are returned to the script editor.

    Note

    As a security measure, any time you exit or refresh the script editor the token is automatically deleted from the variable. You must re-paste a valid token into the variable each time you reopen or refresh the script editor if you want to run a script that uses the variable.

    If you forget this step, you get an error message when you run the script.

Add code blocks to the existing script

Copy and paste each code block in the table below into a separate cell in the script editor.

Note

If you get a Python error (a red error block) at any point, see Troubleshooting.

Cell # Instructions
Cell 3

Make an API request

In cell 3, connect to the Open Exchange Rates API and return the most recent rates.

  1. Add a third cell in the script editor, and copy and paste the code block below into the cell.

  2. Run cell 1.

    Note

    You need to re-run cell 1 because the script editor session memory was cleared when you saved the v_oxr_token variable.

  3. Run cell 3.

# CELL 3

# Assign your Open Exchange Rates token to a Python variable
oxrtoken = hcl.secret["v_oxr_token"].unmask()

# Connect to the Open Exchange Rates API and return the latest exchange rates for all currencies relative to USD
response = requests.get(f"https://openexchangerates.org/api/latest.json?app_id={oxrtoken}")

# Convert the list of rates from a JSON string to a Python dictionary
response_dict = response.json()

# Convert the Python dictionary to a Pandas dataframe
latest_rates = pd.DataFrame.from_dict(response_dict)

# Select a subset of columns to display in the dataframe
latest_rates = latest_rates[["timestamp", "base", "rates"]]

# Set the dataframe option for maximum rows to display
pd.set_option('max_rows', 200)

# Set the dataframe option for the number of decimal places to display for the float data type
pd.set_option('display.float_format', lambda x: '%.5f' % x)

# Copy the dataframe to a new dataframe and add a numeric index column
latest_rates_2 = latest_rates.reset_index().rename(columns = {"index": "currency code"})

# Display the new dataframe
latest_rates_2
Cell 4

Assign returned information to variables

In cell 4, assign specific rates to variables. You could then use the variables in subsequent data analysis that requires converting between currencies.

  1. Add a fourth cell in the script editor, and copy and paste the code block below into the cell.

  2. Run the cell.

# CELL 4

# Import the date and datetime classes from the Python datetime module
from datetime import date, datetime

# Assign the Open Exchange Rates UTC timestamp to a variable
v_oxr_utc_timestamp = response_dict['timestamp']

# Convert the UTC timestamp (in seconds) to a regular timestamp
v_timestamp_converted = datetime.fromtimestamp(v_oxr_utc_timestamp)

# Assign specific exchange rates to variables
v_cny_rate = response_dict['rates']['CNY']
v_eur_rate = response_dict['rates']['EUR']
v_gbp_rate = response_dict['rates']['GBP']
v_mxn_rate = response_dict['rates']['MXN']

# Connect to the Open Exchange Rates API and return all currency names
currency_name = requests.get("https://openexchangerates.org/api/currencies.json")

# Convert the list of currency names from a JSON string to a Python dictionary
currency_name_dict = currency_name.json()

# Assign specific currency names to variables
v_usd_name = currency_name_dict['USD']
v_cny_name = currency_name_dict['CNY']
v_eur_name = currency_name_dict['EUR']
v_gbp_name = currency_name_dict['GBP']
v_mxn_name = currency_name_dict['MXN']

# Display the specified exchange rates and currency names
print(f"EXCHANGE RATES ({v_timestamp_converted})")
print(f"1 {v_usd_name} =")
print(f"{v_cny_rate} {v_cny_name}")
print(f"{v_eur_rate} {v_eur_name}")
print(f"{v_gbp_rate} {v_gbp_name}")
print(f"{v_mxn_rate} {v_mxn_name}")

Example of cell output

EXCHANGE RATES (2021-07-23 20:00:00)
1 United States Dollar =
6.4814 Chinese Yuan
0.849479 Euro
0.727298 British Pound Sterling
20.066332 Mexican Peso

Learn more

To return only a subset of the available exchange rates, add a query string parameter to the exchange rate GET request (cell 3, line 7):

&symbols=comma-separated list of ISO currency codes

For example, this GET request returns the exchange rates for only five currencies: Chinese Yuan, Euro, British pound, Mexican peso, and US dollar:

response = requests.get(f"https://openexchangerates.org/api/latest.json?app_id={oxrtoken}&symbols='CNY','EUR','GBP','MXN','USD'")

Example of the Open Exchange Rates API response

If you used the currency codes in the example above, you now get a dataframe that contains only the exchange rates you specified.

Save the script and exit the script editor

Save your final tutorial work and exit the script.

  1. In Robots, on the script editor tool bar, click Save and commit.

  2. Enter a commit message such as Created script to use Python Requests library with third-party APIs.

  3. Click Commit to save and commit the script.

    The message Your script has been committed successfully appears.

  4. On the page header, click the robot name (API practice robot <your initials>).

    Result You are returned to the Script versions tab in the robot. Every time that you commit and save a script, the saved version is added to this tab.

What you learned

Congratulations! You've used native scripting in Robots with the Python Requests library to extend your reach beyond Diligent One.

Specifically, you have learned how to:

  • use the Python Requests library as an alternate way for making HighBond API requests

  • use the Python Requests library to interact with a third-party API and bring real-world data into a Robots script

What's next?

Venture out into the world

Venture out into the world of publicly available APIs. You now know the essential process for connecting from Robots to a REST API. There are thousands of APIs out there and some of them are likely to interest you, or contain information that can help move your automation goals forward. Many APIs are fee-based, but many also offer a free option with reduced functionality so that you can try them.

Tip

Download an API client, such as Postman, or Insomnia, so that you can test API requests and responses independent of Diligent One. Postman includes a useful feature for auto-generating the Python code for a particular request.

Set up dynamic reporting

Some of your organization's enterprise systems may also have APIs that allow you to pull data for purposes such as reporting. The techniques you've learned in this tutorial provide a good starting point for setting up an automated reporting pipeline – one that runs from an enterprise system through an API to Robots, Results, and Storyboards.

Work collaboratively

If you don't currently have coding skills there may be others in your organization who do, and are willing to help. IT personnel and system administrators can often assist with the process of authenticating against enterprise systems. Build relationships with experts, and learn as you go.

 

Learn more

Learn more about HCL and scripting in Robots:

Troubleshooting

If you get a Python error (a red error block) when running a cell or the entire script, perform the troubleshooting procedure below.

  1. In the script editor, click Manage variables and double-check the following:

    • Use the Python Requests library with the HighBond API

      Check that the password variable v_hb_token exists and that you have supplied a token in the Value field.

      The token must be a HighBond API token, not an Analytics token. To make sure, re-copy an appropriate token from the Manage API tokens screen in Launchpad and paste it into the Value field.

    • Use the Python Requests library with a third-party API

      Check that the password variable v_oxr_token exists and that you have supplied a token in the Value field.

      The token must be a valid Open Exchange Rates App ID (authentication token). To make sure, re-copy an appropriate token from the Open Exchange Rates App IDs page and paste it into the Value field.

    Tip

    Remember, if you exit or refresh the script editor, the value of any password variable is automatically deleted and must be re-specified.

  2. If a password variable is completely absent, complete all required aspects of the password variable definition.

    For more information, see:

  3. Click Save and close.

    Result All variables are saved and you are returned to the script editor.

  4. On the script editor toolbar, click Run all cells sequentially .

    Result If no errors remain, the script runs from the first cell to the last.

    Note

    When you save and close the Variables window, the script editor session memory is cleared. Re-running all the cells sequentially ensures that all required Python libraries are imported, and that all prerequisite operations required by subsequent cells are performed.