EliteQuant Excel

Introduction

EliteQuant Excel is an open source quantitative modeling solution in Excel. Excel is one of the most popular tools in the business world, across both sell side and buy side. EliteQuant Excel is an Excel Add-in that enables you to create quantitative financial models in Excel spreadsheet, in the same way how financial professionals such as traders, quants, and portfolio managers do their daily to daily work.

You are able to create pricing and simulation tools for products across all asset classes such as interest rate and FX, and from plain vanilla to exotic be-spoken instruments. You are also able to backtest and live trade from Excel, with the so-called RTD, or real-time data support.

This post will discuss the technical stack used by EliteQuant Excel from developer’s perspective.

Code Structure

Before proceed, I want to point out that QuantLib does provide an Excel add-in called QuantLibXL. But I find C#/ExcelDNA route is simplier and more flexible. The technical stack requirment is shown as follows. In order to understand the structure, you need some previous knowledge about C++, C#, and VBA.

Below is the simplified code structure of the project.

1
2
3
4
5
6
7
8
9
10
11
+-- CppCoreLibrary
| +-- QLExtension
| +-- quantlib.i
+-- SwigConversionLayer
| +-- swig
| +-- NQuantlibc
| +-- QLEX
| +-- NQuantLibcPINVOKE.cs
+-- CSharpApplication
| +-- EliteQuant
| +-- EliteQuantExcel

C++ Analytical Library

EliteQuant Excel is based on open-source C++ library Quantlib, which in turn depends on Boost C++. Therefore before we start you you need to install both of them. Fortunately, QuantLib official website provides a detailed intallation guide across operating systems.

Then CppCoreLibrary solution layer holds a project called QLExtension. In this project you are able to extend QuantLib with your own functions. I chose a non-intrusive approach to QuantLib by putting customized codes in a separate project. This project is a standard C++ static library so it should be compiled successfully by including QuantLib and Boost in its project path.

SWIG Conversion Layer

To convert C++ code to C#, you need to install a tool called SWIG. This is the approach taken by QuantLib, in order to support languages such as C#, Java, Perl, Python, and Ruby. If you have your own extended classes in QLExtension, you need to add SWIG scripts accordingly.

There is a command line script hidden in the Custom Build Tool of quantlib.i, where swig command is invoked to process the conversion.

1
2
3
4
5
6
7
echo Invoking SWIG CSharp ...

echo on

D:\Workspace\swigwin\swig.exe -csharp -c++ -outdir "..\..\SwigConversionLayer\csharp" -namespace EliteQuant -o "..\..\SwigConversionLayer\cpp\quantlib_wrap.cpp" "..\..\SwigConversionLayer\swig\quantlib.i"

echo off

SWIG uses a technique called P/Invoke to facilitate the interoperability. The actually DllImport can be found in auto-generated NQuantlibPInvokde.cs. It also automatically generates NQuantlibc C++ project and QLEX C# project served as a bridge between these two languages, accordingly to the instruction scripts in the swig folder. To learn more about P/Inovke, please refer to this technical blog C++/C# interoperability.

C# Layer

C# layer uses ExcelDNA to create add-ins for Excel. Its official website has instructions for installation and usage. It enables you to create your own add-ins via Visual Basic, C# or F#.

The callbacks are straightforward. For example, if you want to find out how many business days between two dates according to some calendar, use the following code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[ExcelFunction(Description = "business days between two dates (doesn't include these two days)", Category = "EliteQuantExcel - Time")]
public static object eqTimeBusinessDaysBetween(
[ExcelArgument(Description = "Start Date ")]DateTime date1,
[ExcelArgument(Description = "End Date ")]DateTime date2,
[ExcelArgument(Description = "Calendar (default NYC) ")]string calendar)
{
if (ExcelUtil.CallFromWizard())
return "";

string callerAddress = "";
callerAddress = ExcelUtil.getActiveCellAddress();
OHRepository.Instance.removeErrorMessage(callerAddress);

try
{
if ((date1 == DateTime.MinValue) || (date2 == DateTime.MinValue))
throw new Exception("Date must not be empty. ");
Date start = EliteQuant.EQConverter.ConvertObject<Date>(date1);
Date end = EliteQuant.EQConverter.ConvertObject<Date>(date2);

if (string.IsNullOrEmpty(calendar)) calendar = "NYC";
EliteQuant.Calendar can = EliteQuant.EQConverter.ConvertObject<EliteQuant.Calendar>(calendar);

return can.businessDaysBetween(start, end, false, false);
}
catch (Exception e)
{
ExcelUtil.logError(callerAddress, System.Reflection.MethodInfo.GetCurrentMethod().Name.ToString(), e.Message);
return "";
}
}

After you compile and load the xll into Excel, you should be able to use the eqTimeBusinessDaysBetween in the same way you call Excel built-in functions such as average() or vlookup().

Pricing and Structuring Trades

EliteQuant Excel allows you to build front office pricing workbooks. An example would be the classical Black-Scholes model located here. Besides price and greeks on European call and put options, you are able to see visually how they change with underlying prices and option maturities.

The key is to construct an object and store it in a key-value memory space for later retrieval. In this example we created in one Excel cell an European option given its strikes, volatility etc, and then store it in a dictionary with object id as key and the oject itself as value. Then in another Excel cell we are able to retrieve this object by object id and calculate its greeks based on given underlying price. This dictionary is implemented in class OHRepository.

For example, eqInstGetOptionGreeks retrieves the option by its object id, calls its member methods, and returns the value back to Excel.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
[ExcelFunction(Description = "European Option with Black Scholes Pricer", Category = "EliteQuantExcel - Instruments")]
public static object eqInstGetOptionGreeks(
[ExcelArgument(Description = "id of option ")] string ObjectId,
[ExcelArgument(Description = "Greek type ")]string gtype,
[ExcelArgument(Description = "Option type (VANILLA or MULTIASSET)")] string otype,
[ExcelArgument(Description = "trigger ")]object trigger)
{
if (ExcelUtil.CallFromWizard())
return "";

string callerAddress = "";
callerAddress = ExcelUtil.getActiveCellAddress();

try
{
Xl.Range rng = ExcelUtil.getActiveCellRange();

if (ExcelUtil.isNull(gtype))
gtype = "NPV";
if (ExcelUtil.isNull(otype))
otype = "VANILLA";

if (otype == "VANILLA")
{
VanillaOption option = OHRepository.Instance.getObject<VanillaOption>(ObjectId);
switch (gtype.ToUpper())
{
case "NPV":
return option.NPV();
case "DELTA":
return option.delta();
case "GAMMA":
return option.gamma();
case "VEGA":
return option.vega();
case "THETA":
return option.theta();
case "RHO":
return option.rho();
default:
return 0;
}
}
else
{
return "Unknown option type";
}
}
catch (Exception e)
{
ExcelUtil.logError(callerAddress, System.Reflection.MethodInfo.GetCurrentMethod().Name.ToString(), e.Message);
return "#EQ_ERR!";
}
}
}

Data and Graph

With the help of VBA, it is able to import data into Excel. This workbook downloads historical stock price data from Quandl or Yahoo! and present them in a Candlestick chart. The implementation takes two steps. The first step is to write a C# web request to Yahoo! and download historical data, as shown in this file; then in the second step VBA calls this C# function and update Excel graph object.

In the spreadsheet the following VBA script calls C# function eqDataHistoricalQuotes by Application.Run, and update the chart object ChartStockDataCandle with the historical data retrieved.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Sub RetrieveHistData()
Dim sym As String
Dim startDate As Double
Dim endDate As Double
Dim hp, lp As Double
Dim freq As String
Dim isDesc As Boolean
Dim obj As Variant

Application.ScreenUpdating = False

sym = Range("Symbol").Value
startDate = Range("StartDate").Value
endDate = Range("EndDate").Value
freq = Range("Freq").Value
isDesc = Range("IsDes").Value

obj = Application.Run("eqDataHistoricalQuotes", sym, startDate, endDate, freq, isDesc)
nrow = UBound(obj, 1)
ncol = UBound(obj, 2)

Columns("A:G").Select
Selection.ClearContents

Range("A1").Resize(nrow, ncol).Value = obj
hp = Application.WorksheetFunction.Max(Range("A1").Offset(1, 2).Resize(nrow - 1, 1))
lp = Application.WorksheetFunction.Min(Range("A1").Offset(1, 3).Resize(nrow - 1, 1))

ActiveSheet.ChartObjects("ChartStockDataCandle").Activate
ActiveChart.ChartTitle.Text = sym
nSeries = ActiveChart.SeriesCollection.Count 'nSeries = 4
For i = 1 To nSeries
ActiveChart.SeriesCollection(i).Name = Range("A1").Offset(0, i).Value
ActiveChart.SeriesCollection(i).Values = Range("A1").Offset(1, i).Resize(nrow - 1, 1).Value
ActiveChart.SeriesCollection(i).XValues = Range("A1").Offset(1, 0).Resize(nrow - 1, 1).Value
Next
ActiveChart.Axes(xlValue).MinimumScale = lp * 0.95
ActiveChart.Axes(xlValue).MaximumScale = hp * 1.05
ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "#,##0.00"

Application.ScreenUpdating = True

Range("A1").Select
'MsgBox sym + " hist data retrieved"
End Sub

Real-Time Data (RTD)

It wouldn’t be complete if we leave out Exel Real Time Data(RTD) service, which opens the door to real time market data. This workbook scrapes Yahoo! Finance real time market prices and updates them in Excel. The underlyiing RTD server is implemented here.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
[ComVisible(true)]
public class RTDSimpleTimerServer : IRtdServer
{
private IRTDUpdateEvent _callback;
private System.Windows.Forms.Timer _timer;
private Dictionary<int, RoutineTask> _tasks = new Dictionary<int, RoutineTask>();

// 1. Start the server
public int ServerStart(IRTDUpdateEvent CallbackObject)
{
// Called when the first RTD topic is requested
// Sets server to poll for updates every second
_callback = CallbackObject;
_timer = new System.Windows.Forms.Timer();
_timer.Tick += Callback;
_timer.Interval = 1000;
return 1;
}

public void ServerTerminate()
{
// Kills timer and queries so they don't run after workbook closes
_timer.Dispose();
_tasks = null;
}

// 2. connect to the data
public object ConnectData(int topicId, ref Array Strings, ref bool GetNewValues)
{
// ...
}

public void DisconnectData(int topicId)
{
// Removes query on disconnect
_tasks.Remove(topicId);
}

public int Heartbeat()
{
// Called by Excel if a given interval has elapsed (returns true)
return 1;
}

public Array RefreshData(ref int topicCount)
{
// ...

_timer.Start();
return results;
}

private void Callback(object sender, EventArgs e)
{
// Stops timer and tells all queries to run their async delegates
_timer.Stop();
lock (_tasks)
{
foreach (KeyValuePair<int, RoutineTask> t in _tasks)
{
t.Value.AsyncImport();
}
}
_callback.UpdateNotify();
}
} // End class RTDSimpleTimerServer

To learn more about RTD in ExcelDNA, please check out the article Streaming realtime data to Excel by Gert-Jan van der Kamp.