Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 math functions in SQL? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, April 24, 2007 1:06 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, August 19, 2013 7:40 AM Points: 150, Visits: 221
 Is it possible to do heavy financial/math functions in SQL?I have an excel functionFunction 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 Functionpassing in 48,50,0.06,0.25,0.25 I get back a value of 1.847038So 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 floatselect@underlying = 48,@strike = 50,@RiskFree = 0.06,@expTime = 0.25,@vol = 0.25select @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
Post #360664
 Posted Tuesday, April 24, 2007 1:19 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 6:28 AM Points: 22,511, Visits: 30,237
 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
Post #360671
 Posted Tuesday, April 24, 2007 1:40 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 6:28 AM Points: 22,511, Visits: 30,237
 Also, convert your data from int to float before using and that you aren't passing the log10 function a 0 (zero) value.
Post #360681
 Posted Thursday, April 26, 2007 6:20 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, August 19, 2013 7:40 AM Points: 150, Visits: 221
 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
Post #361113
 Posted Thursday, April 26, 2007 9:05 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 6:28 AM Points: 22,511, Visits: 30,237
 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.
Post #361180
 Posted Thursday, April 26, 2007 2:23 PM
 SSCrazy Group: General Forum Members Last Login: Monday, April 14, 2014 3:07 PM Points: 2,382, Visits: 3,369
 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 thisselect @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"
Post #361358
 Posted Thursday, April 26, 2007 2:50 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 6:28 AM Points: 22,511, Visits: 30,237
 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.
Post #361371
 Posted Friday, April 27, 2007 6:46 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, August 19, 2013 7:40 AM Points: 150, Visits: 221
 Thanks for the help Chris
Post #361494

 Permissions