Calculating Financial Performance question

Question

Post reply

Calculating Financial Performance question

BillyWilly

SSCrazy

Points: 2304
More actions
April 2, 2003 at 1:01 pm

#80582

Hey....
I'm easily able to use the SUM aggregate function to "SUM" data from row 1 to row N..
But what if I want the "Product" of numbers from row 1 to Row N ???
I need to do basic Financial performance calculations which requires such multiplication and cannot offhand think of a simple "Set Based" way to do it. I'm staring a Cursor straight in the face.
Any suggestions ????

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65138 More actions · Answer 1

If by product you mean row1.numb * row2.numb etc. Then

declare @r numeric(28,0)

set @r=1

select top 100 @r=@r*numb from tabela

select @r

Can't seem to find a way not to use TOP.

Far away is close at hand in the images of elsewhere.
Anon.

BillyWilly SSCrazy Points: 2304 More actions · Answer 2

Most excellent David - thanks.

My performance data ends up looking like this:

ENTITY DATE PERFORMANCE

S&P 500 5/1/2002 .05

S&P 500 5/2/2002 .04

S&P 500 5/3/2002 .09

S&P 500 5/4/2002 .1

S&P 500 5/5/2002 -.05

This data is essentially a weeks worth of performance. To calculate the "Weeks Performance" - I add one to each performance number, then multiply times 1 + the next performance number, and so on....

Then - after all multiplications are done - subtract 1 from the result. So...

DECLARE @Result decimal(38, 7)

SET @Result = 1

SELECT

@Result = @Result * (PERFORMANCE+ 1)

FROM

PerformanceTable

WHERE

DATE between '5/1/2002' and '5/1/2002'

AND ENTITY = 'S&P 500'

SELECT @Result - 1

And I come up with this: .2438430

which is correct.

So - if I wrap this in a function which receives the Entity to check, and Begin and End dates - I'm all set for weekly, monthly, quarterly etc.. performance.

Thanks again and I've only done brief testing - but this should do it. !!!!

BillyWilly SSCrazy Points: 2304 More actions · Answer 3

Actually - spoke with a Finance guy and he has a slightly different way which is additive and uses LOG and EXP - but the result is exactly the same. Still have to add one to each number and then subtract 1 from the end result - but I can use the super fast SUM intrinsic function rather than multiplication. - here it is:

DECLARE @Result decimal(38, 7)

SELECT

@Result = SUM(LOG((Perf + 1)))

FROM

Performance

WHERE

id between 1 and 5

/* id is the SerialID of a date value */

SET @Result = EXP(@Result) - 1

SELECT @Result

This works the same way put is probably more effecient and less prone to error - CERTAINLY more so than using a cursor.

- B