SQL Server Performance Forum – Threads Archive

Excel formulas in SQL

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!"

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

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.

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!"

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!"

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

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.

Thanks guys! I will look into it. sorry I have been so slow in posting blogs Satya<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> 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!"

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] let me know when you can, what you have suggested about Reporting Services also helpful. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

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!"

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.

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!"

found it now.. thanks<img src=’/community/emoticons/emotion-1.gif’ alt=’

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([email protected],cast([email protected] as float)/360e0))
from IncomeStream
)
while abs(@pv) >= 0.0001 begin
declare @t float
set @t = @irrPrev
set @irrPrev = @irr
set @irr = @irr + (@[email protected])*@pv/(@[email protected])
set @pvPrev = @pv
set @pv = (
select sum(amt/power([email protected],cast([email protected] 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!"

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

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!"

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

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=’

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!"

Thanks! Bookmarked. Always good to know. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

]]>