Last year I wrote about the ease and utility of pulling data into Excel via web queries. This mechanism has served me well in the interim and has proven to be an effective tool in collecting data for certain kinds of analysis. It’s not suited for every application, but if the data you are interested in consists of thousands to tens of thousands of rows, it’s a simple and workable option.
In a recent effort to pull data into an Excel spreadsheet for further analysis, I needed more flexibility than a static report URL could provide. The back end could accept parameterized values (e.g. the desired date range) in the report URLs. I just needed to be able to set these values (initially or through user input) from within the spreadsheet, then request the needed data. Seems generally simple and useful enough, so I’ll share what I did.
Sample Report
Ignoring most of the particularities of the reporting back end, say the report in question consists of an SQL query:
1 2 3 4 5 6 7 | /* Use defaults if no params sent */ SET @beginmonth = IFNULL(@begin_month, "2000-01-01"); SET @endmonth = IFNULL(@end_month, "2010-12-31"); SELECT date_format(date_entered, '%Y-%m') AS DATE, profit_dollars FROM main_db.monthly_profits WHERE DATE(date_entered) BETWEEN @beginmonth AND @endmonth ORDER BY DATE DESC; |
The URL to access it is http://initech.com/reports/total_profits. Using the query above, this would return data from the beginning of 2000 to the end of 2010. To pass in values for the start and end date parameters you would use the URL http://initech.com/reports/total_profits?begin_month=2000-03-01&end_month=2000-06-31. This would limit the data returned to the second quarter of 2000.
Use in Excel
So what if you want to use dynamic parameters like these when pulling data into Excel? Let’s assume you have a spreadsheet already pulling data from the static report URL and displaying results. If you run into trouble, check out my earlier article on the subject. Once that’s working, right-click the resultant data table in Excel, select Edit Query, and add the parameter names and values in the format ["FieldName"]:
FieldName in this case can be whatever you want to call the variable for the user’s convenience. This format is recognized by Excel and you’ll be prompted to enter a value. You can enter it yourself of course, or you can select a cell from which to pull the value. You can also specify whether to use the cell for future data refreshes and whether to refresh automatically. These features are key for our intended use. You can set a parameter value in a single cell and have it effect any number of web queries, across various sheets!
Note: This last feature only partially works in Mac Office 2008. A cell’s value can be selected as a parameter value but after updating it you have to manually refresh all related web queries. In Windows Office 2007, updating the specified cell updates all queries that have this property set, which is great for easing administrative load caused by having to update reports!
Date format annoyance
Your backend reporting system might likely expect dates in proper ISO format (YYYY-MM-DD). Excel, in its infinite wisdom, allows “Date” typed fields to be one of a myriad of formats, not one of which is proper ISO. So you need to make your date fields Text type and enter the dates as ISO format yourself.
This article has additional information on the above approach as well as more screenshots of the various steps.
No related posts.
Related posts brought to you by Yet Another Related Posts Plugin.
Hey thx for the article.
I still have a problem. Can you help me make a dynamic web query from smartmoney.com .
Everytime I want to change the query thing which looks like this :
http://www.smartmoney.com/quote/KO/?story=financials&opt=YI to http://www.smartmoney.com/quote/“Enter Your Ticker”]/?story=financials&opt=YI it doesn’t do anything.
Can you help me?
I’d like to create a web query that will validate an address list that I have against http://zip4.usps.com/zip4/welcome.jsp and return the zip and zip +4 for each record in the table. When I try to create the query, I can’t define dynamic inputs needed for the site nor can I define that I only need the zip data back. Any tips for a novice?
@Nick Unfortunately I don’t think you will be able to tackle that with Excel. The page doesn’t allow form parameters to be passed via URL parameters, so there’s a killer right there. And you would need to do some parsing to figure out the return data as well.
I recommend doing this through a Python script that can POST the form data.
Hi
Thanks for your explanation. I am interested in using a list of parameters stored in a worksheet, and having the web query work through the list of parameters (even changing several parameters at the same time) and placing the results back into the workbook.
I have seen commercial products that can do this, but I have no idea how they work.
Any ideas?
Thanks
Joe