• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding lead/lag with highest correlation between two time series

J

jimmytt

New Member
Hello,

I am wondering if there is a way to determine the lead or lag between two time series with the highest correlation? For instance, if we have S&P 500 Earnings Per Share and the Year-over-year percent change in the US budget deficit, can I create a formula or use a tool to determine the lead of the deficit that has the best correlation with earnings? By trial and error it appears that Deficit today is best correlated with earnings 2 years later. But can I automate that?

Thank you in advance!

Jeff
 

Attachments

  • Def v EPS.xlsx
    20.9 KB · Views: 4
Yes, it looks like the best correlation is 2 years ago. Your spreadsheet attached with
  • a formula to return the correlation of different 'sliding' datasets in column F
  • whose maximum is in cell F28
  • which refers to a dataset starting in cell C28
  • which has a date ending in cell B28 of 30 Apr 2022
That specific pair of datasets is plotted in the chart at cell H3.

Correlation is pretty poor at .078 where 1 would be perfect correlation.
 

Attachments

  • Chandoo56810Def v EPS.xlsx
    38.7 KB · Views: 6
Last edited:
You must log in or register to reply here.
Back
Top