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:
If you are making a lot of DDE queries, then see limitations for how you can improve your spreadsheets.
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.
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.
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.
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.
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.
Prices are in dollars.
Below are some example DDE formulae for Excel and OpenOffice.
Excel:
=Spark|BHP.Quote!Price
OpenOffice:
=DDE("Spark";"BHP.Quote";"Price")
Excel:
=Spark|BHP.Depth!BuyCount.BuyVolume.Buy.Sell.SellVolume.SellCount
OpenOffice:
=DDE("Spark";"BHP.Depth";"BuyCount.BuyVolume.Buy.Sell.SellVolume.SellCount")
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")
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
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")
Excel:
=Spark|BHP.Trades.VWAPOnly!Time.Price.Volume
OpenOffice:
=DDE("Spark";"BHP.Trades.VWAPOnly";"Time.Price.Volume")
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.
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.
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.
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 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 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.
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, etcCombine them into a single query: =Spark|BHP.Quote!High.Low.Price.Volume
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, etcPut those stocks into a watchlist and combine the quote queries into a single watchlist query: =Spark|Example.Watchlist!Code.High.Low.Price.Volume