Home | Business News | Browse by Publication | I | Interfaces

Optimal trading of ETFs: spreadsheet prototypes and applications to client-server applications.

Article Excerpt
This paper presents an application of an Excel spreadsheet-development methodology used by quantitative analysts and traders in financial markets. The spreadsheet used regression and Excel's Solver to determine the optimal investment of a firm's risk capital. The proprietary methodology used to develop real-time trading tools and its repetitive design structure allowed the firm to become a market-maker exchange traded fund (ETF) rapidly. By adhering to the methodology, the firm's documentation of user requirements, data inputs, calculations, and user interfaces, and a full prototype using Excel, made incremental growth possible and provided a solid foundation for conversion into coded software. Rapid development gave the firm the opportunity to derive revenue from market-making activities in new investment products; these would become a major source of revenue. This methodology, which the authors presented in 2001 at the International Conference on Software Quality in Pittsburgh, Pennsylvania, and its implementation led to the development of a complete trading-system development methodology.

Key words: securities; brokerage; trading; system design; operations; OR/MS implementation. History: This paper was refereed.

**********

Spreadsheets enable engineers to focus on business models; users need not be computer scientists to program sophisticated algorithms. However, material errors in spreadsheets arise often because users and programmers are not well-versed in proper programming practices, although spreadsheets are as amenable to rigorous development practices as other programming languages. Boehm and Basili (2001) have found that good architectural practices in software development can reduce the cost of program implementation by avoiding defects. Rafftensperger (2001) estimated that 90 percent of spreadsheets have errors with mild to severe consequences. Studies by Davies and Ikin (1987) and Cragg and King (1993) found that most spreadsheet-development processes are very informal; this finding persists today, especially in the financial industry where the need for speed begets poor development practices (Kumiega and Van Vliet 2006).

Spreadsheets are ubiquitous in financial markets. Many software applications for the trading of securities and derivatives evolve from spreadsheets for three reasons:

* Excel enables easy connectivity to real-time market data feeds through Dynamic Data Exchange (DDE) and Application Programming Interfaces (APIs).

* Third-party calculation engines to build and test sophisticated models and optimization routines are available.

* Excel's intuitive interface enables end users to program applications rapidly.

Financial firms manage trillions of dollars in notional value every day using hybrid Excel spreadsheets. (The many graduate programs in financial engineering teach optimization using spreadsheets in their curricula, largely because of the pioneering research of Harry Markowitz, Myron Scholes, and Fisher Black.) Rapid development and testing of models and software is a key driver of profitability because millions of dollars are at stake. Our experience shows that management and end users can see and understand complex models and optimization routines to manage millions of dollars of partners' capital easily only using Excel because equations are transparent in Excel.

Application of Spreadsheets

In 2001, the Philadelphia Stock Exchange (PHLX) designated TFM Investment Group (TFM) as a market maker in exchange traded funds (ETFs). Actual trading was to begin within 45 days. As is often the case with new financial products, management expected trading volume in the initial months to be low, increasing over the longer term. TFM chose to implement its trading models first in Excel, then to convert them later into C++ client-server applications if necessary. At the time, Andrew Kumiega was Director of Financial Engineering at TFM.

Andrew Kumiega and TFM management partners, Mike Coghlan and Randy Chandra, applied an early version of the Kumiega | Van Vliet (K|V) Trading System Development methodology. This methodology requires development of rapid, iterative prototypes for testing and implementation with crossover of functionalities into VBA code in the expectation of later conversion to application software using C++. The application of this iterative development methodology, which required intensive end-user involvement and early-and-often delivery of incremental prototypes for feedback, virtually guaranteed trader acceptance and successful deployment.

Spreadsheets enable rapid development. Profitable trading opportunities can come and go before development teams can build and test full client-server applications. As Smith et al. (2007, p. 69) point out, "Some projects never [progress] past the proof-of-concept stage because the spreadsheets [are] appropriate." However, trading applications that demonstrate longer-term potential for profitable implementation can readily be converted to traditional client-server applications. As Grossman (1999) identifies, "spreadsheets are useful for quick prototyping, even of models that will ultimately be written in special-purpose software." Therefore, there is a financial link through the theory of real options. One can think of the cost of spreadsheet development as a premium on a real call option; if the business models prove themselves, management can fund the cost of conversion to a client-server application fully.

ETF Overview

An ETF is a basket of stocks or bonds that mirrors an index. ETFs allow investors to purchase shares of the index without buying shares of each of the constituent, or component, securities in the index. Professional fund managers and retail investors can buy and sell ETF shares on an exchange as they would shares of stock (NASDAQ 2006). Retail investors typically buy ETFs as long-term investments in indexes or sectors, while professional fund managers tend to trade them more frequently to implement sophisticated market strategies. Nevertheless, the price of an ETF share is normally set at a value such that a retail investor could afford to purchase 100 shares (unlike the index itself, which can increase ad infinitum). However, the price of an ETF share must be tied to the aggregate...