Features Pricing Download Help Account

Excel Add-in for RTD

Spark registers an Excel Add-in that includes an RTD server to provide realtime updates to Excel.

Spark includes User Defined Functions to simplify access to RTD.

Installation

Spark will automatically install and update the following file each time it starts:

%AppData%\Roaming\Microsoft\AddIns\Spark.xll

To activate the add-in, open Excel, choose File, Options, Add-ins, then click Go... to Manage Excel Add-ins.

If Spark for Excel is not already listed as an add-in, then click Browse... to open a file dialog at the above AddIns folder where you can choose Spark.xll.

Support for both the 64 bit and 32 bit versions of Excel

Spark will try to detect whether you have the 64 bit or 32 bit version of Office and install the correct version of Spark.xll to the Microsoft add-ins folder.

If you receive an error message when activating the add-in, then you will need to manually install the other version of the Spark add-in by following the instructions below.

Keeping up to date

When you update Spark to the latest version, it will attempt to update the Excel Add-in.

This will fail if Excel is currently using it.

Close down Excel and restart Spark (or wait a minute) to allow the Excel Add-in to update.

Example

To show 5 levels of depth for BHP:

  • Drag out a range of 4x5 cells.
  • Enter the formula: SparkDepth("BHP", "BidVolume,Bid,Ask,AskVolume")
  • Press Ctrl Shift Enter

Tips

  • Drag out a range of cells that matches the number of fields and the number of rows you want returned.
  • Use Ctrl Shift Enter when entering a formula for a range of cells otherwise Excel will complain that you can't change part of an array.
  • Excel disables RTD updates while you are typing a formula so don't accidentally leave your cursor in the formula box.
  • Don't highlight whole columns or Excel will attempt to update all 65536 rows and may become unresponsive. Instead, you should select the range of cells that you want to populate.

Stock queries

Stock (and Index) queries take a code and a comma separated list of fields, and optionally some options.

Drag out a range of cells to match the number of fields, and to limit the number of results returned.

SparkQuote(code, fields[, options])
SparkDepth(code, fields[, options])
SparkOrders(code, fields[, options])
SparkTrades(code, fields[, options])
SparkOptions(code, fields[, options])
SparkHistory(code, fields[, options])

Watchlists and Smartlists

Watchlists and Smartlists take a name or numeric index, and a comma separated list of fields, and optionally some options.

SparkWatchlist(index or name, fields[, options])
SparkSmartlist(index or name, fields[, options])

For example, Watchlist(10, "Code,Price") will return your 10th watchlist and Smartlist("Best Stocks", "Code,Price") will return your Best Stocks smartlist.

Codes and markets

By default, your default exchange determines the market.

You can specify the market directly by appending .ASX, .CXA, or .NZX to the code.

Alternatively, you can use the short forms: .A, .C, or .N.

e.g. SPK will use your default exchange, SPK.CXA will return Spark on Cboe, and SPK.N will return Spark on NZX.

If you build the code dynamically, use CONCAT(code, ".", market).

Fields

Each query takes a comma separated list of fields.

For the full list of fields, see here.

Options

Each query optionally takes a comma separated list of options:

VWAPOnly Only return trades that affect VWAP (SparkTrades only)
FHLLOnly Only return trades that affect First, High, Low or Last (SparkTrades only)
NoConsolidate Do not consolidate ASX and CXA. By default, results are consolidated if the Spark preference is enabled.

Functions for individual quote fields

The following functions return a single field from a SparkQuote.

e.g. SparkPrice("BHP") is equivalent to SparkQuote("BHP", "Price").

SparkBid(code[, options]) Stock bid
SparkAsk(code[, options]) Stock ask
SparkOpen(code[, options]) Stock or index first price
SparkHigh(code[, options]) Stock or index high price
SparkLow(code[, options]) Stock or index low price
SparkClose(code[, options]) Stock or index last price
SparkPrice(code[, options]) Stock or index price
SparkPrevClose(code[, options]) Stock or index previous close
SparkVWAP(code[, options]) Stock VWAP
SparkChange(code[, options]) Stock or index change
SparkPctChange(code[, options]) Stock or index % change
SparkVolume(code[, options]) Stock traded volume
SparkValue(code[, options]) Stock traded value
SparkAuctionPrice(code[, options]) Stock auction price
SparkAuctionVolume(code[, options]) Stock auction volume
SparkAuctionSurplus(code[, options]) Stock auction surplus volume

Utility functions

SparkOk() OK if connected to a running Spark instance that is connected to at least one market stream.
Offline if Spark is not connected to any market streams.
Unavailable if the Excel Add-in is not connected to Spark
SparkConnected() TRUE if Spark is connected to at least one market stream
SparkVersion() The Spark version
SparkFullVersion() The full Spark version with build number
SparkBuildNumber() The Spark build number

Functions specific to the Excel Add-in

The following functions return information about the Excel Add-in itself, rather than the Spark application.

SparkXLLConnected() TRUE if the Excel Add-in is connected to a running Spark instance
SparkXLLVersion() The version of the Excel Add-in. This should match SparkVersion()
SparkXLLFullVersion() The full version with build number of the Excel Add-in
SparkXLLBuildNumber() The build number of the Excel Add-in

Installing the 64 bit or 32 bit version manually

If Spark guesses the wrong version of Office, you can manually install the other version of the Spark add-in once and Spark will maintain that version.

Use File Manager to copy the appropriate version of Spark.xll to the Microsoft AddIns folder:

64 bit %AppData%\Local\Iguana2\Spark\AddIns\Spark.xll
32 bit %AppData%\Local\Iguana2\Spark\AddIns32\Spark.xll

The Microsoft add-ins folder is %AppData%\Roaming\Microsoft\AddIns

You may need to close Excel first.

Reinstalling a different version of Office

If you change your version of Office from 32 bit to 64 bit, or vice versa, then Excel will fail to load the Spark add-in.

To replace the Spark add-in with the correct version, delete it from the Microsoft add-ins folder and restart Spark.

i.e. delete %AppData%\Roaming\Microsoft\AddIns\Spark.xll

Alternatively, you can following the instructions above to replace it with the other version of the Spark add-in.

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