A RTD (Real Time Data) function is used for values that update periodically as new data becomes available. For example, RTD functions can be used for ticking stock prices or FX quotes.
With PyXLL, any Python type can be returned from an RTD function, including pandas DataFrames.
For information on writing RTD functions with PyXLL please see https://www.pyxll.com/docs/userguide/rtd.html.
While you can return array types from RTD function in Excel, there are some limitations and there is a known issue with dependency tracking when returning an array from an RTD function in Excel. To avoid these limitations and issues, we recommend using PyXLL's "object" type to return a DataFrame to Excel as an object handle and then use a second function to expand that DataFrame object handle into an array.
See https://www.pyxll.com/docs/userguide/udfs/cached-objects.html for details about PyXLL's "object" type. The following video also covers the topic of this FAQ article:
To specify the return type of the RTD function we use the type parameter to PyXLL's "rtd" type. To specify that we want to return an object handle use "rtd<object>" as the return type, for example:
from pyxll import xl_func
@xl_func("... : rtd<object>")
def your_dataframe_rtd_function(args):
# Construct your subclass of the RTD type that periodically
# sets its 'value' to a pandas DataFrame
rtd = YourRTDClass(...)
# Return the RTD object and the value shown in Excel will be
# the current value taken from the RTD object, and will update
# as the RTD's value is updated.
return rtd
When you call this function from Excel you should see something like "DataFrame@x", which updates as your RTD function updates.
To convert this object handle to an array you need to use a second function like the following:
@xl_func("object x: dataframe", auto_resize=True)
def expand_df(df):
return x # No need to do anything here,
# the PyXLL type conversion will do the work.
Above, the PyXLL type conversion handles converting the object handle to an array of data. If you needed to provide some options for how the conversion happens, you can do so using "pyxll.get_type_converter" instead, for example:
from pyxll import get_type_converter
@xl_func("object x: var", auto_resize=True)
def expand_df(df):
# Get the conversion function. You could also do any type checking of df
# here if you wanted to support different types.
# Type parameters can be passed using the 'src_kwargs' argument.
to_var = get_type_converter("dataframe", "var", src_kwargs={})
# convert the DataFrame object and return it
return to_var(df)