Skip to content

Latest commit

 

History

History
168 lines (114 loc) · 11.8 KB

README.md

File metadata and controls

168 lines (114 loc) · 11.8 KB

How to integrate Financial Data from Workspace platform to Excel with Xlwings

Example Code Disclaimer: ALL EXAMPLE CODE IS PROVIDED ON AN “AS IS” AND “AS AVAILABLE” BASIS FOR ILLUSTRATIVE PURPOSES ONLY. REFINITIV MAKES NO REPRESENTATIONS OR WARRANTIES OF ANY KIND, EXPRESS OR IMPLIED, AS TO THE OPERATION OF THE EXAMPLE CODE, OR THE INFORMATION, CONTENT, OR MATERIALS USED IN CONNECTION WITH THE EXAMPLE CODE. YOU EXPRESSLY AGREE THAT YOUR USE OF THE EXAMPLE CODE IS AT YOUR SOLE RISK.

Overview

With the 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 still be an important file format/application in the business. It is easy to distribute and non-IT people (especially your boss) can open it easily than setup Jupyter/Python environment.

This example project contains a series of Jupyter Notebook and Python console applications 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 Delivery Platform (RDP) (formerly known as Refinitiv Data Platform) via the ease-of-use LSEG Data Library for Python (Data Library version 2) as an example of data set.

Introduction to xlwings

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.

Please refer to Financial Reporting with Workspace, xlwings and Excel article which give detail regarding how to integrate xlwings with other LSEG Python API.

Note: This project is based on xlwings version 0.33.9.

Introduction to the Data Library for Python

The LSEG Data Library for Python (aka Data Library version 2) 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 RDP Platform. 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.

Figure-1

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 project is based on Data Library Python versions 2.0.1 using the Desktop Session only.

Prerequisite

This demo project requires the following dependencies software.

  1. Workspace desktop application with access credential.
  2. xlwings library version 0.30.x and above.
  3. Microsoft Excel.
  4. Python version 3.10 or 3.11
  5. Jupyter Lab.
  6. 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 RD Library - Python Quickstart page.

Running the xlwings CE Notebook example.

  1. Open a command prompt and go to project's folder

  2. Run the following command in a command prompt to create virtual environment named xlwings for the project.

    $>python -m venv xlwings

  3. Once the environment is created, activate an environment named xlwings with this command in a command prompt

    $>xlwings\Scripts\activate

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

    (xlwings) $>pip install -r xlwingsce_requirements.txt

  5. Go to project's notebook folder and input your Workspace App Key a file name lseg-data.config.json with the following content

    {
    "logs": {...},
    "sessions": {
        "default": "desktop.workspace",
        "desktop": {
            "workspace": {
                "app-key": "YOUR APP KEY GOES HERE!"
            }
        }
    }
}

  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) $>notebook>jupyter lab

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

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

    Figure-2

Running the xlwings PRO Notebook example.

  1. Open a command prompt and go to project's folder

  2. Run the following command in a command prompt to create virtual environment xlwings_rdp for the project.

    $>python -m venv xlwingspro

  3. Once the environment is created, activate a virtual environment named xlwingspro with this command in a command prompt

    $>xlwingspro\Scripts\activate

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

    (xlwingspro) $>pip install -r xlwingspro_requirements.txt

  5. Follow the same steps as Running the xlwings CE Notebook example to open Jupyter Notebook in the web browser.

  6. Open part2_datalibrary2_xlwingspro.ipynb Notebook document, then follow through each notebook cell.

    Figure-3

Note: You can create a Python virtual environment with Anaconda or MiniConda distribution/package manager too.

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.

Acknowledgements

Thank you Felix Zumstein (@fzumstein) from xlwings for your contribution, code and content review and your support.

References

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

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