Excel Guide

Key Functions

The main objective of rateslib-excel is to fully replicate the Python API methods of rateslib, callable from Excel, with successful integration into the Excel calculation dependency tree.

There are 4 key cell functions to replicate the different types of Python’s general API:

• Class instance creation.

• Class method calls.

• Class attribute value fetching.

• Isolated Function calls.

rl_cls(name, keywords, values)	Initialise any rateslib Class provided with a dict of keyword args.
rl_meth(name, obj, keywords, values)	Perform any rateslib Class method provided with a dict of keyword args,
rl_attr(name, obj)	Get an attribute value of a Class instance.
rl_fn(name, keywords, values)	Perform any rateslib function provided with a dict of keyword args.

The above image shows a FixedRateBond being created as a Class object cached to a cell (A4). Additionally a settlement date and price are given and the ytm method is called returning 3.27%

Three of these also have extentions to force the returned value to be a Python object, instead of allowing Pyxll to type convert it automatically. This is often useful for chaining and/or for visualisation when structuring a spreadsheet.

Note

General guidance is to try the above methods first. If the result is not suitable and a general representation would be better wrapped into a single cell, then use the object return variant below.

rl_meth_obj(name, obj, keywords, values)	Perform any rateslib Class method provided with a dict of keyword args, returned as a cached Python object.
rl_attr_obj(name, obj)	Get an attribute value of a Class instance, returned as a cached Python object.
rl_fn_obj(name, keywords, values)	Perform any rateslib function provided with a dict of keyword args, returned as a cached Python object.

Type Wrapping

Because rateslib is a Python library it relies on Python types. Excel cannot natively pass Python objects, from cells, to Excel functions. The way to solve this is to create object wrappers. This means a wrapper, once created, can then be passed to an Excel function and treated as a Python object.

Rateslib-excel makes use of the following wrappers to cache Python objects to worksheet cells:

Wrapper type	Contained Python type	Wrapping function
PyDateTime	datetime.datetime	rl_dt()
PyInt	integer	rl_int()
PyNoInput	rateslib.NoInput	rl_blank() and rl_noinput()
PyDict	dict	rl_dict()
PyTuple	tuple	rl_tuple() and rl_seq_tuple()
PyList	list	rl_list() and rl_seq_list()
PyInputs	list (as Excel array)	rl_inputs() and rl_seq_inputs()
PySeries	pandas.Series	rl_series()
PyDataFrame	pandas.DataFrame	None

Notice that a Python list is included in this table twice. The difference is how these objects will be interpreted in function calls, and specifically, how they are unwrapped.

• A PyList is a genuine Python list of values. Suppose the keyword of the function was instrument_labels and the desired input is a list of strings, e.g. [“1M”, “2M”, “3M”]. This input would require a PyList object.

• A PyInputs is a list of values associated to each keyword of the function. Suppose the keywords were settlement, ytm and metric, then, since these are distinct keywords, an array of cells would be permissible, e.g. (A1:A3 or A1:C1). However, if the array is not contiguous, then a PyInputs wrapper can be used to collect these values from across a worksheet, inside a list which acts as an Excel array, and passed to a function call.

The above image shows the keywords {"ytm", "settlement", "metric"} being collected from non-contiguous cells and their respective values {3.269986, 2024-09-03, "modified"} being collected into a PyInputs object, to be used in the "duration" Class method call, returning the modified duration of 6.595.

Displaying Objects

Some sorts of values returned by rateslib functions contain DataFrames, or chart figures. To visualise these correctly in an Excel worksheet requires the conversion method, such as rl_to_arr() or rl_to_plot().

rl_to_arr(obj)	Display an object, such as PyDataFrame or PyDict, in an array of cells.
rl_to_f64(val)	Display the float() value of a Python object in a cell.
rl_to_plot(fig)	Display a matplotlib.figure as a visible embedded image in Excel.
rl_unwrap(obj)	Perform the unwrap() method on any PyWrapper type.

The above image shows an IRS instrument created and cached to a cell (H3), and then the "cashflows" Class method applied with the "curves" keyword set to blank, with the resulting DataFrame expanded to cells using the "rl_to_arr" function.

Compositing

Some objects, e.g. Curves, have more complex inputs, especially nodes. These need to be created, or composited, in the sheet and then passed to a Curve type constructor.

The above image shows a series of PyDateTime wrappers created as the node dates of the Curves (A21:A24) with some explicit discount factor values (B21:C24). PyDict wrappers are then created from the combination of these cells in B18 and C18, and finally two Curves are constructed in cells B16 and C16 from this data.

These Curves can be plotted replicating the usual method from Python.

The above image shows calling the "plot" Class method on the Curve created previously in cell C16. Some input arguments to the "plot" method are pre-structured and finally the "rl_to_plot" function is invoked on the cached Matplotlib Figure in cell I21 to generate an embedded image.

Special Functions

Some functions are so common that it is much more user friendly to provide specific functionality. For example, with rl_dt(), it is far more convenient to use rl_dt(2022, 12, 16) than using the function replicator rl_fn_obj() method (which will still work) via rl_fn_obj("dt", {"year", "month", "day"}, rl_seq_input("vals", rl_int(2022), rl_int(12), rl_int(16))). Note the integer inputs also need to be wrapped.

Scheduling Functions

rl_dt(year, month, day, excel_dt)	Create a wrapped Python datetime object.
rl_add_tenor(start, tenor, modifier, ...[, ...])	Implement the rateslib add_tenor method.
rl_dcf(start, end, convention, termination, ...)	Implement the rateslib dcf method.
rl_next_imm(start, definition)	Implement the rateslib next_imm method.
rl_get_imm(month, year, code, definition)	Implement the rateslib get_imm method.

The above image shows the basic application of these scheduling methods interacting with wrapped and unwrapped datetime objects.

The Solver

The rateslib Solver is a key element that calibrates Curves and Surfaces and is integral to all risk sensitivity calculations. In Python it dynamically mutates certain objects (Curves, Surfaces and FXForwards) which are all stored in the local namespace memory. To avoid breaking the Excel dependency tree calculation, which is very important for spreadsheet structuring, this special function will package the mutated objects and return them to excel so that dependent calculations can rely on these objects. It will also package some of the Solver result parameters including if it was a success or failure.

rl_Solver(keywords, values)

Initialise the rateslib Solver class and return mutated objects to the sheet.

The above image shows the use the Solver. Calibrating instruments and their rates are first structured (rows 38:40). Then these are collected into PyList wrappers (B43:B46), before being supplied to the "rl_Solver" function (B47). The returned data from the Solver function is expanded to an array of cells (A48:B56).

Difference between PyXll and XlOil

Both of the add-ins work in a very similar way. The key for interaction with rateslib is an object cache for storing binary Python objects within a workbook when it is not representable in simple Excel types. Both PyXll and XlOil have object caches but they operate in slightly different ways.

PyXll allows customisation of the str key for this cached object which makes it more recognisable in cells. This occurs via an overloaded cached_object_id function.

XlOil defines its own cache id key name and so cells become more unrecognizable. However, in order to remedy this the rateslib_pyxll.types.rl_repr() is available to return a more recognisable representation of the cached object. This isn’t technically required and Excel workbooks should work with both add-ins in the same way.

There are some additional differences in the way these add-ins handle type conversion between Excel and Python. For example:

• Pyxll interprets a range of one cell as an array of length 1, whilst XlOil treats that as an isolated object and not an array. Rateslib-excel is aware of this difference and internally mitigates this individual modules for each different add-in.

• Due to XlOil’s own cache id, a special method rl_unwrap_dict() allows a PyDict to be expanded to cells directly.