lundi 11 mai 2015

Excel For Trading Models - Using Excel's Capabilities To Make Money

By Carl Keenan


Excel is widely used by hedge funds and professional traders to manage trades, calculate P&L, compute buy and sell signals, and much more. These capabilities are available to the average trader, many of whom already use charting software to help with their trade strategies, often with limited success. Including Excel in your trading workflow process can deliver high value in terms of profitability, discipline and consistency. There are a few things you need to learn, but it is achievable with a little effort and the benefits can be very significant to your bottom line.

You should first ask yourself how you might use use Excel for trading. Will you just import prices and volume data into a spreadsheet? Do you intend to generate buy/sell signals? How about tracking positions, profits and losses in a spreadsheet? Do you have an existing trading software platform you'd like to integrate with? Would you consider building a complete Excel for trading system with VBA, formulas, price imports and other features?

There are a variety of functional options you can go with. Stock and futures watch lists are popular. These can be quite elaborate with multiple prices, colors, positions, profits, losses, etc. Real time or end of day P&L reports can be built to track your performance across trades. Tracking portfolio performance and attribution is another use. A trading log where you record your trade decision steps, emotions and results on each trade can help develop discipline and consistency. The main uses for Excel in trading include signal generation, risk and trade management. Many of these data points can be charted to provide a "one look" view.

Once you have your data into Excel for trading purposes, then what will you be doing with it? You can create a position blotter, watch list, profit and loss statement, trade history log, or a big price history database. These can then be used for current day and historical trend analysis, evaluating your trading performance using common statistics like standard deviation, sharpe ratio, drawdown, maximum drawdown, etc. There are virtually unlimited uses of Excel for trading workflows.

You should spend some time planning your spreadsheet designs before you implement Excel for trading. A good modular design helps understand the data flows and makes testing for accuracy much easier. Being able to find what you need when you need it is critical during a trading session. Several simple spreadsheets linked together can often be better and more efficiency than a single large spreadsheet with multiple tabs. It really depends on your preference and your system resources available. As you build out your spreadsheets keep in mind that it's easier to manage small workbooks and the tend to run faster. Whether you have single or multiple workbooks, each spreadsheet should have a specific purpose. A few caveats: external links can become corrupted and slow things down so be careful with them. More than 15,000 rows of data can slow Excel down. Make sure you back things up regularly. Charts should be used sparingly for intraday use, as your charting program is probably much better at this than Excel and charts tend to bloat your files.

These ideas should help you get started using Excel for trading to improve your trade processes and increase profits with less risk.




About the Author:



Aucun commentaire:

Enregistrer un commentaire