Recently I’ve been rediscovering the fact that threading is hard. I’ve been extending the SpreadServe Addin to support Tiingo‘s IEX market data feed. Real live ticking market data is usually only found inside investment banks, brokers and big hedge funds as it takes a lot of cash and infrastructure to connect to exchanges directly or to subscribe via Reuters. Even newer internet contenders like xignite are very expensive too. Tiingo’s IEX feed provides live ticking equity top of book data at an unprecedented price point. That is an exciting new development that I want to support in SSAddin. Coding it up has renewed my appreciation of how tricky multithreaded code can be. The SSAddin is implemented in C# packaged as an XLL using ExcelDNA. As with any Excel XLL, the worksheet functions it defines are executed on the main Excel thread. If they are long running, then they’ll block the GUI. So the worksheet functions pass off their work to a background thread. This means that SSAddin can do quandl and tiingo historical data queries without blocking the main Excel thread. Query results are cached, and there’s a set of worksheet functions to pull results out of the cache. So far so good. However, adding subscriptions to Tiingo’s IEX market data adds more complexity. In .net callbacks for web socket events are dispatched on pool threads. Ticking data is pushed back into Excel via RTD. So lots of lock statements are necessary to coordinate access to the queue for passing work from the Excel thread to the background thread, and for coordinating access to subscription management data structures and the RTDServer between the background thread and the pool threads that dispatch the socket callbacks. All good fun which has prompted a few thoughts. Firstly, threading is hard! Secondly, I must get round to learning Rust and understanding the borrow checker. Thirdly, thanks heavens for lock reentrancy in .net!

I’ve been wanting to use RethinkDB for the cloud based SpreadServe service offering for sometime, so when I heard the Windows version had gone into beta there was no excuse for further delay. NoSQL DBs are all the rage now, with Mongo, Cassandra, Couch and Redis to choose from. For me, RethinkDB stood out from the crowd for several reasons. Firstly, its changefeeds. All distributed systems have to resolve the challenge of keeping process caches in sync with the DB. I’ve seen two quality hand rolled solutions to this at major banks in the past few years. One based on SQL Server triggers that caused pub sub broadcasts of XML formatted updated or inserted rows. And on JP Morgan’s Athena project I saw Twisted object serialisaton used to update socket subscribers with recently changed objects. Both approaches scaled up well. What makes RethinkDB special is that it solves that problem for you out of the box with changefeeds. The second appealing feature of RethinkDB for me is that the Python API is a first class citizen andnot an afterthought. The Python API’s event handling and coroutine implementation style is neatly integrated with Tornado, which I’m also using in SpreadServe. And thirdly, I liked the fact that RethinkDB’s core implementation is in C++, and is open source. Like RethinkDB, and like JP’s Athena for that matter, SpreadServe is C++ on the inside with Python APIs.

So I’ve been working with the RethinkDB 2.3.0 beta build for Windows for a few days now. I’ve been delighted by several aspects of Rethink, and I’ve hit a few gotchas. I’ve also realised that the shift to coroutine based coding is a big, big deal. So let me lay that out here, for the record. First, the things that have delighted me…

And here are the gotchas that I hit…

  • When coding in Python, don’t forget a .run( ) on the end of your r.table( ).get( ) or r.table( ).insert( )
  • Method names aren’t consistent across APIs. For instance getAll( ) in JS is get_all( ) in Python. Even if you’re coding in Python, as I am, you’ll still find yourself using JS in the admin GUI’s data explorer, so this is an irritation.
  • You need tornado 4 or better as RethinkDB’s Tornado integration imports tornado.tcpclient, which isn’t in 3.x. It took me a while to track down as the server process which I was connecting to RethinkDB was exiting silently, with no trace of an import error in log or console. However, Python docs do say that imp.load_module( ), as used in r.set_loop_type( ) can throw ImportError. Once I got a try/except clause around r.set_loop_type( ) I caught the exception and realised I needed to upgrade from Tornado 3.2 to 4.2.1.

Once I was past the gotchas I realised I needed to upgrade my coding style to embrace coroutines. They’ve been in Python since 2.7, and Tornado has adopted them. They’re all over the RethinkDB examples. I’ve been coding in a single or low threaded async callback style for at least ten tears now, having realised that the multiple blocking worker thread approach is horribly inefficient and prone to deadlocks and races. But all my code has been very callback oriented, and coroutines are a big shift away from that. One of my big challenges over the last few days has been figuring out how to combine the two styles. I have my own C++ & Python framework with uses a single threaded async style. And I’ve got a load of Tornado based code in the same style. Now I need to combine that with RethinkDB code written in a coroutine style. I found this fantastic blog post with detailed commentary on refactoring a bunch of callback style Tornado code to use coroutines:

It’s been invaluable. One mistake I’ve made is thinking that Rethink/Tornado coroutines can be invoked directly like generators. They can’t, you must use loop.add_callback( ) to schedule them. I’ll be back with more as I explore RethinkDB and coroutines more, and I aim to post more code samples like this gist of a minimal, complete Tornado Web Server with RethinkDB changefeed.

There are a couple of spreadsheets in the SpreadServe beta that illustrate point 3 (component reuse) from my recent Spreadsheets are code post. One of them – ycb_quandl_pub.xls – is running on the AWS host, and a recent post explained in detail how it uses Quandl data to drive QuantLib’s yield curve bootstrapping functions. ycb_quandl_pub.xls is paired with ycb_quandl_sub.xls. You can download both of them from here, and as their names suggest, ycb_quandl_pub.xls is a publisher, and ycb_quandl_sub.xls is a subscriber. ycb_quandl_pub.xls will run equally happily in Excel or SpreadServe, but it only becomes a reuasable component when it’s running in SpreadServe. Try downloading ycb_quandl_sub.xls and running it in Excel on your desktop. You’ll need to install SSAddin to make it work. Then you’ll see that ycb_quandl_sub.xls is updated with the dates and rates of the bootstrapped curve calculated by ycb_quandl_pub.xls. You may see #N/A in the cells for a few minutes until the first tick arrives from the server, which recalcs every five minutes. The s2cfg sheet in ycb_quandl_sub.xls configures the SSAddin to use its s2websock function to subscribe to the rates published by the RealTimeWebServer every time the ycb_quandl_pub.xls sheet hosted in a SpreadServeEngine instance recalculates. The RealTimeWebServer can support many subscribers, so all the logic in ycb_quandl_pub.xls from Quandl, QuantLib and the worksheet formula is shared by all the subscribers. A user with edit permission could change some aspect of the model on the publisher side, the Interpolator or TermStructureCalendar perhaps, and all the subscribers would get the same updated data as a result. Those familiar with typical pricing engine architectures in investment banks will recognise the makings of a graph of pricing engines here. But the major difference is that no server side C++, C# or Java coding is necessary to make it happen. Graphs of quant or trader developer spreadsheets can be strung together very rapidly. The benefit of the spreadsheet level component reuse that SpreadServe makes possible should be apparent.

SpreadServe resources

August 7, 2015

In preparation for the launch of SpreadServe‘s beta program I’ve added a page of resources to this blog. I’ve just finished moving the documentation on to It’s very cool to be able to edit the docs on my laptop, push the changes to github, and have them appear automatically, via webhook, on readthedocs. The source ReStructured Text docs are on the SpreadServe github repository. Also on github is the SpreadServe Addin which extends Excel with background thread quandl queries and cron like scheduled triggers. And there’s a link to the Amazon hosted instance running a yield curve bootstrapping sheet that automatically pulls depo, futures and swap rates from quandl. More on that in another post. Finally, there’s a link to the Google Group for SpreadServe. Please join the group if you’d like to download the SpreadServe beta and kick the tyres.

Python 3 & PyCharm

July 1, 2015

I’ve been coding in Python since 2000, and for a long time my dev env preferences haven’t changed. Like many I used Python 1.5.2 with a basic text editor, often vim, for a long time. Once the 2.x series of Python releases started I held off and stuck with 1.5.2 for a long time. I never used 1.6.x. I can’t remember whether I made the jump to 2.1 or 2.2, but I’ve been using 2.x for a long time now, usually with notepad++ as my editor. Part of the reason is that it takes time for the extensive Python ecosystem to catch up and port all the libraries and frameworks. Anyway, I’ve just finished a contract where I used Python 3.3 and the PyCharm IDE, and it was a breath of fresh air. I’d never consider development in Java or C++ without an IDE, and my preferences are IntelliJ & MS Visual C++ respectively. Previously I’d felt an IDE was unnecessary in Python, mainly because the rapid cycle time is so quick. Unlike C++ the cycle is not edit, compile, link, test. In Python one just edits and tests, which makes the printf style of debugging far more effective. PyCharm turbocharges the debugging process with breakpoints and visual object graph traversal. And during coding it interactively highlights syntax errors and variable references. That’s a big time saver too, since it makes code run at the first attempt without throwing syntax errors. +1 for PyCharm!

So what about the shift from Python 2.x to 3.x ?  For me the important points have been the move to more iterator based coding. The iteritems( )/iterkeys( )/itervalues( ) methods no longer exist as items( )/keys( )/values( ) no longer return lists, they return iterable view objects. Those view objects are not stand in replacements for lists. And I had to get used to using the next( ) operator with generators. And, of course, print is now a function and no longer a statement. But apart from that it was straightforward.

Update 2015-07-01: I’ve just been pinged by an old coding compadre who downloaded PyCharm on my recommendation, and needs a tip on fixing up interpreter paths to pick up libs. I had to read a couple of StackOverflow articles to figure this out too, so I though I’d document it here. I’m using PyCharm Community Edition 4.5.2, and to add libraries to my interpreter search path I go to the File/Settings dialog. In the left had tree control, under the Project: <myproj> node I select Project Interpreter. Then I click on the cog icon in the top right, next to the selected interpreter, and choose the More… option. This throws up another dialog: Project Interpreters. On the right are several icons. The bottom one is a mini tree control that shows a pop up tooltip saying “show paths for the selected interpreter”. Click on that, and finally you get the Interpreter Paths dialog, and you can add your library. Phew!! Could this config be buried any deeper? IntelliJ: sort it out! PyCharm is very, very good, but this is quite a useability flaw….

I’ve been doing a lot of Excel RTD addin coding recently, as I’ve been adding RTD support to SpreadServe. As part of that work I’ve developed two new addins, both of which I’ve posted on github. Of course, both addins work in Excel and SpreadServe. The first, SSAddin, supports queries and Unix cron style timer events on background threads. Both these things can be done with VBA of course, and that’s how quandl’s existing Excel addin does it. However, SSAddin gives you the means to achieve automated, scheduled downloads from quandl with no Visual Basic and no manual keystrokes into a GUI. The second addin, kkaddin, is based on Kenny Kerr‘s example C# RTD code. While I was researching RTD I read Kenny’s excellent material on the topic. John Greenan also has some quality content on his blog too. However, I wasn’t able to find a single, simple, download with C# boilerplate code that would build and run; so that’s what kkaddin addresses.

quandl badly formed URL

April 20, 2015

I’ve started working on some new code that pulls data from quandl, and I was getting this error…

 { "error":"Unknown api route."}

I was using the first example from quandl’s own API page

and googling didn’t turn up any answers. Fortunately the quandl folk responded on Twitter, and all’s well. The URL should be…

So I’m recording the issue here for any others that get stuck. Looks like “unknown api route”==”badly formed url”.

Excel industrialisation

April 3, 2015

John Greenan has produced an excellent series of posts on Excel VBA Industrialisation on his blog. It’s a topic dear to me, so I figured I’d better respond. In his posts JG presents a series of VB Extensions based techniques to enable the export of embedded VB from a spreadsheet, so it can be version controlled, as well as techniques for error logging and reporting. The code is out there on github, and it’s a valuable addition to the public domain, especially since there are several commercial offerings addressing this space. For instance, spreadgit, ClusterSeven and Finsbury Solutions. JG kicks off his discussion in part one by observing that VBA is in the doldrums, and that the cool kids are using MEAN, Scala, OCaml or Haskell. Sure, the cool kids are never going to use VBA. But that’s not just because other languages are cooler, it’s because VBA and the latest programming languages are aimed at completely different audiences. Scala, OCaml & Haskell are for developers, and Excel is for non developers, end users, business users. The very reason for Excel’s phenomenal success and ubiquity is because it enables end users to create software solutions. Apparently there are eleven million professional software developers in the world. But even those eleven million can’t meet the world’s demand for software, so end users have to generate their own solutions, and they use Excel to do it. The result is, as JG points out in the comments to part six in his series: “In many cases the requirement for Excel Industrialisation is for a firm with an existing portfolio of ‘000s of spreadsheets that cannot all, in a cost-effective manner, be manually rewritten to conform to a coding standard.”

A version control system is an important part of controlling those portfolios of end user developed spreadsheets. However, it solves only part of the problem. Another major underlying factor that causes so many spreadsheet problems is their manual, desktop operation. Since Excel is a desktop application, Excel spreadsheets must be manually operated by their users. Users have to start up Excel, load the sheet, key in unvalidated data, hit F9, and then copy & paste or email the results out. All of that is error prone. And all of this manual operation is a major factor preventing any organised, systematic testing. All of these problems were writ large with the London Whale. All these problems could be resolved if we could decouple Excel as a development environment from Excel as a runtime. It’s great that end users can develop their own solutions in Excel, but it’s burdensome and error prone for this solutions to be operated manually on desktop PCs. Those solutions should be automated, resilient and scalable, and hosted by a server side rumtime. That, of course, is SpreadServe.

I’ve been heads down working on SpreadServe recently, so haven’t paid so much attention to the etrading topics that I used to blog about so much. Thanks to an update from mdavey, I’ve been catching up on the excellent, thought provoking content that jgreco has been posting on his plans for a new US Treasury trading venue, organised as a limit order book, with buy and sell side trading on an equal footing. I enjoyed the post on internalization and adverse selection. His points about single dealer platforms are well founded too, though my own experience in rates trading is that it’s difficult to get client flow on to SDPs as by their very nature they can’t offer multi dealer RFQs, which are critical for real money clients that must prove best execution for regulatory reasons. Of course, if the inter dealer prices from BrokerTec, eSpeed and EuroMTS were public in the same way as equity prices from major exchanges are public, then more solutions to the best execution problem would be possible. As jgreco rightly points out, transparency is key.

Now I want to raise a few questions prompted by jgreco’s posts, both pure tech, and market microstructure…

  • Java? Really? I wonder if it’s inspired by LMAX’s Java exchange implementation, their custom collections and Disruptor. I would have expected C++, but then I’m an old school C++er.
  • Is that really the workflow ? That must be a tier 2 or 3 bank. All my experience has been at tier 1 orgs where all pricing and RFQ handling is automated. If a trader quotes a price by voice, it’s a price made by the bank’s own pricing engines. Those engines will be coded in C++, driven by Eurex futures or UST on the runs, and showing ticking prices on the trader desktop. Mind you, obfuscation techniques were used to frustrate step 2: copy and paste quote. After you’ve spent a fortune building a rates etrading infrastructure, you don’t want everyone backing out your curve from your Bloomberg pages.
  • Will DirectMatch have decimal pricing, or are you going to perpetuate that antiquated 1/32nd stuff?
  • How will you handle settlement/credit risk? Will each trade result in two, with counterparties facing off with a clearing house?
  • How do you shift liquidity? When liquidity is concentrated at a single venue, it’s difficult to move. The only case I know of is German Govt Futures moving from Liffe to Eurex. I guess UST liquidity is fragmented across D2D and D2C venues, so it’s not concentrated all in one place, improving DirectMatch’s chances of capturing some of the flow.

The old ones are the best ones, and I spent too many hours yesterday butting my head against a classic Windows bug. I was testing an optimisation in the SpreadServeEngine‘s handling of nested invocations of XLL supplied functions. I was using QuantLib‘s YieldCurveBootstrapping spreadsheet, and the QuantLib 1.4.0 XLL addin. The sheet invokes the qlPiecewiseYieldCurve function, and the fourth parameter, RateHelpers, is supplied by an invocation of ohPack. Normally in this scenario, the XLOPER returned by ohPack would be marshalled to the engine’s internal representation, before being marshalled back to an XLOPER for handing into qlPiecewiseYieldCurve. I added a shortcut that made the XLOPER available as well as the internal representation, so the marshalling process could proceed by shortcut if possible. Unwittingly I ended up newing an object in one DLL which was released by another DLL when qlPiecewiseYieldCurve returned and released it’s stack frame. My debug build threw a run time assert on _BLOCK_TYPE_IS_VALID. Cue a bug hunt with me toothcombing through the codebase for a double delete or a buffer overrun. Spreadsheet engines are complex beasts since they’re basically a runtime for a functional programming language, so must maintain an object graph and dispatch imperatively at the nodes to tokenised code and XLL supplied C/C++ functions. There’s a lot of memory pool and stack management to do in all that! So I was sure there was a subtle memory management bug somewhere that had been exposed by my optimisation for the nested XLL functions corner case. While I was looking for the bug I did get some good reading on Windows heap debugging done: this and this are recommended. When debugging it’s easy to keep going deeper and deeper in pursuit of a supposedly subtle bug. Better to take a breath and try and widen one’s focus. It was this article that made me think again about the fact that each DLL has it’s own C run time linked in, and if their memory management implementations differ, there will be problems. So it was in this case. Adding a static allocator function to the DLL responsible for freeing the object ensured that the new & delete were done by the same DLL, and the bootstrapping model started running through smoothly. Of course what I need to do now is revisit my build system’s link model and ensure all DLLs and EXEs are linking the same CRT implementation…