2 Replies Latest reply on Jul 17, 2012 4:06 PM by Aaron Rubinstein

# Calculating Stock Price Change From Average Price Over Date Range

I have attached a sample dataset with daily closing prices for two stocks. I am trying to create a chart that shows, for each day of 2012, the percentage difference in that day’s closing price from the average closing price for the stock over the last 20 trading days of 2011 (which would be the date range 12/2/11 through 12/31/11). I would like to plot this on a line chart with each company represented by a line (see the Excel chart below for an example of what I am going for).

I am not sure how to setup a calculated field to get the average stock price for each company over the 12/2/11 through 12/31/11 timeframe, and then calculate the percentage difference from the current price. Any help with this would be much appreciated.

Thanks,

Aaron

• ###### 1. Re: Calculating Stock Price Change From Average Price Over Date Range

Hello Aaron,

In the attached workbook I set up a simple version. There's a calculated field called 12/2/11 to 12/31/11 avg with the following formula:

With the Compute Using set to Date, this gets the first 21 rows of the data set, which are the 2011 data. The latter two arguments could be changed to use a different window of data in the partition, and be made quite dynamic using parameters if you wanted.

Then the % difference is calculated using those results and plotted.

Cheers,

Jonathan