Compound return - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Compound return

Author

Topic

Peter01
Starting Member

16 Posts

Posted - 2014-11-02 : 03:57:20

Hi,
Looking to create a query, as simple as possible, that allows me to compound returns on a rolling daily basis. So far this this have I have:

DECLARE @stock_returns TABLE
(
stock_code VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
daily_return NUMERIC(10, 2) NOT NULL
);

INSERT INTO @stock_returns(stock_code, date1, daily_return)
VALUES ('stock1', '20140708', 0.51),
('stock1', '20140709', 0.003),
('stock1', '20140710', 0.005),
('stock1', '20140711', 0.006),
('stock1', '20140712', 0.002),
('stock1', '20140713', 0.007),
('stock1', '20140714', 0.0024),
('stock1', '20140715', 0.0024),
('stock1', '20140716', 0.0025),
('stock2', '20140710', 0.0016),
('stock2', '20140711', 0.0066),
('stock2', '20140712', 0.0023),
('stock2', '20140713', 0.0035),
('stock2', '20140714', 0.0057),
('stock2', '20140715', 0.0097),
('stock2', '20140716', 0.0071),
('stock2', '20140717', 0.0071);
SELECT
stock_code,
date1,
daily_return,
lag(daily_return, 1, 0) over (order by date1) as LAG,
(lag(daily_return, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return
FROM @stock_returns
where date1 >DATEADD(month, -21, GETDATE()) and stock_code='stock1'

But I´m not getting what I need. If you run the above select, the output should be:
stock_code date1 daily_return LAG Compound_return
stock1 2014-07-08 0.00510 0.00000 0.0051000000
stock1 2014-07-09 0.00300 0.00510 0.0081153000
stock1 2014-07-10 0.00500 0.00300 0.0080150000
stock1 2014-07-11 0.00600 0.00500 0.0110300000
stock1 2014-07-12 0.00200 0.00600 0.0080120000
stock1 2014-07-13 0.00700 0.00200 0.0090140000
stock1 2014-07-14 0.00240 0.00700 0.0094168000
stock1 2014-07-15 0.00240 0.00240 0.0048057600
stock1 2014-07-16 0.00250 0.00240 0.0049060000

The problem is with this column:
(lag(daily_return, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return

The (daily_return + 1) portion should be the accumulated compound return. So it should be something like (lag(ACCUMULATED_COMPOUND RETURN, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return

And the output should be:

Date1 Daily return LAG Compound Return
08/07/2014 0,0051 0,0000 0,0051
09/07/2014 0,0030 0,0051 0,0081
10/07/2014 0,0050 0,0030 0,0132
11/07/2014 0,0060 0,0050 0,0192
12/07/2014 0,0020 0,0060 0,0213
13/07/2014 0,0070 0,0020 0,0284
14/07/2014 0,0024 0,0070 0,0309
15/07/2014 0,0024 0,0024 0,0334
16/07/2014 0,0025 0,0024 0,0359

Any help would be much appreciated.
Many Thanks!!!!
Peter

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-02 : 07:30:46

I would be pleasantly surprised to find the SQL Windowing functions capable of computing cumulative products. It may be possible, but in spite of resolving to do so, I haven not explored the capabilities that were added in SQL 2012 thoroughly. In the meantime, what I have been doing is to use a recursive CTE.

Couple of things you want to be careful about:

1)Use the appropriate data type for the daily returns. Numeric(10,2) is not good. It just rounds the daily return to 2 decimal places, thus giving you completely wrong answers. Use float.

2) If you are able to find a windowing function, this may not be an issue, but in the following code, if you have missing dates, the results would be suspect (unless the return for the missing date is rolled into the return for the following day)

3) If you have more than 100 days, add an OPTION (MAXRECURSION 0) or something similar at the very end of the code.

4) Your test table data for stock1 for the first date is off by a factor of 100

with cte1 as
(
	select *, ROW_NUMBER() over (partition by stock_code order by date1) as RN
	from @stock_returns
) ,
cte2 as
(
	select *, (1.0E0 +daily_return) as AssetValue, 1.0E0 * daily_return as Compound_return
	from cte1 where RN = 1

	union all

	select
		c1.stock_code,
		c1.date1,
		c1.daily_return,
		c1.RN,
		c2.AssetValue * (1.0E0 + c1.daily_return) as AssetValue,
		c2.AssetValue * (1.0E0 + c1.daily_return) - 1E0 as Compound_Return
	from
		cte1 c1
		inner join cte2 c2 on 
			c1.RN = c2.RN+1
			and c1.stock_code = c2.stock_code
)
select * from cte2 order by stock_code, RN

Peter01
Starting Member

16 Posts

Posted - 2014-11-02 : 15:46:36

Hi James,
Thanks for your input.
While using CTe gives me the exact result, it is very slow when processing more than 3 months of daily returns.
All teh best,
Peter

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-11-02 : 17:22:05

Try this:

select stock_code
      ,date1
      ,daily_return
      ,lag(daily_return,1,0) over(order by date1) as lag
      ,((1.0+(sum(daily_return) over(order by date1)-daily_return))*(1.0+daily_return))-1.0 as compound_return
  from @stock_returns
 where date1>dateadd(month,-21,getdate())
   and stock_code='stock1'

ps.: this has not been tested

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-02 : 18:05:57

You could use the SUM windowing function against LOG(1E0 + daily_return) from unbounded preceding to current row (which I think is the default) and then take the exponent of the result. The problem with that is that if your daily_return turns out to be -1.0 or less, the query will blow up. Normally that should not happen, but if your positions get wiped out for whatever reason, and you are forced to pay expenses, you could end up with 100 percent loss and then some. But you can easily protect against the code blowing up by checking for that condition, although you may not be able to prevent your investment from blowing up.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-03 : 09:38:14

Did you find a an elegant solution to the problem?

Peter01
Starting Member

16 Posts

Posted - 2014-11-04 : 01:00:41

Thank you guys!

What bitsmed is suggesting works very well, but I still miss some decimals.

The result from the select at the end is:
0,0354822499999998
and then using excel the result is:
0,03594795595841790

Peter

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-04 : 08:40:55

It is not missing any decimals; it is calculating something altogether different from compound return. For each row T0, it is computing the simple return from start to T-1, and then taking that result as the basis for T0, in effect compounding for only from T-1 to T0. The results look close because the daily returns are small and the period is only a few days. Change your returns to a constant 0.5 for all days, and compare what you get against what Excel computes, or what I posted gives. The results will be NOWHERE close.

When you use windowing functions, the window for each row exists on its own. So if you have 10 rows, there are 10 virtual windows, all existing at the same time, independent of the other windows. So the results of one row is not available for use in another row.

jcarrallo
Starting Member

35 Posts

Posted - 2014-11-04 : 09:34:31

Thanks James. You are quite right.
Your alternative seems the only one then.
A return = -1 is highly unlikely.
Could you please elaborate a bit more?
Many thanks,
Peter

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-04 : 09:47:20

What I meant by using the log/exp is the following:

;with cte as
(
	select
		stock_code,
		date1,
		sum( log( 1e0 + daily_return ) ) over ( partition by stock_code order by date1 rows unbounded preceding) as LogAssetValue
	from
		@stock_returns
)
select stock_code, date1, exp(LogAssetValue) 
from cte 
order by stock_code, date1;

jcarrallo
Starting Member

35 Posts

Posted - 2014-11-04 : 10:03:35

Spot on!!!!!
thank you for sharing your expertise :-)
All the best,
Peter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources