Daily Dose of Excel

I answer about four questions a month on the newsgroups. I'm off to a roaring start in April, but I don't like my answer much. I never know just how "correct" an answer to give. So I gave an answer that I felt was appropriate for the original poster and I'll give a slightly different answer here.

The OP has an external query pulling stock quotes from Yahoo! Finance. He's using their csv download option, but just querying the csv rather than downloading it and loading it into Excel. One problem with that is that it puts all of the data in one column. If there's a built-in way to fix that, I don't know it. The other problem is that the list of companies he wants to see is variable and lives in a range of cells. I actually don't know if these are his problems, but I like to pretend.

My objectives are to create a web query to Yahoo, add a parameter that points to a range, and parse the data into multiple columns. Does that sound like fun? Well, I'm doing it anyway.

I can't create the query the old fashioned way. The web query user interface is like a browser; if you try to point to a CSV file, the browser wants to download it. So I create a web query to whatever table is on whatever page that happens to open up. Basically, I'm creating a query that I will later edit to point to where I want.

I pick any old arrow on the Dell website and create a query. I get a message that my query doesn't return any data, but I just OK past that message because I know I'll be making changes. Next I go to the VBE, and specifically the Immediate Window, to change my query.

The part you can't see says this:

wshquotes.QueryTables(1).Connection = "URL;http://quote.yahoo.com/d/quotes.csv?s=^GSPC[""EnterTicker"",""Ticker""]&f=nl1c"

If this URL isn't exactly what you want, don't worry. Alex has done all the legwork that will allow you to get whatever data you want. For me, I'm getting the name, last, and change for the S&P500 Index plus whatever else I enter. The "whatever else I enter" part is the parameter and is the name/prompt pair of strings inside the square brackets. When I Refresh the QueryTable, it asks me for a Ticker. I enter MSFT and the web query returns some data.

I have a parameter, but it doesn't point to a range, so that's next. The range B1:B10 will hold whatever ticker symbols I want and D1 will put them in the format I need for the URL.

The formula in D1 is ="+"&B1&IF(NOT(ISBLANK(B2)),"+"&B2,"")&IF(NOT(ISBLANK(B3)),"+"&B3,"")&IF(NOT(ISBLANK(B4)),"+"&B4,"")
&IF(NOT(ISBLANK(B5)),"+"&B5,"")&IF(NOT(ISBLANK(B6)),"+"&B6,"")&IF(NOT(ISBLANK(B7)),"+"&B7,"")&IF(NOT(ISBLANK(B8)),"+"&B8,"")
&IF(NOT(ISBLANK(B9)),"+"&B9,"")&IF(NOT(ISBLANK(B10)),"+"&B10,"")

Inspiring, huh? If a ticker exists, it puts a "+" in front of it and adds it to the list. Now I can click on the Query Parameters button on the External Data Toolbar and point to D1.

I also click on the Data Range Properties button and check the box to make the table automatically refresh.

OK, I have a parameterized web query that refreshes automatically. Unfortunately, it gives me this

Everything in one column. Yuck. Time to get fancy. I know that Text to Columns will parse the results, but I'm not going to manually do that every time the QueryTable refreshes. Fortunately, QueryTables have events. Two events, to be precise. I'll be using the AfterRefresh event. I'll bet you can guess what the other one is.

Other than out brief jaunt into the Immediate Window, we've been pretty much in Excel's UI up until now. It's time to step through the looking glass into VBA and (gasp) class modules. Open the VBE (Alt+F11) and add a class module and a module to your project. Name the class module (F4) CQTEvents and name the module MEntryPoints. In your class module, put this code:

Of course I never use public variables in a class because I'm a class module snob. So I have a private variable declared WithEvents that exposes the events of the QueryTable variable. Because it's a private variable, I need public get and set statements so code outside of the class can access it. Finally, I create the AfterRefresh event (using the code pane drop downs) and simply call the TextToColumns method to parse out the QueryTable results. If you try to TextToColumns into a range that already contains data, you'll get a message. I use DisplayAlerts to avoid that.

The only thing left is to tell my class which QueryTable I care about. I do that in the standard module with this code:

That's so easy I don't even have to explain it. Run Auto_Open and wait for your QueryTable to refresh. Then go fix your typos and you're all set. And you thought class module were difficult. If you're following along at home, be sure to do this during trading hours. Having those one minute updates where nothing changes isn't too exciting.

Laura asks:

I am trying to use one query but with multiple paremeters depending on the cell. For example I have a list of dates in Column A and I want to run the query for each dates shown in column B, so the paramter for B1 is A1, B2 is A2 etc.. How do I do this in Excel?

I suppose you could have a bunch of different queries - one for each cell. I think that would be hard to maintain and probably lead to a huge Excel file. I would use a helper sheet that stored one query and use formula to bring over the data I need.

First, create a query on a sheet that contains all the records for the table you're interested in. For this example, I'm using the Invoices table from Northwind.mdb (actually mine is called Nwind.mdb, but I don't know what the difference is). Place the query in A3, or if you already have it in A1, insert a couple of rows above the query. We'll need them later. If you need help creating this query, see Querying External Data in Excel.

Next, create a new sheet and put your dates in column A. Back on the query sheet, put some formulas in B1 and B2.

B1: =TEXT(MIN(Summary!A2:A9),"mm/dd/yyyy")
B2: =TEXT(MAX(Summary!A2:A9),"mm/dd/yyyy")

These will serve as the parameters for this query. It will limit the rows that are brought in, but there will still be more rows than you need. I needed the TEXT function because the ODBC driver couldn't understand Excel's dates.

Now I'll make the query a parameter query. I do this in VBA, but you can use Query Manager, Query Editor, or you can edit the SQL statement directly in MSQuery. In the Immediate Window of the VBE, I type ?activecell.QueryTable.CommandText (with a cell in the query selected, or course), and see that the SQL statement is:

SELECT Invoices.ShippedDate, Invoices.CustomerID, Invoices.ExtendedPrice FROM [Invoices]

To add the parameters, I type this into the Immediate Window:

activecell.QueryTable.CommandText = activecell.QueryTable.CommandText & " WHERE (Invoices.ShippedDate >= ? and Invoices.ShippedDate < = ?);"

In the end, the SQL statement should be:

SELECT Invoices.ShippedDate, Invoices.CustomerID, Invoices.ExtendedPrice FROM [Invoices] WHERE (Invoices.ShippedDate >= ? and Invoices.ShippedDate < = ?);

Now when I refresh the query table, Excel will prompt me for parameter values. Just type something that looks like a date and don't worry about what it returns. If all has gone well, the Parameters button on the external data toolbar will now be enabled. Click it to open the Parameters dialog.

I change Parameter1 to point to cell B1 and Parameter2 to point to cell B2. Now the query is set to return a limited number of rows, but it will contain all the rows you need.

The last step is make the formula on the non-query sheet. With dates in column A, I put this formula in column B to return the first CustomerID for that date.

=VLOOKUP(A2,Data!Query_from_MS_Access_Database,2,FALSE)

and I get this:

On an ongoing project, the client uses the TM1 OLAP system. One of the interesting things I noticed was this:

A user can "slice" data from the database into Excel. The result is a new worksheet where the appropriate cell contains a formula such as =DBRW(...), which essentially looks up the TM1 database for the current value that corresponds to the specified parameters.

So far so good. But, here's the twist. One can enter a new value into the cell containing the formula. TM1 will update the OLAP database with this new value and restore the formula.

As soon as I saw what was happening I guessed how it was done. Here's the implementation of a proof-of-concept. Of course, as a proof-of-concept there are a lot of safeties, performance issues, and other niceties that are missing.

An obvious requirement is that that one must have a secondary data storage since it is impossible for a value and a formula to actually co-exist in a cell. So, that requires a backend database to store the actual value and I decided to use MS Access to create one.

The database had a single table with 3 columns: Col1, Col2, DataVal. For those who want to know how this maps to an OLAP system, think of Col1 and Col2 as dimensions in a TM1 OLAP system and DataVal as the value at the intersection of specific elements in those dimensions.

That led to the infrastructure to access data in the database. In a standard module:

OK, nothing unusual about the above. It's standard stuff to write a User Defined Function that retrieves data from an external database. Again, remember this is proof-of-concept code and leaves out a lot of safeties and performance effectiveness issues.

This is used in a worksheet cell as =DBVal(A3,B3,C3) where A3 contains the Access table being queried, and B3 and C3 the values for the 2 columns Col1 and Col2 respectively. In TM1 parlance, this would correspond to the cube name, and the two elements of the 2 dimensions in the cube.

Next, the infrastructure to allow a new value to update the database.

First, a worksheet event procedure that keeps track of the existing formula. Note that I would never deploy an event procedure in a worksheet code module, but it works well to test concepts.

Next, an event procedure that responds to a new value entered by the user.

The SelectionChange event procedure above saves the cell formula, if it has one, at the time the user selects the cell. Then, if the user enter a value, the Change event procedure uses the new value to update the database through UpdateDB and then restores the formula saved by the SelectionChange procedure.

The corresponding code for the updateDB subroutine in the standard code module:

And, for completeness, code, in the standard module, to close the database connection:

To use the above, in the Access database, a table named Table1. I had 4 records in it corresponding to values of "a" and "b" for Col1 and Col2. The associated DataVal values were 1, 2, 3, and 4, respectively.

In the Excel worksheet, Cell A3 had the value "Table1" and in B3 and C3 one could enter the values corresponding to Col1 and Col2 (i.e., either "a" or "b"). The =DBVal() formula then retrieved the corresponding value from the database.

Now, enter a new value in the cell that contains the =DBVal() formula. The code will update the database with the new value and restore the =DBVal() formula. This will show the new value in the cell.

I'm getting an error in MSQuery. I've had nothing but trouble with MSQuery since I changed employment. The current error I'm getting is:

The Microsoft Office Access database engine cannot find the input table or query 'mdb.tblDocuments'. Make sure it exists and that its name is spelled correctly.

Aside: I just had to type that error message because error text in a message box isn't copy-able.

The SQL statement MSQuery generates is:

SELECT tblDocuments.DocID FROM `C:\AIMCDConfig`.mdb.tblDocuments tblDocuments

It seemed to me that it chose odd location for the quote marks, particularly since there are no spaces in the path. I tried deleting the quote marks, I tried putting the quote mark after mdb, and I tried putting the quote mark after tblDocuments. I also tried messing with that period between mdb and tblDocuments. Deleting them produced the same error. Moving them created two errors in succession, namely:

SQL Query can't be represented graphically. Continue anyway?

Could not add the table "C:\AIMCDConfig.mdbtblDocuments".

What finally worked is:

SELECT tblDocuments.DocID FROM "C:\AIMCDConfig.mdb".tblDocuments tblDocuments

I still can't get rid of the “can't be represented graphically” message, but it works. Once I get the query in Excel, I manually change the CommandText property to:

SELECT tblDocuments.DocID FROM tblDocuments

and it works like a peach.

When I create an external data table in Excel, I almost always want to have a named range that refers to every column. This way I can create array formulas and refer to column names rather than cell references. Which formula is easier to read?:

=SUM((LEFT(Assembly!Name,3)="AXI")*(Assembly!QuantityOnHand))
=SUM((LEFT(Assembly!B2:B174,3)="AXI")*(Assembly!G2:G174))

Another advantage is that the named ranges expand and contract when the external data changes. No need for dynamic ranges, because they're already built in.

To create these named ranges, I select the entire QueryTable and choose Insert > Name > Create and choose the Top checkbox. This creates a global range name based on whatever is in the first row of the selected range.

The downside is that this creates global names and I want local names. The reason I want local names, other than simply good practice, is that I may have the same name on a different sheet. In one workbook, I have a sheet with an external data query of raw materials and another sheet for finished goods. They both have a column called QuantityOnHand.

In order to change the names to worksheet-level, I wrote this macro:

It looks for names whose Parent is the workbook (global names) and that are on the sheet in question. Of those, it looks for names whose Name property is equal to the cell above (cells(0)), which indicates that the name was created with Insert > Names > Create. Once the name has been identified, it saves the Name and Refersto properties so that the name can be recreated. The name is deleted and then recreated with the sheet name prefixed, thereby making it local or worksheet-level.

I tried simply changing the Name property, but nothing happened - the names stayed globally scoped. I tried deleting the name after I created a worksheet-level name, and nothing happened. I had to first delete the global name, then create the local one. By the time I was done, I realized that I should have just written my own Create routine instead of fixing already created names.

I think a 'Create Local Names' utility should be added to the Name Manager. What do you think?

If you're working with ActiveX Data Objects (ADO) and connecting to a Jet database, you may get this error. One possible cause is that you misspelled a column name. When Jet encounters an unrecognized column name, it assumes it's a parameter. Check the spelling of the field names in your SELECT statement.

Another possible cause, and one I experienced today, is surrounding strings in your WHERE clause with double quotes - it needs to be single quotes. Mine was fairly easy to track down because the error was "Too Few Parameters, Expected 3". My SELECT statement started like this: SELECT * FROM salesorder... so I knew I hadn't spelled any column names wrong. I also knew I had five variables in my WHERE clause; three Strings and two Dates. I figured the problem must be in the Strings and it was.