Spreadsheet Update - Cards Needed

So being away from writing I was not just sitting around doing nothing.  I was still trading and purchasing cards, but in doing so I hit some snags and needed to find out more information in concerns with my collection.

Issues that I hit:
1. Cards that I need for my Evernote listing
2. Cards that I have in my tradebook
3. Verification of cards in inventory vs cards in my trade inventory
4. Number of cards needed by color
5. Number of cards needed by rarity

1. Cards that I need for my Evernote listing
So I am not going to go into detail with how I use Evernote just yet, but I will explain the changes I made to my spreadsheet in order to accomplish what I needed.  So for this I needed to easily copy and paste the cards that I need into something mobile that I carry with me for in person trades.
___________________________

Ok so here is the new inventory sheet I have created.  Instead of doing a copy and paste of all the cards and then parsing the data in a notepad, I needed an easy way to populate some cells, do a copy and paste of those rows, sort them, and add them to a list of cards that I needed on Evernote.  So I added some Columns.

___________________________

Here is the new spreadsheet.  I am only showing the top rows, but it will be enough for you to understand the new features.  For this section we are going to concentrate on Columns W, X, Y, Z, and AA.

___________________________

Here you can see what is being populated.  You will also notice that some of the Rows are empty.  I did this so that once there was a full playset that collected then the data in those cells would be nulled out.

Column W: =IF(G2<4,D2," ")

- This will look at the G2 cell (Card Count in the collection) and see if it is below 4, and if it is then it will populate the cell with what is in cell D2.  If G2 is not less than 4 it will leave the cell blank.

- Column D is filled with the card type (ie: Artifact).

Column X: =IF(G2<4,E2," ")

- This will look at the G2 cell (Card Count in the collection) and see if it is below 4, and if it is then it will populate the cell with what is in cell E2.  If G2 is not less than 4 it will leave the cell blank.

- Column E is filled with the card rarity (ie: R for Rare).

Column Y: =IF(G2<4,F2," ")

- This will look at the G2 cell (Card Count in the collection) and see if it is below 4, and if it is then it will populate the cell with what is in cell F2.  If G2 is not less than 4 it will leave the cell blank.

- Column F is filled with the card name (ie: Altar Golem).

Column Z: =IF((4-G2>0),4-G2," ")

- This will look at the G2 cell (Card Count in the collection) and see if it is below 4, and if it is then it will populate the cell with what is in cell G2.  If G2 is not less than 4 it will leave the cell blank.

- Column G is filled with the card needed count (ie: 3, the number of the specific card for the collection to fulfil the playset needed).

Column AA: =IF(Z2=" "," ",IF(C2="SOON",0,C2*Z2))

- This will look at the Z2 cell and see if the cell is blank, if it is blank it will leave it blank.  If it is not blank then it will do another check, it will check to see if C2 contains "SOON", if it does then it will use a zero, but if it is not "SOON" it will multiply C2 with Z2.

- What this formula does is check to see if there is something in the count column for the needed cards.  If there is something there it will check to see if the price pulled from the TCGPlayer is "SOON", and if it is no that means there is a price set.  It will then multiply the card needed count in Z2 by the price in C2.

You should be able to highlight rows W, X, Y, Z, and AA and drag it down to the bottom of the sheet row populated with cards.  This should configure each of the cells with the formulas so you can see what cards are needed for your playset.

Cell Z1: =SUM(Z2:Z602)

This cell will give a total count of all the cards needed.

Cell AA1: =SUM(AA2:AA602)

This cell will give the total dollar amount needed to fill out the playset.

Online Trading - Deckbox.org

Found it, love it!

www.deckbox.org

Seriously the greatest thing I have found for trading.  I know you can sell on eBay, and you can trade to stores, or carry all your trading fodder around and trade at the LGS, but if you are looking for the throw away rares, and looking to make trades that you would normally not find at a LGS, this site is for you.

The way it works is you post your tradelist, and then create your wishlist.  You start out with zero trades and you just start trading.  You can search for specific cards, and you can also just do a search for things that are on your wishlist.  The site does a wonderful job with matching up things on your wishlist to things on others tradelists, and then posts the matches on other traders profile pages.  This means you don't need to sift through all the pages of cards on other traders pages, and you can just hit the profile pages to see if there is a good trade matchup.

Now, if you are a new trader, you will most likely ship first.  Usually you will need to get to over 10 trades before people feel better about trading with you.  Now I have only really had a couple of issues with traders on the site, but nothing pointing to straight up evil people.  Most of the issues I have had were just not very organized people.  Most of the time you can tell who those people are by looking at their tradelist.  Usually when people have un-updated lists, or don't use the condition posting accurately, or don't update the editions the cards are in, then they are more than likely a bad trader.

Granted there are some people that have been on the site for quite some time, and they were early adopters where some of the options were not available, but the site has made it really easy to add and remove cards from your tradelist.  Hopefully I can get to more blogging and share my different ways I have been able to do this quite quickly.

The Forums for BTR (Bad Trade Reports) is very useful as well, and I highly suggest sticking with waiting 2 weeks before opening something up.  I have had issues with shipments not getting to me, I have had issues with my packages getting to other people, and I have had issue with tracking not showing up.

Either way, check out the site.  I have a lot more to share about this site, but just wanted to put this out there as I am trying to do more blogging, and will update some other posts.

Outpost 2000 & Beyond - Coon Rpaids, MN

Outpost 2000 & Beyond - Coon Rapids
http://www.outpost2000.com/

Grade: D

So I was out and about doing some Deckbox trading done, and I was up in the Coon Rapids area and decided to stop by a store I heard about on 'Magic: the Ameturing' podcast.  It was Outpost 2000 & Beyond, and let me tell you, I hope to never go there again.

What I did like about the store:
Plenty of space to play
Lots of space to park
McDonalds close by

What I did not like about the store (I am not even sure you can call it a store):
Not clean at all
McDonalds close by
They had a sign on the front door banning all outside food and drink
     - This just means they will charge you $5.00 for a can of soda
They had a single glass case with a bunch of binders with cards they are selling
     - There was no way to browse any of their cards without bothering the clerk
Their sealed product was very minimal
     - I only saw 1 opened box of each standard set, and nothing else
They has some comics all boxed up in racks near the front
Gaming tables for Warhammer type games were also in the front, but nothing crazy

I guess having a small exposure of product is not a bad thing, but it was just thrown together.  Just not sure what they were trying to do.

All in all, not impressed at all.  I think I spent a total of 3 minutes in that store.  Looked like they had a good showing for 3:30 on a Sunday, but it was mostly kids, Tweens, and teenagers.  It was a very basic beginner crowd for sure.  I spent more time trying to find the store than browsing the items they had for sale.

Now that I am writing this, I did a little more digging.  Looks like there is a sister store in Crystal. Looks like I am going to be stopping in to see if this store is any better.  And also found that there was a calender and Sunday at the Coon Rapids store is Pokemon day, so that explains the young crowd.

I will give an update on the Crystal store as soon as I go, and I hope I don't make it up to the Coon Rapids store again, but I am pretty sure I will, and will be disappointed again.

Update - Oh yeah, the website is nothing special.  They don't sell anything on there, and it looks like it just a bulletin board for the stores.  In fact, when you go to the products and click on the links, they send you directly to the vendor's websites...  So horrible...

Holiday Gift Boxes are back! - Mead Hall Games

They are back!  I dont know what it is about this whole gift box idea, but I love them.

1 2000 count box
4 Theros packs
5 card separators
2 sheets of label stickers
20 lands
1 foil special print card

Now you might think the best part of this whole deal are the packs, or the foil card, but oh no!  I consider the best part of this whole thing is the box itself.  You can hold up to 2000 cards in it, and they stack very well.  When you have a large collection, the hardest thing to do is keeping the small amount of important cards safe.  I had been doing some of this with Fat Pack boxes, but it go to be too overwhelming with how many boxes I needed to keep around.  Now I can keep all my trade stuff in a gift box, and all my foils in another gift box.

I have gone so far as to sleeve up all the cards I am looking to trade, and then storing them in this box. I will get more into the trading setup in a bit, once I get caught up with the sort and count that just got done.

I was hoping they would do this again this year!  So excited, I just had to pick me up two from the Mead Hall Games in Minneapolis when I stopped in to pick up some CUBE sleeves.

Commander 2013 - The Source Comics and Games

So it has been a couple of weeks, but I did go out and buy the new Commander set that was released in the beginning of Nov.  Was able to do a pre-order at The Source Comics and Games, swooped in on release day and picked up all 5 new decks.

It was pretty easy to do the pre-order.  I thought there was going to be a limited order, and I would miss it, but all I needed to do was call them up on the pre-order day and reserve myself a set.  No hassle at all.  The day that I went to go pick them up, it seemed they had a ton extra, but they were telling people they could only buy a single blue deck per person / per day.  So they knew what everyone wanted and limited what people could buy off of that, which is pretty cool in my mind.  I am a collector and really hate losing out on stuff because people over buy and sell on eBay.  The Source also sold at list prices, so that was another reason they limited people.

Love The Source!  The everyman store!

Done counting...

So, after all that I am 98% done with the count.  I still have Portal, Portal 2, and Unglued I need to sort, count, and organize.  But with the 98% I have counted over 160,000 cards, and I have sorted everything in the places they need to be.

I have started a new account on Deckbox.com, and I have my trade book all set.  I will wrtie a completely new blog for the Deckbox.com peice.  I have also bought a couple new items, and I will write a couple new blogs on as well.

I will send an update to this as soon as I get this all sorted. out.

Still counting...

So I have spreadsheet complete and I am still counting.  It takes a very long time to sort and count all your cards, down to the very last basic land...

Lets review how I have everything separated.

Collection
Standard
Standard is going to be split up by Block, and I am also going to keep the Core sets in different boxes. This will allow me to sort the block into the Modern stacks when it gets removed from Standard.
- Block > Color > Alphabetic
- Core > Color > Alphabetic

Modern
- Color > Alphabetic

PreModern
- Color > Alphabetic

Trade
Rare - I am keeping the rares separate for ease of access when I trade or sell.
- Set > Color > Alphabetic

Uncommon - I am keeping the uncommons separate for ease of access when I trade or sell.
- Set > Color > Alphabetic

Common - I am keeping the common separate because of the amount of cards I have.
- Set > Color > Alphabetic

Land
- Set > Color > Alphabetic

Foil
Set > Color >Alphabetic

I started by counting the foils first because I have such a small amount of those cards.

Then I moved to counting the Trade cards because they were separated by Set > Color > Alphabetically and I would not have to switch between Set Tabs when counting the cards like I will have to when I count my collection.  I started with the least amount of cards in Rares.  Then I moved to count the Uncommons, and off to the Commons.  I will be counting Basic lands last because they are basic lands.  I have more Common cards than lands, but who wants to count lands...

When I count my collection I am going to have to swap between Set Tabs because the way I have sorted my Collection was by Color > Alphabetically.  This will take the most time because I will want to make sure that my count is accurate because I will be pulling my needs list from here and posting it on my Evernote app.  (Evernote is an application that I have installed on my Mac, iPad, and iPhone.  It allows me to keep running list of cards that I need with me at all times for when I trade.  I will go through this more when I get to it.)

Column C: Pricing from the Prices.xlsx spreadsheet
Column G, K, O: Card count for the specific section
Column H, L, P: Contains the SUM of the card count of the section and the price giving me the total

I also kept the Column A sections for the totals for Collections, Trade, and Foil.  In each section there are total counters for each rarity type

In my previous blog I went through the Column A totals were setup:
http://dogonsiereht-mtg.blogspot.com/2013/08/set-tab-total-column.html


Please bare with my posts as I am going through 30+ 3200 count boxes and counting every single card. I started 3 weeks ago, and I have gone through Foil, Trade Rare, Uncommon, and Commons from (Alpha - Rise of Eldrazi).  I still need to count Trade Commons (Scars of Mirrodin - M14), Basic Lands, and my Collection.

Going through this process has also pointed out some design changes that I will want to make to the spreadsheet like finding a better way to account for what I have in my trade book, keeping a total tab with everything on it rather than keeping the totals on my Collection total tab, and adding the WotC special decks.  I am also trying to find a good way to keep my sealed product on a separate tab as well.

More to come...