Comic Book Page

My Comic Book Inventory System

(Last updated: 2014-10-29)

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:

ColumnDescription
APreviews Page
BCode
CEnter Quantity
DTitle
ERetail
FYour Discount
GYour Price
H(Unused)
IExtended Retail
JTotal


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:

CellData
B15Your order at retail
B16Discount
B17Subtotal
B26Shipping
B29Your 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:

ColumnDescription
AYYYY-MM
B(Unused)
CW (weeks in the month)
D(Unused)
ERetail
FDiscount
GSubTotal
HShipping
ITotal
JAverage
K(Unused)
L-25%
MTax
NTotal
O(Unused)
PSavings
QAverage
R(Unused)
SRetail
TTax
UTotal
V(Unused)
WSavings
XAverage
Y(Unused)
ZYYYY-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:

ColumnDescription
AStatus
BCode
CQuantity
DTitle
ERetail
FDiscount
GPrice
H(Unused)
IExtended Retail
JTotal
K(Unused)
LShip Date
MCancellation Notes
N(Unused)
OPublisher
PImprint
QTitle
RVolume
SAddendum (Annual, King-Size, Special, etc)
TIssue (integer value)
UIssue Text (anything that isn't an integer value)
VVariation (reprint, second printing, etc)
WCover Price
XBox (which box I've got the comic in)
Y(Unused)
Z(Unused)
AAReference (TRUE/FALSE: is this a reference book like Who's Who)
ABNotes (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)
AJComicsPriceGuide Code (Title ID for ComicsPriceGuide.com)
AK(Unused)
ALCover Image FileName (folder and name of the resized cover image)
AM(Unused)
ANCover Image Width (width of the resized cover image)
AOCover Image Height (height of the resized cover image)
AP(Unused)
AQCover Image Size (size of the resized cover image in bytes)
AR(Unused)
ASSort By (single column text string used for sorting)
AT(Unused)
AUCover Image Link (hyperlink to the cover image, hyperlinks have been removed from this workbook)
AV(Unused)
AWDivider (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)
AXDivider Count (number of comics between this divider and the next)
AY(Unused)
AZComicsPriceGuide 9.4 Value (value of the comic if it were grades 9.4 taken from ComicsPriceGuide.com)
BA(Unused)
BBComicsPriceGuide Issue URL (URL to the price guide data at ComicsPriceGuide.com for this comic book)
BC(Unused)
BDRaw Cover Image Width (width of the raw cover image from ComicsPriceGuide.com)
BERaw Cover Image Height (height of the raw cover image from ComicsPriceGuide.com)
BF(Unused)
BGRaw Cover Image Size (size in bytes of the raw cover image from ComicsPriceGuide.com)
BH(Unused)
BIRaw Cover Image Link (hyperlink to the raw cover image, hyperlinks have been removed from this workbook)
BJ(Unused)
BKComicsPriceGuide Issue Code
BL(Unused)
BMComments
BNArtist
BOWriter
BPCover Artist
BQInker
BREditor
BSAge)
BTCover Price
BUDate
BV(Unused)
BWGem Mint
BXMint
BYMint Minus/td>
BZNear Mint Plus
CANear Mint
CBNear Mint Minus
CCVery Fine/Near Mint
CDVery Fine Plus
CEVery Fine
CFVery Fine Minus
CGFine/Very Fine
CHFine Plus
CIFine
CJFine Minus
CKVery Good/Fine
CLVery GoodPlus
CMVery Good
CNVery Good Minus
COGood/Very Good
CPGood Plus
CQGood
CRGood Minus
CSFair/Good
CTFair
CUPoor
CV(Unused)
CWOrder
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:

BoxItems
Action FiguresAction figures
BooksBooks
DCBSDCBS items like the weekly shipping charge and bags and board
MagazinesMagazines
RPGsRole playing games
PreviewsPreviews
To ReadNew comics
To Read: Trade PaperbacksTrade Paperbacks
To Read: HardcoversHardcovers

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:

ColumnDescription
APublisher
BImprint
CTitle
DVolume
EAddendum (Annual, King-Size, Special, etc)
FIssue (integer value)
GIssue Text (anything that isn't an integer value)
HVariation (reprint, second printing, etc)
ICover Price
JBox (which box I've got the comic in)
K(Unused)
L(Unused)
MReference (TRUE/FALSE: is this a reference book like Who's Who)
NNotes (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)
VComicsPriceGuide Code (Title ID for ComicsPriceGuide.com)
W(Unused)
XCover Image FileName (folder and name of the resized cover image)
Y(Unused)
ZCover Image Width (width of the resized cover image)
AACover Image Height (height of the resized cover image)
AB(Unused)
ACCover Image Size (size of the resized cover image in bytes)
AD(Unused)
AESort By (single column text string used for sorting)
AF(Unused)
AGCover Image Link (hyperlink to the cover image, hyperlinks have been removed from this workbook)
AH(Unused)
AIDivider (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)
AJDivider Count (number of comics between this divider and the next)
AK(Unused)
ALComicsPriceGuide 9.4 Value (value of the comic if it were grades 9.4 taken from ComicsPriceGuide.com)
AM(Unused)
ANComicsPriceGuide Issue URL (URL to the price guide data at ComicsPriceGuide.com for this comic book)
AO(Unused)
APRaw Cover Image Width (width of the raw cover image from ComicsPriceGuide.com)
AQRaw Cover Image Height (height of the raw cover image from ComicsPriceGuide.com)
AR(Unused)
ASRaw Cover Image Size (size in bytes of the raw cover image from ComicsPriceGuide.com)
AT(Unused)
AURaw 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:


ColumnDescription
APublisher
BImprint
CTitle
DVolume
EAddendum
FSeries Launch Date
G(Unused)
HComicsPriceGuide Title ID
IComicsPriceGuide Title Name
JComicsPriceGuide Title Date Range
KComicsPriceGuide Title URL
L(Unused)
M(Unused)
N(Unused)
O(Unused)
P(Unused)
Q(Unused)
R(Unused)
S(Unused)
TSort By (single column text string used for sorting)
U(Unused)
VReference (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).


Copyright (c) 2014