Features Pricing Download Help Account

Exporting real-time data from Spark using DDE

Spark incorporates a DDE server that can send data to DDE capable applications like Excel. Data can be updated in real-time, or on a periodic basis.

DDE functionality is automatically available when 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


If you are making a lot of DDE queries, then see limitations for how you can improve your spreadsheets.

Basic usage

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.

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:

Excel: =Spark|<topic>!<fields>

OpenOffice: =DDE("Spark";"<topic>";"<fields>")

Tip: To update multiple cells, press Control Shift Enter when entering the formula, rather than just Enter.

Service Name

Spark's DDE service name is Spark.

If you are a Spark 1 user, then you can change the service name to Spark2 in preferences.


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 smartlist
Version Spark version information

options can include:

VWAPOnly Only return trades that affect VWAP (Trade query only)
FHLLOnly Only return trades that affect First, High, Low or Last (Trade query only)
Consolidate Consolidate ASX and CXA. Only necessary when specifying ASX exchange explicitly - see below


fields is a list of fields separated by dots.

In addition, you can limit the number of rows returned and specify the update frequency.

For the full list of fields, see here.

Setting the exchange

By default, DDE will use the Default Exchange set in your preferences.

Append .A, .C, or .N to the code to set the exchange to ASX, CXA, or NZX.

Setting the exchange disables consolidation.

The exchange is ignored in Watchlist and Smartlist DDE queries.


By default, DDE will use the Consolidate preference to determine whether to consolidate ASX and CXA stocks.

You cannot enable consolidation in DDE unless the preference is set - the Consolidate option will be ignored.

When making a DDE quote, depth, orders, trades, or history request, you can disable consolidation by appending an exchange.

For example, if your default exchange is ASX, then:

Topic Consolidate Preference Response
BHP.Quote Yes BHP consolidated ASX and CXA quote
BHP.Quote No BHP ASX quote
BHP.A.Quote n/a BHP ASX quote
BHP.C.Quote n/a BHP CXA quote

If your default exchange is NZX, then to get consolidated ASX and CXA, you must specify both A for ASX and Consolidate:

Topic Consolidate Preference Response
SPK.Quote n/a SPK NZX quote
SPK.N.Quote n/a SPK NZX quote
SPK.A.Quote n/a SPK ASX quote
SPK.C.Quote n/a SPK CXA quote
SPK.A.Consolidate.Quote Yes SPK consolidated ASX and CXA quote
SPK.A.Consolidate.Quote No SPK ASX quote

Watchlist and Smartlist responses only look at the Consolidate preference to determine whether to consolidate, so that the result matches what is shown in the main app. If you specify the Consolidate option in a DDE watchlist or smartlist query, it will be ignored.

For more about consolidation, see here.

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. can be manual, which requires you to manually request to update the data, or a number followed by ms, s, m or h to denote milliseconds, seconds, minutes or hours, such as 60s, 5m or 1h. The default update frequency is 1s.


Prices are in dollars.


Below are some example DDE formulae for Excel and OpenOffice.

Basic last price lookup for BHP

Excel: =Spark|BHP.Quote!Price

OpenOffice: =DDE("Spark";"BHP.Quote";"Price")

Market Depth for BHP

Excel: =Spark|BHP.Depth!BuyCount.BuyVolume.Buy.Sell.SellVolume.SellCount

OpenOffice: =DDE("Spark";"BHP.Depth";"BuyCount.BuyVolume.Buy.Sell.SellVolume.SellCount")

Market Depth for BHP - First 100 rows, updated every 2 seconds

Excel: =Spark|BHP.Depth!BuyCount.BuyVolume.Buy.Sell.SellVolume.SellCount.100.2s

OpenOffice: =DDE("Spark";"BHP.Depth";"BuyCount.BuyVolume.Buy.Sell.SellVolume.SellCount.100.2s")

Top level quote information everything in the watchlist "My Stocks"

Excel: =Spark|My_Stocks.Watchlist!Code.Buy.Sell.Open.High.Low.Price.Volume.Change.PctChange

OpenOffice: =DDE("Spark";"My_Stocks.Watchlist";"Code.Buy.Sell.Open.High.Low.Price.Volume.Change.PctChange")

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:

Spark|'My Stocks.Watchlist'!Code.Buy.Sell.Open.High.Low.Price.Volume.Change.PctChange

End of day prices for NAB - Last 20 records

Excel: =Spark|NAB.History!Date.First.High.Low.Last.Price.Change.PctChange.Volume.AdjustmentFactor.20

OpenOffice: =DDE("Spark";"NAB.History";"Date.First.High.Low.Last.Price.Change.PctChange.Volume.AdjustmentFactor.20")

VWAP trades for BHP

Excel: =Spark|BHP.Trades.VWAPOnly!Time.Price.Volume

OpenOffice: =DDE("Spark";"BHP.Trades.VWAPOnly";"Time.Price.Volume")

Excel formatting

Spark sends data to Excel in its native table format to improve speed and accuracy.


We suggest formatting price cells as numbers to 4 decimal places.

You may need to check for empty prices in your formulas.

Dates and times

Excel requires dates and times to be encoded as numbers.

You need to format these cells as dates or times to see them in a useful format.

Empty cells

Some fields do not have a defined value at certain times, for example the High when the stock has not traded.

By default, Spark sends these fields as blank, and Excel automatically converts these blanks to zero.

This may be acceptable and convenient for some formulas, but it could cause unexpected results.

It is often necessary to check whether a cell has a valid value before performing a calculation on it, but when Excel converts blanks to zero, ISBLANK() will fail to detect the empty cell.

If you need to be able to detect invalid values, you can set a preference to format empty cells with the error #N/A.

This can be checked with the ISNA() function.


Invalid fields appear as #NAME? errors.

Fields of stocks that do not exist are formatted with the error #N/A, apart from Code and Exchange.

TSV and CSV formatting

If your DDE client application requests the standard format CF_TEXT, it will receive tab separated responses.

If your DDE client application registers the clipboard format "Csv" and requests that format, it will receive comma separated responses.


Prices are in dollars to 4 decimal places.

Dates and times

Dates are formatted DD MMM YYYY for compatibility with Excel and Spark1 DDE clients.

Times are formatted HH:MM:SS.

Date times are formatted DD MMM YYYY HH:MM:SS.

Empty cells

Empty cells are blank by default.

The can be changed to N/A by setting a preference.


Invalid fields are indicated by ?.


Each DDE query consumes a resource and Windows puts a hard limit on how many can be in use at any one time.

You may reach the limit with as little as 1,500 simultaneous DDE queries, depending on how much memory is available to Windows.

If these resources are exhausted it could cause problems with all running programs, including Spark and Windows itself.

A very large number of queries usually indicates poor design that can be easily improved by combining the queries.

Reducing the number of queries can drastically improve the responsiveness of Excel.

Combine queries for the same topic:

Instead of making multiple DDE queries for various quote fields of the same stock, e.g.:

  • =Spark|BHP.Quote!High
  • =Spark|BHP.Quote!Low
  • =Spark|BHP.Quote!Price
  • =Spark|BHP.Quote!Volume, etc

Combine them into a single query: =Spark|BHP.Quote!High.Low.Price.Volume

Combine stocks that have the same fields:

Instead of making multiple DDE queries for different quotes that all have the same fields, e.g.:

  • =Spark|BHP.Quote!High.Low.Price.Volume
  • =Spark|NAB.Quote!High.Low.Price.Volume
  • =Spark|CBA.Quote!High.Low.Price.Volume
  • =Spark|CSL.Quote!High.Low.Price.Volume, etc

Put those stocks into a watchlist and combine the quote queries into a single watchlist query: =Spark|Example.Watchlist!Code.High.Low.Price.Volume

Showing help for Windows. Change to MacOS.
Showing help for MacOS. Change to Windows.