SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
samalex
samalex
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1173 Visits: 1068
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94743 Visits: 38956
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)
samalex
samalex
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1173 Visits: 1068
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
paul.knibbs
paul.knibbs
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4210 Visits: 6240
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.
lptech
lptech
SSC Eights!
SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)

Group: General Forum Members
Points: 919 Visits: 3440
You could do it an extended stored procedure (xp).

http://msdn.microsoft.com/en-us/library/aa214418(v=sql.80).aspx
samalex
samalex
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1173 Visits: 1068
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 Smile
davoscollective
davoscollective
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1657 Visits: 1008
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search