Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

math functions in SQL? Expand / Collapse
Author
Message
Posted Tuesday, April 24, 2007 1:06 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 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




Post #360664
Posted Tuesday, April 24, 2007 1:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:21 PM
Points: 23,031, Visits: 31,553

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

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #360671
Posted Tuesday, April 24, 2007 1:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:21 PM
Points: 23,031, Visits: 31,553

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




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #360681
Posted Thursday, April 26, 2007 6:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:21 PM
Points: 23,031, Visits: 31,553

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.

 




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #361180
Posted Thursday, April 26, 2007 2:23 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399

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"
Post #361358
Posted Thursday, April 26, 2007 2:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:21 PM
Points: 23,031, Visits: 31,553

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.

 




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #361371
Posted Friday, April 27, 2007 6:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse