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!
No Responses