Main Menu

Article - To EXCEL in Magic - Part 2

Started by Maqi, 20-02-2013, 12:58:21 AM

Previous topic - Next topic

Maqi

Hello everybody!

Last time I shared with you a little EXCEL tool to calculate probabilities.
This time however I won't share something "little" with you but rather a monstrous database.

You can find it here: HL-Lists_2010-2013.xls

I collected all Highlander decklists posted on mp.org and mtgpulse.com beginning with late 2010 tournaments up to the present. (I surely missed several decks. Nevertheless the list is quite exhaustive...).

The EXCEL database contains more than 15600 rows of data!

I built the sheet in a way that for every card ever posted you will be able to know who played the card, where, when and in which deck. Additionally you will be able to know how many players attended the tournament, which place the card finished and how many HL-decklists where posted for this event.

You can use the sheet for all kinds of calculations. Here are two interesting results that I computed with the data.

Most commonly played cards in top finishing HL decks 2010-2013:

Rank Cardname            total#   Score (the lower this value the better the card performed compared to other top listed cards)
1  Wasteland               161   0,616545894
2  Misty Rainforest        143   0,603243978
3  Flooded Strand          140   0,606150794
4  Windswept Heath         137   0,609489051
5  Scalding Tarn           136   0,617749183
6  Marsh Flats             132   0,604482323
7  Polluted Delta          128   0,596137153
8  Verdant Catacombs       128   0,598307292
9  Arid Mesa               127   0,5970035
10 Wooded Foothills        124   0,594198029
11 Kitchen Finks           114   0,594176413
12 Swords to Plowshares    112   0,58891369
13 Bloodstained Mire       111   0,633758759
14 Phyrexian Metamorph     106   0,58110587
15 Stoneforge Mystic       103   0,583333333
16 Sensei's Divining Top   101   0,610286029
20 Birds of Paradise        95   0,571491228
21 Maze of Ith              94   0,597369976
22 Noble Hierarch           94   0,57358156
23 Sword of Fire and Ice    94   0,576536643
24 Oblivion Ring            92   0,588164251
.
.
.


Very interesting is the fact, that StP and Kitchen Finks appeared more often than Bloodstained Mire in top finishing lists!

Here's another statistic which shows the top performing cards (that appeared at least 20 times total) within all of the posted decklists (which basically means that these cards were especially good at winning tournaments or placed very high):


deck# Cardname            total#   Score
1 Rhox War Monk           22   0,504419192
1 Reveillark              27   0,513888889
1 Geist of Saint Traft    24   0,516203704
1 Razorverge Thicket      26   0,517628205
2 Forest                  23   0,523550725
1 Enlightened Tutor       24   0,528935185
1 Into the Roil           21   0,534391534
1 Gaea's Cradle           23   0,535628019
1 Gifts Ungiven           20   0,5375
1 Birthing Pod            45   0,539814815
1 Arbor Elf               45   0,544135802
1 Stirring Wildwood       46   0,544384058
1 Sower of Temptation     34   0,546160131
1 Loxodon Hierarch        22   0,547348485
1 Temple Garden           71   0,552230047
1 Worldly Tutor           43   0,552648579
1 Seat of the Synod       24   0,553240741
1 Tainted Pact            44   0,55334596
1 Primeval Titan          55   0,554292929


Note the fact that a configuration of "2 Forest" seems to place very high overall. A curious fact is that "Into the Roil" seems to place very high, too. Rhox War Monk being at the top together with some other cards on this list suggests that Bant decks have been the cream of the crop during the last 3 years (add this to the fact that Kitchen Finks and StP are played more often than Bloodstained Mire...).

These are just two calculations that I made. Please feel free to do your own and share them with the community. I'm very interested in what else the data can tell us.

One thing though: Please make sure to disable the "auto-calculate function" in the Options menu of your EXCEL. At least with my laptop it can take quite a while to calculate more complex formula (especially when you choose to implement WHEN-operators and things like that).

'til next time!

Maqi

W0lf

2 Forests to play Kitchen Finks in Mono Red obviously

Maqi

#2
I repeatet the "cream of the crop" analysis with an updated database. I chose only tournament data from 06/12 up until today. Here are the results:

   Cardname      Total #    Score   
   1 Plains      20      0,523611111   
   1 Mother of Runes      22      0,525883838   
   1 Enlightened Tutor      27      0,531893004   
   1 Sylvan Library      28      0,543650794   
   1 Scrubland      24      0,545138889   
   1 Knight of the Reliquary      27      0,549897119   
   1 Qasali Pridemage      27      0,549897119   
   1 Treetop Village      23      0,550724638   
   1 Tarmogoyf      30      0,552777778   
   1 Temple Garden      26      0,556089744   
   1 Sword of Fire and Ice      32      0,556857639   
   1 Vindicate      22      0,561237374   
   1 Savannah      33      0,568181818   
   1 Mishra's Factory      24      0,57349537   
   1 Stoneforge Mystic      44      0,575757576   
   1 Eladamri's Call      25      0,578888889   
   1 Karakas      41      0,579268293   
   1 Aven Mindcensor      32      0,579427083   
   1 Llanowar Elves      23      0,580917874   
   1 Avacyn's Pilgrim      22      0,587121212   
   1 Bayou      25      0,588333333   
   1 Verdant Catacombs      46      0,59057971   
   1 Noble Hierarch      32      0,592447917   
   1 Path to Exile      42      0,596230159   
   1 Swords to Plowshares      50      0,5975   
   1 Scavenging Ooze      32      0,598958333   
   1 Chain Lightning      21      0,600529101   
   1 Elspeth, Knight-Errant      34      0,60253268   
   1 Demonic Tutor      30      0,602777778   
   1 Birds of Paradise      31      0,60483871   
   1 Marsh Flats      54      0,606481481   
   1 Wooded Foothills      46      0,60718599   
   1 Arid Mesa      54      0,60776749   
   1 Garruk Relentless      25      0,607777778   
   1 Restoration Angel      31      0,610215054   
   1 Oblivion Ring      41      0,610433604   
   1 Green Sun's Zenith      34      0,613970588   
   1 Misty Rainforest      58      0,615181992   
   1 Natural Order      21      0,616402116   
   1 Trinket Mage      25      0,617222222   
   1 Celestial Colonnade      21      0,619047619   
   1 Tundra      37      0,619369369   
   1 Windswept Heath      54      0,622685185   
   1 Flooded Strand      60      0,62337963   
   1 Hallowed Fountain      29      0,626436782   
   1 Eternal Witness      27      0,62808642   
   1 Fauna Shaman      22      0,628787879   
   1 Wasteland      69      0,628824477   
   1 Polluted Delta      54      0,629115226   
   1 Scalding Tarn      58      0,630268199   
   1 Kitchen Finks      44      0,630366162   
   1 Underground Sea      21      0,631613757   
   1 Bloodstained Mire      41      0,632452575   
   1 Remand      23      0,634057971   
   1 Preordain      32      0,637152778   
   1 Phyrexian Metamorph      44      0,637626263   
   1 Maze of Ith      39      0,642806268   
   1 Force of Will      41      0,64600271   
   1 Mana Drain      38      0,646564327   
   1 Sword of Feast and Famine      29      0,652777778   
   1 Tectonic Edge      33      0,653198653   
   1 Lightning Bolt      32      0,653645833   
   1 Snapcaster Mage      39      0,654558405   
   1 Tropical Island      27      0,654835391   
   1 Mental Misstep      20      0,65625   
   1 Sensei's Divining Top      52      0,658119658   
   1 Batterskull      27      0,659465021   
   1 Breeding Pool      25      0,662777778   
   1 Mana Leak      36      0,664351852   
   1 Jace, the Mind Sculptor      42      0,66468254   
   1 Arc Trail      28      0,668154762   
   1 Plateau      20      0,66875   
   1 Gideon Jura      20      0,66875   
   1 Cryptic Command      21      0,669973545   
   1 Vendilion Clique      34      0,672385621   
   1 Brainstorm      41      0,672425474   
   1 Phantasmal Image      36      0,676697531   
   1 Grim Lavamancer      24      0,677083333   
   1 Daze      28      0,679563492   
   1 Fact or Fiction      27      0,684670782   
   1 Flametongue Kavu      27      0,686728395   
   1 Ponder      32      0,687934028   
   1 Ancestral Vision      22      0,688762626   
   1 Counterspell      31      0,690412186   
   1 Burst Lightning      21      0,694444444   
   1 Bribery      20      0,697222222   
   1 Venser, Shaper Savant      31      0,698028674   

Wow "1 Plains" = optimal configuration! ;)
Ignoring this odd number 1 we can see that white and green cards lead the field.

And here are the cards with the most appearances in top lists (absolute numbers), again regarding only data from 06/12 onwards:

   Cardname      Total #    Score   
   1 Wasteland      69      0,628824477   
   1 Flooded Strand      60      0,62337963   
   1 Scalding Tarn      58      0,630268199   
   1 Misty Rainforest      58      0,615181992   
   1 Polluted Delta      54      0,629115226   
   1 Windswept Heath      54      0,622685185   
   1 Arid Mesa      54      0,60776749   
   1 Marsh Flats      54      0,606481481   
   1 Sensei's Divining Top      52      0,658119658   
   1 Swords to Plowshares      50      0,5975   
   1 Wooded Foothills      46      0,60718599   
   1 Verdant Catacombs      46      0,59057971   
   1 Phyrexian Metamorph      44      0,637626263   
   1 Kitchen Finks      44      0,630366162   
   1 Stoneforge Mystic      44      0,575757576   
   1 Jace, the Mind Sculptor      42      0,66468254   
   1 Path to Exile      42      0,596230159   
   1 Brainstorm      41      0,672425474   
   1 Force of Will      41      0,64600271   
   1 Bloodstained Mire      41      0,632452575   
   1 Oblivion Ring      41      0,610433604   
   1 Karakas      41      0,579268293   
   1 Snapcaster Mage      39      0,654558405   
   1 Maze of Ith      39      0,642806268   
   1 Mana Drain      38      0,646564327   
   1 Tundra      37      0,619369369   
   1 Phantasmal Image      36      0,676697531   
   1 Mana Leak      36      0,664351852   
   1 Vendilion Clique      34      0,672385621   
   1 Green Sun's Zenith      34      0,613970588   
   1 Elspeth, Knight-Errant      34      0,60253268   
   1 Tectonic Edge      33      0,653198653   
   1 Savannah      33      0,568181818   
   1 Ponder      32      0,687934028   
   1 Lightning Bolt      32      0,653645833   
   1 Preordain      32      0,637152778   
   1 Scavenging Ooze      32      0,598958333   
   1 Noble Hierarch      32      0,592447917   
   1 Aven Mindcensor      32      0,579427083   
   1 Sword of Fire and Ice      32      0,556857639   
   1 Venser, Shaper Savant      31      0,698028674   
   1 Counterspell      31      0,690412186   
   1 Restoration Angel      31      0,610215054   
   1 Birds of Paradise      31      0,60483871   
   1 Demonic Tutor      30      0,602777778   
   1 Tarmogoyf      30      0,552777778   
   1 Sword of Feast and Famine      29      0,652777778   
   1 Hallowed Fountain      29      0,626436782   
   1 Daze      28      0,679563492   
   1 Arc Trail      28      0,668154762   
   1 Sylvan Library      28      0,543650794   
   1 Flametongue Kavu      27      0,686728395   
   1 Fact or Fiction      27      0,684670782   
   1 Batterskull      27      0,659465021   
   1 Tropical Island      27      0,654835391   
   1 Eternal Witness      27      0,62808642   
   1 Knight of the Reliquary      27      0,549897119   
   1 Qasali Pridemage      27      0,549897119   
   1 Enlightened Tutor      27      0,531893004   
   1 Temple Garden      26      0,556089744   
   1 Breeding Pool      25      0,662777778   
   1 Trinket Mage      25      0,617222222   
   1 Garruk Relentless      25      0,607777778   
   1 Bayou      25      0,588333333   
   1 Eladamri's Call      25      0,578888889   
   1 Grim Lavamancer      24      0,677083333   
   1 Mishra's Factory      24      0,57349537   
   1 Scrubland      24      0,545138889   
   1 Remand      23      0,634057971   
   1 Llanowar Elves      23      0,580917874   
   1 Treetop Village      23      0,550724638   
   1 Ancestral Vision      22      0,688762626   
   1 Fauna Shaman      22      0,628787879   
   1 Avacyn's Pilgrim      22      0,587121212   
   1 Vindicate      22      0,561237374   
   1 Mother of Runes      22      0,525883838   
   1 Burst Lightning      21      0,694444444   
   1 Cryptic Command      21      0,669973545   
   1 Underground Sea      21      0,631613757   
   1 Celestial Colonnade      21      0,619047619   
   1 Natural Order      21      0,616402116   
   1 Chain Lightning      21      0,600529101   
   1 Bribery      20      0,697222222   
   1 Gideon Jura      20      0,66875   
   1 Plateau      20      0,66875   
   1 Mental Misstep      20      0,65625   
   1 Plains      20      0,523611111   

Also very interesting, I think. As usual Wasteland and fetchlands lead the list, but then white and blue cards dominate.

What does this tell us? Please feel free to discuss.

W0lf


Maqi

I listed only cards that appeared at least 20 times since 06/12. Worldly Tutor made its way into the top finishing decks just 17 times...

W0lf

That´s because it`s a mediocre card.
But 44 Soneforge mystics in 69 decks? Guess you know what to do now.

Tabris

We should do what the -36 Karma guy recommend
.

Btw. thanks for the work maqi, I know how hard the datamining is ;)

MMD

Quote from: W0lf on 13-03-2013, 03:06:00 PM
That´s because it`s a mediocre card.
But 44 Soneforge mystics in 69 decks? Guess you know what to do now.

Where can I vote for a troll&spam button for this forum?
Feel free to browse through my MKM account:

http://www.magickartenmarkt.de/index.php?mainPage=showSellerChart&idInfoUser=13199

I also have a huge amount of chinese and japanese foil HL staples not listed yet,  which I would like to downgrade to english foil. Just let me know!

Maqi

His Karma aside... that percentage is quite high.

carte_blanche

Thanks to Maqi for his effort to gather the data... but I really don't envy you people in the council for your job. When I just look to this card list, I see almost a pure goodstuff deck ready to be played.

I just copy / pasted the list in deckstats.net and that's what I'm refering to in the following.


Looking roughly at the cards played, there are several cards with UU in the mana cost and Cryptic Command. Green and white come up with two cards with double colored mana in the casting cost. There are no black or red cards with double colored mana in the list.

-> To me it seems that if you're playing control - you play blue based decks (nothing new, I suppose). I'm even a bit surprised that there is Bribery and Cryptic Command in the list... maybe control decks are not that dead in the format as I used to think but there is no diversity, even though other options exist (... but are not competitive enough?).

-> Also taking account of the number of cards of the different colors, we got a format containing just 3 colors: blue, white, green. Maybe red is there as a sidenote. Black does not seem to have any impact on the best decks at all, even though cards like Demonic Tutor and Dark Confidant exist.

35% of the lands in the list produce white mana. That leads me to the assumption that white is the most popular splash color. For which reasons... Kitchen Finks, Stoneforge Mystic, flexible or very cheap removal. IF you really look foreward to change that, maybe Stoneforge Mystic is really a card to consider to ban. I'm writing that because of the statistics and because the card is frowned upon by many people. I myself am not sure whether I would vote for a ban... but fortunately, that is not my task. ;)

As far as I see it, there are almost no cards (besides Bribery and Cryptic Command) that I associate with certain archetypes. It's really just goodstuff overall and thus "shapeless" which makes it hard for me to get a clear view of what is to do (concerning the ban list) to "cure" the HL format (of a goodstuff sickness?).

At least now I can say that I think it would not help the format to ban Demonic Tutor. The card is the best card to gather combo pieces - an archetype that is rare currently. Also colorwise, one would take away one of the few competitive toys the black player has left. It seems to me like the format "balances" the absurd power level of the black tutor by it's "wrong" color (it sounds weird, I know). To me, it seems like players like to spalsh black for two or three cards (because it's easy with that manabase) but if you ban them, most of them won't miss it too much - it was a spash anyway. Just fill the slots with the next best blue / white / green card.

One last thing: there is no mass removal in the list at all... in a creature heavy format. Is control with a low creature count so bad in comparison with spells-on-legs-control that you cannot play it sccessfully, even though you got a fair number of mass removal at hand. Or is it too slow? (I know about Tabrys' Esper list... is he just the only one who likes this kind of deck?)

It's just a list of things that came to my mind. Certainly, I missed some points. I just hope that you take them as a motivation to start discussing. :) Maybe I'll add some points, when I have more time to brood over the card list, but right now I got the impression that it is not an easy task.

@Council: Keep up the good work.

@all: Keep on playing!  ;D