Real-Time Data Made Easy: Empowering Excel with Lightstreamer and RTD Integration

If you are new to Lightstreamer, it’s a real-time messaging server that allows data to be pushed to mobile, web, and desktop applications in real-time. Its optimized transmission algorithms make it the right choice for applications that require low-latency data delivery, such as financial trading, online gaming, and data streaming services.

Excel is a popular spreadsheet software developed by Microsoft. It is used to organize, analyze, and visualize data in various industries and fields. Excel provides numerous features and functions to perform calculations, create charts, and manage data efficiently.

RTD stands for Real-Time Data and is a specific functionality in Excel. It enables users to retrieve and display real-time data from external sources, such as financial markets, databases, or other data feeds. With RTD, users can create dynamic and constantly updating spreadsheets, making it particularly useful for monitoring and analyzing time-sensitive information.

In essence, Excel is a versatile tool for data manipulation, while RTD is a specialized feature that facilitates the integration of real-time data to keep spreadsheets up-to-date with the latest information.

In this blog post, we delve into the remarkable potential of merging Lightstreamer, RTD (Real-Time Data), and Excel to supercharge financial data analysis. Discover how this seamless integration empowers traders, analysts, and financial professionals to tap into real-time market data and transform Excel into a dynamic financial dashboard for diverse applications across trading, portfolio management, and market analysis, and witness how this powerful trio unlocks previously untapped data-driven opportunities. Stay ahead of the curve in the fast-paced financial world by harnessing the real-time capabilities of Lightstreamer, RTD, and Excel for unparalleled insights and decision-making precision.

The demo

We developed a demo project showing an integration between the .NET Standard client API for Lightstreamer and the RTD Server for Excel.

The demo is made up of a DLL library that acts as an RTD Server, which receives updates from Lightstreamer Server on one side and injects them into Excel on the other side. The library has been developed with C# (full source code is provided, see below). It leverages the .NET Standard Client API for Lightstreamer to subscribe to 30 stock items and the Microsoft.Office.Interop.Excel to set up the RTD server.

Dig the code

The main class is RtdServer, found in RtdServer.cs source file, which contains an implementation of the IRtdServer interface from the Interop.Excel, that serves as a bridge for communication between Excel and the Lightstreamer server.

The RTD function uses the following syntax:

=RTD(RealTimeServerProgID,ServerName,Topic1,[Topic2], ...)

So, the first parameter is the identifier of the RTD server installed on the local system who will provide the data. It is registered in the C# code and for this demo is defined as: lightstreamer.rtdnew23

The ServerName parameter is not used by the demo and the following parameters are a free and variable list of values called ‘topics’ which should represent the values required by the excel sheet to populate the cells.

In the demo the first topic value of the list (Topic1) is used as a selector. 

The ‘CONFIG’ value is a special case that once received by the RTDServer triggers the connection to the Lightstreamer server.

Please look at A1 cell in the provided Excel sheet (ExcelDemo_New.xlsx):

=rtd("lightstreamer.rtdnew23",,"CONFIG","http://localhost","8080","WELCOME","STOCKS","user_test","o93ujDNJasf,ajfih393!_ard3sfaklj_sjsijhdf3934eifaskik(2332334yh32rdusjdsshkahkhgfasif24egfwebkjbcfasik")

The demo uses the values of the other topics to initialize the connection, respectively:

 – Lightstreamer server url (Topic2),

 – port number (Topic3),

 – Adapter Set name (Topic4),

 – Data Adapter name (Topic5),

 – user (Topic6),

 – password (Topic7).

 And return the current status of the connection with the Lightstreamer server.

The ‘OPTIONS’ value is a special case that once received by the RTDServer leverages some specific tuning for Lightstreamer. Currently it is possible to configure:

 – the max frequency for each subscriptions, max_frequency, Subscription.RequestedMaxFrequency

 – the transport for the communication  with the Lightstreamer server, forced_transport, ConnectionOptions.ForcedTransport

 – the stalled timeout, stalled_timeout, ConnectionOptions.StalledTimeout

 – a proxy, proxy, ConnectionOptions.Proxy

Double check the cells of column A (A2, A3, A7) for some examples of configuration.

Instead, regarding the cells in the demo with stock market tickers, the formula is as follows:

=rtd("lightstreamer.rtdnew23",,"item1","stock_name")

where Topic1 and Topic2 are respectively the name of the Item and the field we want to display in the cell and therefore, they must be subscribed through the client session of Lightstreamer.

Once all the items and fields required by the Excel sheet are subscribed and their subscriptions are submitted, the demo will start receiving real-time updates from the Lightstreamer server. These updates are collected in a temporary data structure and are dequeued whenever the RefreshData function is called to be passed to the proper cell of the Excel sheet.

LSConnect.cs, StocklistConnectionListener.cs, and StocklistSubListener.cs contain classes used to interface to the Lightstreamer .NET Standard Client library.

The Demo produces a log file, TestRTD.log, with information about the interaction both with the Excel sheet and the Lightsgtreamer server.

Check out the sources for further explanations.

To recap

August 2, 2024
Originally published: August 2, 2023

4 min read

Table of Contents

Tags: