Replay One Season

dhill · 04-07-2007, 05:40 AM

I posted a while back about replaying one season using the stats for that season in order to simulate a table top baseball game. That thread was hijacked to the ticket forum. I was asked what I did. If you do not know basic excel, this may not make sense. This is what I did:

Open the batting.cvs in a text editor that can read large files like EditPadLite. Excel will not read this file due to the number of rows.
I wanted to replay 1980, so I deleted all players from the begining through 1955, about 25 years before the 1980 season. I actually looked at all the players from 1980 to determine which one had the earliest start date. It turned out to be 1956. This eliminated over 33k records and allowed me to read the file in Excel.

Open the batting.csv file in Excel. Copy all of the 1980 stats into another worksheet. Sort the 1980 data by Column A (ascending) and Column C (descending). This will place the last team that the player played with first.

Create a vlookup formula for columns C through V (or the last column of the worksheet), looking up the value in Col A in the 1980 data worksheet, and replacing with the corresponding column, replace Column C with the lookup value in column 3, D with 4, etc. So for columm C the formula would be =Vlookup(a1,1980Stats!$a$1:$v$??,3)

Delete all rows with #N/A values. These palyers did not play in 1980. Delete the 1980 worksheet. Save the file back as an CSV file.

Repeat the above for pitching.csv, fielding.csv, and fieldingOF.csv. If you know excel, this should not take long.

You will then have a database of 1980 players with their 1980 stats repeated for each of the years that they played. When you replay the season, the players will play closer to their 1980 abilities.

I left injuries on, which might account for the swings in some teams.

OOTP 1980
Eastern Division
New York Yankees 111 51
Boston Red Sox 87 75
Baltimore Orioles 82 80
Cleveland Indians 82 80
Milwaukee Brewers 70 92
Toronto Blue Jays 70 92
Detroit Tigers 65 97

Western Division
Kansas City Royals 99 63
California Angels 89 73
Texas Rangers 82 80
Minnesota Twins 80 82
Oakland Athletics 77 85
Chicago White Sox 70 92
Seattle Mariners 70 92

National Standings
Eastern Division
Montreal Expos 103 59
Philadelphia Phillies 97 65
St. Louis Cardinals 92 70
Pittsburgh Pirates 85 77
New York Mets 78 84
Chicago Cubs 66 96

Western Division
Los Angeles Dodgers 92 70
Houston Astros 83 79
San Diego Padres 78 84
Cincinnati Reds 76 86
San Francisco Giants 71 91
Atlanta Braves 51 111

Real 1980 Standings
AMERICAN LEAGUE
Eastern Division
NY Yankees 103-59
Baltimore 100-62
Milwaukee 86-76
Detroit 84-78
Boston 83-77
Cleveland 79-81
Toronto 67-95

Western Division
Kansas City 97-65
Oakland 83-79
Minnesota 77-84
Texas 76-85
Chi White Sox 70-90
California 65-95
Seattle 59-103

American League Championship
Kansas City-NY Yankees 3-0 WWw

NATIONAL LEAGUE
Eastern Division
Philadelphia 91-71
Montreal 90-72
Pittsburgh 83-79
St Louis 74-88
NY Mets 67-95
Chi Cubs 64-98

Western Division
Houston 93-70
LA Dodgers 92-71
Cincinnati 89-73
Atlanta 81-80
San Francisco 75-86
San Diego 73-89

National League Championship
Philadelphia-Houston 3-2 WlLww

pstrickert · 04-09-2007, 09:51 PM

I'm not too familiar with Excel, so forgive me if this is a stupid question. Does the vlookup formula have to be inserted for every record in the database? Here's what I mean . . .

If "a1" in . . .

a1,1980Stats!$a$1:$v$??,3

refers to the first record, then does "a1" have to be replaced with "a2", "a3", "a4", etc., on each successive line, in order to account for every player? Otherwise, the formula only works for the player whose name appears in "a1", right?

Thanks.

pstrickert · 04-10-2007, 07:48 PM

Is there anyone else with Excel experience out there? Could you perhaps answer my question? I understand everything about dhill's instructions, except for the "a1" part of the formula. How do I make it work for all records in column A? I'm sure I'm missing something obvious, but like I said I rarely use Excel. (I have the 2007 version of Excel, BTW.) Thanks for your help.

04-07-2007, 05:40 AM	#1
dhill Bat Boy Join Date: Jul 2006 Posts: 6	Replay One Season I posted a while back about replaying one season using the stats for that season in order to simulate a table top baseball game. That thread was hijacked to the ticket forum. I was asked what I did. If you do not know basic excel, this may not make sense. This is what I did: Open the batting.cvs in a text editor that can read large files like EditPadLite. Excel will not read this file due to the number of rows. I wanted to replay 1980, so I deleted all players from the begining through 1955, about 25 years before the 1980 season. I actually looked at all the players from 1980 to determine which one had the earliest start date. It turned out to be 1956. This eliminated over 33k records and allowed me to read the file in Excel. Open the batting.csv file in Excel. Copy all of the 1980 stats into another worksheet. Sort the 1980 data by Column A (ascending) and Column C (descending). This will place the last team that the player played with first. Create a vlookup formula for columns C through V (or the last column of the worksheet), looking up the value in Col A in the 1980 data worksheet, and replacing with the corresponding column, replace Column C with the lookup value in column 3, D with 4, etc. So for columm C the formula would be =Vlookup(a1,1980Stats!$a$1:$v$??,3) Delete all rows with #N/A values. These palyers did not play in 1980. Delete the 1980 worksheet. Save the file back as an CSV file. Repeat the above for pitching.csv, fielding.csv, and fieldingOF.csv. If you know excel, this should not take long. You will then have a database of 1980 players with their 1980 stats repeated for each of the years that they played. When you replay the season, the players will play closer to their 1980 abilities. I left injuries on, which might account for the swings in some teams. OOTP 1980 Eastern Division New York Yankees 111 51 Boston Red Sox 87 75 Baltimore Orioles 82 80 Cleveland Indians 82 80 Milwaukee Brewers 70 92 Toronto Blue Jays 70 92 Detroit Tigers 65 97 Western Division Kansas City Royals 99 63 California Angels 89 73 Texas Rangers 82 80 Minnesota Twins 80 82 Oakland Athletics 77 85 Chicago White Sox 70 92 Seattle Mariners 70 92 National Standings Eastern Division Montreal Expos 103 59 Philadelphia Phillies 97 65 St. Louis Cardinals 92 70 Pittsburgh Pirates 85 77 New York Mets 78 84 Chicago Cubs 66 96 Western Division Los Angeles Dodgers 92 70 Houston Astros 83 79 San Diego Padres 78 84 Cincinnati Reds 76 86 San Francisco Giants 71 91 Atlanta Braves 51 111 Real 1980 Standings AMERICAN LEAGUE Eastern Division NY Yankees 103-59 Baltimore 100-62 Milwaukee 86-76 Detroit 84-78 Boston 83-77 Cleveland 79-81 Toronto 67-95 Western Division Kansas City 97-65 Oakland 83-79 Minnesota 77-84 Texas 76-85 Chi White Sox 70-90 California 65-95 Seattle 59-103 American League Championship Kansas City-NY Yankees 3-0 WWw NATIONAL LEAGUE Eastern Division Philadelphia 91-71 Montreal 90-72 Pittsburgh 83-79 St Louis 74-88 NY Mets 67-95 Chi Cubs 64-98 Western Division Houston 93-70 LA Dodgers 92-71 Cincinnati 89-73 Atlanta 81-80 San Francisco 75-86 San Diego 73-89 National League Championship Philadelphia-Houston 3-2 WlLww

04-09-2007, 09:51 PM	#2
pstrickert Hall Of Famer Join Date: Dec 2005 Posts: 16,323	I'm not too familiar with Excel, so forgive me if this is a stupid question. Does the vlookup formula have to be inserted for every record in the database? Here's what I mean . . . If "a1" in . . . a1,1980Stats!$a$1:$v$??,3 refers to the first record, then does "a1" have to be replaced with "a2", "a3", "a4", etc., on each successive line, in order to account for every player? Otherwise, the formula only works for the player whose name appears in "a1", right? Thanks.

04-10-2007, 07:48 PM	#3
pstrickert Hall Of Famer Join Date: Dec 2005 Posts: 16,323	Is there anyone else with Excel experience out there? Could you perhaps answer my question? I understand everything about dhill's instructions, except for the "a1" part of the formula. How do I make it work for all records in column A? I'm sure I'm missing something obvious, but like I said I rarely use Excel. (I have the 2007 version of Excel, BTW.) Thanks for your help.