Tick data in Excel

It is possible to have tick data in Excel using QuoteLink however I have noticed that some customers when they try to process tick start by using cell events which don’t work well because of the way Microsoft RTD technology is designed. To understand why, let me first explain how QuoteLink works for the case of Level I quotes.

Basically we have 2 layers of software, the first layer is the QuoteLink framework which connects to the feeds and gets the data from the data provider over the internet . The QuoteLink framework itself is completely independent from Excel. On top of it you have a RTD server (named QLRTD) which makes the data available in Excel through the RTD worksheet function.

Now let's examine how RTD works. The Microsoft RTD technology is based on a notification mechanism, basically when an update comes from the server side this mechanism simply notifies Excel telling that new data is available. Excel will then take its time and will pick the data whenever it thinks it’s the best time. This fits Excel's main goal which is to keep the sheet consistent according to the formulas and its dependencies. If there is another notification while the first one has not yet been picked Excel has no way to know it and Excel is not interested in distinguishing the 2 or more updates. This is extremely efficient in maintaining the worksheet and presenting the data in its different forms to human users which. It is easir to understand though that while using the events will work in many cases it is not in general a good idea as not all ticks will generate an update. In my opinion it is simply bad design to process ticks this way if every tick counts.

There are fortunately better ways to make tick by tick data available in Excel.   We can divide a solution in 3 parts

1. Calculate any extra data at the precise rhythm of the server updates
2. Calculating and displaying the data in Excel using worksheet formulas + charts
3. Human brain reads the data in the most appropriate form and makes decisions

Most people even those without any experience are enough good at 2) and that is the main reason of the popularity of spreadsheets. For 1) there are two possibilities. If an user knows a bit of programming it is possible to use VBA events inside QuoteLink which will render every tick. This uses the COM API which connects directly to the QuoteLink framework without going through the worksheet functions. There is a another solution in QuoteLink requiring no programming at all. This solution is based in first collecting the ticks at the fastest speed on the underlying QuoteLink framework and then presenting them to Excel all at the same time as shown in the picture.

Each cell addresses a single tick that is already stored in the computer. By changing formula parameters you can address all ticks even those in past( as backfill is also implemented). In detail Excel will be notified whenever a new tick is available but as the tick has a different cell Excel can take its time as it wants never losing any tick. To illustrate this please see the Ticks Example on the Professional edition.

(Available in version 0.77.34 or later)