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)
select @blackscholes = (@underlying * @d1 - @strike * Exp(-@expTime * @RiskFree)) * (@d1 - @vol * SQRT(@expTime))
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.
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.
Also, convert your data from int to float before using and that you aren't passing the log10 function a 0 (zero) value.
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
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.
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)
select @d1 = (Log(1.0 * @underlying / @strike) + @RiskFree * @expTime) / (@vol * SQRT(@expTime)) + 0.5 * @vol * SQRT(@expTime)
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.
Thanks for the help