determine lead/lag times between data series

[henryh2]

Hello everyone,

I have two data series (two columns, ~600 rows long each; one is the monthly closing price of a stock index and the other is a macroeconomic indicator) and I'm looking to find out some information between them (the lead/lag times).

I need to figure out how to:
- determine where each series peaks and troughs (there are many peaks and troughs)
- determine the lead/lag time between these peaks and troughs. i.e. when series one peaks series two peaks X months later (each row represents a month)

I am absolutely stumped as to how to do this!


Thanks!
Henry

 

[PaddyD]

hi - welcome to the board.

a search for 'peak trough' turned up a few things, including:

http://www.mrexcel.com/forum/showthread.php?t=194880&highlight=peak+trough

http://www.mrexcel.com/forum/showthread.php?t=58655&highlight=peak+trough

 

[henryh2]

Thanks Paddy, that works perfectly for determining peaks and troughs.

Also would you know how to determine lead/lag times between peaks/troughs? Say series one (Column D), on row 5, determined a peak and series 2 (Column G), on row 7, peaked. What formula would I use to display that the lead time was 2?

Thanks!

 

[dafan]

How would you know which peak matched with which other peak? Say you have in Column D peaks on row 5 and 10. Column E has a peak on row 7. How would you know the lead time is 2 or 3?

 

[henryh2]

First I'm going to determine the peaks/troughs and then I'll selectively choose which of these are relevant. I should be left with ~4-5 peaks (per series) for my data range which spans 1959-current. The theory is that series 1 is a leading indicator of series 2 so I'm going to base my study on that assumption (or at least see how it turns out).

So all I need to do at this point is figure out how to determine lead/lag times in between peaks and troughs of different series.

 

[dafan]

Something like this? Bit too much info but all formulas are alike and can be dragged down from D2:

Excel Workbook
	A	B	C	D
1	Time	P/T Series 1	P/T Series 2	Lead Series 1
2	1
3	2	Peak		1
4	3		Peak
5	4
6	5	Trough		2
7	6
8	7	Peak	Trough	3
9	8
10	9
11	10		Peak
Sheet1