Messages

Forum Index : Systems & Strategies : Messages Page 1 of 2  1 2  
Author: [Systems & Strategies] Topic: Sabermetrics Calculations in Excel...Anyone? Excel Guru's?
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#1
Posted: 2/1/2011 2:32:11 PM

Does anyone have experience with pulling stats off the internet into Excel through a web query and then setting up the excel file to perform calculations based on the imported statistics.  My main goal would be to calculate Base Runs and Runs Created (sabermetrics)  This would save lots of time instead of having to perform the calculation manually.  I personally don't have that kind of time.  Is this possible at all or am I barking up the wrong tree.

Maybe we can start a brainstorming session for anyone interested.  Certainly I'm not the only one.

Any insight on this topic would be benificial.

Thanks in advance.

 

quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#2
Posted: 2/1/2011 7:29:19 PM
hzjames....friend request sent
quote
MeMyselfandEye send a private message View Space | Friends | Playbook |
MeMyselfandEye
Participation Meter
Rookie
Joined: Jul 2009
Posts: 691
Location:
#3
Posted: 2/3/2011 9:04:35 AM
Oil..I looked into the feasibility of this..basically you need to import the data for every pitcher and every batter they faced - career stats - and the problem I see is it is not a set amount of fields...every time a pitcher faces a new batter, the data imported grows by a row when using a web query...it is easy to get the data in, but manipulating after the point is where it becomes problematic - each new batter pushed the data down a row...

I'm fairly adept at excel, ...I tried to automate this porcess last year, a tough task indeed. Even had my brother (software engineer) start writing a program that would rip the data off of the web and do all this automatically...still in process, but no where near being completed...If my brother has a hard time with it (20+ years experience), then it is a tough call indeed.

Currently, I do a web query for each pitcher - career stats- against every hitter they have ever faced. I have one for each pitcher so I can get the data into Excel quickly. 

I used to wait until 1 hour before game time when the lineups are posted - that way I would be 100% accurate of the hitters facing a certain pitcher - , I ended up cutting and pasting stats into a spreadsheet that contained all the formulas....I could do a game in about 5 minutes apeice using this methods...


quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#4
Posted: 2/3/2011 11:58:33 AM

Thanks so much for the insight MMAE. Unfortunately this looks way more involved than I have time for.  However the cutting and pasting of stats you mention at the end of your post seems more realistic and something I would consider pursuing.  Can you offer any tips on the best way to organize a spreadsheet to accomplish this or a good starting point.  5 minutes a piece, now that's

quote
MeMyselfandEye send a private message View Space | Friends | Playbook |
MeMyselfandEye
Participation Meter
Rookie
Joined: Jul 2009
Posts: 691
Location:
#5
Posted: 2/3/2011 12:10:48 PM
Oil,,accept my friend request and PM me your email address...


quote
B_Capps send a private message View Space | Friends | Playbook |
B_Capps
Participation Meter
Prospect
Joined: Apr 2012
Posts: 7
Location:
#6
Posted: 4/22/2012 7:09:04 PM
is this still of interest to you guys?
quote
peter1988 send a private message View Space | Friends | Playbook |
peter1988
Participation Meter
Prospect
Joined: Jun 2011
Posts: 383
Location:
#7
Posted: 4/22/2012 10:20:10 PM
interests me.
quote
DegenGamble send a private message View Space | Friends | Playbook |
DegenGamble
Participation Meter
Veteran
Joined: Oct 2011
Posts: 1052
Location: New Jersey
#8
Posted: 4/22/2012 10:27:28 PM
I can do it in excel but would like to know how those stats would be used in handicapping - better yet how it would be incorporated in a system?
Posted using a mobile device.
quote
B_Capps send a private message View Space | Friends | Playbook |
B_Capps
Participation Meter
Prospect
Joined: Apr 2012
Posts: 7
Location:
#9
Posted: 5/7/2012 6:05:20 PM
where is the best place to pull statistics?
quote
DegenGamble send a private message View Space | Friends | Playbook |
DegenGamble
Participation Meter
Veteran
Joined: Oct 2011
Posts: 1052
Location: New Jersey
#10
Posted: 6/1/2012 5:27:20 PM
QUOTE

Originally Posted by MeMyselfandEye:

Oil..I looked into the feasibility of this..basically you need to import the data for every pitcher and every batter they faced - career stats - and the problem I see is it is not a set amount of fields...every time a pitcher faces a new batter, the data imported grows by a row when using a web query...it is easy to get the data in, but manipulating after the point is where it becomes problematic - each new batter pushed the data down a row...

I'm fairly adept at excel, ...I tried to automate this porcess last year, a tough task indeed. Even had my brother (software engineer) start writing a program that would rip the data off of the web and do all this automatically...still in process, but no where near being completed...If my brother has a hard time with it (20+ years experience), then it is a tough call indeed.

Currently, I do a web query for each pitcher - career stats- against every hitter they have ever faced. I have one for each pitcher so I can get the data into Excel quickly. 

I used to wait until 1 hour before game time when the lineups are posted - that way I would be 100% accurate of the hitters facing a certain pitcher - , I ended up cutting and pasting stats into a spreadsheet that contained all the formulas....I could do a game in about 5 minutes apeice using this methods...


Actually, it not that difficult and is defintely doable.  How I know?....because I did it.  I have a spreadsheet that automatically pulls the career stats of pitcher vs batter and automatically calculates Runs Created, Base Runs and Extrapolated runs for the lineup for each game.

The problem I have is I'm not sure if i'm calculating Runs Created, Base Runs, and Extrapolated runs PER GAME correctly.  Right now i'm taking the result - dividing by total AB's and multiplying by 3 AB (min AB per game)

The second problem is there are many games where a bunch of batters that never faced that pitcher ever - so not sure what to do about that - except for bypassing the game altogether.

 

quote
DegenGamble send a private message View Space | Friends | Playbook |
DegenGamble
Participation Meter
Veteran
Joined: Oct 2011
Posts: 1052
Location: New Jersey
#11
Posted: 6/1/2012 5:46:36 PM

Oh an if you are interested in today's games - I have leans on the following - 10PM games lineups are not out yet

Phi / Mia O8.5Phi ML

 

Atl / Was O7.5Was ML 

Cin / Hou O8.5

Hou ML 

Min / Cle O9

 

Oak / KC O7.5

 

KC ML

 

Sea ML

 

NYY / Det O8.5

 

quote
DegenGamble send a private message View Space | Friends | Playbook |
DegenGamble
Participation Meter
Veteran
Joined: Oct 2011
Posts: 1052
Location: New Jersey
#12
Posted: 6/1/2012 5:48:02 PM

formatted correctly

Phi / Mia O8.5
Phi ML

Atl / Was O7.5
Was ML

Cin / Hou O8.5
Hou ML

Min / Cle O9

Oak / KC O7.5

KC ML

Sea ML

NYY / Det O8.5

quote
DegenGamble send a private message View Space | Friends | Playbook |
DegenGamble
Participation Meter
Veteran
Joined: Oct 2011
Posts: 1052
Location: New Jersey
#13
Posted: 6/1/2012 7:30:16 PM

added Tex / LAA over 8.5

quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#14
Posted: 6/1/2012 10:16:51 PM
Degen, I know your pretty adept with excel. If I get a chance I'll run some #'s for tomorrows games. I only do first 5 innings so I'm only dealing with the starting pitcher. Are you able to calculate for 1st 5 only? If not theres no sense in me comparing #'s. Shoot me a pm if you want with your formula for base runs and I'll check it with mine
Posted using a mobile device.
quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#15
Posted: 6/1/2012 10:22:09 PM
Degen,
I only use games where there is a minimum of 7 batters in the line up with stats against the opposing pitcher
Posted using a mobile device.
quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#16
Posted: 6/1/2012 10:24:15 PM
I also don't have a large sample size of results. Since this is a little time consuming I only do it sparatically. If it were automated that would be sweet
Posted using a mobile device.
quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#17
Posted: 6/2/2012 11:01:47 AM

June 2 - Play No1

BOS - 6.766

TOR - 5.017

Play on Bos (5innings) -115

First post of the day Based on Base Runs 5 innings.

quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#18
Posted: 6/2/2012 1:59:26 PM

June 2 - Plays 2,3,4

MIA - 3.688

PHI - 1.712

2 - Play on MIA (5inn) +137

LAD - 3.963

COL - 4.210

3- Play on COL (5inn) -142

4 - Play on LAD/COL U5.5 (5inn) +120

 

 

 

 

quote
DegenGamble send a private message View Space | Friends | Playbook |
DegenGamble
Participation Meter
Veteran
Joined: Oct 2011
Posts: 1052
Location: New Jersey
#19
Posted: 6/2/2012 4:56:23 PM

Oilcountry - I stil have a lot of work to to with the spreadsheet.  Right now it automatically pulls in career pitcher vs individual batter data.

I still need to pull in

career pitcher vs team data

season pitcher vs team data

I aslo need to work on my Runs Created / Base Runs formulas.

Right now - I calculate per pitcher vs batter matchup and then add them, but I also need to add the stats for all the individual players on the team - and then run it through the formula.

So I have some work to do - which i'm doing as we speak, but the heavy lifting is done.  All the data is pulled automatically into the spreadsheet - so what's left is the calculations.  I will PM you with more detail

 

quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#20
Posted: 6/2/2012 4:58:44 PM

June 2 - Play 5

CIN - 3.497

HOU - 3.772

5 - Play on HOU (5inn) +111

As I entered my bet on the Under 4, it didn't accept as the line changed to 3.5, therefor no play on the total.

quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#21
Posted: 6/2/2012 5:02:06 PM
QUOTE Originally Posted by DegenGamble:

Oilcountry - I stil have a lot of work to to with the spreadsheet.  Right now it automatically pulls in career pitcher vs individual batter data.

I still need to pull in

career pitcher vs team data

season pitcher vs team data

I aslo need to work on my Runs Created / Base Runs formulas.

Right now - I calculate per pitcher vs batter matchup and then add them, but I also need to add the stats for all the individual players on the team - and then run it through the formula.

So I have some work to do - which i'm doing as we speak, but the heavy lifting is done.  All the data is pulled automatically into the spreadsheet - so what's left is the calculations.  I will PM you with more detail

 

Degen,

I only use Career Pitcher vs. Individual data

I await your PM

quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#22
Posted: 6/2/2012 5:10:37 PM

June 2 - Play 6

ARI - 2.960

SD - 4.646

6 - Play on SD (5inn) -103

quote
DegenGamble send a private message View Space | Friends | Playbook |
DegenGamble
Participation Meter
Veteran
Joined: Oct 2011
Posts: 1052
Location: New Jersey
#23
Posted: 6/2/2012 6:32:41 PM
oilcountry - PM sent
quote
oilcountry99 send a private message View Space | Friends | Playbook |
oilcountry99
Participation Meter
Prospect
Joined: Jan 2010
Posts: 478
Location: Ontario
#24
Posted: 6/2/2012 8:02:47 PM

June 2 - Play 7,8

TEX - 4.486

LAA - 5.053

7 - Play on LAA (5inn) -101

8 - Play on TEX/LAA O3.5 (5inn) -125

That's it for today, lots of plays, missed SEA/CHW CHW would have been a play.

I don't plan on posting plays, this was just for Degen to do a comparison or anyone else out there doing Base Runs.

Peace!

quote
mattymeach31 send a private message View Space | Friends | Playbook |
mattymeach31
Participation Meter
Prospect
Joined: Apr 2012
Posts: 41
Location: New York
#25
Posted: 6/2/2012 8:52:40 PM
Do you have a excel file for this system or are you working on one?
quote
Forum Index : Systems & Strategies : Messages Page 1 of 2  1 2  
You have entered the forum as a GUEST. 
You must login/register to post or reply.