Cross Correlation analysis with two series of wavelets

**mrsak87** · 03-06-2015, 05:15 AM

Hey all,

This one will be a tough one to explain...

I essentially have 3 columns of data and 40000+ rows of it. Column A measures seconds from time 0 to say 10. Column B is a series of values creating on a graph wavelets from a source detected by receiver one and Column C is the same series of wavelets detected by receiver two. The two receivers are relatively closely spaced apart and therefore the profiles of these waves will be very similar although NOT identical. The spacing between the wavelets also changes due to the density of the soil/rock they travel throughThe aim is for excel to detect the peaks of each correlating wave and detect the lag time between the two series at each source. An example of a lag between two wavelets is shown

https://www.google.co.uk/search?q=wa...tm%3B365%3B152

This lag time calculation (column 4!?) allows me to do all the statistical analysis for my job. The traditional way is to manually pick the peaks using ancient software but this simply isn't feasible with the amount of data I have.

This is an example data set.... With col 1 being seconds, columns 2 and 3 are waves 1 and 2

3.495 0.003115 0.001985
3.495417 0.003294 0.001946
3.495833 0.003429 0.001883
3.49625 0.003548 0.001806
3.496667 0.003639 0.00173
3.497083 0.003709 0.001679
3.4975 0.00374 0.001686
3.497917 0.003703 0.001711
3.498333 0.003556 0.001727
3.49875 0.003412 0.00184
3.499167 0.003209 0.00191
3.499583 0.002988 0.00197
3.5 0.002736 0.002007
3.500417 0.002539 0.002056
3.500833 0.00238 0.002056
3.50125 0.002244 0.001977
3.501667 0.002132 0.001892
3.502083 0.002012 0.001765
3.5025 0.001865 0.001573
3.502917 0.001694 0.001352
3.503333 0.001466 0.00108
3.50375 0.001204 0.000792
3.504167 0.000926 0.000546
3.504583 0.000611 0.000363
3.505 0.000216 0.000193
3.505417 -0.00027 1.13E-05
3.505833 -0.00083 -0.0002
3.50625 -0.00146 -0.00044
3.506667 -0.0021 -0.0007
3.507083 -0.00272 -0.00093
3.5075 -0.0033 -0.00116
3.507917 -0.00379 -0.00136
3.508333 -0.00414 -0.00153
3.50875 -0.00447 -0.00182
3.509167 -0.0047 -0.00212
3.509584 -0.00487 -0.00245
3.51 -0.005 -0.00281
3.510417 -0.00507 -0.00321
3.510834 -0.00503 -0.00353
3.51125 -0.0049 -0.00374
3.511667 -0.00471 -0.00392
3.512084 -0.00446 -0.00408
3.5125 -0.00413 -0.00423
3.512917 -0.00377 -0.00436
3.513334 -0.00335 -0.00441
3.51375 -0.00291 -0.00437
3.514167 -0.00247 -0.00429
3.514584 -0.00203 -0.00419
3.515 -0.00159 -0.00403
3.515417 -0.00119 -0.00382
3.515834 -0.00081 -0.00354
3.51625 -0.00046 -0.00321
3.516667 -0.00015 -0.00284
3.517084 0.000113 -0.00245
3.5175 0.000305 -0.00205
3.517917 0.000438 -0.00168
3.518334 0.000519 -0.00134
3.51875 0.000652 -0.00089
3.519167 0.000708 -0.00047
3.519584 0.000729 -3.4E-05
3.52 0.000687 0.000389
3.520417 0.000608 0.000794
3.520834 0.000495 0.001104
3.52125 0.000324 0.001285
3.521667 9.63E-05 0.001408
3.522084 -0.00016 0.001494
3.5225 -0.00041 0.00155
3.522917 -0.00059 0.001613
3.523334 -0.00074 0.001605
3.52375 -0.0009 0.001497
3.524167 -0.00104 0.001325
3.524584 -0.00112 0.001144
3.525 -0.00117 0.000966
3.525417 -0.00117 0.000823
3.525834 -0.00108 0.000694
3.52625 -0.00091 0.000594
3.526667 -0.0007 0.000532
3.527084 -0.00048 0.000521
3.5275 -0.00025 0.000531
3.527917 7.3E-06 0.000578
3.528334 0.000349 0.000716
3.52875 0.00063 0.000816
3.529167 0.000928 0.001012
3.529584 0.0012 0.001246
3.53 0.001466 0.001472
3.530417 0.00167 0.001611
3.530834 0.001849 0.001731
3.53125 0.002003 0.001878
3.531667 0.002096 0.001979
3.532084 0.002182 0.002091
3.5325 0.002285 0.002206
3.532917 0.002406 0.002279
3.533334 0.002551 0.002348
3.53375 0.002706 0.002393
3.534167 0.002852 0.002379
3.534584 0.002982 0.002315
3.535 0.003103 0.002254
3.535417 0.003205 0.002189
3.535834 0.003291 0.002122
3.53625 0.003371 0.002048
3.536667 0.003442 0.00195
3.537084 0.003479 0.001838
3.5375 0.003489 0.001761
3.537917 0.003459 0.001702
3.538334 0.003374 0.001643
3.53875 0.003342 0.00171
3.539167 0.003263 0.001756
3.539584 0.003165 0.001798
3.54 0.003017 0.001825
3.540417 0.002874 0.001881
3.540834 0.002682 0.001888
3.54125 0.002449 0.001827
3.541667 0.00222 0.00178
3.542084 0.001999 0.001719
3.5425 0.001765 0.001628
3.542917 0.001566 0.001529
3.543334 0.00135 0.001367
3.54375 0.001121 0.001136
3.544167 0.00086 0.000885
3.544584 0.000536 0.000623
3.545 0.000138 0.000342
3.545417 -0.00029 6.72E-05
3.545834 -0.00077 -0.00019
3.54625 -0.00132 -0.00044
3.546667 -0.00191 -0.00068
3.547084 -0.00251 -0.00091
3.5475 -0.00311 -0.00115
3.547917 -0.00366 -0.00138
3.548334 -0.00412 -0.00158
3.54875 -0.00458 -0.00188
3.549167 -0.00492 -0.00215
3.549584 -0.00515 -0.00243
3.55 -0.00526 -0.00272
3.550417 -0.00531 -0.00307
3.550834 -0.00526 -0.00339
3.55125 -0.00508 -0.00366
3.551667 -0.00483 -0.00394
3.552084 -0.00451 -0.00418
3.5525 -0.00415 -0.00438
3.552917 -0.00378 -0.00454
3.553334 -0.00335 -0.00461
3.55375 -0.0029 -0.00459
3.554167 -0.00247 -0.00451
3.554584 -0.00208 -0.0044
3.555 -0.00173 -0.00422
3.555417 -0.00141 -0.004
3.555834 -0.00108 -0.00372
3.55625 -0.00077 -0.00338
3.556667 -0.00047 -0.00298
3.557084 -0.00019 -0.00257
3.5575 8.75E-05 -0.00215
3.557917 0.000319 -0.00176
3.558334 0.000493 -0.00143
3.55875 0.000688 -0.00102
3.559167 0.000791 -0.00064
3.559584 0.000846 -0.00024
3.56 0.000862 0.000145
3.560417 0.000833 0.000543
3.560834 0.000738 0.000879
3.56125 0.00056 0.001115
3.561667 0.000355 0.001324
3.562084 0.000147 0.001476
3.5625 -7.3E-05 0.001586
3.562917 -0.00027 0.001671
3.563334 -0.00046 0.001679
3.56375 -0.00065 0.0016
3.564167 -0.00079 0.001485
3.564584 -0.00087 0.00136
3.565 -0.00092 0.001206
3.565417 -0.00092 0.001057
3.565834 -0.00089 0.000903
3.56625 -0.00085 0.000769
3.566667 -0.00079 0.000657
3.567084 -0.00072 0.000595
3.5675 -0.00064 0.000576
3.567917 -0.00049 0.000638
3.568334 -0.00021 0.000808
3.56875 4.79E-05 0.000932
3.569167 0.000385 0.001122
3.569584 0.00077 0.001288
3.57 0.001197 0.001422
3.570417 0.001565 0.001463
3.570834 0.001884 0.001503
3.57125 0.002141 0.001604
3.571667 0.002322 0.001716
3.572084 0.002467 0.001869
3.5725 0.002583 0.002053
3.572917 0.00265 0.002205
3.573334 0.002726 0.002355
3.57375 0.002815 0.002467

My VB ability is marginally better than just recording but only slightly. Thanks for any help!

**mrsak87** · 03-06-2015, 05:16 AM

Sorry the data just needs delimiting with a space

**MrShorty** · 03-06-2015, 10:07 AM

I'm not sure if we have anyone on this forum who is that familiar with signal processing algorithms. If we have someone, hopefully they will chime in.

I find that I must first understand the math behind something, and this looks pretty intense mathematically. I would suggest you start with http://en.wikipedia.org/wiki/Cross-correlation to get a basic feel for the problem. From there, you might find the pages in the "external links" section interesting.

A search for "signal cross correlation algorithms" brought up several useful hits, though most of them were based on other programming languages (C or Matlab). It looks like Matlab might have a built in function for cross correlation -- in which case, it might be easier to do in Matlab than Excel if you have access to Matlab (or similar).

A quick search for "signal cross correlation Excel VBA" found a few potentially useful hits.

I would suggest that, if you want to build your own algorithm from scratch, this could get fairly involved. I would suggest you spend some time becoming familiar with some of the resources mentioned. If you don't need to build from scratch, Look through some of these internet searches for available code in more modern languages.