Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Trying To Access Tables And Stats From Baseball Reference

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I currently have an Excel worksheet set up to take real-life baseball stats and convert them to ratings for MLB 10: The Show. Right now, I have to manually insert all of the stats I need by "hand" into the spreadsheet. However, I would like to change the way I insert stats. I want to have Excel read the name I type in, search the name on Baseball-Reference, and then pull the specific tables I need from the site and import them onto my spreadsheet. Anyway I can do this? I have Excel 2007. Thanks a bunch!

View Answers     

Similar Excel Tutorials

Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...
Completely Hide the Quick Access Toolbar in Excel
I'll show you how to completely remove the entire Quick Access Toolbar from Excel. This will allow you to have a s ...
Get the Name of a Worksheet in Macros VBA in Excel
How to get the name of a worksheet in Excel using VBA and Macros and also how to store that name in a variable for ...
Absolute and Relative Cell References in Excel
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...

Helpful Excel Macros

Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Hide Specific Comments in Excel - Comments Will Still Display on Hover
- Hide specific comments in Excel with this macro. Comments are still visible on hovering over the cell that contains the
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a

Similar Topics







I downloaded a MS Excel spreadsheet for keeping track of our 8 year old Baseball Team's game and individual stats.

I question is how do I make a seperate area in the sheet that automatically keeps a running total of each kids, batting average, hits, runs, etc.?

I have the spreadsheet, but don't know how to attach it to this for anyone to see.

Any help would be appreciated.

Thanks!

Keith


I coach a baseball team, and want to create a "sortable stats" page in excel so everyone can see where they stand in each statistical category. I have tabs for each games' stats - they are pulled into a cumulative tab where all the stats are totaled.

Basically, i want to do a vlookup and auto-sort based on each category on a single page.

So the formula would have to pull the stat leader in each category and grab the players' names in descending order

The following is a rough representation of how all the stats are set up. What formula should i use to sort runs (and then replicate for each additional category)? Thanks!!!! (columns A-D, Rows 1-4)

.....A.........B.........C..........D
1 Name...Runs

2 Smith.....5
3 Jones.....6
4 Burns.....9
5 Steel......2


So thanks to a user on this forum, I was able to develop code to pull data that was always on the same sheet and in the same cell from about 100 workbooks. It takes about 30 seconds per workbook (about an hour total) to get 98 values from each. I was initially using 2007 but now use 2003.
Is this as good as it can get? Suggestions for speeding it up? i tried to deactivate the screen refreshing, but I always got an error or it would freeze while running the code.

A bit of background on what the code does just in case: Each Client of my company has its own spreadsheet where cases (like a law firm) are entered to keep track of cases opened, closed, duration, etc. Each column tracks a different stat and there are 7 rows (25-31) for 7 years over which I am tracking data. The Master spreadsheet has this macro attached to it so that it can pull all the stats from all of our clients to combine them all together. Currently we have about 100 clients.



Sub PullStats()
Dim z As Long, e As Long
Dim f As String
d = 2
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
On Error Resume Next
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
If Cells(e, 1) ActiveWorkbook.Name Then
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C25"
Cells(e, 2) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C26"
Cells(e, 3) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C27"
Cells(e, 4) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C28"
Cells(e, 5) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C29"
Cells(e, 6) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C30"
Cells(e, 7) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C31"
Cells(e, 8) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D25"
Cells(e, 9) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D26"
Cells(e, 10) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D27"
Cells(e, 11) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D28"
Cells(e, 12) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D29"
Cells(e, 13) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D30"
Cells(e, 14) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D31"
Cells(e, 15) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N25"
Cells(e, 16) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N26"
Cells(e, 17) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N27"
Cells(e, 18) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N28"
Cells(e, 19) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N29"
Cells(e, 20) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N30"
Cells(e, 21) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N31"
Cells(e, 22) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F25"
Cells(e, 23) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F26"
Cells(e, 24) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F27"
Cells(e, 25) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F28"
Cells(e, 26) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F29"
Cells(e, 27) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F30"
Cells(e, 28) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F31"
Cells(e, 29) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G25"
Cells(e, 30) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G26"
Cells(e, 31) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G27"
Cells(e, 32) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G28"
Cells(e, 33) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G29"
Cells(e, 34) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G30"
Cells(e, 35) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G31"
Cells(e, 36) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P25"
Cells(e, 37) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P26"
Cells(e, 38) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P27"
Cells(e, 39) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P28"
Cells(e, 40) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P29"
Cells(e, 41) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P30"
Cells(e, 42) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P31"
Cells(e, 43) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M25"
Cells(e, 44) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M26"
Cells(e, 45) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M27"
Cells(e, 46) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M28"
Cells(e, 47) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M29"
Cells(e, 48) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M30"
Cells(e, 49) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M31"
Cells(e, 50) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O25"
Cells(e, 51) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O26"
Cells(e, 52) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O27"
Cells(e, 53) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O28"
Cells(e, 54) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O29"
Cells(e, 55) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O30"
Cells(e, 56) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O31"
Cells(e, 57) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I25"
Cells(e, 58) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I26"
Cells(e, 59) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I27"
Cells(e, 60) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I28"
Cells(e, 61) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I29"
Cells(e, 62) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I30"
Cells(e, 63) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I31"
Cells(e, 64) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J25"
Cells(e, 65) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J26"
Cells(e, 66) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J27"
Cells(e, 67) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J28"
Cells(e, 68) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J29"
Cells(e, 69) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J30"
Cells(e, 70) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J31"
Cells(e, 71) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K25"
Cells(e, 72) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K26"
Cells(e, 73) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K27"
Cells(e, 74) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K28"
Cells(e, 75) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K29"
Cells(e, 76) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K30"
Cells(e, 77) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K31"
Cells(e, 78) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L25"
Cells(e, 79) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L26"
Cells(e, 80) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L27"
Cells(e, 81) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L28"
Cells(e, 82) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L29"
Cells(e, 83) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L30"
Cells(e, 84) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L31"
Cells(e, 85) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q25"
Cells(e, 86) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q26"
Cells(e, 87) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q27"
Cells(e, 88) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q28"
Cells(e, 89) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q29"
Cells(e, 90) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q30"
Cells(e, 91) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q31"
Cells(e, 92) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R25"
Cells(e, 93) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R26"
Cells(e, 94) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R27"
Cells(e, 95) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R28"
Cells(e, 96) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R29"
Cells(e, 97) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R30"
Cells(e, 98) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R31"
Cells(e, 99) = Cells(1, 98)
End If
Next e
MsgBox "Compilation is complete."
End Sub


im trying to do baseball stats in excel :
1B 2B 3B HR BB K
Players Name 1B 1B K PO GO
Players Name K PO K PO GO
Players Name PO K K PO GO
Players Name GO 1B K PO GO
Players Name PO GO K PO GO
Players Name K PO K PO GO
Players Name K 1B K PO GO
Players Name PO 1B K PO GO
Players Name GO 1B K PO GO

what im trying to do, is to have the stats compute for me for each player so for the 1st person it would show me he had 2 (1B) , 1 (K) , 1 (PO) , and 1(GO) then for the next player , it would show 2 (K) , 2 (PO) , and 1 (GO) , and so forth, and when i changed the stats around it would change with in the boxscore as well


Hey guys I am new here, but I need some help with VBA.

I'm not very good at VBA, but I'm trying to learn it on the go.

I've created an excel spreadsheet with players projections. I took MLB players stats from the past 2 years and ive created a formula to come up with the projections. (I'm a baseball geek, and I play fantasy baseball, so im trying to create this for fun.)

Now, I've created a button to begin, then the user follows a couple message prompts. Then Once the user selects a player, the players projected stats should appear.

This is the problem, I cannot get this to work at all. I don't know how to get VBA to to search a sheet for a player that someone selected, and output its stats on a message box.

Can anyone help me?? I'd greatly appreciated it. Thanks everyone in advance.


I have a worksheet that pulls stats from baseball reference, and on one .iqy file, it takes tables 1,2 and 12 from the players 2010 splits. However, that page doesn't have fielding, so I need to create another .iqy that takes stats from the fielding page. How do I open up the second .iqy file and add it to the other I already have?


I'm keeping track of stats for my local baseball team. I use this formula to search the input tab for cumulative stats.

{=IF($Y$3="Both",SUMIF('Game Data'!$B$3:$B$193,$B5,'Game Data'!D$3:D$193),SUM(IF('Game Data'!$B$3:$B$193=$B5,IF('Game Data'!$X$3:$X$193=$Y$3,'Game Data'!D$3:D$193,0),0)))}

Y3 is a combo box that tallys between Both, Regular Season, and Playoffs
Game Data B represents the player name
B5 is the player name on the stats tab
Game Data D is the particular stat
Game Data X designates if the entered stats are during the regular season or playoffs

QUESTION 1:Can i change the search range (currently 3:193) to include the entire column? The below formula does not work when I change Y3 to playoffs or regular season

={IF($Y$3="Both",SUMIF('Game Data'!B:B,'Season Stats'!$B4,'Game Data'!D:D),SUM(IF('Game Data'!B:B='Season Stats'!$B4,IF('Game Data'!X:X='Season Stats'!$Y$3,'Game Data'!D:D,0),0)))}

QUESTION 2: Is there a way I could add additional Combo Boxes to search by Playoffs/Regular/Both AND Year AND Season.

Oh, just building an Access database isn't an available option.

THANKS!


Hello Helpful People,

I want to combine several years worth of baseball stat sheets for analyis. Stat sheets are done by year, so I can get an Excel sheet with 2009 stats for every player, another with the 2008 stats, and so on. I want this all in one sheet with all the data kept separate, and it would look something like this: Column A is the player's name. Column B is the player's position. Columns C through H would be 2009 stats in various categories. Columns I through N would be the same categories but for 2008, and so on.

The problem for me is that each year there are different players playing baseball. There's a lot of overlap, but it isn't anywhere near perfect. I'd want blanks to appear in cells that correspond to stats from years a player did not play.

Also, I've found a site that assigns unique codes to each player, which avoids the problem of having multiple players with the same name.

Is there any way to do what I'm trying to do? I'm fairly handy with a lot of Excel functions, but this one has me stumped.

Thanks for any help you provide.


I have an excel spreadsheet with with 17 numeric columns that represent baseball stats. I would like to multiple all of the data in each of those columns and rows (20) by 1.434. Rather than go in and take each individual # in each column and manually key in =#*1.434 I would like to know how to globally apply that formula across the spread sheet. Manually keying would take me 45-60 minutes per sheet.

PURPOSE OF FORMULA: I am attempting to project baseball stats from the 1994 strike season for each team and each player over the 162 games that should have been played. Since each team played a uneven amount of games I am applying different amounts in the formula to bring them close to 162. I can send the spreadsheet to anyone who is interested if they'd like to tinker with it. I think I uploaded it here as well94Chisox.xls




I've searched here (and elsewhere) for something that will take baseball stat inputs and return an ordered list based on ordinal criteria (i.e. return max OBP unless also max OPS, then return 2nd best OBP). I found a website that will make a lineup, but does not have adjustable criteria and their criteria don't match mine. I did find one spreadsheet here, but I couldn't modify it (don't know enough Excel to do so), and it is not scalable.

So, I am looking for a way to generate a lineup based on these stats: On Base % (OBP), Slugging (SLG), and OPS (OBP+SLG) and Batting Avg (BA)

The rules I need to follow:
#1 = best OBP unless is also best OPS or best SLG, then 2nd best OBP
#2 = best OPS
#3 = 2nd best OBP unless used for #1, then 3rd best
#4 = best SLG unless same as #2, then 2nd best SLG

Obviously, none can repeat, but would need to repeat the ordering for 20>n>7 players. Also, there will likely be duplicate numbers for each stat, so BA becomes the tiebreaker.

I'd attach what I have, but I have nothing at this point, sorry. Any help would be appreciated! I hope I've been specific enough and I put this in the right place?

Ray

Hello and thanks for taking the time to read this request for help. I've been trying to familiarize myself with MS Excel's Web Query function (MS Excel 2007) in order to collect statistics from a baseball game.

Here is this particular page I'd like to start pulling certain data from: http://www.mmobaseball.com/stats.aspx

I can get the first page of data easily, the first 50 or so players and their stats, but I cannot get anything from the other pages (which are accessed by the 'next button') nor can I get the defensive stats of any player (accessed by clicking the button labeled "defensive stats"). When I look at the page source I see that regardless of the page I'm viewing, excel only pulls data from the original page.

How can I collect the data from these other pages? Is there more information needed to assist me? Thanks in advance


I am using Excel to analyze several columns of baseball stats. I want to be able to sort through the list and find out how many particluar players from a particular team achieved something in the same year.

My data is in columns such as player, team, year, HR, Hits, RBI, etc......

For example, I want to sort through the data and count the number of Cub players who hit 20 homers in the same season. How do I get the COUNTIF function to do this? I've tried using COUNTIF in formulas with IF and AND statements but get incorrect results.

Thanks,

madmanmac


Hello and thanks for taking the time to read this request for help. I've been trying to familiarize myself with MS Excel's Web Query function (MS Excel 2007) in order to collect statistics from a baseball game.

Here is this particular page I'd like to start pulling certain data from: http://www.mmobaseball.com/stats.aspx

I can get the first page of data easily, the first 50 or so players and their stats, but I cannot get anything from the other pages (which are accessed by the 'next button') nor can I get the defensive stats of any player (accessed by clicking the button labeled "defensive stats"). When I look at the page source I see that regardless of the page I'm viewing, excel only pulls data from the original page.

How can I collect the data from these other pages? Is there more information needed to assist me? Thanks in advance

I've cross posted this same question on another board. http://www.mrexcel.com/forum/showthr...46#post2164946, there has been no answer yet but I will update if there is.


I went through the topics, and nobody has asked my exact question, so I gotta post a new thread.

I am my team's statistician, and as such, I have people asking for the goofball stuff OBP, SLG, and OPS.

I'm using =IF's to cover the #DIV/0 error, and have the cells formatted as #.000 to cover the way those stats are shown. However, I'm noticing numbers errors due to rounding up or down.

ie: Guy has a .714 OPB, and a .714 SLG making his OPS 1.428 however, 10/14 = .71428 so .71428+.71428 = 1.42852 thus 1.429 is showing up in the cell.

What sort of formula should I use to make my OPS add ONLY the first three digits of the previous two stats. I want excel to add .714 + .714 and that's it.


I have many spreadsheets with sports stats. I pulled the information from the web. So on one page I have the stats. The next page I want to be able to type the teams name and have excel pull out all the stats I want. Makes it easy to compare teams stats that are playing each other. Is this possible I tried to use most of the lookup functions but I just cant figure this out.


I will attach the sheets.


Sorry for the bad thread title but I'm not even sure what formula I would use to do this. I'm working with some baseball stats and I have a spreadsheet with all the players, what team they play for, and their various stats.

I would like to have a formula that allows me to input a team, and return all the players who play for that team along with their stats. Is this easy?




I have spent much of the day learning the multiple ways of importing tables from webpages to Excel, but I am having trouble with the following task:

I want to import the lineups for every game played by every MLB team from 1990-2013. The best source of this data seems to be baseball-reference.com. Here it the format for the URL for the Yankees in 2012:

http://www.baseball-reference.com/te...-lineups.shtml

It seems to import pretty well manually, and I usually delete everything above Game 1 and after Game 162. I suppose I would need a script that runs 2 loops, one for the 30 teams and one for the years, with each team being a separate sheet. Can someone help me with this? Thank you.

Hey all.
First time poster and very much a newbee at excel.........I'll add a few pitchers of current sheet below..

So first of all i'm creating an up-to-date baseball spreadsheet, that gives pitcher + team mathups and reference to stats such as road/home, day/night, Vs Right/Left handed Pitcher etc.....

so far i have all the stats in worksheets and have created a couple of drop downlists through data validation of all pitchers available and all teams available, these drop down lists are matched to the stats by way of

e.g In C4 there is drop down list of teams i can choose (Seattle in example)

in D5 to give me wins for Seattle
=VLOOKUP($C$4,Standings,MATCH(Wins,Standings_cols,0),0)

Then in B17 drop down list of all possible pitchers (A COOK in example)

In B18 to give me # games that A COOK has pitched
=VLOOKUP($B$17,Pitchers_All_All,MATCH(Games,Pitchers_All_All_cols,0),0)

This works fine but i have to go through the trouble of manually selecting which team is playing and which pitcher is pitching.

Ideally i would like to have A main drop downlist from web querry data from
http://www.statfox.com/mlb/gamematchup.asp
at the top of the spreadsheet that has the current teams playing for the current day and ultimately changes the names in C4,C5 and B17,B18.
i.e
DETROIT at ARIZONA
LA DODGERS at LA ANGELS

Then for this drop down list to connect with C4 and choose the particular team that is selected i.e choose Detroit, with c5 being chosen as ARIZONA

Also i want the Pitchers to be taken from the web query as i change the Teams PLaying. So if i select "DETROIT at ARIZONA" from list then in the Pitchers box in B17 it would automatically choose J BONDERMAN likewise choosse D HAREN in B18.


Is this at all possible?
Any help would be greatly appreciated. I would also like to add the day/ night variable from a drop down list that will change the stats shown, but baby steps for now.


Excel 07, Vista home

Thanks for taking the time




I posted yesterday and people here were really helpful, so I will try again!

I am working on a backend database for a TV graphics package for live baseball coverage. My current sticking point is getting the players to match up with their stats. Before each game, I go online to the team's websites and copy/paste their statistics and personal info into my database. The problem is, they store the personal info (height/weight, etc) in a different table from their batting stats and there's a third for pitching stats.

I need to get a single table with all the stats for a single player on one line (this seems reasonable, right?). But for some reason, my vlookup's either give me #N/A for the players it can't find, or all 0's when I try to add in a isna() into the mix. I've tried a bunch of different ways, and I use a LOT of vlookup's in this workbook, so I (thought) I understand how to do it!

I think part of my problem might be that I'm doing a vlookup on the player NAME as the lookup value (so a text string instead of a number), but I'm not sure. I have double checked that the names do match.

I have attached the two relevant worksheet's from my workbook. Thanks for looking!

TeamConcatenate.xlsx

Hi!

I'm part of a Simulation Football League and we are using Excel to record our stats.

I created the basic workbook, but I still need some help.

I have attached the file to this post.


My problem
You will see that in the column "Player" Where it list the "Player Names" it will say "Tom Brady" in Week 1.

How do I fix the workbook so that no matter what name I put the "Player" Column of Week 1,2, 3, 4,...etc. he will automatically show up in the Season Stats worksheet? Which is also attached. Same goes for the "Position" How do I do that?

Also, I want the Season Stats worksheet to get all accumulated Stats from Week 1, 2, 3, 4...etc. How can that be done? But each week will be separate week for the stats of that week. But Season Stats will combine all of them with the averages.

I don't know how to do it. So I attached the workbook and hopefully someone who is really skilled with Excel can do it.

The Season Stats worksheet is at the end.

Thanks in advance!


What I'm trying to do is create a stats spreadsheet for my Beersbee league. I made the "single game" stats table rather easily but I'm having trouble with sending the data from a the single game to another table that will be called "season stats".

I want to be able to add all of the numerical stats (poles, bottles, catches, etc.) to the to the season stats table. But also there are some percentage stats (Hit%, Catch%, etc.) that need to be averaged with the season percentages at the conclusion of each game.

I have a button called "new game" that is linked to a macro that deletes all of the data from the previous game and it seems like everytime I click this it will delete all the data from season stats!

Please Help! This would be awesome to have this going for the summer!

Thanks


Hey guys,

I figured this would be best place to ask this question. I was wondering what is the best way to go about automatically tracking team stats for this game I play on Excel. I use a separate program for rolling the dice so I just put the outcome on Excel, but I have to manually collect the stats each game. The game rules are as follow http://geocities.com/dicebaseball2k/rules

Any advice tips? I have been looking to the vlookup functions but I don't know how to truly tackle my project.




I'm trying to set up a worksheet to compile stats leaders from several different worksheets (baseball statistics of players from various teams). The catch is that in order to qualify for a league leader in a average category you need to have 3.1 plate appearances per team game played. For example: Player A has 24 plate appearances in 6 team games for a 4.0 plate appearance average. Hence, Player A would qualify for the league leader in average, slugging percentage, etc...Player B has 15 plate appearances in 6 team games for a 2.5 plate appearance average....therefore, Player B would not qualify for the league leaders in the previously mentioned stats. I would like to set up the worksheet to collect the qualified players and transfer their name and average which are located in two different columns per team worksheet. There are 30 team worksheets so data would be compiled from all 30 sheets...Help please!

Good day guys,

As we know, baseball season is starting, and everyone loves stats! Only thing is now we care more about singles (beers drank) than actual singles. =D

I'm in the process of doing up, as close to possible, score sheet (I downloaded a template off of the MS Office template site), which seems to be doing alright.

Only problem I'm encountering is the diamond pattern (for marking bases). Is there a way I can put in, for instance, a "2" and have the Diamond pattern show up, with the lines drawn to second (this would signal a double, two beer that night).

That would make the math part of this so much simpler, as we have running tabs and such.
If need be, I could attach my "work in progress" spreadsheet to have a look at, as I may be going about this in a whole wrong way.

Thanks,
Bobbo


Hello everyone,
First time poster, long time visitor. I'm wondering if someone can help me with an excel spreadsheet that contains statistical information for hockey players. We run a tournament in the summer with about 18 teams and each team has 23 players. We have a "top 15" player list and I want to create individual team "spreadsheets" that have all the players' stats. When I update the stats manually, I want our leaderboards to automatically change for me.

Afterwards, I would like to embed the tables into our web site using HTML snippets, which I create by just saving the excel spreadsheet as a webpage and inserting the code snippet. However, I would like users looking at the standings and stats of the individual teams to be able to click the column name which automatically sorts the column. This is helpful for someone looking at a team roster with the stats and would like to see who has the most goals, or assists, etc...

For an example, see http://www.nhl.com/ice/playerstats.h...=nav-sts-indiv and I want it to look exactly like that. Any help on this situation will be greatly appreciated and possibly even compensated if need be. Thanks ahead of time guys! Good luck!!