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.

Today Dreamworks released the trailer for our new film How to Train Your Dragon. I’ve been trying to stay away from watching too many shots and sequences from this film because everyone working on it is really enjoying it and I want to be surprised when I see the final piece.

That being said.. the trailer looks great!

Big congrats to Simon Otto and everyone else on the animation crew (and other departments too!).

I happen to have some incredibly talented cousins.. you’ve all heard about my fab photographer cousin Erica Berger.

Another georgeous and awesome cousin Zeva Bellel has a great site called Paris By Appointment Only.

Now, one of my cousins from my dad’s side of the family has started this really cool project called The Grok Project.  Starting in november, Ian is going to spend three years steping through the entire history of science and technology by re-creating and experiencing as much of it as possible,  blogging about the whole thing (yay internets!).

Some of the cool things he plans on doing to “grok” science are:

• Make stuck stone tools
• make copper tools
• make woven cloth
• visit a copper mine
• impress cuneiform writing on clay tablets
• Measure earth’s circumference/diameter using eratosthenes’ method
• make a blowgun
• build hand-cranked rotary fan
• build a trebuchet
• measure the speed of light
• hot air ballooning
• assemble an internal combustion engine
• scuba diving
• build a telephone
• build a vacuum tube
• extract/splice DNA
• build a humanoid robot

and more!

Check out the project as it moves forth.. I smell a TV show coming on!!

Rock ‘n Roll Bride · Under The Spotlight – Erica Berger.

My cousin Erica has been featured on Rock ‘n roll Bride!  Go Erica!!

Courier: First Details of Microsoft’s Secret Tablet – Microsoft courier tablet – Gizmodo.

I’m not one to usually covet microsoft products.. but oh man this looks amazing.  I love my tabletPC.. I’d love it more if it were a mac.  But this.. this.. this may pull me away from mac..

MAY pull me away..

I’m SUPER jazzed and excited to see Bluetoo venture’s Todoist application for the iPhone.  As many of you know I’ve been researching GTD applications and trying to come up with as system that works for my needs.

I’m currently using Toodledo which is a very nice application.. it’s well documented, has tons of support, and the development team is great.  The only problem is that I’m just not a fan of the web UI. It’s difficult to use.. the sub-task support is complicated and feels buggy. I’m not saying it is buggy.. it just feels funky when i use it. Oh, and the UI just doesn’t make me scream with joy.  There are a lot of great things about Toodledo.. it really is a fantastic product.  However, for day to day use I keep coming back to..

Todoist.  It is a much simpler application but I LOVE the UI.  Unfortunately, it hasn’t had an iPhone application to match, therefore making it unusable for me in production.

Well, Bluetoo Ventures is about to release “Doings”.. their todoist iPhone application.  I haven’t seen it yet, but I’m VERY excited.. can’t wait to give it a shot!

As our production has begun, I’ve been talking to our animators about my goals in running the animation department for the show. It’s been an interesting process, one in which I’ve learned a lot about myself, my values, and how I feel our department can be most sucessful.

I’m not going to get into specific tricks and techniques for what we’re going to do (this is a public blog, after all), but I thought others might find it interesting at least to hear what I feel are the 3 areas of focus that are important to having a creative, successful, and happy team dynamic. I think that these values probably apply to almost any team dynamic.

You’ll notice that I’ve labeled each area A, B, and C, not 1, 2, 3, because I don’t think there is a hierarchy as to which area should be considered first.  Each area is of equal importance, and while not every decision one makes can have each area “win”, I believe if each area is considered while making decisions, in the long run you can achieve growth and success in all of them.

Area A: The Best Quality Product

This area of focus is on making the best quality product (whatever it is you’re creating.  In my case, the best animation and acting we can produce).  Here, all of your decisions should be focused on the good of the product.  This include things like – who do you have working on the most key components of your product?  Have you done your research?  What is the end goal?  What tools do you need to reach that goal?  What are the possible pitfalls that can get in the way?   When I started on this show I pictured the end result of what we would need to have a successful show.  What would our rigs need to do?  What specific tools would we need?  Who knowledge would I have needed to have?

Picturing the end result allowed me to work backwards and ask important questions about the features we needed.  If the rigs had to support “x”-feature, then what do I need to do to ensure that could happen?  How much planning do we need to have?  Are the tools even available to support this feature?  Who do I know that can create it?  How much time would it take?

By asking these things at the head of the show, I could build a path and plan on how to ensure we would be ready once the show started.  Knowing that it’s impossible to foresee every eventual hurdle and hiccup, we also created a plan for how to handle problems we don’t know about yet.  It’s important to realize that something will happen that will try and keep you from reaching this goal.  Instead of being surprised and reacting to it, create a plan ahead of time so when it occurs, you have a method for dealing with it that keeps everyone sane and “happy”.

Area B: The Happiest and Most Productive Team

Of course it’s impossible to create a great product without a great team behind it.  This second area of focus is all about your team and how it works together.  You have to think about what THEY need to achieve this goal.  What can you do to help the team morale (always an issue when people are working hard and putting their hearts on the line every day in dailies).  How can you help them grow together, learn to trust each other, and work in harmony?  How can you remove any of the political back-stabbing that can happen in group dynamics and help them nurture each other instead?  How can you ensure that perception of your team is one of compassion, cooperation, and inspiration?

Area C: The Individual

Of course, each team is made up of unique individuals, and if every person isn’t feeling satisfied and creative, then it’s difficult to have the team work successfully.  And if the team isn’t working, then there’s no way you’re going to achieve the best product you can.

I like to think broader than that, though.  I’m not just trying to help animators be great so this particular film is great.  I want the animators to be happy, creative, successful, fulfilled, empowered, and passionate because that’s the kind of environment that I feel is a great place to work.  I want every animator to feel like they’re growing, that there is a future for them, and that they “control” that future.  I know that I always work my best when I feel like I have a personal stake in the results of what I’m doing.  I want every animator to have goals that are greater than their current abilities, and know that they’ve got a team of people supporting and encouraging them to achieve those goals.  Knowing that it’s okay for them to reach and fall, because we’re there to help pick them up and allow them to reach again.  If your team doesn’t feel safe reaching and failing, then they’ll never reach and achieve.

Growth.. personal and professional are important to me.   That’s why we’ve asked each animator to create a list of goals for themselves.. for the show, for their careers, for their personal lives.  They can share it with me or not, but I want them to at least think about it.  Then, they should think about what the next physical action they should take that would help them achieve the goal.  This is actually something I did a few years ago when I was animating on Shrek the Third.  I had a goal to become a supervising animator, but I felt like I didn’t quite have the acting chops to achieve it.  So I thought about what I needed to reach this goal.  I wrote to my Head of Character Animation and my Department Supervisor and said that I had an eventual goal to be a supervisor, but felt like I was lacking in subtle acting ability.  In order to achieve my goal, I wanted to focus more of my animation time on subtle shots so I could learn and grow.  I wrote about what things I would be doing personally to become better in this area, and then asked for their support and the opportunity to try these more challenging shots.   Sure enough, I was given more subtle shots and was pushed beyond my comfort level.  I knew I had their support to grow, and felt okay failing because they were behind me.  It was hard, but I worked at it, and I soon became a supervising animator (and now a Head of Character Animation).

Making Decisions..

As I mentioned earlier, not every decision we make can support each area of focus.  Sometimes we have to give certain shots to animators we know can achieve the level required in the short time we have.   Sometimes we can’t afford a training class for the entire department even though it would be incredibly useful and in the long run would help everyone become stronger.  Sometimes an individual animator’s goal on a particular shot or sequence will not work with our current schedule and we can’t accommodate them.  However, we are always thinking of each area of focus with every decision.  We try hard when there are conflicting goals to find a way to still achieve a desired result by looking at things over the course of time.

Maybe this particular shot can’t go to this particular animator, but we can give that animator two simpler shots, and then on the next sequence we’ll give them the shot that will really push them, and in order to support them in their growth, we’ll make sure the supervisor has extra time put aside to help them and work with them so they’re supported and not just left dangling in the wind.

Maybe once in a while we’d have to work saturdays during crunch in order to get the show done on time, and maybe that means that parents won’t get to see their families as much.. but what if everyone in the department took a few bucks every day and put it in a pot, and then we took some money from the department morale budget and set up a family picnic one saturday every month with food and drinks and games for the kids to play?  And what if we personally thanked all the family members for their understanding and patience?

Maybe we have to make hard decisions that make some people unhappy, or it may take longer to achieve their goals.. but I firmly believe that by focusing on these three areas with every decision, and letting the teams know that we’re actively doing this, we can really help create a creative, supported, artistic, professional, forward-thinking, engaged, successful team that will produce the most amazing work possible.

At least, that’s my thought.

I’d love to hear yours!

This past weekend my wife spent some time working on the blog for her business.  She runs an interior design company – Penelope Jones Interior Design – and is quite amazing, I must say. Her sense of color, texture, form, and light blows me away.  I love watching her step into a room, look around, and immediately her brain starts churning as she works out what walls need to go, how the flow of the room should work, and what the place should feel like.

We recently renovated our bathroom, and I was in the lucky position to not see any of it until it was done.  I was blown away.  She took this really disgusting little room that I hated being in & turned it into a gorgeous, elegant, and beautiful room that just feels “right”.  AND she did it on a budget, not that you can tell by looking at it.

Enjoy!

People have been asking, and I’ve finally gotten around to providing both my Fast Animation Rigs DVD and Integrating a Creature Rig within a Production Pipeline DVD as downloadable from LULU.com.

You can access them and other items in the store.

I’m often asked if these titles are still relevant in the industry today, and I have to say I still think they are. They’re obviously older techniques (they came out in 2001 and 2002), but many people consider them to be really great learning tools and I’ve had amazing feedback from them over the years.

I hope people find these useful!