Some folks trade stocks, some trade shiny pieces of cardboard. I fall in the latter category, and found myself annoyed everytime I had to manually check or track prices and list cards on eBay – so I automated a good chunk of the process, taking inspiration from the finance world.
Tools
Docker, Wasabi, TCGplayer, ClickHouse, Dagster, Pico, Python, eBay SDK
Intro
As mentioned in the snippet above, this whole project revolves around the TCG Yu-Gi-Oh. I was introduced to the show and game early on in middle school, and while I never got into the competitive scene, I have remained a collector. The card game itself has 9,000+ unique cards, all ranging in price from cents to hundreds, and in some cases thousands, of dollars.
I keep my collection cataloged in a Google Sheet, which serves as a portfolio of what I own, and what I still need to collect. I try to keep this source sterile, with the only automated process that writes to it is a script I wrote to pull product IDs from TCGplayer, to simplify the price queries down the line. This sheet is ingested into a database, and prices are updated (appended) every 6 hours. On top of this is a simple UI, which shows the most and least expensive cards (just for fun) and which cards are moving up or down in price (for practical reasons). Should I wish to sell a card on eBay, this UI also uses an API that allows for a quick scheduled listing. Once listed, I get a message on Slack with a link to the newly created listing, from where all I have to do is snap a photo of the card and wait till it goes live.
Currently, all the code resides in a Docker container which runs on a headless mini-PC locally. And while the project is in a functional state, it will remain private until I get around to prettifying it, documenting necessary setup steps, and generalizing the input (i.e. previously mentioned Sheet). Might also toss it up into DigitalOcean, but I don’t want to deal with potential security issues.
Data
To start off, the source for our price data is TCGplayer. This site is amazing, I buy from there frequently. They also have a relatively intuitive API available for free.
I won’t get into the entire logic of how cards are released, what rarities exist, edition differences, and all of that jazz, but there here some important summary points:
- Cards are released in sets.
- Cards have a unique tag, indicating which set they are from.
- Initial set releases are marked as 1st edition, subsequent printings are Unlimited edition.
- For the most part, each card in a set is of one rarity type. There are exceptions, discussed below.
- Each card has an associated product ID in TCGplayer.
Inventory Example
The above is just to show what the inventory sheet looks like. The script I alluded to earlier populates the product ID, a cleaned version of the name, and a link to TCGplayer’s image of the card. This script is run automatically after new data is added, which is relatively infrequent since new data is only available when a new set is released. In the case above, we have one card being of two rarity types in the same set. This case is why simply using the card tag is not enough. An additional identifier (the product id) is unique across these cases and used as a reference throughout the database.
Database
I opted to use ClickHouse for this project, as it was a little more malleable and faster to instantiate than Postgres. The database consists of a handful of tables:
- edition
- rarity
- core_set
- card
- inventory
- price
On startup, a ClickHouse server is initialized inside the Docker container. The database and tables are then created per configurations in a yaml file, and populated using the data in the inventory sheet. The price table is also populated here using a backup of price data located in the S3.
Price Updates
Also part of this Docker container is a Dagster pipeline. Dagster is an awesome tool, relatively simple to get started and offers total control over scheduled or dependant task. Dagster here could have just been a simple chron job, but I wanted to try something new – which is not to say I’m not thinking of simplifying this…
All this pipeline does is:
- Pull product IDs for owned cards from the database.
- Query TCGplayer to get the most recent price for each ID.
- Append the recent prices to the database.
- Back up the price database to S3.
- Prune any prices older than 3 months.
User Interface
The UI is a simple webpage that uses the default pico as a theme. It allows me to easily see what cards are worth, and go simplifies the process of listing them on eBay.
Top and Bottom
The homepage is just a list of the most expensive cards in the collection, along with the cheapest. This page serves
little purpose, other than entertainment value.
Movers
The next page is a list of cards with the highest changes in price. I use this page to see what I should think about
either buying or selling.
Price View
Finally, a raw view of prices. From here I can filter cards for anything above a certain threshold,
and hit the list button to set up a scheduled listing on eBay.
Listing
The only things I have to input to list a card on eBay are pictured below – this is my overly-simplified OMS. All the
tedium of manually filling out repeated things like categories, names, descriptions, postal codes, shipping information,
etc. for cards is pre-filled using a yaml file. I only set the condition and price, and it is listed. From there I get
a Slack message with a link to the scheduled listing on my phone, snap a photo of the card, and done. Onto the next one.
What’s next?
Aside from general clean-up, I want to add some more ‘collection’ functionality. So far, the work has been focused on tracking prices and selling cards. I would ideally like to track the percentage complete of a set so that I can prioritize finishing the collection accordingly.
Lastly, I plan on adding some notifications based on price changes; however, aside from some general metrics of mid vs market prices, I would need to dig around a bit and see what theories folks have on when to buy/sell.