This is a minimal example implementation of an MS Office Add-In for Excel, integrating with Diffusion to stream live market data into an Excel table.
The Add-In makes use of React, Fluent UI, office.js , and the JavaScript Diffusion SDK. It is written in TypeScript and uses Vite for bundling.
InstrumentTableView encapsulates the streaming of live data from Diffusion to Excel and is written to prevent the streaming of live data faster than Excel can recalculate, which would rendering Excel unresponsive to user input.
In broad terms, it receives topic updates from Diffusion and delivers them to an EventBatcher to coalesce streams of updates into update batches. Batches are passed to the receiver function which categorizes updates into topic additions, updates, and removals, and then passes them to the TableViewer.
This class coalesces the stream of topic updates and delivers them as batches to an EventBatchReceiver function. A batch is delivered when its ready promise resolves and no current call to the receiver is ongoing. A batch becomes ready when the number of events exceeds a threshold or a timeout is reached. This logic ensures that Excel is not overwhelmed.
Scenario: The EventBatcher is configured with a batch size of 1,000 and a timeout of 100ms. A single update arrives for topic some/topic. After 100ms, a batch holding the topic update is delivered to the receiver.
Scenario: The EventBatcher is configured with a batch size of 1,000 and a timeout of 100ms. 1,001 updates arrive for topic some/topic. As soon as 1,000 updates are received, a batch holding the first 1,000 updates is delivered to the receiver, which completes immediately. After 100ms, the second batch is delivered, containing the remaining single update.
Scenario: More realistically, the EventBatcher is configured with a batch size of 1,000 and a timeout of 100ms. 1,000 updates arrive from a broad set of unique topics. The EventBatcher delivers the batch of 1,000 topic updates to the receiver, which ultimately waits for Excel while it recalculates a complex Excel table.
In the meantime, the EventBatcher receives further updates which it coalesces into a second batch. The second batch is delivered to the receiver function once the promise of the prior call is resolved.
The TableViewer class encapsulates an{" "} Excel.Table object and layers atop functionality so developers can interact with the table in terms of topic updates, instead of rows and columns. Row locations are indexed optimistically and rebuilt when the table is reordered.
While the TableViewer is updating its content and all dependent Excel formulas are recalculating, the EventBatcher continues to receive and coalesce updates from Diffusion.
Use git clone to get a local copy of this repo.
Ensure you have a recent version of npm, e.g. on macOS run brew update; brew upgrade.
npm install
Open ./src/taskpane/modules/AuthDefaults.ts and edit to reflect your own environment.
As Office.js mandates secure HTTP and WebSockets we suggest creating a free Diffusion Cloud instance, which is automatically configured for SSL/TLS. If you wish to use a locally hosted Diffusion instance ensure that your host is configured to trust its self signed certificate or the Add-in will be unable to connect.
24 hours of test data is available for demonstration and test purposes. It comprises 2 data sources, covering 54 FX currency pairs, creating 126 discrete topics with a topic path prefix market-data/
cd loader
npm install
npm run load
This will connect to your configured Diffusion server and re-play the data in real time. Open the Diffusion console to observe and subscribe to the data and observe the data is changing.
Office add-ins are web apps, requiring a web server.
To start the development server, run:
npm run dev
Accept any requests from your system relating to changed System Certificate Trust settings.
Open a new shell and load the add-in into your Excel instance: use the start script. e.g:
npm run start
Accept any requests from your system relating to changed System Certificate Trust settings.
Once Excel has begun the add-in will appear on the right hand side.
The task pane can be 'torn off' the main Excel window to float above as a child window. It can be closed, and resumed using the "Capital Markets" icon in the main Excel ribbon.
The user can stream live data into an Excel table using the Market Data tab. Enter a symbol or symbol fragment e.g. EUR to search for symbols or browse them in the pull down menu. Once selected click the Live Data button to stream live market data into an Excel table.
An incomplete user interface. If completed the user might use this to check and change the Diffusion server URL and related credentials.
This demonstration suggests at but does not yet implement the following.
-
Unsubscribe: users can subscribe to symbols, and it is intuitive that they be able to unsubscribe as well.
-
Automated subscription to FX time-series candlestick data for Dukascopy data, displayed in a table and as an Excel stock chart.
-
Editing the server URL and authentication details: hinted at by the Authentication tab, it would be intuitive to load and save these in user defaults.
purge.sh finds and removes Excel's web cache. Microsoft advise clearing the cache as part of the development and debugging process. Takes the -n option to remove even more.
defaults.sh useful only on macOS. Applies the system defaults to enable web view development tools from within the taskpane.