Theros PreOrder - The Source Comics and Games

Today is the day of the first day to purchase pre-orders for the first set in the new Block.  I will be heading up to the Source since they have the best deal on any and all pre-orders.  They only charge $80 per box, but only for pre-order.  Outside of that they charge list price on everything after pre-order.

http://www.sourcecomicsandgames.com/

UPDATE:

Got my 10 pre-orders.  So the way the Source works is you pay for the pre-order and you get a small card, then on the day of the release they exchange the card for the box.  Now if you lose the card, then you are completely out of luck.  They do not keep any records of who bought what.

On the date of the set release they actually stay open Thursday night until midnight.  That way you can pick your new cards up at midnight the day of the release.  Now I know, you have the cards, so what is the point in staying up in line waiting for your cards when you can just pick them up in the morning.  It is pretty fun though.  Standing in line, talking to people, everyone excited about the new set.  Some nights when it is below zero, not worth it, but for the most part it is worth it.

I am a bit too old to stay up that late, so I just pick them up when the store opens, but I do miss standing in line.

Nugget of importance:
The Source usually sells out the first day of pre-order, but what people don't know is that they have a sister store that also does pre-order.  They only have 10 boxes to do the pre-order with, but if you do find that you missed out on the Source pre-order, give Uncle Sven's a call.  They might still have some boxes left.

Mead Hall Games - Minneapolis, MN

http://www.meadhallgames.com/

Grade: A-

What a beautiful site, and really good prices.  They also have a MTG social network setup called The Meadery.  I had been to the store once last winter, and they were still setting up.  They did not have much of a selection of cards, and everything they were selling was Standard.  As the days went by since the opening, they have been improving more and more.  It is quite a good feeling to see a store like this.  Most shops in the Minneapolis area are focused on 2 things, selling you stuff and getting you to come in the store to play.  This shop does that along with showing off simple delights to the eye.

The store is remodeled to the manner of a fine wine vendor.  Hardwood floors and the decor of simplicity.  When I first went to the store I had thought that they were in big trouble with the gaming space they did not have.  I also saw a huge t-shirt printing press, and was just confused how they would be able to support any tournaments here (Just read an article that the owner operator has many businesses, one being a t-shirt printer).

Now I have only seen pictures since I was there last, but they have improved it greatly since.  It looks like there is more of a selection of cards to choose from, and they support local graphic novel writers, and they finally got a sign.  I swear I drove around and around multiple times because they never had a sign, and I never would have thought of a shop under the apartment building...  Seriously, I thought my GPS was cracked out!

The website is just a piece of work.  Clean lines, easy to find the sets you are looking for, and just all around good looking.  You hit the site and they have a rotation of card art in a manner that I would have never thought of.  You just get a search bar right in the middle of the art.  Google-esk design.  Simple and to the point.  The more and more I visit the site, the more passionate I get towards my own MTG collecting.

I think the thing that gets me going the most is the social network.  Here, I can communicate with the local community, find trades, setup game nights, get great ideas for anything MTG.  Just by going to the site I was introduced to a podcast Magic: the Amateuring.  I have only listen to one of the podcasts, but I am already hooked.

Other stores are good, but these guys are really putting in the time to make something special and it shows.  They really care about what they are doing.  I am very impressed.  I have yet to spend a day playing down there, but I am trying to plan something soon.  I just need to get the trade book setup so I don't show up unprepared.

They do have an awesome calendar setup, and you can purchase tickets to the upcoming game nights.  What excites me is they have a Commander Thurs.  Just for EDH.  A whole night, just for EDH.  Amazing...  But like I said, still need to head down there to see what the people are like, and check out the atmosphere.  I am pretty sure I will do an update later on what comes of this adventure.

Set Tab: Total Column

Ok, so I think I have the spreadsheet complete.  Again, I had to use two different spreadsheets.  One for the prices and the second for the card count.  I am going to explain this in a strange way, as I am going to try and talk about the process that I used to build the system in total.

I was going to use 3 different spreadsheets:
Collection
Trade
Foil

But I was able to design a way to keep all the data for a specific set on a single sheet.  This way I will not have to use multiple spreadsheets in order to keep everything straight.  I know, the Price spreadsheet is a separate sheet, but we will get to that later.

So for the basic setup of the Collection spreadsheet, there are different types of Tabs.  There are Collection Tabs, and there are Set Tabs.  The Set Tabs are for a single set that was released by WotC. The example we are looking at today is from the Tenth Edition Set Tab.

Here is what the set Tab looks like for the Tenth Edition.  I only have to top part of this Tab as to explain what was done.

On the far Left is the Total columns.  This will be used to verify the count, and also to total up the three different sections of the collection.  Lets just talk about this column today.

Sheet totals verification


Collection Section









Trade Section








Foil Section










Card Count Totals for Collection, Trade, Foil, we will talk about this in a future Blog



_________________________________________
Sheet Totals
In the Sheet Total I have them into two different totals for verification.

Total 1: =SUM(SUM(A7:A12)+SUM(A17:A22)+SUM(A27:A32))
Total 2: =A6+A16+A26

Total 1 will take all of the Rows in a Section and add them together
Total 2 will take the Section Totals and add them together
_________________________________________
Section Totals
Sections are labeled with the name in Blue
Collection
Trade
Foil

Directly under the section title is the Section Total.
Section Total: =SUMIF(H2:H602,">0",H2:H602)

Here we are going to be using a SUMIF command.  This will allow us to look at column, match an expression, and then SUM a total if the expression matches.  So in this instance for the Section Total we will be looking at the 'H' column, checking to see if the value is greater than 0 (>0), and if the cell is greater than 0 we will add all those values up together.

=SUMIF
Excel command we are using

H2:H602
Column we are looking in

">0"
Value we are looking for

H2:H602
Column we will be adding together if the values match

Now for a little background as to why I had to do this rather than just SUM the whole column.  From the Price.xlsx we are pulling from TCGPlayer.com.  For some of the cards the price is set to 'SOON', and that just means the site does not have a price for that specific card yet.  Now, we will get to the card pricing in a later blog, but I had an issue when trying to calculate the prices of the cards when there was a 'SOON' value for the specific card.  So with this new formula, if there was issue and the value was something other than a price, this formula will skip over the cell values that are not greater than 0.

Now, we will be losing that value in the totals we are looking for, but we will at least be able to get the totals on the cards there are prices for.  If we did not do this, we could get 'VALUE' in some of the totals instead, and that is a pain in the ass to try and sort out.  So in losing some of the prices due to TCGPlayer not having a price, we still get a running total for all the cards they do have a price for.
_________________________________________
Card Types
Under each section there are rows for each card type known today:
M: Mythic Rare
R: Rare
U: Uncommon
C: Common
L: Land
T: Token

Each Card type also has a total for the section it is in:
Collection
M: =SUMIF(I2:I602,"M",H2:H602)
R: =SUMIF(I2:I602,"R",H2:H602)
U: =SUMIF(I2:I602,"U",H2:H602)
C: =SUMIF(I2:I602,"C",H2:H602)
L: =SUMIF(I2:I602,"L",H2:H602)
T: =SUMIF(I2:I602,"T",H2:H602)

One thing to point out, the Card Type Total will only pull from the Columns for that specific Section. So for the Collection totals, we will only be looking at columns 'I' and 'H'.  This way we can get specific data on the Card Type totals, and keep track of each Card Type easily.  In a previous spreadsheet I manually '+' each of the cells into a single total for each Card Type, and it was a huge pain in the ass to setup.

=SUMIF(I2:I602,"M",H2:H602)

Again if we look at this formula we are using a SUMIF command.  This will allow us to look in one column, check to see if there is a Card Type we are looking for, and then add all those cells together.

=SUMIF
This command will allow you to do a lookup

I2:I602
Here is the section that you are looking at

"M"
This is the character you are doing the lookup on

H2:H602
This is the section you are going to be adding up if there is a "M"

So for each Card Type we are looking into the 'I' column, looking for that specific Card Type and only adding up the prices for those cards in column 'H'.
_________________________________________
Verification
So like I mentioned before we have two different totals we are looking at.

Total 1: =SUM(SUM(A7:A12)+SUM(A17:A22)+SUM(A27:A32))
Total 2: =A6+A16+A26

Total 1 is looking at the SUMs of each Card Type in each Section
- Each Card Type is looking at column 'I' looking at the Card Type, and then adding up all the Card Types that match.

Total 2 is doing a SUM of each Section Total
- Section Totals are just adding up all the prices that are greater than 0 in a specific column (Example: Column H).

This way we are looking to see if we get the same total two different ways.  Now, you are most likely asking yourself why in the world would I do something like this?  Well with different version of my spreadsheets, I have always found that I mess up a lot and the totals you get are not real in some cases. Now I have been able to combat this with using the SUMIF statements, but it is generally a good practice to see if you get the same outcome using multiple formulas.

So you are getting a total of all the prices in a single column, and you are getting the prices of each Card Type and adding those together to see if it is the same value.  I know, it might be overkill, but I really like knowing that my data is correct, and I am looking at the true value of my collection.
_________________________________________

So now that we have gone through that, I will go through the rest of the design of the Set Tabs in a later Blog.