The following Magic tracking spreadsheet walkthrough has been provided by long-time LR listener and lover of value, Zachary Orts. Download links to the spreadsheet are included in the article and at the end of it as well. Thanks Zach!
What is up everybody. Welcome to another write-up for Limited Resources. As always, I am Zachary Orts and I will be your guide through our spreadsheet today. First, some quick background. A long, long time ago when LR was in its infancy, they had a co-host named Jon Loucks. If you go even further back, nearly to the Stone-Age, but not quite, you will reach the Ryan Spain Days (RSD). One of the tools frequently referred to in the RSDs was keeping track of your drafts on Magic Online. Keeping track of your win-rate and keeping track of how much you spend is not just an invaluable tool, but also a necessary one.
When I started playing MTGO, I was not a very good Magic player. I copied my spreadsheet from Ryan’s, and I have made some improvements to the document as I’ve tried to improve my game. At least, I consider them improvements. I haven’t shown them to Ryan, so I’m not sure if he would consider them to be a step up or a blight upon his efforts. Anyway, the spreadsheets are here to tell us how much we’re winning, how much we’re spending, and there are a few extra secrets sprinkled in there for you along the way. This Limited Resources write-up is meant to serve as a guide for what my template is for and how to use it in the best possible way. All of the formulas are set-up, and once you learn how it works the upkeep per-draft is about 90 seconds. I’ve sent Marshall a blank template and a copy of my Theros drafts so that you can see what a filled out spreadsheet looks like. I suggest having the blank template in front of you for the remainder of this walkthrough.
I like to keep my sets separate, so that I can see what my win percentage is in a specific draft format. After a format is over, I will copy and paste my complete history of a set into a running total spreadsheet, so I can see my overall win percentage and cost per draft. You can choose to do this as you want, but if you’re going to keep your sets separate, the first thing you’re going to want to do is SAVE AS. If you don’t save as, you’re going to have to recreate the template every time a new set comes out. That’s a pain.
I should say that a great debt is owed to Ryan Spain for the spreadsheet, but also to Matthew Watkins who helped me refine what I already had, as well as figure out how the tables worked.
Let’s dive into the columns!
Column A – DATE
This is self-explanatory. Just put the date that you did your draft on. It’s helpful for looking up draft files, and simply knowing how many times you draft over the course of the week.
Column B – SETS
I put the three letter abbreviation for the set that I’m drafting. THS, SOM, NBS, ROE, ISD, DII, AVR, etc.
Column C – NUMBER
Simply the number of times you have done this particular format. I like this because I always save out my decks in deckbuilding with the corresponding number. Then I can pull up any draft decks easily. Sometimes Matt and I are noticing a discrepancy in our winrates with certain colors. Then I can easily go to a WU deck that I 3-0’d with so I can show him what one of my 3-0 WU decks look like. It also shows you at a glance how many times you’ve drafted a specific set.
Column D – FORMAT
Swiss, Sealed, 84, 43, PTQ, Daily, etc.
Column E – COLORS
It’s very important to keep these in typical WUBRG order. The spreadsheet is able to count all ten guilds and a five color deck. For example, if you write your Rakdos deck as RB, then the spreadsheet won’t know to tally it. It has to be written as BR. These are the decks the spreadsheet will recognize.
Column F – SPLASH
This is a late addition to the spreadsheet. I made a separate column for splashes so that I could effectively tally up my win percentage and my times drafted for each color and guild.
Columns G through R – RESULTS
Under the respective ROUND column I put a W or an L, depending on whether I win or lose. I then keep track of whether or not it is a 2-0, 2-1, 1-2 or 0-2. If my opponent no-shows, I normally put 1-0 for my record, but I do count timing my opponent out in game three or game two as a win. Marking with W or L is crucially important because the final tally will automatically pull it from here.
Columns S and T – MW and ML
Stands for Match Wins and Match Losses. It should automatically tally if you put in your W and L correctly. Each new draft, make sure that you copy and paste the formulas into the cell below. The formulas should be =COUNTIF(G2:P2, “W”) and =COUNTIF(G2:P2, “L”)
Column U – PACKS WON
Simply how many packs I won in the draft.
Column V – PACKS NET
I do this manually because I want to be able to control for sealed and phantom queues in the same spreadsheet. If you just want to track your drafts (so that you’re always losing three packs) you can insert the following formula: (=U2-3).
Column W – TIX NET
This is almost always going to be -2. Sometimes, however, it’s going to be something else. For example, throwback drafts are going to be -3. Also, for prerelease drafts it’s going to be -15, while the price of packs is going to be 0.
Column X – COST OF DRAFT, UNWEIGHTED
This is a snazzy little formula that just tells me my cost of draft if I bought all of my packs for exactly four tickets and if I didn’t sell any rares. The formula should be ( =(3-U2)*(-4)-2 ). Every time you draft, you just have to ctrl-c the formula to the cell below it. It will automatically adjust for the new line.
Column Y – COST OF DRAFT (WEIGHTED)
This is the most important formula in the whole document. It calculates from your tix net, cost of packs and tickets from selling rares. As before, just ctl-c the formula to the cell below it everytime you draft.
Column Z – COST OF PACKS
How many tickets you spent to buy your packs (should be expressed as a negative number!). Sometimes, when prices are low, I stock up on packs, and then I just put a larger number of lost tickets here. It will make a few specific drafts look more expensive, but as you do more drafts, the price will even out.
Column AA – TICKETS FROM SELLING RARES
Whenever I sell rares, I put the number of tickets received here (expressed as a positive number).
Column AB – RARES
After I have submitted my deck, I will sort by rarity then add any rares that I drafted here. After I sell them, I will bold them and put the price I sold them for in parentheses. The reason I keep such meticulous track is because when we move into full block drafting, I don’t want to be confused of when I drafted which money cards from the first block.
In order to make the live-updating work correctly, I’ve found that it’s best to add new rows when you get to about three rows left. If you let it go until one or two, it can get a little funky. Not sure why.
A few notes on interpreting the data. These cells are from the template.
C24 – This gives you, at a glance, how many events you are looking at.
L24 – This gives you your GAME winning percentage.
T27 – This gives you your MATCH win percentage.
Y24 – This will tell you how many tickets you have lost (or gained!) drafting.
AA24 – This is my favorite (and most useful) number. Your average (weighted!) cost per draft.
A29:A39 – Auto-calculates the number of times you have drafted each guild. I like to BOLD a guild when I win a draft with it so I see which guilds I haven’t 3-0’d a draft with yet.
F29:F33 – This tells you how many times you have drafted each color.
G29:G33 – Percentage of time drafting each color.
K29:K33 – Win Percentage with each color.
N30 – Tells you what percentage of your events you have won (it counts the amount of times you have not lost in an event).
H40-H50 – Win Percentage with each specific guild.
And, finally, the charts on pages two and three of the document will auto-update to give you win percentages with each color and each guild.
So, there you have it. A full primer on how to use the spreadsheet. If you have any questions, hit me (@zvazda) or Matt (@Oraymw) up on twitter. If you have in-depth questions, you can shoot me an email at email@example.com . Finally, after each set, if you have a large sample size, go ahead and shoot me your spreadsheet in an email form. The results won’t tell us anything definitively. Numbers from specific people’s drafts tell us very little, but it might be fun to throw them all in a spreadsheet and see what “limited resources members” cost per draft and/or win percentages are.
Google Docs versions of both spreadsheets (Thanks Nathan @nex3 for these!)