Time-Correlations between two data sets moving one step forward and backward
sometime call "time/frequency signal shift", "Time Delay" or "Horizontal Shifting"

history, inside the problem, the software, how to use, examples, donations
time shift n.1
Fig.1, results after moving the 2nd data set 2 steps forward, the correlation on left, the residues on right


  Copying from Wikipedia: Early work in time-frequency analysis can be seen in the Haar wavelets (1909) of Alfréd Haar, though these were not significantly applied to signal processing. More substantial work was undertaken by Dennis Gabor, such as Gabor atoms (1947), an early form of wavelets, and the Gabor transform, a modified short-time Fourier transform. The Wigner-Ville distribution (Ville 1948, in a signal processing context) was another foundational step.

  With this small piece of software we do not like to solve all problems of correlation between 2 distributions but only make a tentative to produce a Lag-Plot (or lag correlation) of your data. Please do not confuse with Correlogram and with Scaled Correlation.

  In Fig.1 you can see the scatterlplot between 2 distributions after 2 steps forward; on the right the residues of regression, very useful to check "the quality" of obtained values from correlation's formulae.

  Accounting all I said previously, I select Excel for Horizontal Shifting. It is not a new idea and you can be inspired reading the following:

  1. from David Meko, University of Arizona; a description of 3 different Lagged Correlation with examples in climate analysis,
  2. from Prof. John McDonald, University of Delaware; a very, very interesting web page on regression/correlation, and an Excel spreadsheet regression up to 1000 pairs of observations,
  3. a citation of Frank Anscombe; for their study around 1961 on the formal properties of residuals in linear regression, see Wikipedia web page,
  4. also from Wikipedia; a web page on Exploratory Data Analysis (EDA) a starting point for any future analysis,
  5. from Jeffrey Sukharev et al.; a paper on "Correlation Study of Time-Varying Multivariate Climate Data Sets", this is the link, to the .pdf version,
  6. from Mehmet Sayal, Intelligent Enterprise Technologies Laboratory, HP Laboratories, Palo Alto; a paper on "Detecting Time Correlations in Time-Series Data Streams", this is the link, to the .pdf version,
  7. from WaveMetrics software web site; a page on Convolution, please see the zero-delay point definition.

Inside the Problem

  The idea of this software comes from many of us work in the spectrophotometric UV-Vis field. Several times the replicate of the same solution produce a slight different spectra.

  Accepting a little difference in absorbance, we aspect the same value in nanometre, for the centre of absorbance band. Plotting 3 replicate we can find a difference of one step (0.5nm) in the centre values! And sometime not.

  So I have started the design of a simple spreadsheet to move the second distribution 4 steps forward and backward and every time calculates the Pearson correlation.

  It is better to spent some time on the procedure:

  1. first we have 2 different distributions with the same number of data points, the distribution comes from a replicate in an instrument, or from a day windowing of a microclimate monitoring,
  2. if it is coming from a replicate or from a windowing we can hypothesized a "linear correlation" between the 2 distributions,
  3. if there is a little error of the reproducibility of the instrument, or a little delay between 2 consecutive days, we need a few steps forward or backward for looking for the best correlation,
  4. the simple coefficient of determination (r2) is not sufficient, (but this is another story), it is better to calculate some parameters errors and the residues of correlation. Plot always is mandatory.

  Avoiding confusion we are doing a procedure as shown in Fig.2. In column Y1 the 1st distribution, is fixed, in column Y2 and following the 2nd distribution moved one cell up and down (one lambda step of spectrometer, as example).

time shift n.2
Fig.2, the second distribution is moved forward and backward before calculation

The Software

time shift n.3
Fig.3, the calculated parameters for regression

  This free software was distributed under Creative Commons Public License, CCPL 3.0, see my Copyright web page. Please read accurately the Liability Waiver in red.

  There are a 2 different versions, all work on MS-Excel 2000, up to MS-Excel 2003, and was tested on MS-Excel 2007 and 2010 version. All files are in native .XLS format, available:

  In the Fig.3, on the right, are shown the regression parameters calculated by software, the "errors" are useful to estimate the quality of correlation.


How To Use

  Dear students, this is not a course on Excel so you should already know how to use formulae and functions. I remember you to pass every lesson for Excel 2003 in this Course, and after that, pass all lessons of 2010 version (and do ALL projects).

  Very often the writer of a software is not the best writer of "instructions", he/she already knows how to use it! But I have made here a tentative to describe how to proceed:



  The best example, up to today, is the file sources.XLS with the columns B and C correlated with a lag, and with the other column with practically no correlation.

  We are working to fill the file sources.XLS with some other examples coming from UV-VIS or NIR spectra, wait please.


  If you know Excel you can expand or reduce the 1500 rows, and you can probably design a better "autoload" solution. But remember the Copyright on these files.


Donations if you have a Bitcoin wallet

  If you have a nice fat wallet you can send a few cents to this code 1ETv1mJTZTB7EiXwZkucF2BTA512Rkaw6 maybe with a message (be careful to the transaction cost, send 0.1 and pay 0.01 is not a good idea).

1ETv1mJTZTB7EiXwZkucF2BTA512Rkaw6    <== my code , Thank you.

Donations if you have not a Bitcoin wallet

  If you like this work and measures you can make a small donation of Bitcoin. You do not need a wallet to make a donation, indeed even those who have Bitcoin wallet is better to pass for a web site that "gift" currency since this does not have a transaction cost.

  To make a donation you must use some of your web-time. Go to www.GreenBitCo.in, enter my code 1ETv1mJTZTB7EiXwZkucF2BTA512Rkaw6 enter the Captcha code that you see, press "Claim Now" button. Wait 15 minutes on the publicity page, when the 15 minutes pass fill the new Captcha and press "Claim Now" again. To get some value are necessary 10 pages or more. Be careful, do not use roulette, dices, casino and other stealing web site.

1ETv1mJTZTB7EiXwZkucF2BTA512Rkaw6    <== my code , Thank you.

Universita' Degli Studi di Roma
La Sapienza
Dr. G. Visco
appointed professor for chemometrics & ....
Corso di Laurea in: Scienze Applicate ai Beni Culturali ed alla Diagnostica per la loro Conservazione

goto_back   help_page   page_copyright   author's page

Back Page   read help   C© Copyright   we did it