Excel quiz bowl scoresheets

Anything that's on topic but doesn't fit elsewhere, including related events that might be of interest to quizbowl players (KMO, Science Bowl, etc).
User avatar
Matt Weiner
Sin
Posts: 8413
Joined: Fri Apr 11, 2003 8:34 pm
Location: Richmond, VA

Re: Excel quiz bowl scoresheets

Post by Matt Weiner » Tue Nov 16, 2010 4:57 pm

What would be the difficulty of adding an "SQBSify" button to a sheet that spits out its results in the text-based encoding that SQBS .dat files use, into, say, a centralized Google Doc that you can then save as such a file to have your stats done by robot?
Matt Weiner
Founder of hsquizbowl.org

lchen
Wakka
Posts: 180
Joined: Wed Apr 02, 2008 8:11 pm
Location: Hunter, NYC
Contact:

Re: Excel quiz bowl scoresheets

Post by lchen » Tue Nov 16, 2010 7:59 pm

Matt Weiner wrote:What would be the difficulty of adding an "SQBSify" button to a sheet that spits out its results in the text-based encoding that SQBS .dat files use, into, say, a centralized Google Doc that you can then save as such a file to have your stats done by robot?
Probably quite high, since I can't actually program worth a damn. I also don't really understand the SQBS .dat format. Is there documentation available somewhere?
Lily Chen
Hunter '12, MIT '16

User avatar
cvdwightw
Auron
Posts: 3447
Joined: Tue May 13, 2003 12:46 am
Location: Southern CA
Contact:

Re: Excel quiz bowl scoresheets

Post by cvdwightw » Tue Nov 16, 2010 8:45 pm

Matt Weiner wrote:What would be the difficulty of adding an "SQBSify" button to a sheet that spits out its results in the text-based encoding that SQBS .dat files use, into, say, a centralized Google Doc that you can then save as such a file to have your stats done by robot?
For the moment, I'm ignoring all the stuff at the front and back of the SQBS file, some of which I've figured out and some of which I haven't.

For each game, SQBS records the results as follows:

[Game ID]
[number of team on left - SQBS tracks teams by number; for instance 0 is the first team entered in the Team Entry box]
[number of team on right]
[score of team on left]
[score of team on right]
[tossups heard - this is set to zero if SQBS does not keep track of it]
[round number]
[number of bonuses heard by team on left]
[number of bonus points scored by team on left]
[number of bonuses heard by team on right]
[number of bonus points scored by team on right]
[up to nine? lines of zeros; this appears to have something to do with bonus conversion tracking settings but I haven't figured it out yet. For automatic bonus tracking without bouncebacks, it's six lines of zeros. For no bonuses (tossups-only), there are no lines of zeros.]
{[number to indicate Player on Team Left, e.g., 0 to indicate Player #1, 1 to indicate Player #2, etc.; -1 is used to indicate the absence of a player in that position - I believe these numbers refer to the order in which the players are listed on the roster, but I'm not 100% sure]
[games played (<= 1) by corresponding player on Team Left]
[powers scored by corresponding player on Team Left]
[tossups scored by corresponding player on Team Left]
[negs scored by corresponding player on Team Left]
[custom point value tossups scored by corresponding player on Team Left]
[total points scored by corresponding player on Team Left]
[number to indicate Player on Team Right]
[games played (<= 1) by corresponding player on Team Right]
[powers scored by corresponding player on Team Right]
[tossups scored by corresponding player on Team Right]
[negs scored by corresponding player on Team Right]
[custom point value tossups scored by corresponding player on Team Right]
[total points scored by corresponding player on Team Right]}
Repeat the lines in braces six times (once for each player slot; even if a team only has one player), then go to the next game.

Basically, the difficulty here is that the macro needs to know the number of each team (and the number of each player on that team) and the game ID number to properly execute stats by robot, notwithstanding those bonus-related zeros that I haven't parsed yet. Because of this, I'm hesitant to declare the plausibility of an "SQBSify" button for a single scoresheet without it being able to read something else from an external file.
Dwight Wynne
socalquizbowl.org
UC Irvine 2008-2013; UCLA 2004-2007; Capistrano Valley High School 2000-2003

"It's a competition, but it's not a sport. On a scale, if football is a 10, then rowing would be a two. One would be Quiz Bowl." --Matt Birk on rowing, SI On Campus, 10/21/03

"If you were my teammate, I would have tossed your ass out the door so fast you'd be emitting Cerenkov radiation, but I'm not classy like Dwight." --Jerry

Avram
Lulu
Posts: 90
Joined: Thu Oct 09, 2008 5:45 pm

Re: Excel quiz bowl scoresheets

Post by Avram » Wed Nov 17, 2010 2:14 am

cvdwightw wrote:For the moment, I'm ignoring all the stuff at the front and back of the SQBS file, some of which I've figured out and some of which I haven't.
[..snip..]
Basically, the difficulty here is that the macro needs to know the number of each team (and the number of each player on that team) and the game ID number to properly execute stats by robot, notwithstanding those bonus-related zeros that I haven't parsed yet. Because of this, I'm hesitant to declare the plausibility of an "SQBSify" button for a single scoresheet without it being able to read something else from an external file.
I agree with Dwight that this wouldn't be easy to do. One possibility, I suppose, is creating the teams and players in SQBS, then having the Excel spreadsheet import the SQBS file, so that it has the team and player ID numbers to work with. That would then let the spreadsheet provide drop-downs for team and player names, and eventually export SQBS-like data (as I have stated elsewhere, the game records from SQBS can be concatenated to create a tournament file with little extra processing). The spreadsheet loaded with the SQBS data could then be distributed to rooms. I'm afraid, however, that doing all that file reading and writing from VB will make the spreadsheet almost a full-fledged stat system unto itself.

For reference, you can consult the appropriate code in QBSQL: http://code.google.com/p/qbsql/source/b ... ns.php#569 . The code there does create SQBS-formatted data files that SQBS is able to read without any known errors (but note that QBSQL does not do bounce-backs, so my code doesn't handle that part of the data file). It also ignores the end of the file since it doesn't seem to be needed.
Avram Lyon
Kazan Federal University '11
UCLA '14 (or so)
Grinnell '06

User avatar
dtaylor4
Auron
Posts: 3725
Joined: Tue Nov 16, 2004 11:43 am
Location: Springfield, IL

Re: Excel quiz bowl scoresheets

Post by dtaylor4 » Mon Dec 13, 2010 1:19 am

Is there a way you can add a slot for TUH? As someone who uses it in an area where many teams are cheap and stupid, this helps when there are subs.
Donald Taylor
Springfield, IL
(Decatur) MacArthur '05/UIUC '09 BS Accountancy/'10 Master of Accounting Science
Freelance writer, staffer, stats guru, TD

Where is Christchurch? That's in Queensland
Where is Palmerston North? That's in New South Wales

lchen
Wakka
Posts: 180
Joined: Wed Apr 02, 2008 8:11 pm
Location: Hunter, NYC
Contact:

Re: Excel quiz bowl scoresheets

Post by lchen » Mon Dec 13, 2010 7:29 pm

dtaylor4 wrote:Is there a way you can add a slot for TUH? As someone who uses it in an area where many teams are cheap and stupid, this helps when there are subs.
Sure, try this. Hopefully it works correctly.
Lily Chen
Hunter '12, MIT '16

User avatar
jonpin
Forums Staff: Moderator
Posts: 1972
Joined: Wed Feb 04, 2004 6:45 pm
Location: BCA NJ / WUSTL MO / Hackensack NJ

Re: Excel quiz bowl scoresheets

Post by jonpin » Tue Dec 14, 2010 3:36 pm

All current versions of my scoresheets, which henceforth can be found here, have a row for entry of tossups played.

I'll be editing the PACE one over break to incorporate three-cycle overtime.
Jon Pinyan
Coach, Bergen County Academies (NJ); former player for BCA (2000-03) and WUSTL (2003-07)
HSQB forum mod, PACE member
Stat director for: NSC '13-'15; NHBB '13-'15; ACF '14, '17; NASAT '11

"A [...] wizard who controls the weather" - Jerry Vinokurov

User avatar
Corry
Rikku
Posts: 328
Joined: Fri Feb 10, 2012 11:54 pm

Re: Excel quiz bowl scoresheets

Post by Corry » Tue Jul 03, 2012 7:40 pm

Dang, I was actually planning on making something like this for our team practices a few weeks ago. But I guess I was 4 years late.

Anyways, these are awesome.
Corry Wang
Arcadia High School 2013
Amherst College 2017
NAQT Writer and Subject Editor

romeokar
Lulu
Posts: 43
Joined: Mon Oct 24, 2011 8:28 am

Re: Excel quiz bowl scoresheets

Post by romeokar » Mon Mar 09, 2015 8:17 am

We have adopted Google everything in Darien. At the last tournament I ran, we used Google Sheets and Google Docs in conjunction with SQBS to aid with scoring. The system was efficient and precise. If you would like me to share the files with you, just email me (kromeo@darienps.org).
Ken Romeo
Middlesex Middle School
Darien, CT

fett0001
Tidus
Posts: 590
Joined: Wed Feb 27, 2008 11:50 am

Re: Excel quiz bowl scoresheets

Post by fett0001 » Sat Mar 21, 2015 1:35 pm

romeokar wrote:We have adopted Google everything in Darien. At the last tournament I ran, we used Google Sheets and Google Docs in conjunction with SQBS to aid with scoring. The system was efficient and precise. If you would like me to share the files with you, just email me (kromeo@darienps.org).
Could you just make them publicly viewable, and post a link?
Mike Hundley
PACE Secretary
Martinsville High School 2001-2005
Virginia Tech 2005-

romeokar
Lulu
Posts: 43
Joined: Mon Oct 24, 2011 8:28 am

Re: Excel quiz bowl scoresheets

Post by romeokar » Fri Sep 25, 2015 10:31 am

Ken Romeo
Middlesex Middle School
Darien, CT

randycoxclemson
Kimahri
Posts: 2
Joined: Fri Sep 14, 2018 9:11 am

Re: Excel quiz bowl scoresheets

Post by randycoxclemson » Wed Oct 10, 2018 4:49 pm

In case anyone is interested in my spreadsheet, I created it (Excel) to track practices, though it can be used for games as well.

It handles up to 40 players, spread across as many as 4 teams. No one usually needs that, but we have had an explosion in JV players and sometimes have 20 playing tossups at the same time.

Anyway, this allows for tracking simple scores or, by adding a letter (H, L, S, or T), tracking each player's category scores. That's the real reason I created it, so that I could discern which players were better at each category.

So, feel free to use it if you like. I hope you find it helpful.
Attachments
Practice scoring.xlsx
40 player category tracking scoresheet
(149.31 KiB) Downloaded 7 times
Last edited by randycoxclemson on Thu Oct 11, 2018 10:16 pm, edited 1 time in total.
Randy Cox
Volunteer, DW Daniel HS, Central SC

troyharris
Lulu
Posts: 86
Joined: Tue Apr 12, 2016 8:47 am

Re: Excel quiz bowl scoresheets

Post by troyharris » Thu Oct 11, 2018 12:59 pm

We used these for our PAQT event this year. The only issue is accounting for substitutes, since there is no "Toss-ups heard" direct column on the sheet. The other drawback is with bounce backs. Since you can't add bonus points without first filling the toss-up side with a number, you either have to zero out a player, or, as I did during practice, create a player that doesn't exist and just put zeros all the way through for that column.
Troy Harris
Plymouth, NH

eckj
Kimahri
Posts: 3
Joined: Mon Dec 12, 2016 8:45 pm
Location: Copley, OH
Contact:

Re: Excel quiz bowl scoresheets

Post by eckj » Tue Oct 16, 2018 8:58 am

I have been working on these forever and still tweaking but I thought I would share. I adapted the scoresheets Lily Chen created to track a number of items including subjects answered for each players and much more. I think I have cleaned up most of the bugs at this point and any feedback is appreciated. I am self taught on Excel so I am sure I did some crazy stuff but it works. If you have any questions about them e-mail me at joshua.eck@copley-fairlawn.org

There are two excel scoresheets: one for tournaments and one for practice.
-the practice one can accommodate 16 players for 25 rounds.
-the tournament one can accommodate 6 players.
Attachments
NAQT Practice Monthly Stat Tracking 1.8.xlsx
Practice Scoresheet
(1.06 MiB) Downloaded 26 times

eckj
Kimahri
Posts: 3
Joined: Mon Dec 12, 2016 8:45 pm
Location: Copley, OH
Contact:

Re: Excel quiz bowl scoresheets

Post by eckj » Tue Oct 16, 2018 10:19 am

Here is the tournament spreadsheet.
Attachments
NAQT Subject Breakdown V2.3.xlsx
(302.78 KiB) Downloaded 9 times

Post Reply