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.
There are many ways to use Excel for trading, and your first consideration should be narrowing down your intended use of the tool. Will you use it to compute trading signals? Is your interest importing data automatically into Excel? How about calculating profits, drawdowns, risk and other analytics? Do you have many open positions you need to track? Would you like to integrate Excel with a charting platform? Are you interested in automating your workbooks with VBA to increase speed and accuracy?
Bringing price and volume data into a spreadsheet automatically is one way to implement Excel for trading. This uses DDE links to a price data database, either an internal or vendor provided database. DDE links are efficient and can capture fast moving prices (with certain limitations relevant to algorithmic trading). Importing price and volume data into Excel with web query functionality is an alternative to DDE links. This works if you want to capture a smaller volume of prices or economic data from websites like Yahoo Finance, Google Finance, etc. You can also import data into Excel using the Data from Other Sources function. This connects to SQL Server, MS Analysis Services, XML files and ODBC — this is a good option for the technically minded.
Using Excel for trading is highly dependent on data. Importing prices and fundamental data into Excel automatically is a great first step to implement Excel for trading. In fact, not much else can be achieved until you import data, so this is a basic foundation step. There are multiple ways to do this. DDE links can be used to import data from a data vendor. Your broker\’s API can be used to connect to the actual prices your broker uses. Internal or vendor provided databases can be connected using SQL or web queries. How you implement the data import will have a lot to do with your strategy and the data types you want. For automated intraday trading with fast moving prices a DDE link is best. The Data from Other Sources function in Excel uses SQL Server, XML files or ODBC to connect to a database if you have one internally at your office or home. Web queries can work for end of day and fundamental quarterly type data. Economic data comes out infrequently so speed is not an issue.
Best practices of Excel for trading involve planning your spreadsheet workflows and relationships so everything works together correctly and you can find what you need when you need it. You have a choice here of building a multiple spreadsheet environment or creating a single workbook with lots of tabs. The prior approach is modular and tends to work well because each separate workbook is for a specific purpose, small, and easy to manage. The downside is you may need to manage lots of links and Excel links have a tendency to break and get corrupted. Big workbooks with lots of sheets can be useful in Excel for trading since you have everything in one place. However, Excel tends to bog down and the files get huge when you start using more than 10,000 rows of data, charts, and multiple tabs together. It can also be a bit risky to have your whole daily trading operation in one file. Just make sure you back up your files in an external location every day!
These ideas should help you get started using Excel for trading to improve your trade processes and increase profits with less risk.
Want to learn more about Excel for trading? Visit this site for a FREE GUIDE on building trading models in Excel.