Percentage Difference between 2 rows.

[carliny]

Hi,

I am trying to get the percentage return between 2 rows on a data series.

The goal is to produce the column "Return" in Access

Date HFRXGL Index Return 31/01/2011 1217.3 -2.31% 30/11/2010 1189.23 0.27% 29/10/2010 1192.48 -1.11% 30/09/2010 1179.23 -1.69% 31/08/2010 1159.35 -0.17% 30/07/2010 1157.41 -1.22% 30/06/2010 1143.33 3.69% 31/05/2010 1185.55 0.00% 30/04/2010 1185.55 -0.80% 31/03/2010 1176.09 -1.36% 26/02/2010 1160.06 -0.26% 29/01/2010 1157.01 0.02% 31/12/2009 1157.23 -0.55% 30/11/2009 1150.85 -1.63% 30/10/2009 1132.07 0.06% 30/09/2009 1132.74 -2.17% 31/08/2009 1108.15 -1.24% 31/07/2009 1094.46 -1.57% 30/06/2009 1077.3 -0.04% 29/05/2009 1076.9 -3.05% 30/04/2009 1044.02 0.00%
Thanks for your help

 

[jzwp22]

Assuming that you meant your data to look as the table that follows and assuming that the return is calculated as follows: ((PreviousIndex - CurrentIndex)/CurrentIndex) * 100. You need to use a nested query.

Date HFRXGL Index Return
31/01/2011 1217.3 -2.31%
30/11/2010 1189.23 0.27%
29/10/2010 1192.48 -1.11%
30/09/2010 1179.23 -1.69%
31/08/2010 1159.35 -0.17%
30/07/2010 1157.41 -1.22%
30/06/2010 1143.33 3.69%
31/05/2010 1185.55 0.00%
30/04/2010 1185.55 -0.80%
31/03/2010 1176.09 -1.36%
26/02/2010 1160.06 -0.26%
29/01/2010 1157.01 0.02%
31/12/2009 1157.23 -0.55%
30/11/2009 1150.85 -1.63%
30/10/2009 1132.07 0.06%
30/09/2009 1132.74 -2.17%
31/08/2009 1108.15 -1.24%
31/07/2009 1094.46 -1.57%
30/06/2009 1077.3 -0.04%
29/05/2009 1076.9 -3.05%
30/04/2009 1044.02 0.00%

The nested query would look something like this (you will have to substitute your own table and field names)

SELECT mytable.myDate, mytable.HFRXGLIndex as CurrentIndex, (SELECT TOP 1 Q1.HFRXGLIndex from mytable as Q1 where Q1.mydate< mytable.mydate order by Q1.mydate Desc) as PreviousIndex, ((PreviousIndex-CurrentIndex)/CurrentIndex * 100) as myReturn
FROM mytable
order by mytable.myDate desc

BTW, the word "date" is a reserved word in Access, so it would be better to use a different field name. Also it is not recommended to have spaces or special characters in your table or field names. If you have fields or table names that use reserved words, spaces or special characters, those names must be enclosed by square brackets ([]), but I would just recommend changing the to avoid the hassles of having to add square brackets to everything.