Integrating LSEG Financial Data to Excel with Xlwings and Data Library - Part 1

February 28,2025

Wasin Waeosri

Felix Zumstein

Overview

This project is an update version of my old How to integrate Financial Data from LSEG to Excel with Xlwings - Part 1 project because that library is outdated. This updated article aims to use the strategic LSEG Data Library for Python with the Workspace platform.

With the rise of Data Scientists, Financial coders or Traders (aka Citizen Developers), the rapid growth of Jupyter application, and popularity of Python for data and financial works, the main target of every Citizen Developer is replacing Microsoft Excel with the Jupyter+Python 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 set up the 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 LSEG Workspace desktop application via the ease-of-use Data Library for Python as an example of data set.

Introduction to xlwings

Let’s start with an overview of the xlwings library. The xlwings is a Python library that makes it easy to call Python from Excel and vice versa on Windows and macOS. The library lets you automate Excel from Python source code to produce reports or to interact with Jupyter notebook applications. It also allows you to replace VBA macros with Python Code or write UDFs (user defined functions - Windows only).

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 advance features such as reports, embedded Python code in Excel, one-click installers for easy deployment, video training, dedicated support and much more.

Note:

This article is based on xlwings version 0.33.9 and Python version 3.10.10.

That covers the basic of xlwings.

Introduction to the Data Library for Python

Now let me turn to the Data Library. The Data Library for Python provides a set of ease-of-use interfaces offering coders uniform access to the breadth and depth of financial data and services available on the Workspace, RDP, and Real-Time Platforms. The API is designed to provide consistent access through multiple access channels and target both Professional Developers and Financial Coders. Developers can choose to access content from the desktop, through their deployed streaming services, or directly to the cloud. With the Data Library, the same Python code can be used to retrieve data regardless of which access point you choose to connect to the platform.

The Data Library are available in the following programming languages:

For more deep detail regarding the Data Library for Python, please refer to the following articles and tutorials:

Disclaimer

This article is based on Data Library Python versions 2.0.1 using the Desktop Session only.

Prerequisite

The demo project requires the following dependencies:

Workspace access credential.
xlwings library version 0.33.x and above.
Microsoft Excel.
Python
Jupyter Lab.
Internet connection.

Please contact your LSEG's representative to help you to access LSEG Workspace credentials. You can generate/manage the AppKey by follow the steps on "Desktop - Eikon or Refinitiv Workspace" section of DataDLibrary - Python Quickstart page.

Code Walkthrough

Import xlwings and Data Library

Let start with xlwings CE first. The application needs to import the xlwings and lseg.data libraries.

    	
            # import xlwings and LD library

import xlwings as xw

import lseg.data as ld

import matplotlib.pyplot as plt

Initiate and Getting Data from Data Library

The Data Library lets an application consume data from the following platforms:

DesktopSession (Workspace desktop application)
PlatformSession (RDP, Real-Time Optimized)
DeployedPlatformSession (deployed Real-Time/ADS)

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

    	
            # Open Desktop Session

ld.open_session(config_name='./lseg-data.config.json')

# Result: <lseg.data.session.Definition object at xxxx {name='workspace'}>

Once the application successfully creates a session with a running Workspace desktop application, we can request Time-Series data as an example with the Library Access Layer.

    	
            # Declare parameter(s)
universe = 'LSEG.L'
historical_title = '%s Historical Data' % (universe)
 
df_historical = ld.get_history(
    universe=[universe],
    fields=['BID','ASK','OPEN_PRC','HIGH_1','LOW_1','TRDPRC_1','NUM_MOVES'],
    interval='1D',
    count = 90
)
 
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 an xlwings object and establish a connection to a workbook.

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

The step above 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 it
 
historical_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.

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

The result is as follows:

That is, an application is ready to get and export LSEG Data from Workspace desktop application to the excel file.

More Interaction and Customization with xlwings CE

The example above 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 sheet
historical_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 statements above 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 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

That brings us to how to plot a graph and import it to Excel. The xlwings CE library 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 to make it suitable to plot a graph.

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 Graph
columns = ['OPEN_PRC','HIGH_1','LOW_1','TRDPRC_1']
 
df_historical.set_index('Date',drop=True,inplace=True)
fig = plt.figure()





plt.ticklabel_format(style = 'plain')
plt.title(f'{universe} 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 above 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. To add a graph at the end of the data table in our Excel sheet, 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

    	
            '''
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,
                              format='svg',
                              export_options={"dpi": 300})

The result is as follows:

Adding New Sheet

My next point is how to add new sheet to your Excel file. 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.

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

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

Next, we request News data from the Data Library and export it to this newly created sheet object.

    	
            # -- Requesting News Data
 
from datetime import timedelta
news_df = ld.news.get_headlines(universe, start="20.02.2025", end=timedelta(days=-10), count=10)
news_df.head(3)

The example result of response data is as follows:

Then we can initiate the xlwings sheet object for this News data as a separate news_sheet variable, customize that sheet, and put data in it.

    	
            # initiate the xlwings sheet object]
news_sheet = wb.sheets[news_sheet_title]
 
#Set Sheet Title
news_sheet.range("A1").value = f'{universe} News data'
news_sheet.range("A1").api.Font.Size = 14 # Change font size
news_sheet.range("A1").api.Font.ColorIndex = 2 # Change font color
news_sheet.range('A1:U1').color = (0,0,255) # Change cell background color
 
# put data to Excel
 
news_sheet.range("A2").options(index=False).value = news_df
 
news_sheet.range('2:1').api.Font.Bold = True
news_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')

That’s all I have to say about xlwings CE.

Reporting with xlwings PRO

The code walkthrough above shows that you can create an excel report file from LSEG 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 has features to solve all of CE version limitations. The xlwings Reports provides a capability 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 having to touch 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 the instruction on How to activate xlwings PRO and License Key pages.

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

    	
            $> pip install "xlwings[all]"

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_report
 
app = xw.App(visible=True)
report = app.render_template(
        'rdp_report_template.xlsx',
        'rdp_report_pro.xlsx',
        historical_title=historical_title,
        df_historical=df_historical.head(10),
        graph = fig)

The render_template() method generates 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.

Let's leave a brief introduction of xlwings PRO here.

Conclusion and Next Step

The xlwings CE library lets Python developers integrate data with Excel in a simple way. The xlwings PRO allows Python developers and business users to work together to integrate data with Excel or PDF report file in much easier than xlwings CE.

The xlwings Reports help businesses and financial teams design the report to match their business requirement freely. The Python developers/data engineers can focus on how to retrieve and optimize data without no need to worry about report design, look & feel. xlwings Reports also help developers can automate report generator process periodicity (such as a daily, weekly, or monthly report).

If users want dynamic data and charts in the report file, the xlwings Embedded Code feature lets users run Python code in the macro-enabled Excel report directly. Users do not need to run a separate Python code themselves or wait for Developers to generate a report file for them.

The newly introduced to_pdf feature also lets developers export the Excel Workbook/Sheets to the PDF file. This function helps business users who do not have Microsoft Office installed can still be able to open the PDF report file.

At the same time, the Data Library for Python let developers rapidly access the Data Platform content with a few lines of code that easy to understand and maintain. Developers can focus on implement the business logic or analysis data without worry about the connection, authentication detail with the Platforms.

The integration between LSEG APIs and xlwings is not limited to only the Data Library. Any LSEG APIs that support Python programming language such as Data/Delivery Platform APIs, or LSEG Tick History - REST API, or DataStream Web Service - Python can work with the xlwings library using the same concept and code logic as this Data Library notebook examples.

That covers all I wanted to say today.