Saturday, November 7, 2020

How I Used Google Sheets to Build A Rotisserie Draft Simulator

Magic the Gathering is a popular collectible card game with a variety of different ways to play. "Drafting" is where players take turns picking cards to put in their deck from a common card pool. One popular variant is called a Rotisserie Draft. Unfortunately, I could not find any ways to Rotisserie Draft online with friends so I decided to create a tool to do that in google sheets.

Players Rotisserie Draft by taking turns publicly choosing a card from a cube. Cubes are carefully selected subsets of cards of similar power levels that are made to be drafted together. Typically cubes have 360, 450, or 540 cards.


The stipulation is that once a card has been chosen no other players can choose that card. You can read more about Rotisserie Drafting here.


The problem is that since each player ends up picking 45 cards it is hard to remember the cards that are still available to be picked and what cards have been picked already. Lucky for us, Google Sheets makes it easy to build a tool that keeps track of this automatically. Check out that tool here.


Here is how I built that tool.


First I downloaded the names of all the 540 cards from the Summer Vintage Cube. That is saved in a tab called “List” and a named range called “List”. Named ranges are useful here since I will be checking against the list of cards many times.


Next I made a tab called Two_Person_Draft where two players could track the cards they pick.

I used data validation to make sure that people are only picking cards in the list and are using the full name of the cards.




There also needs to be a check if the card has already been picked. 



By creating the “AlreadyPicked_TwoPerson” that is the range of  B:B and C:C I used the countif() function to see if that card exists. The countif(range, value) function returns an integer of the number of occurrences of a value in a range. For my purposes I only want to be notified if there are 2 or more occurrences since there is no problem if a card has been picked once or not at all. 



Some simple conditional formatting to make it clear that a card has been picked already. I used these rules. 


Now, when a card is being picked twice it will say FALSE



Once I had the 2 player draft set up it was simple to duplicate the system for 4 and 8 player drafts. 


 



This is a convenient way to Rotisserie draft with friends online since you both don't need to be in the same room. After you finish drafting you can export the list to a deck editor to finalize your deck and add basic lands. 


Data Viz and Analysis of the Numerai Leaderboard

There is a protocol built on Ethereum called Numerai. They explain it in more detail on their website but in essence it is a way for anyone...