Messages

Forum Index : Systems & Strategies : Messages

Author:

[Systems & Strategies] Topic: Sabermetrics Calculations in Excel...Anyone? Excel Guru's?

oilcountry99

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

Location: Ontario

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

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

Location: Ontario

Posted: 2/1/2011 7:29:19 PM

hzjames....friend request sent

quote

MeMyselfandEye

View Space | Friends | Playbook |

Rookie

Joined: Jul 2009

Posts: 691

Location:

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

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

Location: Ontario

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

View Space | Friends | Playbook |

Rookie

Joined: Jul 2009

Posts: 691

Location:

Posted: 2/3/2011 12:10:48 PM

Oil,,accept my friend request and PM me your email address...

quote

B_Capps

View Space | Friends | Playbook |

Prospect

Joined: Apr 2012

Posts: 7

Location:

Posted: 4/22/2012 7:09:04 PM

is this still of interest to you guys?

quote

peter1988

View Space | Friends | Playbook |

Prospect

Joined: Jun 2011

Posts: 383

Location:

Posted: 4/22/2012 10:20:10 PM

interests me.

quote

DegenGamble

View Space | Friends | Playbook |

Rookie

Joined: Oct 2011

Posts: 908

Location: New Jersey

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

View Space | Friends | Playbook |

Prospect

Joined: Apr 2012

Posts: 7

Location:

Posted: 5/7/2012 6:05:20 PM

where is the best place to pull statistics?

quote

DegenGamble

View Space | Friends | Playbook |

Rookie

Joined: Oct 2011

Posts: 908

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

View Space | Friends | Playbook |

Rookie

Joined: Oct 2011

Posts: 908

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

View Space | Friends | Playbook |

Rookie

Joined: Oct 2011

Posts: 908

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

View Space | Friends | Playbook |

Rookie

Joined: Oct 2011

Posts: 908

Location: New Jersey

#13

Posted: 6/1/2012 7:30:16 PM

added Tex / LAA over 8.5

quote

oilcountry99

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

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

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

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

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

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

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

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

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

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

View Space | Friends | Playbook |

Rookie

Joined: Oct 2011

Posts: 908

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

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

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

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

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.

Degen,

I only use Career Pitcher vs. Individual data

I await your PM

quote

oilcountry99

View Space | Friends | Playbook |

Prospect

Joined: Jan 2010

Posts: 384

Location: Ontario

#22

Posted: 6/2/2012 5:10:37 PM