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.
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.
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.
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.
echo Invoking SWIG CSharp ...
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 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.
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().
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.
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.
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.
To learn more about RTD in ExcelDNA, please check out the article Streaming realtime data to Excel by Gert-Jan van der Kamp.