The Gateway to Algorithmic and Automated Trading

Automation Without Tears

Published in Automated Trader Magazine Issue 19 Q4 2010

Literally thousands of traders around the globe use Excel TM to develop and run their mechanical trading models. But until now, the unbridgeable gap has been a bullet-proof way to automate those models for real time simulation and live trading. Gary Stone, Chief Strategy Officer, and Howard Stone, Product/Business head for Tradebook API at Bloomberg Tradebook outline the ideal solution.

While power Excel TM users are commonplace in financial markets, by no means all of these users are also VBA programmers. As a result, a large segment of the trading community has been disenfranchised by the lack of any simple way to automate mechanical models that they are running in Excel. The models may spit out entry and exit signals from Excel, but the process has always still required the trader to manually click to place the requisite orders. The alternative of hiring a programmer to automate the model using VBA or another language immediately runs into the trader/programmer interface where misunderstandings and errors frequently abound.

Faster competitive productivity

This isn't just a matter of convenience and avoiding programming costs/misunderstandings. In the financial markets of today, where doing more with less is not an option, this is also about productivity, risk management and (ultimately) competitive edge:

• As more traders have access to more computational firepower, the lifespan of many trading ideas is decreasing appreciably, as too many traders can easily data mine the same alpha opportunities. Ten years ago a robust trading model might capture inefficiencies for several years. Today, an initially profitable model's performance often starts to decay in a matter of months, weeks - or even days. That places a premium upon a trading organisation's ability to maintain a production line of new models and - once fully tested - deploy them in the market as quickly as possible.

• When used intelligently, trade automation can beget greater productivity. The individual who can automate trade execution and management has additional time in which to research and develop new trading models. For those traders who don't have access to a dedicated programming team, Excel has plenty of tools to facilitate this development process without delving into VBA. The key is having the time with which to do that, which automation delivers. As a result, the trading operation as a whole is able to maximise its available intellectual capital and work more efficiently.

• While it's common for traders to claim their models are parameter-less, there's usually at least one parameter somewhere in the logic that can be optimised. Often the parameter value that is the most stable and has the most stable adjacent values in out of sample Excel testing is the one that is selected for live trading. However, slight changes in market behaviour can make this a sub-optimal choice in real time. Automation of a trading model offers an alternative - namely the simultaneous trading of a range of best performing parameter values in order to reduce optimisation risk. Workflow considerations mean that scenario isn't feasible for a human trader, but an automated Excel solution can make light work of it.


Grasping these opportunities requires an automation tool that is transparent to the trader, doesn't involve heavyweight programming and has an intuitive workflow. The obvious way of accomplishing this is an Excel component that makes automated connections behind the scenes between trading models and the required markets- without the need for the trader to worry about any of the underlying plumbing. So all he/she has to do is select a strategy from their list of pre-built trading models, choose the desired market and position size, click "Run" and allow the automation component to take care of all the entries and exits.

The trick here is to accomplish this without disrupting the trader's existing workflow. If a trader is accustomed to running their mechanical models in an Excel GUI they have already built, they don't want to have to waste time learning a new layout. Therefore, any automation component needs to be in a format that can be dropped transparently into an existing Excel workbook. It then runs invisibly in the background monitoring the status of the trader's existing mechanical models and whenever one of those flags a buy/sell condition it executes the requisite order(s).

A step up

Simple automation from within Excel obviously has a lot to offer where orders are of a size unlikely to create market impact. However, for larger orders or illiquid markets, an additional algorithmic execution element is desirable. So rather than just specifying a simple buy or sell as the response to a trade signal, the trader can also select a suitable execution algorithm to handle the order.

An additional benefit here is that Excel's ability to nest 'if' statements allows a further level of sophistication. A chain of 'if' statements can be used to automatically choose the most appropriate execution algorithm for a particular trading signal. For example: "If a trading model A flags an order for market B before C time of day and the order size is less than D shares, use execution Algo X - otherwise use Algo Y".

Segregation and overlay

In the context of trade automation, Excel's ability to run multiple instances of itself presents further opportunities. For example, trading models for individual market types (such as equities, futures or FX) can be run in separate Excel instances.

From a trader's perspective, this is ergonomically easier than having to flip among different workbooks or worksheets in the same single instance of Excel in order to monitor activity. A further advantage is that a separate instance of Excel can be run that handles just risk management overlay by monitoring the other instances.

Are you sure about that?

A potential inflexibility with automating mechanical trading models in Excel relates to deciding whether a condition can be true instantaneously (i.e. the models reacts to each new data tick) or whether it only responds to values at the close of a predefined bar length (such as 5 or 10 minutes).

Real life isn't quite so convenient - a single blip above a target level may not offer the signal confirmation a trader requires, while having to wait for the close of a 10 minute bar may see the alpha opportunity missed. Therefore there is a need for any Excel trade automation component to offer time based confirmation functionality, e.g.: "The long entry condition has gone from false to true - and has remained true for 35 seconds".

This functionality also allows trade signal strength to be allied to position size. For example, for each defined unit of time an entry or exit condition remains true it could be deemed stronger and an additional number of contracts or shares can be automatically bought/sold.


Being able to build and back test trading models in Excel and then deploy them automatically in live trading is a huge benefit for any Excel power user. However, it still omits one vital step - real time simulated (or paper) trading.

The final piece in the Excel trade automation jigsaw is to be able to conduct this type of simulated trading and then once it is satisfactorily completed switch immediately into live production without any re-coding or re-plumbing being required.


Automation of trading models within Excel can deliver value on multiple levels. In addition to the generic advantages of productivity, workflow and risk management, the right automation tool can turn Excel into a complete integrated development, testing and automated/algorithmic trading environment.

By the way, if you're looking for this tool - it's called Tradebook Order Builder...

Bloomberg Tradebook is a global agency broker offering advanced trading algorithms and direct market access to over 60 global equity, futures, and options markets and 41 currency pairs in our Foreign Exchange marketplace. Many traders have created valuation, investment and trading strategy models in various applications and nourish them the Bloomberg Professional® service data API. Now, using the same connectivity as Bloomberg's data API, traders can integrate their strategies with Bloomberg Tradebook's high performance Order API and connect their strategies to the electronic execution highway.

Nothing in this document constitutes an offer or a solicitation of an offer to buy or sell any security or other financial instrument or constitutes any investment advice or recommendation of any security or other financial instrument. BLOOMBERG TRADEBOOK believes that the information herein was obtained from reliable sources but does not guarantee its accuracy.

Communicated by Bloomberg Tradebook Europe Limited, registered in England & Wales No. 3556095, authorized and regulated by the UK Financial Services Authority No. 187492. This communication is directed only at persons who have professional experience in the investments which may be traded over the systems and certain high net worth organizations. Available in all countries and is only offered where clearance been obtained.

Bloomberg Tradebook LLC member of FINRA ( SIPC/NFA. Bloomberg Tradebook do Brasil is the representative of Bloomberg Tradebook LLC in Brazil registered with the BACEN. Bloomberg Tradebook Services LLC, Bloomberg Tradebook Australia PTY LTD ABN 36 091 542 077 ACN 091 542 077, Bloomberg Tradebook Do Brasil LTDA., Bloomberg Tradebook Canada Company Member of CIPF, Bloomberg Tradebook Limited, Bloomberg Tradebook Hong Kong Limited the first ATS authorized by the SFC-AFU 977, Bloomberg Tradebook Japan Limited member of JSDA/JIPF, Bloomberg Trading Services Japan LTD, Bloomberg Tradebook Singapore Pte Ltd Company No. 200104338R, Bloomberg Trading Services (Singapore) Pte Ltd Company No. 200101232G. Bloomberg Tradebook Bermuda LTD, licensed to conduct Investment Business by the Bermuda Monetary Authority.

BLOOMBERG, BLOOMBERG PROFESSIONAL, BLOOMBERG TRADEBOOK are trademarks and service marks of Bloomberg Finance L.P.("BFLP"), a Delaware limited partnership, or its subsidiaries. Bloomberg Tradebook is provided by a BLP subsidiary, Bloomberg Tradebook LLC, and its affiliates and is available on the BPS.

Getting it done

Figure 1: Tradebook Order Builder worksheet with a range of orders in various states

Figure 1: Tradebook Order Builder worksheet with a range of orders in various states

Figure 2: Tradebook Order Builder worksheet with orders in evaluation state waiting to be triggered by a moving average trading model

Figure 2: Tradebook Order Builder worksheet with orders in evaluation state waiting to be triggered by a moving average trading model

Bloomberg Tradebook