How to integrate Financial Data from Python to Excel with Xlwings — Part 1

Published in

LSEG Developer Community

11 min readDec 28, 2020

The original article on the Refinitiv Developer Community is available here.

Introduction

With the rise of Data Scientists, Financial coders or Traders (aka Citizen Developers), and the rapid growth of Jupyter application, the main target of every Citizen Developer is replacing Microsoft Excel with the 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 will demonstrate how to export financial data and reports from the Python/Jupyter application to an Excel report file, using the xlwings CE and xlwings Pro libraries. This article uses content from Refinitiv Data Platform (RDP) as an example dataset.

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

All data and Graph are exported from Python to Excel directly via xlwings CE.

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 interact with Jupyter notebook applications. It replaces VBA macros with Python Code, lets you write UDFs (user defined functions — Windows only) and allows remote-control of Excel via the REST API.

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

Introduction to Refinitiv Data Platform (RDP) Libraries

Refinitiv provides a wide range of content and data which requires multiple technologies, delivery mechanisms, data formats, and multiple APIs to access that content. The RDP Libraries are suite of easy-to-use interfaces providing unified access to streaming and non-streaming data services offered within the Refinitiv Data Platform (RDP). The Libraries simplify how you access data through various delivery modes such as Request Response, Streaming, Bulk File and Queues.

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. 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 in-depth details regarding the RDP Libraries, please refer to the following articles and tutorials:

Disclaimer

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

Code Walkthrough for Getting Data

Initiate and Getting Data from RDP Libraries

The RDP Libraries let the application consume data from the following platforms:

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

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

# Open RDP Platform Sessionsession = 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:

Code Walkthrough for Exporting Data to Excel with xlwing

Interact with xlwings CE

The application needs to import xlwings packages in order to interact with the xlwings CE library.

# import xlwings
import xlwings as xw# 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

With xlwings, we can export this df_historical DataFrame to excel directly. The first step is to initiate an 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 the wb object. The wb = xw.Book() statement creates a new excel file. If you are using wb = xw.Book(filename), then 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 ithistorical_sheet = wb.sheets[0]
historical_sheet.name = historical_title

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

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

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 is often hard to read. The application can use xlwings API to customize the excel report look and feel, and then customize Pandas DataFrame to make the data easier to understand.

# Clear current sheethistorical_sheet.clear()

We then use xlwings Sheet object and its api property to change the report’s look and feel.

Note: The xlwings api property is only supported in Windows. 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.

We can restructure the 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, and set the column header font and background color to make them distinguishable from the data.

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

The result is the following readable report table:

Plotting a Graph

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

We begin by changing all non-Date columns data types from String to Float. Then we can 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 the pyplot library.

# Plotting a Graphcolumns = ['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 plt.show() method 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 the data table in our Excel sheet, so we need to get the position of the last row of the report table with the xlwings end() function.

The sheet.cells.last_cell statement returns the lower right cell
The sheet.cells.last_cell.row statement returns the 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 the historical_last_row variable, mark the position of the picture at the last row of the table + 3 rows down to make some space between data table and graph. Finally, we can add the figure object as a picture to the xlwings CE sheet object using the 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 as follows:

Adding New Sheet

An Excel file can contain many reports created from different data to help make business decisions . The xlwings library 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 Datawb.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 the RDP Delivery Layer and export ESG data to this newly created sheet object.

# -- Requesting ESG DataRDP_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-fullendpoint_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 as follows:

The data returned from RDP Delivery layer is in JSON message format, so we need to convert it to a 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)

Then we can initiate the xlwings sheet object for ESG data as a separate esg_sheet variable, customize that sheet, and put data in 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 the 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 handling the data which makes the source code difficult to maintain in the long run.

The xlwings PRO library has features to solve the limitations of the CE version . The xlwings PRO report package provides the ability to generate an 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 Python code.
Template variables: Python variables (between curly braces) can be used inside cells , e.g. {{ title }}. They act as placeholders that get replaced by the actual values.
Frames for dynamic tables: Frames are vertical containers that dynamically align and style tables that have varying number of rows.

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

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

$>pip install "xlwings[pro]"

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

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

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

from xlwings.pro.reports import create_reportwb = 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 the rdp_report_pro.xlsx file with the format defined in rdp_report_template.xlsx and the data that we pass to the function. The application does not need to hard code the setting for cell, sheet and workbook anymore. The xlwings PRO report package automatically replaces {{ historical_title }}, {{ df_historical }} and {{graph}} variables with data that the application passes through report package’s create_report() function.

The application can pass text, DataFrames, or even Graphs 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:

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 follow the steps in Eikon Data API Quick Start page.

Running the Notebook example.

The application/Jupyter Notebook examples files are available at GitHub page. Please follow Running the xlwings CE Notebook example section.

Conclusion and Next Step

The xlwings CE library lets Python developers integrate data with Excel in a simple way. The library is suitable for a wide range of developers from casual coders, data scientists, professional traders to seasoned programmers, and allows them to work on data analysis and generate reports based on their skill. The next part of the series will cover and show how powerful xlwings PRO is when compared to the CE library. The lives of Python developers will be more easier with xlwings PRO.

At the same time, the Refinitiv Data Platform (RDP) Libraries gives developers rapid access to Refinitiv Platform content with just a few lines of code that are easy to understand and maintain. Developers can focus on implementing the business logic or analysing data without worrying about the connection and authentication details for the Refinitiv Platforms.

The integration Python APIs/libraries and xlwings is not limited to only Refinitiv RDP Libraries. Any Data Science/Financial APIs that supports Python such as Eikon Data API (Eikon Data API-xlwings article) 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:

For any questions related to this article or Refinitiv Data Platform Libraries, please use the Developers Community Q&A Forum.

GitHub

https://github.com/Refinitiv-API-Samples/Article.RDPLibrary.Python.Xlwings