Anyway to write VB/C#/C++ scripts in SQL 2000? Need financial calculations (IRR, NPV, etc)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Anyway to write VB/C#/C++ scripts in SQL...

Anyway to write VB/C#/C++ scripts in SQL 2000? Need financial calculations (IRR, NPV, etc)

Rate Topic

Display Mode

Topic Options

Author

Message

samalex

Posted Monday, January 07, 2013 3:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 135, Visits: 554

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.

Post #1403887

Lynn Pettis

Posted Monday, January 07, 2013 4:16 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 21,627, Visits: 27,477

Have you looked at the OLE Automation procedures? That may do what you want, but I haven't used them.

Cool

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 #1403904

samalex

Posted Tuesday, January 08, 2013 11:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 135, Visits: 554

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

Post #1404391

paul.knibbs

Posted Wednesday, January 09, 2013 8:48 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 1,259, Visits: 4,260

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.

Post #1404843

lptech

Posted Wednesday, January 09, 2013 8:59 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 1:31 PM
Points: 97, Visits: 995

You could do it an extended stored procedure (xp).

http://msdn.microsoft.com/en-us/library/aa214418(v=sql.80).aspx

Post #1404849

samalex

Posted Wednesday, January 09, 2013 10:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 135, Visits: 554

paul.knibbs (1/9/2013)

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 :)

Post #1404890

davoscollective

Posted Thursday, January 10, 2013 5:32 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:01 PM
Points: 408, Visits: 688

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

Post #1405708

« Prev Topic | Next Topic »

Permissions