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 Spark.

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

<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

<options> 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)

<fields> is a list of fields separated by dots. Each field can be one of:

Code
Exchange
Name
ShortName
QuoteBases
State
Suspended
GICS
GICSGroup
GICSIndustry
GICSSector
 Open
First
High
Low
Last
Price
Change
PctChange
Volume
Value
Turnover
 VolumeYesterday
VolumeWeekAvg
VolumeMonthAvg
RelVolumeYesterday
RelVolumeWeekAvg
RelVolumeMonthAvg
PctRelVolumeYesterday
PctRelVolumeWeekAvg
PctRelVolumeMonthAvg
TotalIssue
MarketCap
 NumTrades
MarketPrice
PrevClose
VWAP
AuctionPrice
AuctionVolume
AuctionSurplus
PostAuctionTopBuy
PostAuctionTopSell
PostAuctionTopBuyVolume
PostAuctionTopSellVolume
Time
Date
TimeLastTrade
CondCodes
 Buy
BuyCount
BuyVolume
BuyDateTime
Sell
SellCount
SellVolume
SellDateTime
 CallPut
Underlying
ExpiryDate
StrikePrice
ContractSize
OpenInterest
 Version
FullVersion
BuildNumber

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 s, m or h to denote seconds, minutes or hours, such as 60s, 5m or 1h. The default update frequency is 1s.

Examples

Below are some example DDE formulae for Excel and OpenOffice.

Basic last price lookup for BHP

Excel: =Spark|BHP.Quote!Price
OpenOffce: =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
OpenOffce: =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.20
OpenOffice: =DDE("Spark";"NAB.History";"Date.First.High.Low.Last.Price.Change.PctChange.Volume.20")

VWAP trades for BHP

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

Cell Formats

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.