Dynamic Data Exchange (DDE) allows information from Aspen to be transferred directly to a spreadsheet program. The links between Aspen and the spreadsheet are live. As information comes into the Aspen program or as Aspen calculates a value, it is immediately updated in the spreadsheet.
Links can contain quote information, historical data arrays, special option quote fields, and the outputs of Aspen formulas.
MicrosoftÒ Excel is the only spreadsheet program that we currently recommend with regards to DDE, although customers have successfully used DDE with other programs such as Lotus 1-2-3.
You can create a DDE link from within either Aspen Graphics or Excel. Aspen Graphics must be running for the link to be created. In the following examples, we will create the four different types of links from within the Excel program.
If you create the DDE link from within Excel, your link will automatically be pasted into the highlighted cell(s), so make sure you have selected the appropriate cells before you create the link. You only need to select the cell where you want the link to begin.
To create a real-time quote link, follow these steps:
| If the toolbar is not displayed, call Aspen Graphics Technical Support for assistance. |
Figure 1
DO NOT HIT ENTER!
| The elements you select will be displayed in Excel in the order chosen, from left to right, as an indivisible array. You won't be able switch the fields around once they are pasted into Excel, so carefully plan how you will want to view the link. |
To create a link, using historical data, follow these steps:
Figure 2
The Start Date defines the earliest data for your link. The End Date defines the most recent data in the link. Start and End Dates must be entered in MM/DD/YY format. For example, to enter the start date of March 1, 1997, type in 3/1/97.
Start Time and End Time Top of Page
If you selected an Intraday interval, a Start Time and End Time can also be entered. Times must be entered in HH:MM format, using 24-hour syntax. For example, if you wanted to enter an end time of 1:00 p.m., you would enter 13:00 in the End Time field.
Max Days
The Max Days field allows you to limit the number of days in the link if you selected a Daily interval. For example, if you would like to link the last 20 days of data, you would enter 20 in the Max Days field.
Periods Top of Page
The Periods field allows you to limit the total number
of periods, regardless of the type of interval selected. If you want to link 100 15-minute
periods to the spreadsheet, enter the number 100 in the Periods field.
| If you do not want to specify either Max Days or Periods for the link, enter a 0 in both fields. Leaving these fields blank will return an error message. |
If the data array you want to paste into Excel contains more than 30 periods,
paste your link, then highlight all the cells needed (for example, if you have 50
periods in your link, you need to select cells 1-50). Tap the F2 key one time, hold
down the   keys, and tap the  key one time. |
A single left click on the Option tab will display the following Link Generator Helper Box (see figure 3).
Figure 3
The Option Link Generator window looks identical to Quotes, but has fields pertaining specifically to options analysis
.To create an Option Quote Link, follow these steps:
| DO NOT HIT ENTER! |
| The elements you select will be displayed in Excel in the order chosen, from left to right, as an indivisible array. You won't be able switch the fields around once they are pasted into Excel, so carefully plan how you will want to view the link. |
Any Aspen Graphics formula or function can be linked to a spreadsheet. To link to a spreadsheet, follow these steps:
Example: IntrVal(sph7)
| Aspen exports the output or the value of the formula to Excel; it does not export the formula itself. |
Creating a Link in Aspen Graphics Top of Page
To create a link in Aspen Graphics, follow these steps:
DO NOT HIT ENTER!
| The elements you select will be displayed in Excel in the order chosen, from left to right, as an indivisible array. You won't be able switch the fields around once they are pasted into Excel, so carefully plan how you will want to view the link. |
| If you create the DDE link from within Aspen, your link is sent to the Windows® clipboard until you designate which cell(s) in Excel you would like the link to be pasted. |
- Activate the Excel spreadsheet where you want the link.
- Highlight the cell(s) into which the link will be pasted.
- Click on the second custom button in Excel.
| If the toolbar is not displayed, call Aspen Graphics Technical Support for assistance. |
Aspen Graphics allows the user to create his/her own "symbol" in Excel and import that information into the Aspen data files. This utility can be used to export both an array of historical data or live (updating) data from a spreadsheet program into Aspen Graphics. The first step in either case is to define the symbol in your Aspen Graphics Database.
To do this, click on the third custom DDE button in Excel.
The Create Symbol DDE Helper (see Figure 4) appears allowing you to enter all the relevant information about the symbol you are creating.
Figure 4
Table 1
defines and provides an example for each field in the Create Symbol Dialog box:| Before naming your symbol, check to make sure there is not an existing symbol trading with that name. In Aspen, bring up the page Superquote, then type in the name that you want to use for the symbol you are creating. If the message "<name_of_symbol> undefined" appears, it is okay to use that name for your symbol. If it is quoted, try a more descriptive name for your symbol. |
Field |
What To Enter |
Example |
| Symbol | Name of Symbol. | Corn_Spread |
| Description | Description of Symbol appearing on a chart or Superquote. | July/Sept. |
| Scale | Increments in which a symbol trades (same as "Units" on Superquote). | 1/8 |
| Exchange | Exchange on which the instrument will trade (for synthetic symbols based on real symbols, use the exchange for the real symbol). | CBOT |
| Type | Type of Instrument (stock, future, option, etc.). | Future |
| Base 100 | Only for interest rate futures subtracted from 100. | (leave blank) |
| Currency | Currency in which the instrument trades. | US Dollars |
| Amount/Pt | Dollar value of a one-point move. | 50 |
| Volatility | Base volatility at-the-money; optional. | (leave blank) |
Field |
What To Enter |
Example |
| Minimum Tick | Minimum number of ticks per trade (If the smallest amount a symbol can uptick or downtick is 1/8, the minimum tick should be 1). | 1 |
| Expiration | Date of Expiration of your symbol. | 12/31/97 |
| Trading Hours | Session start and ending times, and time zone of exchange (left blank, these will default to the exchange entered above). | (leave blank) |
| If symbol Type is an Option, these additional fields need to be entered: | ||
| Strike Price | Strike price of the option. | 285 |
| Underlying Symbol | Symbol for the underlying instrument of the option. | Cn7 |
| Strike Scale | Minimum strike units. | 1 |
| Put/Call | What kind of option is being created? | Call |
| Option Type | American (exercise before or at expiration) or European (exercise at expiration only). | American |
Table 1
Once the fields in the Create Symbol DDE Helper box have entries, select OK. The new symbol is automatically entered into the Aspen Graphics Symbol Info List. It will now be defined in the Aspen Graphics system but there won't be any price data for it yet.
Exporting historical data from Microsoft® Excel Top of Page
| Before the data can be assigned to a
symbol, the symbol must be defined using the method described in the previous sections. |
First Cell Second Cell
| Corn_Spread | hloc | ||||
| 5/1/97 | 6:00 | 16 |
13.75 |
14 |
16 |
| 5/2/97 | 6:00 | 15.75 |
14.5 |
14.5 |
15.25 |
| 5/5/97 | 6:00 | 16.5 |
15 |
15 |
16.5 |
| 5/6/97 | 6:00 | 16.5 |
16 |
16.5 |
16 |
| 5/7/97 | 6:00 | 16.5 |
15 |
16.5 |
15 |
| 5/8/97 | 6:00 | 15 |
14.5 |
15 |
14.75 |
Figure 5
Figure 5 shows an array of data. In addition to the date, time, high, low, open and close for each day, there is a top row of information which tells Aspen where to assign this new data. The first cell of the top row contains the name of the symbol you created. The second cell is a code which signifies the order of the data in the table. Table 2 lists the five choices for the contents of the second cell.
| Contents of 2nd Cell | Type of Data | Aspen Datafile | Order of Data in the array | |
| TICKHIST | Tick data | Ticks.dat | Date, Time, Price, Volume | |
| HLOC,15 | 15 minute data | Bars.dat | Date, Time, High, Low, Open, Close | |
| HLOCV,15 | 15 minute data | Bars.dat | Date, Time, High, Low, Open, Close, Volume, Open Interest | |
| HLOC | Daily data | Days.dat | Date, Time, High, Low, Open, Close | |
| HLOCV | Daily data | Days.dat | Date, Time, High, Low, Open, Close, Volume, Op Int | |
Table 2
To export the data into Aspen, highlight the entire array (Top Row and Body), and single-left click on the fourth custom Aspen button.
Using the AspenTick Function Top of Page
The AspenTick function allows dynamic links to be created from Excel to be exported to Aspen Graphics in real time. The AspenTick value will automatically update in Aspen each time the value changes in Excel. First, create a symbol using the method described in the section Creating a Symbol Using DDE.
| The AspenTick function sends information to Aspen as tick data. It will be written to the ticks.dat data file. You'll be able to see it in a tick or intraday chart or in a quote window. You won't be able to view this data on a daily (or higher time frame) chart, because those charts reference the days.dat datafile. To chart information from Excel on a daily chart, use the method described in the previous section Exporting Historical Data from Excel. |
Once you have a cell in Excel that is producing the values you want to export to Aspen, follow these steps:
Figure 6
Table 3
provides an explanation for each of the Aspen Tick fields.
| Name of field | What to Enter |
| Symbol | Enter the name of the symbol you created. |
| Price | Single-left click on the cell which contains the value that you want to link to Aspen. |
| The next three fields are optional: | |
| Count | To have this link update only when its value changes, enter a zero (0). If this field is left blank, the link will update any time any link on the spreadsheet updates. |
| Bate | If you want to define the price as a TRADE, BID, ASK, SETTLE, OPEN or CLOSE, enter that quote code here. If left blank, it will default to Trade. |
| TkVol | Enter the tick volume associated with the price. If left blank, the value will be 1. |
Table 3