Geek Answers Handbook

Computing an arithmetic return from a value table

I created a table of index price levels (for example, S & P 500), which I would like to calculate daily income. The structure of the table is as follows:

Date Value 2009-07-02 880.167341 2009-07-03 882.235134 2009-07-06 881.338052 2009-07-07 863.731494 2009-07-08 862.458985

I would like to calculate the daily arithmetic income (i.e. interest income) of the index, defined as:

 Daily Return = P(2)/P(1) - 1

Where P represents the index value in this case. Given the above input table, the desired result will look like this:

  Date Return 2009-07-03 0.002349318 2009-07-06 -0.001016829 2009-07-07 -0.019977077 2009-07-08 -0.001473269

It occurs to me that self-connection will work, but I'm not sure what the best way to increase the date on the second table is to take account of holidays.

Any thoughts on how best to do this?

+4
source share
3 answers

Simple CROSS APPLY

 SELECT Tlater.Date, (Tlater.Value / TPrev2.Value) - 1 FROM MyTable Tlater CROSS APPLY ( SELECT TOP 1 TPrev.Value FROM MyTable TPrev WHERE TPrev.Date < Tlater.Date ORDER BY TPrev.Date ) TPrev2

Note: this becomes trivial in Denali (SQL Server 2012) with LAG (untested, CTE may be required)

 SELECT OrderDate, (Value / (LAG(Value) OVER (ORDER BY Date))) -1 FROM MyTable

or

 ;WITH cPairs AS ( SELECT Date, Value AS Curr, LAG(Value) OVER (ORDER BY Date) AS Prev FROM MyTable ) SELECT Date, (Curr / Prev) -1 FROM cPairs
+2
source 
 WITH cteRank AS ( SELECT [Date], Value, ROW_NUMBER() OVER(ORDER BY [Date]) AS RowNum FROM YourTable ) SELECT c1.[Date], c1.Value/c2.Value - 1 as [Return] from cteRank c1 inner join cteRank c2 on c1.RowNum - 1 = c2.RowNum where c1.RowNum > 1
+3
source

If you use 2005+, you can use the ROW_NUMBER function in conjunction with CTE:

 ;with RowNums as ( select *, row_number() over (order by date) as RN from table ) select *, r1.Value / r.Value - 1 as Return from RowNums r inner join RowNums r1 on r.RN = r1.RN - 1
+2
source

More articles:


All Articles