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.
0
To remove first post, remove entire topic.
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.
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...
0
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...
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
0
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
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.
0
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.
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
0
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
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
0
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
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
0
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
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
0
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
Hot Forum Topics
