Email:      Pass:    Pass?
Advertisements


Free Excel Forum

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!


Similar Excel Video Tutorials

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
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid

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


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


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


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


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.


OK, I'm trying to reference text from another cell and it's not working. I assume it's either the parentheses or brackets that throwing it of. I am making a number of reports that have 10 formulas in them and would like to use excel to change the formulas when I change the group name. I have a formulas cell and a group 1 and group 2 cell.

Here is my formula...

(Sum ({Stats.H}, {Stats.Opponent})+Sum ({Stats.BB}, {Stats.Opponent}))/(Sum ({Stats.AB}, {Stats.Opponent})+Sum ({Stats.BB}, {Stats.Opponent})+Sum ({Stats.SAC}, {Stats.Opponent}))

In my group2 field I have {Stats.Opponent}

What I would like to do is have my formula like this


(Sum ({Stats.H}, d2)+Sum ({Stats.BB}, d2))/(Sum ({Stats.AB}, d2)+Sum ({Stats.BB}, d2)+Sum ({Stats.SAC}, d2))

Then whenever I change the report to use another group, I only have to change the group2 field and excel would generate the new formula and I'd just copy and paste that into the new report fields.

Thanks for any ideas...

BTW, I tried using search and kept coming up with and error about exceeding the maximum execution time or a blank page...


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 have a line of baseball stats, and I want to have the ability to copy those stats to another page. However, there are formulas for those stats, and I only want the text not the formulas. Is there a way to do that with a macro?

Also, after copying one row of stats, if I refresh the data for another player, how do I copy the new player's stats on the next line of the other other worksheet?


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


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.


Hi. The title might not be clear, but I wasn't quite sure how to describe what I'm trying to accomplish; if it's even possible.

I participate in a fantasy baseball league and we use Excel 2007 to keep track of the statistics. Basically, we select players in 8 statistical categories. The rosters we draft are stored in a sheet named "Rosters" and the official player stats are kept on a separate sheet named "Stats" (ie. BA is Batting Average, HR is Home Runs, etc.).

I've attached a sample XLSX file that shows our current setup. We update the statistics monthly by copying and pasting the actual MLB stats from the CBS Sportsline website into the "Stats" sheet. Then, we'll manually find the BA for Joe Mauer, for example, then transfer that number to his spot on the Rosters sheet. So, in the test.xlsx file, it would require copying cell C2 from the "Stats" and pasting it into the E2 cell in "Rosters." We do that manually which takes about 3-4 hours.

I'm hoping there's a way to make this more of an automatic process. If anyone has any ideas or suggestions, I would greatly appreciate it. Thank you!

test.xlsx

Ted


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


... to find the error in this formula

=IF(LEFT(K9,1)="D",IF(F19="NR","NR",IF(F19="Reline","NR-Reline",IF(F19="Changeover","NA-Change",IF(T24="HP4",Stats!J1,IF(T24="HP2",Stats!J1,IF(T24="RP3",Stats!J1,IF(T24="RP5",Stats!J1,IF(AI12="HP",Stats!J7,IF(AI12="RP",Stats!J10,Stats!J1))))))))),"NA - Field")

Jenn


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.


Hi All

I am putting together a spreadsheet to track stats in our baseball league. I have each players stats on sheet 1. I want to rank the players based on their batting average which is in row I. I would like this ranking to show up on a separate worksheet, sheet 2, ranked in descending order. Pretty simple, but I would like it to re-rank every time I change a players stats on Sheet 1. Basically, I want sheet 2 to update and re-rank every time I make a change on sheet 1.

Seems pretty simple, and there are probably a number of ways to do it, would a macro work?


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 two Excel files. On one, there is a long list of baseball player names. On the other, there are also baseball player names, but there are stats next to the respective names. The goal is to take the information from the second file, and put it next to the names in the first file. So far, I have been searching the name on the second file, and copying the stats to the other file next to the respective name.

How can I automate this tedious and long process?

I have a background in programming with C++, C#, Java, etc. I could probably write this in C# if I really tried, but I have a feeling that's not the best approach, or is it? (I'd have to look up on how to handle Excel files)


I'm in a baseball league that replays games and we keep stats. What I'm trying to do is to have a list of my players with their lifetime stats on one page. I will be adding to this on a yearly basis. I've already used countif to only list the players once on one sheet, but having trouble trying to add their stats for each year. I just need something to lookup, each year they played and then add up all their games they played. I can do the rest once I have one of the stats.

Here is an example starting with column A as the year and moving across:
YEAR NAME GAMES
2005 Alfonzo 120
2006 Alfonzo 45

What I need is something that will add up 120 and 45 onto another sheet, but also let me add 2007 and so on and will contiune to add them up.

Thanks,

Quicksand


Hi-
I had previously received some great advice about how to properly parse my data to get it from the interwebs into the excel.
I now need help with my sorting of the data. The story is this:
I am in a fantasy baseball league where every week, we accumulate our total stats and go to head to head with another team. At the end of the week, in each matchup, whichever team has the higher total gets a point. There are 12 categories, so you could win a weekly matchup 12-0-0 (wins, losses, ties) if you do really well.
The problem that I have with this method of scoring is that you only go up against one opponent, not the entire league each week. So I am trying to get the raw stats for each team for each week and say where they finish in each category (1-10).
I have so far accomplished getting the stats from the internet to the excel, getting each team's weekly totals all in a nice readable order, getting all the stats for week 1 into a nice little box (the blue box on the analysis tab), and I am starting to get the stats sorted to the point that for each category, it spits out the team with the highest value and next highest and all that (the pink box).
The problem that I am having is that I sometimes get duplicate values: in week 1, both the rockford peachz and the vaya con bernabe teams hit 12 homers, so when I do a vlookup on the "large" function, it gives the same team for the second highest homers. Is there a way to get around this? Do I need some insane complicated if function?
I have attached the spreadsheet, all of the real action is in the analysis tab. Also, FYI, we are currently in week 8, so the only stats that I have are before that.
thanks!


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