DDE - Exporting real-time data from Spark
What is DDE
DDE stands for Dynamic Data Exchange. The primary function of DDE is to allow Windows applications to share data. For example, a cell in Microsoft Excel could be linked to a value in another application and when the value is changed, it would be automatically updated in the Excel spreadsheet.
DDE and Spark
Spark incorporates a DDE server which allows users to extract certain data from Spark and link it to DDE capable applications like Excel. Data can be updated in real-time, or on a periodic basis.
DDE functionalty is automatically enabled once you login to Spark.
Possible uses for DDE include
- Real-time portfolio valuations
- Automated report generation
- Development of proprietary trading models
- Advanced market scanning
Spark's DDE Specification
DDE is accessed by specifying 3 parameters: Service, Topic and Fields. Each DDE client application may have a different way of writing the parameters. Formula syntax for Excel and OpenOffice follows, but please consult your application's DDE interface help for further information.
Spark's DDE service name is
To create a DDE link in a spreadsheet, select the cell(s) or column(s) that you want updated and enter a formula as follows:
Tip: To update multiple cells, press <control> <shift> <enter> when entering the formula, rather than just <enter>.
<topic> is a
<name> optionally followed by a
<type>, optionally followed by any number of
<options>, all separated by dots (periods).
<name> can be a stock code, an index code, a watchlist name or a smartlist name. You can specify watchlists and smartlists by their number, as in W1, W2 or S1, S2. You can optionally specify an exchange for stocks and indexes by appending ".A" for ASX, ".O" for ASX Options or ".N" for NZX to the code.
Tip: If a watchlist or smartlist's name contains a space or other characters that Excel does not allow, enclose the whole topic in single quotes. Alternatively, spaces can be replaced with underscores.
<type> can be one of:
- Quote - top level quote data for specified stock or index
- Depth - market depth data for specified stock, aggregated by price
- Orders - market depth data for specified stock, individual orders not aggregated by price
- Trades - trade data for specified stock or index
- Options - derivatives with specified stock as underlying
- History - end of day historical values for specified stock or index
- IndexQuote - quote data for specified index
- IndexValues - intraday index values for specified index
- IndexHistory - end of day historical index values for specified index
- Watchlist - top level quote data for stocks and indexes in specified watchlist
- Smartlist - top level quote data for stocks and indexes in specified marketscans
- Version - Spark version information
- VWAPOnly - only return trades that affect VWAP (Trade query only)
- FHLLOnly - only return trades that affect First, High, Low or Last (Trade query only)
<fields> is a list of fields separated by dots. Each field can be one of:
Controlling the number of rows
Optionally, you can append to the list of fields a
count to limit the maximum number of rows returned. e.g. appending ".20" will limit the result to at most 20 rows.
Controlling the update frequency
You can also append a
period to designate how frequently you want the information updated. This is useful to prevent Excel consuming the CPU on frequently changing information.
<period> can be
manual, which requires you to manually request to update the data, or a number followed by
h to denote seconds, minutes or hours, such as 60s, 5m or 1h. The default update frequency is 1s.
Below are some example DDE formulae for Excel and OpenOffice.
Basic last price lookup for BHP
Market Depth for BHP
Market Depth for BHP - First 100 rows, updated every 2 seconds
Top level quote information everything in the watchlist "My Stocks"
Note that because Excel does not allow spaces in fields, "My Stocks" has been written "My_Stocks". Spark will still find the "My Stocks" watchlist. That same example could have been written as:
End of day prices for NAB - Last 20 records
VWAP trades for BHP
Spark encodes Time fields as
HH:MM:SS and Date fields as
DD MMM YYYY. You may need to change the cell format in your application to have these fields display properly.
Prices are in dollars.
Numbers are given in up to 4 decimal places.