I’ve just updated my Netty based TransFICC web socket server to work with version 330cb31-3670 of the TransFICC API and service. TFWebSock enables web socket clients to subscribe to TransFICC sourced market data. Those web socket clients could of course be browsers, but in the case of this demo the client is the SSAddin Excel addin. Which means that you can use TFWebSock and SSAddin to pull live ticking ICAP iSwap swap rates into Excel on your desktop. And if you want to automate and serverize that spreadsheet you can do so with SpreadServe.

I should point out that the ticking rates in this demo are from the iSwap test system, and are not live production rates. Many thanks to the teams at TransFICC and ICAP for making this data available!

Advertisements

SpreadServe and TransFICC

August 11, 2017

Earlier in the summer I did a POC integration of SpreadServe with TransFICC. For those unfamiliar, TransFICC has an ex-LMAX founding team, and is a new entrant in the ECN gateway space. Technically their key differentiators are cloud hosting and high performance engineering techniques. For Transficc clients the only thing running on premises is the Java API, which puts the same orthogonalised interface on all the usual fixed income ECNs, and connects to the cloud hosted gateway processes.

It’s been a while since I built anything non trivial in Java, so I was pleasantly surprised by how improved the Java ecosystem has become. Gradle has transformed dependency and package management. And the single threaded async approach popular in the Python and C++ worlds has finally made it to Java with Netty. You can find the code on GitHub here.

 

Back in April I read a great post by Jaakko Piipponen on the SaaS financial model he’d built as an Excel spreadsheet. I’m interested in spreadsheet financial models, so I enjoyed Jaakko’s run down on how his model covers PnL report, operating expenses, payroll and revenue. Jaakko describes how you only need to spend 30 mins a month downloading reports from the Baremetrics dashboard and pasting it into Excel to keep it up to date. The post is titled an “SaaS financial model you’ll actually update”, because the value of the model is such that 30 mins of manual handle turning a month is more than justified. Now I’m the first to advocate Excel for business agility; it’s a great tool that enables sales, marketing and finance people to solve problems for themselves without the bottleneck of a software development team. But the downside of Excel is manual operation like the download and paste Jaakko spells out. Wouldn’t it be great if the Baremetrics data in the spreadsheet downloaded and updated automatically? Then the model would always be up to date, and no tedious and error prone download’n’paste operations are necessary.

So I built it by adding Baremetrics functions to my open source XLL Excel Addin SSAddin. Here’s a spreadsheet that strips Jaako’s model down to just the revenue parts of the model…

https://github.com/SpreadServe/SSAddin/blob/master/xls/flight_bare_model2.xls

It uses SSAddin functions to automatically download the revenue numbers every 10 minutes into the MRR Export sheet via the Show Summary API so the Revenue Model sheet will update every 10 minutes with the latest new customers, subscriptions, cancellations, upgrades and downgrades. All you need to do is download SSAddin 32 or 64 bit .xll binaries from here…

http://spreadserve.com/s3/downloads.html

Then install the addin in Excel and fire up the spreadsheet. Don’t forget to ensure that auto calc is on, and that you’ve got a copy of SSAddin.xll.config from the download page and put it next to SSAddin.xll on your PC. You’ll need to edit the .config to add your Baremetrics license key.

You can see flight_bare_model2.xls running online and driven by spreadserve.com data here…

http://sscalc0.online/flight_bare_model2.xls/0

http://sscalc0.online/flight_bare_model2.xls/MRR%20Export

This online spreadsheet takes automation to the ultimate level. When you run a spreadsheet model automated with SSAddin on your desktop or laptop you still have to start Excel, load the spreadsheet and hit F9 before auto updates can start. With SpreadServe hosting, you can move the spreadsheet onto a cloud server. All manual operations are eliminated, and everyone sees the same auto updating numbers in their browsers.

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.

 

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…

 

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.

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 I

January 16, 2017

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…