Posted By | Message |
abothebear
Posts: 68
Joined: Jan 2022
|
Friday, August 5, 2022 4:19 PM | |
Suppose I traded an 84 Fleer Gene Garber for a 1987 Topps Greg Gross, a 1986 Fleer George Gervin, and a 1997 Donruss Gary Gaetti. Suppose I wanted to keep track of that trade and future trades. What is a good and easy way to log these in a spreadsheet or table? I am having trouble connecting the three cards to the one. And I want to be able to keep track of it as it develops too. If I traded the 86 Gervin for four more cards, how would I add that on so that it was nested within the Gervin line?
I started out with a table, and put the three cards in one cell in the same row as the traded card. but then there was no way to build off of the single cards in that cell, or even just add a reference to a new row that would be married to that card and not shift when other lines were added to the table. If the cards traded for could be nested in their own cells within the row of the trade, and then I could build off of that in a similar way like a tree, or at least one single column more where I could reference or link to a new row where the next trade would live, that would be great. Of course, sometimes I trade three cards for one card, so if there is a way to organize that kind of thing, I would love to know that as well.
Clearly, I don't know what I am doing.
-------------------------------
Generally, the condition I look for is: 1800s-1929 - G and up (1+)... 1930-1951 - VGEX and up (2.5+).. 1952-1967 - EX and up (5+)... 1968-1978 - EXMT and up (6.5+)... 1979-1994 - EXMT/NM and up (7.5+)... 1995-present - NM+ and up (8.5+)
|
|
|
|
Dumpsterdave
Posts: 74
Joined: Feb 2019
|
Friday, August 5, 2022 8:05 PM | |
Here's my suggestion for Excel / Numbers spreadsheets.
Column A) Transaction number / Trade ID: This can be simple numeric, but it can get difficult to keep track of. You can use a date in this, for example today would by 20220805 (year, month, date makes sorting easier). If you have more than one trade in a day, make that the decimal (e.g., 20020805.1). This is a clugie way to do this but it can work. Note, this will be a text or numeric column - NOT a date format.
Column B) The actual date in date format. (So you can use this to sort).
Column C) To or From. Did you get or recieve said card.
Column D-?) Card information. As many columns as you want to track the card speciffic (player, year, set, number, value etc.)
Anyway you cut this, you're using a spreadsheet to act like a database, and that gets messy regardless of how you do it. I'm working up an example of what it looks like. Using this, you will have a row for each card involved in a trade. If you're using Excel you can use Pivot Tables to summarize each transaction.
-------------------------------
|
|
|
|
Dumpsterdave
Posts: 74
Joined: Feb 2019
|
Friday, August 5, 2022 8:12 PM | |
ID
|
DATE
|
FROM / TO
|
PLAYER
|
YEAR
|
SET
|
SUBSET
|
NUMBER
|
VALUE
|
CONDITION
|
20020805.0
|
8/5/2002
|
TO
|
Garber, Gene
|
1984
|
Fleer
|
|
179
|
-$0.22
|
NMT
|
20020805.0
|
8/5/2002
|
FROM
|
Gross, Greg
|
1987
|
Topps
|
|
702
|
$0.05
|
NMT
|
20020805.0
|
8/5/2002
|
FROM
|
Gervin, George
|
1986
|
Fleer
|
|
36
|
$8.00
|
NMT
|
20020805.0
|
8/5/2002
|
FROM
|
Gaetti, Greg
|
1997
|
Donruss
|
|
117
|
$0.25
|
NMT
|
Edited on: Aug 5, 2022 - 8:13PM -------------------------------
|
|
|
|
abothebear
Posts: 68
Joined: Jan 2022
|
Friday, August 5, 2022 8:41 PM | |
Thanks!! So in this scenario, if I sorted by card, I could see all the transactions it as involved in and the order they occurred, and I could then sort by each transaction if I wanted to see all the cards that were involved in each step of that trade tree? If I understand correctly, this would give me a lot of what a I am looking for, but it wouldn't give me the linear picture. And if I traded Garber for Gross for Grissom for Garvey for Grimm for Garrapollo for Gretzky, it might take a little while to sort my way back through time to find the genesis of my prized new Gretzky. Right?
-------------------------------
Generally, the condition I look for is: 1800s-1929 - G and up (1+)... 1930-1951 - VGEX and up (2.5+).. 1952-1967 - EX and up (5+)... 1968-1978 - EXMT and up (6.5+)... 1979-1994 - EXMT/NM and up (7.5+)... 1995-present - NM+ and up (8.5+)
|
|
|
|
jdmx1
Posts: 5
Joined: Oct 2021
|
Friday, August 5, 2022 10:36 PM | |
This is definitely something that is going to need to be done with a database design. You can do it in Excel with multiple sheets, where each sheet represents a table but you cannot establish PK - FK relationships. So you are going to be responsible for data integrity. And there will have to be someway for you to track the items physically. Scroll to see why.
So lets start with the sheet/table definitions. I will stick with 2 tables as it is the easiest to understand. But in right this out, I was tempted to go to 3 tables and split the cards fields into their own table and have the TradeCards table be just the TradeId, the CardId, and FromTo. But that makes it a lot harder to work with in Excel.
Trade
Id - this can be a date or a number but it must be unique for the trade
Date - when the trade occurred
Who - who you traded with
CCTo - the number of cards you sent -- not really needed but it could be nice to see the count
CCFrom - the number of cards you got -- same as above
TradeCards
Id - this is the same id as the trade
CardId - this is the cards unique id
FromTo - is this card something you sent or received in the trade
Player - card player
Year - card year
Set - card set
... - other card characteristics like SN, auto, Graded, etc. Just have a column for each. The TCDB entry form is a good way to get an idea of what each to add
You need the CardId so you can differentiate between that same card being acquired in different trades. Per your example, in your first trade, you got 87 Gross, 86 Gervin, and 97 Gaetti. Then 4 trades later, you trade for a bulk lot of 87 Topps and you get another Gross. Then 8 trades later, you trade a 87 Gross for a 84 Garvey. Which 87 Gross did you trade? The one from the 1st trade or the one trade 5?
So HOW you store and label your cards physically is going to be just as important for tracking as keeping track of the cards in the spreadsheet.
Also when you trade a card that you acquired earlier, you are going to need to use the same CardId as the one you created when you got it. That CardId is how you are going to be able to link the trades together.
But having you data setup this way will allow you to track your trades and be able to link the cards you flip from one trade to the next.
I think I could create a sql statement that will give you a linear picture. I also think I could do it with a macro. Basically what you would want to do is start with a card that has been involved in a trade and then decide if you want to go up or down the list. To go down, you would find the trade that you sent that card out in and get the list of cards you were sent. You would write that list to a string or another excel sheet etc, and then for each card you got, you would call the same function and look for each card to see if you sent it out in a future trade. You could even tag the iterations with a number or a tab so you could see the tree.
|
|
|
|
abothebear
Posts: 68
Joined: Jan 2022
|
Friday, August 5, 2022 11:47 PM | |
Wow! Thanks for this. I'm going to have to read this through a few times to get my head wrapped around it.
-------------------------------
Generally, the condition I look for is: 1800s-1929 - G and up (1+)... 1930-1951 - VGEX and up (2.5+).. 1952-1967 - EX and up (5+)... 1968-1978 - EXMT and up (6.5+)... 1979-1994 - EXMT/NM and up (7.5+)... 1995-present - NM+ and up (8.5+)
|
|
|
|
jdmx1
Posts: 5
Joined: Oct 2021
|
Saturday, August 6, 2022 9:53 AM | |
If my understanding is correct, what you want to do is not trival. It is going to require a lot of precise data entry, even more than getting your inventory uploaded to tcdb. And the entry is going to be tedious.
This is a function that would be something tcdb could implement, although the recent change of having duplicates card just increase the count instead of creating a new record would need to be rolled back. Even then, you would need to figure out how to mark the cards ( label on penny ) so you could id what card you are sending in a trade. This actually gets easier for SN or graded cards if you add the SN value to the db.
The reason tcdb could add this is the schema for set/card labeling is already set. The above schema design is just trying to dumb down what a full schema implementation would look like. Then if EVERY trade you made was done through the tcdb transaction manager, you would be able to match of the CardId like I described above as this is how they do trade transactions ( I am assuming a bit here but I understand how I would design it )
How you trade is going to matter too. If you do bulk trades, it is going to get a bit hard to keep track. Did you just trade 5 for 5 or was it 1 for 1 five times?
I have a spreadsheet that I use to keep track of my card purchases on online. I record the purchase price, sales tax, and split the shipping charge among the items in the package. I also have tabs that I use to keep track of different sets I am working and my progress towards completion. I collect SN patch sets and Triple Thread SN sets. For SN sets, in some cases ( okay most ), I am working on a rainbow set instead on any 1 specific SN. Only if I get close on something do I change focus. Well, tcdb does not currently allow a display of needed cards for a rainbow set ( submitted a request though ) so I need to track my progress separately. That is how I know I am 65 cards away from the 2019 Topps 150th patch SN rainbow set even though if you look at the /25, /50, and /150 want lists, it is going to show a lot more.
Well since I was tracking that, I also decided to add to the checklists the amount I spent on each card. So for a given set I have been tracking ( not all are set up but since I have the master list, I can construct a new list with data if I want ), I can tell you how many cards I have bought of a player, the total I have spent for that card, the average for that card, the total I have spent for the set, and the average per card in the set.
I say this to let you know that the data entry for this can be very tedious, especially if I have gotten a large lot. I am not recording my tcdb trades in the excel sheer other than to mark the cards on the checklist. I still have to add the cards to the tcdb database. It gets real easy to fall behind. At one point, I was a couple hundred cards away from having my entire inventory online. Now that is closer to 1000. Your physical inventory methods are going to matter a lot to allow you to keep everything tracked.
|
|
|
|
budler
Posts: 2,171
Joined: Dec 2017
|
Saturday, August 6, 2022 11:44 AM | |
When I first read your post I was thinking WHY???
Then I reread it and though back 20 years ago and I was thinking about doing something similar (mainly tying sellers/sites/prices/dates) together with the cards. A friend (IT guy) offered to make me a database where I could do it.
Then he LMK It would not be that easy if I wanted to link a lot of things together and run reports. He informed me that coping and pasting can cause problems. Like stated here: If my understanding is correct, what you want to do is not trioval. It is going to require a lot of precise data entry, even more than getting your inventory uploaded to tcdb. And the entry is going to be tedious.
Just to let you know: coping and pasting into excel files can cause issues with sorting. Due to the formatting of where you copied from.
|
|
|
|
abothebear
Posts: 68
Joined: Jan 2022
|
Saturday, August 6, 2022 1:29 PM | |
Last year I bought two different small lots of 70s cards, about 50 cards total. Of the group, I only wanted to keep five or six. But they were in nice shape so I figured someone could use them and could trade them on (it is much easier for me and my budget to trade than buy). Over the last six months I've traded many of them. Often the folks that ant them don't have exactly what I am looking for, but I find something that works so that they can get the cards. Those cards go back on my trad list and they end up getting traded in a similar way. So the answer to the why question is that now I have acquired cards that are three or four trade generations from that initial group, and I was thinking about how interesting it would be to see where the cards in my collection originated from.l (especially if the links keep getting added on). There is an episode of the Office where the employees bring in junk for a swap meet and Dwight begins by trading a push pin and trades his way all the way up to a telescope (and Jim tricks him into trading it for a packet of magic beans). I've seen people do this with real baseball trades too. Some trade trees cover 20+ years. Here is a fun one originating with the Cardinals trading Kent Bottenfield for Jim Edmonds. https://www.vivaelbirdos.com/2018/1/19/16911536/cardinals-kent-bottenfield-jim-edmonds-david-freese-randal-grichuk-toronto-blue-jays-dominic-leone
-------------------------------
Generally, the condition I look for is: 1800s-1929 - G and up (1+)... 1930-1951 - VGEX and up (2.5+).. 1952-1967 - EX and up (5+)... 1968-1978 - EXMT and up (6.5+)... 1979-1994 - EXMT/NM and up (7.5+)... 1995-present - NM+ and up (8.5+)
|
|
|
|
jdmx1
Posts: 5
Joined: Oct 2021
|
Saturday, August 6, 2022 1:46 PM | |
My trick for pasting in Excel is to go to a clean sheet, right click on cell A1, Paste Special -> Values or Paste as Text for html. Then sort to remove the interlaced lines, delete all the blank rows, delete all the blank and unneeded columns. Then use formulas to parse the text into other columns. Once I have the formula right, highlight the column, copy, and paste as values. Once I have the data the way it need to be, then I copy and paste into its correct location. Then I handle the formatting.
Or I am using Excel to format data into an insert or update sql statement. At which point, once I have the query correct, I copy and paste into the db window to run.
|
|
|
|