Calc2Code Tips of the Day

Day 3

Excel has a great (and little-known) addin function called SQL.REQUEST which allows you to pull information out of a database and dump the results into a spreadsheet. You do need to know a little bit about the SQL language to use it, and teaching SQL is a little beyond the scope of this Web page, but if you don't know any SQL, a sample line will give you an idea of how it works:

Select FirstName, LastName, Sales From SalesHistory Where SaleDate >= '1/1/06' and SaleDate <= '12/31/06'

will return a list of all the lines in the database table called SalesHistory that have a SaleDate in 2006. For each line, it will return the columns called FirstName, LastName and Sales, in that order. The words Select, From, Where and And are part of the SQL language (and the And is optional, only used here because we wanted to specify both an upper and lower bound). This is a lot like the DMIN, DMAX, and other database functions built-in to Excel, but much simpler to understand.

The powerful part of this is that the SQL.REQUEST function takes this Select statement as a as a text string that can be built up with a formula. So if you have the 1/1/06 in cell B15 and the 12/31/06 in cell B16 as part of a nice-looking template, the call to SQL.REQUEST can look something like:

=SQL.REQUEST(...,"Select FirstName,LastName,Sales From SalesHistory Where SaleDate >= '"&B15&"' and SaleDate <= '"B16&"'")

This means you can create your queries based on the values in cells. The ranges you return, the fields you retrieve, the table you look in, and even the database can all be changed by the user.

The result of a call to this function is an array formula, so it can span many rows and columns. And once the values are retrieved, you can use Excel formulas to massage and manipulate the retrieved data just as if it has been typed in or loaded from a text file.

The functionality to get data from a database is available in VBA, but many people don't know VBA and/or don't want to use it, and this function represents a very simple alternative. It does have a limitation in that it can only read from a database; it cannot write to it, whereas the routines in VBA do allow writing.

SQL.REQUEST does have one other limitation: it doesn't ship with Excel 2003 or Excel 2007. However, it does appear to work with them just fine. If you are using either of these newer versions, and upgraded from an older version, you have the needed XLODBC.XLA file, and you can simply load it and use it.

If you can't get your hands on a copy, you still have another option: use the one that comes with Calc2Code. Yes, in our zeal to emulate all of Excel's built-in functions so that converted Calc2Code DLLs can use them, we have made this function available. One thing we could not do, though, is make our version have a dot in the name, so it is SQL_REQUEST instead of SQL.REQUEST. If you use the one that came with Excel, you need to replace the dot from the spreadsheet formulas with an underscore before running the Calc2Code builder.

Let’s look at SQL.REQUEST in action. As in the above example, suppose we have a database with a table named SalesHistory with the following information in it:

We would like to calculate the dollars per day of selling a salesperson is generating. If it takes them 15 days to make a 900 sale, we’ll say that’s 60/day. They will have several sales over time, and we want to calculate the average of each sale.

Our Select statement for a salesperson with a given ID between dates MM and PP is

SELECT SaleDate, Sale FROM SalesHistory WHERE ID=’NN’ AND SalesDate<=’MM’ AND SalesDate >= ‘PP’ ORDER BY salesdate

The ORDER BY tells the database to return the results sorted by salesdate.

We would insert this into a SQL_REQUEST call as shown in saleshistory2.xls. We haven’t used SQL_REQUEST here because to open our spreadsheet you’d need to download the database from our Web site which is a bit beyond the scope of today’s tip. Instead we just have the values in cells B6:C10 without the SQL.REQUEST call (what the call would look like, without the connection string (which is database dependent) is shown in D1):

Column I is used to determine whether we are past the last row of results. If not, the number of days it took to make the sale is in Column F, and the sales per day is calculated in column H. We then average the non-blank entries by multiplying the sales per day by 1 when we have a non-blank row, and 0 otherwise. The average is then calculated in K3.

Now we can create a function with Calc2Code that calculates the average sale per day between a given pair of dates for a given salesperson, based on data in the database. Here’s the Configuration screen:

This routines works as long as the salesperson has fewer than 100 sales in the date range. We could make room for more by simply copying the formulas down more rows, and making the formula in K3 reference the larger range. Calc2Code provides another nice way to avoid this problem, which is the subject of another tip for another day.

Click here to see today’s next tip…