How to use Excel's XIRR function in StoredProcedure?

Hi All,

Is it possible to use excel's XIRR function In SQL 2000 StoredProcedure....Or Is there any function available like XIRR that I can use in SQL 2000.

Thank you,

Balwant Patel.

I think you should implement the XIRR function yourself

A sample code is available here at http://www.geocities.com/alexgutierrezotero/

hoping this could give you enough idea to work on.

- Jignesh

|||

Hi Jignesh,

Thank you for your reply...I apply the logic that on the site given by you...But it works too slow...and sometimes I m not getting teh correct result so ...I m finding other alternatives.

Regards,

Balwant Patel

|||

Hello,

is it posible in some menner that we export all data from sql server to excel there we calculate Xirr and return all that data back to sql server?

Thank You

With Rergards

Jatin

|||

hi
you can use this SP to write file:

CREATE PROCEDURE dbo.uspWriteToFile
@.FilePath as VARCHAR(255),
@.DataToWrite as TEXT
@.DataToWrite as VARCHAR(8000)
AS
SET NOCOUNT ON
DECLARE @.RetCode int , @.FileSystem int , @.FileHandle int

EXECUTE @.RetCode = sp_OACreate 'Scripting.FileSystemObject' , @.FileSystem OUTPUT
IF (@.@.ERROR|@.RetCode > 0 Or @.FileSystem < 0)
RAISERROR ('could not create FileSystemObject',16,1)

EXECUTE @.RetCode = sp_OAMethod @.FileSystem , 'OpenTextFile' , @.FileHandle OUTPUT ,

@.FilePath, 2, 1
IF (@.@.ERROR|@.RetCode > 0 Or @.FileHandle < 0)
RAISERROR ('Could not open File.',16,1)

EXECUTE @.RetCode = sp_OAMethod @.FileHandle , 'Write' , NULL , @.DataToWrite
IF (@.@.ERROR|@.RetCode > 0)
RAISERROR ('Could not write to file ',16,1)

EXECUTE @.RetCode = sp_OAMethod @.FileHandle , 'Close' , NULL
IF (@.@.ERROR|@.RetCode > 0)
RAISERROR ('Could not close file',16,1)

EXEC sp_OADestroy @.FileSystem
RETURN( @.FileHandle )
ErrorHandler:
EXEC sp_OADestroy @.FileSystem
RAISERROR ('could not create FileSystemObject',16,1)
RETURN(-1)
GO

Good luck