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_arr_no_idx`()	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.

Calendar 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.

The above image shows the basic application of these calendar 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).