ARTICLE

How to visualize performance of a domestic vs. foreign asset adjusted for exchange rate using Workspace API

July 16,2017

Gurpreet Bal

Visualize performance of a domestic vs. foreign asset adjusted for exchange rate

This article demonstrates how a user can use LD Library for Python to quickly prototype and plot custom analytics which are not readily available within Workspace desktop. The example shown here is one of the many use cases, that Quants/Analysts can employ to analyze financial data.

LD library for Python

LSEG provides an ease of use python library for accessing market data, timeseries, fundamental and reference data and news. This library connects to the local instance of Workspace and runs with user's Workspace credentials, and provides an easy data retrieval interface. The library can also be used in the Cloud hosted LSEG CodeBook environment.

For more information on the LSEG Data library for Python , please visit the LSEG Developers Portal.

Application Sample

The goal of this article is to demonstrate how easy it is to perform a simple analysis and plot it on a chart. This what-if analysis compares the return of a foreign asset against a domestic asset, if the same dollar amount was invested in both; taking into account the currency exchange rate during the comparison period. The term domestic is used loosely here and this approach can be used to compare any two assets, trading in any currencies. The sample accomplishes this by getting the trade currency of both the assets, and then requesting the calculated cross rate for this currency pair. Timeseries of price history for both assets are first expressed in units of domestic currency and then rebased to the start of the comparison period. The rebased data is then plotted on a line chart.

The accompanying sample compares the performance of a Canadian bank - TD (TD.TO), trading on Toronto Stock Exchange in Canadian Dollars with British bank - Barclays (BARC.L), trading on London Stock Exchange in Pound Sterling.

Dependencies

To try the code provided in this article, a setup and familiarity with following technologies is required:

Python language
JSON
Pandas Data Analysis library
Jupyter (iPython) Notebooks
Plotly (or Cufflinks) Charting library

Implementation

Following steps are executed to create the comparison chart:

Define domestic and foreign instruments and a time range for comparison. Since the performance data is normalized, any one of the two instruments can be considered as domestic and the other as foreign, and it will produce the same output.
Get the trading currency for both instruments. In this example we use "TR.CompanyMarketCap.Currency" field, which generally provides the currency of the main equity issue of the company on the main listing exchange.

    	
            data = ld.get_data([instr1, instr2], "TR.CompanyMarketCap.Currency")

3. Create a cross-currency exchange rate RIC and get historical data for the given time range. The cross rate is calculated by LSEG for most currency pairs. This RIC is formed by appending =R to both currencies. For e.g. Calculated cross rate RIC for CAD vs GBP would be "GBPCAD=R".

    	
            # cross rate ric = curr1curr2=R. It is a calculated metric

crossRIC = data['Currency'][1] + data['Currency'][0] + '=R'

curr = ld.get_history([crossRIC], fields='MID_PRICE', interval='1D', start=start_date, end=end_date)

4. Get historical data (CLOSE Price) for both instruments for requested time range.

5. Convert the price history of the foreign asset into domestic currency using the cross-currency rate we retrieved earlier.

    	
            # create a single DataFrame with both data series (useful if using cufflinks to plot instead of plotly)
perf = pd.concat([perf1, perf2 * curr], axis=1)
perf.columns = [instr1, instr2]
# drop invalid entries from dataframe
perf = perf.dropna()

6. Rebase the data to the start of the time period, so that initial investment in both instruments shows up as 100%.

    	
            # rebase both timeseries to 100% at start date
perf_rb = perf * 100 / (perf[instr1][0], perf[instr2][0])

7. Plot a double line chart. The two chart lines will show the % gain/loss on an investment in both instruments in domestic currency.

    	
            # using offline plotly for charting
offline.iplot([{
    'x': perf_rb.index,
    'y': perf_rb[col],
    'name': col
}  for col in perf_rb.columns])

Simplification

The code sample above breaks the implementation into multiple steps for clarification and to show as an example, how various data and timeseries can be retrieved using Eikon API. A simple implementation of this code would be:

    	
            # get and rebase the data

response = ld.get_data([instr1, instr2], fields=['TR.CLOSEPRICE(Curn=CAD)', 'TR.CLOSEPRICE.date'], parameters={'SDate':start_date, 'EDate':end_date, 'Frq':'D'})

df = response.pivot_table(values='Close Price', index=['Date', 'Instrument']).unstack('Instrument').dropna()

rebased = df.apply(lambda series: series/series[0]*100)



# plot it in a chart

offline.iplot([{

    'x': rebased.index,

    'y': rebased[column],

    'name': column[1]

} for column in rebased.columns])

Output

The resulting chart shows that an equal Canadian dollar amount invested in TD on Jan 2nd 2015 and liquidated on Jan 3rd 2017 would yield 21% gain vs. 15% loss on investment in Barclays, when factoring in the currency movement.

See the attached iPython notebook for complete working sample.

Related samples

LSEG github samples repository has many more samples and snippets of code showing other uses of LD Library. Just search for python or LD Library within repositories.