Microsoft Excel Queries for Stats - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Baseball Leftovers

Microsoft Excel Queries for Stats

Moderator: Baseball Moderators

Microsoft Excel Queries for Stats

Postby SAV » Thu Oct 10, 2013 11:04 pm

It's the offseason, so it's time to refine processes for 2014. I've been downloading stats everyday from Fangraphs. Supposedly one can get the information into Excel via queries. Rather than recreating the wheel I decided to reach out to some of the smartest baseball people I know: anyone have something already?
Thank you!
SAV

“You win pennants in the off season when you build your teams with trades and free agents.” –Earl Weaver
SAV
College Coach
College Coach

User avatar

Posts: 325
(Past Year: 1)
Joined: 11 Nov 2005
Home Cafe: Baseball

Re: Microsoft Excel Queries for Stats

Postby Element » Fri Oct 11, 2013 1:49 pm

What exactly is the question .. ?
Image
Element
General Manager
General Manager

User avatar
Cafe RankerMock(ing) Drafter
Posts: 4909
(Past Year: 719)
Joined: 25 Feb 2006
Home Cafe: Baseball

Re: Microsoft Excel Queries for Stats

Postby SAV » Fri Oct 11, 2013 2:09 pm

Element wrote:What exactly is the question .. ?


lol. I want to know if anyone has already created Excel dynamic queries to obtain player stats from the web. I've used the "Export Data" function in Fangraphs this year and then copy/pasted it into my analysis tool ... but I've HEARD the queries are a far more efficient way to accomplish it ...
Thank you!
SAV

“You win pennants in the off season when you build your teams with trades and free agents.” –Earl Weaver
SAV
College Coach
College Coach

User avatar

Posts: 325
(Past Year: 1)
Joined: 11 Nov 2005
Home Cafe: Baseball

Re: Microsoft Excel Queries for Stats

Postby Skin Blues » Fri Oct 11, 2013 3:22 pm

It's pretty quick to just call up the data you want on Fangraphs using custom leaderboards and export it as a CSV. Can't imagine anything else being easier.
Skin Blues
General Manager
General Manager

User avatar

Posts: 3081
(Past Year: 113)
Joined: 11 Apr 2010
Home Cafe: Baseball

Re: Microsoft Excel Queries for Stats

Postby Element » Fri Oct 11, 2013 3:31 pm

SAV wrote:
Element wrote:What exactly is the question .. ?


lol. I want to know if anyone has already created Excel dynamic queries to obtain player stats from the web. I've used the "Export Data" function in Fangraphs this year and then copy/pasted it into my analysis tool ... but I've HEARD the queries are a far more efficient way to accomplish it ...


I cannot imagine that this is possible (or legal) without back-end access to the sites DB. As mentioned, it's easiest to create a custom leaderboard to gather the information that you want & export it. I have a good head start on 2014 already also.
Image
Element
General Manager
General Manager

User avatar
Cafe RankerMock(ing) Drafter
Posts: 4909
(Past Year: 719)
Joined: 25 Feb 2006
Home Cafe: Baseball

Re: Microsoft Excel Queries for Stats

Postby SAV » Mon Oct 14, 2013 8:30 pm

[/quote] I cannot imagine that this is possible (or legal) without back-end access to the sites DB. As mentioned, it's easiest to create a custom leaderboard to gather the information that you want & export it. I have a good head start on 2014 already also.[/quote]

What I did in 2013 was daily export data last 14 days hitters stats (std, advanced, and batted ball) and last 14 days pitchers stats (std, advanced, and batted ball) to look at most recent trailing 14 days. That's six downloads every morning before I even begin massaging data. Never did get the custom leaderboard to work for me ... which would cut it down to two downloads/day. Everytime I saved the custom reports at the bottom of the Fangraphs page (below) all I got was a version as of a point in time. What did I do wrong? http://www.fangraphs.com/leaders.aspx?p ... &players=0
Thank you!
SAV

“You win pennants in the off season when you build your teams with trades and free agents.” –Earl Weaver
SAV
College Coach
College Coach

User avatar

Posts: 325
(Past Year: 1)
Joined: 11 Nov 2005
Home Cafe: Baseball

Re: Microsoft Excel Queries for Stats

Postby Skin Blues » Tue Oct 15, 2013 3:33 pm

It's hard to fiddle with it now because it stops at the end of the regular season. I don't see any date-specific data in the URL, so maybe it's a caching issue. But if you find a way to alter the URL to go to specific date range then you could do all data collection at once instead of having to do it every single day.

Alternatively, you could just download game logs for every player at the end of the season. It's a lot more work, but you could do it all at once, and/or program something to do it for you. Not sure on the legality of that. Out of curiousity, what do you plan on using this for?
Skin Blues
General Manager
General Manager

User avatar

Posts: 3081
(Past Year: 113)
Joined: 11 Apr 2010
Home Cafe: Baseball

Re: Microsoft Excel Queries for Stats

Postby SAV » Tue Oct 15, 2013 3:40 pm

Skin Blues wrote:It's hard to fiddle with it now because it stops at the end of the regular season. I don't see any date-specific data in the URL, so maybe it's a caching issue. But if you find a way to alter the URL to go to specific date range then you could do all data collection at once instead of having to do it every single day.

Alternatively, you could just download game logs for every player at the end of the season. It's a lot more work, but you could do it all at once, and/or program something to do it for you. Not sure on the legality of that. Out of curiousity, what do you plan on using this for?


I use this information in-season to look for trending players (either up or down). It's always 14 days trailing, but it's a different snap-shot everytime I download (oldest day drops off, next new day adds on just by virtue of in being the most recent 14 days). I then run it through my valuation formulas and look for other indicators (e.g., too low a BABIP for a pitcher or to high a HR/FB% for a hitter) as a way to mitigate small sample size. I'm ALWAYS looking for the next best FA pickup/streamer while trying to discern if my guys are for real or simply smoke & mirrors. Anyway, that's the process ... and it has worked ... but I'm looking to streamline download time and/or Excel time in making those decisions in 2014.
Thank you!
SAV

“You win pennants in the off season when you build your teams with trades and free agents.” –Earl Weaver
SAV
College Coach
College Coach

User avatar

Posts: 325
(Past Year: 1)
Joined: 11 Nov 2005
Home Cafe: Baseball


Return to Baseball Leftovers

Who is online

Users browsing this forum: No registered users and 8 guests

Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Wednesday, Oct. 22
(All times are EST, weather icons show forecast for game time)

San Francisco at Kansas City
(8:07 pm)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact