Return to General Talk

## Computer help. Stats to Excel

Moderator: Baseball Moderators

### Re: Computer help. Stats to Excel

Tavish wrote:
AussieDodger wrote:
Tavish wrote:Do a web query.

1. Data > Import External Data > New Web Query
2. Copy/Paste the address into the address bar.
3. Select the table you want to import.
4. ????
5. Profit!

This is Excel not underpants gnomes

The philosophy is all-encompassing.

Tav is right. I use it at work all the time.

1. Data > Import External Data > New Web Query
2. ?????
3. Paycheck!

Want an easy icon?
http://www.fantasybaseballcafe.com/forums/viewforum.php?f=133
All you have to do is beat a few guys at picking Sunday's winners.[/url]
StlSluggers
Hall of Fame Hero

Posts: 14414
(Past Year: -302)
Joined: 24 May 2004
Home Cafe: Baseball
Location: Parking in the gov't bldg @ 7th and Pine. It's only \$3.00 on game day!

### Re: Computer help. Stats to Excel

So, Steve-O, did the web query work?
Keep wreves in General Talk in 2011!
knapplc
Hall of Fame Hero

Posts: 7853
(Past Year: -17)
Joined: 27 Dec 2004
Home Cafe: Football
Location: It's "ell see." ELL SEE!!!

### Re: Computer help. Stats to Excel

thedude wrote:You can also just cut and paste and click "text to colums" under data.

This is the method I use all the time. It is not perfect - but with a little tweaking - it gets the job done.

A lot of times with baseball stats it separates the first and last names into two different columns and you have to make a new column
and use a formula like ("=A1&" "&B1) to combine the names into one column.
dclark0699
Major League Manager

Posts: 1764
(Past Year: -11)
Joined: 13 Jan 2007
Home Cafe: Baseball
Location: The Ohio State University

### Re: Computer help. Stats to Excel

Web query is definitely the way to go, especially if you want to keep updating as the season progresses. I do this to track a non-roto fantasy league that I have been a part of for years - I just set up the spreadsheet last year and the web query works like a charm (albeit slowly since I grab so much data). Any questions about it, let me know ...

My biggest difficulty is that I would like to grab more than the top 50 in any given category (as shown by your links) with one query. I figured out how to do this at foxsports.com, but I think mlb.com is a more reliable source of stats. Does anyone know how to display an mlb.com stats page with more than the top 50 at a time?
wolv0023
Little League Legend

Posts: 22
Joined: 6 Sep 2004
Home Cafe: Baseball

### Re: Computer help. Stats to Excel

So if you grab data from a website using a web query, are you saying it updates when you reopen that sheet? Or that it's easier to update when new data comes online? I'm not familiar with web queries, so any info would be great.
Keep wreves in General Talk in 2011!
knapplc
Hall of Fame Hero

Posts: 7853
(Past Year: -17)
Joined: 27 Dec 2004
Home Cafe: Football
Location: It's "ell see." ELL SEE!!!

### Re: Computer help. Stats to Excel

You can set up a worksheet in newer versions of Excel to automatically update the web query when the sheet is opened. I don't have this option set - I just go to Data -> Refresh Data and the query is then reset to the latest info on the website.
wolv0023
Little League Legend

Posts: 22
Joined: 6 Sep 2004
Home Cafe: Baseball

### Re: Computer help. Stats to Excel

knapplc wrote:So, Steve-O, did the web query work?

Yup. I was using it to look at the .200 slugging and k/bb thing for ST stats. I'll share my results here (instead of the leftovers) since y'all were so helpfull. BTW, my draft was tonight.

Hitters with increase of .200 in Slugging
Code: Select all
`Player    IncreaseN Swisher   0.375I Rodriguez   0.367P Polanco   0.344C Guillen   0.292B Anderson   0.288F Pie   0.274R Furcal   0.272C Guzman   0.265R Howard   0.257S Pearce   0.224L Milledge   0.211`

Pitchers (not very useful because of limited sample size)
Code: Select all
`Player    % IncreaseC Zambrano   421%M Parra   369%R Oswalt   357%T Buchholz   352%B Webb   335%B Carlyle   332%J Litsch   324%F Hernandez   293%R Halladay   282%A Lopez   254%J Santana   237%R Nolasco   227%J Towers   221%J Weaver   213%H Bell   203%J Contreras   196%J Maine   196%`
In my fridge: Founders Kentucky Breakfast Stout (KBS), Central Waters Peruvian Morning, Bell's Batch 10,000, Dale's Pale Ale, Capital Brewery Amber, New Glarus Moon Man, and Guinness.
Steve-o
Major League Manager

Posts: 1863
(Past Year: -16)
Joined: 7 May 2004
Home Cafe: Baseball
Location: Finally back home. A sweet 15 mi from Miller Park.

### Re: Computer help. Stats to Excel

StlSluggers wrote:
Tavish wrote:
AussieDodger wrote:
This is Excel not underpants gnomes

The philosophy is all-encompassing.

Tav is right. I use it at work all the time.

1. Data > Import External Data > New Web Query
2. ?????
3. Paycheck!

Is that the same as

1.Meet girl > Buy dinner > Get drunk
2.???????
3. Make breakfast?

AussieDodger
Hall of Fame Hero

Posts: 11301
(Past Year: -37)
Joined: 22 Jan 2006
Home Cafe: Baseball
Location: What do you mean, Flash Gordon approaching?

### Re: Computer help. Stats to Excel

AussieDodger wrote:
StlSluggers wrote:
Tavish wrote:The philosophy is all-encompassing.

Tav is right. I use it at work all the time.

1. Data > Import External Data > New Web Query
2. ?????
3. Paycheck!

Is that the same as

1.Meet girl > Buy dinner > Get drunk
2.???????
3. Make breakfast?

Well, let's see. One of them makes money, the other one uses it up.

I'm going to say that they're not related in the slightest.

Want an easy icon?
http://www.fantasybaseballcafe.com/forums/viewforum.php?f=133
All you have to do is beat a few guys at picking Sunday's winners.[/url]
StlSluggers
Hall of Fame Hero

Posts: 14414
(Past Year: -302)
Joined: 24 May 2004
Home Cafe: Baseball
Location: Parking in the gov't bldg @ 7th and Pine. It's only \$3.00 on game day!

### Re: Computer help. Stats to Excel

knapplc wrote:So if you grab data from a website using a web query, are you saying it updates when you reopen that sheet? Or that it's easier to update when new data comes online? I'm not familiar with web queries, so any info would be great.

Yeah the web queries will update. You can set the behavior, like if you want it to refresh when you open the workbook or at intervals. They are really nice when you have many pages to scrape data from. If you can determine the URL sequence you can use a macro and a web query to pull a lot of data off a lot of pages in a relatively short period of time.
Maine has a good swing for a pitcher but on anything that moves, he has no chance. And if it's a fastball, it has to be up in the zone. Basically, the pitcher has to hit his bat. - Mike Pelfrey
Amazinz
Mod in Retirement

Posts: 18680
(Past Year: -120)
Joined: 16 Mar 2004
Home Cafe: Baseball
Location: in Canada, toughening up figure skaters

PreviousNext

Return to General Talk

### Who is online

Users browsing this forum: No registered users and 10 guests