PO Foreign Exchange Rate - Lookup Rates Online

Owned by Paul Hitchcock
Last updated: Jul 16, 2020
3 min read

When entering in a foreign exchange rate you can configure a lookup to query an online source for exchange rates. To lookup the rate select Base and Foreign Currencies and a Date and click ‘Lookup Rate’

The following settings are used to configure the lookup. Each setting is a value in the Settings table and can be modified on the ‘All’ tab in ‘Options’. If no URL has been configured, the Lookup Rate link will not be visible. See examples below.

  • ExchangeRateLookup_URL - This is the URL that will be used to lookup the rate. You can use the following tags in the URL which will be replaced in the URL prior to querying the source.

    • <basecurrency> - Will be replaced with the ISO Currency Symbol for the selected Base Currency.
      Example: USD=U.S. Dollars, CAD=Canadian Dollars

    • <foreigncurrency> - Will be replaced with the ISO Currency Symbol for the selected Foreign Currency.

    • <date> - Will be replaced with the selected date using the format. You can use an adjusted date in the URL by adding a plus or minus after the word date followed by an integer. For example the following tag will use the selected date minus 3 days
      <date-3>
      You can also force a custom date format by following the word date with a colon and a custom date format string as in the following example. If no format is provided the default of yyyy-MM-dd (four digit year, 2 digit month and 2 digit day) will be used.
      <date:yyyy-MM-dd>
      You can combine both the date adjustment and format if needed, see the following example:
      <date-5:yyyy-MM-dd>

  • ExchangeRateLookup_ResponseFormat - The expected response format from the URL. Accepted values are XML or JSON.

  • ExchangeRateLookup_ResponsePath - The path used to select the value from the http response body based on the ResponseType

    • JSON - The response body will be parsed to a JObject using Newtonsoft JSON libraries and the JObject.SelectNode function will be used with this response path to retrieve the exchange rate from the body of the response.

    • XML - The response body will be parsed to an XmlDocument using System.XML libraries and the SelectSingleNode function will be used with this response path to retrieve the exchange rate from the body of the response.

JSON Example

Using the following settings will use the Valet service provided by Bank of Canada and will return daily exchange rates between the selected date and 30 days prior to the selected date. The rates are returned in chronological order from oldest to newest so we write the response path using JSONPath syntax to find the last observation and grab the value for the property 'v' which holds the decimal value of the exchange rate.

Setting Values in Crows Nest:

ExchangeRateLookup_URL: https://www.bankofcanada.ca/valet/observations/FX<basecurrency><foreigncurrency>/json?start_date=<date-30>&end_date=<date>
ExchangeRateLookup_ResponseFormat: JSON
ExchangeRateLookup_ResponsePath: $.observations[-1:]..v

XML Example

Using the following settings will use the Valet service provided by Bank of Canada and will return daily exchange rates between the selected date and 30 days prior to the selected date. The rates are returned in chronological order from oldest to newest so we write the response path using XPath syntax to find the last observation and grab the value for the property 'v' which holds the decimal value of the exchange rate.

Setting Values in Crows Nest:

ExchangeRateLookup_URL: https://www.bankofcanada.ca/valet/observations/FX<basecurrency><foreigncurrency>/xml?start_date=<date-30>&end_date=<date>
ExchangeRateLookup_ResponseFormat: XML
ExchangeRateLookup_ResponsePath: //o[last()]/v