Generating trade signals and managing existing trades are two typical uses of Excel. The everyday trader often just relies on his chart software or canned technical analysis newsletters. Building your own strategies in Excel can give you increased control, efficiency and trading profitability. There are a few things you need to learn, but overall the process is normally fairly smooth.
One of the first considerations is how you will use Excel for trading. Will you be importing price data into a spreadsheet? Will you track your positions, profits, and losses there? Do you intend to integrate it with an existing trading platform? Do you want to develop a complete Excel for trading system with VBA, charts, order entry, and such?
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.
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.
Hopefully these concepts will be useful in kick starting your Excel for trading.
One of the first considerations is how you will use Excel for trading. Will you be importing price data into a spreadsheet? Will you track your positions, profits, and losses there? Do you intend to integrate it with an existing trading platform? Do you want to develop a complete Excel for trading system with VBA, charts, order entry, and such?
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.
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.
Hopefully these concepts will be useful in kick starting your Excel for trading.
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