Every bank uses Excel for trading on a daily basis. The ordinary investor or trader won’t use Excel in this way, but the methods for employing Excel in a trading atmosphere are not hard. You simply need to understand how you intend to use Excel, and what kind of trading work-flow works for you.
One of the first concerns is how you will use Excel for trading. Will you be publishing price information into a worksheet? Will you track your positions, profits, and losses there? Do you want to incorporate it with an active trading platform? Would you like to develop a complete Excel for trading system with VBA, stock charts, order entry, and the like?
Publishing price and volume data is one way to apply Excel for trading. This is normally done by means of DDE links to an internal or external prices repository. DDE links are simple to use and do a sufficient job of posting fast paced prices, but cannot accomodate enormous volumes. Alternately, you can move price and volume details into Excel online by querying straight from Excel’s Data from Web operation. This can be best for fundamental data capture of market information, etc from Reuters and other standard online resources. Finally, you can capture market data into your spread sheet using the Data from Other Sources functionality which helps you use SQL Server, data cubes, files, and other sources.
Once you have your data into Excel for trading purposes, then what to do after that? You can configure a position-keeper, favorites, P&L tool, historical trading log, or a big pricing data set. These can then be used for present and past risk analysis, calculating your trading performance using stats like success rate, Treynor ratio, maximum drawdown, etc. There are virtually unlimited uses of Excel for trading workflows.
Best practices of Excel for trading require planning your processes and parts so everything operates correctly and efficiently. You have a choice here of designing a multi-sheet Excel file set or creating a solitary workbook with multiple tabs. The prior approach is modular and tends to work well because each file is for a specific purposeand doesn’t take up much memory space. The downside is you may need to manage lots of links which tend to get corrupted. Huge Excel models with lots of sheets can be useful in Excel for trading since you have everything in one place. However, Excel bogs down and the files get huge when you start using more than 15 thousand rows of data, graphs, and multiple tabs together. It can also be somewhat risky to have your whole trading tool set in one file. Be sure to back up your tools to another storage device every day!
Thinking about these factors beforehand will help you use Excel for trading properly.
