Excel-Based QuikTest Template

[QuikSand]

Excel-Based QuikTest Template

For those who are inclined to do testing within FOF, I thought it might be useful to share a tool that I have developed for this purpose. At the very least, it reduces the amount of time necessary to pull essential team-wide data from a season’s worth of games run under a certain system.

GET THE EXCEL FILE HERE

- - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - -

Here’s how to use it, forgive me for the amount of detail:

1. Sim a single player game to the end of the exhibition season, just before Week 1 of the regular season

2. Go to “Global Options” and set injuries to ZERO (it’s best to control everything you can, I believe)

3. Create or load the gameplans that you want to use for your testing

4. Ensure that the settings for other things (like the “miscellaneous” screen settings) are set to your liking

5. Ensure that your depth charts are set the way you want them

6. SAVE this game at this point (you may use the “Save As” option if you might want to come back to it, or if you just want to have a backup)

From here, you are ready to run the testing – it’s a pretty simple routine. And with the Excel tool, you’ll find it a fairly painless experience. I find that simming ten seasons for testing purposes requires maybe 15 minutes of time. It’s not that exhausting, really…

7. Click “Simulate Games” and choose to “Simulate Until Playoffs” – this will take a little while

8. When the regular season concludes, click “Exit” and then “Team Summary” from the (poorly-titled) Simulation Window.

9. From the Team Summary page, click “Print” and print the output as a text file. (Directions on how to set up a generic/text printer option can be found here)

10. From MS Word or some other program, open the newly created text document, use Ctrl-A to select its entire contents, Ctrl-C to copy it all -- and close the document.

11. In MS Excel, go to the appropriate vacant slot on Row 15 of one of the worksheets… Cell C15 if this is the first time you are doing this… and use Ctrl-V to paste the text data into that spot.

12. Re-open the FOF 2007 window, and LOAD the game that you had previously saved – DO NOT SAVE YOUR GAME FIRST.

13. Go back to step 7 to sim another regular season, or, if you are done simming – go on to Step 14.

Note: I actually find the quickest process here to actually change the order a bit:

SIM – PRINT – LOAD – SIM – COPY – PASTE – PRINT – LOAD – SIM – PRINT…

(This way, while the game is simming the next season, I am copy/pasting the past season’s text results)[/i]


If you are done simming for whatever gameplan you are trying to test, check to see that the appropriate key stats are being picked up in the summary section above the text you have pasted. (Worksheets are set up for a default of 10 seasons of QuikTesting… if you want fewer than 10, delete some of the summary data, if you want more, copy the summary data further across those rows)

At this point, you should have summary data in two nice compact spots. Your results for this particular gameplan (or whatever you were testing here) should be summarized in the areas from A2:B12 in the worksheet. The data should look something like this:

Wins	11.70
	
Rush/Gm	29.3
YPC	3.76
Pass/Gm	32.53
YPA	7.08
	
DYPC	3.93
DYPA	6.00
Pts/Gm	14.43

So… the worksheet will pluck out the data from your team summary text dumps, and come up with the average of those various stats. A quick summary of games won, running offense, passing offense, defensive points allowed, and defense against both the run and pass.

Give this worksheet a title that fairly describes your gameplan (or whatever you are testing) and place it into Cell A1 of the worksheet.

You will also see the same data above summarized in the first worksheet of the Excel workbook – very craftily labeled “Summary.” That will present the same data as above, but horizontally spread across one extended row – with room for up to six different pages of data (from up to six different tests). That ought to provide the simplest means to compare results across gameplans.


That’s it. At this point, you may be overwhelmed that there are so many steps – but really this is not technically difficult at all, I just chose to be tedioulsy exhaustive with explaining how to do things, to try to fend off as many operational questions as I can. Basically, it’s just simming seasons, and dumping the text team summary data into the right cell of Excel, and the worksheet does the rest. Indeed, that explanation would probably serve well enough to most people.

With that – feel free to download the spreadsheet. There’s no particular reason why to stop at the handful of stats that I chose, so someone who is feeling enterprising might be able to expand on that. All I ask is that if you make meaningful improvements using this spreadsheet as a starting point, that you share back with me and the rest of the community.

[KWhit]

Very nice explanation.

I have tried to go the testing route in the past but got bogged down trying to create a SP roster that somewhat resembled my MP team roster. That was freaking impossible.

So I think I need to just pick a generic team and use it to test with. It's better than nothing.

Thanks for taking the time to share this.

[CU Tiger]

For testing ilike to take the NFL team I most want mimick and test there.
Then build my MP team towards those goals.

This eliminatees variables.
For example I created a GP that could consistently yield a 1700 yard rb and 4.2+ YPC using LT and SD, now in MP it only nets 1200 and 3.2. O line good in both WR same in both QB close to same. It tells me that RB is key to this GP working

[QuikSand]

For anyone who is interested, ToddieC made some improvements to my original testing template, and I have posted it here for public use:

http://www.fof-ihof.com/upload/QuikS...st_revised.xls

The revised version pulls even more data out of the Team Summary data for each trial, and presents more side-by-side comparisons on the summary page. And uses pretty colors, too! Thanks to ToddieC for sharing.

[wade moore]

Thanks! I've never posted about it, but I do use this when I get the chance to test.. it's HUGELY helpful.

[QuikSand]

I'm using this a good deal right now, and I wish there were an easy way to pullout the stats for a certain player from a set of results. Anyone with some Excel expertise have any idea how this might be accomplished?

Obviously, the problem is that individuals don't appear in the same slot in each printing -- so the guy "22 Smith" might be the #1 rusher in box C119 in the first trisl, but he might be the second rusher listed in box D121 in the next trial -- ideally, there could be some mechanism to pull out the appropriate stats for that particular guy, and analyze them. I'm doing it by hand now, which isn't awful, but it gets tedious.

Anyone have any ideas, or even a shortcut on how I might be able to do this myself>

[bselig]

Is there any reason this shouldn't work with open office?

[QuikSand]

Originally Posted by bselig

Is there any reason this shouldn't work with open office?

I've used OpenOffice, but I don't know how deep its compatibility goes. The worksheets use the VALUE, LEFT, and RIGHT functions, none of which are all that complicated -- so I would expect it to work just fine in OpenOffice, but I have not tested it myself.

[bselig]

Dunno what I'm doing wrong, don't really use spreadsheets often. When I follow the directions, pasting just seems to haphazardly drop the contents of the file in the middle of the spreadsheet which probably isn't the intended effect

Here are the contents of the file I'm outputting from the game, maybe includes too much stuff?

Spoiler

Front Office Football 2007
2041 Summary for Pittsburgh Steelers

Year 2041
Record 16-3
Winning Pct. .842
All-Time 431-129
Winning Pct. .769
Playoffs 51-21
Playoff Visits 31
Bowl Wins 10
Head Coach Zack Price
Record 90-22
Winning Pct. .803
Off. Coord. H. Ellard
Def. Coord. L. Zapisek

Pittsburgh Steelers Team Rank
Rushes per Game 38.6 2
Rushing Yards 172.1 1
Yards Per Carry 4.45 10
Pass Attempts 22.0 32
Completions 13.5 32
Completion Pct. 61.4 12
Passing Yards 193.4 28
Yards Per Attempt 8.79 1
Yards Per Catch 14.32 2
Total Yardage Gained 350.0 7
3rd Down Conversions 45.5 3
Points Per Game 24.9 4 (T)
Pass Rush Pct. 12.7 29
Pass Defense Pct. 50.2 20
Turnovers 22 7 (T)
Turnover Margin 0 15 (T)

Opponents Team Rank
Rushes per Game 23.6 3
Rushing Yards 100.9 6
Yards Per Carry 4.27 21
Pass Attempts 35.0 27
Completions 21.4 28
Completion Pct. 61.1 22 (T)
Passing Yards 234.2 21
Yards Per Attempt 6.69 16
Yards Per Catch 10.96 11
Total Yardage Gained 323.7 15
3rd Down Conversions 33.5 3
Points Per Game 20.3 14
Pass Rush Pct. 13.5 8
Pass Defense Pct. 52.8 15
Turnovers 22 27

Week Team Versus Oppnt
1 24 at BAL 20
2 16 DET 14
3 33 at KCY 22
4 17 HOU 7
5 19 at IND 20
6 34 CIN 13
7 15 JAX 10
8 25 at TEN 24
9 23 at CLE 16
10 33 BAL 27
12 30 at GBY 24
13 28 NED 37
14 30 at MIN 17
15 27 CLE 24
16 17 CHI 27
17 28 at CIN 23
$$CS 17 KCY 10
$$CF 31 NED 16
**FB 38 vs GBY 37

Passing Pos Att Comp Yards Y/Att TD Int Rate
5 B. Hanks QB 352 216 3094 8.79 23 11 98.6
**Team --- 352 216 3094 8.79 23 11 98.6
$$Opp --- 560 342 3747 6.69 24 12 86.2
Front Office Football 2007
Rushing Pos Att Yards Y/Att TD Fum
37 R. Unger RB 400 1756 4.39 10 7
49 T. Milburn RB 171 811 4.74 7 6
**Team --- 618 2753 4.45 19 28
$$Opp --- 378 1614 4.27 8 21

Receiving Pos Targ Catch Yards Y/Ctc Y/Tar Drop TD
81 V. Herndon WR 99 61 1043 17.10 10.54 5 7
82 B. Meyer WR 53 32 434 13.56 8.19 5 2
80 B. Benjamin WR 52 29 455 15.69 8.75 6 3
84 S. Culp WR 37 20 297 14.85 8.03 2 2

Defense Pos Tack Asst Sack Hurr Ints Defn PDPct
50 L. Rasmic OLB 82 34 0.0 1 0 6 74.9
46 C. Eagleton S 76 16 0.0 1 1 9 77.6
51 E. Churchill ILB 73 34 2.0 3 0 2 77.1
40 K. Culver CB 61 19 0.0 0 4 3 77.0
47 R. Boireau CB 59 13 0.0 0 4 16 81.6
44 J. Walters CB 55 13 0.0 0 1 11 78.5
41 A. Kasischke S 43 11 0.0 0 2 3 78.2
95 M. Heuring OLB 37 13 0.5 0 0 3 74.9
91 T. Booker DT 36 16 1.5 13 0 0 81.9
93 C. Ferich DE 35 9 9.0 21 0 0 81.2
72 B. Horn DE 35 12 13.0 18 0 0 81.0
29 G. Alonzo S 32 15 0.0 0 0 12 83.1
71 T. Tiller DT 30 13 2.5 1 0 0 82.0
33 A. Garcia S 18 5 0.0 0 0 2 74.4

[zlionsfan]

Originally Posted by QuikSand

I'm using this a good deal right now, and I wish there were an easy way to pullout the stats for a certain player from a set of results. Anyone with some Excel expertise have any idea how this might be accomplished?

Obviously, the problem is that individuals don't appear in the same slot in each printing -- so the guy "22 Smith" might be the #1 rusher in box C119 in the first trisl, but he might be the second rusher listed in box D121 in the next trial -- ideally, there could be some mechanism to pull out the appropriate stats for that particular guy, and analyze them. I'm doing it by hand now, which isn't awful, but it gets tedious.

Anyone have any ideas, or even a shortcut on how I might be able to do this myself>

There are a few ways you can do it, depending on how flexible you want it to be and how the data appears.

One good way is to use SUMIF(namesrange, playername, valuestosum). That's probably best for how the worksheets are laid out. You'd still have to know where the RB data fell in the summary sheet. Say the RBs are rows 119-125 with names in column A and rushing yards in column D. =SUMIF(A119:A125, "22 Smith", D119125) would give you his rushing yards.

If the data is contiguous and has a header row, you can use filtering to bring up just the rows you want to see, but that won't do calculations for you.

[zlionsfan]

Originally Posted by bselig

Dunno what I'm doing wrong, don't really use spreadsheets often. When I follow the directions, pasting just seems to haphazardly drop the contents of the file in the middle of the spreadsheet which probably isn't the intended effect

Here are the contents of the file I'm outputting from the game, maybe includes too much stuff?

The problem might be that you're not selecting the specific cell in the workbook before you paste the data. If you don't pick a cell, Excel will drop the data in starting with the cell you last selected, even if it was when you'd saved the file last.

[QuikSand]

bselig - make sure that you are posting the data into row 43 of the Test worksheets. I know the version I got back from ToddieC had not corrected the little "helper" reference to the left, which still said Row 15, I believe. I am pretty sure that Row 43 is the correct location for the Team Summary data.

When you paste it into the cell, yes it spills down to cover maybe 70-90 rows of data, but the important data is then grabbed for the summary section above.

If you're still having trouble, send me a PM and I'll do my best to help you out.

[DougW]

Originally Posted by bselig

Is there any reason this shouldn't work with open office?

It works great in open office. I've never seen it in anything else, so I can't compare - but if pasted in the correct place - the data is where it looks like it should be. The only thing I've noticed that "may be wrong" is that the average column is not a running total, all 10 seasons have to be pasted before the average is calculated. Then again, this may be the design.


In short .. yeah, it is compatible with open office.

[MrDNA]

Disclaimer: I have never used Excel before buying FOF.

11. In MS Excel, go to the appropriate vacant slot on Row 15 of one of the worksheets… Cell C15 if this is the first time you are doing this… and use Ctrl-V to paste the text data into that spot.

So... I don't know if I'm doing this right. I paste ALL the data I copied into cell c15 which then makes an incredibly long list of text all the way across the page. Then I run another sim and post ALL that data into d15? This just seems to give me one long line of gobbledeegook below the otherwise very useful looking formulas and such. I can't imagine I am doing this right. Please help the Excel deficient.

And while we're at it: is the revised template different as to where I paste the info?

[QuikSand]

At one point, a revised version of the template moved where the data was to be located. That might be the source of an error. I'll look into it.

[QuikSand]

Okay, if you are getting all the massive text crammed into one cell, rather than into a column, it appears that it's a cell formatting issue. I have no idea how that could happen, but I think it can be fixed.

What you should be seeing when you paste in the data is that it fills up a segment starting with cell C15 (if you're using the original file from the top of the thread) and going down from there. I'm not sure what causes the error you are seeing, nor how to fix it, right off the top of my head... but if you're getting one long jumbled bunch of text all jammed into C15, it's not formatting properly.

[MrDNA]

So what it SHOULD be doing is putting some data in C15, some in C16, some in C17, etc.

I'll try fooling around with it. Thanks for your help! (If anyone else has any idea, let me know how to change the formatting)

[QuikSand]

MrDNA, try the link in post #4 of this thread... but I think you need to post the data one row lower than the arrow points to make it work. The other improvements to it are pretty nice, overall. Maybe that will resolve your formatting issues. *shurg*

[wade moore]

Originally Posted by MrDNA

So what it SHOULD be doing is putting some data in C15, some in C16, some in C17, etc.

I'll try fooling around with it. Thanks for your help! (If anyone else has any idea, let me know how to change the formatting)

Ok, I think I understand the problem.

Are you pasting into C15?

I don't remember at this point why it says "Paste in row 15". You should be doing a copy all from the txt file and then doing the first paste in c42. The next year should be in d42, then e42, and so on.

In reality it should be a blank line on row 42 then row 43 will have the "Front Office Football 2007".

Here is a screenshot:



If when you paste it all goes on one row instead of carrying down rows - what program are you using to open the .txt file? I would recommend using notepad rather than trying to open it in excel because excel thinks it is smarter than it is.

Let me know if that doesn't help.


EDIT: I just checked. Quik's early, early on version of this you were posting into row 15. So, I think this is just an artifact of an old version that just needs to be cleaned up.

[MrDNA]

Thanks, Wade! You nailed it.

And for anyone else following my exploits, yes, you do need to fill in all 10 trials before the average shows up. Cheers!

[wade moore]

Originally Posted by MrDNA

Thanks, Wade! You nailed it.

And for anyone else following my exploits, yes, you do need to fill in all 10 trials before the average shows up. Cheers!

Glad I could help!

That makes me realize, I think I could pretty easily modify this so you didn't have to do 10 years - I'll try to take a look at that later.