A wide variety of market participants use Excel for trading on a daily basis. The steps you need to take to implement Excel correctly for trading are relatively simple. You need to think about your desired workflows, then build the various spreadsheets and data sources and integrate them.
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?
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.
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.
Considering these factors beforehand will help you put together the best Excel for trading layout to achieve your specific needs.
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?
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.
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.
Considering these factors beforehand will help you put together the best Excel for trading layout to achieve your specific needs.
About the Author:
Want to learn more on how to implement Excel for trading? Visit this site for a FREE GUIDE on creating and coding trading models in Excel.
No comments:
Post a Comment