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