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.

To request historical quote data in Microsoft Excel (example)

▪       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.

To request historical quote data in Microsoft Excel using Excel Add-in

Besides manually generating RTD links, you can also generate RTD links for retrieving the historical data using TeleTrader Professional Add-in for Excel.

▪       Open a Microsoft Excel spreadsheet.

▪       Click the TeleTrader Professional 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 Professional, you might be required to download and install Microsoft VSTO 2010 Runtime, which is a prerequisite for running the TeleTrader Professional 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.

 

Syntax for RTD Links to Historical Data

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 (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.

 

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.

Learn more:

Tips and Examples for Retrieving Historical Data