Since I have a comic book collection of over 55,000+ comics, one of the questions I get from time to time is how do I keep track of my collection. Well, that is a good question and one that I'm going to try and answer here.

This is by no means any sort of suggestion or recommendation on how you should inventory your collection. The only purpose of this webpage is to document my haphazard manner of keeping tabs on my collection.

---

Note: This page is a work in progress and does not currently describe my entire process.

---

Over the years I've used a few different home grown systems and I'll be discussing what I currently use. It is based almost entirely in varisou Excel files with a bunch of Visual Basic for Applications macros that I've written. It isn't the best inventory system and needs some improvement in a number of places. But, it does what I need and pretty much works for me.

Keep in mind that it has been developed on the fly over an extended period of time. My system had to change when I swtiched from shopping at a local comic book store to getting my comics from DCBService.com (aka DCBS). And, while this system was evolving I had to keep track on the dozens of comics I was getting each week. As a result, the structure of this Excel file is organic at best and chaotic at worst. I tweaked the structure substantially as I reorganized my comic book collection into Drawer Boxes. The layout of the columns is partially thought out, partially random and partially to accomodate data entry and some of the VBA macros. A lot of the unused columns are there for layout purposes only.

Don't bother emailing me to tell me that this system is either insane or poorly designed. It is both and I'm completely aware of that. But, I've been working on this on an "as needed" basis in my spare time. And, concurrent to all of that, I've been managing to stay relatively current on reading over 100 comic book titles a month. In addition, I also have been keeping current on tracking the sales of the top 300 comics and top 100 trades each month and doing podcasts on that information. So it isn't like I've been spending lots of time cleaning up this process and perfecting it. It works for me and that is all I need for now.

---

The Files

The system consists of multiple Excel files: _YYYY-MM - Draft.xls, _DCBS Items on Order.xls, _DCBS Order History.xls, _DCBS Shipment History.xls and _My Comic Book Collection.xls. Note the, uhm, "brilliant" naming convention of the files. The underscores are so these files sort at the top fo the folder I've got them in (which is _CBdb if you were wondering).

---

_YYYY-MM - Draft.xls

Okay, this file is fairly easy to describe. It is the monthly order form from DCBS. Every month I download the latest order form off the DCBS download page. I keep an untouched version with the original file name along with the various Word and text files. Then I make a copy of the order form and rename it to _YYYY-MM - Draft.xls with the YYYY replaced with the year and the MM replaced with the month. This version of the file goes into my _CBdb folder and that is the copy that I use for my monthly order.

I have no control over this file format and if DCBS changes it at some point then my system will have to adapt accordingly. The relavent parts of the file is, of course, the list of items offered for the month. This usually starts around line 104 of the worksheet but that doesn't really matter to my system. The important aspects of the format is the column layout of the file:

Column	Description
A	Previews Page
B	Code
C	Enter Quantity
D	Title
E	Retail
F	Your Discount
G	Your Price
H	(Unused)
I	Extended Retail
J	Total

---

Figuring Out my Monthly Order

I'll get into how I use the _YYYY-MM - Draft.xls file for more than just sending in my monthly order to DCBS when I cover how I process my monthly order. For now, the important thing to note is that this is what I consider to be one of the main feeds into my inventory system. It usually takes me four steps to figure out my monthly DCBS order.

The first step is what I call a "blind pass". Basically, I go through the order form shortly after downloading it each month and mark down my order for the items that I know I'm getting and put a "0" (zero) for any items that I want to be sure to investigate later. At this point, I haven't looked at Previews at all hence my referring to it as a "blind pass". These items usually account for the vast majority of my order each month. What can I say, I'm a creature of habit...

When I get can find the time, I usually spend about two hours going through Previews from cover to cover doing a second pass through the order form. This usually accounts for a number of changes here and there but usually nothing too radical. Typically I'll add things that look interesting or that I simply over looked during my first pass throught the order form. At this stage I try to make some decisions on some of the tagged items and sometimes I'll tag a few more items.

The final stage of my ordering deciding process involves making those last few decisions and calculating the nubmer of bags and boards I need for the month. This usually goes pretty quick and is something I normally do the day or two before the order is due at DCBS. I tend to wait until the last minute so I don't have to email DCBS with changes.

That pretty much sums up how I go about figuring out my order each month.

---

Processing my Monthly Order

Once I've figuring out my monthly DCBS order and sent it in, then the fun begins. As I mentioned above, I use the monthly order form from DCBS to populate my inventory system. This involves putting the monthly order data into the _DCBS Order History.xls and _DCBS Items on Order.xls workbooks.

---

_DCBS Order History.xls

I start by putting a copy of the order form worksheet into my _DCBS Order History.xls workbook. Once I've copied the worksheet into the Order History workbook, I rename it to YYYY-MM. I then remove all of the items that I didn't order to condensed the list. I also remove some of the extraneous line at the top of the top but leave in the shipping information and the order summary information.

By removing the lines that I do I wind up with key information from the DCBS order form in predictable places:

Cell	Data
B15	Your order at retail
B16	Discount
B17	Subtotal
B26	Shipping
B29	Your total

This file contains my orders going back to when I first started buying from DCBS in 2003-11. There are another worksheets in this workbook and a chart. The worksheet is called Summary and as you might guess, it contains a summary of my orders. Here is the layout of that worksheet:

Column	Description
A	YYYY-MM
B	(Unused)
C	W (weeks in the month)
D	(Unused)
E	Retail
F	Discount
G	SubTotal
H	Shipping
I	Total
J	Average
K	(Unused)
L	-25%
M	Tax
N	Total
O	(Unused)
P	Savings
Q	Average
R	(Unused)
S	Retail
T	Tax
U	Total
V	(Unused)
W	Savings
X	Average
Y	(Unused)
Z	YYYY-MM
AA	(Unused)

Columns E to J are the data for my DCBS orders. Columns L to Q are an estimate of how much the DCBS orders would have cost me at the local comic shop I had been a loyal customer of had the 25% discountI had remained in place.\ Columns S to X are an estimate of how much the DCBS orders would have cost me at the local comic shop if I had paid full retail price. I track the rough comparison to the local shop as a way to estimate how much I'm saving using DCBS. While the local store had every right to end the discounts the way it did, I feel that it would have been in the bet interests of the store to give customers more than a week to budget accordingly. I think I can accurately say that by blindsiding me with the abrupt ending of the discount that the store has lost thousands of dollars of profit from my business alone. On the upside, thanks to DCBS I was able to take those same thousands of dollars and spend them on more comic books that I would have been able to do at the local shop.

Okay, I quick note about the worksheet naming convention I have in this file and column A and Z of the Summary worksheet. Excel has this cool function called INDIRECT. It allows you to pull the data from another cell based on the cell reference. This means that I can pull the value from B15 from the worksheet named in column A and put it into column E automatically. I do the same thing for the other key pieces of data from the order forms. So once I copy the new worksheet into this workbook, I add a new row into the Summary page with and enter in the name of the new worksheet. I then copy the formulas for the previous line and Excel reads the data from the relavent parts of the new worksheet and updates the chart automatically.

The chart I have in this workbook is for the Retail and Total amounts for the monthly orders. I find that it helps in controlling my spending if I can see how it is trending from month to month. Okay, maybe it doesn't help much but it can be interesting information to look at.

---

_DCBS Items on Order.xls

The next step is a bit more involved and consists of moving the monthly order data into the _DCBS Items on Order.xls workbook. The purpose of this file is to track everything I currently have on order with DCBS and to prepare the data for getting moved into the _My Comic Book Inventory.xls workbook once I actually get the comics.

The main worksheet in this file is called Items on Order. As one might guess from the name, this contains the list of items I have on order with DCBS. The first few rows have some basic information about how up to date the data is. The first row is manually updated to refelct the most recent Previews that the list has data for. The second row is updated by VBA macros and reflects the most recent Diamond shipping list that has been processed. The third row is updated by VBA macros and reflects the most recent Diamond cancellation list that has been processed. The fourth row is manually updated to replace the most recent shipment that I have processed and removed from the list.

The column layout for the worksheet is rather long as it is what I use to map the DCBS data (columns A to G) into the format I keep my inventory in (columns O and on). And don't think for a minute that all of these columns are populated. Most of the columns after column AS are completely optional and usually empty. Here is the list of columns:

Column	Description
A	Status
B	Code
C	Quantity
D	Title
E	Retail
F	Discount
G	Price
H	(Unused)
I	Extended Retail
J	Total
K	(Unused)
L	Ship Date
M	Cancellation Notes
N	(Unused)
O	Publisher
P	Imprint
Q	Title
R	Volume
S	Addendum (Annual, King-Size, Special, etc)
T	Issue (integer value)
U	Issue Text (anything that isn't an integer value)
V	Variation (reprint, second printing, etc)
W	Cover Price
X	Box (which box I've got the comic in)
Y	(Unused)
Z	(Unused)
AA	Reference (TRUE/FALSE: is this a reference book like Who's Who)
AB	Notes (sometimes the note is about my copy, sometimes it is about the issue in general, it is unlikely to be of much use to anybody but me)
AC	(Unused)
AD	(Unused)
AE	(Unused)
AF	(Unused)
AG	(Unused)
AH	(Unused)
AI	(Unused)
AJ	ComicsPriceGuide Code (Title ID for ComicsPriceGuide.com)
AK	(Unused)
AL	Cover Image FileName (folder and name of the resized cover image)
AM	(Unused)
AN	Cover Image Width (width of the resized cover image)
AO	Cover Image Height (height of the resized cover image)
AP	(Unused)
AQ	Cover Image Size (size of the resized cover image in bytes)
AR	(Unused)
AS	Sort By (single column text string used for sorting)
AT	(Unused)
AU	Cover Image Link (hyperlink to the cover image, hyperlinks have been removed from this workbook)
AV	(Unused)
AW	Divider (text for the plastic divider that is in front of that comic book, white text on black means that the title is a reference title and has a black divider instead of a white one)
AX	Divider Count (number of comics between this divider and the next)
AY	(Unused)
AZ	ComicsPriceGuide 9.4 Value (value of the comic if it were grades 9.4 taken from ComicsPriceGuide.com)
BA	(Unused)
BB	ComicsPriceGuide Issue URL (URL to the price guide data at ComicsPriceGuide.com for this comic book)
BC	(Unused)
BD	Raw Cover Image Width (width of the raw cover image from ComicsPriceGuide.com)
BE	Raw Cover Image Height (height of the raw cover image from ComicsPriceGuide.com)
BF	(Unused)
BG	Raw Cover Image Size (size in bytes of the raw cover image from ComicsPriceGuide.com)
BH	(Unused)
BI	Raw Cover Image Link (hyperlink to the raw cover image, hyperlinks have been removed from this workbook)
BJ	(Unused)
BK	ComicsPriceGuide Issue Code
BL	(Unused)
BM	Comments
BN	Artist
BO	Writer
BP	Cover Artist
BQ	Inker
BR	Editor
BS	Age)
BT	Cover Price
BU	Date
BV	(Unused)
BW	Gem Mint
BX	Mint
BY	Mint Minus/td>
BZ	Near Mint Plus
CA	Near Mint
CB	Near Mint Minus
CC	Very Fine/Near Mint
CD	Very Fine Plus
CE	Very Fine
CF	Very Fine Minus
CG	Fine/Very Fine
CH	Fine Plus
CI	Fine
CJ	Fine Minus
CK	Very Good/Fine
CL	Very GoodPlus
CM	Very Good
CN	Very Good Minus
CO	Good/Very Good
CP	Good Plus
CQ	Good
CR	Good Minus
CS	Fair/Good
CT	Fair
CU	Poor
CV	(Unused)
CW	Order
CX	(Unused)

As you can see, it is a little insane. Okay, maybe more than a little insane...

So, every month, once I have placed my order with DCBS and have updated the _dCBS Order History.xls workbooks I insert the rows of itesms that I have ordered into this worksheet. Then comes the no so fun part. I sort the list by column D so it is in alphabetical order by the DCBS description and start populating columns Q to AJ. This usually just involves making a copy of the data from an adjacent line and changing the issue number in column T. Sometimes the price has to be changed in column W also. I've got a formula in cell W4 that compares the total of column E with the total of column W so I know if they match. I've also got conditional formating on column W so that cells that don't have the same price as column E are highlighted automatically. This makes it easier to spot cover price mistakes. The initial value in the Box column is as follows:

Box	Items
Action Figures	Action figures
Books	Books
DCBS	DCBS items like the weekly shipping charge and bags and board
Magazines	Magazines
RPGs	Role playing games
Previews	Previews
To Read	New comics
To Read: Trade Paperbacks	Trade Paperbacks
To Read: Hardcovers	Hardcovers

This step usually takes a little while and sometimes requires pulling up the _My Comic Book Collection.xls workbook to get the data for a title that I no longer have on this worksheet.

So, why do I go to this trouble versus using the existing data from the DCBS order form? Well, for starters, my inventory file predates my shopping at DCBS. But the main reason is that I like to maintain complete control over how the titles in my inventory as that dtermines the sort order. Since I keep my actual comic books in this same sorted order in the Drawer Boxes, this is important to me.

---

Processing my Weekly DCBS Shipment

Each week I get a shipment from DCBS. The first thing I do before I even get the shipment is download the weekly shipping list from the Diamond website.

VBA Macro: ProcessShippingList

I then run the ProcessShippingList VBA macro that I've written to process that file. This VBA macro parses the weekly shipping list from Diamond and marks the comic in the Items on Order worksheet that have shipped. Column A is populates with the letter "x" and the ship date is put into column L. Columns A and B and both highlighted in green. This macro also updates cell E2 with the date of the shipping list. It usually only takes a few second for the marco to highligh the items that shipped for the week.

I've got the Items on Order worksheet set up with AutoFiltering. This allows be to filter the list down to just the items that shipped very easily. I then go through this list and look up the Comics Price Guide.com title ids for any new titles. This is a manual step and usually doesn't take more than a few minutes.

Once I've processed the shipping list I then make a copy of this worksheet and name the copy Shipment ### - YYYY-MM-DD. The ### is the number of the shipment from DCBS. I started at 001 and add one to it each week. As you can see, it is a very complicated process. And while this number isn't really needed, I like knowing how many weekly shipments I've gotten from DCBS. The YYYY-MM-DD is the date the shipment hit the comic book stores. I usually get my shipment from DCBS about five days later. On this new worksheet I then remove the first four rows and all of the rows for items that didn't ship that week.

Once the shipment from DCBS arrives, I go through the box and check each item in the shipment against the packing list. There have been a few times when something was listed on the packing list but not in the shipment. But that has only happen three or four time out of the 160+ shipments that I've gotten. DCBS is amazingly good about getting me the comic books that I order.

Once I've gone through everything in the shipment, I then compare my list of expected items on the Items on Order worksheet to the packing list. If there are things that I didn't expect, I copy the rows for those items from the Items on Order worksheet to the Shipment worksheet for that week and mark column A on the Items on Order worksheet with an "x". Likewise, if there are things that I expected to ship that aren't in the shpment, I delete those rows from the Shipment worksheet and replace the "x" in column A for those rows on the Items on Order worksheet with a "L" (for late). When I'm done with this I essentially have two lists of what was in my shipment. One is the condensed list on the weekly Shipment worksheet which will get moved into the _DCBS Shipment History.xls workbook and aspects of it copied into the _My Comic Book Collection.xls workbook. The other list is the rows marked with "x" in column A on the Items On Order worksheet. Once I'm confident that the lists match and accurately reflect what I got, I delete the rows from the Items on Order worksheet and those items are obviously no longer on order with DCBS. This is a step that I could automate with a VBA macro but do manually. Once I've deleted the rows from the Items on Order worksheet, I manually update cell E3 with the date of the shipping list that was just processed.

At this stage I move the weekly Shipment worksheet out of the _DCBS Items on Order.xls workbook and into the _DCBS Shipment History.xls workbook. Obviously the column layout of the worksheets in this workbook are identical to the Items on Order worksheet in the _DCBS Items on Order.xls workbook. The _DCBS Shipment History.xls workbook is just a historical record of what I got in each weekly shipment from DCBS.

The next step is to copy the items from the shipment into the _My Comic Book Collection.xls workbook. This is a multistep process and one that I probably should automate with a VBA macro when I get a chance. The first thing I do is insert enough blank rows into the ComicBookInventory worksheet in the _My Comic Book Collection.xls workbook for the new items. I then copy the data from columns O to CX from the weekly Shipment worksheet in the _DCBS Shipment History.xls workbook into columns A to CJ of the the ComicBookInventory worksheet in the _My Comic Book Collection.xls workbook. I then remove the conditional formating on the copied cells in column I of the new rows on the Comic Book Inventory worksheet.

And that is basically the processing I go through each week for my DCBS shipments. I'll cover more about the layout and management of the ComicBookInventory worksheet below.

---

---

VBA Macro: ProcessCancellationList

Each month, along with the monthly Previews data, Diamond released a list of cancelled items. I have a VBA macro in my _DCBS Items on Order.xls workbook that processes these lists. This VBA macro parses the monthly cancellation list from Diamond and marks the comic in the Items on Order worksheet that have been cancelled. Column A is populates with the "#" and the cancellation date and reason is put into column M. Columns A is highlighted in red and columns B and D to J are formatted with the strikethrough so they appear crossed out. This macro also updates cell E3 with the date of the cancellation list. It usually only takes a few second for the marco to highligh the items that have been cancelled. The rows are only removed from the worksheet after I have confirmed that the items have been cancelled from my order with DCBS.

---

VBA Macro: GetCoverImages

One of the macros I've written gets the cover images from www.ComicsPriceGuide.com and resizes them to 80 pixels wide. Unfortunately this macro seems to have broken recently due to some changes in the www.ComicsPriceGuide.com website. But I knew that was a risk when I first wrote that code. Prior to that macro breaking, I was able to download images for just over 98% of my collection from www.ComicsPriceGuide.com. I originally put this macro together because I found it much easier to verify my inventory using cover images than a huge grid of data.

---

_My Comic Book Inventory.xls

Here is a rundown of the structure of the Inventory worksheet in "My Comic Book Collection" Excel workbook as I've currently got it:

Column	Description
A	Publisher
B	Imprint
C	Title
D	Volume
E	Addendum (Annual, King-Size, Special, etc)
F	Issue (integer value)
G	Issue Text (anything that isn't an integer value)
H	Variation (reprint, second printing, etc)
I	Cover Price
J	Box (which box I've got the comic in)
K	(Unused)
L	(Unused)
M	Reference (TRUE/FALSE: is this a reference book like Who's Who)
N	Notes (sometimes the note is about my copy, sometimes it is about the issue in general, it is unlikely to be of much use to anybody but me)
O	(Unused)
P	(Unused)
Q	(Unused)
R	(Unused)
S	(Unused)
T	(Unused)
U	(Unused)
V	ComicsPriceGuide Code (Title ID for ComicsPriceGuide.com)
W	(Unused)
X	Cover Image FileName (folder and name of the resized cover image)
Y	(Unused)
Z	Cover Image Width (width of the resized cover image)
AA	Cover Image Height (height of the resized cover image)
AB	(Unused)
AC	Cover Image Size (size of the resized cover image in bytes)
AD	(Unused)
AE	Sort By (single column text string used for sorting)
AF	(Unused)
AG	Cover Image Link (hyperlink to the cover image, hyperlinks have been removed from this workbook)
AH	(Unused)
AI	Divider (text for the plastic divider that is in front of that comic book, white text on black means that the title is a reference title and has a black divider instead of a white one)
AJ	Divider Count (number of comics between this divider and the next)
AK	(Unused)
AL	ComicsPriceGuide 9.4 Value (value of the comic if it were grades 9.4 taken from ComicsPriceGuide.com)
AM	(Unused)
AN	ComicsPriceGuide Issue URL (URL to the price guide data at ComicsPriceGuide.com for this comic book)
AO	(Unused)
AP	Raw Cover Image Width (width of the raw cover image from ComicsPriceGuide.com)
AQ	Raw Cover Image Height (height of the raw cover image from ComicsPriceGuide.com)
AR	(Unused)
AS	Raw Cover Image Size (size in bytes of the raw cover image from ComicsPriceGuide.com)
AT	(Unused)
AU	Raw Cover Image Link (hyperlink to the raw cover image, hyperlinks have been removed from this workbook)

As a Database Administrator I find the structure to be a complete mess on a technical level but it facilitated the mass data entry I did while I reorganize my comics.

I intend to replace the Volume number with the year that the title started but haven't gotten around to doing this yet. I think I've got a way to convert my data to that but it just hasn't been a high priority for me. That and I need to figure out how I want to handle multiple volumes of a given title from the same publisher that all started during the same year.

I've also got a worksheet with information on the various titles. Here is the structure of that worksheet:

Column	Description
A	Publisher
B	Imprint
C	Title
D	Volume
E	Addendum
F	Series Launch Date
G	(Unused)
H	ComicsPriceGuide Title ID
I	ComicsPriceGuide Title Name
J	ComicsPriceGuide Title Date Range
K	ComicsPriceGuide Title URL
L	(Unused)
M	(Unused)
N	(Unused)
O	(Unused)
P	(Unused)
Q	(Unused)
R	(Unused)
S	(Unused)
T	Sort By (single column text string used for sorting)
U	(Unused)
V	Reference (TRUE/FALSE: is this a reference book like Who's Who)

I sort things alphabetically, ignoring spaces and punctuation. Numbers are spelled out (Tenth Muse versus 10thMuse). This is the approach used by most price guides including the Standard Catalog of Comic Books which I use as a guideline on what the "official" title is for some comics.

The "Sort By" column is in the format of "Title [Publisher: Imprint] (v#) Addendum #000Issue IssueText". If there is no Imprint, the ": Imprint" is left out. If there is no Addednum, the " Addendum" is left out. The Issue number is zero filled to 8 digits (to hold that magic number of 1,000,000 thanks to DC). If the issue is negative (like -1, thank you Marvel) the first digit out of the 8 is replaced with an underscore. If there is any IssueText data, it is concatenated immediately after the Issue number with no space inbetween. All of this results in a single string that I used to sort by (Excel can't handle complex sorting very well but is excellent for on the fly data manipulation and data entry).