You can also request historical quote data from the TeleTrader 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.
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). The symbol identification number can be obtained by using the command Copy as RTD link in the TeleTrader WebStation. You can also use the Lookup command to search for a symbol ID within Excel (see Searching for TeleTrader Symbols). |
|
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 (1-60) 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.
For intraday data, specify start or end of the time region in the date/time format DDMMYYYYhhmm.
S=<DateTime> Start date E=<DateTime> End date |
|
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. |
Retrieve the volume of the Microsoft stock for 3 days counting backwards from April 5th, 2006 in a daily compression (each formula needs to be entered in a different cell):
=RTD("ProfRTD";;"History";"{829802},Daily, VOLLAST,E=05042006";1)
=RTD("ProfRTD";;"History";"{829802},Daily, VOLLAST,E=05042006";2)
=RTD("ProfRTD";;"History";"{829802},Daily, VOLLAST,E=05042006";3)
Use cell references to make the formula reusable for any symbol (the correct symbol ID should be entered into the referenced cell A1):
=RTD("ProfRTD";;"History";A1&",<Period>,<Field>,<TimeRegion>";<Index>)