Software guru, Inc

Real World Solutions

The Guru's Blog

Excel Functions inside SQL Server Stored Procedures

Jan 20

Written by:
1/20/2013 4:44 PM  RssIcon

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:

  • Strings
  • Math
  • Statistics
  • Financial
  • Engineering
  • Financial Options
  • Windowing

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:

  • Strings:
    • Concat
    • Parse (splits delimited strings to a table)
    • PadLeft,PadRight, etc.
    • TRIM, Clean etc.
    • And many more
  • Math:
    • Count,Avg,Max,Min (OK, SQL has this but there are some advantages in some instances)
    • Trig functions (cos,sin,tan, etc)
    • SumSQ
    • Quotient
    • Interp (linear interpolation of a line)
    • Spline (spline interpolation
    • Matrix Mathematics (multiplication, inverse, determinant, etc)
    • And of course a ton more.
  • Statistical:
    • Mean, Median, Mode, etc
    • Geometric and Harmonic Means, Weighted Means
    • Standard Deviation/Variance
    • Covariance
    • Kurtosis
    • Chi-Squared
    • Shapiro-Francia tests
    • T-Tests
    • And so much more.
  • Financial:
    • BondInt, Accrual, etc.
    • BusinessDays, etc.
    • Present Value
    • Modified Diets Calculations
    • Capital Asset Pricing Model tools (Alpha/Beta, Sharpe Ratio, Treynor Ratio)
    • Depreciation
    • 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
    • Greeks
    • Bjerksund & Stensland
  • Engineering
    • Bessel Functions
    • Error Functions
    • Complex Numbers
    • Base Conversions (base 2, base 10, hex, etc)
    • Conversion Functions (distance, weight, speed, volume, etc)
  • Windowing
    • 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.

3 comment(s) so far...


Gravatar

Re: Excel Functions inside SQL Server Stored Procedures

Yeezy 350
Balenciaga
Moncler Jackets
Moncler Sale
NMD
Nike Outlet Canada
Pandora Bracelets
Pandora Jewelry
Jordan 1 Retro
Pandora Charms Sale Clearance
Air Jordan 1 Retro
Pandora Jewelry
Pandora Charms
Moncler Jacket For Women
Pandora Jewelry
Pandora Jewelry
Goyard Handbags Outlet
Jordan Ones
Women Nike Shoes
Nike Running Shoes Sale
Nike Shoes For Women
Balenciaga Shoes
Moncler Outlet
Balenciaga Sneakers
Balenciaga
Moncler Outlet
Adidas Shoes Men
Nike Canada
Pandora Jewelry
Nike Air Jordans 1
Adidas New Shoes
Nike Sneakers For Men
Asics Outlet Online
Adidas Canada
Ferragamo
Nike Shoes Men
Nike Shoes
Asics Sneakers For Women
Nike Canada
Jordan 1 Retro
Goyard Bags
Adidas Running Shoes Women
Mens Basketball Shoes
Adidas Shoes
Balenciaga Sneakers
Salvatore Ferragamo
Ferragamo
Adidas Website
Moncler Coat
Pandora Jewelry
Asics Gel-Kayano
Nike
Pandora Jewelry Official Site
Jordan 1 High
Pandora Store
Pandora Charms
Asics Outlet
Moncler Coats
Pandora Jewelry Official Site
Nike Canada
Pandora Jewelry Official Site
Wholesale Jordan
Pandora Jewelry Official Site
Wholesale Jordan Shoes
Nike Outlet
Air Jordan 1 Dior
Cheap Moncler Coats
Jordan 1 Low
Jordan 1 Lows
Pandora Charms
Pandora Charms
Nike
Pandora Official Site
Pandora Jewelry Black Friday
Nike Sneakers For Women
Pandora
Moncler Jackets Outlet
Asics
Pandora
Pandora Outlet
Pandora Bracelet Charms
Nike Air Jordan 1 Retro
Nike Outlet Store
Nike Store
Pandora Charms
Pandora Jewellery
Pandora
Sneakers Adidas
Pandora Rings Official Site
Adidas Shoes
Pandora Outlet
Pandora Rings
Air Jordan 1
Asics Shoes For Women
Pandora Store
Nike Shoes
Yeezy
Nike Sneakers
Nike Outlet
Nike UK
Jordan Wholesale Distributors
Nike Store
Adidas Shoes Canada
Pandora Charms
Pandora Outlet
New Nike Shoes
Pandora Outlet Online
Wholesale Cheap Jordans
Pandora Outlet
Nike Canada Online
Nike Men Shoes
Adidas Yeezy
Pandora Jewelry
Pandora Charms
Adidas Trainers
Adidas Clearance
Adidas Outlet
Pandora Bracelet
Pandora Charms
Ferragamo Shoes Men
Pandora Outlet
Cheap Mens Adidas Shoes
Pandora Jewelry
Nike Men's Shoes
Pandoras
Pandora Charms
Balenciaga Triple S
Pandora
Pandora Jewelry Outlet
Yeezy Shoes
Balenciaga Shoes Men
Pandora Jewelry
Air Max 270 Men
Asics Running Shoes Women
Jordan 1 Low
Pandora Jewelry
Puma Store
Pandora Jewelry Official Site
Pandora Charms Outlet
Pandora Jewelry
Nike Factory
Ferragamo Belt Men
New Nike Shoes
Jordan 1s
Adidas Women's Shoes
Pandora Jewelry

By Parker on   7/8/2022 8:29 AM
Gravatar

Re: Excel Functions inside SQL Server Stored Procedures

Pandora Official Site
Pandora Jewelry
Adidas Running Shoes
Adidas Women's Shoes
Jordan Wholesale Distributors
Pandora Charms Jewelry
Nike Air Jordan 1
Nike Running Shoes
Goyard Handbags Outlet
Nike Outlet Store
Pandora Charm
Nike Shoes Women
Ferragamo
Pandora Charms Outlet
Pandora Jewelry
Pandora Jewelry
Asics Outlet
Nike Shoes
Air Jordan 1 Retro High Og
Pandora
Moncler Sale
Moncler Jackets
Salvatore Ferragamo
Pandora
Nike Outlet
Adidas Canada
Pandora Jewelry Official Site
Nike Outlet Store
Cheap Moncler
Jordan 1 Low
Outlet Moncler
Jordan 1 High
Pandora Jewellery
Nike Store
Nike Shoes For Women
Adidas Outlet Store
Wholesale Jordans China
Adidas Sneakers
Nike Shoes Canada
Yeezy
Pandora Charms
Nike Shoes
Nike Canada
Pandora Charm Bracelet
Pandora Jewelry Official Site
Nike UK
Balenciaga
Shoes Nike
Nike Outlet
Pandora Jewelry Official Site
Adidas Shoes Men
Puma Shoes
Adidas Canada
Nike Sneakers
Nike Outlet Canada
Nike Sneakers For Men
Pandora Outlet
Pandora Jewelry
Pandora Jewelry Outlet
NMD
Pandora Bracelets Charms
Nike Shoes Canada
Asics Outlet
Asics Running Shoes For Men
Ferragamo Belts
Moncler
Ferragamo Belt Men
Adidas Trainers Sale
Pandora
Jordan 1 Dior
Pandora
Adidas Sale
Ferragamo Shoes
Pandora
Pandora Charms
Men's Nike Shoes
Pandora Bracelet
Pandora Charms
Pandora Charms
Pandora Jewelry
Pandora Charms Outlet
Adidas Outlet
Asics Sneakers For Women
Pandora Outlet Online
Jordan 1 Retro
Pandora Charms
Asics Shoes Women
Pandora Charms
Goyard Bags Outlet
Pandora
Pandora Jewelry
Pandora
Pandora Rings Official Site
Pandora Jewelry 70% Off Clearance
Pandora Charms
Charms Pandora
Jordan 1 Low
Adidas Official Site
Jordan One
Pandora Jewelry
Air Jordan 1
Balenciaga Sneakers
Adidas Canada
Pandora Jewelry Outlet
Cheap Mens Adidas Shoes
Pandora Jewelry
New Nike Shoes
Pandora Outlet
Yeezy 350
Jordan 1 Low
Balenciagas
Moncler Coats Outlet
Balenciaga Shoes Women
Pandora
Nike Canada
Jordan 1's
Nike Outlet Store
Pandora Charms Sale Clearance
Pandora Charms
Moncler Jacket
Nike Shoes For Men
Jordan 1 Retro
Wholesale Jordan
Cheap Nike Shoes
Asics Gel Kayano
Women Nike Sneakers
Yeezy Shoes
Nike Canada Online
Balenciaga
Pandora Rings Official Site
Pandora Jewelry
Air Max 720
Moncler Coat
Pandora Website
Asics Outlet
Jordans 1
Pandora Charms
Cheap Basketball Shoes
Nike Shoes Outlet
Yeezy
Adidas New Shoes
Wholesale Jordan Shoes
Balenciaga Triple S
Moncler Factory Outlet
Balenciaga
Pandora Jewelry

By Holder on   7/22/2022 4:25 PM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment   Cancel 
You must be logged in and have permission to create or edit a blog.