Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Anyway to write VB/C#/C++ scripts in SQL 2000? Need financial calculations (IRR, NPV, etc) Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 3:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:50 PM
Points: 206, Visits: 948
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
Posted Monday, January 7, 2013 4:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 20,732, Visits: 32,497
Have you looked at the OLE Automation procedures? That may do what you want, but I haven't used them.



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
Posted Tuesday, January 8, 2013 11:08 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:50 PM
Points: 206, Visits: 948
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
Posted Wednesday, January 9, 2013 8:48 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:50 AM
Points: 1,602, Visits: 5,652
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
Posted Wednesday, January 9, 2013 8:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:41 AM
Points: 227, Visits: 2,168
You could do it an extended stored procedure (xp).

http://msdn.microsoft.com/en-us/library/aa214418(v=sql.80).aspx
Post #1404849
Posted Wednesday, January 9, 2013 10:25 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:50 PM
Points: 206, Visits: 948
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 :)
Post #1404890
Posted Thursday, January 10, 2013 5:32 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 471, Visits: 875
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 »

Add to briefcase

Permissions Expand / Collapse