Inventory Analysis with Excel

Pursuit of Knowledge card art

In yesterday's post, I presented information on my card inventory. Some of that information was taken directly from the Dragon Shield app I've been using, but not all- some I had to generate using formulas in Microsoft Excel. This post walks you through how to do that (and come up with some other interesting numbers).

As I said in my original Dragon Shield post, that app works really well, but doesn't present all the information I would like about my collection. Thankfully, it lets you export data as a .csv file, which enables further analysis using Microsoft Excel. Throughout this article, I'll circle the formulas and results I used in Excel.

Step 1: Export your inventory as a .csv file and save it where you like.

Step 2: Save this .csv file as a .xlsx file.

- This is important. CSV files do not save formulas/etc., so if you do your work in that format without converting it, you will lose all your formulas.

Step 3: Open the .xlsx file. Hide or delete columns that do not interest you (for today's post, I deleted most columns to focus on what I cared about). You're ready to begin!

My first goal was to understand the number of unique cards I owned. Since Dragon Shield records different versions of the same card on different lines, I needed a way to count the unique values. Here's how I did that:

- I sorted by the "Card Name" column. This is not necessary, but showed me which cards I had multiple versions of. (In the picture below, you see I have two versions of "+2 Mace," for example.) 

- Select the Card Name column.

- Click "Advanced" in the Sort & Filter area

- Select "Copy to another location." Choose a location (I chose I1). Check the "Unique records only" box:

- This puts a list of unique values in column I. You can see that now +2 Mace shows up only one time, for example:

- Now you can use the COUNTA function in Excel to count the values in column I. This gives you the total number of unique cards you own:

- Note that I also recorded my total number of cards. Dragon Shield tells you this, but I wanted to make sure the Excel data matched that. Easily done counting cards in column A with the SUM function:

My next question was how many copies of a given unique card do I have- disregarding different versions. In Excel, you do that with a SUMIF function- summing the quantity in column A if the card name in column I matches the card name in column B:

If desired, you can then sort by column H to determine which cards you have the most copies of, ignoring versions.

Finally, we'll look at card monetary values. This one is tricky, in a way. Dragon Shield retains versions, in part because different versions may have different values. Dragon Shield will add up your total value based on each card you have of a given version and give you a total. That's good (and the most precise), but I wanted more granular insight.

You can get the average (or mean- I use the terms interchangeably) card value with the AVERAGEIF function. Similar to the SUMIF formula above, you can average the value of different versions of the same card (in the circled example, the +2 Mace card versions are worth either $0.02 or $0.03 . . . this function returned an average of $0.025, but rounded to $0.03, as you can see below).  

If you want the total value of a given card based on how many you have, you can then multiply the average value by the quantity of cards you have. Note: this can be deceiving if you have a version of a card that is much more (or less) expensive than other versions. But for cases where different card versions have approximately equal value, it gives you a reasonable estimate:

You can get the mean value of all cards using the AVERAGE function. Note: I did this same function on the original list (which preserved different versions) and it dropped the mean value by $0.10. This goes back to my point in the previous paragraph- averaging different card versions can skew your results. But however you do it, use the following formula:

You can also get standard deviation using the STDEV.P function:

Using these commands on my averaged data, I see a mean card value of $0.77 and standard deviation of $2.80. Again- these are skewed based on my approach. Using the precise (version-specific) data in column E, I see a mean card value of $0.67 and standard deviation of $2.53.

If my approach is skewing things, why am I using it? Because I care about unique cards, and not their versions. I have 68 copies of Evolving Wilds in 27 different versions, for example. Those range in cost from $0.09 to $0.24, but most trend towards the lower end, so the average value is $0.12. I'm content using that value for all of my Evolving Wilds, and it makes it easier for my final piece of analysis- determining how many unique cards in your collection are above or below a certain dollar amount. The next three screenshots show commands to return the number of unique cards above the average value, above $20, and below $10, respectively:

You can see how easy it is to adjust the command to return different value ranges.

One final comment: most people consider a 'budget' Magic card to be one that can be purchased for $10 or less. My total Magic collection has 11,155 unique cards; 10,986 of them are worth $10 or less. That means 98.5% of my collection is budget, and shows just how much of the game you can explore without breaking the bank.