Plotting data with different frequencies

[mattesich]

Does anyone know how to plot a single line on a chart using data sets with different frequencies? Specifically, I'm trying to create a historical price-to-earnings (PE) chart. The earnings data points are quarterly but my price data is daily. My current solution is to assign a calendar quarter (eg. "2Q11") to each price date, then use a vlookup formula to calculate the PE. I was wondering if there was a simpler solution. Thanks!

 

[Steve=True]

Hi Mattesich!

I am not sure I completely understand how you are plotting the data.

Perhaps this solution I created might work for you:

http://www.exceldashboardtemplates.com/?p=460

If not, perhaps you could describe how the final chart looks in more detail so that we can figure a solution for you.


Steve=True
http://www.exceldashboardtemplates.com/?p=61

 

[mattesich]

Thanks, Steve. I did find that post in my original search for a solution in these message boards. However, that is a bit different than what I'm trying to do. Your solution is a single point in time. What I am trying to plot is a time series. So if I wanted to plot the PE over the past year, 3 years, 5 years, etc I have two times series - the daily prices of a stock and the quarterly (or trailing 4 quarters) EPS. Any more elegant solutions than the one I described in my original post would be much appreciated.