You can also request historical quote data from the Market Data Server and display it in Microsoft Excel. To do so, you must use an RTD formula in the cell of your spreadsheet where you want to show the data.
▪ Open a Microsoft Excel spreadsheet.
▪ In the cell where you want the data field to appear, type (or copy) the following formula:
=RTD("ProfRTD";;"History";"{829802},Daily, PCLOSE, E=23092008";1)
▪ The requested data will appear in the Microsoft Excel cell. The request above will for example fetch the Close price of the Microsoft stock on September 23rd, 2008.
Besides manually generating RTD links, you can also generate RTD links for retrieving the historical data using TeleTrader WorkStation Add-in for Excel.
▪ Open a Microsoft Excel spreadsheet.
▪ Click the TeleTrader WorkStation ribbon in the Excel toolbar.
▪ Click the RTD Server toolbar button and then the Show button.
▪ In the Formula Generator pane define: Symbol name, Interval, Time Series and Quote Elements.
▪ Select one cell in the Excel spreadsheet and click the Insert button.
▪ Data will be displayed as an array to the right and down starting from the selected cell in the Excel spreadsheet.
|
Symbol |
Defines the symbol identification number (for example {829802} ) or unique name (for example MSFT_0FSPC) of the symbol for which you want to retrieve historical data. If you don't know the symbol's identification number, enter the symbol name, ISIN or WKN in the field and click the Search button. In the Symbol Search Results dialog, double-click the symbol that you want to use. |
|
Interval |
Choose the desired time compression for the historical data series. |
|
Start Date |
Choose the first date for the historical data series. |
|
End Date |
Choose the last date for the historical data series. |
|
Records |
Enter the number of retrieved historical data records – this setting defines the number of rows that will be added in the spreadsheet. |
|
Display Time |
Select this option to show time stamps in the Start Date and End Date date pickers (relevant when using Intraday data). |
|
Sort Descending |
Check to sort the historical data in descending order (newest data on top). Clear to sort the historical data in ascending order (oldest data on top). |
|
Quote Elements |
Select all quote elements that you want to show for the historical data series - this setting defines the number of columns that will be added in the spreadsheet. |
|
Select All |
Selects all quote element items. |
|
Insert |
Copies the RTD formulas into the Excel spreadsheet, based on the selections above. |
Note This interface can be used with Excel 2007 (or higher). During installation of the latest version of TeleTrader WorkStation, you might be required to download and install Microsoft VSTO 2010 Runtime, which is a prerequisite for running the TeleTrader WorkStation Add-in for Excel. If you don't install Microsoft VSTO 2010 Runtime, you will still be able to use RTD and DDE links, but you will not be able to use the graphical user interface of the Add-in.
In a Microsoft Excel spreadsheet, use the following syntax to request historical quote data with RTD:
=RTD("ProfRTD";;"History";"<Symbol>,<Period>,<Field>,<TimeRegion>";<Index>)
Note The character you have to use for separating the different arguments of the RTD function (shown as a semicolon ; above) depends on the regional configuration of your operating system. Under Windows, you can change this configuration in the Control Panel.
|
Symbol |
Internal identification of the symbol. You can either use the symbol identification number (for example {829802} ) or its unique name (for example MSFT_0FSPC). To find out the identification of a symbol, click in the row of the symbol in a price page and press F2. Identification number and unique name can also be displayed in the price page columns TID and TUN or in the Chart Properties dialog for a symbol. You can also use the Lookup command to search for a symbol ID within Excel (see Searching for Symbols with RTD). |
|
Period |
Data compression that you want to use. MONTHLY Use a monthly compression WEEKLY Use a weekly compression DAILY Use a daily compression MINx Use an intraday compression and specify the number of minutes (possible values: 1, 2, 3, 4, 5, 6, 10, 12, 15, 20, 30, 60, 120, 180, 240, 360, 480, 720) For example: MIN5 – 5-minute compression TICK Use uncompressed data (tick by tick) |
|
Field |
Name of the data field that should be retrieved. The following fields are available: DATE Date for the retrieved record / data point TIME Time for the retrieved record / data point LAST Last price for the retrieved record / data point (for tick data only) OPEN Open price for the retrieved record / data point HIGH High price for the retrieved record / data point LOW Low price for the retrieved record / data point PCLOSE Last price for the retrieved record / data point VOLLAST Volume for the retrieved record / data point OI Open interest for the retrieved record / data point (for daily, weekly and monthly data only) |
|
TimeRegion |
You can specify a time region using a start date and end date. For different data compressions, you can define the time region in different ways.
For tick data, use NOW. This specifies the current point in time.
For daily, weekly or monthly data, enter the start or end dates for the time region that should be retrieved in the format DDMMYYYY. You can specify one or both dates. When you only use a start date, the end date is implicitly set to the current date/time.
S=<DateTime> Start date E=<DateTime> End date
For intraday data, specify start or end of the time region in the date/time format DDMMYYYYhhmm. |
|
Index |
The RTD functionality in Excel can only connect single cells to a data source. If you need more than one cell of data for the same symbol and time region (for example, the last 30 ticks of a symbol), you can use the Index parameter. An index of 1 means that the data for the specified time / date is retrieved. An index of 2 means that the data from the last bar before the specified end date is retrieved, and so on. |
Tips and Examples for Retrieving Historical
Data