Importing from text files is one of the most common ways to get price-volume data into your Excel spreadsheet.
The import operation is usually done using some automated VBA code in Excel, or with a batch process which is run on a timer (for example, end of day prices imported to generate P&L, risk, and trading signals).
Text files are used when real-time performance is not needed. The data is “snapshot” from the markets and a process is run to import the market data into the Excel trading model or data repository. These small delays are insigificant for trading signals calculated on wide intraday intervals such as 15, 30, or 60 minutes, and daily (End of Day or EOD), weekly, or monthly data.
Market data text files come in a wide variety of formats and layouts. Typical formats include:
- ASCII Text (.TXT)
- Comma-Separated Values (.CSV)
- Extensible Markup Language (.XML)
- Various software vendor formats such as MetaStock, TradeStation, etc.
Using text files to import market data into Excel has two main advantages. First, the data can be edited and and formatted in Excel, Notepad, Wordpad or any other simple text editor. This makes it easy to understand and cheap to implement.
Second, the time delay inherent in using text files allows for some data cleansing operations which guarantee the integrity of the market data. The value of clean, accurate, and complete data should not be underestimated. Bad data is the #1 reason for trading model errors and the #1 complaint of market analysts! Cleansing operations include split adjustments, dividends, missing prices, incorrect decimal placement, dead (unchanged) prices, wrong security symbols, etc.
Importing market data from text files can be done manually using Excel’s Import Wizard, or automatically using Excel VBA data import functionality.
