need help with excel

[trapper700]

hey everyone,
was wondering if anyone can help me to randomize the debut year of players in this database? Someone was kind enough to post instructions how to do it, but I'm no good with excel, and I don't particularly understand what he means.

1. Copy the Master CSV to Excel.
2. Create a column with new Random year using the function Randbetween(FY, LY) with first year about 5 years before your league starting and last year the year you want your last season to run without having fictional players.....
3. Create a column subtracting the random debut year from the actual debut year.
4. Paste the Random Year over the actual debut year using Paste Special - Value.
5. Create a column subtracting the number created in step 3 with the birth year. Paste using the Paste Special - Value over the birth year.

Batting/Pitching and Fielding CSV

1. Copy all these to a Excel workbook.
2. Create a Master CSV that has the Lahmn ID and the value created in step 3 above.
3. Create a column in the batting file that does a lookup on the lahman ID, returning the value created in the master csv step 3.
4. Create a column that subtracts this value from the year of the record. Paste this column into the year column using paste special value.
5. Do this for all files that has the year value in it.
6. Delete the extra columns you created in all imported csvs and export the new tables to csv files. Just make sure to save a copy of the original lahmans csv files in case you have made any errors or in case if you want to create another random debut database in the future.....


I used to have this in an access program using queries and it worked good. The above way is a little more work but you can create a Random Debut table in about 25-30 minutes. On Simcentral I have a random debut database in the OOTP5 section that I create a few years ago using my access application. It does not have any players from 2004-present but it does have the historical players. IF you started in 1901 you would have enough players for 24 teams with an average of 4-5 rounds each year after the initial draft.

The above steps require a intermediate understanding using MS EXCEL and the functions to create the tables, also it requires the Analysis Pack that comes with Excel 2002 and above for some of the functions...(randbetween being one - otherwise you will need to use the random function)

You can download the database I want to use for this here.

If anyone can do it for me, or help walk me through it, I'd greatly appreciate it.

Thanks,
Trapper

 

[trapper700]

bump

 

[theknight571]

Ok...I think I am now is a position to help.... I've imported all of the CSV files into Access, and did the calculations mentioned in your post. (or at least I did them my way )

All you need it a "random" Debut year, and then have the associated records updated accordingly...right?

Do you need to maintain number of years in the league?

for Example: Player 1 (Actual) debuted in 1932 and played for 12 years. If he gets assigned the debut year of 2002, you lose 8 years of data. So do I need to make it so Player 1 debuts no later than his number of years of "service"? In this case he can debut no later than 1993.

 

[trapper700]

Originally posted by: theknight571
Ok...I think I am now is a position to help.... I've imported all of the CSV files into Access, and did the calculations mentioned in your post. (or at least I did them my way )

All you need it a "random" Debut year, and then have the associated records updated accordingly...right?

Do you need to maintain number of years in the league?

for Example: Player 1 (Actual) debuted in 1932 and played for 12 years. If he gets assigned the debut year of 2002, you lose 8 years of data. So do I need to make it so Player 1 debuts no later than his number of years of "service"? In this case he can debut no later than 1993.

ah, well, i think that the way the game works, it doesn't know what the current year is, so as long as it still has his statistical record for that player till 2014 or so, his projected ratings should work out fine. I don't think there would be any need to move his debut till '93 in order to compensate for that.

thanks, btw, i really appreciate this. The main reason i wanted something like this was so that I could start in the deadball era of baseball and play with players during the different periods of baseball.

 

[theknight571]

Ok... lemme see what I can do.

You don't care if their records take you beyond the current season...right?

I'm heading into meetings soon.... but I'll try to get this knocked out once I get home.

 

[theknight571]

Ok... between meetings now...

Is there a date range you want the Debut dates to fall between?

Or should I randomly assign years from 1871 to 2005. (The current range of debut years)

 

[trapper700]

Yeah, it won't matter if the records take you beyond the current season. randomly assigning between the years 1871 to 2005 or 1900 to 2005 would work, thanks.