Excel Web Query Help

**TomG** · 06-14-10, 11:34 AM

Originally posted by str8sNUTZ

1. I have multiple Worksheets setup, with each worksheet having a different web query. Some of the worksheets import MLB game data with the matchup listed as "HOUSTON at NY YANKEES" in an individual cell, whereas another worksheet has the matchup listed at HOUSTON and then the cell below it has NY YANKEES.

I used Text to Columns to split the HOUSTON at NY YANKEES up, and get the format the same, however when the web query runs, it does not update the Text to Column result, I have to manually highlight the cells, choose Text to Columns, then choose the output cell(s). Is there a better way to do this?

Web queries are great at importing raw data from the web into Excel. Unfortunately, they are terrible at importing the data into an easily workable format. You're going to need to do one of two things. 1) Write a macro that automates the Text to Columns and whatever other formatting you desire. 2) Play around with Excel's Text Formatting formulas using stuff like FIND, LEFT, RIGHT, MID, to grab the data from the import.

Originally posted by str8sNUTZ

2. Is there anyway to have a web query follow a link to a different webpage? For instance, I want to get the data off of covers.com for matchups. However, the main matchup page (covers.com/sports/mlb/baseball-matchups.aspx) contains the links to the individual matchups, and those links change on a daily basis (at least I believe they do). Or is there another way to get this data (pitching stats, L5 games, L10 games, trends, etc.)

Yes. You can setup a dynamic web query. It's a bit complicated but you'll eventually be able to figure it out. Google search "Dynamic Web Query" to get started on learning more.

Originally posted by str8sNUTZ

3. I have searched and searched for a comprehensive database of player stats to be used in a web query, but end up only finding retrosheet and baseball-ref that have an albhabet list that I must click on the 1st letter of their last name, then find their corresponding page. I can't fathom how to setup a web query to pull all the current player stats. My only solution is to setup multiple worksheets (each with a web query) that correspond to a list from fangraphs, or espn, etc. All these websites have the info, but it is usually found on multiple pages. So I would setup each worksheet to correspond to a different page. Then I could create one more worksheet that would pull all the data from the web query worksheets. Is there a better way to do this?

Sounds like some combination of dynamic web queries and aggregation. That's a lot of work. I'd suggest keep searching for a more friendly source site (I don't have any suggestions). That will save you a lot of work.

Originally posted by str8sNUTZ

Thanks for any direction you can provide.

You're on your way to becoming an Excel wiz. Good luck!

**str8sNUTZ** · 06-14-10, 12:34 PM

Thanks TomG. I'll look into the Dynamic Web Queries.

Now I've got another problem. I had a worksheet with a Web Query that pulled in 4 columns of info for each game. It looked similar to:

Houston data1 data2 data3
NY Yankees data1 data2 data3

The team names that imported all had a SPACE after the name, screwing up all my vlookup formulas. So I left a blank column after the data, then in the 6th column I used a formula to get rid of the SPACE after the team name, and in the columns after that used an IF formula to copy the data from the 2nd, 3rd, 4th columns so that I could use Vlookup (since it has to read left to right).

However, when the web query refreshed with today's games (only 3 games), I got a bunch of #REF errors in the 7th, 8th and 9th columns (where the data from the IF statements was). I can't, for the life of me figure out why the formulas I have in the 7th, 8th, and 9th columns doesn't remain static, thus pulling the info over. When I check the Formula in these columns, all cell references (from the 2nd, 3rd, and 4th columns) are now #REF instead of B2 (for instance).

Thought this would be easier than figuing out Python and MySQL, but maybe I was wrong.

**TomG** · 06-14-10, 01:24 PM

Try using the =INDIRECT function to prevent wiping of data.

**TomG** · 06-14-10, 01:27 PM

And/or your web query options to make sure the web import isn't deleting cells and then inserting new ones over them. There is an option for this.

**str8sNUTZ** · 06-14-10, 01:50 PM

Thanks again Tom. Going to work on this tonigt, and will report back tomorrow.

**djiddish98** · 06-14-10, 03:11 PM

You could also vlookup a Concatenation of your team name with a space. just set it up right in the formula. Not sure if this is programmatically more efficient than creating a new column with the right vlookup string, but it does cut down on the useless columns.

I agree with everything that TomG wrote. Web Queries are great for updating simple formatted data in tables on a single page. You'll need something more powerful if you actually want to walk through a website

If you feel like it, you can take the macro approach and work with your web queries that way. If you're not familiar with macros, you can use the macro recorder in excel to record what you want the program to do and go from there. The macro recorder is very crude, but you'll at least see the necessary code. Replace the static content in the macro recorder code with some variables and you can then have a much more robust "web query" at your fingertips.

Once you feel comfortable with macro's though, I would recommend ditching web queries and moving onto working with an internetexplorer.application object. I'm no expert, but I feel like its a lot more flexible than excel's web query.

**str8sNUTZ** · 06-14-10, 04:20 PM

Ok, so working on getting the Web Query for covers.com to work. I run a simple web query to import the matchups page, and am now trying to figure out how to run a dynamic web query to continue on to a specific matchup page.

The thing I noticed is the weird way that the link is for the matchups. For instance today (4 games) the end of the links are:

summary_3.html
summary_4.html
summary_5.html
summary_6.html

If they numbered them corresponding to the # of games that day, it would be simple (I think). However, the way they start at 3 is odd. Going to take a look at it tomorrow with a full slate of games.

**str8sNUTZ** · 06-15-10, 09:56 AM

After reading numerous google results on Dynamic Web Queries, I couldn't wrap my head around how to set it up to go to covers.com/sports/mlb/baseball-matchups.aspx then (after some input by me) redirect the data download to another webpage.

So I decided to do it like this (probably the much longer/harder way):
Opened a new Excel Workbook
Created 30 worksheets (since the max games that the Covers Matchup page should have is 30 = 2 days at 15 games per day)
Each worksheet corresponds to 1 game, and within that one worksheet I have it running 3 seperate web queries (1 for pitchers, 1 for Last 5 games, and 1 for Last 10 games)

Now I have the unenviable task of getting another worksheet to pull the correct data for TODAYs games from the corresponding 30 worksheets.