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:
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.
Each field can be one of:
| Code | Ticker Code |
| Exchange | Exchange |
| Name | Company Name |
| ShortName | Short Company Name |
| QuoteBases | Quote Bases |
| State | Trading State |
| Suspended | Suspension state (Y or N) |
| GICS | GICS (8 hex digits) |
| GICSGroup | GICS with least significant 6 digits masked off |
| GICSIndustry | GICS with least significant 4 digits masked off |
| GICSSector | with least significant 2 digits masked off |
| Open | First on market price traded in interval |
| First | Alias for Open |
| High | Highest on market price traded in interval |
| Low | Lowest on market price traded in interval |
| Last | Last on market price traded in interval |
| Price | Last or Previous Close |
| Change | Change since previous close |
| PctChange | % Change since previous close |
| Volume | Volume traded in interval |
| Value | Value traded in interval |
| Turnover | Alias for Value |
| VolumeYesterday | Volume traded at this time yesterday (approx) |
| VolumeWeekAvg | Average volume traded by this time of the day over the last week |
| VolumeMonthAvg | Average volume traded by this time of the day over the last month |
| RelVolumeYesterday | Difference between volume now and volume traded at this time yesterday (approx) |
| RelVolumeWeekAvg | Difference between volume now and average volume traded by this time of the day over the last week |
| RelVolumeMonthAvg | Difference between volume now and average volume traded by this time of the day over the last month |
| PctRelVolumeYesterday | % Difference between volume now and volume traded at this time yesterday (approx) |
| PctRelVolumeWeekAvg | % Difference between volume now and average volume traded by this time of the day over the last week |
| PctRelVolumeMonthAvg | % Difference between volume now and average volume traded by this time of the day over the last month |
| TotalIssue | Total number of shares on issue |
| MarketCap | Market Capitalisation |
| NumTrades | Number of trades |
| MarketPrice | Last on market traded price |
| PrevClose | Previous close |
| VWAP | Volume Weighted Average Price |
| AuctionPrice | Price that auction will execute at |
| AuctionVolume | Volume that will execute at auction |
| AuctionSurplus | Difference between buy volume and sell volume at auction |
| PostAuctionTopBuy | Top bid price after auction |
| PostAuctionTopSell | Top offer price after auction |
| PostAuctionTopBuyVolume | Volume at top bid price after auction |
| PostAuctionTopSellVolume | Volume at top offer price after auction |
| Time | Time of interval |
| Date | Date of interval |
| TimeLastTrade | Time of last trade |
| CondCodes | Trade Condition Codes |
| Market | Exchange |
| Buy | Price of buy order |
| BuyCount | Count of buy orders at depth level |
| BuyVolume | Volume of buy orders at depth level |
| BuyDate | Date of buy order |
| BuyTime | Time of day of buy order |
| BuyDateTime | Time of buy order |
| BuyMarket | Market of buy order |
| Sell | Price of sell order |
| SellCount | Count of sell orders at depth level |
| SellVolume | Volume of sell orders at depth level |
| SellDate | Date of sell order |
| SellTime | Time of day of sell order |
| SellDateTime | Date and time of sell order |
| SellMarket | Market of cell order |
| TradeSide | Whether the trade came from a Buy, a Sell, or an Auction |
| CallPut | Derivative type (CALL or PUT) |
| Underlying | Ticker code of the derivative's underlying instrument |
| ExpiryDate | Expiry Date of derivative |
| StrikePrice | Strike Price of derivative |
| ContractSize | Contract Size |
| OpenInterest | Number of contracts held at end of previous session |
| AdjustmentFactor | Gross adjustment for the date in stock history |
| ShortPct | % of shares held short |
| ShortPctChange | Change in % of shares held short between latest report and previous report |
| ShortPctChangeWeek | Change in % of shares held short between latest report and one week prior |
| ShortPctChangeMonth | Change in % of shares held short between latest report and one month prior |
| ShortPctChangeYear | Change in % of shares held short between latest report and one year prior |
| ShortDaysToCover | Number of days to |
| ShortSharesHeld | Number of shares held short |
| ShortReportTotalIssue | Total number of shares on issue on the date of the latest report according to Spark preference |
| ShortReportDate | Date of the latest short report |
| Version | Spark version e.g. v1.2.3 |
| FullVersion | Spark version with build number, e.g. v1.2.3.4 |
| BuildNumber | Spark build number |
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 ?.