SQL Server Performance

Excel formulas in SQL

Discussion in 'SQL Server 2005 General Developer Questions' started by MichaelB, May 4, 2007.

  1. MichaelB Member

    Ok. I got a question guys. thanks in advance for looking!
    I have a request to be able to replicate formulas with SQL 2005. I know this was doable in sql 2000 (seehttp://msdn2.microsoft.com/en-us/library/aa203746(office.11).aspx) but will that work with 2005? and if not, is there any other way around? exposed in CLR maybe? I see that it is available out of the box with Analysis but we dont want to have to build cubes just for this. any suggestions???

    Mike


    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  2. FrankKalis Moderator

    Without further details I would go for CLR integration. But then again, as you have mentioned, the tool of choice (aka Analysis Services) is readily available.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. satya Moderator

    With the latest excel pack for SQL 2005 it is possible, as referred by Frank without futher details it will be a shot in dark.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. MichaelB Member

    I am trying to do xirr excel function within sql server.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  5. MichaelB Member

    and really dont want to build cubes at this point for this data

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  6. FrankKalis Moderator

    You could make a UDF out of it. XIRR calculation is an approximative calculation. Might work well for a small amount of data, but a pain for larger tables. I think Steve Kass posted a version to the MS newsgroups. You could find it on Google.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  7. satya Moderator

    I'm not sure whether the Office 2003 comes with .NET programmability and in this case you might try downloading the office programmability addin.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  8. MichaelB Member

    Thanks guys! I will look into it.<br />sorry I have been so slow in posting blogs Satya<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Michael <br />MCDBA<br /><br />"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  9. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] let me know when you can, what you have suggested about Reporting Services also helpful.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>
  10. MichaelB Member

    one other thing.. I dont have user defined function template in VS 2005 that IU can add. Very strange. ever hear of this?

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  11. satya Moderator

    I think most of it can be handled using CLR integration and ASSEMBLY creation, I will check back at my end on VS side of it.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  12. MichaelB Member

    thanks.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  13. MichaelB Member

    found it now.. thanks<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Michael <br />MCDBA<br /><br />"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  14. MichaelB Member

    I found it YEA!!!

    It wasnt called XIRR but IRR but it works the same. It is from Steve Kass.
    Works perfectly!

    Below is the link and I will paste the code in case the link ever goes cold!

    http://groups.google.co.uk/group/mi...omestream kass newton&rnum=2#23213f66e69da8d7

    create table IncomeStream (
    amt float,
    d datetime
    )
    insert into IncomeStream values (-1000,'19980101')
    insert into IncomeStream values (200,'19990101')
    insert into IncomeStream values (200,'20000101')
    insert into IncomeStream values (200,'20010101')
    insert into IncomeStream values (200,'20020101')
    insert into IncomeStream values (200,'20030101')
    insert into IncomeStream values (200,'20040101')

    go

    create function irr(
    @d datetime
    ) returns decimal(18,10) as begin
    declare @irrPrev float set @irrPrev = 0
    declare @irr float set @irr = 0.1
    declare @pvPrev float
    declare @pv float
    set @pvPrev = (
    select sum(amt)
    from IncomeStream
    )
    set @pv = (
    select sum(amt/power(1e0+@irr,cast(d-@d as float)/360e0))
    from IncomeStream
    )
    while abs(@pv) >= 0.0001 begin
    declare @t float
    set @t = @irrPrev
    set @irrPrev = @irr
    set @irr = @irr + (@t-@irr)*@pv/(@pv-@pvPrev)
    set @pvPrev = @pv
    set @pv = (
    select sum(amt/power(1e0+@irr,cast(d-@d as float)/365e0))
    from IncomeStream
    )
    end
    return @irr
    end
    go
    select dbo.irr('19980101')


    go


    drop function irr
    drop table IncomeStream
    go








    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  15. FrankKalis Moderator

    Oh, you should have mentioned the IRR function before. I could have posted a similar solution. Actually I used my UDF on our portfolio of about ~300 securities, mostly fixed-income securities. Trouble is that for each security you first need to determine its cash-flows, push them into the temp table and then loop over this to determine the appropriate discount rate. And that for each single security. Next thing is that there is no single overall interest rate convention. The US securities still mostly use the 30/360 convention, while we here in Europe use act/act. Next point is that securities with a term to maturity of <= 1 year at the time of calculation are considered money-market securities with yet another convention act/365. Now, these things can be quite simple to implement. However, until now you have only dealt with plain-vanilla fixed-income securities. Many bonds however do have embedded derivatives in them. That's where it gets REALLY interesting. Options et al of any kind make it even harder to be exact.

    I don't know what for you will use this, but if you need to be quite exact in your calculation, these aspects can be difficult to implement and will slow down things considerably. What I ended up with on my side is, that I push the basic data into Excel and from there use a specialized Add-In library. Referencing that library made it also easier to discuss with our auditors than explaining each time why I did the things I did in my own code.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  16. MichaelB Member

    Frank,

    I appreciate your insight into this topic. I didnt mention IRR since that is not what it is. I was trying to copy excel's xirr function.
    I wonder if your pump to excel would be slow. are you using SSIS to do this? what kind of performance do you get?

    The use will not be something that will be audited but just informational. It seems fast and matches output from excel's xirr.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  17. FrankKalis Moderator

    The transfer from SQL Server to Excel is actually initiated from Excel. So, Excel pulls the data via ADO across the wire via the .CopyFromRecordset method, so I can control where the data is placed to ensure that my formulas still work after the load. With "only" ~300 securities to analyze that happens almost instantly.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  18. MichaelB Member

    that sounds good, but we would like to have several code bits pulling this data from different applications. oh well<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Michael <br />MCDBA<br /><br />"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  19. MichaelB Member

    I found the one that works for me... Just thought I would share!

    http://www.blackteaconsulting.com/journal/?p=79

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  20. FrankKalis Moderator

    Thanks! Bookmarked. Always good to know. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page