Automatically Updating Aspen Datafiles with Excel Data

Automatic updating involves use of a macro in ARG32.XLA and ARG.XLA. This macro is named AspenTick. It updates the symbol with the price to the number of decimal places supplied.

Note: The AspenTick macro will not work unless QMASTER.XLB (the Excel button bar) and the correct .XLA file (16-bit or 32-bit) have been loaded. Currently, we are investigating the feasibility of placing these files in the /XLSTART directory so they will load automatically and DDE will seem more user-friendly. Further note that these changes affect Microsoft Excel, not Aspen Systems. It is, therefore, imperative these files be located in the correct directories (which will be delineated in a later release of this document) and load. If these supporting files are not in place or do not load, the DDE functionality described here and elsewhere will not work.

When Aspen's supporting files have been loaded, the AspenTick macro is available through Excel's Insert menu. Open the Insert menu and select Function. This displays the Function Wizard dialog box:

At the bottom of the Function Category list is the User Defined category. Select this category and you will see the AspenTick macro appear in the Function Name list, as shown above. Highlight AspenTick and select the Next button. Selecting the Next button displays the Function Wizard's second screen:

The syntax for the macro is:

where:

"<symbol>"

The name of the instrument to be updated. The name you give must be enclosed in quotation marks. Excel's Function Wizard encloses the name in quotes for you. Note also that if you're creating a symbol, the name should not conflict with existing instrument symbols.

<price>

Specifies the Excel cell where the value you want to update is located. The cell should be formatted so that should all digits are displayed.

<decimals>

Specifies the number of digits after the decimal point.

And, Optional Arguments are:

<count>

A unique value that acts as an accumulator, or counter, for purposes of insuring tick integrity during DDE link updating. If it is included in the call, the update will only be processed if this value is different from the last time the call was made.

Microsoft Excel does not update links selectively. If more than one link is present in an Excel spreadsheet, and none of the links use the <count> argument, a change in one link causes all links to update. There is nothing you can do to change this; this is the way Microsoft Excel works.

However, you can outfox Excel by using the <count> argument in the AspenTick macro to ensure transaction integrity, an important consideration when using Excel to compile accurate transactions in Time & Sales. The <count> argument examines link updating activity and determines whether a link is ready to be updated. The <count> argument is an accumulator with an initial value of 0. When the <price> argument is updated, the <count> argument is incremented by one. When the <count> argument changes, the AspenTick macro permits Excel to update Aspen.

For example, in a spreadsheet containing two links (A and B) equipped with the <count> argument, A may be trading more often than B. When A receives a new price, Excel attempts to update both links; however, while A's <count> argument has changed, B's has not, so the AspenTick macro instructs Excel to update only A. The converse also applies.

<bate>

The bate code for the respective price. If it is NOT included in the call, only trades are sent. Values for the bate field may be:

TRADE, BID, ASK, SETTLE, OPEN, CLOSE

<tick_volume>

The tick volume associated with the price. If it is NOT included in the call, a value of 1 is sent.