Still Using Spreadsheets to Manage Risk or Create Algos? Time to Reconsider.

I’ve been building technology for trading firms for over a decade. From proprietary desks with in-house development and security so tight that you have to check your cellphones at the door, to shared office spaces where people openly collaborate about their strategies; I’ve seen it all. I’d say that one of the most common tools that I’ve seen on trading desks is Microsoft Excel.

Excel is popular because you can combine realtime data feeds from a multitude of sources with complicated mathematical formulas to do incredibly complex things incredibly quickly from a time-to-delivery standpoint. I’ve seen excel sheets used for everything from position analytics and reporting, to even realtime execution algorithms and complex order types. As a developer, I use Excel sheets to build proofs of concept constantly. Gatekeeper’s Formula Strategy, Spread Bandit, and even the Position Manager in Gatekeeper found their genesis in spreadsheets. It’s easy to use and anyone with a basic understanding of math and logic can become a programmer, well, almost.

Like just about anything that seems too good to be true, it is. All of the things that make Excel powerful can also make it dangerous. Being able to link cells together to form incredibly complex strings of logic without any kind of compile checks or design best practice enforcement means that everyone who “knows enough to be dangerous,” is. According to a recent Marketwatch Article, 88% of excel sheets contain errors! This is not surprising considering that a research survey of senior executives conducted by Vision Critical, only one fifth of companies have control policies for spreadsheets and even in companies that have spreadsheet policies, it is not always applied one third of the time!

One thing that separates traders from programmers, however, is that programmers have bug elimination built into their trade. They use strongly typed languages, compile time checks, best practices policies built into their development methodologies, and post-development quality assurance processes before their code ever makes it into production. Software engineers have had a lifetime of hard knocks and painful experiences stemming from bugs to know that you can’t put a prototype into production, and the same should apply to spreadsheets.

So as a trader, what can you do to protect yourself from spreadsheet errors?

The first thing to do is to find places where your spreadsheets are either error-prone, rely on third party data sources, or rely on bad or changing assumptions. In those places, you need to add checks for data validity and create alerts for when it’s not.

If your spreadsheets are used to enter orders into the market and/or display data that might cause a trading decision, it’s time to contract a developer to build you a custom tool, or evaluate off-the-shelf solutions. I’ve seen position greeks be completely wrong because someone didn’t update an expiration date after a contract roll or realtime price valuations become slightly off because interest rates changed and weren’t updated (okay that hasn’t happened recently, but you get the idea).

If hiring a software company sounds too expensive, remember that it’s cheaper than trading errors. Just ask JP Morgan who lost $2 billion due to spreadsheet errors! At the very least, give this article a read about best practices, or check out the European Spreadsheet Risks Interest Groups to fully understand the risks.

Trading Yen Future Options with Scaled Dynamics

Currencies have been making new highs in the volatility department. The bank of Japan is appointing a new governor, and all of the potential replacements, especially the Prime Minister’s choice, have much more “dove” (they don’t like to print) like stances on monetary policy. This could mean premium selling opportunities. Writing premium on the Scaled Dynamics Gatekeeper Platform can be done in a number of ways. While anyone could just sell options manually, the Gatekeeper platform allows you to auto quote in two meaningful ways. First, you can use formula strategies to make a one or two sided market, auto-hedging into the futures based on delta and fill price. If you already have a position that you want to move inward or outward, spread bandit will price one strike against another, auto quoting in one strike leaning on the other.

Gatekeeper Platform

To create a two sided market, simply open a formula strategy builder from the main window and set a strike as a primary instrument. The underlying (in this example, March Yen futures) should be set as the underlying. Gatekeeper allows 3 kinds of orders in formula strategies. Formula and Option orders work in the primary instrument and get their price and quantity from the result of pre-defined formulas. Event orders are sent after the formula/option orders have been filled. To make a two sided market, add two option orders, setting one as a buy and one a sell order. If you’d like to delta hedge, add an event order. Save the strategies and you can clone them to get the entire strike range.

Strategy Builder

Use the following formulas for call and put pricing:

Call Bid Price:B76.Price.Call(Future: Reference1.Bid, Strike: Primary.Strike, Rate: Rate, Time: Primary.YearsToExpiry, Volatility: Volatility)

Call Ask Price:B76.Price.Call(Future: Reference1.Ask, Strike: Primary.Strike, Rate: Rate, Time: Primary.YearsToExpiry, Volatility: Volatility)

Call Delta Hedge Price: B76.ImpPrice.Call(Initial: Reference1.Settle, Price: Fill.Price, Strike: Primary.Strike, Rate: Rate, Time: Primary.YearsToExpiry, Volatility: Volatility)

Put Bid Price: B76.Price.Put(Future: Reference1.Ask, Strike: Primary.Strike, Rate: Rate, Time: Primary.YearsToExpiry, Volatility: Volatility)

Put Ask Price: B76.Price.Put(Future: Reference1.Ask, Strike: Primary.Strike, Rate: Rate, Time: Primary.YearsToExpiry, Volatility: Volatility)

Put Delta Hedge Price: B76.ImpPrice.Put(Initial: Reference1.Settle, Price: Fill.Price, Strike: Primary.Strike, Rate: Rate, Time: Primary.YearsToExpiry, Volatility: Volatility)

To adjust volatility, you can click and drag each bid/ask volatility, noting your relation to the implied volatility, or adjust an entire of strikes. You can also manually set your own skew (linearly or exponentially), or just offset from implied volatility.

Vol Edit Window

In the next post, I will detail how to trade options spreads using Spread Bandit. In the meantime, 2 week free trials are available on the Scaled Dynamics website. If you’d like to learn more, contact

October and November Travel Schedule

Chicago is starting to get colder. Those of you who know me well know that I don’t tend to stick around during the nasty Chicago winters. I’ve booked myself through most of October and November, however I’d love to get out more. If you would like to see Justin Braun in your city, shoot me an email and I’ll try and add your city to my roster provided I can either find a business case to go, or you have something REALLY fun and compelling to offer.

October 13 thru October 16 – New York City, New York
October 17 thru October 23 – London, UK
October 28 thru November 2 – Santa Barbara, California
November 13 thru November 19 – Phoenix, Arizona