Did you know there is an easy way to grab baseball statistics from web pages and easily pull the data into Excel? And if that same data on the web changes, the new information will automatically pull into your Excel file. The function in Excel is called a “web query” and it’s pretty amazing stuff. I’ll show you how to make your first web query in the post to follow
Credit Where Credit Is Due
I recently learned of web queries when I was looking into features in Excel 2013, and more recently I came across the idea being applied to baseball statistics in Joseph Adler’s book, Baseball Hacks: Tips & Tools for Analyzing and Winning with Statistics. If you enjoy the Excel and data analysis articles here at SmartFantasyBaseball.com, you need to get your hands on a copy of “Baseball Hacks”. I cannot recommend it enough. Web queries are only one of the 75 different “hacks” in the book.
Please note that I only recommend resources that I think are extremely valuable and that I use myself. The links to Baseball Hacks above are affiliate links, through which I earn a small commission if you were to purchase the book. The book will cost you the same if you directly go to Amazon and search for it. If you prefer a generic link through which I receive no benefit, use this.
Acquire A Target
The first thing you’ll need to do is identify a web page that has the data you want to grab. The information has to be in a table format. For this first example we’ll go to one of my favorite sites, Brooksbaseball.net, and pull in the pitch type usage information for Jose Fernandez.
If you visit the link above or look at the image below, you can see the percentage use of each type of pitch is presented in a tabular format (below the graph). It is this table that we will now link to an Excel file.
Making Your First Web Query
You can watch the video or refer to the written step-by-step instructions below.
| Step | Description |
|---|---|
| 1. | Start a new blank Excel file. |
| 2. | On the Data tab, click on the From Web option. |
| 3. | The “New Web Query” dialog will load. This wizard is looking for the web address of the page you are trying to pull into Excel.
Again, for our example we’ll be using Jose Fernandez’s pitch usage page here: http://brooksbaseball.net/outcome.php?player=605228&gFilt=&time=month&startDate=03/30/2007&endDate=09/29/2013&s_type=2Select and copy this URL. |
| 4. | Paste the website address into the “Address:” field. Click the Go button. |
| 5. | The website will load (note, you may get errors about scripts running on the page, these are ok to ignore). You can resize this window dragging the bottom right-hand corner. This will allow you to see more of the web page. |
| 6. | Scroll through the web page. You will notice several small black arrows over a yellow background. Each of these icons represents a table that can be linked to your Excel file.
For this specific example, look for the arrow that immediately precedes the detailed list of pitches. Click this arrow. |
| 7. | Once you’ve selected an arrow it will turn green and highlight the data that will come into Excel. |
| 8. | Click the Import button. |
| 9. | Because we started this example with a blank workbook, we can leave the Existing worksheet option and click OK. |
| 10. | You should see a message indicating that the data is downloading into Excel. |
| 11. | When the data is loaded you should see the exact same information in your Excel file as you see on the webpage. |
You’ve Shown Me Some More Excel Voodoo. But What Can I Do With This?
That’s a fair question. In the remaining parts of this series we’ll walk through creating a practical example of something we can build with this.
The important thing to recognize now is that the power of the web query is not just in its ability to pull data into Excel. The true power is that the data in Excel updates as the data changes on the web. So this function will be most useful when linked to information that is regularly updated. Things like Pitch f/x data, preseason projections, game log data, or player statistics.
A Word of Caution
Please keep in mind that web queries may violate the terms of service for certain web sites. If you query a web site, it is your responsibility to make sure that you use the data in compliance with any applicable terms and conditions for that site.
THanks For Reading
As always, be smart. And please ask any questions in the comments below.
In Open Office:
Insert
Link to External Data..
Hey man, how do you do this on Excel for Mac 2011? I’ve tried making a .txt file by editing some of the default queries that Office gives you, but to no avail. I love your stuff! Just need to use it on mac.
Thanks,
Sam
Hi Sam. When I first read your comment, I figured you were talking about how to do the CSV import I recently talked about in this post, but now I see you left it on this web query page.
I did add a few screenshots to that post above. They’re towards the bottom.
It does also depend on the version of Excel you have. My screenshots are from Excel for Mac 2011 (but now 2016 is out). In 2011 and earlier, you should see “External Data Sources” on the “Data” tab. The “HTML” button should get you started on a web query and the “Text” option should work for CSV.
If you already found those and are still having trouble, let me know. Maybe try shooting me an e-mail with some specifics about where you’re getting hung up.