OOTP5 Archive!

[kq76]

For a history of the updates, please see the following post.

For the latest news, please see the v5.44 & v6.00 release post.

Introducing OOTP Archive v5.44 & v6.00!

In the early days of my OOTP experience, I created a small excel workbook to track some of the stats for my solo OOTP league. As time progressed I added more and more to it and after awhile I decided I should try to make it useful and available to others. And so, after much tweaking and optimizing I am finally ready to offer it to the OOTP community.

"So what exactly does it do? Why would I want to use it?", you ask.

Basically, it is an excel workbook that uses CommishJoe's Exporter CSVs to create a statistical history of a league. It is like Cato's html history, except that I believe it is a more comprehensive stat recorder and analyzer.

It keeps track of totals and averages of statistics and ratings for players, teams, and the league on a yearly and all-time basis, as well as player contracts and team finances.

"Great! So where do I get it and how do I use it?", you say.

You can download the 100KB (approx.) zip file at the following sites:

http://members.shaw.ca/kq76/ootp/
http://www.tresclub.com/downloads/downloads.htm (possibly updated)
http://www.ootpdownloadcenter.com/ (possibly updated)

If you cannot download it, then just email me and I will email it back to you.

When you open the excel file, it will ask if you want to update links, click "No" or "Continue". It might also ask if you want to "Enable Macros". You need to for it to work. I promise they are safe, but if you want to make sure, just run it through a virus scanner. From there, just follow the short "directions" on the "home" sheet and the workbook will do the rest. After that, you peruse the sheets for whatever you may be interested in.

If you have any questions, or comments, please let me know. I can be contacted by PM.

Thank you and enjoy,
Kier

P.S. I would like to give a special thank you to all the previous utility makers for enhancing my OOTP experience.

[kq76]

Update History:

5.43
-Completely redid the YLTF (finances) sheet yet again to give people more information to help in budgeting, including "Actual Year To Date", "Forecasted Current Year End", and "Forecasted Next Year Start"
-Fixed the problem of not copying the data of the last few players in the stats and ratings sheets
-Fixed finding ExporterDir for archive.bat
-Added day and month to date
-Fixed some formatting
-Changed sorting of batter stats to XR
-Updated Exporter link again

5.42
-Completely re-worked team finances sheet (now has much more info and room for info not tracked by CSVs)
-Added FA, Arb El, and Auto Renew flags
-Fixed TY Player Expenses (Profit is now also fixed as a result)
-Updated Exporter link
-Changed division IDs to #s

5.41
-Added Change CSVs Folder button as a possible fix (listed in the troubleshooting section) to the problem that was happening for some people with v5.3#.
-Fixed the CSVs folder path from not being fully replaced (lengthy paths caused them not to be replaced in some formulas)
-Fixed the copying of the ratings and post season sheets to the archive
-Added the ability to customize what sets of sheets to process
-Reworked Refs and Arrays sheets into 2 Refs sheets and only the league specific refs, Refs2, gets copied to the archive
-Separated regular season and post season stats processing groups
-Added Generate CSVs button for times when all you want to do is generate CSVs or change the CSVs folder (I'd make a separate one for it, but it would depend on some of the answers to the generate CSVs macro)
-Added Delete Archive button to delete archive
-Added the ability for "N/A" to be marked beside some ref info folders for when those options are rejected. Now, if later those options are accepted, the last selected folders can be reused
-Fixed minor problems with the ATTT, ATTA, YLTS, and YLTF sheets
-RARs now show as 0 instead of divide by 0 error
-Extra (non-raw) stats in Stats sheets are now value pasted instead of formula pasted
-Added the DH
-Fixed some column widths
-Redid the Directions section
-Redid the Notes section, including an explanation of RAR

5.40
-Reworked setup directions
-Added ability to generate CSVs from utility
-Added error checks
-Added confirmations of locations
-Added ability to select what groups of sheets to process
-Added batting and throwing handedness
-Added team cities and nicknames
-Separated Stats and Ratings sheets
-Replaced multiple processing sheets with Clips, Fill and Fill2 sheets
-Added back Post Season sheets
-Sheets (except for fielding) pre-sorted from best to worst
-Added P% (Pythagorean %) and WD (Win Differential, W%-P%) stats
-Added RAR (Runs Above Replacement) stat for batting and pitching
-Added ERC (Earned Runs Component) stat
-Added numerous pitching rate stats
-Added TB/H batting stat which can be thought of as a pure power stat
-Added PP (Primary Position) to fielding sheets to differentiate from Pn
-Rearranged Refs sheet
-Added AFC sheet directions
-Simplified directions on Home sheet
-Added web links

5.35
-Commented out numerous calls in the VB code that are only needed for running one procedure at a time as they were apparently causing the utility to not work for some people. They are needed to run many procedures by themselves and saved me a great deal of time in testing the utility. But most people will only use the add new year macro, so they are not necessary. Doing this should also speed up the utility.
-Optimized some VB code
-Fixed some column widths and decimal places
-Replaced the division abbreviations with number codes as they were often not unique
-Fixed a minor backward compatibility issue with Excel 2000. The utility should now work for most people using Excel 2000 and 2002/XP. There is an issue with Excel 97, but I doubt many people still use it so I'm not going to bother finding workarounds that will probably lead to code that is not as good as what the later versions allow for.
-Edited the ReadMe, especially the Features and Troubleshooting sections

5.34
-Updated and tested with Excel 2002
-Added team finances
-Added player contracts
-Added player ratings (current & potential)
-Added team and league statistics
-Added columns for user-defined stat formulas (UD#)
-Added the archive file to shrink file sizes and increase file integrity
-Changed the utility's name from OOTP5 Excel History to OOTP5 Archive to distinguish it from Cato's History utility
-Removed the Top / Best sheets as it is possible to get this info from sorting the player sheets
-Renamed sheets for better clarity
-Added the Links sheet
-Cleaned up the Home sheet
-Optimized the VB code
-Changed the ReadMe to HTML

5.21
-Significantly improved processing time, especially for large leagues, by improving the deleterows VB code
-Added the ability to change the CSVs path
-Increased the # of rows to process for the advanced filter procedures. Some large leagues had more players fitting the criteria than I had allowed for, but now even leagues with the max of 40 teams should be ok.
-Edited the directions
-Added troubleshooting, contact info, and wish list sections.
-Reorganized the home sheet.
-Shrunk size by deleting blank cells shown by scrollbar

5.20
-Redid the VB code for it to also work with Excel 97
-Added sheet links on the home sheet
-Changed sheet names to be more intuitive
-Added Glossary (below)
-Fixed problem with RL stat
-Fixed TRST Averages and Ranks for expansion teams

5.12
-Fixed a problem with the year data caused by 5.11

5.11
-Took away ability to change the path of the CSVs. Until, I find a better workaround, put the exported CSVs into c:\CSVs.

5.10
-Added buttons to add new years: 1) without career stats and 2) with only career stats (Please note that doing 1 of these may screw up something (the ATm sheet, etc) that uses the # of years added data. To fix it, manually change it in the references sheet.)
-Optimized the career sheets' code
-Added sort buttons to career sheets
-Added sheets: ATm, APn, Avgs
-Added button to initialize workbook (deletes all added data)

[Smiley]

[michael]

Your 'example' link does not work for me. Sounds good though!

[kq76]

EDIT: Okay, now there's just a screenshot. The full example was a bit big.

[sporr]

This is excellent stuff. I've dabbled with it in the past, but these enhancements convince me to dust it off and try it again. Thanks!

[dougaiton]

This seems like a great utility, but I get a bit confused. Trying to set it up for an initial leguea (having exported and all that, set teh league paths), it says update files: teams.csv, but I can't find the csv file anywhere! Any ideas...

[sporr]

The teams.csv file should be one that Commish Joe's exporter produces.

[dougaiton]

Yeah, its just they don't appear when I try to update. I don't know whether my version of Xcel recognises CSV files as Excel workbooks, and as there isn't an 'All Files' version...

[kq76]

Originally posted by dougaiton
This seems like a great utility, but I get a bit confused. Trying to set it up for an initial leguea (having exported and all that, set teh league paths), it says update files: teams.csv, but I can't find the csv file anywhere! Any ideas...

Yeah, its just they don't appear when I try to update. I don't know whether my version of Xcel recognises CSV files as Excel workbooks, and as there isn't an 'All Files' version...

Excel 2000 and XP should work. I don't understand exactly where you're having a problem, so I'm going to copy the directions here and then try to explain them better. I realize my directions may be a bit complicated, but I was trying to make them as comprehensive as possible to ensure that as few as possible people would have problems. Most utilities seem to come with minimal directions that are usually just not enough for a lot of people. I was trying to do the opposite.

***
Directions: (updated for v5.34)

You can just press the "Add New Year" button (to the right) and follow the directions in the windows that appear. However, I do recommend that you read the more detailed list of the directions below at least once before you use the utility for the first time.

1) Before each year you want to add, use CommishJoe's Exporter5 (available at http://bosi.jpbaseball.com/downloads.php) to export your league's CSVs.
2a) If this is the first time you use this version of the utility, do the following:
i) Press the "CSVs Path" button to select the folder where your CSVs are.
ii) Press the "Archive Path" button to select the folder for the output file.
iii) Press the "First Year Added" button to input the first year of CSVs added.
2b) If this is not the first time you use this version of the utility, just check that the reference information (to the left) is correct. If it is not, correct it using the aforementioned change buttons. Editing the reference information cells themselves will not work.
3) Press the "Add New Year" button and follow its directions just to double check that everything is correct.
4) Wait till all the windows that popped up disappear.
5) Peruse the output file (it will have your league's name at the end of the filename) for whatever interests you. The sort buttons might be useful, but problems may arise if the sorts are not undone before saving.
6) If you want to put your league's archive on a webpage, from the output workbook file menu select "Save As Webpage" and then "Save".

Once you are familiar with the window messages, to get through them faster, press your enter key twice instead of using the mouse to click the options.
***

Ok, so you can press the add new year button and follow the directions that pop up or read the directions ahead of time. I think that's self-explanatory. I will specifically explain the ahead of time directions, but the popups are virtually the same.

Step 1 tells you to use CommishJoe's (CJ from here on) Exporter to create your CSV files from the league files. You will need the latest version of CJ's Exporter for this. I suspect that this might be your problem right here. It has its own help page, but the pertinent info is:

***
In Windows Explorer, bring up exporter.bat (right click and choose Edit). It'll look something like this:

player.exe "c:\temp" "c:\temp"
teams.exe "c:\temp" "c:\temp"
schedule.exe "c:\temp" "c:\temp"
pause

The first directory is where the .dat files are that you would like to export. The 2nd directory is the destination where you would like to save the CSV files too. Leave the quotes, and ensure the destination directory exists. Save your changes, exit your editor, and double click on Exporter.bat. It should now generate the CSVs to the destination directory.
***

Step 2 gives you 2 options, a and b, depending on whether this is the first time using the utility for this league or not. I'll focus on a seeing as how you have yet to get it to work.

2ai tells you to press the Change CSVs Path button. Once you do, a popup will appear that allows you to select where CJ's Exporter created your league's CSVs. So if it is c:\temp like CJ's instructions says, then you select drive C and then the temp folder and press ok.

2aii is similar to 2ai except that you now have to select the folder where you want the utility to create the future output file, or archive. Say for example you want it to appear on your desktop and your desktop folder is at d:\desktop (mine is, but yours probably is not), then you select that folder.

2aiii is for you to input what the first year of CSVs you are adding. So if your league is from 2003 to 2012 and you want to add all those years, then here you want 2003 here for every year you add. But say you just want to add years 2008 to 2012, then 2008 is the year you need.

Step 3 just tells you to press the Add New Year button and follow the steps, some of which you may have already done. Once you get familiar with them, you can just press ok without reading them.

Step 4 is to simply wait till all the CSVs close. You are essentially done.

Step 5 is to just check out the archive for whatever info you might be interested in.

Step 6 tells you how to make the archive into html if you want.

I've tested this with a few leagues and other than some minor column width issues that are particular to certain leagues which the user can easily fix, there doesn't seem to be any problems.

Perhaps you can tell me exactly where in the direction steps that this update teams.csv occurs. In the meantime, I would recommend making sure you have CJ's latest exporter (http://bosi.jpbaseball.com/files/exporter5.zip this says 5 as others do, but there's actually different versions of v5. this link should lead to the latest though) and trying it with that.

[Alec Burkhardt]

I'm getting the same problem.

For some reason, while getting all the CSVs, the excel program isn't able to find both the teams.csv and the players.csv files. Well, it seems to find them once but then can't find them again for some reason.

Just to be clear, I've followed steps 1-3 (and even tried changing where the CSVs are located since normally I have them in a folder about 3 or 4 levels deep rather than in just c:\temp). The computer starts working on step 4, opens 5 CSVs (league, teams, players, bat_stats & player_field) and then opens a Windows file open box entitled 'Update Values: teams.csv' as if it's trying to find the appropriate file to open and needing user input. The box begins in the 'My Documents' folder and the only types of files shown are true Excel files (.xl*) for the user to select. If you attempt to change the file types available, the only three choices for viewing are 1)Microsoft Excel Files (.xl*); 2)Web pages & address (.htm, .mht); & 3)Lotus 1-2-3 Files (.wk?). So .csv is not a file type that appears to be available. I've tried going to the correct folder and entering teams.csv manually but that doesn't solve the problem.

After either attempting to direct the program to teams.csv or just clicking cancel, the program then moves on to the same type of problem concerning players.csv. Then back to the same problem involving teams.csv again. Then back to the problem with players.csv - but at this point Visual Basic pops up with the following error:

=====
Run-time error '13':

Type mismatch
=====

when I click on debug, VB opens to the following part of your code:

=====
Sub HasRefreshPlayersRan()
Sheets("Players").Select
If Range("A3") = 0 Then
Call RefreshPlayers
Else

End If
End Sub
=====

the third line (If Range("A3") = 0 Then) is highlighted. Not knowing enough VB, at this point I simply shut down the program. I have no idea what the issue might be, but hopefully I've described what has occured when I've attempted to use the program sufficiently to be of some help in figuring it out.

[kq76]

Thx Alex for that very detailed explanation. I'll try to look at it today and see if I can make sense of the problem.

EDIT: Oh, and btw, what version of excel are you using? Because I am getting reports from some people that it is working, so maybe it's particular to some Excel version.

EDIT2: What path did you have for the CSVs? Was there a space in it?

[Alec Burkhardt]

Excel 2002

My prefered path is c:\bbhist\"leaguename"\data ("leaguename" changes depending on the league but I never have spaces in it) but I've also tried it using c:\temp and got the same error.


I've used (actually still am using) your previous Excel History and it's worked just fine using my preferred path. Have no idea what might be the problem, especially since the program does appear to open the CSVs once but then doesn't seem to recognize them after that. Hope this helps.

Alec

[kq76]

Originally posted by Alec Burkhardt
Excel 2002

My prefered path is c:\bbhist\"leaguename"\data ("leaguename" changes depending on the league but I never have spaces in it) but I've also tried it using c:\temp and got the same error.


I've used (actually still am using) your previous Excel History and it's worked just fine using my preferred path. Have no idea what might be the problem, especially since the program does appear to open the CSVs once but then doesn't seem to recognize them after that. Hope this helps.

Alec

Ok, well 2002/XP is what I was using as well. I've found some issues with it in 2000 and 97 now though. I'll try to fix the ones with 2000, but probably not 97 as I doubt many people still use it.

This "update" msg sounds a lot like the msg when you first open the utility though, which you're supposed to say no to. But you're saying this update msg comes well after that. I don't really know what the issue is especially since I cannot duplicate it, but I'll try to think about it.

Could you tell me how many sheets (YLTF, YSRB?) get processed (as in year data, not just the headings) in the output file? If none, how many in the utility file? Knowing that it gets stuck near the hasrefreshplayers procedure is helpful, but it'd be nice to pinpoint it even further as that procedure is called a few times.

Also, if it's possible, could you upload a zip of your league file without the subfolders somewhere for me to download? I doubt it, but it could be something particular to some league files.

[Alec Burkhardt]

While the title on the window says "Update Values: teams.csv", the window itself is really the same as you would get if you went to open a file in Excel. And the user really has all the same options that exist in that window, except that the only three options in the 'File of type' are Excel, Web pages & Lotus 1-2-3. It's not like the 'Update Links' window that pops up when you open the file initially.

As far as I can tell, none of the sheets get processed (all sheets are either blank or have REF# messages) and I've never gotten any output files from it. It opens the following CSVs (league, teams, players, bat_stat, player_field) and then the 'Update Values: teams.csv' window pops up. Since there is no way I've found to actually get it to access the teams.csv, this is when the program gets hung up. I get the error message and option to debug. At that point there are six files open in Excel: OOTP5 Archive v5.34.xls and the five .csv files that were opened before the hang up.

And I can certainly send you a zip of the league if you think that would help. Would you want the files from the OOTP5 folder, the output files generated at the end of each season, or would just the CSVs be enough? Probably easiest for me to email the zip directly to you rather than try to find someplace to upload it. You can contact me directly at alec_burkhardt@yahoo.com if you wish.

Alec

[kq76]

Ok, I'll email you Alec. Thank you so much. It's frustrating when you put so much time into something for it not to work for some people, so I really appreciate the help in trying to fix it.

Oh, and I've fixed the other problem I found with it in 2000. It was a really simple backwards compatibility issue. I'll try to fix the problem with it in 97. Later I'll upload a v5.35 that will hopefully have all the problems fixed.

[kq76]

Well, I made some changes (among a few others) to the Archive utility that should have prevented it from trying to "update" the CSVs. However, when I gave it to Alec to test on his computer, it got a bit farther, but the same problem occured. I don't really know what the problem is as I was able to run the 5.34 and 5.35 versions of the utility successfully using Alec's league's CSVs on my computer. However, like Alec and Doug both brought up, it's probably differences in Excel (even though we were all using 2002/XP) or some other programs. I've replaced 5.34 with 5.35 on my site as I do believe it is better and should work for most people. If anyone has any clue as how to fix the problem some are having with it, please let me know.

EDIT: Also, please let me know whether the inclusion of the ratings and talents are worth the extra room or not. Without them, more years could be added.

[YoungEagle]

Nice work Kieran

I basically did the same thing for the NPBL, I just used Access rather than Excel. I use the .csv from Commish Joe's exporter and import them into Access table.

With thius I can easily track rating changes, talent levels, and such from year to year. One query then gets the job done. This is what I used to determine the average ages of each team that Jesse posted awhile ago.

[dangarion]

I'm running into the same exact problem with Excel not wanting to look for CSV files.

I first used a fresh completely unservice packed XP version, and then I updated it all the way to SP2 and my Excel still doesn't like the CSV files for some reason. It will bring them up when I initially set the folder, but when I try to add another year Excel doesn't have CSV in the drop down option when you are trying to change the file type (xls,html,etc). It's deffinately some type of Excel problem. But I don't know if there is a work around that can be produced by KQ76 or not.

[kq76]

Originally posted by YoungEagle
Nice work Kieran

I basically did the same thing for the NPBL, I just used Access rather than Excel. I use the .csv from Commish Joe's exporter and import them into Access table.

With thius I can easily track rating changes, talent levels, and such from year to year. One query then gets the job done. This is what I used to determine the average ages of each team that Jesse posted awhile ago.

Thanks Will.

Am I correct in that Access can hold more data than Excel easier? And can Access do everything Excel can, especially the sorts? Including all the VB macros? I learnt basic Access years ago and off and on I've been wondering if I should change the utility to Access, if it would be better somehow, but I just don't know as much about it as I do with Excel. Maybe I could check out your Access utility sometime?

EDIT: One other thing I should probably consider is that maybe the majority of people either don't have access but do have excel or are like me are pretty decent when it comes to excel but not nearly as good with access. I'd like to play around with it in access just for the fun of it and I've tried on several occassions, but I just don't really know how to start.