Article

How to integrate Financial Data from Refinitiv Data Platform to Excel with Xlwings - Part 1

October 22,2020

Wasin Waeosri

Felix Zumstein

Overview

With rise of Data Scientist, Financial coder or Trader (aka Citizen Developers) and rapid growth of Jupyter application, the main target of every Citizen Developers are replacing Microsoft Excel with Jupyter application (reference: Jupyter is the new Excel).

However, Excel is not obsolete and is still an important file-format/application for businesses. It is easy to distribute, and non-IT people (especially your boss) can open it easily rather than having to setup a Jupyter/Python environment.

This article is the first part of the series that demonstrate how to export financial data and report from Python/Jupyter application to Excel report file using xlwings CE and xlwings Pro libraries. The demo application uses content from Refinitiv Data Platform (RDP) as an example data.

Note: All figures and reports in the demonstration use Time-Series 90 days data that was queried on 14th October 2020.

Introduction to xlwings

xlwings is a Python library that makes it easy to call Python from Excel and vice versa. The library lets you automate Excel from Python source code to produce reports or to interact with Jupyter notebook applications, replacing VBA macros with Python Code, write UDFs (user-defined functions - Windows only), and remote-control Excel via the REST API.

The xlwings CE is a free and open-source library (BSD-licensed) which provides basic functionalities to lets developers integrate Python with Excel.
The xlwings PRO provides more advanced features such as reports, embedded Python code in Excel, video training, a dedicated support channel, and much more.

Please refer to Financial Reporting with Eikon, xlwings and Excel article which give detail regarding how to integrate xlwings with Eikon Data API.

Introduction to Refinitiv Data Platform (RDP) Libraries

Refinitiv provides a wide range of contents and data which require multiple technologies, delivery mechanisms, data formats, and the multiple APIs to access each content. The RDP Libraries are a suite of ease-of-use interfaces providing unified access to streaming and non-streaming data services offered within the Refinitiv Data Platform (RDP). The Libraries simplified how to access data to various delivery modes such as Request-Response, Streaming, Bulk File, and Queues via a single library.

Depending on the level of service and data requirements, developers can easily choose how to access data services using the appropriate access channel defined within the library.

With the library, developers can access content from all 3 of the access points - all from within the same application if required. The RDP Libraries are available in the following programming languages:

Refinitiv Supported Editions: Python and TypeScript/JavaScript (coming in 2020)
Community-based Edition: C#

For more deep detail regarding the RDP Libraries, please refer to the following articles and tutorials:

Disclaimer

As these articles are based on alpha version 1.0.0.a5 of the Python library, the method signatures, data formats, etc are subject to change.

Code Walkthrough

Import xlwings and RDP Libraries

Lets start with xlwings CE first. The application needs to import xlwings and refinitiv.dataplatform packages in order to interact with the xlwings CE and RDP library.

    	
            # import xlwings and RDP libraries
import xlwings as xw
import refinitiv.dataplatform as rdp
 
# import all required libraries
import datetime
import configparser as cp
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib
import matplotlib.ticker as tick
import json

Initiate and Getting Data from RDP Libraries

The RDP Libraries let application consumes data from the following platforms

DesktopSession (Eikon/Refinitiv Workspace)
PlatformSession (RDP, Refinitiv Real-Time Optimized)
DeployedPlatformSession (deployed Refinitiv Real-Time/ADS)

This article is focusing on the PlatformSession only. However, the main logic for other session types is the same when interacts with xlwings library.

    	
            # Open RDP Platform Session
session = rdp.open_platform_session(
    <app_key>, 
    rdp.GrantPassword(
        username = <rdp_username>, 
        password = <rdp_password>
    )
)
session.get_open_state() # Result: <State.Open: 3>

Once the application successfully creates a session with RDP, we can request Time-Series data as an example with the RDP Libraries Function Layer.

    	
            # Declare parameter(s)
universe = 'VOD.L'
historical_title = '%s Historical Data' % (universe)
 
df_historical = rdp.get_historical_price_summaries(
    universe = universe,
    interval = rdp.Intervals.DAILY,
    count = 90,
    fields = ['BID','ASK','OPEN_PRC','HIGH_1','LOW_1','TRDPRC_1','NUM_MOVES']
    )
df_historical

Example Data:

Interact with xlwings CE

With xlwings, we can export this df_historical DataFrame to Excel directly. The first step is to initiate the xlwings object and establish a connection to a workbook.

    	
            wb = xw.Book() # Creating an new excel file. wb = xw.Book(filename) would open an existing file

The above step initiates an xlwings workbook class in wb object. The wb = xw.Book() statement creates a new excel file. If you are using wb = xw.Book(filename), the xlwings will open an existing file.

Next, instantiate the xlwings sheet object. The application will interact with the Excel file mostly via this sheet object.

    	
            # Select the first excel sheet, and rename it
 
historical_sheet = wb.sheets[0]
historical_sheet.name = historical_title

Then you can just pass your DataFrame object to sheet.range(<cell>).value property to set Pandas DataFrame to Excel file directly.

    	
            # Set historical_sheet dataframe to cell A1
 
historical_sheet.range("A1").value = df_historical.head(30)

The result is following:

That is, the application is ready to get and export Refinitiv Data to the excel file.

More Interaction and Customization with xlwings CE

The above example shows how to export data "as is" which often hard to read. The application can use xlwings API to customize excel report look and feel, and then customize Pandas DataFrame to make data easier to understand.

    	
            # Clear current sheet 
historical_sheet.clear()

Then we use the xlwings Sheet object and its API property to change the report look and feel.

Note: The xlwings API property supports Windows only. For MacOS, please refer to this page.

    	
            historical_sheet.range("A1").value = historical_title
historical_sheet.range("A1").api.Font.Size = 14 # Change font size
historical_sheet.range("A1").api.Font.ColorIndex = 2 # Change font color
historical_sheet.range('A1:H1').color = (0,0,255) # Change cell background color

The above statements create the following excel report format.

Next, we restructure df_historical DataFrame to make it easier to read by naming the index column to "Date"

    	
            df_historical.index.name = 'Date'
df_historical.head(5)

Next, we set the DataFrame object to A2 cell, then set column header font and background color to make them distinguishable from data.

    	
            historical_sheet.range("A2").value = df_historical.head(30)
 
#Make Column headers bold
historical_sheet.range('2:1').api.Font.Bold = True
# Change cell background color
historical_sheet.range('A2:H2').color = (144,238,144) 
# Set sheet autofit the width of row
historical_sheet.autofit('r')

The result is the following readable report table:

Plotting a Graph

The xlwings CE also supports Matplotlib library's figures in Excel as a picture. Before we plot a graph, we need to transform and re-format our data in DataFrame object first.

We begin by changing all non-Date columns data type from String to Float, then change the DataFrame Date index to be a data column. This will let us plot a graph using Date as X-Axis.

    	
            for column in df_historical:
    df_historical[column]=df_historical[column].astype(float)
 
df_historical.reset_index(level=0, inplace=True)
df_historical.head(5)

Next, we sort data in ascending order and plot a graph with pyplot library.

    	
            # Plotting a Graph
 
columns = ['OPEN_PRC','HIGH_1','LOW_1','TRDPRC_1']
 
df_historical.set_index('Date',drop=True,inplace=True)
fig = plt.figure() # Create Figure
 
# Create graph title from Company and RIC names dynamically.
plt.ticklabel_format(style = 'plain')
plt.title('VOD.L interday data for last 90 days', color='black',fontsize='x-large')
ax = fig.gca()
 
df_historical.plot(kind='line', ax = fig.gca(), y=columns, figsize=(14,7) )
plt.show()

The above plt.show() function just creates a figure object, and then plots and shows a line graph in Jupyter Notebook. We can use the xlwings pictures API to export this figure object into the Excel sheet as a picture.

We will add a graph at the end of data table in our Excel sheet, so we need to get the position of the last row of report table with xlwings end() function.

The sheet.cells.last_cell statement returns lower right cell
The sheet.cells.last_cell.row statement returns row of the lower right cell

    	
            # historical_sheet.cells.last_cell.row = row of the lower right cell
 
'''
change to your specified column, then go up until you hit a non-empty cell
'''
historical_last_row = historical_sheet.range((historical_sheet.cells.last_cell.row, 1)).end('up').row

After we have the position of the last row in historical_last_row variable, mark the position of the picture at the last row of the table + 3 rows row down to make some space between data table and graph. Finally, we add the figure object as a picture to the xlwings CE sheet object with pictures API at the marked position.

    	
            rng = historical_sheet.range('B{row}'.format(row = historical_last_row + 3))
 
historical_sheet.pictures.add(fig, name='MyPlot', update=True, top=rng.top, left=rng.left)

The result is following:

Adding New Sheet

An Excel file can contain many reports created from different data to help make business decisions, xlwings lets you create a new sheet and export data into it dynamically via Python application by using Sheet object add() function.

    	
            esg_sheet_title = '%s ESG Data' % (universe)
# Create new sheet for ESG Data
wb.sheets.add(esg_sheet_title)

The above xlwings statement creates a new sheet in your Excel workbook.

Then we will request Environment, Social and Governance data (ESG) from RDP Delivery Layer and export ESG data to this newly created sheet object.

    	
            # -- Requesting ESG Data
 
RDP_version = '/v1'
base_URL = 'https://api.refinitiv.com'
 
category_URL = '/data/environmental-social-governance'
service_endpoint_URL = '/views/scores-full'
 
query_parameters = {
    'universe': universe,
    'start': -5,
    'end': 0
}
 
#https://api.refinitiv.com/data/environmental-social-governance/v1/views/scores-full
endpoint_url = base_URL + category_URL + RDP_version + service_endpoint_URL 
...
endpoint = rdp.Endpoint(session, endpoint_url)
response = endpoint.send_request( query_parameters = query_parameters )
print('This is a ESG data result from RDP library')
print(response.data.raw)

The example result of response data is following:

The data returned from RDP Delivery layer is in JSON message format, so you need to convert it to Pandas DataFrame first.

    	
            titles = [i["title"] for i in response.data.raw['headers']]
esg_df = pd.DataFrame(response.data.raw['data'],columns=titles)
 
esg_df.head(3)

And then we initiate the xlwings sheet object for ESG data as a separate esg_sheet variable, customize that sheet and put data to it.

    	
            esg_sheet = wb.sheets[esg_sheet_title] # esg_sheet_title = 'VOD.L ESG Data'
 
#Set Sheet Title
esg_sheet.range("A1").value = 'VOD.L Environmental, Social and Governance Scores for last 5 years'
esg_sheet.range("A1").api.Font.Size = 14 # Change font size
esg_sheet.range("A1").api.Font.ColorIndex = 2 # Change font color
esg_sheet.range('A1:U1').color = (0,0,255) # Change cell background color
 
esg_sheet.range("A2").options(index=False).value = esg_df
 
esg_sheet.range('2:1').api.Font.Bold = True
esg_sheet.range('A2:U2').color = (144,238,144) # Change cell background color

Saving Excel Report

You can dynamically save this excel file with xlwings Book object save() function.

    	
            wb.save('rdp_report.xlsx')

Reporting with xlwings PRO

The above code walkthrough shows that you can create an excel report file from Refinitiv Data easily with xlwings CE API. However, the Python application source code is a combination of formatting the report's look & feel and handle data which make the application hard to maintain in the long run.

The xlwings PRO has features to solve the limitations of the CE version. The xlwings PRO report package provides the ability to generate excel report file with the following features:

Separation of code and design: Users without coding skills can change the template on their own without writing the Python code.
Template variables: Python variables (between curly braces) can be directly used in cells , e.g. {{ title }}. They act as placeholders that will be replaced by the actual values.
Frames for dynamic tables: Frames are vertical containers that dynamically align and style tables that have a variable number of rows.

You can get a free trial for xlwings PRO here, then follow instructions in How to activate xlwings PRO page.

Once you have activated your xlwings PRO license, please install xlwings PRO additional dependencies via the following command:

    	
            $> pip install "xlwings[pro]"

Then create Excel template file as *rdp_report_template.xlsx* file with the following template format:

This template defines all look and feel (font, color, etc.) and also positions of auto-generated data with variables inside {{ and }} syntax.

Then use the report-api to generate an excel file based on a template file and data with create_report() function.

    	
            from xlwings.pro.reports import create_report
 
wb = create_report(
    'rdp_report_template.xlsx', 
    'rdp_report_pro.xlsx', 
    historical_title=historical_title, 
    df_historical=df_historical.head(10), 
    graph= fig
)

The above create_report() function will generate rdp_report_pro.xlsx file with the format defined in rdp_report_template.xlsx and data that we pass to the function. The application does need to hard code setting cells, sheets, and workbooks anymore. The xlwings PRO report package automatic replace {{ historical_title }}, {{ df_historical }} and {{graph}} variables with data that the application pass through report package's create_report() function.

The application can pass text, DataFrame, or even Graph to the function and xlwings will generate the excel report file based on the look and feel of the template file.

Prerequisite

The demo project requires the following dependencies software.

RDP Access credentials.
Microsoft Excel.
Python Anaconda or MiniConda distribution/package manager.
Classic Jupyter Notebook application.
Internet connection.

Please contact your Refinitiv's representative to help you to access Refinitiv Workspace/Eikon credentials. You can generate/manage the AppKey by following the steps in Eikon Data API Quick Start page.

Running the Notebook example.

The first step is to unzip or download the example application from GitHub into a directory of your choice

1. Open Anaconda Prompt and go to the project's folder

2. Run the following command in an Anaconda Prompt to create a Conda environment named xlwings_rdp for the project.

    	
            (base) $>conda create --name xlwings_rdp python=3.7

3. Once the environment is created, activate Conda environment named xlwings_rdp with this command in Anaconda Prompt

    	
            (base) $>conda activate xlwings_rdp

4. Run the following command to install RDP Library for Python, xlwings CE and all dependencies in xlwings_rdp environment

    	
            (xlwings_rdp) $>pip install -r requirements.txt

5. Go to project's notebook folder and create a file name rdp.cfg with the following content

    	
            [rdp] 
username = YOUR_RDP_USERNAME 
password = YOUR_RDP_PASSWORD 
app_key = YOUR_RDP_APP_KEY

Please note that you do not need the "" or '' characters for a string value as the following example:

    	
            [rdp] 
username=example@email.com
password=$$example_password%%
app_key=bbb_example_client_id_ccc

6. In the current Anaconda Prompt, go to project's notebook folder. Run the following command to start classic Jupyter Notebook in the notebook folder.

    	
            (xlwings_rdp) $>notebook>jupyter notebook

7. Jupyter Notebook will open the web browser and open the notebook home page.

8. Open rdp_xlwingsce_notebook.ipynb Notebook document, then follow through each notebook cell.

Conclusion and Next Step

The xlwings CE lets Python developers integrate data with Excel in a simple way. The library is suitable for a wide range of developers from casual coder, data scientists, professional trader to seasoned programmer to work on data analysis and generate report based on their skill. The next part of the series will cover and show how powerful xlwings PRO is when comparing to CE library. Python developers life will be more easier with xlwings PRO.

In the same time, the Refinitiv Data Platform (RDP) Libraries let developers rapid access Refinitiv Platform content with a few line of code that easy to understand and maintenance. Developers can focus on implement the business logic or analysis data without worry about the connection, authentication detail with the Refinitiv Platforms.

The integration between Refinitiv APIs and xlwings is not limited to only RDP Libraries. Any Refinitiv APIs that support Python such as Eikon Data API (Eikon Data API-xlwings article), RKD API can work with xlwings using the same concept and code logic as this RDP Library notebook example.

References

You can find more details regarding the Refinitiv Data Platform Libraries, xlwings and related technologies for this notebook from the following resources: