sometime call "time/frequency signal shift", "Time Delay" or "Horizontal Shifting"

Fig.1, results after moving the 2^{nd} 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:

- from David Meko, University of Arizona; a description of 3 different Lagged Correlation with examples in climate analysis,
- 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,
- a citation of Frank Anscombe; for their study around 1961 on the formal properties of residuals in linear regression, see Wikipedia web page,
- also from Wikipedia; a web page on Exploratory Data Analysis (EDA) a starting point for any future analysis,
- 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,
- 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,
- from WaveMetrics software web site; a page on Convolution, please see the zero-delay point definition.

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:

- 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,
- if it is coming from a replicate or from a windowing we can hypothesized a "linear correlation" between the 2 distributions,
- 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,
- the simple coefficient of determination (r
^{2}) 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 1^{st} distribution, is fixed, in column Y2 and following the 2^{nd} distribution moved one cell up and down (one lambda step of spectrometer, as example).

Fig.2, the second distribution is moved forward and backward before calculation |

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:

- a single spreadsheet with space for 1500 data values, having only the 2 columns Y1 and Y2 to be filled with your values. This GVisco_OLS-time-shift_v1.19.XLS
- a 2 spreadsheets version, one with your data and the other with calculation. You must download the file: the GVisco_OLS-time-shift_v1.19_autoload.XLS and the sources.XLS.

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.

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:

- there are 2 versions of software, all with space for 1500 data values. Following the first one,
- download the single sheet file
*GVisco_OLS-time-shift_v1.19.XLS*, with space for 1500 values, and open it with Excel, - download the file with examples,
*sources.XLS*and open it with Excel, - copy the cells B1-C1501 from
*sources.XLS*and paste in the other file, sheet "raw", pointing to cell A6, - do not touch any other cells in the "raw" sheet and in the "calc" sheet, pass to "chart" sheet
- in the "chart" sheet you can see the scatterplot of the two distributions on the left and the consequent residues on the right,
- looking the scatterplot and the values in the "calc" sheet you can find the best lag for obtaining correlation, after that you can copy the value of Fig.3 for your lag.
- alternatively you can download the autoload sheet file
*GVisco_OLS-time-shift_v1.19_autoload.XLS*, and save in your HD, - download the file
*sources.XLS*and save in the same place of HD (same directory, same folder, same ....), - start Excel and File/Open the file
*sources.XLS*, - in the same Excel running use File/Open the file
*GVisco_OLS-time-shift_v1.19_autoload.XLS*, - the software "autoload" 2 columns from the previous ones, see the cells G1 and G2 in the "chart" sheet,
- make some tentative to change the cell G2 from C to I and see the obtained scatterplots, it is interesting the couple H and I with a complete uncorrelation,
- do not touch any other cells in the "raw" sheet and in the "calc" sheet, fill only the G1 and G2 in "chart" sheet
- next you can fill the
*sources.XLS*with your data from B2 to IV2 and point to those columns in the working spreadsheet, - in the "chart" sheet you can see the scatterplot of the two distribution on the left and the consequent residues on the right,
- looking the scatterplot and the values in the "calc" sheet you can find the best lag for obtaining correlation, after you can copy the value of Fig.3 for your best lag.

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.

**Tips**

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 |