You can request historical quote data in your spreadsheet software from the TeleTrader Market Data Server. To do so, you must use a special formula syntax to tell TeleTrader WorkStation DDE what data you want to fetch.

To request only a single data field (for example, the Close price at a specific date), you can use the normal formula functions of your spreadsheet software. To request multiple data fields or records (for example, the dates and Close prices for a certain period of time), you have to use an array formula. With array formulas you can populate many data fields with one single command. Probably you will use array formulas most of the time.

Note             The following examples describe how to set up a DDE connection in Microsoft Excel. You can also use Open Office instead. Syntax differences are explained below.

To request historical quote data in Microsoft Excel (single data field)

▪       Open a Microsoft Excel spreadsheet.

▪       In the cell where you want the data field to appear, type (or copy) the following formula:

=ProfDDE|History!'{829802} ,DAILY,PCLOSE,S=23092008,E=23092008'

▪       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 (multiple data fields)

▪       Open a Microsoft Excel spreadsheet.

▪       Highlight an area in the Excel spreadsheet that is big enough to hold the data fields that you want to request. For the following example, highlight an area that is six columns wide and eight rows high.

▪       Click into the formula bar in Microsoft Excel and type (or copy) the following formula:

=ProfDDE|History!'{829802} ,MIN3,DATE,TIME,OPEN,HIGH,LOW,PCLOSE,N=8,R'

▪       Press the keys Ctrl + Shift + Enter to insert the quote data as an array into the selected area.

Note             The formula should now be surrounded with braces { } in the Excel formula bar to show that it has been inserted as an array formula. You can only edit the array in whole, not single cells that are part of it. To find more information about Excel arrays, please consult the documentation for Microsoft Excel.

▪       The requested data will appear in the highlighted area in Microsoft Excel. The request above will for example fetch the fields Date, Time, Open, High, Low and Close of the Microsoft stock for the last eight ticks in a 3-minute compression.

To request historical quote data in Microsoft Excel (multiple data fields) using Excel Add-in

Besides manually generating DDE links, you can also generate DDE 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 DDE 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 DDE  formula 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.

Syntax for DDE links to historical quote data

In your spreadsheet software, certain formulas are used to create the DDE links. To request historical data, you have to manually set up the links using the following syntax:

=ProfDDE|History!'<Symbol>,<Period>,<Field1>[,<Field2>,...],<Retrieved Data>[,<Order>]'

Note             In this user guide, the syntax for Microsoft Excel is used for all examples. In Open Office, the syntax needs to be changed as follows:

=DDE("PROFDDE";"History";"<Symbol>,<Period>,<Field1>[,<Field2>,...],<Retrieved Data>[,<Order>]")

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.

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(s)

Name of the data field(s) 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)

If you want to retrieve more than one data field, separate the field names with a comma.

Retrieved Data

You can either specify how many records (data points) should be retrieved, or define a time region using a start date and end date. You cannot use both number of records and start / end date at the same time.

 

Number of records:

The last x records / data points of the symbol will be retrieved. The data is updated dynamically whenever new data comes in, so that always the most current data is shown.

N=x                           Specify the number of records / data points that should be retrieved

                                  For example: N=10

 

Time region with start / end date:

The records / data points between the start date and the end date are retrieved. You have to always specify both dates, separated with a comma, in the format DDMMYYYY.

S=<Date>      Specify the start date for the time region that should be retrieved

E=<Date>      Specify the end date for the time region that should be retrieved

              For example: S=01032005,E=31032005

 

If you want to set a time interval in addition to the date interval, you have to use the ISO date/time format YYYY-MM-DDThh:mm:ss with the parameters Sdt and Edt:

Sdt=<ISO>     Specify the start date and time for the time region that should be retrieved

Edt=<ISO>             Specify the end date and time for the time region that should be retrieved

                                  For example: Sdt=2009-02-02T19:00:00,Edt=2009-02-02T20:00:00

Order

Order of the retrieved records / data point.

Skip to show the data in chronological order: Oldest data is displayed first.

R                               Reverse chronological order: Newest data is displayed first

Note             If the result of your formula is an array, that means if you request several data fields or records at once, you have to use the array command (Ctrl + Shift + Enter) after typing the formula in your spreadsheet.

Historical data: More examples

Retrieve the last two dates, times and closes for the EURUSD currency pair (using its identification number) in a 5-minute compression (3 columns x 2 rows):

=ProfDDE|History!'{949843},MIN5,DATE,TIME,PCLOSE,N=2,R'

Retrieve the dates and closes for the Dow Jones Industrial Average (using its unique name) between April 3rd, 2006 and April 24th, 2006 in a daily compression (2 columns x 15 rows):

=ProfDDE|History!'DJI_0AWSPC,DAILY,DATE,PCLOSE,S=03042006,E=24042006'