Excel Functions inside SQL Server Stored Procedures
1/20/2013 4:44 PM
SQL Server is a great product that many clients begin using when they want to automate a lot of work previously done in Excel and Access. People are amazed at the ease and speed at which SQL handles incredible volumes of data that would bring Excel to its knees. Unfortunately, there are a lot of Excel functions for which there are no equivalent functions in Transact-SQL, and which are very hard to write in t-SQL. The answer, of course, is write them in SQL CLR, a .Net assembly callable from SQL Server as an extended stored procedure. But who has time to write Accrual and BondInt functions, or string functions? The people at WestClinTech do.
WestClinTech has written function libraries that replicate a great deal of Excel functionality, and includes some very advanced functionality that Excel doesn't have. I've been using them now for a few years at a couple clients and been very happy. You can buy their products as specific libraries or as a suite of all their products. Their libraries include:
- Financial Options
All products are licensed per named server (NOT per instance) and Active/Passive Clusters and HAG's count as a single server. If you buy their ELO option, you can move the product from server to server during the length of the ELO.
Additionally there are three suites, The regular suite includes everything except Financial Options and Windowing, the PLUS suite adds the Financial Options (and at an incredible bargain). The Windowing product is stand alone and adds a lot of the windowing functionality found in SQL 2012 to earlier versions of SQL. There is also a Developer Edition of the suite.
While all suites are great deals for the money, the Developer edition is remarkably cheap for development and test servers. It can only run on the Developer Edition of SQL Server. There is also a SQL Express version that only runs on SQL Express, and the best part of this is that it's free. So individual developers with SQL Express on their local machine can develop for the named servers at no cost.
All the expected Excel functions (things you'd want to do in SQL but can't--obviously there are Excel functions like vlookup that have no need of being redone in SQL) are there. Examples include:
- Parse (splits delimited strings to a table)
- PadLeft,PadRight, etc.
- TRIM, Clean etc.
- And many more
- Count,Avg,Max,Min (OK, SQL has this but there are some advantages in some instances)
- Trig functions (cos,sin,tan, etc)
- Interp (linear interpolation of a line)
- Spline (spline interpolation
- Matrix Mathematics (multiplication, inverse, determinant, etc)
- And of course a ton more.
- Mean, Median, Mode, etc
- Geometric and Harmonic Means, Weighted Means
- Standard Deviation/Variance
- Shapiro-Francia tests
- And so much more.
- BondInt, Accrual, etc.
- BusinessDays, etc.
- Present Value
- Modified Diets Calculations
- Capital Asset Pricing Model tools (Alpha/Beta, Sharpe Ratio, Treynor Ratio)
- Loan and Amortization calcs etc.
- Future and Options Pricing (this is the cheapest place to buy these calcs, believe you me)
- Black Scholes
- Binomial calculators
- Bjerksund & Stensland
- Bessel Functions
- Error Functions
- Complex Numbers
- Base Conversions (base 2, base 10, hex, etc)
- Conversion Functions (distance, weight, speed, volume, etc)
- Rolling window calculations, MTD, Past Month, etc. moving averages etc.
The prices are great for what you get and they keep upgrading and adding new functions all the time. And with the standard maintenance and ELO, you just keep getting the new functions.
I love it, it has save me a ton of time, and thus my clients, a lot of money.
it's a great investment.
I should point out, that as a Consultant, I think my time is as valuable as my clients do, and so I'm more than happy to recommend and use developer components and tools to speed the process up. I know a lot of consultants don't like to do this because it reduces billable hours, but that's either just naive or greedy. In any event, I look at the project level perspective and what does it take to get the job done, and done on time and under budget and if we can buy a shrink wrapped product that saves everyone a lot of time and money, then I'm all in favor.