SSAddin: Google Analytics, Baremetrics, Quandl & Tiingo

I’ve mentioned SSAddin on this blog before, but not given much detail about it. SSAddin is an XLL Excel addin exposing APIs for Google Analytics, Baremetrics, Quandl, Tiingo as worksheet functions. It’s distributed under a permissive Apache 2.0 license, so you can use, repurpose or redistribute however you like with no fees so long as you include license and copyright notices. You can download 32 and 64 bit .xll binaries from here: http://spreadserve.com/s3/downloads.html

Don’t forget to take a copy of SSAddin.xll.config too and put it next to SSAddin.xll on your PC. You’ll need to edit the .config to add your license keys for Google, Baremetrics, Quandl and Tiingo. There’s documentation here, but I suggest you start by playing with example spreadsheets from GitHub: https://github.com/SpreadServe/SSAddin/tree/master/xls

Don’t hesitate to ask questions in the comments here, or on the download page, or to raise an issue on GitHub.

 

Analysing the Enron spreadsheets with SpreadServe

Recently I’ve been using the Enron spreadsheets to test SpreadServe, simply because it’s good testing practice to expose any codebase to a high volume of diverse inputs. Felienne made them available on figshare, but they’re in a slightly obscure zip format, so I posted them on github to make them more accessible. SpreadServe posts information about the formulae used in each sheet into the spreadserve.com DB, so I did a simple analysis of Enron formula use. The results are on github here. To summarise: there are 15927 sheets, and only 8421 use formulae. 152 different functions are used across all sheets, and only 170 sheets use maths funcs that go beyond arithmetic. So the Enron spreadsheets weren’t as diverse as I’d hoped. They made for a good volume test though. Here’s a short video about exploring the Enron spreadsheets with SpreadServe…

 

SpreadServe AMI part III

The SpreadServe 0.4.2b AMI is now available in the US West Oregon region: just search for SpreadServe in public images. There’s a now YouTube video on launching a SpreadServe AMI.

Two points of interest came up in preparing the AMI: Admin password persistence, and the region bound nature of AMIs. This blog from 2013 suggests that Admin passwords don’t persist in AMIs, but I’ve found they do now. So the Administrator password for a SpreadServe 0.4.2b AMI is SpreadServe042b. If you launch your own SpreadServe instance using the AMI, then I suggest you change the password!

The SpreadServe 0.4.2b AMI is only published in the US West Oregon region. Only AMI owners can copy to another region, so if you want a SpreadServe AMI in another region you’ll need to do a simple workaround: start an image in US West Oregon, stop it and create your own image, then copy to your preferred region.

IEX market data, CreateEvent & CPU spikes

This week I’ve been testing the SpreadServe addin with Tiingo’s IEX market data. I was checking performance on my sscalc0.online AWS host for a group of SpreadServeEngines executing various test and demo spreadsheets, including one that subscribes to IEX tickers for AAPL & SPY, via Tiingo API websockets. That API gives us real time top of book as well as last trade price and size for the cash equity traded on IEX. In my test scenario I was running five engines, two of them idle, three running spreadsheets, one of which was a simple IEX market data subscriber. Using Process Explorer I saw some odd CPU spiking on the idle engines. Zooming in with Process Explorer I could see the busyness was on a thread that should have been idle, sleeping inside a WaitForSingleObject call, waiting for a signal to check its input queue. The event object waited upon was created by some generic code invoking win32’s CreateEvent and also used in another thread. Reading the docs I found that CreateEvent’s fourth param, the event object name, implies that the caller will get a handle to a previously created event object if the names match. And I was using a hardwired name! So my thread was being repeatedly woken by events from another thread. A quick fix to make the names unique produced idling engines with no unnecessary CPU burn. All very instructive, partly because running on AWS makes one very aware of paying by the CPU hour.

SpreadServe AMI part II

The core component in a SpreadServe deployment is the SpreadServeEngine, a headless C++ server binary that implements the Excel compatible calculation engine. The engine discovers its hostname through the win32 API using GetComputerNameExA( ComputerNameDnsFullyQualified, …). On AWS this was giving me hostnames like WIN-THU4IQNRN6F, when what I wanted was the fully qualified domain name, like ec2-54-186-184-85.us-west-2.compute.amazonaws.com. Harry Johnston helpfully advised on StackExchange that, since the host is not joined to a domain, GetComputerNameExA will only return the FQDN if I explicitly set it via Control Panel. Naturally I want to avoid manual fixes on a SpreadServe AMI so I settled on using Amazon’s EC2 instance metadata. The FQDN hostname can be discovered with an HTTP GET on this URL from any EC2 host: http://169.254.169.254/latest/meta-data/public-hostname. I built a small helper server process to query instance metadata using Tornado’s async HTTPClient and write it to the localFS, where SpreadServeEngine can read it. Result: any new SpreadServe AMI will automatically discover its public DNS.

SpreadServe AMI part I

Recently I’ve been working on building an EC2 AMI for SpreadServe, so deployment becomes a one click operation for Amazon AWS users. I ran into an interesting snag so I thought I’d capture it here. My aim was to deploy SpreadServe as a Windows Service on an AWS Windows Server 2012 R2 image, so I used pywin32‘s excellent win32service module. Here’s my github boilerplate project for a Windows Service in Python. On my AWS host my SpreadServe Windows Service was failing to start, and leaving no trace in the system or application event logs. pywin32service has a debug mode; when I tried that I got a Windows 0xc00007b error, which indicates a mix of 32 and 64 bit binaries. SpreadServe is 32 bit all the way, so something was wrong. I turned to procmon to try and figure what was failing. procmon showed that my 32 bit pythonservice.exe was loading a 64 bit python27.dll, instead of the 32 bit python27.dll that’s part of the SpreadServe install tree. The 64 bit DLL was coming from the C:\Program Files\Amazon\cfn-bootstrap directory, which is added to the standard Windows 2012 R2 image by Amazon to support CloudFormation, and is on the system path. After much experimenting I couldn’t find a way to stop Windows Service Host from using the system path, so I had to change it to replace cfn-bootstrap with SpreadServe directories. Problem solved…

RethinkDB batch insert performance

The forthcoming cloud version of SpreadServe uses a Tornado based server to persist a breakdown of all formulae used in a spreadsheet loaded by SpreadServe. For complex sheets I found that the insertion of many formulae in the formula table could be timeconsuming. In one test scenarion a multi-formula insert took 5 minutes. So I checked out the RethinkDB’s troubleshooting page where there are some useful performance tips. Batch insertions with the recommended batch size of 200 brought the insert time down from 5 mins to 21 secs. Further improvements came from using soft durability and noreply, bringing the insert time down to ~3.5 secs. However, I found that my Tornado server couldn’t respond to incoming HTTP GETs while the insert coroutine was looping on the insert batches. I figured that noreply meant that the yield in the loop resumed immediately, without waiting for the reply IO from the DB. Taking out noreply allowed the single threaded server to handle HTTP GETs in the middle of an insert. If improved performance is necessary in future, splitting the Tornado server into two processes may be the way to go, but for current test scenarios performance is acceptable.

Threading is hard: Tiingo, IEX & SSAddin

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!

Starting out with RethinkDB

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…

• Very simple install process
  • https://www.rethinkdb.com/docs/install/windows/
  • https://www.rethinkdb.com/docs/install-drivers/python/
• Nice docs
  • https://rethinkdb.com/docs/sql-to-reql/python/
  • https://rethinkdb.com/docs/async-connections/#python-with-tornado-or-twisted
• Great admin UI: the data explorer is very good.

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: https://emptysqua.re/blog/refactoring-tornado-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.

Windows Containers

Recently Microsoft has added support for containers to Windows Server. It’s available on the Azure cloud on VMs running Windows Server 2016 Tech Preview 3. I’ve been playing with it and I’ve got SpreadServe running inside a container. There’s much more detail here. But to summarise I found three workarounds were necessary…

• A two step process to build images as Windows container doesn’t like SpreadServe’s NSIS installer
• Web server inside the container should be on port 80 only internally
• A one line launch script that sets up environment variables is necessary