Fractional dataset delay (subsample resolution) in a spreadsheet

Figure 1

Working code is provided[1] for copying and use, no macros.

Fractional delay means one timeseries can be delayed or advanced in time relative to another by any amount including any fraction of one sample time. This is achieved by a short digital filter (5 taps) which is “designed” by the spreadsheet to user demand.

A demonstration of usage on real data is the next DaedalEarth article.  Link to demonstration.

Given sensible data size and reasonably fast spreadsheet software a modern GUI spreadsheet can attach a knob or slider to the control variable, so implementing interactive exploration: an output plot changes as the control is moved as does a correlation figure.[2]

There are caveats, the user needs to be reasonably knowledgeable on what they are doing, particularly the validity of results which I mention because so much “data” is actually of questionable validity as a time series (or sequence) as a consequence of Nyquist and Shannon violations, and of prior contamination from poor practices.

I want to put a very clear message here: – whilst most of this work is mine the filter design method is not and to my chagrin came from a web source I have lost and cannot find again so I cannot attribute whoever it was who gave me the idea it might be possible to produce a variable version which could be implemented in a spreadsheet. So far as I remember the source was an academic/engineering discussion group and several years ago.

Thank you whoever you are.

Basic technology used is the Farrow Interpolator which is a clever variation on polyphase filtering and comes in many forms and derivations. A web search will find a great deal of information.

• https://ccrma.stanford.edu/~jos/pasp/Farrow_Structure.html
• http://www.mathworks.co.uk/help/dsp/examples/fractional-delay-filters-using-farrow-structures.html
• http://www.dsprelated.com/showarticle/22.php

Figure 2

One of the two input datasets is fed into a convolver where the other input is the 5 coefficient digital filter. The standard spreadsheet function to do this is SUMPRODUCT(), 5 pairs of values, one half of it locked in position, the other the input data, same formulae is copied down to form the output new delayed dataset.

Optionally there is provision for shifting by whole samples, computes a moveable window on the input data, result is infinite adjustment of position. Key function here is OFFSET()[2].

An unavoidable downside is the loss of useful data at the end of one or the other dataset, can only use common to both data. This needs to be remembered when functionally comparing data using correlation: don’t fall off the end. Trying to do too much is tempting.

The Nyquist problem, which in this case means the dataset does not meet the Nyquist criterion, it attempts to represent fast (or high frequency) data which is beyond representation at the used sample rate. This can be trivially demonstrated by creating a hard square wave and then trying to delay that by a fraction, the sharp corners will be a mess. Solution? None, fix the wrong data or sample rate.

How accurate is it given excellent data? I’ve no actual figures, hopefully errors are -20dB or better. On poor data, you need to ask?

Do not try and use this with irregularly sampled data, eg. paleo data. That is a very complex problem. (could be done or resampling will work, done it, mileage varies)

And off you go, good luck. Don’t expect miracles, push too hard.

Tim.

---

1. core working spreadsheet for use as a template

https://daedalearth.wordpress.com/wp-content/uploads/2013/11/fractional-delay.xls

AFTER DOWNLOAD CHANGE THE FILE SUFFIX FROM XLS TO XLT

Reasons: WordPress will not allow XLT files
XLT is a template. The spreadsheet software will behave differently on opening the file, take a copy of the XLT contents and set to a blank filename for saving as XLS and you as the XLS originator. This makes accidental overwrite of the template hard to do.
If useful, move the xlt to your template directory.

2a. OpenOffice keeps visual controls under Toolbars / Form Controls and then design mode or usage on that menu.
You “attach” the control to a cell, it will alter that value.
Controls tend to be integer steps only, which is wrong for this usage here. Suggest you set the control range for 10x or more than you need and then edit the delay control cell to eg. =Q15/10 or whatever cell the control varies. It will move in increments of 0.1

Hint: if you add a control save as new template for your own use.

2b. Microsoft Excel looks to be almost identical.
Small PDF here http://www.math.ksu.edu/~bennett/m100f06/m100slider.pdf

2c. LibreOffice is probably the same as OpenOffice but recent versions (4.x) seem to have taken a large speed hit on Spreadsheet, about 8x slower and interaction becomes very poor.

2d. Gnumeric, KSpread and others. No idea. Tell me.