Sabermetrics Calculations in Excel...Anyone? Excel Guru's?

Forum: Systems & Strategies Page 1 of 2  1 2  
Author: [Systems & Strategies] Topic: Sabermetrics Calculations in Excel...Anyone? Excel Guru's?
oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
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.

 

oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
Posted: 2/1/2011 7:29:19 PM
hzjames....friend request sent
MeMyselfandEye PM MeMyselfandEye
Joined: Jul 2009
Posts: 691
 
MeMyselfandEye
Participation Meter
Rookie
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...


oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
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

MeMyselfandEye PM MeMyselfandEye
Joined: Jul 2009
Posts: 691
 
MeMyselfandEye
Participation Meter
Rookie
Posted: 2/3/2011 12:10:48 PM
Oil,,accept my friend request and PM me your email address...


B_Capps PM B_Capps
Joined: Apr 2012
Posts: 7
 
B_Capps
Participation Meter
Prospect
Posted: 4/22/2012 7:09:04 PM
is this still of interest to you guys?
peter1988 PM peter1988
Joined: Jun 2011
Posts: 384
 
peter1988
Participation Meter
Prospect
Posted: 4/22/2012 10:20:10 PM
interests me.
DegenGamble PM DegenGamble
Joined: Oct 2011
Posts: 1070
 
DegenGamble
Participation Meter
Veteran
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.
B_Capps PM B_Capps
Joined: Apr 2012
Posts: 7
 
B_Capps
Participation Meter
Prospect
Posted: 5/7/2012 6:05:20 PM
where is the best place to pull statistics?
DegenGamble PM DegenGamble
Joined: Oct 2011
Posts: 1070
 
DegenGamble
Participation Meter
Veteran
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.

 

DegenGamble PM DegenGamble
Joined: Oct 2011
Posts: 1070
 
DegenGamble
Participation Meter
Veteran
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

 

DegenGamble PM DegenGamble
Joined: Oct 2011
Posts: 1070
 
DegenGamble
Participation Meter
Veteran
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

DegenGamble PM DegenGamble
Joined: Oct 2011
Posts: 1070
 
DegenGamble
Participation Meter
Veteran
Posted: 6/1/2012 7:30:16 PM

added Tex / LAA over 8.5

oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
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.
oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
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.
oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
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.
oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
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.

oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
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

 

 

 

 

DegenGamble PM DegenGamble
Joined: Oct 2011
Posts: 1070
 
DegenGamble
Participation Meter
Veteran
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

 

oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
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.

oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
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

oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
Posted: 6/2/2012 5:10:37 PM

June 2 - Play 6

ARI - 2.960

SD - 4.646

6 - Play on SD (5inn) -103

DegenGamble PM DegenGamble
Joined: Oct 2011
Posts: 1070
 
DegenGamble
Participation Meter
Veteran
Posted: 6/2/2012 6:32:41 PM
oilcountry - PM sent
oilcountry99 PM oilcountry99
Joined: Jan 2010
Posts: 799
 
oilcountry99
Participation Meter
Rookie
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!

mattymeach31 PM mattymeach31
Joined: Apr 2012
Posts: 41
 
mattymeach31
Participation Meter
Prospect
Posted: 6/2/2012 8:52:40 PM
Do you have a excel file for this system or are you working on one?
Forum: Systems & Strategies Page 1 of 2  1 2  
You have entered the forum as a GUEST. 
You must login/register to post or reply.