math functions in SQL?

Question

math functions in SQL?

Christopher Klein

SSCrazy

Points: 2648
More actions
April 24, 2007 at 1:06 pm

#92390

Is it possible to do heavy financial/math functions in SQL?
I have an excel function
Function BlackScholes(Underlying, Strike, RiskFree, expTime, Volatility)
    d1 = (Log(Underlying / Strike) + RiskFree * expTime) / (Volatility * Sqr(expTime)) + _
    0.5 * Volatility * Sqr(expTime)
    BlackScholes = Underlying * Application.NormSDist(d1) - Strike * Exp(-expTime * RiskFree) * _
    Application.NormSDist(d1 - Volatility * Sqr(expTime))
End Function
passing in 48,50,0.06,0.25,0.25 I get back a value of 1.847038
So I tried to recreate it in SQL (realizing that I have no idea how to recreate Application.NormSDist atm)
declare
@underlying int,
@strike int,
@RiskFree float,
@expTime float,
@vol float,
@d1 float,
@blackscholes float
select
@underlying = 48,
@strike = 50,
@RiskFree = 0.06,
@expTime = 0.25,
@vol = 0.25
select
@d1 = (Log(@underlying / @strike) + @RiskFree * @expTime) / (@vol * SQRT(@expTime)) + 0.5 * @vol * SQRT(@expTime)
select @blackscholes = (@underlying * @d1 - @strike * Exp(-@expTime * @RiskFree)) * (@d1 - @vol * SQRT(@expTime))
print
@blackscholes

Starting with the first select @d1 line. I get a domain error because Log(48/50) is invalid.
I always thought Log was a Log, how can Excel do it and not SQL? Anyone know of a workaround? I figure onec I get that squared away (no pun intended), I can figure how to do the standard/normal distribution.
Thanks,
Chris

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

LOG in Excel defaults to base 10, where as LOG in SQL is the Natural Logarithm (same as ln in Excel) of a number. In SQL you need to use LOG10.

hth

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Also, convert your data from int to float before using and that you aren't passing the log10 function a 0 (zero) value.

Christopher Klein SSCrazy Points: 2648 More actions · Answer 3

Actually, the more I think about this one, it might be a good canidate for a CLR assembly. Without diving back into statistics and more math than I really want to, instead of rewritting the wheel I could always create a class that just calls the office excel built in functions.

Thanks for the heads up on the Log10

Chris

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

That, of course, requires that you Office (Excel) installed on your server. I personally try to keep office of my SQL server systems as much as possible. Using CLR, there are probably math packages out there you could use and not have to worry about Office.

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 5

The problem is NOT with LOG function.

You are using INTEGER DIVISION!

@underlying and @strike are both integers.

select @d1 = (Log(@underlying / @strike) + @RiskFree * @expTime) / (@vol * SQRT(@expTime)) + 0.5 * @vol * SQRT(@expTime)

Try this

select @d1 = (Log(1.0 * @underlying / @strike) + @RiskFree * @expTime) / (@vol * SQRT(@expTime)) + 0.5 * @vol * SQRT(@expTime)

N 56°04'39.16"
E 12°55'05.25"

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Peter, That is why I told him to convert the values passed to float in my second post on the subject. However, the values he would get would not match what he got in Excel using LOG in SQL, as LOG in SQL is the natural log while LOG in Excel defaults to LOG base 10 which is LOG10 in SQL.

Christopher Klein SSCrazy Points: 2648 More actions · Answer 7

Christopher Klein

SSCrazy

Points: 2648

April 27, 2007 at 6:46 am

#702895

Thanks for the help

Chris