Track your Magic The Gathering Collection with OOCalc – Part III

Now on to part III of my series on using OOCalc to track your Magic The Gathering card collection.  If you’ve missed parts 1 and 2, you may want to check them out, since part 3 builds upon those two bits.

Seriously, go read them.  You’ll be missing out on some pretty neato spreadsheet magic.

Go on, it’s okay.  I’ll wait.

Okay, you done?  Great!

In part III, we’re going to create the Collection bit of the spreadsheet where we get the value of our cards and calculate how much our collection is worth!

Oh man, can’t wait to sell the whole thing and buy a new Apple Tablet.

Yeah, I know they don’t “officially” exist yet, but I’m allowed to dream, aren’t I?

Okay, our current spreadsheet has two Sheets.. a blank Collection sheet, and our Pricelist that we downloaded and imported in part 2.

The Collection sheet is going to have the following columns: Card Name, Count, and Value. These should be pretty self explanatory, but just to make sure everyone is on the same page here..

Card Name will be the name of the card I want the value of.  It should match one of the names from the Pricelist sheet.

Count is the number of these cards that I have.

Value is going to be the associated price of the Card Name from the Pricelist sheet, multiplied by the Count.

So far so good?  Great!  Here’s an image of my spreadsheet so far with those headings placed at A1, B1, and C1.

collection1

Let’s go ahead and just enter the name of a card in A2.  For the fun of it, let’s pick a card that’s worth quite a bit of money.  How about.. Ancestral Recall from the Alpha series of cards.

Select A2 and type:

Ancestral Recall (A)

Obviously nothing happens yet, because we haven’t told C2 to get the price.  We’ll use the VLOOKUP function here to make the query.

Select C2 and type:

=VLOOKUP(A2;Pricelist.A2:B14317;2;1)

collection2

Remember.. the first field is the cell we want to grab and the second field is the array we’re going to search.  The third is the column within that array that we’re going to return, and the fourth is whether or not we’re sorting.  Since our pricelist is sorted alphabetially, we’re going to enter “1” for the sort value.

Notice something special in the second field.. we’re specifying a specific sheet to search for the array: Pricelist.A2:B14317.  This tells oocalc to go to a different sheet than the one we’re currently on.  If we didn’t do this, we would be searching the current sheet, and that wouldn’t do us any good at all.

We’re not quite finished yet, we still need to enter a Count.  Let’s pretend that we actually were really lucky and found 4 of these Ancestral Recall (A) cards.  Go ahead and enter 4 in B2.

Notice the value didn’t update, so we need to update the VLOOKUP function to multiply the value by the count.  Change VLOOKUP to:

=VLOOKUP(A2;Pricelist.A2:B14317;2;1) * B2

collection3

Not bad!  However, we still need to make a few more adjustments.  First, remember in part 1 when we copied and pasted our function it would offset the search values?  That’ll happen again if we copy C2 to C3.  Give it a shot and see what you get:

C2: =VLOOKUP(A2;Pricelist.A2:B14317;2;1) * B2

C3: =VLOOKUP(A3;Pricelist.A3:B14318;2;1) * B3

Some of the offsetting is fine.. A2 to A3 and B2 to B3 for example.  But the Pricelist.A2:B14317 being changed to Pricelist.A3:B14318 isn’t going to work.  Again, we need to use the $ symbol to lock down the cells we don’t want to adjust.

Change C2 to:

=VLOOKUP(A2;Pricelist.A$2:B$14317;2;1) * B2

Now you can copy and paste the field without it doing the wrong thing.

BUT you notice that if you DO paste into C3 without putting the name of anything in A3, you get a big lovely #N/A.  That means that the function is working, but it’s not returning a valid value.  That’s pretty ugly, isn’t it.  Let’s fix the display by telling the function to just return a blank string if there’s noting in A3.  We can do that using a Conditional!

We’re going to use an IF statement to check and see if A3 is blank.  If it is, we’ll display a blank value.  If not, then we’ll use our VLOOKUP function.

IF works like this:

IF ( Test; Then_value; Otherwise_value).

For example, if you had a value of “foo” in A1 and put this function in B1:

=if (A1=”foo”;”doo”;”darn”)

B1 would see that A1 does indeed = “foo”, so it would return “doo”.  If we did:

=if(A1=”poo”;”doo”;”darn”)

and A1 was still “foo”, then the expression would see that A1 didn’t equal “poo”, so it would return “darn”.

Does that make sense?

Here’s how we’re going to change our expression in C2:

=IF(A2=””;””;VLOOKUP(A2;Pricelist.A$2:B$14317;2;1) * B2)

Then copy and paste this into C3.  Notice the #N/A is gone?  That’s because we’re using the IF statement to check and see if A2 is blank.  Since it is, then this reuturns a blank string.  That looks much better!

Now let’s go ahead and enter another expensive card name in A3.. this time let’s use.. Badlands (A).

collection4

Notice the Value didn’t update.  That’s because we didn’t enter a count number.  If we simply change the value of count to 1, you’ll see the Value gets updated correctly:

collection5

Wouldn’t it be great if by default the count assumed it was always 1?  We can certainly do that by adding another function into our Value function.. MAX.

MAX will take a list of numbers and return the maximum number.  For example:

MAX(1;50) = 50

MAX(0;10;20;-5) = 20

MAX(.5;.7;150;10010) = 10010

Since we know that most of the time we’ll have 1 or more cards, we can assume that our anything lower than 1 should be returned as… 1.  So all we need to do is change the part of our function where we get the count from B2 to MAX(1;B2).  Then if B2 is 0, it will still return 1.  If it’s 10, then the funciton will return 10.  Sweet!

Go ahead and change C2 to the following function and then paste it into C3:

=IF(A2=””;””;VLOOKUP(A2;Pricelist.A$2:B$14317;2;1) * MAX(1;B2))

Notice that C3 now returns the proper value of the card.. and if you change B3, it will still add up correctly.

Nice!

The last bit of the tutorial is probably the coolest.. I can’t wait for you to see it! 🙂

You’ve probably noticed while scrolling through the list of card names that some of the Magic cards can be quite hard to spell..

It would be really easy to accidentally type in a name wrong and get the wrong value, or no value at all.  We could put in some crazy checking, but with 14,316 cards.. I’d much rather there was an easier way to search and enter card names.

Luckly, there is!

We can use the Validity feature of OOCalc!  It’s a great tool that lets you specify what exactly can be allowed to be entered into a cell.  We’ll use it on the Card Name column to ensure that only the cards shown in the Pricelist can be entered!

To do this, select A2.

Go Data -> Validity

collection6

This will bring up the Validity window:

collection7

Here, you can tell oocalc exactly what type of item to allow in the cell.  The coolest thing is that you can choose a Cell Range.  That’s right.. we can pick the range of cells that are in our Pricelist that have the actual names of the cards.  Not only will this limit the input to only allow those names.. but it will actually create a dropdown option box so the person entering the names can just simply pick the one they want!

Yeah, that’s a LOT of italics, but it’s so cool!

Go ahead and tell it to allow a Cell Range:

collection8

Now enter the range:

Pricelist.A$2:A$14317

Make sure Show Selection List is on.

collection8a

Click OK

You’ll notice that there’s now a pulldown next to A2.  Go ahead and click on it.. you’ll get a pulldown of all the items in the pricelist!

collection10

Just pick the one you want and you’re done!

The rest is up to you.. just copy and the rows as you need them and you’ll be able to get the values of each card!

Enjoy!

Categories:

Tags:

9 Responses

  1. Ethan says:

    Let me know if you get $3,000 for one of your cards.

    Heck let me know if you get $500 for one of your cards.

  2. Matt says:

    Wow!….

    And what about card condition? I mean, a poor or a played Ancestral recall does not match the price of a mint one…
    ^^

    • jason says:

      Good point matt!

      I guess the next step would be to find a site that has a better range of prices & try and scrape those values together into a database.

      My friend Brian also asked if we could figure out a way to keep historical data on each card. I’m sure it’s all possible! When I have time I’m going to look into web-scraping based off a pre-defined list of cards (i.e. the ones we now have). I’m hoping that I can write a visual basic script that will run through the list, scrape a web page for the right info & add it to the spreadsheet, giving us historical data, prices based on card condition, and a glimpse of how rare or common a card is.

      In fact, it might be better to just write something like this in PHP and make it a web page..

      hmm..

  3. gil says:

    Thanks for the tutorial, Jason!
    Getting a baffling error I wonder if you’ve any guesses how to solve. Using the function as you have written for column 3(c), and the validation setting for 1(a), certain cards return a false price value of 0.25. Changing the card name to a different name returns a correct value, so the validation and formula appear to be correct. Changing quantity correctly multiplies the value to Q*.25. Typing a nonexistent card name (ie “faketest”) returns the same .25 result. Now why, even using the dropdown menu to directly select a cardname (in this case, using the latest pricelist, the error card is Dromar the Banisher) would the function be unable to find and match the cardname? When I encountered this error I went into problem solving mode and I’ve now skipped 4 hours sleep to no gain. Baffled and intrigued, look forward to any light you can shed.

  4. gil says:

    Thought I’d follow up, turns out the error occurs when cards in the reference list aren’t sorted in expected ascending order, changing the formula to sort?0 finds the missing itmes and correctly displays. I’ve continued past your tutorial to set up some additional funcitons, having fun with it, let me know if you’re interested in hearing the details.
    -g

  5. Peter says:

    This is f***in awesome! I found my old cards in the basement a few weeks back and now I’m reliving my teenage years from Unlimited all the way to Urza’s. Thanks so much for putting this up, it really helps with how I’m going to be buying and selling cards to create a great collection.

    Thanks again.

  6. jason says:

    haha 🙂 awesome!

  7. Emerald says:

    Great post, I thoroughly enjoyed reading it.

Leave a Reply

Your email address will not be published. Required fields are marked *