January 7, 2013 at 3:07 pm
I'm working on a SQL 2000 database server (I know .. but it is what it is unfortunately), and I need some way to run financial calculations like NPV, IRR, etc. In SQL 2005 and 2008 I use a custom CLR that calculates these via the Excel Interop, but in the SQL 2000 world CLR is a no-go.
So firstly does anyone know of a simple way to run these financial calcs? I've tried a few canned functions that supposedly calculate them, but they never tie back to what Excel shows.
And if not is there anyway to write a COM object or possibly call a command line application which could be written to accept the parameters and return the correct values?
Thanks for any suggestions.
January 7, 2013 at 4:16 pm
Have you looked at the OLE Automation procedures? That may do what you want, but I haven't used them.
January 8, 2013 at 11:08 am
Lynn Pettis (1/7/2013)
Have you looked at the OLE Automation procedures? That may do what you want, but I haven't used them.
I looked through a few sites related to this, but I didn't see any practical way to use it for what i need. What I did do though was build a command line application in VB that when fed the values returns the financial result so I could use it via xp_cmdshell. Problem though unrelated to SQL is it uses interop, and I don't guess the SQL 2000 server has Excel installed :-/ I know running Excel on the server is less than ideal, but given the lack of financial functions in MS SQL there's not many other options.
Sam
January 9, 2013 at 8:48 am
Couldn't you work it the other way round and use a connection to SQL from the Excel spreadsheet to populate its columns? Difficult to say how viable this is in your particular environment, but I know Excel is certainly capable of getting data from a SQL server source like this.
January 9, 2013 at 8:59 am
You could do it an extended stored procedure (xp).
http://msdn.microsoft.com/en-us/library/aa214418(v=sql.80).aspx
January 9, 2013 at 10:25 am
paul.knibbs (1/9/2013)
Couldn't you work it the other way round and use a connection to SQL from the Excel spreadsheet to populate its columns? Difficult to say how viable this is in your particular environment, but I know Excel is certainly capable of getting data from a SQL server source like this.
Paul, that wouldn't be feasible in our environment since we recalculate the financial values each time data is saved.
lptech (1/9/2013)
You could do it an extended stored procedure (xp).http://msdn.microsoft.com/en-us/library/aa214418(v=sql.80).aspx
lptech, this actually does look promising, but Excel isn't installed on the SQL 2000 Server I'm using so I don't have access to the Interops to build the DLL I'd need to calculate the financial values. So I'm running into a roadblock not with SQL but rather with our server configuration. But this might be an option for another project I'm working on, so thanks 🙂
January 10, 2013 at 5:32 pm
NPV is a fairly straight-forward formula, assuming you have all your cash flow values in the same row, and you know the rate of return.
Unfortunately IRR has no straight forward way to calculate so it involves trial & error and most methods e.g. those used by financial calculators and Excel, involve converging towards and settling on a value for IRR where the NPV is insignificantly close to 0 for it to be effectively zero. I remember doing a Finance exam years ago that provided a lookup table on the back of the paper which enabled us to get an IRR that was 'close enough'.
I found this other friendly sparring match between Celko, dwain.c & Jeff Moden on just this topic. You wouldn't have the benefit of rCTE's so might be some effort converting it into an old SQL 2000 function or procedure: http://www.sqlservercentral.com/Forums/Topic1394442-392-1.aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply