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.

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)

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

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).

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:

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

This will bring up the Validity window:

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:

Now enter the range:

Pricelist.A$2:A$14317

Make sure Show Selection List is on.

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!

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!

In my last post on Tracking your Magic The Gathering Collection, I introduced the technique on using the VLOOKUP function to help find prices for individual objects based on a previously defined price list.

If you recall, we ended up with two tables.. a price list and an itemized list of things we’re going to purchase, complete with the number of each item, the price per item, and the total cost.  Here’s the result:

Now we want to do the same thing, but with the price list for our entire Magic The Gathering collection.

Sweet.

In the last post I mentioned a great site where you can get the current going rate for Magic cards.. http://www.magictraders.com/.  So the this part of the tutorial will be how to take the price list and create an Open Office Calc spreadsheet with it.

It should be noted that I’m using Open Office 3 for this tutorial on OS X.  The same principle should apply for Excel, Open Office 2, and most other spreadsheet programs.  I like Open Office because it’s free.. open source.. and pretty powerful.

Step 1: Download the price list

To get the price list, simply go to the MagicTraders website navigate to their pricelists.

You’ll see that they have a few different price lists you can download.  For my collection, I just needed the Non-Foil Physical Magic Cards, and picked the Pipe-Delimited Format. This looks something like:

Card|Price|StdDev|Average|High|Low|Change|Raw N
AErathi Berserker|0.75|0.00|0.75|0.75|0.75|0.00|1
AEther Barrier|0.40|0.10|0.40|0.50|0.30|0.00|2
AEther Burst|0.42|0.12|0.42|0.50|0.25|-0.00|3
AEther Charge|0.25|0.00|0.25|0.25|0.25|0.00|2
AEther Figment|0.27|0.13|0.27|0.56|0.03|0.00|15
AEther Flash (6th)|0.44|0.20|0.44|0.71|0.25|-0.00|3

By inserting the “pipe” or “|” character between fields, it’s very easy for OOCalc to interpret this and turn it into a spreadsheet.  Simply click on the link (you can go there directly by going to: http://www.magictraders.com/pricelists/current-magic-excel.txt).  This will display the link in your browser.

You can then download this file as a text file simply by going File->Save Page As.. and saving the text file somewhere you can find as current-magic-excel.txt.

I usually just save mine on my desktop so I can find it easily.

Step 2: Load the file into OOCalc

Next, we’re going to load the file as a spreadsheet into OOCalc.

In OOCalc, choose File -> Open

Navigate to your desktop (or wherever you saved the file) and load current-magic-excel.txt.

Oocalc will launch a Text Import wizard that will allow you to specify how Oocalc should interpret the file.

As you can see, right now it’s just importing each line as one big field.  We want to break up the items by the “|” character.  To do that, we’re going to click the other checkbox, and type a | character into the field.  You’ll imediately notice the difference in how the fields will be imported:

Click OK and oocalc will open with the entire price list.  It’s a big spreadsheet, but the whole thing will be in there loaded just as you’d like.  Super sweet.

This is a brand new document that Oocalc created.  If you look down at the bottom of the window, you’ll notice a little tab that says Sheet1.

What we’re going to do is insert another “sheet” so our document contains two sheets.. one that’s our collection, and the other that’s our price list.

Step 3: Making Multiple Sheets

Right-click on Sheet 1 and rename it to “Pricelist”.  That way we’ll have a dedicated sheet that we know is our pricelist.

Click OK

Next, we’ll add another sheet and name it “Collection”.

Right-click next to Pricelist and choose Insert Sheet.

Name the sheet  Collection.

Click OK

Now you have two sheets in your document.. Collection and Pricelist.  By clicking on the tabs at the bottom, you can flip back and forth between the two.

Save your document so you don’t loose any of your hard work!

In the next post, I’ll show you how to create the collection list, and use the super-crazy-handy Validity tool so you don’t have to type in the names of your cards each time you want to add a new one!

Just recently some friends at work have started playing Magic The Gathering again.  MTG was one of the first collectible card games from Wizards of the Coast. I got a bit hooked on it back in the late 90′s and used to play quite a lot with friends.  The “official” definition from Wikipedia goes as follows:

Each game represents a battle between powerful wizards, known as “planeswalkers“, who use the magical spells, items, and fantastic creatures depicted on individual Magic cards to defeat their opponents. Although the original concept of the game drew heavily from the motifs of traditional fantasy role-playing games such as Dungeons & Dragons, the gameplay of Magic bears little resemblance to pencil-and-paper adventure games, while having substantially more cards and more complex rules than many other card games.

There’s even a YouTube channel that teaches you how to play!

Honestly, it’s not nearly as geeky as it sounds.  In fact, I believe quite secretly that it was playing this game that really made my wife think “wow.. this American’s not nearly as dorky as I first thought!”

Please.  Let me have my delusions.

Anyway, I kind of forgot about the game for the past 6 years or so and was a bit surprised the other night when it came up in conversation.  What was even more surprising was that one of the animators at work mentioned how much his cards were worth the last time he sold them (to protect his identity, I’ll call him “Don”).  Turns out he made quite a healthy buck by selling his cards to a collector over EBay.

I realized that I actually have quite a few cards sitting down in my basement that might actually be worth some money, so I pulled them out and started looking through various web sites to try and figure out how much they might be worth.

This ended up being incredibly tedious.  For each card I’d go to EBay, search for the card type, find a few prices, average out what the card was worth and try and come up with a “good guess” of what the card is going for.  Every card search was taking 5-10 minutes of investigation.

There had to be a better way.

I realized that what I really wanted was to get a full list of all the card prices that I could find, then take a list of all the cards I have and cross-reference them to determine the prices.  I did a bit of searching and found an awesome site called http://www.magictraders.com/ that has updated price guides every day and week.  The great thing is that you can download the lists into a text file that is easily slurped into a program like Open Office Calc!  For example, if you go here: http://www.magictraders.com/pricelists/current-magic-excel.txt you will get something that looks like:

Card|Price|StdDev|Average|High|Low|Change|Raw N
AErathi Berserker|0.75|0.00|0.75|0.75|0.75|0.00|1
AEther Barrier|0.60|0.29|0.60|0.99|0.30|0.15|3
AEther Burst|0.42|0.12|0.42|0.50|0.25|-0.00|3
AEther Charge|0.25|0.00|0.25|0.25|0.25|0.00|2
AEther Figment|0.27|0.13|0.27|0.56|0.03|0.01|15
AEther Flash (6th)|0.44|0.20|0.44|0.71|0.25|-0.00|3
AEther Flash (7th)|0.36|0.14|0.36|0.56|0.20|-0.00|7
AEther Flash (WL)|0.34|0.04|0.34|0.37|0.30|-0.00|2
AEther Membrane|0.29|0.05|0.29|0.37|0.25|0.00|4

Cool, eh?  It has the card name, the price, the average price, high, low, etc etc.  Tons of data.

So once I found this, I figured I could just turn that into a table in Open Office Calc and then cross reference it with my list of cards.  But how to do this elegantly?

I thought I would start by taking the same concept.. a list of items with a price and see how I could build a way of building a list of how many items I had and how much they cost.

I’ll explain in greater detail.  Let’s say I wanted to go shopping and I was going to purchase 3 apples, 2 bananas, and one feijoa.  I want to find out how much that’s going to cost.  I also have a list of a bunch of fruit and how much each item costs.  What I can do is use that second list as data to figure out the total cost of what I’m going to buy.  Let me show you how.

First, I’m going to create a list of fruit and cost by opening up calc and creating the following list:

As you can see, it’s a list of fruit and their associated costs per item.  Then I want to build a list of what fruit I’m going to purchase.  So I added this table to my page:

Now I just typed in the fruit and number of each piece that I wanted to purchase:

Here you can see both the initial list and the items and amount of each item I want to purchase.  Notice there’s no price showing up in my purchase list yet.  This is where the fun begins!  What I want to do is cross reference the item with the price from the list above.  For example, in cell C9, I want the function to answer the question “What is the cost of an apple?”.  Another way to put it is: “Please look at the item in A9.  Find that same item in the list from A2 to A5.  Once you’ve found that item, tell me what the value is from the B column.”

Fortunately, there’s a really easy way to get this information using the VLOOKUP function.

Simply put, the VLOOKUP function will allow you to search an array of cells to match a certain string and then return the result from the specified column.

In other word: VLOOKUP(<string to search for>; <array of cells to search>;<column from the array to return from>;<mode>).

In this example I would do:

=VLOOKUP(A9;A2:B5;2;0)

So let’s explain each section.  The first item.. the “string to search for” is A9.  This is the cell we’re going to look at.  In the case of row C9 we’re asking the vlookup function to search for the word “apple”.

So where do we want it to search?  Well, we have a price list, so we want it to search in that list, right?  We know apple is SOMEWHERE between A2 and A5.  So we should put that as the array, right?  Well, we could.. but the VLOOKUP function wouldn’t be able to return the information we want.  The way this function works is you pass it all the columns the function will be dealing with, then you specify which column it’s going to return from.  We’re looking for both the name and the price of it, so we enter BOTH columns in our search array: A2:B5

Then we enter the column we’re going to return the value from.  In this case, B2 is the value we want.. that’s in the second column, so we enter 2.

Finally, we tell the function that we don’t care about sort order, so we set the mode to 0.

So here’s the function that we enter in that cell:

=VLOOKUP(A9;A2:B5;2;0)

And what result do we get?

Pretty nifty, right?

Now, let’s copy that function and paste it into C10 and see what the result is.  Select C9, hit ctrl+c (or command+c on the mac).  Then go to C10 and hit ctrl+v (command+v on mac).  You should get the price of the banana.

This seems pretty straightforward, right?  just copy and paste.. copy and paste.  However, if you check out the function for cell C10, you’ll notice it’s changed.. the new function is:

=VLOOKUP(A10;A3:B6;2;0)

Did you see the difference?

The range that the function is searching has shifted down a cell.  Previously the range was A2:B5 and now it’s A3:B6.  This is because we didn’t explicitly say what the range should be.  When you enter a cell coordinate into a function, you have to tell open office calc whether or not to shift those cells when you copy and paste.  So how do we do that?

OOcalc has a nifty way of locking those cells down.. simply by entering a $ in front of either the Cell or the Row.  So in our case, we need to actually change our initial function in C9 to this:

=VLOOKUP(A9;A$2:B$6;2;0)

Now, if we copy that and paste it into C10 the function will look like:

=VLOOKUP(A10;A$2:B$6;2;0)

Notice that the search area stayed the same, ensuring we’re searching the correct cells, but the cell representing the string we’re looking for shifted appropriately (A9 to A10).

The next step is to simply copy that function to all the other slots in the C row that are appropriate.  In the case of our list, just to C11, but if you wanted to buy more produce.. add more lines and go for it!

Now that we have the cost of each individual item, we need to tally up the cost based on the number of items we’re going to purchase.  To do this, select D9 and enter the following function:

=B9*C9

This will give us the actual cost of each item of fruit.  Copy that function to D10 and D11 and we’ll get the cost for each item we’re purchasing.

Finally, we want to sum up the total cost.  Here, we’ll use a simple SUM function to get the number of items we’re purchasing, and the total cost (minus taxes, shipping fees, etc.).

In B13 enter the following function:

=SUM(B9:B12)

And then copy that function to D13 and we’re done!

Now you can see how easy it is to build a list of items based on an initial price list.

In the next post, I’ll talk about how to extend this to creating a really useful way of tracking your Magic Card Collection and value.