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?
Importing price and volume data is one way to implement Excel for trading. This is typically done through DDE links to an internal or external pricing database. DDE links are easy to use and do a good job of updating fast moving prices, but cannot handle huge volumes. Alternately, you can import price and volume data into Excel from the Internet using web queries directly from Excel's Data from Web functionality. This is good for basic data capture of prices, volume, financial statements, etc. from Yahoo Finance, MSN Money Central, Quicken and other standard websites. Finally, you can import data into your spreadsheet using the Data from Other Sources function which allows you to use SQL Server, MS Analysis Services, XML files, and ODBC connections.
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.
Implementing Excel for trading requires planning your spreadsheet designs to integrate everything together correctly. The key things are having accurate and well tested formulas, and being able to find what you need when you need it. Multiple simpler spreadsheets linked together or a single large spreadsheet with multiple tabs are possible. You will likely have a mixture as you build out your spreadsheets. Keep in mind that it's easier to manage small workbooks with fewer tabs and they take up less memory and run faster. The ideal approach is to design in a modular way with each spreadsheet for a specific purpose. Be careful of external links, however. These can break and slow things down, and are difficult to debug if you have a lot of them. Also, if your spreadsheets have more than 10,000 rows of data, charts, and multiple tabs together then they may slow down. It's risky to have your whole trading workflow in one Excel file. Be sure to back up your files externally.
Considering these factors beforehand will help you put together the best Excel for trading layout to achieve your specific needs.
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?
Importing price and volume data is one way to implement Excel for trading. This is typically done through DDE links to an internal or external pricing database. DDE links are easy to use and do a good job of updating fast moving prices, but cannot handle huge volumes. Alternately, you can import price and volume data into Excel from the Internet using web queries directly from Excel's Data from Web functionality. This is good for basic data capture of prices, volume, financial statements, etc. from Yahoo Finance, MSN Money Central, Quicken and other standard websites. Finally, you can import data into your spreadsheet using the Data from Other Sources function which allows you to use SQL Server, MS Analysis Services, XML files, and ODBC connections.
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.
Implementing Excel for trading requires planning your spreadsheet designs to integrate everything together correctly. The key things are having accurate and well tested formulas, and being able to find what you need when you need it. Multiple simpler spreadsheets linked together or a single large spreadsheet with multiple tabs are possible. You will likely have a mixture as you build out your spreadsheets. Keep in mind that it's easier to manage small workbooks with fewer tabs and they take up less memory and run faster. The ideal approach is to design in a modular way with each spreadsheet for a specific purpose. Be careful of external links, however. These can break and slow things down, and are difficult to debug if you have a lot of them. Also, if your spreadsheets have more than 10,000 rows of data, charts, and multiple tabs together then they may slow down. It's risky to have your whole trading workflow in one Excel file. Be sure to back up your files externally.
Considering these factors beforehand will help you put together the best Excel for trading layout to achieve your specific needs.
About the Author:
The best Excel for trading systems are created by using Excel's spreadsheets, formulas, and VBA code together to build a cohesive yet flexible system. Get your FREE guide on building trading models in Excel by visiting ExcelTradingModels.com
